تنسيق بيانات Excel تلقائيًا باستخدام دالة PIVOTBY الجديدة
عند العمل على جداول بيانات معقدة، يصبح من الصعب تتبع كل القيم وتحليلها يدويًا. بدلًا من إنشاء Pivot Table تقليدي وإجراء عدة خطوات، توفر دالة PIVOTBY الجديدة في Excel طريقة مباشرة لإعادة تنظيم البيانات وتجميعها تلقائيًا بناءً على الأعمدة التي تحددها.
بمجرد كتابة الصيغة، يُعاد ترتيب الجدول لعرض القيم الإجمالية أو الإحصائية بحسب الفئة أو التاريخ أو أي معيار آخر، دون أي تدخل إضافي. في هذا الدليل، نوضح طريقة استخدام الدالة مع أمثلة عملية، ونقارنها بطرق التجميع التقليدية لتوضيح الفرق الكبير في الأداء والسهولة.
تتيح لك دالة PIVOTBY في برنامج Excel تجميع الأرقام دون الحاجة إلى إعادة إنشاء بياناتك في جدول محوري. علاوة على ذلك، تُحدّث ملخصات البيانات المُنشأة عبر PIVOTBY تلقائيًا لتعكس التغييرات في بياناتك الأصلية، ويمكنك تخصيص ما تُظهره من خلال تعديل الصيغة.
ملاحظة
إذا كنت تعرف بالفعل كيفية استخدام دالة GROUPBY، فسيكون تعلم استخدامها سهلًا. الفرق هو أن GROUPBY تُجمّع المتغيرات في صفوف فقط، بينما تحتوي PIVOTBY على وسيطة إضافية تُتيح لك أيضًا تجميع متغيرات مُحددة في أعمدة.
بنية PIVOTBY
تتيح لك دالة PIVOTBY إدخال ما مجموعه 11 وسيطة. على الرغم من أن هذا قد يبدو مُرهقًا، إلا أن فائدة وجود هذا العدد الكبير من الوسائط هي إمكانية تخصيص النتيجة لتتوافق مع ما تُريد أن تُظهره البيانات.
مع ذلك، الحقول الأربعة الأولى فقط مطلوبة.
الحقول المطلوبة
تُستخدم حقول PIVOTBY لتحديد المتغيرات التي تريد عرضها في الصفوف، والأعمدة، ومكان العثور على القيم، وكيفية تجميع بياناتك.
=PIVOTBY(a,b,c,d)
حيث
- a هي الخلايا التي تحتوي على الفئة أو الفئات التي تريد ظهورها كرؤوس صفوف أسفل يسار نتيجتك،
- b هي الخلايا التي تحتوي على الفئة أو الفئات التي تريد ظهورها كرؤوس أعمدة في أعلى نتيجتك،
- c هي القيم التي ستظهر في وسط نتيجتك، وفقًا للصفوف والأعمدة التي حددتها للوسيطين a وb، وd هي دالة Excel (أو الدالة التي أنشأتها بنفسك باستخدام LAMBDA) التي تحدد كيفية تجميع البيانات.
ملاحظة
على الرغم من أن الوسيطين a وb ضروريان لـ PIVOTBY لإنتاج نوع النتيجة التي تم إنشاؤها لإنتاجها، إلا أنه يمكنك حذفهما لتجميع البيانات في صفوف أو أعمدة فقط.
الحقول الاختيارية
تتيح لك حقول PIVOTBY الاختيارية تضمين الرؤوس والإجماليات، وفرز بياناتك، وتحديد بعض المعلمات الإضافية – في ظروف محددة للغاية:
=PIVOTBY(a,b,c,d,[e,f,g,h,i,j,k])
| المُعامل | الوصف | ملاحظات |
|---|---|---|
| e | رقم يحدد ما إذا كانت المعاملات a وb وc تحتوي على رؤوس أعمدة، وما إذا كنت تريد عرضها في النتيجة | 0 = بدون رؤوس أعمدة (افتراضي) 1 = تحتوي على رؤوس، لكن لا يتم عرضها 2 = بدون رؤوس، لكن يتم إنشاؤها تلقائيًا 3 = تحتوي على رؤوس ويتم عرضها |
| f | رقم يُحدد ما إذا كان المعامل a يحتوي على مجاميع كبرى وفرعية | 0 = بدون مجاميع 1 = مجاميع كبرى في الأسفل (افتراضي) 2 = مجاميع كبرى وفرعية في الأسفل -1 = مجاميع كبرى في الأعلى -2 = مجاميع كبرى وفرعية في الأعلى |
| g | رقم يُحدد طريقة ترتيب الأعمدة | يشير الرقم إلى الأعمدة ضمن المعامل a، يليه العمود في المعامل c. على سبيل المثال، إذا كان لديك عمودان في المعامل a، فإن الرقم “2” يعني الترتيب بحسب العمود الثاني، و”3″ تعني الترتيب حسب المعامل c. الرقم الموجب يعني الترتيب التصاعدي أو أبجديًا، والسالب يعني العكس. |
| h | رقم يُحدد ما إذا كان المعامل b يحتوي على مجاميع كبرى وفرعية | 0 = بدون مجاميع 1 = مجاميع كبرى على اليمين (افتراضي) 2 = مجاميع كبرى وفرعية على اليمين -1 = مجاميع كبرى على اليسار -2 = مجاميع كبرى وفرعية على اليسار |
| i | رقم يُحدد طريقة ترتيب الصفوف | يشير الرقم إلى الأعمدة ضمن المعامل b، يليه العمود في المعامل c. يعمل بنفس طريقة المعامل g. |
| j | تعبير منطقي أو صيغة تُستخدم لتصفية الصفوف التي تحتوي على قيم أو نصوص معينة | غير متوفر |
| k | رقم يمنحك تحكمًا إضافيًا في كيفية تنفيذ الحسابات عند استخدام دالة PERCENTOF في المعامل d | 0 = مجاميع الأعمدة (افتراضي) 1 = مجاميع الصفوف 2 = المجموع الكلي 3 = مجموع العمود الأب 4 = مجموع الصف الأب |
PIVOTBY: استخدام الوسائط المطلوبة فقط
أولاً، دعوني أوضح لكم كيفية استخدام دالة PIVOTBY بأبسط صورة، مع الحقول الأربعة المطلوبة فقط.
لنفترض أن جدول البيانات هذا مُعطى لك باسم Sports_Viewers. يعرض الجدول أرقام المشاهدة المباشرة (العمود د) لست رياضات (العمود ب) عبر أربع مناطق (العمود ج) على مدار أربع سنوات (العمود أ)، وطُلب منك إنشاء ملخص لإجمالي عدد المشاهدين حسب الرياضة والسنة.
يمكنك استخدام دالة GROUPBY للقيام بذلك، مع أنك ستحتاج إلى استخدام وسيطات اختيارية لإنتاج نتيجة تُسهّل التحليل. علاوة على ذلك، بما أن GROUPBY تُرتّب البيانات في صفوف فقط، فقد ينتهي بك الأمر بقائمة طويلة من المعلومات يصعب تفسيرها.
بدلاً من ذلك، يتيح لك استخدام دالة PIVOTBY أخذ متغير واحد أو أكثر ووضعه في أعمدة، مما يمنحك صورة أوضح لكيفية تراكم الأرقام.
ملاحظة
بما أن دالة PIVOTBY تُنتج مصفوفة ديناميكية منسدلة، يجب عليك كتابة صيغتها في مساحة من جدول البيانات غير مُنسقة كجدول Excel.
للقيام بذلك، في الخلية F1، اكتب
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)
حيث
- Sports_Viewers[Sport] هو العمود المُسمّى في الجدول المُسمّى، والذي يُمثّل الرياضات الست المختلفة. ستكون هذه هي عناوين الصفوف أسفل يسار النتيجة.
- Sports_Viewers[Year] هو عمود السنة في الجدول، وستكون هذه هي عناوين الأعمدة أعلى النتيجة.
- Sports_Viewers[Viewers] هو العمود الذي يُظهر عدد الأشخاص الذين شاهدوا هذه الرياضات في تلك السنوات، لذا ستكون هذه البيانات هي الجزء الرئيسي من النتيجة.
SUM يُخبر Excel أنك تريد أن تُجمع أعمدة الإجمالي الأرقام معًا. من ناحية أخرى، إذا استخدمت، على سبيل المثال، المتوسط، فستُظهر لك النتيجة متوسط هذه الأرقام.
نصيحة
بدلاً من كتابة أسماء الجداول والأعمدة يدويًا في الصيغة (المعروفة أيضًا باسم المراجع المُهيكلة)، إذا حددت خلايا الجدول باستخدام الماوس، فسيُدخلها Excel نيابةً عنك.
بفضل طريقة عرض PIVOTBY للبيانات وتنظيمها، يمكنك استخلاص رؤى من النتيجة مباشرةً. على سبيل المثال، يمكنك ملاحظة عدم وجود بيانات لبعض الرياضات في سنوات معينة. أيضًا، في حال عدم وجود الحقول الاختيارية، يضيف Excel تلقائيًا الإجماليات إلى كلٍّ من صفوف وأعمدة نتيجة PIVOTBY، مما يعني أنه يمكنك الاطلاع فورًا على إجمالي أعداد المشاهدات لكل سنة ولكل رياضة.
بدون استخدام الوسيطات الاختيارية، يُرتّب Excel أيضًا النتيجة أبجديًا أو تصاعديًا حسب الوسيطة “أ”، ولا يُضمّن عناوين أعمدة للوسيطة “أ”.
لنفترض الآن أنك تريد تضمين عمود يُقسّم كل رياضة إلى أربع مناطق مختلفة. للقيام بذلك، ستحتاج إلى تضمين كلٍّ من الرياضة والمنطقة في الوسيطة “أ”:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)
نصيحة
لتجميع الأعمدة غير المتجاورة في وسيطة، استخدم الدالة CHOOSECOLS.
توفر هذه النتيجة تحليلًا أكثر تفصيلًا لبياناتك من خلال عرض إجمالي مشاهدات كل رياضة حسب المنطقة. ومع ذلك، نظرًا لإضافة متغير إضافي إلى الوسيطة a، لجعل النتيجة أكثر وضوحًا، يمكنك إدخال بعض الوسائط الاختيارية.
PIVOTBY: استخدام الوسائط الاختيارية
عند إنشاء صيغة في Excel، يتم فصل كل وسيطة بفاصلة. هذا يعني أنه إذا أردت تخطي أي من الوسائط الاختيارية، فما عليك سوى كتابة فاصلة أولى لفتح الوسيطة، متبوعة بفاصلة ثانية لإغلاقها. عدم وجود أي شيء بين هاتين الفاصلتين يُشير إلى أنك حذفت هذه الوسيطة عمدًا، لذا يُريد البرنامج أن يعتمد الإعداد الافتراضي.
المثال 1: الإجماليات الكلية والجزئية
إحدى طرق توضيح نتيجة PIVOTBY في المثال السابق هي إضافة صفوف المجاميع الفرعية لكل رياضة.
للقيام بذلك، اكتب:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2)
لاحظ كيف أن الفاصلتين بعد الحجة d (SUM) تشيران إلى أنك تريد القفز فوق الحجة e (الرؤوس)، ولكن تضمين الحجة f (في هذه الحالة، يعني 2 أنك تريد تضمين مجموع فرعي في أسفل كل رياضة، بالإضافة إلى الإجماليات الكلية في أسفل النتيجة الإجمالية).
ملاحظة
لتوضيح البيانات أكثر، استخدمتُ التنسيق الشرطي لتلوين أي صفوف في النتيجة حيث العمود F ليس فارغًا، والعمود G فارغًا، والعمود F لا يحتوي على عبارة “الإجمالي الكلي”. لا يُنصح بتطبيق التنسيق المباشر على المصفوفات المُفرّقة، لأن التنسيق مُرتبط بالخلايا، وليس بالبيانات. هذا يعني أنه في حال تغيّر البيانات، فلن يتغير التنسيق معها.
مثال 2: ترتيب الفرز
الآن، لنلقِ نظرة على طريقة أخرى للتحكم في نتيجتك: الفرز حسب أحد أعمدة المخرجات.
الكتابة:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2,3)
يعني أنه بعد تخطي الحجة e، بالإضافة إلى تضمين الإجماليات الكلية والإجماليات الفرعية (رقم 2 في الحجة f)، فأنت تريد أيضًا ترتيب بياناتك حسب أرقام المشاهدة الإجمالية بترتيب تصاعدي (رقم 3 في الحجة g).
لاحظ كيف أن الترتيب حسب إجمالي أرقام المشاهدة لا يُرتب الرياضات حسب إجمالي مشاهداتها فحسب، بل يُرتب أيضًا الفئات الفرعية الإقليمية حسب إجماليها الفرعي.
مثال 3: النسب المئوية (PERCENTOF)
إليك كيفية عرض نتيجتك باستخدام النسب المئوية.
الكتابة:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],PERCENTOF,,,2,,,,2)
يخبر Excel أنك تريد أن تكون الرياضات عناوين الصفوف (الوسيطة أ)، والسنوات عناوين الأعمدة (الوسيطة ب)، وتريد تجميع بياناتك (الوسيطة ج) على شكل نسب مئوية (الوسيطة د). يمثل أول 2 (الوسيطة ز) الترتيب، والذي في هذه الحالة يكون حسب القيم. وأخيرًا، يخبر 2 الثاني (الوسيطة ك) Excel أن النسب المئوية يجب أن تكون نسبية بالنسبة لإجمالي البيانات. تخبر الفواصل غير المملوءة Excel أنك تريد منه اعتماد الإعداد الافتراضي للوسيطات هـ، و، ح، ط، و ي.
الآن، حدد الخلايا التي تحتوي على النتائج العشرية، وانقر على أيقونة “نمط النسبة المئوية” في مجموعة “الرقم” ضمن علامة التبويب “الصفحة الرئيسية” على الشريط. أثناء ذلك، انقر على زري “زيادة المنازل العشرية” و”إنقاص المنازل العشرية” لتحديد عدد المنازل العشرية.
نصيحة
حدد صفوفًا إضافية أسفل النتيجة تحسبًا لإضافة المزيد من البيانات إلى الجدول الأصلي مستقبلًا.
وأخيرًا، خذ دقيقة من وقتك لاستيعاب ما تخبرك به هذه النتيجة.
على سبيل المثال، يُشكل عدد مُشاهدي كرة البيسبول في عام ٢٠٢٢ نسبة ١١.٥٪ من إجمالي أرقام المشاهدة لجميع الرياضات على مدار السنوات. ولأن البيانات مُرتبة حسب عمود الإجمالي بترتيب تصاعدي، يُمكنك ملاحظة أن ما يقرب من ربع (٢٣.٧٪) إجمالي أرقام المشاهدة جاء من مُشاهدي كرة السلة.
بالإضافة إلى استخدام SUM وAVERAGE وPERCENTOF لدالة PIVOTBY، يُمكنك أيضًا استخدام دوال تجميع أخرى، مثل COUNT وMIN وMAX.
دالة PIVOTBY تقدم حلاً ذكيًا لكل من يُنظم بياناته يدويًا أو يستخدم Pivot Tables تقليدية.
بفضل هذه الدالة، يمكنك تبسيط تحليلاتك وتسريع إعداد تقاريرك اليومية أو الشهرية بشكل كبير.
كل ما تحتاج إليه هو بناء الصيغة مرة واحدة، وستُعاد هيكلة البيانات تلقائيًا حسب الفئات التي تحددها.
جربها على ملفك التالي لترى الفرق الفوري في طريقة عرض وتحليل البيانات.