کاربرد تجزیه و تحلیل ABC در مایکروسافت اکسل

یکی از روش های کلیدی مدیریت و تدارکات، تحلیل ABC است. با استفاده از آن می توانید منابع سازمانی، محصولات، مشتریان و غیره را دسته بندی کنید. به ترتیب اهمیت. در عین حال، با توجه به سطح اهمیت، هر یک از واحد های فوق ذکر شده به یکی از سه طبقه بندی تقسیم می شود: A، B، یا C. اکسل ابزار در چمدان خود را که انجام این نوع تجزیه و تحلیل آسان تر است. بیایید ببینیم چگونه از آنها استفاده کنیم و تحلیل ABC چیست.

با استفاده از تجزیه و تحلیل ABC

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

• رده A - عناصری که بیش از 80٪ از وزن خاص دارند؛
• رده B - عناصر، مجموعی که از 5٪ تا 15٪ از وزن مخصوص متغیر است؛
• رده C - عناصر باقی مانده، کل مجموع 5٪ یا کمتر از وزن خاص است.

برخی از شرکت ها از تکنیک های پیشرفته تر استفاده می کنند و عناصر را به 3، اما به 4 یا 5 گروه تقسیم می کنند، اما ما بر روی طرح کلاسیک تجزیه و تحلیل ABC تکیه می کنیم.

روش 1: تجزیه و تحلیل با مرتب سازی

در Excel، تجزیه و تحلیل ABC با استفاده از مرتب سازی انجام می شود. همه اقلام از بزرگتر به کوچکتر طبقه بندی شده اند. سپس وزن ویژه تجمعی هر عنصر محاسبه می شود، بر اساس آن یک دسته خاص به آن اختصاص داده می شود. بیایید از یک مثال خاص برای پیدا کردن نحوه استفاده از این روش در عمل استفاده کنیم.

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

1. جدول با مکان نما داده را انتخاب کنید، دکمه سمت چپ ماوس را نگه دارید، به غیر از هدر و ردیف نهایی. به برگه "Data" بروید. بر روی دکمه "مرتب سازی" واقع در نوار ابزار مرتب سازی و فیلتر بر روی نوار کلیک کنید.

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

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

   در قسمت "Column" نام ستون حاوی اطلاعات مربوط به درآمد را نشان می دهد.

   در فیلد "مرتب سازی" شما باید مشخص کنید کدام معیارهای خاص مرتب سازی انجام می شود. ما تنظیمات از پیش تعیین شده - "ارزش ها" را ترک می کنیم.

   در قسمت "Order" موقعیت "نزولی" را تنظیم کنید .

   پس از ساختن تنظیمات مشخص شده، روی دکمه "OK" در پایین پنجره کلیک کنید.

3. پس از انجام اقدام مشخص شده، تمام موارد توسط درآمد از بالاترین به پایین ترین رتبه بندی شد.
4. حالا ما باید نسبت هر یک از عناصر را برای کل محاسبه کنیم. ما برای این منظور یک ستون اضافی ایجاد می کنیم که ما آن را "وزن ویژه" می نامیم. در سلول اول این ستون، علامت "=" را قرار می دهیم ، پس از آن ما اشاره به سلول حاوی مقدار درآمد حاصل از فروش محصول مربوطه است. بعد، علامت تقسیم ( "/" ) را تنظیم کنید. پس از آن ما مختصات سلول را نشان می دهیم که شامل کل مقدار فروش کالاها در سراسر شرکت می باشد.

   با در نظر گرفتن این واقعیت که ما فرمول نشان داده شده را به سلولهای دیگر ستون «وزن مخصوص» با استفاده از نشانگر پر کردن کپی می کنیم، باید آدرس لینک را به عنصر حاوی کل درآمد برای شرکت اصلاح کنیم. برای انجام این کار لینک را مطلق کنید. مختصات سلول مشخص شده در فرمول را انتخاب کنید و کلید F4 را فشار دهید. همانطور که می بینیم، علامت دلار در مقابل مختصات نمایش داده می شود، که نشان می دهد که لینک به مطلق تبدیل شده است. لازم به ذکر است که ارجاع به مقدار درآمد اولین مورد در لیست ( ماده 3 ) باید نسبی باشد.

   سپس، برای محاسبه، بر روی دکمه Enter کلیک کنید.

