KutoolsforOffice — حل واحد، خمس أدوات قوية.تحقيق المزيد بجهد أقل.

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

المؤلفصنتاريخ التعديل

في العديد من مهام تحليل البيانات في Excel، يُعد حساب الوسيط بدقة أمرًا بالغ الأهمية لفهم الاتجاه المركزي لمجموعة البيانات الخاصة بك. ومع ذلك، قد تحتوي مجموعة البيانات أحيانًا على أصفار أو قيم أخطاء (مثل)#DIV/0! و#N/A، إلخ)، والتي يمكن أن تعوق حساب الوسيط مباشرةً. فعلى سبيل المثال، يؤدي استخدام الصيغة القياسية=MEDIAN(range) إلى تضمين الأصفار في الحساب، كما أنها تُرجع خطأً فور وجود أي خلية غير صالحة في النطاق—مما قد يؤدي إلى نتائج مضلِّلة أو فشلٍ كامل في إتمام الحساب، كما هو موضح أدناه.
لقطة شاشة توضح الحالة التي يلزم فيها حساب الوسيط مع تضمين الأصفار والأخطاء في نطاق البيانات

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

الوسيط مع تجاهل الأصفار

الوسيط مع تجاهل الأخطاء

VBA: وسيط مع تجاهل الأصفار والأخطاء (دالة معرّفة من قبل المستخدم)

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

سهم أزرق فقاعة جهة اليمينالوسيط مع تجاهل الأصفار

عندما يحتوي نطاقك على أصفار لا ترغب في أخذها بعين الاعتبار عند حساب الوسيط—مثل القيم المفقودة التي تمثَّل بالرقم 0—يمكنك استخدام صيغة مصفوفة لاستبعاد هذه الأصفار. ويُعدّ هذا مفيدًا بشكل خاص في مجموعات البيانات التي تمثّل فيها الأصفار بيانات غير متوفرة، وليس قياسات فعلية.

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

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

بعد إدخال الصيغة، بدلًا من الضغط على Enter فقط، اضغط علىCtrl + Shift + Enter لجعلها صيغة مصفوفة (سترى أقواسًا معقوفة تظهر حول الصيغة في شريط الصيغة). يضمن ذلك أن القيم غير الصفرية فيA2:A17 فقط هي التي تُؤخذ في الاعتبار لحساب الوسيط. انظر لقطة الشاشة:
لقطة شاشة توضح كيفية تطبيق صيغة الوسيط في Excel مع تجاهل الأصفار

نصائح:

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

سهم أزرق فقاعة جهة اليمينالوسيط مع تجاهل الأخطاء

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

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

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

بعد إدخال الصيغة، اضغط علىCtrl + Shift + Enter (ما لم تكن تستخدم Excel 365/Excel 2021 أو إصدارًا أحدث يدعم المصفوفات الديناميكية). تضمّن هذه الصيغة القيم الموجودة فيF2:F17 التي تكون أرقامًا حقيقية فقط—مع تجاهل خلايا الأخطاء بالكامل.
لقطة شاشة توضح كيفية تطبيق صيغة الوسيط في Excel مع تجاهل الأخطاء

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

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

سهم أزرق فقاعة جهة اليمين VBA: وسيط مع تجاهل الأصفار والأخطاء (دالة معرّفة من قبل المستخدم)

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

كيفية إعداد الدالة المخصصة (UDF):

  1. انقر على علامة التبويبالمطورفي Excel. وإذا لم تكن ظاهرة، فقم بتمكينها من خلالملف > خيارات > تخصيص الشريط.
  2. انقر علىVisual Basic لفتحمحرر VBA.
  3. في محرر VBA، انقر علىإدراج > وحدة نمطيةلإنشاء وحدة جديدة.
  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 الخاصة بك.

سهم أزرق فقاعة جهة اليمين Power Query: وسيط بعد تصفية الأصفار/الأخطاء

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

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

  1. حدد أي خلية داخل نطاق البيانات الخاص بك، ثم انتقل إلى علامة التبويبالبياناتوانقر علىمن جدول/نطاق. إذا لم تكن بياناتك بتنسيق جدول بعد، فسيطلب منك Excel إنشاء جدول—فقط انقر على «موافق».
  2. سيتم فتح نافذة محرر Power Query. انقر على السهم المنسدل للعمود ذي الصلة، ثم ألغِ تحديد0لتصفية القيم الصفرية. (لتصفية الأخطاء، انقر بزر الماوس الأيمن على رأس العمود، واختر)إزالة الأخطاء.)
  3. بعد التصفية، انقر علىالصفحة الرئيسية > إغلاق وتحميللإرسال البيانات المُنظَّفة مرة أخرى إلى ورقة العمل الخاصة بك.
  4. الآن، طبِّق الصيغة القياسية=MEDIAN() على العمود الذي يحتوي القيم المُصفاة فقط، لأن البيانات الآن تستبعد جميع العناصر غير المرغوب فيها.

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

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

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

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

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

🤖KUTOOLS AI مساعد: ثوّر تحليل البيانات استنادًا إلى:التنفيذ الذكي   |  إنشاء الكود|  إنشاء الصيغ المخصصة  |  تحليل البيانات وإنشاء المخططات البيانية|  استدعاء الوظائف المحسنة…
الميزات الشائعة:البحث، التمييز أو وضع علامة على المكررات   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |  التقريب بدون استخدام الصيغة...
بحث متقدم:VLookup متعدد المعايير  |  VLookup متعدد القيم  |   VLookup عبر أوراق متعددة   |   مطابقة غامضة....
قائمة منسدلة متقدمة:إنشاء قائمة منسدلة بسرعة   |  قائمة منسدلة تابعة   |  قائمة منسدلة متعددة الاختيار....
مدير الأعمدة:إضافة عدد محدد من الأعمدة|نقل الأعمدة|تبديل حالة ظهور الأعمدة المخفية|مقارنة النطاقات والأعمدة...
ميزات مميزة:التركيز على الشبكة   |  عرض التصميم   |شريط الصيغ المحسن   | مدير الدفتر والورقة   |  مكتبة الموارد(نص تلقائي)|  أداة اختيار التاريخ   |  تجميع ورقات العمل  |  تشفير/فك تشفير الخلايا   | إرسال رسائل البريد الإلكتروني حسب القائمة   |  مرشح متقدم   |   تصفية خاصة(تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...) ...
أفضل 15 مجموعات الأدوات:12 أدواتالنصوص(إضافة نص،حذف الأحرف المحددة، ...)|   50+أنواعالمخططات البيانية(مخطط جانت، ...)|   40+ صيغعملية(حساب العمر بناءً على تاريخ الميلاد، ...)|   19 أدواتالإدراج(إدراج رمز QR،إدراج صورة من المسار، ...)|   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.

ExcelWordOutlookTabsPowerPoint
  • حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
  • برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
  • يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
  • تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
  • أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل