كيفية استخدام وظيفة pivotby في Excel

تتيح لك وظيفة Excel’s Pivotby تجميع أرقامك دون الحاجة إلى إعادة إنشاء بياناتك في Pivottable. والأكثر من ذلك ، أن ملخصات البيانات التي تم إنشاؤها عبر Pivotby تحديث تلقائيًا لتعكس التغييرات في بياناتك الأصلية ، ويمكنك تخصيص ما تظهره من خلال تغيير الصيغة.

بناء جملة Pivotby

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

ومع ذلك ، لا يلزم سوى الحقول الأربعة الأولى.

الحقول المطلوبة

الحقول المحورية المطلوبة هي المكان الذي تخبر فيه Excel عن المتغيرات التي تريد عرضها في الصفوف ، والتي تريد عرضها في الأعمدة ، ومكان العثور على القيم ، وكيف تريد تجميع بياناتك:

=PIVOTBY(a,b,c,d)

أين

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

الحقول الاختيارية

تتيح لك حقول Pivotby الاختيارية تضمين الرؤوس والمجموعات ، وفرز بياناتك ، و – في ظروف محددة للغاية – تحدد بعض المعلمات الإضافية:

=PIVOTBY(a,b,c,d,(e,f,g,h,i,j,k))

Pivotby: استخدام الحجج المطلوبة فقط

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

دعنا نقول أنه تم تسليم جدول البيانات هذا المسمى Sports_Viewers. يوضح أرقام المشاهدة المباشرة (العمود D) لستة رياضات (العمود ب) عبر أربع مناطق (العمود C) على مدار أربع سنوات (العمود أ) ، وطُلب منك إنشاء ملخص لمجموع المشاهدين حسب الرياضة والسنة.

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

بدلاً من ذلك ، يتيح لك استخدام وظيفة Pivotby أخذ واحد أو أكثر من المتغيرات ووضعها في أعمدة ، مما يتيح لك صورة أوضح لكيفية تراكم أرقامك.

للقيام بذلك ، في الخلية F1 ، اكتب

=PIVOTBY(Sports_Viewers(Sport),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM)

أين

• Sports_viewers (الرياضة) هو العمود المسماة في الجدول المسماة الذي يمثل الرياضات الست المختلفة. ستكون هذه رؤوس الصف أسفل الجانب الأيسر من النتيجة.
• Sports_viewers (سنة) يمثل عمود سنة الجدول ، وستكون هذه رؤوس الأعمدة على طول الجزء العلوي من النتيجة.
• Sports_viewers (المشاهدين) هو العمود الذي يوضح عدد الأشخاص الذين شاهدوا هذه الرياضة في تلك السنوات ، لذلك ستكون هذه البيانات هي الجزء البؤري من النتيجة.
• مجموع يخبر Excel أنك تريد أن تضيف الأعمدة الكلية الأشكال معًا. من ناحية أخرى ، إذا كنت تستخدم ، على سبيل المثال ، ستظهر لك النتيجة متوسط ​​هذه الأرقام بدلاً من ذلك.

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

بدون الوسيطات الاختيارية ، يقوم Excel أيضًا بفرز النتيجة إلى ترتيب أبجدي أو تصاعدي حسب الوسيطة أ، ولا يشمل رؤوس الأعمدة للحجة أ.

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

=PIVOTBY(Sports_Viewers((Sport):(Region)),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM)

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

Pivotby: استخدام الوسائط الاختيارية

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

مثال 1: الإجماليات الكبرى والجماع الفرعي

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

للقيام بذلك ، تحتاج إلى الكتابة:

=PIVOTBY(Sports_Viewers((Sport):(Region)),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM,,2)

لاحظ كيف ، بعد الحجة د ((مجموع) ، تشير الفواصلان إلى أنك تريد القفز فوق الحجة ه (الرؤوس) ، ولكن تشمل الحجة و (في هذه الحالة ، 2 يعني أنك تريد تضمين مجموعة فرعية في أسفل كل رياضة ، وكذلك الإجماليات الكبرى في أسفل النتيجة الإجمالية).

مثال 2: ترتيب الفرز

الآن ، دعونا نلقي نظرة على طريقة أخرى لمعالجة النتيجة: الفرز حسب أحد أعمدة الإخراج.

الكتابة:

=PIVOTBY(Sports_Viewers((Sport):(Region)),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM,,2,3)

يعني أنه بعد تخطي الجدال ه، بالإضافة إلى تضمين الإجماليات الكبرى والمجالس الفرعية (العدد 2 في الحجة و) ، تريد أيضًا طلب بياناتك عن طريق المشاهدة الشاملة بالترتيب الصاعد (رقم 3 في الحجة ز).

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

مثال 3: النسب المئوية (في المئة)

إليك كيفية عرض النتيجة الخاصة بك باستخدام النسب المئوية.

الكتابة:

=PIVOTBY(Sports_Viewers(Sport),Sports_Viewers(Year),Sports_Viewers(Viewers),PERCENTOF,,,2,,,,2)

يخبر Excel أنك تريد الرياضة كرؤوس صف (حجة أ) ، السنوات كرؤوس الأعمدة (حجة ب) ، وتريد تجميع بياناتك (الوسيطة ج) في شكل نسب مئوية (الحجة د). الأول 2 (دعوى ز) يمثل الترتيب ، الذي ، في هذه الحالة ، هو حسب القيم. وأخيرا ، والثاني 2 (دعوى كيخبر Excel أن النسب المئوية يجب أن تكون نسبة إلى البيانات الإجمالية. تخبر الفواصل غير المأهولة بالسكان أنك تريد أن تعتمد الافتراضي للوسائط E ، F ، H ، أنا ، و ي.

الآن ، حدد الخلايا التي تحتوي على النتائج القطحية ، وانقر فوق أيقونة “نمط النسبة المئوية” في مجموعة الأرقام في علامة التبويب المنزل على الشريط. أثناء وجودك هناك ، انقر فوق أزرار “زيادة العشرية” و “تقليل العشرية” لتحديد عدد الأماكن العشرية.

أخيرًا ، خذ لحظة هضم ما تخبرك به هذه النتيجة.

على سبيل المثال ، يشكل عدد الأشخاص الذين يشاهدون الكرة اللينة في عام 2022 11.5 ٪ من أرقام المشاهدة الإجمالية لجميع الألعاب الرياضية على مدار جميع السنوات. أيضًا ، نظرًا لأن البيانات إجمالي العمود بترتيب تصاعدي ، يمكنك أن ترى أن ما يقرب من ربع (23.7 ٪) من أرقام المشاهدة الإجمالية جاءت من أشخاص يشاهدون كرة السلة.

بالإضافة إلى استخدام SUM ، المتوسط ​​، ونسبة المئوية للوسيطة الوظيفية في PIVOTBY ، يمكنك أيضًا استخدام وظائف التجميع الأخرى ، مثل COUNT ، MIN ، أو MAX.