عند البحث عن الخلايا المكررة في عمود أو قائمة واحدة فقط، يمكننا استخدام COUNTIF الدالة لتحديد وعد القيم المكررة بسهولة.
11. حدد خلية فارغة بجانب العمود الذي تريد البحث فيه عن القيم المكررة.

2. اكتب الصيغة =COUNTIF($C$3:$C$12, C3)>1، ثم اضغط على مفتاح Enter.

3. اسحب مقبض التعبئة التلقائية لنسخ الصيغة إلى الخلايا الأخرى.

ملاحظات: في الصيغة =COUNTIF($C$3:$C$12, C3)>1،
(1) $C$3:$C$12 هو العمود أو القائمة التي ستبحث فيها عن القيم المكررة. بما أن العمود ثابت عند نسخ الصيغة إلى خلايا أخرى، فهو عادة ما يكون مرجعًا مطلقًا باستخدام “$”.
(2) C3 هي أول خلية في العمود المحدد. وهي مرجع نسبي لأنها تتغير تلقائيًا عند نسخ الصيغة إلى خلايا أخرى.
(3) ستُرجع هذه الصيغة نعم أو لا. نعم تعني أن القيمة المقابلة مكررة، بينما لا تشير إلى أن القيمة المقابلة فريدة في العمود.
(4) أحيانًا قد لا يكون نعم أو لا واضحًا بشكل مباشر. يمكننا دمج الصيغة الأصلية مع دالة IF لإرجاع مكرر مباشرة.
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicates","")

في بعض الحالات، تحتاج إلى مقارنة عمودين واكتشاف القيم المكررة بينهما. على سبيل المثال، لديك قائمتان من الأسماء وتريد معرفة الأسماء المكررة في القائمة الثانية مقارنة بالأولى. يمكنك استخدام دالتي VLOOKUP وIFERROR لإنجاز ذلك بسهولة.

1. حدد خلية فارغة بجانب القائمة الثانية للأسماء.

2. أدخل الصيغة =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),""), ثم اضغط على مفتاح Enter.

3. اسحب مقبض التعبئة التلقائية لنسخ الصيغة إلى الخلايا الأخرى حسب الحاجة.

ملاحظات: في الصيغة أعلاه،
(1) D3 هي أول خلية في القائمة الثانية للأسماء. يجب أن يتغير المرجع تلقائيًا عند نسخ الصيغة إلى خلايا أخرى، لذا فهو مرجع نسبي.
(2) $B$3:$B$18 هي القائمة الأولى للأسماء. وهي مرجع مطلق لأن النطاق يجب أن يبقى ثابتًا عند نسخ الصيغة إلى خلايا أخرى.
(3) عندما يكون الاسم مكررًا مع الأسماء في القائمة الأولى، ستُرجع الصيغة الاسم؛ وإلا ستُرجع فراغًا.
(4) يمكنك أيضًا استخدام الصيغة =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicates","") لاكتشاف الأسماء المكررة في القائمة الثانية مقارنة بالأولى. ستُرجع هذه الصيغة "Duplicates" إذا كان الاسم المقابل مكررًا.

(5) إذا كنت بحاجة للبحث عن التكرارات في عمودين عبر ورقتي عمل، فقط أضف اسم الورقة قبل مرجع العمود المقارن. في مثالنا، فقط غيّر $B$3:$B$18 إلى Sheet1!$B$3:$B$18 في الصيغة.

الصيغ السابقة لا تأخذ حالة الأحرف في الاعتبار عند البحث عن التكرارات، أي أن "apple" تعتبر مكررة مع "APPLE". يمكنك استخدام صيغة مصفوفة للبحث عن القيم المكررة في عمود واحد مع مطابقة حالة الأحرف.

1. حدد خلية فارغة بجانب العمود الذي ستبحث فيه عن القيم المكررة.

2. اكتب صيغة المصفوفة =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"","Duplicate")، ثم اضغط على مفاتيح Ctrl + Shift + Enter معًا.

3. اسحب مقبض التعبئة التلقائية لنسخ صيغة المصفوفة إلى الخلايا الأخرى.

ملاحظات: في صيغة المصفوفة أعلاه،
(1) $C$3:$C$12 هو العمود الذي تحتاج للبحث فيه عن القيم المكررة. المرجع هنا مطلق لأن المرجع يبقى ثابتًا عند نسخ صيغة المصفوفة إلى خلايا أخرى.
(2) C4 هي أول خلية في العمود. المرجع هنا نسبي لأنه يجب أن يتغير تلقائيًا عند نسخ صيغة المصفوفة إلى خلايا أخرى.
(3) إذا كانت الخلية المقابلة مكررة، سترجع صيغة المصفوفة "Duplicate"، وإلا سترجع فراغًا.

يمكنك استخدام ميزة التنسيق الشرطي لتمييز الخلايا المكررة بسرعة في عمود أو نطاق.

1. حدد العمود الذي ستقوم بتمييز الخلايا المكررة فيه.

2. انقر على الصفحة الرئيسية > تنسيق شرطي > قواعد تمييز الخلايا > القيم المكررة. شاهد الصورة أدناه:

3. في مربع حوار القيم المكررة الذي يظهر، اختر مكرر من القائمة المنسدلة الأولى، وحدد سيناريو التمييز من القائمة الثانية، ثم انقر على زر موافق.

ملاحظات: إذا لم تكن سيناريوهات التمييز الافتراضية مناسبة لك، يمكنك اختيار تنسيق مخصص من القائمة الثانية، ثم تحديد لون التمييز والخط وحدود الخلية حسب الحاجة في مربع حوار تنسيق الخلايا الذي يظهر.

ستلاحظ الآن أن جميع الخلايا المكررة تم تمييزها في العمود المحدد كما هو موضح في الصورة أدناه.

ملاحظات:
(1) بعد تمييز الخلايا المكررة، يمكنك بسهولة تصفية هذه القيم المكررة. (انقر لعرض الطريقة)
(2) بعد تمييز الخلايا المكررة، يمكنك أيضًا إزالة التكرارات دفعة واحدة بسهولة. (انقر لعرض الطريقة)

يفضل بعض المستخدمين تمييز الصفوف بناءً على الخلايا المكررة في عمود معين. في هذه الحالة، يمكننا إنشاء قاعدة تنسيق شرطي مخصصة لإنجاز ذلك.

1. حدد النطاق (باستثناء صف الرأس) الذي ستقوم بتمييز الصفوف فيه بناءً على الخلايا المكررة.

2. انقر على الصفحة الرئيسية > تنسيق شرطي > قاعدة جديدة.

3. في مربع حوار قاعدة التنسيق الجديدة،
(1) انقر لتحديد استخدام صيغة لتحديد الخلايا المطلوب تنسيقها الخيار؛
(2) اكتب الصيغة =COUNTIF($C$3:$C$12,$C3)>1 في مربع تنسيق القيم حيث تكون هذه الصيغة صحيحة ؛
نصائح: في الصيغة، $C$3:$C$12 هو العمود الذي يحتوي على الخلايا المكررة، و $C3 هي أول خلية في العمود.
(3) انقر على زر تنسيق ...

4. في مربع حوار تنسيق الخلايا، حدد لون التعبئة والخط وحدود الخلية حسب الحاجة، ثم انقر على أزرار موافق لحفظ الإعدادات.

حتى الآن، تم تمييز الصفوف في النطاق المحدد بناءً على الخلايا المكررة في العمود المحدد. شاهد الصورة:

لتمييز الصفوف المكررة في نطاق معين، يمكنك أيضًا استخدام ميزة التنسيق الشرطي لتحقيق ذلك.

1. حدد النطاق باستثناء صف الرأس.

2. انقر على الصفحة الرئيسية > تنسيق شرطي > قاعدة جديدة.

3. في مربع حوار قاعدة التنسيق الجديدة:
(1) انقر لتحديد استخدام صيغة لتحديد الخلايا المطلوب تنسيقها الخيار؛
(2) في تنسيق القيم حيث تكون هذه الصيغة صحيحة المربع، اكتب الصيغة =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) انقر على زر تنسيق ...

ملاحظات: في الصيغة =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 هو العمود الأول في النطاق، و $B3 هي أول خلية في هذا العمود؛
(2) $C$3:$C$12 هو العمود الثاني في النطاق، و $C3 هي أول خلية في العمود؛
(3) $D$3:$D$12 هو العمود الثالث في النطاق، و $D3 هي أول خلية في العمود؛
(4) إذا كان هناك أعمدة إضافية في النطاق، يمكنك إضافة مراجع الأعمدة وأول خلاياها في الصيغة.

4. في مربع حوار تنسيق الخلايا، حدد لون التمييز والخط وحدود الخلية وغيرها حسب الحاجة، ثم انقر على أزرار موافق لحفظ الإعدادات.

حتى الآن، تم تحديد الصفوف المكررة وتمييزها في النطاق المحدد. شاهد الصورة:

ربما لاحظت أن جميع التكرارات تم تحديدها أو تمييزها بالطرق السابقة. أحيانًا ترغب فقط في رؤية العناصر المكررة باستثناء أول تكرار. يمكنك أيضًا إنجاز ذلك باستخدام ميزة التنسيق الشرطي وصيغة مختلفة.

1. حدد العمود مع صف الرأس.
نصائح: إذا كنت بحاجة لتمييز الصفوف بناءً على التكرارات في عمود واحد باستثناء أول تكرار، حدد النطاق بدون صف الرأس.

2. انقر على الصفحة الرئيسية > تنسيق شرطي > قاعدة جديدة.

3. في مربع حوار قاعدة التنسيق الجديدة:
(1) انقر لتحديد استخدام صيغة لتحديد الخلايا المطلوب تنسيقها الخيار؛
(2) في تنسيق القيم حيث تكون هذه الصيغة صحيحة المربع، اكتب الصيغة =COUNTIF($C$3:$C3, C3)>1;
نصائح: لتمييز الصفوف بناءً على التكرارات في عمود واحد، اكتب الصيغة =COUNTIF($C$3:$C3, $C3)>1.
(3) انقر على زر تنسيق ...

4. في مربع حوار تنسيق الخلايا الذي يظهر، حدد لون التمييز والخط وحدود الخلية وغيرها حسب الحاجة، ثم انقر على أزرار موافق لحفظ الإعدادات.

ستلاحظ الآن أن الخلايا المكررة باستثناء أول تكرار في العمود المحدد (أو الصفوف بناءً على التكرارات في العمود المحدد) تم تمييزها. شاهد الصورة:

عند تمييز الخلايا أو الصفوف المكررة باستخدام ميزة التنسيق الشرطي، يتم تمييز جميع التكرارات بنفس اللون. ومع ذلك، إذا تم تمييز كل مجموعة من القيم المكررة بلون مختلف، سيكون من الأسهل قراءة البيانات والتعرف على الفروقات. هنا، يمكن استخدام VBA لتحقيق ذلك في Excel.

1. اضغط على مفاتيح Alt + F11 معًا لفتح نافذة Microsoft Visual Basic for Applications.

2. في النافذة، انقر على إدراج > وحدة نمطية، ثم الصق الكود أدناه في نافذة الوحدة الجديدة.

VBA: تمييز الخلايا المكررة بألوان مختلفة:

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. اضغط على مفتاح F5 أو انقر على أيقونة تشغيل لتشغيل هذا الكود.

4. في مربع حوار Kutools لـ Excel الذي يظهر، حدد العمود الذي ستقوم بتمييز الخلايا المكررة فيه بألوان مختلفة، ثم انقر على زر موافق.

سيتم الآن تمييز كل مجموعة من القيم المكررة بلون مميز خاص بها.

الأداة الأولى التي سأقدمها هي ميزة تحديد الخلايا المكررة والفريدة، المقدمة من Kutools لـ Excel. تتيح لك هذه الميزة العثور بسرعة على الخلايا الفريدة أو المكررة بسهولة.

1. حدد العمود أو النطاق الذي تريد البحث فيه عن الخلايا المكررة وتمييزها.

2. انقر على Kutools > تحديد > تحديد الخلايا المكررة والفريدة.

3. في مربع حوار تحديد الخلايا المكررة والفريدة، حدد الخيارات حسب الحاجة، ثم انقر على موافق لإنهاء العملية.

ملاحظات: في مربع حوار تحديد الخلايا المكررة والفريدة،
(1) إذا كنت بحاجة لتحديد أو تمييز جميع التكرارات باستثناء أول تكرار، حدد خيار القيم المكررة (باستثناء التطابق الأول) ، وإلا حدد خيار القيم المكررة (بما في ذلك التطابق الأول) حسب الحاجة.
(2) إذا كنت بحاجة لتمييز التكرارات، فعّل خيار تعبئة لون الخلفية ، وحدد لون التمييز حسب الحاجة.
(3) إذا كنت تريد تحديد أو تمييز الصفوف بناءً على التكرارات في العمود المحدد، فعّل خيار تحديد الصف بأكمله حسب الحاجة.
(4) إذا كنت تريد تحديد أو تمييز القيم المكررة مع مراعاة حالة الأحرف، فعّل خيار حساس لحالة الأحرف حسب الحاجة.

يوفر Kutools لـ Excel أيضًا أداة رائعة – مقارنة الخلايا، لمساعدتنا في العثور بسهولة على الخلايا المكررة وتمييزها في عمودين.

1. انقر على Kutools > مقارنة الخلايا لفتح مربع حوار تحديد الخلايا المتشابهة والمختلفة.

2. في مربع حوار تحديد الخلايا المتشابهة والمختلفة، حدد العمودين في مربعي البحث عن القيم ووفقًا لـ، ثم فعّل خيار القيم المتطابقة، وحدد الخيارات الأخرى حسب الحاجة.

ملاحظات:
(1) إذا كنت بحاجة للبحث عن الصفوف المكررة، فعّل خيار بواسطة الصف ؛ وللبحث عن الخلايا المكررة، فعّل خيار بواسطة الخلية في قسم الطريقة ؛
(2) فعّل خيار تعبئة لون الخلفية وحدد لون التمييز إذا كنت بحاجة لتمييز الصفوف أو الخلايا المكررة؛
(3) فعّل خيار تحديد الصف بأكمله إذا كنت بحاجة لتحديد أو تمييز الصف بأكمله بناءً على التكرارات؛
(4) فعّل خيار حساس لحالة الأحرف إذا كنت تريد البحث أو تمييز التكرارات مع مراعاة حالة الأحرف.

3. انقر على أزرار موافق لإنهاء الإعدادات.

ستلاحظ أن التكرارات في العمود الذي حددته في مربع البحث عن القيم تم تحديدها وتمييزها.

سيرشدك هذا الأسلوب إلى تحديد وتمييز الخلايا المكررة باستخدام قاعدة تنسيق شرطي، ثم تصفية البيانات حسب لون التمييز بسهولة في Excel.

1. طبّق تنسيقًا شرطيًا للعثور على القيم المكررة وتمييزها في العمود المحدد. (انقر لعرض الطريقة)

2. انقر لتحديد رأس العمود للعمود المحدد، ثم انقر على البيانات > تصفية.

3. تابع بالنقر على أيقونة التصفية  في رأس العمود، ثم اختر تصفية حسب اللون، ثم اختر لون التنسيق الشرطي المحدد من القائمة المنسدلة. شاهد الصورة:

ستلاحظ الآن أن الصفوف التي تحتوي على خلايا مكررة فقط هي التي تم تصفيتها. شاهد الصورة:

بدلاً من ذلك، يمكننا أيضًا تحديد التكرارات باستخدام صيغة في عمود مساعد، ثم تصفية التكرارات باستخدام العمود المساعد بسهولة في Excel.

1. بجانب البيانات الأصلية، أضف عمودًا مساعدًا، واكتب مكرر كعنوان للعمود.

2. حدد أول خلية فارغة تحت عنوان العمود، وأدخل الصيغة =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicate","")، ثم اسحب مقبض التعبئة التلقائية لنسخ الصيغة إلى الخلايا الأخرى.

ملاحظات: في الصيغة أعلاه، $C$3:$C$12 هو العمود الذي يحتوي على البيانات المكررة، وC3 هي أول خلية (باستثناء خلية الرأس) في العمود.

3. انقر لتحديد رأس العمود – مكرر، ثم انقر على البيانات > تصفية.

4. ثم انقر على أيقونة التصفية  في رأس العمود، وحدد فقط مكرر، ثم انقر على موافق . شاهد الصورة:

حتى الآن، تم تصفية الصفوف التي تحتوي على قيم مكررة فقط. شاهد الصورة:

بشكل عام، يمكننا استخدام =COUNTIF(range, criteria) لعد عدد مرات ظهور قيمة معينة في النطاق المحدد. على سبيل المثال، لعد عدد مرات ظهور "Apple" في القائمة A2:A10، نستخدم الصيغة =COUNTIF(A2:A10, "Apple") لعد عدد هذه القيمة المكررة.

ومع ذلك، فإن الصيغة =COUNTIF(range, criteria) تعد فقط القيمة المكررة المحددة. ماذا عن عد قيمة مكررة بمعيارين أو أكثر؟ وماذا عن عد التكرارات مع مراعاة حالة الأحرف؟ الطرق التالية ستساعدك في حل هذه المشكلات.

يمكننا استخدام صيغة مصفوفة لعد القيم المكررة مع مراعاة حالة الأحرف وفقًا لمعايير في Excel. على سبيل المثال، لعد عدد مرات ظهور القيمة "Apple" في القائمة B2:B21 مع مطابقة حالة الأحرف، يمكنك القيام بذلك كما يلي:

1. حدد خلية فارغة.

2. أدخل الصيغة =SUM(--EXACT(B2:B20,E2)).

3. اضغط على مفاتيح Ctrl + Shift + Enter لإرجاع نتيجة العد.

ملاحظات: في صيغة المصفوفة،
(1) B2:B20 هو العمود الذي ستعد التكرارات فيه. (2) E2 هي الخلية التي تحتوي على القيمة المحددة التي تريد عد مرات ظهورها. يمكنك تغيير مرجع الخلية إلى القيمة بين علامتي اقتباس، مثل "Apple".

أحيانًا قد ترغب في عد التكرارات بمعيارين أو أكثر. يمكنك استخدام COUNTIFS الدالة لإنجاز ذلك.
على سبيل المثال، يوجد جدول مبيعات فواكه كما هو موضح في الصورة أدناه. هنا نحتاج إلى عد عدد مرات تكرار التفاح الذي تم بيعه في7/5/2020 وكان مبلغ المبيعات أكبر من300. يمكنك عد التكرارات بهذه المعايير كما يلي:

1. حدد خلية فارغة.

2. أدخل الصيغة =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. اضغط على مفتاح Enter للحصول على نتيجة العد.

ملاحظات: في الصيغة أعلاه،
(1) B3:B20 هو عمود التاريخ (الأول)، و G4 هو معيار التاريخ؛
(2) C3:C20 هو عمود الفاكهة (الثاني)، و G3 هو معيار الفاكهة؛
(3) D3:D20 هو عمود المبلغ (الثالث)، و ">300" هو معيار المبلغ.
(4) إذا كان هناك أعمدة ومعايير إضافية في الجدول، يمكنك إضافة مرجع العمود والمعيار.

لعد جميع التكرارات في عمود باستثناء أول تكرار، يرجى اتباع الخطوات التالية:

1. حدد خلية فارغة بجانب العمود.

2. أدخل الصيغة =IF(COUNTIF($B$3:B3,B3)>1,"YES","")، ثم اسحب مقبض التعبئة التلقائية لنسخ الصيغة إلى الخلايا الأخرى.

ملاحظات: في الصيغة أعلاه،
(1) $B$3:B3 هو النطاق الذي تعد فيه التكرارات. في $B$3:B3، سيتغير B3 تلقائيًا عند نسخ الصيغة إلى خلايا أخرى.
(2) B3 هي أول خلية في العمود المحدد.
(3) سترجع هذه الصيغة YES أو فراغًا. YES تعني أن القيمة المقابلة مكررة، بينما الفراغ يعني أنها فريدة.

بعد ذلك، تم تحديد جميع التكرارات في العمود المحدد. يمكننا عد نتائج الصيغة للحصول على العدد الإجمالي للتكرارات.

3. حدد خلية فارغة.

4. أدخل الصيغة =COUNTIF(C3:C16,"YES"), ثم اضغط على Enter مفتاح.

ملاحظات: في الصيغة أعلاه،
(1) C3:C16 هو النطاق الذي طبقنا عليه الصيغة لتحديد التكرارات في الخطوة السابقة.
(2) YES هي القيمة التي أرجعتها الصيغة السابقة.

الآن حصلنا على العدد الإجمالي للقيم المكررة في العمود المحدد. العدد الإجمالي للتكرارات لا يشمل أول تكرار.

لعد عدد جميع التكرارات بما في ذلك أول تكرار في Excel، يمكنك استخدام صيغة مصفوفة لتحقيق ذلك.

1. حدد خلية فارغة.

2. أدخل الصيغة =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. اضغط على مفاتيح Ctrl + Shift + Enter لإرجاع نتيجة العد.

ملاحظات: في الصيغة أعلاه، B3:B16 هو العمود المحدد الذي نريد عد التكرارات فيه بما في ذلك أول تكرار.

لتبسيط عملك وتجنب حفظ الصيغ الطويلة، يمكنك تجربة ميزة تحديد الخلايا المكررة والفريدة المقدمة من Kutools لـ Excel، لعد عدد القيم المكررة في القائمة أو العمود المحدد بسرعة مع استثناء أو تضمين أول تكرار.

1. حدد العمود الذي ستعد فيه عدد القيم المكررة، ثم انقر على Kutools > تحديد > تحديد الخلايا المكررة والفريدة.