5. همانطور که می بینید، نسبت درآمد از اولین مورد ذکر شده در سلول هدف نمایش داده می شود. برای ساخت یک کپی از فرمول در دامنه زیر، مکان نما را در گوشه پایین سمت راست سلول قرار دهید. این به یک نشانگر پر می شود که به نظر می رسد مثل یک کراس کوچک تبدیل شده است. با کلیک بر روی دکمه سمت چپ ماوس و کشیدن دسته پر کردن به انتهای ستون.
6. همانطور که می بینید، کل ستون با اطلاعاتی که سهم درآمد حاصل از فروش هر محصول را مشخص می کند پر می شود. اما مقدار وزن مخصوص در قالب عددی نمایش داده می شود و ما باید آن را به یک درصد تبدیل کنیم. برای انجام این کار، محتویات ستون "weight" را انتخاب کنید . سپس به تب خانه بروید روی نوار در گروه تنظیمات "Number" فیلمی وجود دارد که فرمت داده را نمایش می دهد. به طور پیش فرض، اگر شما هیچگونه دستکاری اضافی را انجام ندهید، فرمت باید بر روی "General" تنظیم شود . ما روی آیکون در شکل یک مثلث واقع در سمت راست این فیلد کلیک میکنیم. در لیست فرمت هایی که باز می شود، موقعیت "درصد" را انتخاب کنید.
7. همانطور که می بینید، تمام مقادیر ستون به درصد تبدیل می شوند. همانطور که باید باشد، 100٪ در خط "مجموع" نشان داده شده است. انتظار می رود سهم کالا در ستون از بزرگتر تا کوچکتر قرار گیرد.
8. حالا ما باید یک ستون ایجاد کنیم که در آن سهم تجمعی با مجموع تجمعی نمایش داده می شود. بدین معنی است که در هر ردیف، وزن خاصی از همه کالاها که در لیست بالا قرار دارند، به وزن فردی یک محصول خاص افزوده می شود. برای اولین مورد در لیست ( ماده 3 )، وزن خاص فرد و سهم انباشته برابر خواهد بود، اما برای همه موارد بعدی، سهم انباشته از آیتم قبلی در لیست باید به شاخص فرد اضافه شود.

   بنابراین، در ردیف اول ما شاخص را از ستون "وزن" به ستون "Accumulated Share" انتقال می دهیم.

9. بعد، مکان نما را در سلول دوم ستون «Accumulated Share» قرار دهید. در اینجا ما باید این فرمول را اعمال کنیم. ما علامت "برابر" را قرار داده و محتویات سلول "وزن مخصوص" همان خط و محتویات سلول "سهم" "Accumulated share" را از خط بالا اضافه می کنیم. همه لینکها نسبی هستند، به این معنا که ما با آنها دستکاری نداریم. پس از آن، بر روی دکمه Enter کلیک کنید تا نتیجه نهایی را نمایش دهد.
10. حالا شما باید این فرمول را به سلول های این ستون کپی کنید که در زیر قرار دارند. برای انجام این کار، ما از نشانگر پر کننده استفاده می کنیم که قبلا در هنگام کپی کردن فرمول در ستون «وزن» استفاده کردیم . در این مورد، رشته "Total" برای ضبط لازم نیست، زیرا نتیجه انباشته شده از 100٪ در آخرین محصول از لیست نمایش داده می شود. همانطور که می بینید، تمام عناصر ستون ما پس از آن پر شده است.
11. پس از آن یک ستون "گروه" ایجاد کنید . ما باید محصولات را به دسته های A ، B و C بر اساس درصد تجمعی نشان داده کنیم. به یاد داشته باشید، همه عناصر به ترتیب زیر به گروه تقسیم می شوند:
    • A - تا 80٪ ؛
    • ب - 15٪ بعدی؛
    • C - 5٪ باقیمانده.

    بنابراین، برای تمام کالاها، سهم تجمعی وزن مخصوص که به مرز تا 80٪ می رسد ، ما رده A را اختصاص می دهیم . برای محصولات با وزن خاص انباشته از 80٪ تا 95٪، ما به رده B اختصاص می دهیم . گروه باقیمانده با مقدار بیش از 95٪ از وزن مخصوص انباشته شده به رده C اختصاص داده می شود .

12. برای وضوح، می توانید این گروه ها را در رنگ های مختلف پر کنید. اما این اختیاری است.

بنابراین، ما عناصر را با توجه به سطح اهمیت، با استفاده از تجزیه و تحلیل ABC، تقسیم کردیم. هنگام استفاده از برخی از روش های دیگر، همانطور که در بالا ذکر شد، تقسیم بندی را به گروه های بیشتری اعمال کنید، اما اصل تقسیم تقریبا بدون تغییر باقی می ماند.

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

روش 2: با استفاده از یک فرمول پیچیده

البته، استفاده از مرتب سازی رایج ترین روش برای انجام تجزیه و تحلیل ABC در اکسل است. اما در بعضی موارد، لازم است این تحلیل را انجام دهد بدون تغییر سطرها در جدول منبع. در این حالت، یک فرمول پیچیده برای نجات خواهد آمد. به عنوان مثال، از جدول منبع مشابه همانطور که در مورد اول استفاده می کنیم استفاده می کنیم.

1. اضافه کردن به جدول اصلی حاوی نام کالا و درآمد حاصل از فروش هر یک از آنها، ستون "گروه" . همانطور که می بینید، در این مورد ما نمی توانیم ستون ها را با محاسبه سهام فردی و تجمعی اضافه کنیم.
2. اولین سلول در ستون "Group" را انتخاب کنید، سپس روی دکمه "Insert function" واقع در کنار نوار فرمول کلیک کنید.
3. فعال سازی کارکرد اصلی انجام می شود . رفتن به دسته "Links and arrays" . عملکرد "SELECT" را انتخاب کنید . روی دکمه "OK" کلیک کنید.
4. پنجره آرگومان تابع SELECT فعال شده است. نحو آن به شرح زیر است:

   =ВЫБОР(Номер_индекса;Значение1;Значение2;…)

   هدف این تابع، خروجی یکی از مقادیر مشخص شده است، بسته به تعداد شاخص. تعداد مقادیر می تواند به 254 برسد اما ما فقط سه نام داریم که به دسته های تحلیل ABC مربوط می شود: A ، B ، C ما بلافاصله می توانیم نماد "A" را در قسمت "Value1" ، "B" در فیلد Value2 و "C" در قسمت Value3 وارد کنید .

5. اما با استدلال "شماره شاخص" ، باید به طور کامل از بین برود، با چند اپراتور اضافی ساخته شده است. مکان نما را در فیلد "شماره فهرست" تنظیم کنید . بعد، بر روی آیکون کلیک کنید، که شکل یک مثلث است، به سمت چپ دکمه "Insert Function" . یک لیست از اپراتورهای اخیرا استفاده شده باز می شود. ما به تابع MATCH نیاز داریم. از آنجایی که این فهرست نیست، ما روی کتیبه «سایر توابع ...» کلیک میکنیم.
6. جادوگر تابع دوباره راه اندازی می شود. باز هم به دسته "Links and arrays" بروید . ما موقعیت "MATCH" را پیدا می کنیم ، آن را انتخاب می کنیم و یک دکمه "OK" را کلیک می کنیم.
7. پنجره argument از اپراتور MATCH باز می شود . نحو آن به شرح زیر است:

   =ПОИСКПОЗ(Искомое_значение;Просматриваемый_массив;Тип_сопоставления)

   هدف از این تابع تعیین تعداد موقعیت عنصر مشخص شده است. این فقط چیزی است که ما برای فیلد "Index number" از عملکرد SELECT نیاز داریم.

   در فیلد "Array قابل مشاهده" می توانید بلافاصله عبارت زیر را مشخص کنید:

   {0:0,8:0,95}

   این باید دقیقا در اتصالات در حال شکل گیری باشد، مانند یک فرمول آرایه. دشوار است حدس بزنید که این اعداد ( 0 ، 0.8 ، 0.95 ) مرزهای سهم انباشته شده بین گروه ها را نشان می دهند.

   فیلد "نوع تطابق" اختیاری است و در این صورت ما آن را پر نمیکنیم.

   در قسمت Value required مقدار مکان نما را تنظیم کنید. سپس دوباره از طریق نماد فوق در شکل یک مثلث به کارشناسی ارشد توابع حرکت می کنیم.

