Skip to main content

Kutools for Office — مجموعة واحدة. خمسة أدوات. أنجز المزيد.

كيفية حساب القيم الوسيطية مع تجاهل الأصفار أو الأخطاء في Excel؟

Author Sun Last modified

في العديد من مهام تحليل البيانات في Excel، يعد حساب الوسيط بدقة أمرًا ضروريًا لفهم النزعة المركزية لمجموعة البيانات الخاصة بك. ومع ذلك، قد تحتوي مجموعة البيانات أحيانًا على أصفار أو قيم خطأ (مثل #DIV/0!, #N/A، إلخ)، مما قد يعوق عملية حساب الوسيط بشكل مباشر. على سبيل المثال، عند استخدام الصيغة القياسية =MEDIAN(range) سيتم تضمين الأصفار في الحساب وستظهر رسالة خطأ إذا كانت هناك خلايا غير صالحة موجودة في النطاق، مما قد يؤدي إلى نتائج مضللة أو فشل في الحساب كما هو موضح أدناه.
A screenshot showing when calculating the median with zeros and errors included in the data range is needed

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

الوسيط باستثناء الأصفار

الوسيط باستثناء الأخطاء

VBA: الوسيط باستثناء الأصفار والأخطاء (UDF)

Power Query: الوسيط بعد تصفية الأصفار/الأخطاء

arrow blue right bubble الوسيط باستثناء الأصفار

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

حدد الخلية التي تريد عرض الوسيط فيها (على سبيل المثال، C2) وأدخل الصيغة التالية:

=MEDIAN(IF(A2:A17<>0,A2:A17))

بعد إدخال الصيغة، بدلاً من الضغط على Enter فقط، اضغط Ctrl + Shift + Enter لجعلها صيغة صفيف (سترى ظهور الأقواس المتعرجة حول الصيغة في شريط الصيغ). هذا يضمن اعتبار القيم غير الصفرية فقط الموجودة في A2:A17 لحساب الوسيط. انظر اللقطة:
A screenshot showing how to apply the median formula in Excel while ignoring zeros

نصائح:

  • إذا كنت تستخدم Excel 365 أو Excel 2021 وما فوقهما، فإن الضغط على Enter فقط كافٍ بفضل دعم الصفائف الديناميكية.
  • تأكد من وجود قيمة عددية غير صفرية واحدة على الأقل في النطاق، وإلا ستعيد الصيغة خطأ #NUM!.
  • هذا الحل مثالي لتنظيف استجابات الاستبيانات أو تقارير النفقات أو بيانات المبيعات حيث يجب استبعاد الأصفار من التحليل.

arrow blue right bubble الوسيط باستثناء الأخطاء

قيم الخطأ مثل #N/A، #DIV/0!، أو #VALUE! يمكن أن تسبب في جعل دالة الوسيط القياسية تعيد خطأ، مما يوقف تحليل البيانات. لحساب الوسيط بأمان باستثناء هذه الأخطاء، يمكنك استخدام صيغة الصفائف التالية.

حدد أي خلية ترغب في عرض نتيجتك فيها، وأدخل الصيغة أدناه:

=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))

بعد إدخال الصيغة، اضغط Ctrl + Shift + Enter (ما لم تكن تستخدم Excel 365 / Excel 2021 أو أعلى، والذي يدعم الصفائف الديناميكية). هذه الصيغة تتضمن فقط تلك القيم الموجودة في F2:F17 والتي هي أرقام حقيقية – تجاهل أي خلايا تحتوي على أخطاء تمامًا.
A screenshot showing how to apply the median formula in Excel while ignoring errors

نصائح وتحذيرات:

  • إذا كانت جميع الخلايا تحتوي على قيم خطأ، فإن النتيجة ستعيد خطأ #NUM! – تأكد من أن بياناتك تحتوي على رقم صالح واحد على الأقل.
  • يمكنك الجمع بين معايير الاستبعاد (على سبيل المثال، استبعاد كل من الأصفار والأخطاء) عن طريق تداخل الشروط.
  • هذه الصيغة مفيدة بشكل خاص عند العمل مع بيانات مستوردة أو نتائج الاستبيانات أو البيانات المالية التي قد تحتوي على حسابات جزئية أو فاشلة.

