حساب المتوسط والاستخراج باستخدام DAVERAGE وDGET

يتناول هذا الدرس آخر دالتين من دوال قواعد البيانات:

• DAVERAGE - متوسط حقل رقمي للسجلات المطابقة.
• DGET - تستخرج قيمة واحدة من الصف الوحيد الذي يطابق المعايير.

تعيد كلتاهما استخدام النمط نفسه (database, field, criteria) الذي تعرفتم إليه، لذلك ستتعلمون أساسًا ما تُرجعانه، لا بنية جديدة.

تحسب DAVERAGE(database, field, criteria) متوسط عمود الحقل عبر الصفوف التي تطابق نطاق المعايير.

• database - الجدول الكامل مع العناوين.
• field - عمود رقمي لحساب متوسطه.
• criteria - نطاق قواعد المطابقة.

وهي تشبه AVERAGEIFS، لكنها تقرأ الشروط من الخلايا.

باستخدام بيانات المبيعات في A1:C13، التي تحتوي على العناوين Region وRep وAmount، ضعوا Region في E1 وEast في E2.

تُرجع الصيغة متوسط Amount عبر صفوف East. فإذا كانت مبالغ طلبات East هي 1200 و800 و1000، فسيكون المتوسط 1000. غيّروا E2 إلى West، وسيتحدّث المتوسط تلقائيًا.

تقبل DAVERAGE مجموعة أدوات المعايير كاملة. لحساب متوسط طلبات East الكبيرة فقط، أنشئوا نطاق معايير من عمودين: ضعوا العنوانين Region وAmount، ثم ضعوا تحتهما East و>1000.

الشروط الموجودة في الصف نفسه هي AND، لذلك تحسب الدالة متوسط المبالغ من صفوف East التي تتجاوز 1000. وتُتجاهل الحقول الفارغة؛ إذ تدخل القيم الرقمية فقط في حساب المتوسط.

على خلاف DSUM التي تُرجع 0، تُرجع DAVERAGE خطأ #DIV/0! عندما لا يطابق أي صف المعايير، إذ لا يمكن حساب متوسط صفر من القيم.

تجنّبوا ذلك بالتحقق من العدد أولًا، أو غلّفوا الدالة باستخدام IFERROR لعرض رسالة واضحة بدلًا من رمز الخطأ.

تختلف DGET عن غيرها؛ فهي تُرجع قيمة واحدة من الحقل في الصف الوحيد الذي يطابق المعايير.

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

لنفترض أن جدولكم يحتوي على مندوبي مبيعات فريدين، وأنكم تريدون معرفة Amount للمندوبة «Sara». ضعوا Rep في E1 وSara في E2.

تعثر DGET على صف Sara الوحيد وتُرجع قيمة Amount الخاصة به. وبما أنها تستطيع المطابقة باستخدام عدة أعمدة للمعايير في الوقت نفسه، فإن DGET تتعامل مع عمليات البحث متعددة المفاتيح التي لا تستطيع VLOOKUP العادية التعامل معها.

تتطلب DGET تطابق صف واحد بالضبط:

• إذا لم يطابق أي صف المعايير، فتُرجع #VALUE!.
• إذا طابق أكثر من صف المعايير، فتُرجع #NUM!.

وهذه الأخطاء مفيدة في الواقع؛ فهي تنبهكم إلى أن المفتاح مفقود أو غير فريد. ضيّقوا المعايير حتى يستوفي سجل واحد بالضبط الشروط.

لضمان وجود تطابق واحد، أضيفوا مزيدًا من الشروط. هل تريدون معرفة Amount للمندوبة Sara في منطقة East؟ استخدموا نطاق معايير من عمودين: ضعوا العنوانين Rep وRegion، ثم ضعوا تحتهما Sara وEast.

يضيّق منطق AND النتائج إلى صف واحد، وتُرجع DGET قيمة Amount الخاصة به. هكذا تعمل DGET كدالة بحث نظيفة متعددة المفاتيح.

بما أن DGET تُصدر خطأً عند عدم وجود تطابقات أو عند وجود تطابقات متعددة، فغلّفوها لتحسين تجربة الاستخدام. تحوّل IFERROR أيًا من الخطأين إلى رسالة واضحة يمكن قراءتها.

إذا أردتم تحديد التكرار بشكل مختلف عن عدم العثور على تطابق، فيمكنكم اختبار العدد باستخدام DCOUNTA أولًا، ثم اختيار الرسالة المناسبة وفقًا لذلك.

إليك دليلًا سريعًا لاختيار الأداة المناسبة:

• هل تحتاجون إلى إجمالي الصفوف المطابقة؟ استخدموا DSUM.
• هل تحتاجون إلى عدد الصفوف المطابقة؟ استخدموا DCOUNT أو DCOUNTA.
• هل تحتاجون إلى متوسط الصفوف المطابقة؟ استخدموا DAVERAGE.
• هل تحتاجون إلى قيمة واحدة من صف مطابق واحد؟ استخدموا DGET.

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

تطابق معايير DGET لديك ثلاثة صفوف في الجدول. ماذا تُرجع DGET؟

لقد أتممتم التعرف إلى عائلة دوال D:

• تحسب DAVERAGE متوسط حقل رقمي للصفوف المطابقة؛ وعند عدم وجود تطابقات تُرجع #DIV/0!.
• تُرجع DGET قيمة واحدة من صف مطابق واحد بالضبط؛ وعند عدم وجود تطابقات تُرجع #VALUE!، وعند وجود تطابقات متعددة تُرجع #NUM!.
• تشترك الدالتان في النمط (database, field, criteria) وقواعد نطاق المعايير الخاصة بـ AND وOR.
• غلّفوا الدالتين باستخدام IFERROR للحصول على مخرجات واضحة واحترافية.

وبالاقتران مع DSUM وDCOUNT، يمكنكم الآن تشغيل تقارير كاملة قائمة على المعايير مباشرةً من جدول منظم.