كيفية حساب الوسيط إذا كانت هناك شروط متعددة في Excel؟

حساب الوسيط لمجموعة بيانات في Excel هو عملية مطلوبة بشكل متكرر في تحليل البيانات وإعداد التقارير. بينما يمكن إيجاد الوسيط لمنطقة بسيطة بسرعة باستخدام وظائف Excel القياسية، غالباً ما تنشأ حالات تحتاج فيها إلى قيمة الوسيط فقط من البيانات التي تلبي معايير محددة - على سبيل المثال، إيجاد قيمة مبيعات وسيطة لمنتج معين في تاريخ محدد ضمن مجموعة بيانات كبيرة. التعامل مع مثل هذه العمليات المعقدة والمشروطة باستخدام الوظائف التقليدية فقط يمكن أن يكون صعباً. في هذا البرنامج التعليمي، سنقدم حلولًا عملية متنوعة لحساب الوسيط بشروط متعددة في Excel، واستكشاف كل من الأساليب القائمة على الصيغ والأتمتة باستخدام VBA للاحتياجات المتقدمة.

• احسب الوسيط إذا استوفى شروطاً متعددة
• رمز VBA - احسب الوسيط بشروط متعددة

احسب الوسيط إذا استوفى شروطاً متعددة

لنفترض أن لديك نطاق بيانات كما هو موضح أدناه، ومهمتك هي تحديد قيمة الوسيط التي تلبي معيارين: على سبيل المثال، تحديد قيمة الوسيط للعمود B حيث يكون العمود A يحتوي على القيمة "a" والعمود C يحتوي على التاريخ "2-يناير". هذا السيناريو شائع بشكل خاص في تقارير المبيعات، نتائج الاختبارات الصفية، وتحليل البيانات التجارية أو الأكاديمية الأخرى حيث يكون التصفية حسب فئات متعددة ضرورية.

للوضوح، دعنا نجهّز ورقة العمل كما يلي: في ورقة Excel الخاصة بك، أدخل شروطك وأنشئ تصميماً مشابهاً للصورة أدناه. هنا، يسرد العمود E معايير العمود A، وصف 1 من الأعمدة F وما بعدها يمثل معايير التاريخ من العمود C.

لحساب الوسيط الذي يستوفي شروطاً متعددة، يمكنك استخدام صيغة صفيف تستفيد من دالتي MEDIAN و IF لبناء قائمة مرشحة من القيم بناءً على شروطك. إليك الطريقة:

1. انقر فوق الخلية F2، حيث تريد أن يظهر نتيجة الوسيط، وأدخل الصيغة التالية:

=MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12)))

تعمل هذه الصيغة عن طريق التحقق، لكل صف، مما إذا كانت القيمة في العمود A تتطابق مع الشرط في E2 وما إذا كانت القيمة في العمود C تتطابق مع العنوان في F1. إذا تم استيفاء كلا الشرطين، فإنه يجمع القيمة في العمود B لحساب الوسيط.

2. بعد إدخال الصيغة، اضغط على Ctrl + Shift + Enter (وليس فقط Enter)، لأن هذه صيغة صفيف. سيحيط Excel الصيغة تلقائيًا بأقواس { } للإشارة إلى أنها صيغة صفيف.

3. اسحب مقبض التعبئة من الزاوية اليمنى السفلى لـ F2 لنسخ الصيغة عبر الخلايا الأخرى ذات الصلة حيث تحتاج إلى وسيط تحت شروط مختلفة، كما هو موضح أدناه:

شرح المعلمات ونصائح الاستخدام: في الصيغة، $A$2:$A$12 هو النطاق الذي يحتوي على الشرط الأول (مثل أسماء المنتجات)، $C$2:$C$12 هو النطاق الخاص بالشرط الثاني (مثل التواريخ)، و$B$2:$B$12 هو النطاق الذي يحتوي على القيم الرقمية التي تريد الوسيط لها. قم بتعديل هذه النطاقات حسب الحاجة لورقة العمل الخاصة بك. استخدم دائمًا المراجع المطلقة ($ symbols) لضمان عدم تغيير النطاقات عند نسخ الصيغة.

احتياطات: إذا لم تكن هناك قيم تلبي كلا الشرطين، ستعيد الصيغة خطأ #NUM!. لتجنب الالتباس، يمكنك وضع الصيغة داخل IFERROR لإرجاع خلية فارغة أو رسالة مخصصة:

=IFERROR(MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12))),"No match")

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

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

رمز VBA - احسب الوسيط بشروط متعددة

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

اتبع هذه الخطوات لاستخدام VBA لحساب الوسيط المشروط:

1. انقر فوق أدوات المطور > Visual Basic. ستُفتح نافذة جديدة Microsoft Visual Basic for Applications. انقر فوق إدراج > وحدة، ثم ألصق الكود التالي في الوحدة:

Sub ConditionalMedian()
    Dim DataRange As Range
    Dim CriteriaRange1 As Range
    Dim CriteriaRange2 As Range
    Dim OutputRange As Range
    Dim Criteria1 As Variant
    Dim Criteria2 As Variant
    Dim TempArr() As Double
    Dim i As Long
    Dim j As Long
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the range containing median values (e.g., B2:B12):", xTitleId, "", Type:=8)
    Set CriteriaRange1 = Application.InputBox("Select the first criteria range (e.g., A2:A12):", xTitleId, "", Type:=8)
    Criteria1 = Application.InputBox("Enter the first criteria value (e.g., a):", xTitleId, "", Type:=2)
    Set CriteriaRange2 = Application.InputBox("Select the second criteria range (e.g., C2:C12):", xTitleId, "", Type:=8)
    Criteria2 = Application.InputBox("Enter the second criteria value (e.g.,2-Jan):", xTitleId, "", Type:=2)
    Set OutputRange = Application.InputBox("Select the cell to output the result:", xTitleId, "", Type:=8)
    
    count = 0
    For i = 1 To DataRange.Rows.count
        If StrComp(CStr(CriteriaRange1.Cells(i, 1).Value), CStr(Criteria1), vbTextCompare) = 0 And _
           CStr(CriteriaRange2.Cells(i, 1).Value) = CStr(Criteria2) Then
            ReDim Preserve TempArr(count)
            TempArr(count) = DataRange.Cells(i, 1).Value
            count = count + 1
        End If
    Next i
    
    If count = 0 Then
        OutputRange.Value = "No match"
    Else
        Call QuickSort(TempArr, LBound(TempArr), UBound(TempArr))
        If count Mod 2 = 1 Then
            OutputRange.Value = TempArr(count \ 2)
        Else
            OutputRange.Value = (TempArr(count \ 2) + TempArr(count \ 2 - 1)) / 2
        End If
    End If
End Sub

Sub QuickSort(arr() As Double, first As Long, last As Long)
    Dim i As Long
    Dim j As Long
    Dim pivot As Double
    Dim temp As Double
    
    i = first
    j = last
    pivot = arr((first + last) \ 2)
    
    Do While i <= j
        Do While arr(i) < pivot
            i = i + 1
        Loop
        
        Do While arr(j) > pivot
            j = j - 1
        Loop
        
        If i <= j Then
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
            i = i + 1
            j = j - 1
        End If
    Loop
    
    If first < j Then
        QuickSort arr, first, j
    End If
    
    If i < last Then
        QuickSort arr, i, last
    End If
End Sub

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

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

نصائح واستكشاف الأخطاء وإصلاحها: عند استخدام حلول VBA، تأكد من أن جميع النطاقات المحددة لها نفس الطول، وتتطابق المعايير مع نوع البيانات الصحيح والتنسيق (مثل النص مقابل التواريخ). إذا لم تكن هناك قيمة تلبي المعايير، سيعرض الإخراج "لا توجد مطابقة." لضمان أفضل استقرار، احفظ مصنفك قبل تشغيل الماكرو وقم دائمًا بتمكين الماكرو عندما يُطلب منك ذلك. هذا الحل باستخدام VBA مناسب للمستخدمين الذين يعرفون إعدادات أمان الماكرو وللاستخدام في سير عمل Excel المُؤتمتة.

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

مقالات ذات صلة:

• احسب الوسيط في النطاق