إعداد نطاق المعايير

يضم Excel مجموعة من دوال قواعد البيانات التي تبدأ أسماؤها جميعًا بالحرف D، مثل: DSUM وDCOUNT وDAVERAGE وDGET وغيرها.

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

يركز هذا الدرس بالكامل على إنشاء نطاق المعايير بطريقة صحيحة، لأن كل دالة من دوال D تعتمد عليه.

تشترك كل دالة من دوال D في الوسيطات الثلاث نفسها:

• database - الجدول بأكمله، بما في ذلك صف العناوين.
• field - العمود الذي ستُجرى عليه العملية، ويُحدد باسم عنوان بين علامتي اقتباس أو برقم العمود.
• criteria - النطاق الذي يحتوي على قواعد المطابقة.

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

نطاق المعايير هو مجرد كتلة صغيرة من الخلايا تتكون من صفين على الأقل:

• يحتوي الصف العلوي على عناوين أعمدة تطابق عناوين قاعدة البيانات تمامًا.
• تحتوي الصفوف التالية على الشروط.

لنفترض أن بيانات المبيعات تحتوي على العناوين Region وRep وAmount. ولمطابقة المنطقة East فقط، يكون نطاق المعايير عبارة عن خليتين متراصتين: Region في الأعلى وEast أسفلها.

يجب أن تكون كتابة العنوان في نطاق المعايير مطابقة تمامًا لعنوان قاعدة البيانات. فإذا كان اسم عمود البيانات هو Amount بينما يحتوي معياركم على Amounts أو amt، فلن تعثر دالة D على العمود، وقد تُرجع خطأ أو صفرًا.

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

لنفترض أن النطاق A1:C13 يحتوي على جدولكم بعناوين Region وRep وAmount. اكتبوا Region في الخلية E1، واكتبوا East في E2. تمثل كتلة الخليتين هذه، E1:E2، نطاق المعايير.

والآن تجمع DSUM صفوف East فقط. تقرأ الدالة العنوان في E1، وتلاحظ أنه يطابق العمود Region، ثم تحتفظ فقط بالصفوف التي تساوي فيها Region القيمة East.

تطابق حالة نصية مثل East افتراضيًا القيم التي تبدأ بهذا النص. لذلك ستطابق East أيضًا القيمة Eastern.

لفرض تطابق تام، أحاطوا النص بمقارنة على نمط الصيغ: اكتبوا ="=East" في خلية المعايير. ويمكنكم أيضًا استخدام أحرف البدل: إذ تطابق E* أي شيء يبدأ بالحرف E، بينما تطابق ?at الكلمات Cat وBat وHat.

لا تقتصر المعايير على النصوص، إذ يمكنكم استخدام عوامل المقارنة مع الأرقام:

• تطابق >1000 المبالغ التي تزيد على 1000.
• تطابق <=50 القيم التي تساوي 50 أو تقل عنها.
• تطابق <>0 أي قيمة لا تساوي صفرًا.

ضعوا العنوان، مثل Amount، في الأعلى، ونص المقارنة تحته. وتقيّم دالة D قيمة كل سجل مقابل تلك القاعدة.

تُدمج الشروط الموضوعة جنبًا إلى جنب في الصف نفسه باستخدام AND، أي يجب أن تكون جميعها صحيحة.

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

تُدمج الشروط الموضوعة في صفوف منفصلة باستخدام OR، أي يكفي تحقق أي صف مطابق.

لمطابقة East أو West، ضعوا العنوان Region في الأعلى، ثم East في الصف التالي وWest في الصف الذي يليه. ويمتد نطاق المعايير الآن على ثلاثة صفوف، ويمر السجل إذا طابق أيًا من القيمتين.

تذكروا تضمين جميع هذه الصفوف في وسيطة criteria.

يمكنكم الجمع بين التخطيطين. لنفترض أنكم تريدون ‎(East AND >1000) OR (West AND >500)‎.

استخدموا عمودين، Region وAmount. ضعوا East مع >1000 في صف، ثم West مع >500 في الصف التالي. يمثل كل صف مجموعة AND، بينما تعمل الصفوف المنفصلة كـ OR. ويتيح هذا التخطيط الشبكي لدوال D التعبير عن منطق متقدم دون تداخل الدوال.

انتبهوا إلى هذه المشكلات:

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

احتفظوا بنطاق المعايير في منطقة واضحة ومستقلة من ورقة العمل.

يمثل نطاق المعايير جوهر كل دالة من دوال D. وهذه أهم النقاط:

• يحتاج إلى صف عناوين تتطابق أسماؤه تمامًا مع أسماء الجدول.
• توضع الشروط في الصفوف أسفله، سواء أكانت نصوصًا أو أحرف بدل أو مقارنات مثل >1000.
• الصف نفسه = AND، والصفوف المنفصلة = OR.
• تجنبوا صفوف الشروط الفارغة، لأنها تطابق كل شيء.

بعد تجهيز نطاق معايير منظم، يمكنكم تمريره إلى DSUM وDCOUNT وDAVERAGE وDGET في الدروس التالية.