بطاقات KPI وإبرازات شرطية

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

تجمع البطاقة الجيدة ثلاثة أجزاء: تسمية واضحة، والرقم الكبير الناتج من صيغة، وغالبًا مقارنة صغيرة مثل التغير مقارنة بالفترة السابقة.

في هذا الدرس، ستنشئ أرقام KPI باستخدام صيغ التجميع، وتضيف مقارنات بين الفترات، وتستخدم المنطق الشرطي لإبراز القيم التي تحتاج إلى الانتباه.

ابدأ بالمقياس الأساسي. إجمالي الإيرادات من ورقة Sales هو مجرد استخدام لـ SUM على عمود المبالغ.

ضع هذه الصيغة في خلية قيمة البطاقة:

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

تكون مؤشرات KPI كثيرة عبارة عن نسب. يقسم متوسط قيمة الطلب إجمالي الإيرادات على عدد الطلبات. أنشئه من مقياسي تجميع.

تستخدم إحدى الطرق المختصرة AVERAGE مباشرةً:

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

يصبح مؤشر KPI أكثر فائدة عند عرضه بجوار هدف. افترض أن هدف الإيرادات الشهري موجود في الخلية B1. احسب مقدار تجاوزك للهدف أو انخفاضك عنه.

الفرق هو القيمة الحالية ناقص الهدف:

يمكنك أيضًا التعبير عنه كنسبة مئوية من الهدف باستخدام =SUM(Sales!C2:C500)/B1. إن عرض القيمة 1.12 يعني للقارئ أنك حققت 112 بالمئة من الهدف. وتحول المقارنة الرقم المجرد إلى قصة مفهومة.

يحب القراء رؤية التقدم. احسب النسبة المئوية للتغير مقارنة بفترة سابقة. لنفترض أن إجمالي هذه الفترة موجود في D2، وإجمالي الفترة السابقة في D3.

صيغة النمو هي:

إذا كانت قيمة هذه الفترة 120 وقيمة الفترة السابقة 100، فستحصل على 0.2، أي نمو بنسبة 20 بالمئة. نسّق الخلية كنسبة مئوية. ويحوّل مؤشر صغير للصعود أو الهبوط كهذا البطاقة الجامدة إلى اتجاه يمكن فهمه بنظرة واحدة.

يمكنك عرض الاتجاه باستخدام كلمات أو رموز مع IF. اقرأ خلية التغير واختر تسمية.

تعرض هذه الصيغة مؤشر صعود أو هبوط مع النسبة المئوية:

عندما يكون التغير في E1 موجبًا، يظهر مؤشر صعود؛ وإلا يظهر مؤشر هبوط. ويؤدي ضم النص إلى رقم منسق إلى إبقاء البطاقة مختصرة وسهلة القراءة من دون الحاجة إلى مخطط.

يغيّر التنسيق الشرطي لون الخلية بناءً على قاعدة، فتبرز القيم المهمة. حدّد خلايا KPI، وافتح Conditional Formatting، ثم أضف قاعدة تعتمد على صيغة.

لتمييز أي بطاقة تقل عن الهدف باللون الأحمر، استخدم قاعدة صيغة مثل:

تحصل الخلايا التي تكون فيها القاعدة TRUE على التنسيق الذي تختاره. وهكذا تتحول لوحة المعلومات إلى اللون الأحمر عندما لا تحقق المبيعات الهدف، وإلى اللون الأخضر عندما تتجاوزه، من دون الحاجة إلى قراءة الأرقام بدقة.

قد ترغب أحيانًا في عرض التنبيه كنص داخل الخلية نفسها، لا كلون فقط. ينشئ IF مع عوامل المقارنة كلمة حالة.

لتسمية الأداء مقارنة بالهدف:

تعرض الصيغة On Track عند تحقيق الهدف، وBehind عند عدم تحقيقه. ويتكامل عمود الحالة هذا جيدًا مع التنسيق الشرطي الذي يلوّن الكلمة، ليقدم إشارة مرئية وأخرى مكتوبة.

تتضمن مؤشرات KPI الفعلية غالبًا أكثر من حالتين: جيدة، وتحذيرية، وحرجة. تتحقق الدالة IFS من الشروط بالترتيب وتعيد أول شرط مطابق، وهو ما يكون أوضح من استخدام IF متداخلة.

لتصنيف الأداء ضمن ثلاث فئات:

تختبر IFS الشروط من الأعلى إلى الأسفل، لذا ضع الشرط الأكثر صرامة أولًا. واربط كل فئة بقاعدة لون، وستوضح بطاقاتك الحالة بنظرة واحدة.

بالإضافة إلى القواعد المفردة، تظلل مقاييس الألوان نطاقًا من الأرقام بتدرج لوني، بحيث تبرز القيم الأعلى والأدنى من دون أي صيغة. وهي مثالية لعمود يحتوي على إجماليات المناطق أو الأرقام اليومية.

حدّد النطاق، وافتح Conditional Formatting، واختر مقياس ألوان مثل التدرج من الأخضر إلى الأحمر.

• تظهر القيم الأكبر باللون الأخضر، والأصغر بالأحمر، وتتدرج القيم المتوسطة بينهما.
• يُعاد حساب المقياس تلقائيًا عند تغير البيانات.

تتناسق مقاييس الألوان بشكل رائع مع بطاقات KPI: تعرض البطاقات الملخص الرئيسي، بينما يوضح العمود الملوّن موضع كل قيمة ضمن النطاق.

رتّب بطاقاتك في صف منظم أعلى لوحة المعلومات. كل بطاقة عبارة عن كتلة صغيرة تتضمن خلية تسمية، وصيغة قيمة، ومقارنة أو حالة أسفلها.

• البطاقة 1: إجمالي الإيرادات، =SUM(Sales!C2:C500)، مع الفرق عن الهدف.
• البطاقة 2: الطلبات، =COUNTA(Sales!A2:A500).
• البطاقة 3: متوسط قيمة الطلب، =AVERAGE(Sales!C2:C500)، مع نسبة النمو.

يُلوّن التنسيق الشرطي خلايا المقارنة. ويقدم الصف ملخصًا فوريًا قبل أن ينتقل القارئ إلى التفاصيل.

اختبر فهمك لإنشاء تنبيه حالة متعدد المستويات.

أنشأت الطبقة الرئيسية في لوحة المعلومات:

• أنتجت صيغ التجميع مثل SUM وCOUNTA وAVERAGE أرقام KPI الكبيرة.
• أضافت صيغ الفرق والنمو سياقًا للمقارنة مع هدف وفترة سابقة.
• حوّلت IF وIFS الأرقام إلى كلمات حالة مثل On Track وWatch وCritical.
• لوّن التنسيق الشرطي باستخدام قواعد الصيغ الخلايا تلقائيًا لتمييز ما يهم.

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