2. في مربع حوار تحديد الخلايا المكررة والفريدة، حدد خيار القيم المكررة (باستثناء التطابق الأول) أو القيم المكررة (بما في ذلك التطابق الأول) حسب الحاجة، ثم انقر على زر موافق.

3. الآن تم تحديد جميع القيم المكررة مع أو بدون أول تكرار، وفي نفس الوقت يظهر مربع حوار يوضح عدد الخلايا التي تم تحديدها. شاهد الصورة أعلاه.

افترض أنك تريد مقارنة قائمتين من الأسماء وعد عدد التكرارات بينهما. كيف يمكنك حل هذه المشكلة بسرعة؟ يمكننا أيضًا إنجاز ذلك باستخدام صيغة في Excel.

1. حدد خلية فارغة.

2. أدخل الصيغة =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. اضغط على مفتاح Enter .

ملاحظات: في الصيغة أعلاه،
(1) B3:B12 هو العمود الأول للأسماء التي ستعد التكرارات فيها.
(2) D3:D18 هو العمود الثاني للأسماء التي ستعد التكرارات بناءً عليه.

بدلاً من ذلك، يمكننا استخدام إضافة خارجية مثل Kutools لـ Excel، لعد العدد الإجمالي للخلايا المكررة بين عمودين بسرعة.

1. انقر على Kutools > تحديد > تحديد الخلايا المتشابهة والمختلفة.

2. في مربع حوار تحديد الخلايا المتشابهة والمختلفة،
(1) حدد العمودين في البحث عن القيم في و وفقًا لـ بشكل منفصل.
(2) فعّل خيار بواسطة الخلية حسب الحاجة.
(3) فعّل خيار قيمة متطابقة حسب الحاجة.

4. انقر على زر موافق.

الآن تم تحديد جميع الخلايا المكررة في العمود الأول، وفي نفس الوقت يظهر مربع حوار يوضح عدد الخلايا المكررة التي تم تحديدها. شاهد الصورة:

ملاحظات: ستقوم هذه الميزة بعد العدد الإجمالي للقيم المكررة في العمود الذي حددته في مربع البحث عن القيم في مربع حوار تحديد الخلايا المتشابهة والمختلفة. إذا كنت بحاجة لعد العدد الإجمالي للقيم المكررة في العمود الثاني، استخدم ميزة تحديد الخلايا المتشابهة والمختلفة مرة أخرى مع تحديد العمود الثاني في مربع البحث عن القيم.

يمكنك عد كل قيمة مكررة مرة واحدة بسرعة باستخدام الصيغة التالية:

1. حدد خلية فارغة.

2. أدخل الصيغة =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&""))

3. اضغط على مفتاح Enter للحصول على نتيجة العد.

ملاحظات: في الصيغة أعلاه، C3:C19 هو العمود المحدد الذي تريد عد كل قيمة مكررة فيه مرة واحدة.

عند عد قائمة، يمكنك عد كل قيمة مكررة مرة واحدة مع مطابقة حالة الأحرف باستخدام صيغة مصفوفة في Excel.

1. حدد خلية فارغة.

2. أدخل صيغة المصفوفة =SUM(IFERROR(1/IF(C3:C19<>"", FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), ""), MATCH(ROW(C3:C19), ROW(C3:C19))),0),0)).

3. اضغط على مفاتيح Ctrl + Shift + Enter معًا لإرجاع نتيجة العد.

ملاحظات: في صيغة المصفوفة أعلاه، C3:C19 هو العمود المحدد الذي ستعد فيه كل مجموعة من التكرارات مرة واحدة مع مطابقة حالة الأحرف.

إذا كان لديك Kutools لـ Excel مثبتًا، يمكنك أيضًا استخدام ميزة استخراج الخلايا المميزة في نطاق (بما في ذلك أول تكرار) لعد كل مجموعة من القيم المكررة مرة واحدة بسرعة في Excel.

1. حدد خلية فارغة.

2. انقر على Kutools > مساعد الصيغة > العدد > استخراج الخلايا المميزة في نطاق (بما في ذلك أول تكرار).

3. في مربع حوار مساعد الصيغة، حدد العمود الذي ستعد فيه التكرارات مرة واحدة في مربع النطاق، ثم انقر على زر موافق.

سيتم تعبئة نتيجة العد في الخلية المحددة فورًا.

يمكننا استخدام ميزة المجموع الفرعي لعد كل مجموعة من القيم المكررة في عمود في Excel.

1. حدد العمود الذي ستعد فيه كل مجموعة من القيم المكررة، ثم انقر على البيانات > ترتيب تصاعدي أو ترتيب تنازلي.

2. في مربع حوار تحذير الترتيب الذي يظهر، فعّل خيار توسيع التحديد، ثم انقر على زر ترتيب.

ستلاحظ الآن أن التحديد تم ترتيبه حسب القيم المكررة في العمود المحدد.

3. أبقِ التحديد محددًا، ثم انقر على البيانات > المجموع الفرعي.

4. في مربع حوار المجموع الفرعي،
(1) حدد العمود المحدد من قائمة عند كل تغيير في ؛
(2) اختر العد من قائمة استخدام الدالة ؛
(3) فعّل فقط العمود المحدد في قائمة إضافة المجموع الفرعي إلى ؛
(4) انقر على زر موافق ...

ستلاحظ الآن أن كل مجموعة من القيم المكررة تم عدها، وتمت إضافة نتيجة العد أسفل كل مجموعة من القيم المكررة، كما هو موضح في الصورة أعلاه.

يمكننا أيضًا إنشاء جدول محوري لعد كل مجموعة من القيم المكررة بسرعة في Excel.

1. حدد النطاق الذي يحتوي على العمود المحدد، ثم انقر على إدراج > جدول محوري.

