كيف تصنّف القيم حسب المجموعة في Excel؟
غالبًا ما يتطلب العمل مع البيانات المجمعة في Excel مقارنة القيم داخل كل مجموعة، مثل تصنيف أرقام المبيعات حسب المنطقة، أو درجات الاختبار حسب الفصل الدراسي، أو مبالغ المعاملات حسب الفئة. وعلى الرغم من أن Excel يوفّر أدوات قوية لفرز البيانات، فإن التصنيف داخل المجموعات (المعروف أيضًا باسم «التصنيف حسب المجموعة» أو «التصنيف الشرطي») يتطلب نهجًا معيّنًا. ويُعدّ هذا النهج مفيدًا بشكل خاص عند الحاجة إلى تقييم الأداء أو تحديد السجلات الأعلى والأدنى ضمن فئات مختلفة دون اختلاط النتائج بين المجموعات. وتستعرض الطرق التالية حلولًا عملية لتصنيف القيم حسب المجموعة، مما يسهّل تفسير وتحليل بياناتك بدقة في مهامك اليومية.
صنّف القيم حسب المجموعة
كود VBA - استخدم ماكرو لأتمتة تصنيف القيم داخل كل مجموعة
صنّف القيم حسب المجموعة
عندما تحتاج إلى تصنيف القيم ضمن مجموعات متميزة—مثل تقييم الطلاب حسب الفصل أو عرض المبيعات حسب المناطق المختلفة—لا يوفّر Excel ميزة مباشرة تُسمى «التصنيف حسب المجموعة». ومع ذلك، يمكن لصيغة مصممة بذكاء أن تنفّذ هذا التصنيف بكفاءة وبدون الحاجة إلى معالجة بيانات إضافية.
لتحقيق ذلك، يمكنك استخدام صيغة مصفوفة تجمع بين اختبارات منطقية ودوال تجميع، ما يمكّنك من مقارنة كل قيمة ضمن مجموعتها المعيّنة فقط، وبالتالي تحصل على الترتيب المطلوب لكل نقطة بيانات.
اتبع الخطوات التالية:
- جهّز بياناتك المجمعة في أعمدة، مثل العمود «المجموعة» (A2:A11) والعمود «القيمة» (B2:B11).
- حدد خلية فارغة مجاورة لبياناتك—عادةً في الصف الأول بجانب قيمك، مثل الخلية C2.
- أدخل الصيغة التالية:
=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1تعمل هذه الصيغة بحساب عدد القيم داخل نفس المجموعة التي تقل عن القيمة الحالية. وفيما يلي معنى كل معلمة:
- ($A$2:$A$11=A2)
→ يتحقّق هذا مما إذا كانت كل خلية في النطاق A2:A11 تساوي القيمة الموجودة في الخلية A2.
→ ويرجع مصفوفة من القيم TRUE/FALSE (أو 1/0) تمثّل ما إذا كان كل صف ينتمي إلى نفس المجموعة التي تنتمي إليها الخلية A2. - (B2<$B$2:$B$11)
→ يتحقق هذا التعبير من عدد القيم في النطاق B2:B11 التي تزيد عن القيمة في الخلية B2.
→ يُرجع TRUE (1) إذا كانت قيمة B2 أقل من قيمة أخرى في النطاق، وFALSE (0) في حال عكس ذلك. - * (الضرب)
→ يجمع هذان الشرطان معًا: - تطابق المجموعة (A2)
القيمة في B2 أصغر من القيم الأخرى
→ لذا سيتم احتساب الصفوف التي تنتمي إلى نفس المجموعة وقيمتها أصغر فقط. - SUMPRODUCT(...)
→ يُجمِع عدد الصفوف التي تستوفي كلا الشرطين. - +1
→ يبدأ الترتيب من 1 (بدلًا من 0)، لذا نضيف 1 إلى عدد القيم الأصغر.
بمجرد إدخال الصيغة في الخلية C2، اسحب مقبض التعبئة لأسفل لتطبيقها على جميع الصفوف ذات الصلة في مجموعة البيانات الخاصة بك. وستقوم الصيغة تلقائيًا بتعديل نفسها لتعمل مع مجموعة وقيمة كل صف، وتحسب الترتيب داخل تلك المجموعة بدقة.
نصائح واحتياطات:
- إذا كان نطاقك واسعًا، فلا تنسَ تحديث مراجع الخلايا وفقًا لذلك.
- للحصول على ترتيب تنازلي (حيث تكون أعلى قيمة هي 1)، غيّر مقارنة الصيغة من
B2<$B$2:$B$11إلىB2>$B$2:$B$11. - لإدارة القيم المكررة، تُعطي هذه الصيغة الترتيب نفسه للقيم المتساوية ضمن المجموعة ذاتها. إذا كنت بحاجة إلى ترتيبات متسلسلة فريدة، ففكّر في استخدام أعمدة مساعدة إضافية.
تتميّز طريقة الصيغة بمرونتها وسهولة تطبيقها على معظم هياكل الجداول المجمعة في Excel. ومع ذلك، قد يتباطأ أداء الحساب في مجموعات البيانات الكبيرة جدًا بسبب اعتمادها على منطق المصفوفات.
كود VBA - استخدم ماكرو لأتمتة تصنيف القيم داخل كل مجموعة
للمستخدمين الراغبين في أتمتة عملية التصنيف أو التعامل مع مجموعات بيانات أكبر بكفاءة، يُعدّ كتابة ماكرو VBA نهجًا قيّمًا. إذ يمكنه أتمتة الخطوات المتكررة، وتقديم مستويات أعلى من التخصيص، ومعالجة البيانات بسرعة تفوق تلك التي توفرها الصيغ المعقدة—مما يجعله الخيار المثالي لإنشاء التقارير المجدولة، أو تنفيذ مهام التصنيف المتكررة، أو عند رغبتك في تجنّب ازدحام ورقة العمل بالصيغ.
قبل المتابعة، تأكد من حفظ عملك وتمكين ماكرو في إعدادات Excel. فيما يلي طريقة كتابة وتشغيل هذا الحل:
- اضغطAlt + F11 لتمكين محرر VBA. في نافذة Microsoft Visual Basic for Applications التي تظهر، انقرإدراج > وحدة نمطية، ثم الصق الكود التالي في الوحدة النمطية المفتوحة:
Sub RankValuesByGroup()
Dim DataRange As Range
Dim GroupRng As Range
Dim ValueRng As Range
Dim OutCol As Range
Dim dictGroups As Object
Dim arrValues, arrRanks
Dim i As Long, j As Long
Dim GroupKey As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the data table range (including group and value columns)", xTitleId, Selection.Address, Type:=8)
If DataRange Is Nothing Then Exit Sub
Set GroupRng = Application.InputBox("Select the group column within your range", xTitleId, DataRange.Columns(1).Address, Type:=8)
Set ValueRng = Application.InputBox("Select the value column to rank within your range", xTitleId, DataRange.Columns(2).Address, Type:=8)
Set OutCol = DataRange.Offset(0, DataRange.Columns.Count).Resize(DataRange.Rows.Count, 1)
OutCol.Cells(1).Value = "RankByGroup"
Set dictGroups = CreateObject("Scripting.Dictionary")
arrValues = ValueRng.Value
arrRanks = ValueRng.Value
' Build group dictionaries for ranking
For i = 2 To UBound(arrValues, 1)
GroupKey = GroupRng.Cells(i, 1).Value
If Not dictGroups.Exists(GroupKey) Then
dictGroups.Add GroupKey, CreateObject("System.Collections.ArrayList")
End If
dictGroups(GroupKey).Add arrValues(i, 1)
Next i
' Rank within each group
For i = 2 To UBound(arrValues, 1)
GroupKey = GroupRng.Cells(i, 1).Value
Dim countLower As Long
countLower = 0
For j = 0 To dictGroups(GroupKey).Count - 1
If dictGroups(GroupKey)(j) < arrValues(i, 1) Then
countLower = countLower + 1
End If
Next j
arrRanks(i, 1) = countLower + 1
Next i
' Output results
For i = 2 To UBound(arrRanks, 1)
OutCol.Cells(i, 1).Value = arrRanks(i, 1)
Next i
MsgBox "Ranking by group completed.", vbInformation, xTitleId
End Sub- انقرتشغيل. سيظهر مربع حوار يطلب منك تحديد نطاق البيانات الكامل، وعمود المجموعة، وعمود القيمة. بعد ذلك، سيولّد الماكرو عمودًا جديدًا يحتوي على ترتيب كل قيمة ضمن مجموعتها.
ملاحظات واستكشاف الأخطاء وإصلاحها:
- تأكد من أن اختيار الأعمدة يتوافق مع بياناتك: يجب أن يتماشى عمود المجموعة وعمود القيمة بشكل مثالي.
- إذا تم تضمين رأس البيانات، فعدّل مؤشر الحلقة الأولية في الكود لضمان الترتيب الصحيح وفقًا لهيكل بياناتك.
- لترتيب القيم تنازليًا، عدّل المقارنة في
If dictGroups(GroupKey)(j) < arrValues(i,1)وفقًا لذلك. - إذا واجهت تحذيرات تتعلق بأذونات الماكرو أو أمانه، فانتقِل إلى إعدادات أمان الماكرو في Excel من خلال: ملف > خيارات > مركز الثقة.
تقدم طريقة VBA مرونةً وأداءً قويًا للتطبيقات الأكثر تقدمًا أو تلك التي تتطلب التوسع، خاصةً عند دمجها في سير عمل إعداد التقارير الآلي.
افتح سحر إكسل مع KUTOOLS AI
- التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية — كل ذلك بأوامر بسيطة!
- الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
- برمجة VBA: اكتب وأَنفِذ أكواد VBA بسلاسة تامة.
- تفسير الصيغ: افهم الصيغ المعقدة بسهولة!
- ترجمة النصوص: اكسر الحواجز اللغوية في جداولك الإلكترونية!
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل