كيفية حساب الوسيط إذا كانت هناك شروط متعددة في Excel؟
حساب الوسيط لمجموعة بيانات في Excel هو عملية مطلوبة بشكل متكرر في تحليل البيانات وإعداد التقارير. بينما يمكن إيجاد الوسيط لمنطقة بسيطة بسرعة باستخدام وظائف Excel القياسية، غالباً ما تنشأ حالات تحتاج فيها إلى قيمة الوسيط فقط من البيانات التي تلبي معايير محددة - على سبيل المثال، إيجاد قيمة مبيعات وسيطة لمنتج معين في تاريخ محدد ضمن مجموعة بيانات كبيرة. التعامل مع مثل هذه العمليات المعقدة والمشروطة باستخدام الوظائف التقليدية فقط يمكن أن يكون صعباً. في هذا البرنامج التعليمي، سنقدم حلولًا عملية متنوعة لحساب الوسيط بشروط متعددة في Excel، واستكشاف كل من الأساليب القائمة على الصيغ والأتمتة باستخدام 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 Sub2. انقر فوق 
(أو اضغط على F5) لتشغيل الكود. سيتم مطالبتك باختيار كل النطاقات المطلوبة وإدخال معاييرك. بعد الانتهاء من المطالبات، ستظهر النتيجة (الوسيط الذي يستوفي جميع الشروط) في الخلية المستهدفة التي حددتها.
يتيح لك هذا الماكرو اختيار النطاق المراد حسابه، نطاقات الشروط، قيم الشروط وأين يتم إخراج النتيجة كل مرة يتم فيها تشغيل الكود. يمكنك أيضًا تعديل الكود بسهولة لتضمين المزيد من الشروط إذا لزم الأمر.
نصائح واستكشاف الأخطاء وإصلاحها: عند استخدام حلول VBA، تأكد من أن جميع النطاقات المحددة لها نفس الطول، وتتطابق المعايير مع نوع البيانات الصحيح والتنسيق (مثل النص مقابل التواريخ). إذا لم تكن هناك قيمة تلبي المعايير، سيعرض الإخراج "لا توجد مطابقة." لضمان أفضل استقرار، احفظ مصنفك قبل تشغيل الماكرو وقم دائمًا بتمكين الماكرو عندما يُطلب منك ذلك. هذا الحل باستخدام VBA مناسب للمستخدمين الذين يعرفون إعدادات أمان الماكرو وللاستخدام في سير عمل Excel المُؤتمتة.
باختصار، يُساعد النهج القائم على 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 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد