Приложение на ABC анализ в Microsoft Excel

Един от ключовите методи за управление и логистика е ABC-анализът. С негова помощ можете да класифицирате ресурсите на едно предприятие, продукти, клиенти и т.н. по степен на значимост. В същото време, според нивото на важност, всяка от горните единици е определена една от трите категории: А, Б или В. Excel има в багажа си инструменти, които улесняват извършването на този вид анализ. Нека да разберем как да ги използваме и какво е ABC анализът.

Използване на ABC анализ

ABC-анализът е вид подобрена и адаптирана към съвременните условия версия на принципа на Парето. Според методологията на поведението си всички елементи на анализа са разделени на три категории по отношение на важността:

• Категория А - елементи с общо тегло над 80% от специфичното тегло;
• Категория Б - елементи, чиято съвкупност е от 5% до 15% от специфичното тегло;
• Категория С - останалите елементи, чиято обща съвкупност е 5% и по-малка от специфичното тегло.

Някои компании прилагат по-усъвършенствани техники и разбиват елементите не в 3, а в 4 или 5 групи, но ние ще разчитаме на класическата ABC-схема за анализ.

Метод 1: анализ чрез сортиране

В Excel, ABC анализът се извършва чрез сортиране. Всички елементи са сортирани от по-големи на по-малки. След това се изчислява кумулативното специфично тегло на всеки елемент, на базата на което му се присвоява определена категория. Да видим на конкретен пример как тази техника се прилага на практика.

Имаме таблица със списъка на стоките, които компанията продава, и съответната сума на приходите от продажбата им за определен период от време. В долната част на таблицата се удрят общите приходи за всички имена на продукти. Заслужава си да използваме ABC-анализа, за да разделим тези стоки на групи по важност за предприятието.

1. Изберете таблицата с дадения курсор, като задържите левия бутон на мишката, с изключение на заглавката и последния ред. Отворете раздела "Данни" . Кликнете върху бутона "Сортиране", намиращ се в полето "Сортиране и филтриране" на лентата.

   Можете също така да действате по различен начин. Изберете горния диапазон на таблицата, след това преминете към раздела "Начало" и кликнете върху бутона "Сортиране и филтриране", намиращ се в полето "Редактиране" на лентата. Списъкът е активиран, в който избираме елемента "Потребителски сортиране" в него .

2. Когато приложите някое от гореизброените действия, прозорецът за сортиране се стартира. Да видим, че опцията "Моите данни съдържа заглавията" е отметнато. В случай на отсъствие, ние установяваме.

   В полето "Колона" посочваме името на колоната, която съдържа данни за приходите.

   В полето "Сортиране" трябва да посочите конкретните критерии за сортиране. Оставяме предварително зададените настройки - "Стойности" .

   В полето "Поръчка" задаваме позиция "Descending" .

   След като направите посочените настройки, кликнете върху бутона "OK" в долната част на прозореца.

3. След извършването на посоченото действие всички елементи бяха сортирани според приходите от най-големия към по-малкия.
4. Сега трябва да изчислим специфичното тегло на всеки от елементите за общата сума. За тези цели създаваме допълнителна колона, която ще наричаме "специфично тегло" . В първата клетка на тази колона поставяме знака "=" , след което посочваме връзката към клетката, в която се намира размерът на приходите от продажбата на съответния продукт. След това зададете знака за разделяне ( "/" ). След това задайте координатите на клетката, която съдържа общата сума на продажбите на стоки в предприятието.

   Предвид факта, че ние ще копираме тази формула на други клетки в колоната "Специфично тегло" чрез маркера за попълване, трябва да установим адреса на връзката към елемента, съдържащ общата сума на приходите от предприятието. За това правим референцията абсолютна. Изберете координатите на посочената клетка във формулата и натиснете F4 . Преди координатите, както виждаме, се появи знак за долар, което показва, че връзката е станала абсолютна. Следва да се отбележи, че позоваването на приходите от първата позиция в списъка ( Стоки 3 ) следва да остане относително.

   След това, за да направите изчисленията, натиснете бутона Enter .

5. Както можете да видите, делът на приходите от първия продукт, включен в списъка, е показан в целевата клетка. За да копирате формулата в диапазона по-долу, поставете курсора в долния десен ъгъл на клетката. То се трансформира в маркер за запълване, който прилича на малък кръст. Кликнете с левия бутон на мишката и плъзнете маркера за пълнене надолу до края на колоната.
6. Както можете да видите, цялата колона е пълна с данни, характеризиращи специфичното тегло на приходите от продажбата на всеки продукт. Но стойността на специфичното тегло се показва в цифров формат и трябва да го превърнем в процент. За да направите това, изберете съдържанието на графата "Специфично тегло" . След това преминете към раздела Начална страница. На лентата в групата за настройки "Брой" има поле, което показва формата на данните. По подразбиране, ако не сте извършили допълнителни манипулации, трябва да зададете формат "Общи" . Кликваме върху иконата под формата на триъгълник, намиращ се вдясно от това поле. В отворения списък с формати изберете елемента "Интерес" .
7. Както можете да видите, всички стойности на колоните бяха преобразувани в процентни стойности. Както се очаква, редът "Общо" показва 100% . Специфичното тегло на стоките се очаква да бъде в колоната от най-големия до по-малкия.
8. Сега трябва да създадем колона, в която се показва натрупаната акция с кумулативния общ брой. Това означава, че във всеки ред специфичното тегло на всички стоки, които се намират в списъка по-горе, ще бъде добавено към индивидуалното специфично тегло на конкретна стока. За първия продукт в списъка ( т. 3 ) индивидуалната акция и натрупаната акция ще бъдат равни, но за всички след индивидуалния индикатор ще бъде необходимо да се добави натрупаната част от предишния елемент в списъка.

   Така на първия ред прехвърлете към колоната "Натрупана акция" индикатора от колоната "Специфично тегло" .

9. След това поставете курсора във втората клетка в колоната "Натрупан дял" . Тук трябва да приложим формулата. Поставяме знака "равен" и добавяме съдържанието на клетката "специфично тегло" на същата линия и съдържанието на клетката "Натрупана дял" от горния ред. Всички справки остават относителни, т.е. ние не ги манипулираме. След това кликваме върху бутона Enter, за да покажем крайния резултат.
10. Сега трябва да копирате тази формула в клетките на тази колона, които са поставени по-долу. За тази цел използваме маркера за попълване, към който вече прибягнахме при копиране на формулата в графата "Специфично тегло" . В този случай редът "Общо" не е необходим за улавяне, защото натрупаният резултат в 100% ще бъде показан на последния продукт от списъка. Както можете да видите, всички елементи от нашата колона бяха запълнени след това.
11. След това създайте графата "Групиране" . Ще трябва да групираме стоките по категории A , B и C според натрупания дял. Както си спомняме, всички елементи са групирани по следната схема:
    • A - до 80% ;
    • Б - следващите 15% ;
    • C - останалите 5% .

    По този начин всички стоки, натрупаният дял от специфичното тегло на който влиза в границата до 80% , се определя категория А. Стоки с натрупано специфично тегло от 80% до 95% получават категория Б. Останалата група стоки със стойност над 95% от натрупаното специфично тегло е определена за категория В.

12. За по-голяма яснота можете да запълните тези групи с различни цветове. Но това е по желание.

По този начин ние разделихме елементите на групи според нивото на важност, като използвахме анализа ABC. Използвайки някои други техники, както споменахме по-горе, прилагаме разделянето на повече групи, но принципът на разделяне остава практически непроменен.

Урокът: Сортиране и филтриране в Excel

Метод 2: Използване на сложна формула

Разбира се, използването на сортиране е най-честият начин за извършване на ABC анализ в Excel. Но в някои случаи този анализ се изисква, без да се променят редовете на места в таблицата на източника. В този случай сложна формула ще дойде на помощ. Например, ще използваме същата таблица източник, както в първия случай.

1. Добавяме към първоначалната таблица, съдържаща името на стоките и приходите от продажбата на всяка от тях, в колоната "Група" . Както можете да видите, в този случай не можем да добавим колони с изчисляването на индивидуални и акумулативни акции.
2. Изберете първата клетка в графата "Група" , след което кликнете върху бутона "Вмъкване на функция" до лентата за формули.
3. Активирането на съветника за функции се извършва . Преминаваме в категорията "Референции и масиви" . Изберете функцията "SELECT" . Кликнете върху бутона "OK" .
4. Прозорецът на аргументите на функцията SELECT е активиран. Синтаксисът е както следва:

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

   Задачата на тази функция е да изведе една от зададените стойности в зависимост от индекса. Броят на стойностите може да достигне 254, но се нуждаем само от три имена, които съответстват на категории ABC-анализ: A , B , C. Веднага можем да въведем в полето "Стойност1" символа "А" в полето "Стойност2" - "В" в поле "Стойност3" - "С" .

5. Но с аргумента "Индекс номер" ще трябва да старателно dinker, като вградени в нея няколко допълнителни оператори. Задайте курсора в полето "Индексно число" . След това кликнете върху иконата, която прилича на триъгълник, вляво от бутона "Вмъкване на функция" . Появява се списък с наскоро използвани оператори. Нуждаем се от функцията MATCH . Тъй като списъкът не е, кликваме върху надписа "Други функции ..." .
6. Прозорецът на помощника на функциите се стартира отново. Отново отидете в категорията "Референции и масиви". Намираме там позицията "ТЪРСЕНЕ" , изберете го и кликнете върху бутона "OK" .
7. Отваря прозореца на аргументите на ОПЕРАТОРА . Синтаксисът му е както следва:

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

   Целта на тази функция е да определи номера на позицията на зададения елемент. Това е точно това, от което се нуждаем за полето "Индексно число" на функцията SELECT .

   В полето "Сканиран масив" можете веднага да зададете следния израз:

   {0:0,8:0,95}

   Тя трябва да е в къдрава скоба, като формулата на масив. Не е трудно да се предположи, че тези числа ( 0 ; 0,8 ; 0,95 ) обозначават границите на натрупания дял между групите.

   Типът "Съответстващ тип" е по избор и в този случай няма да го запълним.

   В полето "Стойност за търсене" настройваме курсора. След това отново през триъгълника, показан по-горе, се преместваме към функционалният помощник .

8. Този път в съветника за функция се преместваме в категорията "Математически" . Избираме името "SUMMER" и кликваме върху бутона "OK" .
9. Отваря се прозорецът с аргументи на функцията SUMIFER . Определеният оператор обобщава клетките, които отговарят на определеното условие. Синтаксисът му е следният:

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

   В полето "Обхват" въведете адреса на графата "Приходи" . За тази цел поставете курсора в полето и след като сте притиснали левия бутон на мишката, изберете всички клетки в съответната колона, с изключение на стойността "Общо" . Както можете да видите, адресът се показва незабавно на полето. Освен това трябва да направим тази връзка абсолютна. За да направите това, направете своя избор и кликнете върху клавиша F4 . Адресът се отличаваше с знаци за долар.

   В полето "Критерии" трябва да посочим едно условие. Въвеждаме следния израз:

   ">"&

   След това веднага след това въведете адреса на първата клетка в колоната "Приходи" . Правим хоризонталните координати в този адрес абсолютно, добавяйки знака за долар от клавиатурата преди буквата. Координатите по вертикала са останали относителни, т.е. преди фигурата да няма знак.

   След това не кликвайте върху бутона "OK" , но кликнете върху името на функцията MATCH в лентата за формули.

10. След това се връщаме в прозореца с аргументи на функцията MATCH . Както можете да видите, в полето "Стойност за търсене" се появиха данните, въведени от оператора SUMESELI . Но това не е всичко. Отидете в това поле и добавете знака "+" без котировки към съществуващите данни. След това въведете адреса на първата клетка в графата "Приходи" . И отново правим хоризонталните координати на това референтно абсолютно и вертикално оставяме относителните координати.

    След това ние вземаме цялото съдържание на полето "Желаната стойност" в скоби, след което поставяме знака за разделяне ( "/" ). След това отново през иконата на триъгълника отидете до прозореца за избор на функции.

11. Както и в последното време в стартирания функционален помощник, ние търсим необходимия оператор в категорията "Математически" . Този път желаната функция се нарича "SUMM" . Изберете го и кликнете върху бутона "OK" .
12. Отваря прозореца с аргументи на оператора SUM . Основната му цел е да обобщи данните в клетките. Синтаксисът на този оператор е съвсем прост:

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

    За нашите цели се изисква само поле "Брой1" . Въведете координатите на диапазона на колоната "Приходи" , с изключение на клетката, която съдържа общите суми. Вече извършихме такава операция в полето "Range" на функцията SUMMER . Както и по това време, координатите на диапазона са абсолютни, избирайки ги и натискайки клавиша F4 .

    След това кликнете върху бутона "OK" в долната част на прозореца.

13. Както можете да видите, наборът от входни функции извърши изчислението и изведе резултата до първата клетка в колоната "Група" . Първият продукт беше причислен към група "А" . Пълната формула, която кандидатствахме за това изчисление, е както следва:

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

    Но, разбира се, във всеки конкретен случай координатите в тази формула ще се различават. Следователно, тя не може да се счита универсална. Но с помощта на ръководството, дадено по-горе, можете да вмъкнете координатите на всяка таблица и да приложите успешно този метод във всяка ситуация.

14. Това обаче не е всичко. Изчислихме само за първия ред от таблицата. За да попълните напълно тази колона с данните, трябва да копирате тази формула в диапазона по-долу (с изключение на клетката в реда "Общо" ), като използвате маркера за попълване, както сме направили повече от веднъж. След въвеждане на данните, анализът на ABC може да се счита за изпълнен.

Както можете да видите, резултатите, получени с помощта на вариант, използващ сложна формула, не се различават от резултатите, които направихме чрез сортиране. На всички продукти се присвояват същите категории, но редовете не променят първоначалната си позиция.

Урокът: Съветник за функции в Excel

Програмата Excel може значително да улесни ABC анализа за потребителя. Това се постига с помощта на инструмент като сортиране. След това се изчислява индивидуалното тегло, натрупаният дял и всъщност групирането. В случаите, когато не е разрешено промяната на началната позиция на редовете в таблицата, можете да приложите метода, като използвате сложна формула.

• ← Използване на функцията SELECT в Microsoft Excel
• Задайте деня от седмицата по дата в Microsoft Excel →