8. این بار در کارشناسی ارشد توابع به رده "ریاضی" حرکت می کنیم . نام "SUMMESLI" را انتخاب کنید و روی دکمه "OK" کلیک کنید.
9. پنجره استدلال عملکرد SUMMESLI راه اندازی شده است. اپراتور مشخص شده سلول هایی را که شرایط مشخص را برآورده می کنند، جمع می کند. نحو آن:

   =СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

   در قسمت "Range" آدرس آدرس ستون "درآمد" را وارد کنید . برای این منظور، ما مکان را در مکان قرار می دهیم، و سپس، با فشار دادن دکمه سمت چپ ماوس، همه سلول های ستون مربوطه را انتخاب کنید، به غیر از مقدار "مجموع" . همانطور که می بینید، آدرس فورا در این زمینه نمایش داده می شود. علاوه بر این، ما باید این لینک را مطلق ایجاد کنیم. برای انجام این کار، انتخاب خود را انتخاب کرده و روی کلید F4 کلیک کنید. آدرس با نشانه های دلار برجسته شده است.

   در زمینه "معیارها" ما باید یک شرط را تعیین کنیم. عبارت زیر را وارد کنید:

   ">"&

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

   بعد از این، ما بر روی دکمه "OK" کلیک نمی کنیم، اما بر روی نام عملکرد MATCH در نوار فرمول کلیک کنید.

10. سپس به پنجره argument از تابع MATCH بازگشتیم . همانطور که می بینید، داده های مشخص شده توسط اپراتور SUMMESLI در فیلد "مورد نیاز" ظاهر می شود. اما این همه نیست به این فیلد بروید و علامت "+" بدون نقل قول به داده های موجود اضافه کنید. سپس آدرس اول سلول ستون "درآمد" را وارد کنید. و دوباره ما مختصات افقی این لینک را مطلق می گذاریم و عمودی ما نسبی را ترک می کنیم.

    بعد، ما کل محتویات فیلد "مورد نیاز" را در پرانتزها می گیریم، پس از آن یک علامت تقسیم ( "/" ) قرار می دهیم. پس از آن، دوباره از طریق نماد مثلث، به پنجره انتخاب تابع بروید.

11. همانطور که در آخرین بار در کارشناسی ارشد توابع، ما برای اپراتور لازم در رده "ریاضی" را جستجو کنید . در این زمان، تابع مورد نظر "SUM" نامیده می شود. آن را انتخاب کنید و بر روی دکمه "OK" کلیک کنید.
12. پنجره استدلال اپراتور SUMM باز می شود. هدف اصلی آن جمع آوری داده ها در سلول ها است. نحو این اپراتور بسیار ساده است:

    =СУММ(Число1;Число2;…)

    برای اهداف ما، ما فقط به فیلد "شماره 1" نیاز داریم. مختصات محدوده ستون "درآمد" را وارد کنید ، به غیر از سلول که حاوی کل کل است. ما قبلا یک عملیات مشابه در زمینه Range از عملکرد SUMMESLI انجام داده ایم. همانطور که در آن زمان، با انتخاب آنها و با فشار دادن کلید F4 ، مختصات مطلق محدوده را تعیین می کنیم.

    پس از آن روی دکمه «OK» در پایین پنجره کلیک کنید.

13. همانطور که می بینید، مجموعه ای از توابع وارد شده یک محاسبه تولید کرده و نتیجه را در سلول اول ستون "Group" داد . اولین مورد یک گروه A بود . فرمول کامل ما برای این محاسبه استفاده شده است به شرح زیر است:

    =ВЫБОР(ПОИСКПОЗ((СУММЕСЛИ($B$2:$B$27;">"&$B2)+$B2)/СУММ($B$2:$B$27);{0:0,8:0,95});"A";"B";"C")

    اما، البته، در هر مورد، مختصات در این فرمول متفاوت خواهد بود. بنابراین، نمی توان آن را جهانی دانست. اما با استفاده از راهنما که در بالا ذکر شد، می توانید مختصات هر جدول را وارد کنید و با موفقیت در هر شرایطی این روش را اعمال کنید.

14. با این حال، این همه نیست. ما فقط برای اولین ردیف جدول محاسبه کردیم. به منظور تکمیل ستون «گروه» با داده، باید این فرمول را به محدوده زیر (به استثنای سلول خط «مجموع» ) با استفاده از نشانگر پر کنید، همانطور که ما بیش از یک بار انجام داده ایم. پس از وارد شدن داده ها، تجزیه و تحلیل ABC می تواند کامل شود.

همانطور که می بینید، نتایج حاصل از استفاده از یک فرم با استفاده از یک فرمول پیچیده در نتایج حاصل از مرتب سازی انجام نمی شوند. تمام محصولات دارای دسته های مشابه هستند، اما خطوط موقعیت اولیه خود را تغییر نمی دهند.

درس: جادوگر عملکرده اکسل

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

• ← با استفاده از عملکرد SELECT در مایکروسافت اکسل
• تنظیم روز هفته به تاریخ در مایکروسافت اکسل →