2. في مربع حوار إنشاء جدول محوري، حدد المكان الذي ستضع فيه الجدول المحوري الجديد، ثم انقر على زر موافق.

3. في جزء حقول الجدول المحوري، اسحب العمود المحدد إلى قسمي الصفوف والقيم. ستلاحظ الآن أن كل مجموعة من القيم المكررة في العمود المحدد تم عدها دفعة واحدة. شاهد الصورة:

إذا كان لديك Kutools لـ Excel مثبتًا بالفعل، يمكنك استخدام ميزة دمج متقدم للصفوف السهلة الاستخدام لعد كل مجموعة من القيم المكررة في العمود المحدد بسرعة.

ملاحظات: ميزة دمج متقدم للصفوف ستعدل النطاق المحدد وتزيل الصفوف بناءً على القيم المكررة في عمود المفتاح الرئيسي المحدد. لحماية بياناتك، يُنصح بعمل نسخة احتياطية أو نسخ بياناتك إلى مكان آخر قبل تنفيذ العمليات التالية.

1. أضف عمودًا فارغًا إلى يمين نطاق البيانات الأصلي، وسمِّ العمود الجديد باسم العد.

2. حدد نطاق البيانات الأصلي والعمود الجديد معًا، ثم انقر على Kutools > دمج وتقسيم > دمج متقدم للصفوف.

3. في مربع حوار دمج متقدم للصفوف،
(1) انقر لتحديد العمود المحدد الذي ستعد فيه كل مجموعة من القيم المكررة، ثم انقر على عمود المفتاح.
(2) انقر لتحديد العمود الجديد (العد)، ثم انقر على حساب > العد.
(3) حدد نوع الجمع أو الحساب للأعمدة الأخرى إذا لزم الأمر.
(4) انقر على زر موافق ...

ستلاحظ الآن أن كل مجموعة من القيم المكررة في العمود المحدد تم عدها دفعة واحدة. شاهد الصورة:

افترض أن هناك قائمة بالفواكه في عمود. ضمن القائمة، تظهر بعض الفواكه عدة مرات. الآن تحتاج إلى تمييز كل فاكهة مكررة حسب ترتيب ظهورها، كيف يمكنك حل ذلك؟ في هذا القسم، سنستعرض صيغة لإنجاز ذلك بسهولة في Excel.

1. أضف عمودًا فارغًا إلى يمين البيانات الأصلية.

2. أدخل الصيغة =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") في أول خلية من العمود الفارغ المضاف.

3. اسحب مقبض التعبئة التلقائية لنسخ الصيغة إلى الخلايا الأخرى.

ملاحظات: في الصيغة أعلاه،
(1) $C$3:$C$14 هو العمود المحدد الذي تريد عد القيم المكررة فيه حسب الترتيب.
(2) C3 هي أول خلية في العمود المحدد.
(3) إذا كانت القيمة المقابلة مكررة، سترجع هذه الصيغة رقم التسلسل1،2،3... حسب ترتيب الظهور؛ وإذا كانت القيمة فريدة، سترجع الصيغة فراغًا.

يمكنك استخدام ميزة إزالة التكرارات لإزالة جميع القيم المكررة باستثناء أول تكرار مباشرة.

1. حدد العمود الذي تريد إزالة جميع القيم المكررة منه باستثناء أول تكرار.

2. انقر على البيانات > إزالة التكرارات.

3. في مربع حوار تحذير إزالة التكرارات، فعّل خيار متابعة مع التحديد الحالي، ثم انقر على زر إزالة التكرارات.

نصائح: لإزالة الصفوف بناءً على القيم المكررة في التحديد، فعّل خيار توسيع التحديد.

4. في مربع حوار إزالة التكرارات، حدد فقط العمود المحدد، ثم انقر على زر موافق.

نصائح: إذا فعّلت خيار توسيع التحديد في الخطوة السابقة، سيتم عرض جميع الأعمدة هنا. ومع ذلك، يجب تحديد العمود المحدد فقط.

5. بعد ذلك، سيظهر مربع حوار يوضح عدد القيم المكررة التي تم حذفها. انقر على زر موافق لإغلاقه.

يمكنك أيضًا استخدام ميزة التصفية المتقدمة لإزالة جميع القيم المكررة من العمود المحدد بسهولة.

1. انقر على البيانات > متقدم.

2. في مربع حوار التصفية المتقدمة،
(1) فعّل خيار نسخ إلى موقع آخر ؛
(2) في نطاق القائمة ، حدد العمود المحدد الذي ستزيل منه القيم المكررة؛
(3) في نسخ إلى ، حدد النطاق الذي ستلصق فيه العمود؛
(4) فعّل خيار سجلات فريدة فقط ؛
(5) انقر على زر موافق ...

ستلاحظ الآن أن العمود المحدد تم لصقه في النطاق المحدد مع إزالة جميع القيم المكررة باستثناء أول تكرار. شاهد الصورة:

يمكنك أيضًا استخدام كود VBA لإزالة القيم المكررة باستثناء أول تكرار من عمود في Excel بسرعة.

1. اضغط على مفاتيح Alt + F11 لفتح نافذة Microsoft Visual Basic for Application.

2. انقر على إدراج > وحدة نمطية، ثم الصق كود VBA أدناه في نافذة الوحدة الجديدة.

VBA: إزالة القيم المكررة باستثناء أول تكرار

Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys

For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3. اضغط على مفتاح F5 أو انقر على زر تشغيل لتشغيل هذا الكود.

4. في مربع الحوار الذي يظهر، حدد النطاق الذي ستزيل منه القيم المكررة، ثم انقر على زر موافق.

جميع القيم المكررة، باستثناء أول تكرار، تم حذفها فورًا من النطاق المحدد.

ملاحظة: هذا الكود VBA حساس لحالة الأحرف.

يمكننا تمييز جميع القيم المكررة بما في ذلك أول تكرار في عمود أو قائمة باستخدام قاعدة تنسيق شرطي، ثم تصفية جميع القيم المكررة حسب لون التمييز. بعد ذلك، يمكننا تحديد جميع الخلايا المصفاة وحذفها دفعة واحدة.

1. طبّق تنسيقًا شرطيًا لتمييز القيم المكررة. (انقر لعرض الطريقة)

2. حدد العمود الذي ستزيل منه القيم المكررة (بما في ذلك أول تكرار)، ثم انقر على البيانات > تصفية.

3. انقر على أيقونة التصفية  في رأس العمود المحدد. في القائمة المنسدلة، اختر تصفية حسب اللون، ثم حدد لون التمييز من القائمة الفرعية.

الآن تم تصفية جميع القيم المكررة.

4. حدد جميع الخلايا المصفاة، وانقر بزر الماوس الأيمن، ثم اختر حذف الصف من القائمة المختصرة. وفي مربع الحوار التأكيدي الذي يظهر، انقر على زر موافق للمتابعة.

5. بعد ذلك، تم حذف جميع القيم المكررة دفعة واحدة. أبقِ القائمة المصفاة محددة، ثم انقر على تصفية > البيانات مرة أخرى لإلغاء التصفية.

حتى الآن، ستلاحظ أن جميع الخلايا المكررة بما في ذلك أول تكرار تم حذفها دفعة واحدة، ولم يتبق سوى القيم الفريدة.

ملاحظات: ستقوم هذه الطريقة بحذف الصفوف بناءً على القيم المكررة في العمود المحدد.

يمكننا أيضًا استخدام صيغة لتحديد القيم المكررة بما في ذلك أول تكرار في عمود مساعد، ثم تصفية القيم المكررة حسب نتائج الصيغة، وأخيرًا حذف هذه القيم المصفاة دفعة واحدة.

1. أضف عمودًا مساعدًا بجانب العمود المحدد، وأدخل الصيغة =COUNTIF($B$3:$B$11,B3) في أول خلية من العمود المساعد، ثم اسحب مقبض التعبئة التلقائية لنسخ الصيغة إلى الخلايا الأخرى. شاهد الصورة:

ملاحظة: في الصيغة أعلاه، $B$3:$B$11 هو العمود المحدد الذي ستزيل منه القيم المكررة، وB3 هي أول خلية في العمود المحدد.

2. حدد العمود المساعد، ثم انقر على البيانات > تصفية.

3. انقر على أيقونة التصفية  في رأس العمود المساعد، ثم في القائمة المنسدلة حدد جميع القيم باستثناء 1، ثم انقر على موافق . شاهد الصورة:

4. الآن تم تصفية جميع القيم المكررة. حدد الخلايا المصفاة في العمود المساعد، وانقر بزر الماوس الأيمن، ثم اختر حذف الصف من القائمة المختصرة.

5. في مربع الحوار التأكيدي الذي يظهر، انقر على زر موافق للمتابعة.

6. الآن تم حذف جميع القيم المكررة وصفوفها دفعة واحدة. تابع بالنقر على البيانات > تصفية مرة أخرى لإلغاء التصفية.

ستلاحظ الآن أن جميع القيم المكررة بما في ذلك أول تكرار تم حذفها دفعة واحدة.

إذا كان لديك Kutools لـ Excel مثبتًا، يمكنك أيضًا استخدام ميزة تحديد الخلايا المكررة والفريدة لتحديد وحذف القيم المكررة بما في ذلك أو باستثناء أول تكرار بسهولة في Excel.

1. حدد العمود الذي ستزيل منه التكرارات.

2. انقر على Kutools > تحديد > تحديد الخلايا المكررة والفريدة.

3. في مربع حوار تحديد الخلايا المكررة والفريدة، حدد خيار القيم المكررة (بما في ذلك التطابق الأول)، ثم انقر على زر موافق.

ملاحظات:
(1) لتحديد وحذف القيم المكررة باستثناء أول تكرار، فعّل خيار القيم المكررة (باستثناء التطابق الأول) حسب الحاجة.
(2) لتحديد وحذف الصفوف بناءً على القيم المكررة في العمود المحدد، فعّل خيار تحديد الصف بأكمله حسب الحاجة.
(3) لتحديد وحذف القيم المكررة مع مراعاة حالة الأحرف، فعّل خيار حساس لحالة الأحرف حسب الحاجة.
(4) لتحديد، تمييز، وحذف الخلايا أو الصفوف المكررة، فعّل خيار تعبئة لون الخلفية أو تعبئة لون الخط وحدد ألوان التعبئة أو الخط حسب الحاجة.

4. بعد ذلك، سيظهر مربع حوار يوضح عدد الخلايا التي تم تحديدها، انقر على زر موافق لإغلاقه.

5. انقر بزر الماوس الأيمن على الخلايا المحددة، ثم اختر حذف من القائمة المختصرة.

6. في مربع حوار الحذف الذي سيظهر، فعّل خيار نقل الخلايا للأعلى، ثم انقر على زر موافق.

حتى الآن، تم حذف جميع القيم المكررة بما في ذلك أول تكرار دفعة واحدة.

