عادةً ، بمجرد إنشاء جدول محوري ، تحتاج إلى توسيع تحليلك وتضمين المزيد من البيانات / الحسابات فيه.
إذا كنت بحاجة إلى نقطة بيانات جديدة يمكن الحصول عليها باستخدام نقطة بيانات موجودة في الجدول المحوري ، فلا داعي للعودة وإضافتها إلى البيانات المصدر.بدلا من ذلك ، يمكنك استخدامPivotTable الحقول المحسوبةلفعل هذا.
المحتويات
ما هو الحقل المحسوب PivotTable؟
لنبدأ بمثال أساسي للجدول المحوري.
لنفترض أن لديك مجموعة بيانات من تجار التجزئة وأنشأت جدولاً محوريًا مثل هذا:
يلخص الجدول المحوري أعلاه مبيعات التجزئة وقيم الربح.
الآن ، ماذا لو كنت تريد أيضًا معرفة هوامش ربح تجار التجزئة هؤلاء (هامش الربح هو "الربح" مقسومًا على "المبيعات")؟
هناك عدة طرق للقيام بذلك:
- ارجع إلى مجموعة البيانات الأصلية وأضف نقطة البيانات الجديدة هذه.لذلك يمكنك ادراج عمود جديد في بيانات المصدر وحساب هامش الربح فيه.بمجرد الانتهاء من ذلك ، تحتاج إلى تحديث البيانات المصدر للجدول المحوري للحصول على هذا العمود الجديد كجزء منه.
- أثناء عمل هذا الأسلوب ، تحتاج إلى الرجوع يدويًا إلى مجموعة البيانات وإجراء العمليات الحسابية.على سبيل المثال ، قد ترغب في إضافة عمود آخر لحساب متوسط المبيعات لكل وحدة (المبيعات / الكمية).مرة أخرى ، يجب عليك إضافة هذا العمود إلى بيانات المصدر ثم تحديث الجدول المحوري.
- تؤدي هذه الطريقة أيضًا إلى تضخيم الجدول المحوري عند إضافة بيانات جديدة إليه.
- أضف العمليات الحسابية خارج الجداول المحورية.حدد هذا الخيار إذا كان من غير المحتمل أن تتغير بنية الجدول المحوري.ومع ذلك ، إذا قمت بتغيير الجدول المحوري ، فقد لا يتم تحديث الحسابات وفقًا لذلك وقد تعطيك نتائج أو أخطاء خاطئة.كما هو موضح أدناه ، قمت بحساب هامش الربح عندما يكون هناك تجار تجزئة في الصف.ولكن عندما أقوم بتغييرها من عميل إلى منطقة ، فإن الصيغة تسوء.
- احسب الحقول باستخدام الجداول المحورية.هذا هواستخدم بيانات PivotTable الموجودة وحساب ملفالطريقة الأكثر فعالية.فكر في الحقول المحسوبة كأعمدة افتراضية تضيفها باستخدام الأعمدة الموجودة في الجدول المحوري.استخدام الحقول المحسوبة في الجدول المحوري له فوائد عديدة (كما سنرى لاحقًا):
- لا يتطلب منك العمل مع الصيغ أو تحديث بيانات المصدر.
- إنه قابل للتوسيع لأنه يأخذ في الاعتبار تلقائيًا أي بيانات جديدة قد تضيفها إلى الجدول المحوري.بعد إضافة حقل محسوب ، يمكنك استخدامه مثل أي حقل آخر في الجدول المحوري.
- من السهل تحديثها وإدارتها.على سبيل المثال ، إذا تغير أحد المقاييس أو احتجت إلى تغيير عملية حسابية ، فيمكنك القيام بذلك بسهولة من الجدول المحوري نفسه.
أضف الحقول المحسوبة إلى الجدول المحوري
دعونا نرى كيفية إضافة حقل محسوب لجدول محوري إلى جدول محوري موجود.
لنفترض أن لديك جدولاً محوريًا يشبه هذا ، وتريد حساب هامش الربح لكل بائع تجزئة:
فيما يلي خطوات إضافة حقل PivotTable المحسوب:
بعد إضافة الحقل المحسوب ، سيظهر كأحد الحقول في قائمة حقول PivotTable.
يمكنك الآن استخدام هذا الحقل المحسوب كأي حقل جدول محوري آخر (لاحظ أنه لا يمكنك استخدام حقل جدول محوري محسوب كمرشح تقرير أو مقسم).
كما ذكرت من قبل ، تتمثل فائدة استخدام الحقل المحسوب للجدول المحوري في أنه يمكنك تغيير هيكل الجدول المحوري وسيتم تعديله تلقائيًا.
على سبيل المثال ، إذا قمت بسحب منطقة وإسقاطها في منطقة الصف ، فستحصل على نتائج مثل ما يلي ، حيث يتم الإبلاغ عن قيم هامش الربح لمتاجر التجزئة والمنطقة.
في المثال أعلاه ، استخدمت معادلة بسيطة (= الربح / المبيعات) لإدراج الحقل المحسوب.ومع ذلك ، يمكنك أيضًا استخدام بعض الصيغ المتقدمة.
قبل أن أعرض لك مثالاً على استخدام صيغة متقدمة لإنشاء حقل PivotTable محسوب ، إليك بعض الأشياء التي يجب أن تعرفها:
- لا يمكنك استخدام المراجع أو النطاقات المسماة عند إنشاء حقول PivotTable المحسوبة.سيؤدي هذا إلى استبعاد العديد من الصيغ مثل VLOOKUP و INDEX و OFFSET وما إلى ذلك.ومع ذلك ، يمكنك استخدام الصيغ التي تعمل بدون مراجع (مثل SUM و IF و COUNT وما إلى ذلك ...).
- يمكنك استخدام الثوابت في الصيغ.على سبيل المثال ، إذا كنت تريد معرفة المبيعات المتوقعة المتوقع نموها بنسبة 10٪ ، يمكنك استخدام الصيغة = المبيعات * 1.1 (حيث يكون 1.1 ثابتًا).
- اتبع ترتيب الأسبقية في الصيغ التي تنشئ الحقول المحسوبة.كأفضل ممارسة ، استخدم الأقواس للتأكد من أنك لست مضطرًا لتذكر الأسبقية.
لنلقِ نظرة الآن على مثال لإنشاء حقل محسوب باستخدام صيغة متقدمة.
افترض أن لديك مجموعة البيانات الموضحة أدناه وتحتاج إلى عرض المبيعات المتوقعة في جدول محوري.
بالنسبة للقيم المتوقعة ، يمكنك استخدام زيادة المبيعات بنسبة 300٪ لكبار تجار التجزئة (أكثر من 5 ملايين مبيعات) وزيادة المبيعات بنسبة 300٪ لتجار التجزئة الصغار والمتوسطين (أقل من 10 ملايين مبيعات).
ملاحظة: أرقام المبيعات هنا وهمية وقد تم استخدامها لتوضيح الأمثلة في هذا البرنامج التعليمي.
هيريس كيفية القيام بذلك:
سيؤدي هذا إلى إضافة عمود جديد إلى PivotTable بقيم توقعات المبيعات.
الجدول المحوري مشكلة حقل محسوب
الحقول المحسوبة هي ميزة رائعة تزيد حقًا من قيمة الجداول المحورية باستخدام حسابات الحقول ، مع الاحتفاظ بكل شيء قابل للتطوير والإدارة.
ومع ذلك ، هناك مشكلة في حقول PivotTable المحسوبة والتي يجب أن تفهمها قبل استخدامها.
لنفترض أن لدي جدول محوري كما هو موضح أدناه وأنا أستخدم حقلاً محسوبًا للحصول على أرقام المبيعات المتوقعة.
لاحظ أن المجاميع الفرعية والإجماليات غير صحيحة.
بينما يجب أن تضيف هذه القيم توقعات مبيعات منفصلة لكل بائع تجزئة ، إلا أنها في الواقع تتبع نفس صيغة الحقل المحسوبة التي أنشأناها.
لذلك بالنسبة لـ South Total ، بينما يجب أن تكون القيمة 22,824,000،22,287,000،XNUMX ، يشير South Total بشكل غير صحيح إلى XNUMX،XNUMX،XNUMX.يحدث هذا لأنه يستخدم الصيغة 21,225,800 * 1.05 للحصول على القيمة.
لسوء الحظ ، لا يمكنك تصحيح هذه المشكلة.
أفضل طريقة للتعامل مع هذا هو إزالة الإجماليات الفرعية والإجماليات الكلية من الجدول المحوري.
يمكنك أيضًا المرور مُقدم من ديبرابعض الحلول المبتكرة للتعامل مع هذه المشكلة.
كيفية تعديل أو حذف حقل محسوب في الجدول المحوري؟
بعد إنشاء حقل PivotTable محسوب ، يمكنك تعديل الصيغة أو حذفها باستخدام الخطوات التالية:
كيف تحصل على قائمة بجميع صيغ الحقول المحسوبة؟
إذا قمت بإنشاء الكثير من حقول PivotTable المحسوبة ، فلا داعي للقلق بشأن تتبع الصيغ المستخدمة في كل حقل.
يتيح لك Excel إنشاء قائمة بكل الصيغ المستخدمة لإنشاء الحقول المحسوبة بسرعة.
فيما يلي الخطوات للحصول بسرعة على قائمة بجميع صيغ الحقول المحسوبة:
- حدد أي خلية في الجدول المحوري.
- انتقل إلى أدوات PivotTable -> تحليل -> الحقول والعناصر والمجموعات -> قائمة الصيغ.
بمجرد النقر فوق صيغة القائمة ، سيقوم Excel تلقائيًا بإدراج ورقة عمل جديدة تحتوي على تفاصيل جميع الحقول / العناصر المحسوبة التي استخدمتها في الجدول المحوري.
يمكن أن تكون هذه أداة مفيدة للغاية إذا كان عليك إرسال عمل إلى عميل أو مشاركته مع فريقك.
