تقارير بنمط الجداول المحورية باستخدام الصيغ

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

الجداول المحورية مفيدة جدًا، لكنها تحتاج إلى تحديث يدوي وتظهر في كتلة ثابتة. أما الجدول المحوري المعتمد على الصيغ فيعيد بناء نفسه مباشرة كلما تغيّرت البيانات.

في هذا الدرس ستنسّق عناوين الصفوف، وعناوين الأعمدة، ومتنًا من صيغ SUMIFS التي تحسب كل تقاطع تلقائيًا.

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

يبدو التقرير الذي نريده كما يلي:

• تسميات الصفوف: كل منطقة مميزة نزولًا في العمود E.
• تسميات الأعمدة: Q1 وQ2 وQ3 وQ4 عبر الصف 1 من F إلى I.
• المتن: إجمالي المبلغ لكل زوج من المنطقة والربع.

تجيب كل خلية في المتن عن سؤال واحد: كم باعت هذه المنطقة في هذا الربع؟

عناوين الصفوف هي المناطق المميزة. استخدم UNIQUE مع SORT حتى تنسكب نزولًا في العمود E وتظل مرتبة.

ضع الصيغة التالية في E2:

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

عناوين الأعمدة هي الأرباع الممتدة أفقيًا عبر صف. يمكنك كتابة Q1 وQ2 وQ3 وQ4 يدويًا، أو سكبها أفقيًا باستخدام TRANSPOSE ملتفة حول UNIQUE.

في F1، تضع الصيغة التالية الأرباع المميزة عبر الجزء العلوي:

تقلب TRANSPOSE قائمة رأسية إلى قائمة أفقية، فتحوّل عمود الأرباع إلى صف من العناوين. والآن أصبح محورا الشبكة في موضعهما.

املأ المتن الآن. تحتاج كل خلية إلى إجمالي منطقة صفها وربع عمودها. تتعامل SUMIFS بسهولة مع شرطين.

في أول خلية من المتن، F2، اكتب:

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

تسمح علامات الدولار بملء الشبكة كلها بنسخ صيغة واحدة. ادرس المراجع المختلطة:

• يثبت $E2 العمود عند E، لكنه يسمح بتغيّر الصف، ولذلك يقرأ كل صف منطقته الخاصة.
• يثبت F$1 الصف عند 1، لكنه يسمح بتغيّر العمود، ولذلك يقرأ كل عمود ربعه الخاص.
• يُثبّت $C$2:$C$500 بالكامل لأن نطاق البيانات لا يتغير موضعه.

انسخ F2 عبر جميع الأرباع ونزولًا عبر جميع المناطق؛ وستعدّل كل خلية مراجعها تلقائيًا وبشكل صحيح.

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

• تُصبح الخلية G2: المنطقة $E2 والربع G$1.
• تُصبح الخلية F3: المنطقة $E3 والربع F$1.

والنتيجة جدول تقاطعي كامل، حُسب فيه إجمالي كل تقاطع. لا حاجة إلى معالج جدول محوري، كما يعاد الحساب فور تغيّر بيانات Sales.

يعرض الجدول المحوري الحقيقي الإجماليات الكلية. أضف عمود إجمالي إلى اليمين وصف إجمالي في الأسفل باستخدام SUM العادية عبر كل سطر.

لحساب إجمالي صف المنطقة الأولى، ضع الصيغة التالية في العمود الذي يلي آخر ربع:

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

إذا كانت أداتك تدعم ذلك، يمكنك تجنب النسخ بتمرير مراجع الانسكاب مباشرةً إلى SUMIFS. استخدم العناوين المنسكبة باعتبارها معايير.

تحسب هذه الصيغة الواحدة إجمالي كل تقاطع بين منطقة وربع:

هنا تمثل E2# قائمة المناطق الرأسية، وتمثل F1# قائمة الأرباع الأفقية. يقرن Excel بينهما لإنشاء شبكة كاملة دفعة واحدة. وتظل طريقة السحب أكثر توافقًا، لكن هذه هي النسخة الحديثة والأنيقة.

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

إذا كان إجمالي صف المنطقة في J2 وكان الإجمالي الكلي في J10، فاكتب:

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

تساعد بعض العادات على إبقاء الجدول المحوري المعتمد على الصيغ موثوقًا:

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

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

تحقق من مدى فهمك للمراجع المختلطة التي تشغّل الجدول المحوري المعتمد على الصيغ.

لقد أعدت إنشاء جدول محوري باستخدام الصيغ وحدها:

• أنشأ الجمع بين UNIQUE وSORT عناوين الصفوف في عمود منسكب.
• وزّعت TRANSPOSE عناوين الأعمدة عبر صف.
• ملأت SUMIFS كل التقاطعات باستخدام المراجع المختلطة $E2 وF$1، إما بالسحب أو باستخدام مراجع الانسكاب مثل E2# وF1#.
• أضافت SUM الإجماليات الكلية الطرفية.

يعاد حساب الشبكة بأكملها مباشرة. بعد ذلك ستجعل لوحة المعلومات تفاعلية باستخدام قوائم منسدلة تتحكم في المقاييس.