في معظم الحالات، نقوم بتحديد القيم المكررة في عمود، ثم حذف الصفوف بالكامل بناءً على هذه القيم. في الواقع، هذه العملية مشابهة جدًا لإزالة القيم المكررة من عمود واحد. لذلك، يمكننا استخدام حلول مشابهة لحذف الصفوف بناءً على التكرارات في العمود المحدد.

الطريقة الأولى هي استخدام ميزة إزالة التكرارات المدمجة لحذف الصفوف بناءً على التكرارات في العمود المحدد. فقط حدد النطاق الذي ستزيل منه الصفوف، ثم انقر على البيانات > إزالة التكرارات لتفعيل الميزة، وحدد فقط العمود المحدد في مربع حوار إزالة التكرارات، ثم انقر على موافق لإكمال عملية الحذف.

يمكننا أيضًا استخدام ميزة التنسيق الشرطي والتصفية لحذف الصفوف بناءً على القيم المكررة في العمود المحدد. أولاً، قم بتمييز الصفوف بناءً على القيم المكررة في عمود معين باستخدام قاعدة تنسيق شرطي (انقر لعرض الطريقة). ثانيًا، صفِّ النطاق حسب اللون. ثالثًا، احذف جميع الصفوف المصفاة بسهولة. وأخيرًا، قم بإلغاء التصفية أو مسحها، وستلاحظ أن الصفوف التي تحتوي على قيم فريدة فقط في العمود المحدد هي المتبقية.

بدلاً من ذلك، يمكنك إضافة عمود مساعد، واستخدام الصيغة =COUNTIF($C$3:$C$21,C3) لتحديد التكرارات في العمود المحدد. ثم صفِّ الأرقام الأكبر من1 في العمود المساعد، واحذف جميع الصفوف المصفاة بسهولة. بعد إلغاء التصفية، ستلاحظ أن الصفوف التي تحتوي على قيم فريدة فقط في العمود المحدد هي المتبقية.

تقدم الإضافة الخارجية Kutools لـ Excel أيضًا ميزة رائعة وسهلة الاستخدام، تحديد الخلايا المكررة والفريدة، لتحديد الصفوف بسرعة بناءً على القيم المكررة في العمود المحدد، ثم يمكنك حذف هذه الصفوف المحددة بسهولة من خلال قائمة النقر بزر الماوس الأيمن.

توفر ميزة دمج متقدم للصفوف في Kutools لـ Excel أيضًا إمكانية حذف الصفوف بسرعة بناءً على القيم المكررة في عمود المفتاح الرئيسي المحدد.

يمكننا إضافة عمود مساعد واستخدام صيغة لتحديد القيم المكررة بين عمودين، ثم تصفية القيم المكررة وحذفها بسهولة.

1. أضف عمودًا فارغًا بجانب العمود المحدد الذي ستزيل منه القيم المكررة.

2. في أول خلية من العمود المساعد (باستثناء خلية الرأس)، اكتب الصيغة =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Unique","Duplicate")، ثم اسحب مقبض التعبئة التلقائية لنسخ الصيغة إلى الخلايا الأخرى.

ملاحظات: في الصيغة أعلاه،
(1) C2 هي أول خلية في العمود المحدد الذي ستزيل منه القيم المكررة؛
(2) $A$2:$A$13 هو العمود الآخر الذي نحتاج إلى المقارنة معه.
(3) سترجع هذه الصيغة مكرر إذا كانت القيمة المقابلة مكررة مع القيم في العمود الآخر، وترجع فريد إذا كانت مختلفة عن القيم في العمود الآخر.

3. حدد العمود المساعد، ثم انقر على البيانات > تصفية.

4. انقر على أيقونة التصفية  في رأس العمود المساعد، ثم في القائمة المنسدلة حدد فقط مكرر، ثم انقر على موافق ...

5. الآن تم تصفية جميع القيم المكررة. حدد الخلايا المصفاة، وانقر بزر الماوس الأيمن، ثم اختر حذف الصف من القائمة المختصرة. ثم انقر على موافق في مربع الحوار التأكيدي الذي يظهر.

6. بعد ذلك، تم حذف جميع القيم المكررة من العمود المحدد. تابع بالنقر على البيانات > تصفية مرة أخرى لإلغاء التصفية.

ستلاحظ الآن أن القيم الفريدة فقط هي المتبقية في العمود المحدد. يمكنك حذف العمود المساعد حسب الحاجة.

ملاحظات: ستقوم هذه الطريقة بحذف الصفوف بالكامل بناءً على القيم المكررة في العمود المحدد.

إذا كان لديك Kutools لـ Excel مثبتًا، يمكنك استخدام ميزة تحديد الخلايا المتشابهة والمختلفة الرائعة لتحديد القيم المكررة بين عمودين بسرعة، ثم حذفها بسهولة.

1. انقر على Kutools > تحديد > تحديد الخلايا المتشابهة والمختلفة لتفعيل هذه الميزة.

2. في مربع حوار تحديد الخلايا المتشابهة والمختلفة، حدد كلا العمودين في مربعي البحث عن القيم ووفقًا لـ بشكل منفصل، ثم فعّل خياري بواسطة الخلية وقيمة متطابقة، ثم انقر على زر موافق. شاهد الصورة:

3. الآن تم تحديد جميع القيم المكررة بين العمودين في العمود الأول (العمود الذي حددته في مربع البحث عن القيم). ثم انقر على زر موافق في مربع الحوار الذي يظهر.

4. يمكنك الضغط على مفتاح Delete لحذف هذه القيم المكررة مباشرة، أو النقر بزر الماوس الأيمن عليها واختيار حذف من القائمة المختصرة.