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