المحتويات
ربما تكون قد سمعت عن قانون باريتو أو مبدأ 20/80. في نهاية القرن التاسع عشر ، اكتشف عالم الاجتماع والاقتصادي الإيطالي فيلفريدو باريتو أن توزيع الثروة في المجتمع غير متكافئ ويخضع لاعتماد معين: مع زيادة الثروة ، يتناقص عدد الأثرياء أضعافًا مضاعفة بمعامل ثابت ( بين الأسر الإيطالية ، كان 19 ٪ من الدخل في 80 ٪ من العائلات). في وقت لاحق ، تم تطوير هذه الفكرة في كتابه بواسطة ريتشارد كوخ ، الذي اقترح صياغة "المبدأ العالمي 20/20" (80٪ من الجهود تعطي 20٪ من النتيجة). من الناحية العملية ، لا يتم التعبير عن هذا القانون عادةً بمثل هذه الأرقام الجميلة (اقرأ "The Long Tail" لكريس أندرسون) ، ولكنه يُظهر بوضوح التوزيع غير المتكافئ للموارد والأرباح والتكاليف ، إلخ.
في تحليل الأعمال ، غالبًا ما يتم إنشاء مخطط باريتو لتمثيل هذا التفاوت. يمكن استخدامه لإظهار بصريًا ، على سبيل المثال ، المنتجات أو العملاء الذين يحققون أكبر قدر من الأرباح. عادة ما يبدو كالتالي:
ميزاته الرئيسية:
- يمثل كل عمود أزرق من المدرج التكراري ربح المنتج بالوحدات المطلقة ويتم رسمه على طول المحور الأيسر.
- يمثل الرسم البياني البرتقالي النسبة المئوية التراكمية للأرباح (أي حصة الأرباح على أساس تراكمي).
- على حد شرطي بنسبة 80٪ ، يتم عادةً رسم خط أفقي حد للتوضيح. جميع البضائع على يسار نقطة تقاطع هذا الخط مع الرسم البياني للأرباح المتراكمة تجلب لنا 80٪ من المال ، وكل البضائع على اليمين - النسبة المتبقية 20٪.
دعونا نرى كيفية إنشاء مخطط باريتو في Microsoft Excel بنفسك.
الخيار 1. مخطط باريتو بسيط يعتمد على بيانات جاهزة
إذا كانت بيانات المصدر تأتي إليك في شكل جدول مشابه (أي ، في شكل نهائي بالفعل):
... ثم نقوم بما يلي.
قم بفرز الجدول بترتيب تنازلي للربح (علامة التبويب البيانات - الفرز) وأضف عمودًا مع صيغة حساب النسبة المئوية المتراكمة للأرباح:
تقسم هذه الصيغة إجمالي الربح المتراكم من بداية القائمة إلى العنصر الحالي على إجمالي الربح للجدول بأكمله. نضيف أيضًا عمودًا ثابتًا بنسبة 80٪ لإنشاء خط متقطع عتبة أفقية في المخطط المستقبلي:
نختار جميع البيانات وننشئ مدرج تكراري منتظم في علامة التبويب إدراج - مدرج تكراري (إدراج - مخطط عمودي). يجب أن يبدو مثل هذا:
يجب إرسال سلسلة النسبة المئوية في المخطط الناتج على طول المحور الثانوي (الأيمن). للقيام بذلك ، تحتاج إلى تحديد الصفوف باستخدام الماوس ، ولكن قد يكون ذلك صعبًا ، حيث يصعب رؤيتها على خلفية أعمدة الأرباح الكبيرة. لذلك من الأفضل استخدام القائمة المنسدلة في علامة التبويب للتمييز تصميم or شكل:
ثم انقر بزر الماوس الأيمن فوق الصف المحدد وحدد الأمر تنسيق سلسلة البيانات وفي النافذة التي تظهر ، حدد الخيار على المحور الثانوي (المحور الثانوي). نتيجة لذلك ، سيبدو مخططنا كما يلي:
بالنسبة إلى سلسلة حصة الأرباح المتراكمة والعتبة ، تحتاج إلى تغيير نوع الرسم البياني من أعمدة إلى خطوط. للقيام بذلك ، انقر فوق كل من هذه الصفوف وحدد الأمر تغيير نوع مخطط السلسلة.
كل ما تبقى هو تحديد صف العتبة الأفقي وتنسيقه بحيث يبدو كخط قطع بدلاً من البيانات (على سبيل المثال ، إزالة العلامات ، وجعل الخط متقطعًا باللون الأحمر ، وما إلى ذلك). كل هذا يمكن القيام به عن طريق النقر بزر الماوس الأيمن على الصف واختيار الأمر تنسيق سلسلة البيانات. الآن سيأخذ المخطط شكله النهائي:
وفقًا لذلك ، يمكننا أن نستنتج أن 80٪ من الأرباح تأتي من أول 5 سلع ، وأن جميع السلع الأخرى على يمين البطاطس تمثل 20٪ فقط من الربح.
في Excel 2013 ، يمكنك القيام بذلك بشكل أسهل - استخدم نوع مخطط التحرير والسرد المدمج الجديد فورًا عند التخطيط:
الخيار 2: مخطط PivotTable و Pivot Pareto
ماذا تفعل إذا لم تكن هناك بيانات جاهزة للبناء ، ولكن فقط المعلومات الأولية الأصلية؟ لنفترض أنه في البداية لدينا جدول ببيانات المبيعات مثل هذا:
لإنشاء مخطط باريتو عليه ومعرفة المنتجات الأكثر مبيعًا ، سيتعين عليك أولاً تحليل بيانات المصدر. أسهل طريقة للقيام بذلك هي باستخدام جدول محوري. حدد أي خلية في الجدول المصدر واستخدم الأمر إدراج - جدول محوري (إدراج - جدول محوري). في النافذة الوسيطة التي تظهر ، لا تغير أي شيء وانقر OK، ثم في اللوحة التي تظهر على اليسار ، اسحب حقول بيانات المصدر من أعلى إلى المناطق السفلية لتخطيط الجدول المحوري المستقبلي:
يجب أن تكون النتيجة جدولًا موجزًا بإجمالي الإيرادات لكل منتج:
قم بفرزها بترتيب تنازلي للإيرادات عن طريق تعيين الخلية النشطة على العمود المبلغ في حقل الإيرادات واستخدام زر الفرز От Я до А (من ي إلى أ) علامة التبويب البيانات.
نحتاج الآن إلى إضافة عمود محسوب بأرباح الفوائد المتراكمة. للقيام بذلك ، اسحب الحقل مرة أخرى ربح في المنطقة القيم في الجزء الأيسر للحصول على عمود مكرر في المحور. ثم انقر بزر الماوس الأيمن فوق العمود المستنسخ واختر الأمر العمليات الحسابية الإضافية -٪ من الإجمالي الجاري في الحقل (إظهار البيانات باسم - النسبة المئوية للإجمالي الجاري). في النافذة التي تظهر ، حدد الحقل الاسم، والتي ستتراكم عليها النسبة المئوية للإيرادات من أعلى إلى أسفل. يجب أن يبدو الإخراج مثل هذا الجدول:
كما ترى ، هذه طاولة جاهزة تقريبًا من الجزء الأول من المقالة. إنه يفتقر فقط للسعادة الكاملة إلى عمود بقيمة حد 80٪ لإنشاء خط قطع في مخطط مستقبلي. يمكن بسهولة إضافة هذا العمود باستخدام حقل محسوب. قم بتمييز أي رقم في الملخص ثم انقر فوق علامة التبويب الصفحة الرئيسية - إدخال - حقل محسوب (الصفحة الرئيسية - إدخال - حقل محسوب). في النافذة التي تفتح ، أدخل اسم الحقل وصيغته (ثابت في حالتنا):
بعد النقر على OK سيُضاف عمود ثالث إلى الجدول بقيمة 80٪ في جميع الخلايا ، وسيتخذ الشكل المطلوب أخيرًا. ثم يمكنك استخدام الأمر مخطط بياني محوري (مخطط بياني محوري) علامة التبويب المعلمات (خيارات) or تحليل الأداء (التحليلات) وقم بإعداد المخطط بنفس طريقة الخيار الأول تمامًا:
إبراز المنتجات الرئيسية
لإبراز العوامل الأكثر تأثيراً ، أي الأعمدة الموجودة على يسار نقطة التقاطع لمنحنى الفائدة المتراكم البرتقالي مع خط القطع الأفقي بنسبة 80٪ يمكن تمييزها. للقيام بذلك ، سيتعين عليك إضافة عمود آخر إلى الجدول بالصيغة:
تنتج هذه الصيغة 1 إذا كان المنتج على يسار نقطة التقاطع و 0 إذا كان على اليمين. ثم عليك القيام بما يلي:
- نضيف عمودًا جديدًا إلى الرسم البياني - أسهل طريقة للقيام بذلك هي النسخ البسيط ، أي تمييز العمود الإضاءة الخلفية، انسخه (CTRL + C) ، حدد الرسم التخطيطي وأدخل (CTRL + V).
- حدد الصف المضاف وقم بتبديله على طول المحور الثانوي ، كما هو موضح أعلاه.
- نوع مخطط السلسلة الإضاءة الخلفية التغيير إلى الأعمدة (المدرج التكراري).
- نقوم بإزالة الخلوص الجانبي في خصائص الصف (انقر بزر الماوس الأيمن على الصف الإضاءة - تنسيق الصف - الفجوة الجانبية) بحيث يتم دمج الأعمدة في كل واحد.
- نزيل حدود الأعمدة ، ونجعل التعبئة شفافة.
نتيجة لذلك ، نحصل على تسليط الضوء على أفضل المنتجات:
PS
بدءًا من Excel 2016 ، تمت إضافة مخطط Pareto إلى المجموعة القياسية من مخططات Excel. الآن ، لإنشائه ، ما عليك سوى تحديد النطاق وعلى علامة التبويب إدراج (إدراج) اختر النوع المناسب:
نقرة واحدة - والمخطط جاهز:
- كيفية إنشاء تقرير باستخدام جدول محوري
- قم بإعداد العمليات الحسابية في PivotTables
- الجديد في المخططات في Excel 2013
- مقالة ويكيبيديا عن قانون باريتو