arrow blue right bubble VBA: الوسيط باستثناء الأصفار والأخطاء (UDF)

بالنسبة للسيناريوهات التي تحتاج فيها بشكل متكرر إلى حساب الوسيط مع تجاهل كل من الأصفار والأخطاء، أو إذا كنت بحاجة إلى حل يتجنب كتابة صيغ الصفائف يدويًا، يمكنك استخدام دالة VBA مخصصة (User-Defined Function, UDF). يقدم هذا النهج مرونة إضافية لأن الدالة المخصصة يمكن أن تشمل جميع معايير الاستبعاد ويتم استخدامها مثل أي صيغة مضمنة، مما يجعلها مناسبة لمجموعات البيانات الكبيرة أو التي يتم تحديثها بشكل متكرر.

كيفية إعداد UDF:

  1. اضغط على تبويب Developer في Excel. إذا لم يكن متاحًا، يمكنك تفعيله عبر File > Options > Customize Ribbon.
  2. انقر على Visual Basic لفتح محرر VBA.
  3. في محرر VBA، اضغط على Insert > Module لإنشاء وحدة جديدة.
  4. انسخ والصق الكود التالي في الوحدة:
Function MedianIgnoreZeroError(rng As Range) As Variant
    Dim cell As Range
    Dim tempList() As Double
    Dim count As Integer
    
    count = 0
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            If cell.Value <> 0 And Not IsError(cell.Value) Then
                count = count + 1
                ReDim Preserve tempList(1 To count)
                tempList(count) = cell.Value
            End If
        End If
    Next cell
    
    On Error GoTo 0
    
    If count = 0 Then
        MedianIgnoreZeroError = CVErr(xlErrNum)
    Else
        MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
    End If
End Function

كيفية استخدام UDF:
بعد العودة إلى Excel، أدخل ببساطة الصيغة =MedianIgnoreZeroError(A2:A17) في أي خلية (استبدل A2:A17 بنطاقك المستهدف). على عكس صيغ الصفائف، تحتاج فقط إلى الضغط على Enter – ليس هناك حاجة لـ Ctrl + Shift + Enter.

  • يعمل هذا الأسلوب بشكل جيد مع مجموعات البيانات الكبيرة للغاية، ويتجنب عيوب صيغ الصفائف ويمكن تعديله لتجاهل القيم غير المرغوب فيها الأخرى عن طريق تحرير الكود أكثر.
  • إذا كان النطاق يحتوي فقط على أصفار أو أخطاء، ستعطي النتيجة خطأ #NUM!
  • إذا تلقيت خطأ #NAME?، تحقق من أن ماكرو VBA مثبت بشكل صحيح وأن الماكرو ممكّن في إعدادات Excel الخاصة بك.

arrow blue right bubble Power Query: الوسيط بعد تصفية الأصفار/الأخطاء

Power Query هو أداة قوية في Excel لاستيراد وتحويل وتحليل البيانات – خاصة عندما يكون هدفك هو تنظيف ومعالجة مجموعات البيانات الكبيرة قبل إجراء العمليات الحسابية مثل الوسيط. باستخدام Power Query، يمكنك بسهولة تصفية الأصفار والأخطاء، مما يضمن أن القيم الرقمية الصحيحة فقط ستبقى في الحساب. هذا الأسلوب مفيد بشكل خاص إذا كانت بيانات المصدر يتم تحديثها بانتظام أو استيرادها من أنظمة خارجية.

خطوات استخدام Power Query لحساب الوسيط مع تجاهل الأصفار والأخطاء:

  1. حدد أي خلية ضمن نطاق البيانات الخاص بك، ثم انتقل إلى تبويب Data وانقر على From Table/Range. إذا لم تكن بياناتك في شكل جدول بالفعل، سيطلب منك Excel إنشاء جدول – انقر على OK.
  2. سيفتح نافذة محرر Power Query. انقر على السهم المنسدل للمستوى ذو الصلة وأزل التأشير عن 0 لتصفية القيم الصفرية. (لتصفية الأخطاء، انقر بزر الماوس الأيمن على رأس العمود واختر Remove Errors.)
  3. بمجرد التصفية، انقر على Home > Close & Load لإرسال البيانات النظيفة مرة أخرى إلى ورقة عملك.
  4. الآن، قم بتطبيق الصيغة القياسية =MEDIAN() على العمود الذي يحتوي على القيم المُصفاة فقط، حيث إن البيانات الآن تستبعد كل العناصر غير المرغوب فيها.

يضمن هذا الأسلوب أن تبقى بياناتك الأصلية دون تغيير، ويوفر قابلية تكرار قوية مع بيانات جديدة أو محدثة، وهو فعال بشكل خاص للمهام التقريرية المتكررة أو عند العمل مع مجموعات بيانات كبيرة أو خارجية. يمكن تحديث سير عمل Power Query بنقرة واحدة فقط كلما تغيرت بيانات المصدر، مما يقلل من التدخل اليدوي وخطر الأخطاء.

  • Power Query متاح في Excel 2016 والإصدارات الأحدث (أو كإضافة لـ Excel 2010 و2013).
  • بعد التحويل، يمكن إجراء العمليات الحسابية على البيانات النظيفة الناتجة، مما يوفر موثوقية أكبر للتحليلات اللاحقة.

إذا حدثت نتائج غير متوقعة، قم بمراجعة خطوات التصفية الخاصة بك في Power Query وتأكد من وجود قيم عددية صالحة متبقية في بياناتك النظيفة.

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

أفضل أدوات الإنتاجية لمكتب العمل

🤖 Kutools AI Aide: أحدث ثورة في تحليل البيانات اعتمادًا على: التنفيذ الذكي |  توليد الأكواد |  إنشاء الصيغ المخصصة |  تحليل البيانات وتوليد الرسوم البيانية |  استخدام Kutools Functions...
ميزات شائعة: البحث، تمييز أو وضع علامة على المكررات | حذف الصفوف الفارغة | دمج الأعمدة أو الخلايا بدون فقدان البيانات | تقريب بدون الصيغة...
بحث متقدم: بحث بمعايير متعددة VLookup | بحث بقيم متعددة VLookup | بحث في عدة ورقات VLookup | مطابقة غامضة...
قائمة منسدلة متقدمة: إنشاء قائمة منسدلة بسرعة | قائمة منسدلة معتمدة | قائمة منسدلة متعددة الاختيارات...
مدير الأعمدة: إضافة عدد محدد من الأعمدة | نقل الأعمدة | تبديل حالة إظهار الأعمدة المخفية | مقارنة النطاقات والأعمدة...
ميزات مميزة: التركيز على الشبكة | عرض التصميم | شريط الصيغ المحسن | مدير أدوات المصنف وورقة العمل | مكتبة النص التلقائي | منتقي التاريخ | دمج البيانات | تشفير/فك تشفير الخلايا | إرسال البريد الإلكتروني حسب الجدول | مرشح متقدم | تصفية خاصة (تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...)...
أفضل15 مجموعة أدوات:12 أداة نصية (إضافة نص، حذف الأحرف المحددة، ...) | أكثر من50 نوع رسم بياني (مخطط جانت، ...) | أكثر من40 صيغة عملية (حساب العمر بناءً على تاريخ الميلاد، ...) |19 أداة إدراج (إدراج رمز الاستجابة السريعة، إدراج صورة من المسار، ...) |12 أداة تحويل (تحويل إلى كلمات، تحويل العملة، ...) |7 أدوات دمج وتقسيم (دمج متقدم للصفوف، تقسيم الخلايا، ...) | ... والمزيد
استخدم Kutools بلغتك المفضلة – يدعم الإنجليزية، الإسبانية، الألمانية، الفرنسية، الصينية، وأكثر من40 لغة أخرى!

عزز مهاراتك في 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 Tabs PowerPoint
  • حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
  • مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
  • الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
  • تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
  • قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد