في هذا المقال القصير سنعرض مثالا يعبر عن مشكلة كانت محل أسئلة عديدة فيما مضى وتم تقديم العديد من الحلول لها باستخدام دوال الصفيف الكلاسيكية، في هذا المقال سنقدم الحل باستخدام الدالة Filter التي تم الإعلان عن طرحها لمستخدمي Excel for Office 365 في سبتمبر 2018، وهي متاحة الآن على سبيل التجربة لبعض المستخدمين المشتركين في برنامج Office Insider.

يمثل السيناريو المطروح مشكلة يواجهها مدحت، الذي يعمل مسئولا للموارد البشرية بإحدى الشركات الكبرى التي تستخدم Office 365. يحتفظ مدحت ببيانات الموظفين في جدول يمثل الشكل التالي جزءا منه:

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

وفيما يلي الخطوات المطلوبة التي قام بها مدحت لإعداد التقرير باستخدام الدالة Filter في Excel for Office 365

1- قام مدحت بإضافة ورقة عمل جديدة للتقرير،

2- في جزء من ورقة العمل الجديدة قام مدحت بإعداد جدول كما بالشكل التالي لاستخدامه في إعداد القائمة المنسدلة للأشهر:

3- قام مدحت بتحديد الخلية D2 من صفحة التقرير، ثم ضغط على علامة تبويب Data ومنها قام بتحديد Data Validation فظهرت نافذة Data Validation كما بالشكل التالي:

4- في نافذة Data Validation قام مدحت بتحديد الاختيار List من مربع الاختيارات أسفل Allow، ثم تحت مربع Source قام بتحديد نطاق قائمة الشهور فظهر النطاق داخل نافذة Data Validation كما بالشكل التالي:

5- بالضغط على OK في نافذة Data Validation تظهر الخلية D2 من صفحة التقرير كما بالشكل التالي:

6- بعد قيام مدحت بتجهيز وتنسيق بيئة التقرير، قام بتحديد الخلية A4 في صفحة التقرير ثم قام بكتابة المعادلة التالية باستخدام الدالة Filter:

المعادلة المكتوبة كما يلي:

=FILTER(All!A2:F13,(Table1[تقييم الأداء]>=70%)*(All!D2:D13>=DATE(2018,VLOOKUP(Sheet3!D2,Sheet3!R3:S14,2,0),1))*(All!D2:D13<DATE(2018,VLOOKUP(Sheet3!D2,Sheet3!R3:S14,2,0),31)),{0,”No Data”,”No Data”,”No Data”,0,0})

وفيما يلي تفسير المعادلة المكتوبة:

النطاق المطلوب تصفيته: All!A2:F13

معايير التصفية:

(Table1[تقييم الأداء]>=70%)*(All!D2:D13>=DATE(2018,VLOOKUP(Sheet3!D2,Sheet3!R3:S14,2,0),1))*(All!D2:D13<DATE(2018,VLOOKUP(Sheet3!D2,Sheet3!R3:S14,2,0),31))

حيث:

(Table1[تقييم الأداء]>=70%) المعيار الأول لا بد أن يكون تقييم الأداء أكبر من أو يساوي 70%

(All!D2:D13>=DATE(2018,VLOOKUP(Sheet3!D2,Sheet3!R3:S14,2,0),1)) المعيار الثاني الحد الأدنى لنطاق التاريخ المطلوب التصفية على أساسه، حيث تم استخدام الدالة Date لتحديد السنة 2018 واليوم 1 ورقم الشهر المقابل لاسمه الذي تم تحديده من القائمة المنسدلة يتم البحث عنه في الجدول الموضح أعلاه في الخطوة 2 باستخدام VLookup وبدلالة اسم الشهر.

(All!D2:D13<DATE(2018,VLOOKUP(Sheet3!D2,Sheet3!R3:S14,2,0),31)) المعيار الأخير الحد الأقصى لنطاق التاريخ وهو آخر يوم في نفس الشهر الذي تم تحديده

الجزء الأخير من المعادلة {0,”No Data”,”No Data”,”No Data”,0,0} يمثل القيم البديلة التي يتم عرضها تحت كل عمود في حالة أنه لا توجد نتائج للشهر الذي تم اختياره

الصورة التالية تمثل النتائج في حالة اختيار شهر نوفمبر

ويلاحظ عدم ظهور الموظف وائل الموظف بإدارة المبيعات والذي يتم تجديد تعاقده في 1 نوفمبر 2018 وذلك لأن تقييم الأداء الخاص به 40%، راجع الشكل في أول المقالة أعلاه.

الدالة Filter متاحة في Excel لمشتركي Office 365 فقط وغير متاحة لمستخدمي Excel 2019 والإصدارات السابقة.