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

كيف تحسب متوسط الخلايا غير المتتالية مع استبعاد القيم الصفرية في Excel؟

المؤلفSiluviaتاريخ التعديل

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

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

لقطة شاشة لجدول Excel المستخدم لحساب متوسط الخلايا غير المجاورة باستثناء الأصفار

احسب متوسط الخلايا غير المتتالية باستثناء الأصفار باستخدام صيغة

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

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

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

=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))

ثم اضغط على مفاتيحCtrl + Shift + Enterمعًا (لإصدارات Excel القديمة؛ أما في Excel 365 أو 2019، فيكفي الضغط على)Enter لأنها تدعم المصفوفات الديناميكية).

لقطة شاشة تُظهر الصيغة لحساب متوسط الخلايا غير المجاورة في Excel

ملاحظة: في الصيغة، تمثّل C2 وG2 الخلية الأولى والأخيرة في نطاقك المستهدف. والرقم "2" يُحدّد عدد الأعمدة الفاصلة بين الخلايا غير المتتالية؛ غيّر هذه القيمة إذا كانت خلاياك مفصولة بعدد مختلف من الأعمدة. عدّل النطاق (C2:G2) والفاصل (2) بما يتناسب مع تخطيط بياناتك.

نصائح:

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

احسب متوسط الخلايا غير المتتالية باستثناء الأصفار باستخدام ماكرو VBA

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

هذه الطريقة ممتازة لـ:

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

1. انقر علىالمطوّر > Visual Basic؛ في النافذة التي تفتح، انقر علىإدراج > وحدة نمطية، ثم الصق الكود التالي في الوحدة النمطية:

Sub AverageNonAdjacentExcludeZero()
    Dim rng As Range
    Dim cell As Range
    Dim SumVal As Double
    Dim CountVal As Long
    Dim SelectedRng As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set SelectedRng = Application.Selection
    Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
    
    On Error Resume Next
    SumVal = 0
    CountVal = 0
    
    For Each cell In SelectedRng
        If IsNumeric(cell.Value) And cell.Value <> 0 Then
            SumVal = SumVal + cell.Value
            CountVal = CountVal + 1
        End If
    Next
    
    If CountVal > 0 Then
        MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
    Else
        MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
    End If
End Sub

2. انقر على زرزر التشغيل التشغيل، أو اضغط علىF5 في محرر VBA. ستظهر نافذة منبثقة تطلب منك تحديد الخلايا الخاصة بك. استخدم الماوس مع مفتاحCtrl للنقر وتحديد الخلايا غير المتتالية التي ترغب في حساب متوسطها. بعد ذلك، انقر علىموافق، وسيعرض الماكرو النتيجة مع استبعاد الأصفار.

احسب متوسط الخلايا غير المتتالية باستثناء الأصفار باستخدام دالة مضمنة في Excel

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

استخدم شريط الحالة:

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

عند اختيار الطريقة الأنسب، خذ بعين الاعتبار حجم بياناتك ومدى تشتت الخلايا المحددة، وكذلك ما إذا كنت بحاجة إلى نتيجة صيغة قابلة للتكرار أو مجرد فحص بصري سريع. وإذا واجهت أخطاءً (مثل)#DIV/0!)، فتأكد من وجود قيمة واحدة على الأقل غير صفرية ضمن تحديداتك. وعند استخدام VBA، احفظ دائمًا ملف عملك قبل تشغيل ماكرو جديد لتجنب فقدان البيانات عن طريق الخطأ.

حدد بسرعة صفوفًا أو أعمدة غير متتالية (بفترات منتظمة) في نطاق محدود في Excel:

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

لقطة شاشة لأداة Kutools for Excel تحديد صفوف وأعمدة الفواصل

Kutools لـ Excel- عزِّز Excel بقوة أكثر من 300 أداة أساسية، لتجعل عملك أسرع وأسهل، واستفد من ميزات الذكاء الاصطناعي لمعالجة البيانات بشكل أكثر ذكاءً وزيادة الإنتاجية.احصل عليه الآن

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

🤖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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
  • أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل