تنبيه: المحتوى الوارد بالمقال ينطبق على مستخدمي Office 365 فقط ولا توجد طريقة بديلة لتطبيقه على الإصدارات السابقة حتى لحظة كتابة هذه السطور

في المقال التالي سنعرض حالة عملية تفيد مجال شركات الأدوية ومديري المبيعات لحساب أحد مؤشرات تقييم الأداء المستخدمة لقياس الأداء لمندوبي البيع وهو (متوسط عدد الزيارات في اليوم).

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

https://www.linkedin.com/pulse/%D8%AF%D9%84%D9%8A%D9%84%D9%83-%D8%A5%D9%84%D9%89-%D8%A7%D8%B3%D8%AA%D8%AE%D8%AF%D8%A7%D9%85-%D8%A7%D9%84%D8%AF%D8%A7%D9%84%D8%A9-filter-%D9%81%D9%8A-excel-office-365-%D9%88%D8%AF%D9%88%D8%A7%D9%84-hassan/

وذلك بالإضافة إلى الدالة Countifs الموجودة بإصدارات برنامج Excel المختلفة بدءا من إصدار Excel 2010

نبدأ الحديث بفرض أن لدينا جدولا يمثل سجل الزيارات التي قام بها ستة مندوبي دعاية بإحدى الشركات خلال شهر فبراير 2020 وتظهر بالشكل التالي وعددها 1525 زيارة

والآن نرغب في حساب متوسط الزيارات اليومي لكل مندوب خلال الشهر، وهو ناتج قسمة إجمالي عدد الزيارات المستهدفة التي قام بها كل مندوب على عدد أيام العمل الفعلية لهذا المندوب، (وليس عدد أيام الشهر).
(الزيارات المستهدفة هي التي تضمنتها الخطة المتبعة، ويوضع أمامها الرقم 1 تحت العمود المسمى Targeted )

من أجل الوصول لمعيار تقييم الأداء الموضح قمت بإعداد صفحة التقرير Report كما توضح الصورة التالية

حيث تم كتابة قائمة بأسماء المندوبين مع استخدام المعادلة التالية لحساب مؤشر تقييم الأداء الموضح.

=LET(TotalCalls,COUNTIFS(Calls!$C$2:$C$1525,Report!A2,Calls!$F$2:$F$1525,1),
WorkingDays,COUNTA(UNIQUE(FILTER(Calls!$B$2:$B$1525,Calls!$C$2:$C$1525=Report!A2,Calls!$F$2:$F$152=1))),
TotalCalls/WorkingDays)

وظيفة الدالة الجديدة Let: تعريف أجزاء من المعادلة كمتغيرات بأسماء خاصة (ينطبق عليها نفس شروط اسم النطاق المعرّف)، وذلك من أجل المزيد من التنظيم للمعادلة وعدم تكرار العمليات الحسابية أكثر من مرة.

مكونات الدالة Let:

تظهر مكونات وعناصر الدالة Let في Excel كما توضحها الصورة التالية:

وفيما يلي شرح مبسط للمعادلة المستخدمة المذكورة سابقا:

• TotalCalls اسم المتغير المعرف الأول.
• COUNTIFS(Calls!$C$2:$C$1525 : بدء معادلة المتغير TotalCalls وفيها نستخدم الدالة Countifs ونبدأ بنطاق أسماء المندوبين وهو النطاق المطلوب معرفة عدد مرات تكرار كل مندوب بداخله، لأن تكرار المندوب هنا معناه زيارة جديدة.
• ,Report!A2 : الخانة الموجود بها اسم أول مندوب، معناها كم مرة تكرر اسم هذا المندوب في الجدول، أي كم عدد الزيارات التي قام بها إجمالا.
• Calls!$F$2:$F$1525,1) : إضافة قيد للعدد السابق. إننا نخبر البرنامج بهذه المعادلة أننا نريد عد الزيارات المستهدفة فقط، أي الزيارات المكتوب أمامها 1 في عمود F وهو عمود بيان الحالة إذا كانت الزيارة مستهدفة أو مخطط لها أم لا، ويحتوي على قيمة 1 إذا كانت الزيارة مستهدفة و0 إذا كانت الزيارة غير مستهدفة. وهكذا انتهى المتغير المعرف TotalCalls.
• WorkingDays : اسم المتغير المعرف الثاني والأخير في المعادلة الذي تم تعريفه باستخدام الدالة Let. والذي سيتم حسابه في ما تبقى من المعادلة المكتوبة. وهو “عدد الأيام التي قام المندوب بالعمل فيها بالفعل خلال الشهر” وفيه سنستخدم الدوال Unique لعد القيم غير المكرورة فقط. والدالة Filter للتصفية باسم المندوب والزيارات المستهدفة فقط.
• CountA(Unique( : (دالتان لا دالة واحدة) المطلوب عدد القيم غير المكرورة في النطاق التالي والذي سيتم تحديده باستخدام الدالة Filter
• FILTER(Calls!$B$2:$B$152 : النطاق المطلوب تحديده وإحصاء عدد خلاياه غير المكرورة، وهو نطاق تواريخ الزيارات، وتم استخدام الدالة Filter لتطبيق شروط معينة وهي شرط اسم المندوب وشرط أن تكون الزيارة مستهدفة
• Calls!$C$2:$C$1525=Report!A2 : القيد أو الشرط الأول بالدالة Filter وهو أن يكون اسم المندوب مطابقا للاسم المكتوب في الخلية A2 بالتقرير.
• Calls!$F$2:$F$152=1 : القيد أو الشرط الثاني للدالة Filter وهو أن تكون الزيارة مستهدفة مكتوب أمامها في نطاق الزيارات رقم 1
• TotalCalls/WorkingDays : المكون الأخير من مكونات الدالة Let وهو العملية المطلوب حسابها باستخدام المتغيرات المعرفة سابقا. ويمكن أن تكون معادلة بسيطة مثل معادلة القسمة المستخدمة أو معادلة أخرى أكثر تعقيدا باستخدام VLookup أو حتى SumProduct وغير ذلك.

وفي الصورة التالية تظهر أجزاء المعادلة كاملة وموضعها من ملف التقرير وملف الزيارات:

ويمكن كتابة نفس المعادلة السابقة بدون استخدام الدالة Let كما يلي:

=COUNTIFS(Calls!$C$2:$C$1525,Report!A2,Calls!$F$2:$F$1525,1)/COUNTA(UNIQUE(FILTER(Calls!$B$2:$B$1525,Calls!$C$2:$C$1525=Report!A2,Calls!$F$2:$F$152=1)))

وللأسف شأن هذه الدالة Let هو نفس حال الدالة XLookup، غير متاحة حاليا إلا لبعض مستخدمي Office 365 على سبيل التجربة، على أن تتاح لجميع مستخدمي Office 365 أولا في وقت لاحق من هذا العام في تقديري.