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

نصائح واحتياطات:

• إذا كان نطاقك كبيرًا، تذكر تحديث مراجع الخلايا وفقًا لذلك.
• للتراكيز بترتيب تنازلي (على سبيل المثال، أعلى قيمة هي الأولى)، قم بتعديل الصيغة من B2<$B$2:$B$11 إلى B2>$B$2:$B$11.
• للتعامل مع القيم المكررة، تقوم هذه الصيغة بتعيين نفس الترتيب للقيم المتساوية داخل نفس المجموعة. إذا كنت بحاجة إلى ترتيبات متتابعة وفريدة، ففكر في استخدام أعمدة مساعدة إضافية.

هذه الطريقة القائمة على الصيغ مرنة وتُطبق بسهولة على معظم هياكل الجداول المجمعة في Excel. ومع ذلك، بالنسبة لمجموعات البيانات الكبيرة جدًا، قد يتباطأ أداء الحساب بسبب الاعتماد على منطق الصفوف.

رمز VBA - استخدام ماكرو لأتمتة ترتيب القيم داخل كل مجموعة

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

قبل المتابعة، تأكد من حفظ عملك وتمكين الماكرو في إعدادات Excel. فيما يلي كيفية كتابة وتشغيل هذا الحل:

1. اضغط على Alt + F11 لتمكين محرر VBA. في نافذة Microsoft Visual Basic for Applications التي تظهر، انقر على Insert > Module، ثم ألصق الرمز التالي في الوحدة المفتوحة:

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

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

ملاحظات واستكشاف الأخطاء وإصلاحها:

• تأكد من أن اختيار الأعمدة يطابق بياناتك: يجب أن تتوافق أعمدة المجموعة والقيمة بشكل صحيح.
• إذا كان رأس البيانات مضمنًا، قم بضبط مؤشر الحلقة الأولي في الكود لتحقيق الترتيب الصحيح (حسب هيكل بياناتك).
• لترتيب بترتيب تنازلي، قم بتعديل المقارنة If dictGroups(GroupKey)(j) < arrValues(i,1) وفقًا لذلك.
• إذا واجهت تحذيرات حول إذن أو أمان الماكرو، تحقق من إعدادات أمان الماكرو في Excel تحت File > Options > Trust Center.

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

اكتشف سحر Excel مع Kutools AI

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

عزز قدرات Excel لديك باستخدام أدوات تعمل بالذكاء الاصطناعي. قم بالتنزيل الآن وعش تجربة الكفاءة كما لم يحدث من قبل!