یکی از روشهای کلیدی مدیریت و لجستیک ، تجزیه و تحلیل ABC است. با کمک آن می توانید منابع شرکت ، کالاها ، مشتریان و غیره را طبقه بندی کنید. بر حسب درجه اهمیت. در عین حال ، با توجه به سطح اهمیت ، به هر یک از واحدهای فوق یکی از سه دسته اختصاص داده شده است: A ، B یا C. Excel در ابزارهای چمدانی خود قرار دارد که انجام این نوع تحلیل را آسانتر می کند. بیایید نحوه استفاده از آنها و تجزیه و تحلیل ABC را کشف کنیم.
با استفاده از تجزیه و تحلیل ABC
تجزیه و تحلیل ABC نوعی بهبود یافته و سازگار با نسخه های شرایط مدرن از اصل پارتو است. با توجه به روش اجرای آن ، تمام عناصر تجزیه و تحلیل با توجه به درجه اهمیت به سه دسته تقسیم می شوند:
- دسته الف - عناصر موجود در کل بیش از 80% وزن مخصوص؛
- دسته ب - عناصری که ترکیب آنها از 5% قبل 15% وزن مخصوص؛
- دسته ج - عناصر باقیمانده ، که ترکیب کل آنها است 5% و وزن مخصوص کمتری
برخی از شرکت ها تکنیک های پیشرفته تری را به کار می برند و عناصر را در 3 یا 4 یا 5 گروه تقسیم می کنند ، اما ما به طرح کلاسیک تجزیه و تحلیل ABC اعتماد خواهیم کرد.
روش 1: تجزیه و تحلیل مرتب سازی
در اکسل ، تجزیه و تحلیل ABC با استفاده از مرتب سازی انجام می شود. همه موارد از بزرگترین تا کوچکترین طبقه بندی شده اند. سپس ، وزن مخصوص تجمعی هر عنصر محاسبه می شود ، بر اساس آن دسته خاصی به آن اختصاص می یابد. بیایید با استفاده از یک مثال خاص ، نحوه استفاده این تکنیک در عمل را دریابیم.
ما یک جدول با لیستی از کالاهایی که شرکت به فروش می رساند و میزان مربوط به درآمد حاصل از فروش آنها برای مدت زمانی معینی داریم. در پایین جدول ، کل درآمد کلیه اقلام کالا ضربه خورده است. وظیفه ، با استفاده از تجزیه و تحلیل ABC ، تقسیم این محصولات با توجه به اهمیت آنها برای شرکت در گروه ها است.
- جدول را با مکان نما داده ، با نگه داشتن دکمه سمت چپ ماوس ، به جز هدر و ردیف آخر ، انتخاب کنید. برو به برگه "داده". روی دکمه کلیک کنید "مرتب سازی"واقع در بلوک ابزار مرتب سازی و فیلتر کردن روی نوار
شما همچنین می توانید متفاوت عمل کنید. محدوده فوق جدول را انتخاب کنید ، سپس به برگه بروید "خانه" و روی دکمه کلیک کنید مرتب سازی و فیلتر کردنواقع در بلوک ابزار "ویرایش" روی نوار لیستی فعال می شود که در آن موقعیتی را در آن انتخاب می کنیم. مرتب سازی سفارشی.
- هنگام اعمال هر یک از اقدامات فوق ، پنجره تنظیمات مرتب سازی راه اندازی می شود. ما طوری نگاه می کنیم که در اطراف پارامتر قرار دارد "اطلاعات من حاوی سرصفحه ها" یک علامت چک تنظیم شده است در صورت عدم وجود آن ، نصب کنید.
در زمینه ستون نام ستون حاوی داده های درآمد را نشان دهید.
در زمینه "مرتب سازی" شما باید طبق چه معیار خاصی مرتب سازی را مشخص کنید. ما تنظیمات از پیش تعریف شده را رها می کنیم - "ارزشها".
در زمینه "سفارش" تعیین موقعیت "نزولی".
پس از انجام تنظیمات مشخص شده ، روی دکمه کلیک کنید "خوب" در پایین پنجره
- پس از انجام عمل مشخص شده ، همه عناصر با درآمد از بزرگترین تا کوچکترین طبقه بندی شدند.
- حال باید وزن مخصوص هر یک از عناصر را برای کل محاسبه کنیم. ما یک ستون اضافی برای این اهداف ایجاد می کنیم ، که به آنها می خواهیم "وزن مخصوص". در سلول اول این ستون ، یک علامت بگذارید "="و پس از آن پیوندی به سلول نشان می دهیم که میزان درآمد حاصل از فروش محصول مربوطه در آن واقع شده است. بعد ، علامت تقسیم را تنظیم کنید ("/") پس از آن مختصات سلول را نشان دهید ، که شامل کل فروش کالاها در سراسر شرکت است.
با توجه به اینکه فرمول مشخص شده را در سلولهای دیگر در ستون کپی خواهیم کرد "وزن مخصوص" با استفاده از نشانگر fill ، باید آدرس لینک مربوط به عنصر حاوی کل درآمد حاصل از شرکت را تصحیح کنیم. برای انجام این کار ، لینک را مطلق کنید. مختصات سلول مشخص شده را در فرمول انتخاب کرده و کلید را فشار دهید F4. در مقابل مختصات ، همانطور که می بینیم ، یک علامت دلار ظاهر شد که نشان می دهد این لینک مطلق شده است. لازم به ذکر است که پیوند به ارزش درآمد اولین مورد موجود در لیست (محصول 3) باید نسبی بماند.
سپس برای انجام محاسبات بر روی دکمه کلیک کنید وارد شوید.
- همانطور که مشاهده می کنید ، نسبت درآمد اولین محصول ذکر شده در لیست در سلول هدف نمایش داده می شود. برای کپی کردن فرمول در دامنه زیر ، مکان نما را در گوشه سمت راست پایین سلول قرار دهید. آن را به یک نشانگر پر کننده تبدیل می کند که مانند یک صلیب کوچک است. بر روی دکمه سمت چپ ماوس کلیک کنید و نشانگر پر را به انتهای ستون بکشید.
- همانطور که مشاهده می کنید ، کل ستون با داده هایی که سهم درآمد حاصل از فروش هر محصول را مشخص می کنند ، پر شده است. اما وزن مخصوص به صورت عددی نمایش داده می شود و ما باید آن را به یک درصد تبدیل کنیم. برای این کار محتویات ستون را انتخاب کنید "وزن مخصوص". سپس به سمت برگه حرکت می کنیم "خانه". در گروه تنظیمات روی روبان قرار بگیرید "شماره" فیلدی وجود دارد که فرمت داده را نشان می دهد. به طور پیش فرض ، اگر دستکاری های اضافی انجام ندادید ، فرمت باید در آنجا تنظیم شود "عمومی". ما بر روی آیکون به شکل مثلث واقع در سمت راست این قسمت کلیک می کنیم. در لیست قالب هایی که باز می شود ، موقعیت را انتخاب کنید "علاقه".
- همانطور که می بینید ، تمام مقادیر ستون به مقادیر درصد تبدیل شده است. همانطور که انتظار می رود ، در خط "کل" نشان داده شده است 100%. پیش بینی می شود که نسبت کالا در ستون از بزرگتر تا کوچکتر باشد.
- حال باید ستونی ایجاد کنیم که در آن سهم جمع شده با جمع تجمعی نمایش داده شود. یعنی در هر ردیف ، وزن مخصوص یک محصول خاص وزن مخصوص همه آن دسته از محصولاتی را که در لیست بالا قرار دارند اضافه می کند. برای اولین مورد در لیست (محصول 3) وزن مخصوص فرد و سهم انباشته مساوی خواهد بود ، اما برای همه موارد بعدی ، سهم انباشته شده از عنصر قبلی لیست باید به شاخص فردی اضافه شود.
بنابراین ، در ردیف اول به ستون حرکت می کنیم اشتراک انباشته نشانگر ستون "وزن مخصوص".
- بعد ، مکان نما را بر روی سلول دوم در ستون تنظیم کنید. اشتراک انباشته. در اینجا ما باید فرمول را اعمال کنیم. ما نشانه ای می گذاریم برابر است و محتویات سلول را اضافه کنید "وزن مخصوص" همان سطر و محتوای سلول اشتراک انباشته از خط بالا همه پیوندها را به صورت نسبی می گذاریم ، یعنی آنها را دستکاری نمی کنیم. پس از آن بر روی دکمه کلیک کنید وارد شوید برای نمایش نتیجه نهایی
- حال باید این فرمول را در سلولهای این ستون که در زیر قرار دارد کپی کنید. برای انجام این کار ، از نشانگر fill استفاده کنید ، که قبلاً هنگام کپی کردن فرمول در ستون ، به آن متوسل شده ایم "وزن مخصوص". در این حالت ، خط "کل" بدون نیاز به ضبط ، از آنجا که نتیجه انباشته در 100% در آخرین مورد از لیست نمایش داده می شود. همانطور که می بینید ، تمام عناصر ستون ما پس از آن پر شد.
- پس از آن یک ستون ایجاد می کنیم "گروه". ما نیاز به گروه بندی محصولات به دسته ها داریم الف, ب و ج با توجه به سهم انباشته نشان داده شده همانطور که به یاد می آوریم ، تمام عناصر طبق طرح زیر در گروه ها توزیع می شوند:
- الف - به 80%;
- ب - موارد زیر 15%;
- با - باقی مانده است 5%.
بنابراین ، برای همه کالاها ، سهم انباشته شده از وزن مخصوص آن تا مرز در آن گنجانده شده است 80%دسته اختصاص دهید الف. کالاهای دارای وزن مخصوص 80% قبل 95% دسته اختصاص دهید ب. گروه محصول باقیمانده با ارزش بیشتر از 95% دسته اختصاص وزن اختصاص داده شده ج.
- برای وضوح ، می توانید این گروه ها را با رنگ های مختلف پر کنید. اما این اختیاری است.
بنابراین ، ما با استفاده از تجزیه و تحلیل ABC ، عناصر را با توجه به سطح اهمیت به گروه ها تقسیم کردیم. هنگام استفاده از برخی تکنیک های دیگر ، همانطور که قبلاً نیز ذکر شد ، از تقسیم به تعداد بیشتری گروه استفاده می شود ، اما اصل تقسیم تقریباً بدون تغییر باقی می ماند.
درس: اکسل مرتب سازی و فیلتر
روش 2: از یک فرمول پیچیده استفاده کنید
البته استفاده از مرتب سازی رایج ترین روش برای انجام تجزیه و تحلیل ABC در اکسل است. اما در برخی موارد ، لازم است بدون تجزیه و تحلیل مجدد سطرها در جدول اصلی ، این تحلیل را انجام دهید. در این حالت ، یک فرمول پیچیده برای نجات در می آید. به عنوان نمونه ، ما از همان جدول مبدأ استفاده می کنیم که در مورد اول است.
- به جدول اصلی که حاوی نام کالا و درآمد حاصل از فروش هرکدام از آنها است ، یک ستون اضافه کنید "گروه". همانطور که مشاهده می کنید در این حالت با محاسبه سهام های انفرادی و انباشته نمی توان ستونهایی را اضافه کرد.
- اولین سلول موجود در ستون را انتخاب کنید "گروه"و سپس بر روی دکمه کلیک کنید "درج عملکرد"واقع در نزدیکی خط فرمول ها.
- فعال سازی در حال انجام است جادوگران عملکرد. ما به سمت دسته حرکت می کنیم منابع و آرایه ها. عملکرد را انتخاب کنید "انتخاب". روی دکمه کلیک کنید "خوب".
- پنجره آرگومان عملکرد فعال می شود. انتخاب. نحو آن به شرح زیر ارائه می شود:
= SELECT (Index_number؛ Value1؛ Value2؛ ...)هدف این تابع بسته به شماره فهرست ، خروجی یکی از مقادیر مشخص شده است. تعداد مقادیر می تواند به 254 برسد ، اما ما فقط به سه نام نیاز داریم که با دسته های تجزیه و تحلیل ABC مطابقت دارند: الف, ب, با. ما می توانیم بلافاصله وارد میدان شویم "Value1" نماد "الف"در این زمینه "Value2" - "ب"در این زمینه "Value3" - "ج".
- اما با یک استدلال شماره فهرست شما باید با ادغام چند اپراتور اضافی در آن ، به طور کامل از آن استفاده کنید. مکان نما را در قسمت زمینه تنظیم کنید شماره فهرست. در مرحله بعد بر روی آیکون به شکل مثلث در سمت چپ دکمه کلیک کنید "درج عملکرد". لیستی از اپراتورهای اخیراً استفاده شده باز می شود. ما به یک عملکرد نیاز داریم جستجو. از آنجا که در لیست نیست ، پس بر روی کتیبه کلیک کنید "سایر ویژگی ها ...".
- پنجره دوباره شروع می شود. جادوگران عملکرد. باز هم به دسته می رویم منابع و آرایه ها. موقعیتی را در آنجا پیدا کنید "جستجو"آن را انتخاب کرده و بر روی دکمه کلیک کنید "خوب".
- پنجره Argument Operator باز می شود جستجو. نحو آن به شرح زیر است:
= SEARCH (Seaded_value؛ Viewed_array؛ Match_type)هدف از این تابع تعیین موقعیت موقعیتی عنصر مشخص شده است. یعنی دقیقاً همان چیزی است که ما برای این زمینه لازم داریم شماره فهرست توابع انتخاب.
در زمینه آرایه را مشاهده کرد بلافاصله می توانید عبارت زیر را مشخص کنید:
{0:0,8:0,95}این باید در براکت های فرفری باشد ، به عنوان فرمول آرایه ای. حدس زدن که این اعداد (0; 0,8; 0,95) مرزهای سهم انباشته شده بین گروهها را مشخص کنید.
میدان نوع مسابقه اختیاری است و در این حالت ما آن را پر نمی کنیم.
در زمینه "به دنبال ارزش" مکان نما را تنظیم کنید سپس مجدداً از طریق تصویری فوق به شکل مثلثی که به سمت آن حرکت می کنیم جادوگر ویژگی.
- این بار در جادوگر عملکرد حرکت به دسته "ریاضی". یک نام انتخاب کنید خلاصه و روی دکمه کلیک کنید "خوب".
- پنجره آرگومان عملکرد شروع می شود خلاصه. عملگر مشخص شده سلول هایی را که یک شرایط خاص را برآورده می کنند جمع می کند. نحو آن عبارت است از:
= SUMMES (دامنه ؛ معیار ؛ sum_range)در زمینه "محدوده" آدرس ستون را وارد کنید "درآمد". برای این اهداف ، مکان نما را در قسمت تنظیم کنید و سپس با نگه داشتن دکمه سمت چپ ماوس ، تمام سلول ها را در ستون مربوطه انتخاب کنید ، به استثنای مقدار "کل". همانطور که مشاهده می کنید ، آدرس بلافاصله در قسمت نمایش داده شد. علاوه بر این ، ما باید این لینک را مطلق کنیم. برای انجام این کار ، آن را انتخاب کرده و کلید را فشار دهید F4. آدرس با علائم دلار مشخص شد.
در زمینه "ملاک" ما باید شرط بگذاریم عبارت زیر را وارد می کنیم:
">"&سپس بلافاصله پس از آن آدرس سلول اول ستون را وارد می کنیم "درآمد". ما با اضافه کردن یک علامت دلار از صفحه کلید در جلوی متن ، مختصات افقی را در این آدرس می سازیم. مختصات عمودی را به صورت نسبی می گذاریم ، یعنی نباید جلوی رقمی هیچ علامتی وجود داشته باشد.
پس از آن بر روی دکمه کلیک نکنید "خوب"، و بر روی نام عملکرد کلیک کنید جستجو در نوار فرمول
- سپس به پنجره argument function باز می گردیم جستجو. همانطور که مشاهده می کنید ، در این زمینه "به دنبال ارزش" داده ها تنظیم شده توسط اپراتور ظاهر شد خلاصه. اما این همه چیز نیست. به این قسمت بروید و علامت را به داده های موجود اضافه کنید. "+" بدون نقل قول سپس آدرس سلول اول ستون را وارد می کنیم "درآمد". و باز هم ، مختصات افقی این لینک را مطلق می سازیم ، و آنها را به صورت نسبی قرار می دهیم.
در مرحله بعد ، کل مطالب این قسمت را بگیرید "به دنبال ارزش" در براکت ها ، و بعد از آن ما علامت تقسیم را قرار می دهیم ("/") پس از آن ، دوباره از طریق نماد مثلث ، به پنجره انتخاب عملکرد بروید.
- مثل آخرین بار در اجرا جادوگر عملکرد به دنبال اپراتور مورد نظر در این گروه هستید "ریاضی". این بار تابع مورد نظر گفته می شود خلاصه. آن را انتخاب کرده و بر روی دکمه کلیک کنید. "خوب".
- پنجره Argument Operator باز می شود خلاصه. هدف اصلی آن جمع بندی داده ها در سلول است. نحو این جمله بسیار ساده است:
= SUM (Number1؛ Number2؛ ...)برای اهداف ما فقط یک زمینه لازم است "شماره 1". مختصات محدوده ستون را در آن وارد کنید. "درآمد"به استثنای سلول که حاوی مقادیر است. ما قبلاً عملیاتی مشابه را در این زمینه انجام دادیم "محدوده" توابع خلاصه. مانند آن زمان ، با انتخاب آنها و فشار دادن کلید ، مختصات دامنه را مطلق می سازیم F4.
پس از آن بر روی دکمه کلیک کنید "خوب" در پایین پنجره
- همانطور که مشاهده می کنید ، مجموعه توابع معرفی شده محاسبه را انجام داده و نتیجه را به سلول اول ستون باز می گرداند "گروه". اولین محصول به یک گروه اختصاص یافت "الف". فرمول کاملی که برای این محاسبه استفاده کردیم به شرح زیر است:
= SELECT (جستجو ((خلاصه ($ B $ 2: $ B $ 27؛ ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B 27 $) ؛ {0: 0.8: 0.95 )؛ "A"؛ "B"؛ "C")اما مطمئناً در هر حالت مختصات موجود در این فرمول متفاوت خواهد بود. بنابراین ، نمی توان آن را جهانی شمرد. اما با استفاده از راهنمایی های ذکر شده در بالا می توانید مختصات هر جدول را درج کرده و در هر شرایطی این روش را با موفقیت بکار بگیرید.
- با این حال ، این همه نیست. ما محاسبه را فقط برای ردیف اول جدول انجام دادیم. به منظور جمع آوری کامل ستون با داده ها "گروه"، شما باید این فرمول را در محدوده زیر کپی کنید (به استثنای سلول ردیف "کل") با استفاده از نشانگر پر ، همانطور که بیش از یک بار انجام داده ایم. پس از وارد کردن داده ها ، تجزیه و تحلیل ABC می تواند در نظر گرفته شود.
همانطور که مشاهده می کنید ، نتایج به دست آمده با استفاده از گزینه با استفاده از فرمول پیچیده به هیچ وجه با نتیجه ای که با مرتب سازی انجام داده ایم متفاوت نیست. همه محصولات به همین دسته بندی اختصاص می یابند ، اما خطوط موقعیت شروع آنها را تغییر نداده است.
درس: جادوگر ویژگی های اکسل
اکسل می تواند تجزیه و تحلیل ABC را برای کاربر بسیار تسهیل کند. این با استفاده از ابزاری مانند مرتب سازی حاصل می شود. پس از این ، وزن مخصوص فرد ، سهم انباشته شده و در حقیقت تقسیم به گروهها محاسبه می شود. در مواردی که تغییر موقعیت اولیه سطرها در جدول مجاز نیست ، می توانید این روش را با استفاده از فرمول پیچیده اعمال کنید.
