كيف تحسب الوسيط في Excel عند تطبيق عدة شروط؟
يُعد حساب الوسيط لمجموعة بيانات في Excel عمليةً شائعةً وضروريةً في تحليل البيانات وإعداد التقارير. فبينما يُمكنك بسهولة حساب الوسيط لنطاق بسيط باستخدام دوال Excel القياسية، غالبًا ما تواجه سيناريوهات تتطلب استخراج قيمة الوسيط فقط من البيانات التي تستوفي مجموعةً من المعايير المحددة—مثل العثور على متوسط مبيعات وسيط لمنتج معين في تاريخ معيّن ضمن مجموعة بيانات ضخمة. وقد يصبح التعامل مع هذه العمليات الشرطية المعقدة باستخدام الدوال التقليدية وحدها أمرًا بالغ الصعوبة. في هذا البرنامج التعليمي، سنقدّم حلولًا عمليةً متنوعةً لحساب الوسيط وفق شروط متعددة في Excel، مستعرضين كلًا من الأساليب القائمة على الصيغ المتقدمة وأتمتة المهام باستخدام VBA لتلبية الاحتياجات الأكثر تعقيدًا.
احسب الوسيط إذا استوفى شروطًا متعددة
افترض أن لديك نطاق البيانات الموضح أدناه، ومهمتك هي تحديد القيمة الوسيطة التي تستوفي معيارين معًا: على سبيل المثال، إيجاد القيمة الوسيطة في العمود B حيث يحتوي العمود A على القيمة «a» ويحتوي العمود C على التاريخ "2-Jan". يُعدّ هذا السيناريو شائعًا جدًّا في تقارير المبيعات، ونتائج اختبارات الفصول الدراسية،以及其他 تحليلات البيانات التجارية أو الأكاديمية التي تتطلب التصفية حسب فئات متعددة.
للتوضيح، دعنا نُعد ورقة العمل على النحو التالي: في ملف 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 إلى النطاق الذي يحتوي على القيم الرقمية التي تريد حساب وسيطها. عدّل هذه النطاقات حسب احتياجاتك في ورقة العمل الخاصة بك، واستخدم دائمًا المراجع المطلقة (باستخدام رمز $) لضمان بقاء النطاقات ثابتة عند نسخ الصيغة.
احتياطات:إذا لم تستوفِ أيٌّ من القيم كلا الشرطين، فستُرجع الصيغة خطأً من نوع#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، تأكد من أن جميع نطاقات التحديد لديك لها نفس الطول، وأن المعايير تتطابق بدقة مع نوع البيانات والتنسيق الصحيحين (مثل النص مقابل التواريخ). إذا لم تُطابق أي قيمة المعايير المحددة، فسيعرض الإخراج «No match.» ولأفضل استقرار ممكن، احفظ ملفك قبل تشغيل الماكرو، وفعّل الماكروات دائمًا عندما يُطلب منك ذلك. تم تصميم حل 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل