جداول الملخص باستخدام المصفوفات الديناميكية

يختصر جدول الملخص قائمة كبيرة من الصفوف الخام في كتلة صغيرة وسهلة القراءة: صف واحد لكل فئة، وبجواره الإجماليات. تخيّل سجل مبيعات يضم مئات الصفوف يتحول إلى جدول منظم يعرض كل منطقة وإيراداتها الإجمالية.

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

في هذا الدرس ستجمع بين ثلاث أدوات قوية: UNIQUE لسرد الفئات، وSUMIFS لحساب إجمالي كل فئة، وFILTER لجلب الصفوف المطابقة. وباستخدامها معًا ستنشئ ملخصًا مباشرًا.

تخيّل ورقة باسم Sales تضم ثلاثة أعمدة: المنطقة في العمود A، والمنتج في العمود B، والمبلغ في العمود C، وتمتد من الصف 2 إلى الصف 200.

هدفنا إنشاء ملخص يعرض كل منطقة مميزة وإجمالي مبيعاتها. والتحدي الأول هو الحصول على قائمة مرتبة ونظيفة بالمناطق من دون كتابتها يدويًا، لأن مناطق جديدة قد تظهر لاحقًا.

• يحتوي النطاق A2:A200 على أسماء مناطق متكررة مثل East وWest وEast وNorth.
• نريد فقط: East وWest وNorth، بحيث تظهر كل منها مرة واحدة.

تشكل هذه القائمة المميزة الأساس الذي يعتمد عليه الملخص بالكامل.

تأخذ الدالة UNIQUE نطاقًا وتُرجع كل قيمة مرة واحدة فقط. وهي تنسكب، أي إن صيغة واحدة تملأ عددًا من الخلايا يساوي عدد القيم المميزة.

اكتب الصيغة التالية في الخلية E2، وستظهر قائمة المناطق تلقائيًا أسفلها:

إذا أُضيفت منطقة جديدة إلى البيانات لاحقًا، فستتوسع القائمة المنسكبة تلقائيًا. ولن تحتاج إلى تعديل الصيغة مطلقًا.

نحتاج الآن إلى حساب إجمالي المبالغ لكل منطقة في العمود E. تضيف SUMIFS القيم من نطاق واحد فقط عندما يطابق نطاق آخر شرطًا معينًا.

بنية الدالة هي SUMIFS(sum_range, criteria_range, criteria). ضع الصيغة التالية في الخلية F2، بجوار المنطقة الأولى:

المرجع E2# هو الجزء المهم هنا. تعني العلامة # النطاق المنسكب بالكامل بدءًا من E2. لذلك تحسب هذه الصيغة الواحدة إجمالي كل منطقة أنتجتها UNIQUE.

يشير مرجع الانسكاب E2# دائمًا إلى الكتلة الكاملة التي أنتجتها الصيغة، مهما كان حجمها. وهذا ما يجعل الملخص ديناميكيًا.

عندما تعثر UNIQUE على 3 مناطق، يمتد E2# إلى 3 خلايا، وتُرجع SUMIFS ثلاثة إجماليات. وعندما تكبر البيانات لتشمل 5 مناطق، يتوسع النطاقان معًا من دون أي تعديل.

• E2 = الخلية العلوية المفردة فقط.
• E2# = المصفوفة المنسكبة بالكامل بدءًا من E2.

احرص على فهم علامة # جيدًا؛ فهي جوهر صيغ لوحات المعلومات.

يصبح الملخص أسهل قراءة عندما تُرتب الإجماليات. لفّ قائمة المناطق بالدالة SORT لتظهر الفئات بترتيب أبجدي، أو فرز الجدول بالكامل حسب الإجمالي.

لسرد المناطق بترتيب أبجدي في E2:

بما أن الإجماليات في F لا تزال تشير إلى E2#، فإن فرز المناطق يعيد محاذاة الإجماليات تلقائيًا. ويبقى العمودان متزامنين.

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

لعرض جميع صفوف المبيعات التي تساوي فيها المنطقة القيمة الموجودة في الخلية H1:

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

تُظهر FILTER خطأ #CALC! عندما لا توجد نتائج مطابقة. وللحفاظ على مظهر نظيف، قدّم رسالة بديلة اختيارية باعتبارها الوسيط الثالث.

يظهر الوسيط الثالث عندما لا توجد أي نتائج مطابقة:

الآن ستعرض المنطقة التي لا توجد لها مبيعات ملاحظة واضحة بدلًا من ظهور خطأ. أضف هذه القيمة البديلة دائمًا إلى لوحات المعلومات حتى لا يؤدي اختيار غير متوقع إلى إفساد التخطيط.

يعرض الملخص غالبًا عدد الطلبات لكل منطقة، وليس قيمة المبيعات فقط. تحسب COUNTIFS عدد الصفوف المطابقة لشرط معين، تمامًا مثل SUMIFS ولكن من دون نطاق جمع.

ضع الصيغة التالية في العمود G بجوار الإجماليات:

يصبح ملخصك الآن مكوّنًا من ثلاثة أعمدة: المنطقة، وإجمالي المبيعات، وعدد الطلبات، وكلها تعتمد على قائمة المناطق المنسكبة المفردة في E2#. ويتم تحديث كل شيء معًا.

إليك الصيغة الكاملة الموضوعة جنبًا إلى جنب:

• E2: تسرد =SORT(UNIQUE(Sales!A2:A200)) المناطق.
• F2: تحسب =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) إجمالي كل منطقة.
• G2: تحسب =COUNTIFS(Sales!A2:A200, E2#) عدد كل منطقة.

تُكتب صيغة E2 وحدها عبر الصفوف؛ أما F وG فتنسكبان اعتمادًا على مرجع #. أضف عملية بيع جديدة في أي مكان في Sales، وستتحدث الأعمدة الثلاثة من دون أي نقرات.

يتمتع الملخص المعتمد على الصيغ بمزايا حقيقية مقارنة بكتابة القيم أو تحديث جدول محوري يدويًا:

• مباشر: يعيد الحساب فور تغيّر البيانات.
• يتكيف مع الحجم: تظهر الفئات الجديدة تلقائيًا باستخدام UNIQUE ومرجع #.
• شفاف: يمكن لأي شخص قراءة المنطق من الخلية.

لكن نطاقات الانسكاب تحتاج إلى مساحة فارغة تتمدد إليها؛ وسنتناول الانسكابات المحجوبة في درس لاحق. في الوقت الحالي، اترك مساحة أسفل صيغك.

اختبر ما تعلمته عن إنشاء جدول ملخص يتحدث تلقائيًا.

لقد أنشأت جدول ملخص يحافظ على تحديث نفسه:

• تسرد UNIQUE كل فئة مرة واحدة وتسكب النتيجة.
• ترتب SORT هذه القائمة لتسهيل قراءتها.
• تحسب SUMIFS وCOUNTIFS إجمالي كل فئة وعددها باستخدام مرجع الانسكاب E2#.
• تجلب FILTER الصفوف المطابقة لعرض التفاصيل، مع رسالة بديلة عند عدم وجود نتائج.

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