كيف تحسب متوسطًا دقيقًا عند وجود قيم مفقودة في Excel؟
عند العمل مع البيانات في Excel، من الشائع مواجهة نطاقات تحتوي على قيم مفقودة أو خلايا مليئة بمؤشرات أخطاء مثل#N/A أو#DIV/0!. إذا استخدمت الدالةAVERAGE القياسية في Excel على مثل هذا النطاق، فستُرجع الصيغة خطأً بدلاً من تجاهل الخلايا المشكلة. وهذا يعني أن حساباتك قد تصبح غير صالحة أو غير قابلة للاستخدام كلما كانت بياناتك غير مكتملة أو احتوت على أخطاء، كما هو موضح في لقطة الشاشة أدناه. ومن الأهمية بمكان أن تكون قادرًا على حساب المتوسط بدقة مع تخطي هذه القيم المفقودة أو الخلايا التي تحتوي على أخطاء، خاصةً في التقارير التجارية أو الأبحاث الأكاديمية أو أي سيناريو يتضمن مجموعات بيانات كبيرة ومعقدة. وفيما يلي ستجد حلولًا عملية وتعليمات خطوة بخطوة لحساب المتوسط الصحيح في مثل هذه الحالات، مما يضمن سلامة بياناتك في تحليل البيانات.
حساب المتوسط مع وجود قيمة مفقودة
لحساب المتوسط في Excel مع تخطي القيم المفقودة أو الخلايا التي تحتوي على أخطاء، يمكنك استخدام صيغ ذكية تتجاهل تلقائيًا الخلايا غير الرقمية وتلك التي تحتوي على أخطاء. ويُعدّ هذا ضروريًا في سيناريوهات مثل تحليل نتائج الاستبيانات (حيث قد تكون بعض الإدخالات غير مكتملة)، أو بيانات المبيعات عبر الزمن (مع أشهر فارغة)، أو تسجيل البيانات العلمية (حيث قد تكون بعض القياسات مفقودة أو غير صالحة). فيما يلي حَلّان موثوقان باستخدام الصيغ، مع تفاصيل كلٍّ منهما.
الخيار 1: دالة AGGREGATE
دالةAGGREGATE مثالية خصوصًا للحالات التي تتوقّع فيها وجود أخطاء أو قيم مخفية في نطاق البيانات، لأنها تتيح لك تحديد أنواع البيانات التي يجب تجاهلها. وهذا يُعدّ مفيدًا جدًّا عند توحيد البيانات من مصادر متعددة أو عند تحديث مجموعة البيانات بشكل متكرر بإدخالات غير متسقة.
1. حدد الخلية التي تريد عرض متوسط النتائج فيها، ثم أدخل الصيغة التالية:
=AGGREGATE(1,6,A2:C2)هنا، يُحدِّد1 عملية حساب المتوسط، ويخبر6 Excel بتجاهل قيم الأخطاء في النطاق المحددA2:C2.
2. بعد إدخال الصيغة، اضغطShift + Ctrl + Enter (لإصدارات Excel القديمة)، أو اضغط ببساطة علىEnter إذا كنت تستخدم Excel 365 أو Excel 2019، لأن هذين الإصدارين يدعمان المصفوفات الديناميكية دون الحاجة إلى ضغطات مفاتيح إضافية.
المزايا:سهلة الاستخدام؛ تتجاهل الأخطاء تلقائيًا وتعيد نتائج دقيقة، وتعمل بكفاءة مع النطاقات التي تحتوي على قيم خطأ.
القيود:متوفرة فقط في Excel 2010 والإصدارات الأحدث. ولا تدعم إصدارات Excel القديمة دالةAGGREGATE.
الخيار 2: صيغة مصفوفة AVERAGE مع IF وISNUMBER
بدلاً من ذلك، يمكنك حساب متوسط القيم الرقمية فقط—مع تخطي الأخطاء والخلايا الفارغة تلقائيًا—باستخدام صيغة مصفوفة. ويُعد هذا النهج مثاليًا عندما تريد التأكد من احتساب الأرقام الصالحة فقط، وهو أمر شائع في التقارير الشهرية أو أنظمة التقدير أو جداول البيانات المالية.
1. في الخلية التي ترغب في ظهور نتيجة المتوسط فيها، اكتب الصيغة التالية:
=AVERAGE(IF(ISNUMBER(A2:C2),(A2:C2)))تختبر هذه الصيغة كل خلية فيA2:C2، وتحتفظ بالقيم الرقمية فقط، ثم تحسب متوسطها.
2. بعد إدخالها، اضغط مع الاستمرار على مفتاحَيCtrl وShift، ثم اضغط علىEnter لتحويلها إلى صيغة مصفوفة في إصدارات Excel القديمة (سيتم التأكد من ذلك بظهور أقواس مجعّدة في شريط الصيغة). أما في Excel 365 وExcel 2019، فيكفي الضغط ببساطة علىEnter.
تلميح:إذا امتدت بياناتك عبر صفوف متعددة واحتجت إلى نتائج لكل صف، فاستخدم مقبض التعبئة (المربع الصغير في الزاوية السفلية اليمنى للخلية المحددة) لسحب الصيغة لأسفل، وسيتم تعديل المرجعيات تلقائيًا لتتناسب مع كل صف.
المزايا:تعمل مع جميع إصدارات Excel، وتتجاهل القيم غير الرقمية والأخطاء، وتُعدّ موثوقة في مجموعة متنوعة من سيناريوهات الأخطاء أو القيم المفقودة.
القيود:تتطلب إدخالها كصيغة مصفوفة في إصدارات Excel القديمة، وهو ما قد يُربك المستخدمين. وقد يؤدي أي خطأ في الإدخال إلى ظهور خطأ#VALUE! إذا لم تُدخل بالطريقة الصحيحة.
بغض النظر عن الطريقة التي تختارها، تأكد من أن النطاق المستهدف يحتوي فقط على البيانات المرغوبة—فأي أعمدة أو صفوف إضافية غير مقصودة قد تشوّه النتائج. وإذا واجهت أخطاءً متكررة، فراجع طريقة إدخال الصيغة (خاصةً صيغ المصفوفة في إصدارات Excel القديمة)، وتأكد من تحديد النطاق بدقة، وافحص وجود أي خلايا مدمجة أو مخفية قد تعطّل الحساب.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل