نمودار پارتو (Pareto Chart) در اکسل + ویدیو

نمودار یا تحلیل پارتو (Pareto Chart) چیست؟

اگر بخواهیم تعریفی بسیار ساده و عامیانه از تحلیل پارتو داشته باشیم، باید اینگونه بیان کنیم که:

تحلیل پارتو می گوید %۸۰ مشکلات شما، تنها ناشی از %۲۰ عوامل و فاکتورها است.

یا ممکن است به این صورت بیان شود:

تنها با متمرکز شدن بر %۲۰ علل و فاکتورها، می توانید %۸۰ مشکلات خود را برطرف کنید.

موضوع جذابیه درسته؟ این روش با قانون ۸۰/۲۰ هم شناخته می شود. علت نامگذاری این روش با نام پارتو، به خاطر ویلفردو پارتو (Vilfredo Pareto) است که اولین بار توسط این شخص، این قانون کشف شد.

سناریو

فرض کنید اخیراً موفق شده اید شرکتی در زمینه تولید نوشابه های انرژی زا راه اندازی کرده اید و تعدادی کارمند نیز استخدام کرده اید، با امید به اینکه فروشتان روز به روز بیشتر شود. اما می بینید که این اتفاق نمی افتد.

فروش محصولات به آن صورتی که انتظار داشتید، پیش نمی رود. ناامید می شوید، اما حاضر نیستید که شرکت خود را نیز تعطیل کنید. تصمیم می گیرید که بررسی کنید مشکل در کجاست. برای این کار، یک نظرسنجی ترتیب می دهید.

این سوالات را در نظرسنجی خود مطرح می کنید و در اختیار عموم قرار می دهید:

آیا شما نوشابه های ما را می خرید یا خیر؟

1. اگر بله، چرا؟
2. اگر خیر، چرا؟

پس از جمع آوری نظرسنجی ها، این آمار بدست آمده است:

از مجموع ۵۰۰ نفری که رأی داده اند، ۲۵۳ نفر جوابشان “بله” بوده است، به این معنی که آنها از شما نوشابه می خرند. ۲۴۷ نفر هم جوابشان “خیر” بوده است، یعنی به دلایل مختلف از شما نوشابه نمی خرند. تمرکز ما باید بر روی آن ۲۴۷ نفری باشد که از ما نوشابه نمی خرند.

براساس داده های جمع آوری شده، ۱۴ دلیل اصلی برای نخریدن محصولات ما وجود دارد:

چرا برخی از مشتریان از شما نوشابه نمی خرند؟

نحوه پیاده سازی روش تحلیل پارتو در اکسل

گام اول: مرتب سازی داده ها به ترتیب نزولی در اکسل

برای مرتب سازی داده ها، محدوده داده ها را به گونه ای انتخاب کنید که سلول “مجموع” شامل آن نشود. کلیدهای S، S، A، ALT را به ترتیب و یکی یکی فشار دهید (کلیدهای میانبر مرتب سازی داده ها). می توانید همین کار را از طریق موس و منوی Data انجام دهید، اما توصیه می کنم این کلید میانبر را هم یاد بگیرید.

• از منوی کشویی ‘Sort By’، “تعداد رأی ها” را انتخاب کنید.
• از منوی کشویی ‘Sort On’، مقدار “Cell Values” را انتخاب کنید.
• از منوی کشویی ‘Order’، مقدار “Largest to Smallest” را انتخاب کنید.
• و در نهایت کلید OK را فشار دهید.

با این تنظیمات، جدول داده های ما براساس نزولی بودن مرتب می شوند.

گام دوم: یافتن فراوانی تجمعی داده ها

در این بخش می خواهیم یک ستون “فراوانی تجمعی” در کنار ستون “تعداد رأی ها” ایجاد و اینگونه عمل کنیم که عدد بعدی را به مجموع اعداد قبلی اضافه کند.

اولین داده، همان مقدار ۷۰ است که در سلول D3 نوشته می شود. مقدار بعدی که در سلول D4 نوشته می شود، حاصل جمع ۵۰ و ۷۰ است که می شود ۱۲۰. بعدی ۱۲۰ + ۴۵ که می شود ۱۶۵ و این روند را تا پایان ادامه می دهیم.

که نتیجه نهایی کار به این ترتیب خواهد شد:

گام سوم: ساخت ستون درصد تجمعی

این مرحله نیز به سادگی مرحله قبل است. تنها کافی است که مقادیر فراوانی تجمعی را برای هر سلول، بر مجموع کل رأی ها تقسیم کنید. مثلاً در سلول E4 بنویسید:

D3/$C$17 =

دقت داشته باشید که به دلیل اینکه نمی خواهیم مقدار سلول C17 در این فرمولها تغییر کند، آنرا به حالت مطلق درآورده ایم (با گذاشتن $).

و در آخر، با انتخاب ستون تجمعی، همه اعداد را به درصد تبدیل می کنیم و نتیجه به این شکل در می آید:

گام چهارم: اختصاص یک کد به هر کدام از دلایل (اختیاری)

از آنجاییکه محتوای سلول های ستون “دلایل” ممکن است به دلیل طولانی بودن آنها، بعداً در نمودار ما به هم ریختگی ایجاد کند، از این رو به هر کدام از دلایل یک کد اختصاص می دهیم. البته این مرحله اختیاری است و می توانید از آن صرف نظر کنید.

گام پنجم: ترسیم نمودار

ترسیم نمودار پارتو در اکسل ۲۰۱۶ و بالاتر

برای ترسیم نمودار پارتو در اکسل ۲۰۱۶ و بالاتر از آن، نیازی به انجام مراحل بالا نیست و کافی است که همان داده های اولی که نامرتب بودن را انتخاب کنید و از مسیر زیر نمودار خود را ترسیم کنید:

Insert -> Recommended Charts -> All Charts -> Histogram -> Pareto Chart

ترسیم نمودار پارتو در اکسل ۲۰۱۳ و ۲۰۱۰ و نسخه های قدیمی تر

اگر از نسخه ۲۰۱۳ یا ۲۰۱۰ و یا نسخه های پایین تر مایکروسافت استفاده می کنید، باز هم می توان نمودار پارتو را ترسیم کرد. برای این کار، از همان داده های مرتب شده در مراحل قبل استفاده می کنیم. داده های همه ستونها به جز ستون “دلایل” را انتخاب کنید. به آدرس زیر بروید:

Insert -> Line -> Line with Markers

نکته: ما توضیحات مربوط به نسخه های قدیمی را نیز با اکسل ۲۰۱۹ یادداشت برداشته ایم. اگر شما با نسخه های قدیمی کار می کنید، با همین توضیحات بی هیچ کم و کاستی کار کنید و مطمئناً موفق می شوید که نمودار پارتو را ترسیم کنید.

اکنون شما نموداری ترسیم کرده اید که ظاهری شبیه به شکل زیر را دارد. این نمودار چیزی نیست که ما به دنبالش هستیم. پس نمودار را اصلاح می کنیم.

گام ششم: حذف داده های غیرضروری از نمودار

در اینجا ما تنها به ستونهای “تعداد رأی ها” و “درصد تجمعی” نیاز داریم. بنابراین ستون “فراوانی تجمعی” را از نمودار حذف می کنیم. روی نمودار راست کلیک می کنیم و گزینه Select Data را انتخاب می کنیم. پنجره Select Data Source باز می شود.

“فراوانی تجمعی” را انتخاب کرده و دکمه Remove را بزنید. نموداری که تولید می شود، شبیه شکل زیر است. این هم شبیه چیزی نیست که ما می خواهیم.

گام هفتم: ایجاد محور دوم برای ستون “تعداد رأی ها”

روی خط مربوط به “تعداد رأی ها” راست کلیک کنید و گزینه Format Data Series را انتخاب کنید. پنجره ای باز می شود. از این پنجره گزینه Secondary Axis را انتخاب و دکمه Close را بزنید. در حال حاضر، نمودار ما به این شکل درآمده است. تنها قسمتی که باقی مانده این است که نمودار خطی “تعداد رأی ها” را به یک نمودار میله ای تبدیل کنیم.

گام هشتم: تبدیل نمودار خطی “تعداد رأی ها” به نمودار میله ای

روی نمودار خطی “تعداد رأی ها” راست کلیک کنید و گزینه “Change Series Chart Type” را انتخاب کنید. از سمت چپ، نمودار ستونی (Column) را کلیک و در نهایت نمودار Clustered Column را انتخاب و OK کنید. بر روی نمودار راست کلیک کنید و گزینه Add Data Labels را نیز انتخاب کنید.

خسته نباشید. این هم از نمودار پارتو.

اما این نمودار گویای چیست؟ این نمودار نشان می دهد که %۶۷ مشکلات را باید در دلایل ۱، ۲ و ۳ جستجو کرد. این دلایل مربوط به افرادی بود که در زمستان، این نوشیدنی برایشان قابل نوشیدن نبود، برای سلامتی شان ضرر دارد و اطلاعاتی درباره محصول ندارند.

بنابراین اگر شما این سه مشکل را برطرف کنید، قادر خواهید بود %۶۷ مشتری های خود را مجدداً جذب کنید.

در این مقاله، یاد گرفتید که چگونه یک نمودار پارتو رو ترسیم و در نهایت تحلیل کنیم. تلاش کردیم تا قدم به قدم ترسیم این نمودار را به شما آموزش دهیم. امیدواریم که این مقاله نیز برای شما مفید واقع شده باشد.

چناچه سوالی و نظری درباره این مقاله داشتید، خوشحال خواهیم شد که نظرات خودتان را در بخش کامنت ها در پایین همین صفحه برای ما یادداشت کنید.

اگر این مقاله را دوست داشتید، برای دوستانتان هم ارسال کنید.