في وقت من الأوقات، كانت الجداول المحورية (Pivot Tables) هي الميزة الأبرز في Excel، ولكن كما هو الحال مع معظم الأدوات والحيل، لم يعد هذا هو الوضع. فبدلًا من إعداد جدول محوري، وتعديل التخطيط، وتحديثه في كل مرة تضيف فيها أرقامًا أو تستبدلها، يمكنك الآن كتابة صيغة واحدة تنتج نفس النتائج بجهد ووقت أقل بكثير.
باستخدام PIVOTBY، يمكنك تجميع الملخصات عبر محورين وتجميع القيم، تمامًا كما تفعل مع الجدول المحوري. ومع ذلك، نظرًا لأن كل شيء موجود داخل صيغة، سيتم تحديث ملخصاتك تلقائيًا، مما يسهل ويسرع عليك تحديد الاتجاهات في بياناتك.
PIVOTBY ليست مجرد جدول محوري آخر
إنها صيغة واحدة تبني ملخصًا كاملاً على الفور
على الرغم من إنتاج مخرجات متشابهة، إلا أن PIVOTBY لا علاقة لها بميزة PivotTable التقليدية في Excel. إنها صيغة صفيف ديناميكي متوفرة في Excel لـ Microsoft 365 و Excel 2024 و Excel 2021 (بما في ذلك إصدارات Mac) تقوم بتجميع بياناتك وتجميعها وفرزها وتصفيتها في خطوة واحدة. تكتب صيغة واحدة، وتضغط على Enter، ويقوم Excel بإخراج جدول ملخص كامل مباشرةً في ورقة العمل الخاصة بك دون الحاجة إلى أي تكوين إضافي.
يبدو بناء الجملة كالتالي:
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])
أنت تحتاج فقط إلى أربع وسيطات: العمود أو الأعمدة التي تريدها كصفوف (row_fields)، والعمود أو الأعمدة التي تريدها في الأعلى (col_fields)، والبيانات التي تريد تجميعها (values)، وطريقة التجميع. يمكن أن تكون طريقة التجميع هذه شيئًا مألوفًا، مثل SUM أو AVERAGE أو COUNT، أو شيئًا أكثر تقدمًا، مثل دالة LAMBDA مخصصة. تظل كل الأشياء الأخرى اختيارية وتمنحك تحكمًا إضافيًا إذا اخترت استخدامها.
على سبيل المثال، لنفترض أنك تريد تلخيص إجمالي الأرباح حسب نوع العنصر (الصفوف) وقناة المبيعات (الأعمدة)، مرتبة حسب أعلى ربح. ستبدو الصيغة كالتالي:
=PIVOTBY(C2:C5000, D2:D5000, N2:N5000, SUM,,,-2)
في هذه الحالة، يحتوي العمود C على أنواع العناصر، ويحتوي العمود D على قناة المبيعات، ويحتوي العمود N على الأرباح. يشير الرقم -2 في وسيطة الفرز إلى Excel لفرز النتائج بترتيب تنازلي. ستلاحظ أيضًا ثلاث فواصل قبلها، وهي موجودة لأن وسيطة row_sort_order تقع في الموضع السابع، ويتطلب Excel عناصر نائبة للوسيطات التي تختار تخطيها.
تمامًا مثل Pivot Table التقليدي، يمكنك أيضًا دمج تجميعات الصفوف والأعمدة المتعددة:
=PIVOTBY(HSTACK(YEAR(F2:F5000), C2:C5000), D2:E5000, N2:N5000, SUM)
هنا، أقوم بتجميع سنة الطلب من العمود F جنبًا إلى جنب مع أنواع العناصر من العمود C كفئات للصفوف، بينما أستخدم قناة المبيعات وأولوية الطلب من العمودين D و E كفئات للأعمدة. نظرًا لأن تجميعات الصفوف تأتي من أعمدة منفصلة، فقد استخدمت دالة HSTACK لدمجها في مصفوفة واحدة. وفي الوقت نفسه، نظرًا لأن تجميعات الأعمدة متجاورة، فقد قمت بتوسيع النطاق فقط. قمت أيضًا بتضمين نطاق تاريخ الطلب في دالة YEAR بحيث يقوم Excel بالتجميع حسب السنة بدلاً من التجميع حسب التواريخ الفردية.
الآن بعد أن رأيت كيف تكرر هذه الدالة ما يمكن أن تفعله Pivot Tables، ستصبح الاختلافات أكثر منطقية. أولاً، يتم تحديث PIVOTBY تلقائيًا كلما تغيرت بيانات المصدر الخاصة بك، لذلك لم تعد بحاجة إلى التحديث يدويًا. ثانيًا، نظرًا لأن PIVOTBY هي مجرد صيغة، يمكنك التعامل مع مخرجاتها مثل أي جزء آخر من ورقة العمل، مما يعني أنه يمكنك توصيلها بقوائم منسدلة أو تطبيق تنسيق أو إنشاء لوحات معلومات تفاعلية دون القلق بشأن تعطل التخطيط. ثالثًا، نظرًا لأنه يدعم LAMBDA، يمكنك إنشاء منطق تجميع مخصص يتجاوز الخيارات المضمنة ويتيح تحليلات لا يمكن لـ Pivot Tables التقليدية تكرارها ببساطة.
كيف تتناسب PIVOTBY مع سير عملي
ملخصات سريعة ومرنة مباشرة من الصيغ
تعتبر PIVOTBY مثالية للحالات التي تريد فيها إعداد تقارير سريعة وآلية بأقل قدر من الصيانة. نظرًا لأنها موجودة كصيغة، فلا يوجد خطر من الملخصات القديمة أو النتائج القديمة؛ ما تراه يعكس دائمًا الحالة الحالية لبياناتك. أجدها مفيدة بشكل خاص عندما أعرف بالفعل التجميعات والتجميعات التي أحتاجها، حيث يمكنني تحديد كل شيء مقدمًا بدلاً من إعادة ترتيب الحقول يدويًا.
ومع ذلك، لا تزال جداول Pivot التقليدية تحتل مكانتها. إذا شاركت المصنف الخاص بك مع زملاء ليس لديهم Microsoft 365 أو Excel 2024 أو Excel 2021، فلن تعمل PIVOTBY معهم. في الإعدادات التعاونية حيث لا يشعر الجميع بالراحة في تحرير الصيغ، تجعل واجهة السحب والإفلات الخاصة بـ Pivot Table الكلاسيكي الصيانة أسهل أيضًا. أيضًا، لا تزال PivotTables تقدم مزايا للتحليل الاستكشافي السريع وللتسلسلات الهرمية القابلة للتوسيع التي تتيح للآخرين التعمق في البيانات بشكل تفاعلي.
في معظم الحالات الأخرى، يتناسب PIVOTBY بسلاسة مع سير عملي. غالبًا ما أستخدمه لتلخيص مجموعة بيانات واحدة لأنه ينتج نتائج أسرع بكثير ولا يتطلب أي إعداد إضافي. ولكنه يتوسع أيضًا إلى ما وراء ورقة واحدة. من خلال دمج VSTACK داخل PIVOTBY، يمكنك دمج البيانات من أوراق عمل متعددة في ملخص موحد على الفور تقريبًا. على سبيل المثال، إذا كانت لديك بيانات مبيعات لثلاثة موظفين، Mercy و Mike و Mitchell، في أوراق منفصلة وتريد تلخيص الإيرادات حسب الموقع والدرجة الوظيفية للموظف، فيمكنك كتابة ما يلي:
=PIVOTBY(VSTACK(Mercy!C2:C5, Mike!C2:C5, Mitchell!C2:C5), VSTACK(Mercy!D2:D5, Mike!D2:D5, Mitchell!D2:D5), VSTACK(Mercy!B2:B5, Mike!B2:B5, Mitchell!B2:B5), SUM)
يمكنك أيضًا جعل ملخصاتك تفاعلية عن طريق ربط وسيطة filter_array بقائمة منسدلة. على سبيل المثال، تحسب هذه الصيغة الطلبات حسب البلد والأولوية، ولكن فقط لنوع العنصر المحدد في الخلية V2:
=PIVOTBY(B2:B5000, E2:E5000, G2:G5000, COUNT,,,,,,C2:C5000=V2)
يعمل التعبير C2:C5000=V2 كقناع منطقي يقوم بتقييم كل صف وإرجاع السجل المطابق فقط. إذا كنت تريد أن تعرض الصيغة جميع الطلبات عندما تحتوي V2 على “الكل”، فما عليك سوى تضمين وسيطة filter_array في عبارة IF:
=PIVOTBY(B2:B5000, E2:E5000, G2:G5000, COUNT,,,,,,IF(V2="all", TRUE, (C2:C5000=V2)))
إذا كنت تريد تصور ملخصاتك، فيمكنك ربط المخططات القياسية مباشرةً بنطاق انسكاب PIVOTBY، مما يضمن تحديث العناصر المرئية تلقائيًا مع تغير البيانات. لذلك، لا يجب أن تفوتك مخططات Pivot أيضًا.
طريقة أسرع لتلخيص البيانات
لن يحل PIVOTBY محل PivotTables في كل سيناريو، ولكن عندما أقوم ببناء لوحات معلومات ديناميكية، أو دمج البيانات من أوراق متعددة، أو التخلص من خطوات الإعداد المتكررة، فإنه يوفر ميزة واضحة. نظرًا لأن كل شيء يتم تشغيله من خلال صيغة واحدة، يمكنني إنشاء ملخصات تظل محدثة تلقائيًا وتبقى سهلة الدمج مع بقية ورقة العمل الخاصة بي. وهذا يجعل عملية إعداد التقارير بأكملها أكثر كفاءة وأقل اعتمادًا على التدخل اليدوي.
أفضل طريقة للشعور بالراحة مع PIVOTBY هي أخذ أحد PivotTables الموجودة لديك وإعادة بنائه باستخدام الصيغة. هناك احتمالات، ستندهش من مدى سهولة وسرعة إعادة إنشائه.
