كيفية ترتيب القيم حسب المجموعة في 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 مرونة وأداء قوي لتطبيقات أكثر تقدمًا أو موسعة، خاصة عند دمجه مع سير عمل التقارير الآلية.
اكتشف سحر Excel مع 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 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد