Един от ключовите методи на управление и логистика е ABC анализ. С него можете да класифицирате ресурсите на предприятието, стоки, клиенти и др. Според степента на важност. В същото време, чрез нивото на важност, всеки от горепосоченото устройство е назначен една от трите категории: A, B или C. Excel програмата има в своите инструменти за багаж, които улесняват извършването на този вид анализ. Нека да разберем как да ги използваме и какъв е ABC анализът.
Използване на ABC анализ
ABC анализът е един вид засилен и адаптиран към съвременните условия за принципа на Парето. Съгласно метода на своето поведение всички елементи на анализа са разделени на три категории според степента на важност:- Категория А - елементи, които имат комбинация от повече от 80% от специфичното тегло;
- Категория Б - елементи, чиято комбинация варира от 5% до 15% от специфичното тегло;
- Категория С - останалите елементи, общото от което е 5% и по-малко специфично тегло.
Отделни компании използват по-напреднали техники и да разделят позиции, а не до 3, но с 4 или 5 групи, но ще разчитаме на класическата схема за анализ на ABC.
Метод 1: Анализ с сортиране
Анализът на Excel ABC се извършва с използване на сортиране. Всички елементи са сортирани от мазни до по-малко. След това се изчислява акумулативният дял на всеки елемент, въз основа на който е определена определена категория. Да разберем как се използва определената методология на практика.
Имаме таблица със списък с продукти, които компанията продава и съответния брой приходи от тяхната продажба за определен период от време. В дъното на таблицата резултатът от приходите като цяло на всички имена на стоките. Съществува задача, използваща ABC анализ, прекъснете тези стоки в групи по своето значение за предприятието.
- Ние подчертаваме масата с курсора, като затваряме левия бутон на мишката, с изключение на капачката и последния низ. Отидете в раздела "Данни". Щракнете върху бутона "Сортиране", разположен в лентата с инструменти "Сортиране и филтриране" на лентата.
Можете също така да правите по различен начин. Ние разпределяме горния диапазон на таблицата, след това преместете в раздела "Начало" и кликнете върху бутона "Сортиране и филтриране", разположен в инструментариума за редактиране на лентата. Списъкът е активиран, в който е активиран позицията "персонализираща се сортиране".
- При прилагане на някоя от горните действия, прозорецът за сортиране стартира. Изглеждаме, че "моите данни съдържа параметърът на заглавието" да е настроен на маркировка. В случай на неговото отсъствие, инсталирайте.
В полето "колона" посочете името на колоната, в която се съдържа данните за приходите.
В полето "Сортиране" трябва да посочите, по които ще бъдат сортирани конкретни критерии. Оставете настройките за предварително зададени настройки - "Стойности".
В полето "поръчка" показват позицията "низходяща".
След продукта на посочените настройки, кликнете върху бутона "OK" в долната част на прозореца.
- След извършване на определеното действие, всички елементи бяха сортирани с приходи от повече до по-малък.
- Сега трябва да изчислим дела на всеки от елементите за общ резултат. Създайте допълнителна колона за тези цели, които наричаме "споделяне на тегло". В първата клетка на тази колона поставяме знака "=", след което определяте връзка към клетката, в която е необходимото количество приходи от прилагането на съответния продукт. След това поставете знака на дивизията ("/"). След това показваме координатите на клетката, която съдържа общия размер на продажбата на стоки в цялото предприятие.
Като се има предвид факта, че посочената формула ще копираме в други клетки на колоната "Share" от маркера за пълнене, адреса на връзката към елемента, съдържащ крайния размер на приходите към предприятието, трябва да поправим. За да направите това, направете връзка абсолютно. Изберете координатите на посочената клетка във формулата и натиснете клавиша F4. Преди координатите, както виждаме, се появи знак за долар, който показва, че връзката е станала абсолютна. Трябва да се отбележи, че позоваването на размера на приходите на първия в списъка на стоките (продукт 3) следва да остане относително.
След това, за да направите изчисления, натиснете бутона ENTER.
- Както виждаме, делът на приходите от първия продукт, посочен в списъка, се появява в целевата клетка. За да копирате формулата в диапазона по-долу, поставяме курсора в долния десен ъгъл на клетката. Неговата трансформация в маркера за пълнене, имащ малък кръст. Щракнете върху левия бутон на мишката и плъзнете маркера за пълнене до края на колоната.
- Както можете да видите, цялата колона е изпълнена с данни, характеризиращи дела на приходите от прилагането на всеки продукт. Но стойността на специфичната гравитация се показва в цифровия формат и трябва да я преобразим в процент. За да направите това, маркирайте съдържанието на колоната "Специфично тегло". След това преместваме в раздела "Начало". На лентата в групата Настройки на групата има форма на показване на данни. По подразбиране, ако не сте създали допълнителни манипулации, форматът "Общи" трябва да бъде инсталиран там. Кликнете върху иконата под формата на триъгълник, разположен отдясно на това поле. В списъка на форматите изберете позицията "Процент".
- Както виждаме, всички стойности на колоните се трансформират в процентни стойности. Както трябва да бъде, 100% посочено в низ "общо". Делът на стоките се очаква в колоната от по-голям до по-малък.
- Сега трябва да създадем колона, в която ще бъде показан натрупаният дял с нарастващ резултат. Това означава, че във всяка линия към индивидуалното специфично тегло на конкретен продукт ще бъде добавен делът на всички тези продукти, които са разположени в списъка по-горе. За първите стоки в списъка (продукт 3), индивидуалната част и натрупаният дял ще бъде равен, но всички последващи показатели ще трябва да добавят натрупания дял от предишния елемент на списъка.
Така че, в първия ред, ние сме прехвърлени в колоната "Натрупан дял" индикатора на колоната "специфична".
- След това поставете курсора на втората клетка на колоната "Натрупана акция". Тук трябва да приложим формулата. Ние поставяме знака "равен" и сгънете съдържанието на клетката "съотношението" на същата линия и съдържанието на клетката "натрупан дял" от низ по-горе. Всички препратки са запазени роднина, т.е. ние не произвеждаме никакви манипулации с тях. След това изпълнете бутона щракнете върху бутона ENTER, за да изведете крайния резултат.
- Сега трябва да копирате тази формула в клетките на тази колона, които са поставени по-долу. За да направите това, ние използваме маркера за пълнене, към който вече сме прибягнали, когато копираме формулата в колоната за акции. В същото време, низът "Общо" не е необходим, тъй като натрупаният резултат е 100%, ще се покаже на последния продукт от списъка. Както можете да видите, всички елементи на нашата колона след това бяха пълни.
- След това създайте колона "група". Ще трябва да групираме стоки по категория А, В и С съгласно посочения натрупан дял. Както помним, всички елементи се разпространяват по групи съгласно следната схема:
- А - до 80%;
- Б - следващите 15%;
- C - останалите 5%.
По този начин всички стоки, които натрупаха дял от специфичното тегло, е на границата до 80%, ние възлагаме категория А. Стоките с натрупаното специфично тегло от 80% до 95% са присвоени на категория Б. Останалите Група стоки със стойността на повече от 95% от натрупаната специфична гравитация ние присвояваме категория С.
- За яснота можете да запълните посочените групи с различни цветове. Но това е по желание.
Така счупихме елементите на нивото на важност, използвайки ABC анализ. Когато се използват някои други техники, както е споменато по-горе, се използва за прекъсване на по-голям брой групи, но принципът на счупване в този случай остава почти непроменен.
Урок: Сортиране и филтриране в Excel
Метод 2: Използване на сложна формула
Разбира се, използването на сортирането е най-често срещаният начин за провеждане на ABC анализ в Excele. Но в някои случаи е необходимо този анализ без пренареждане на линии на места в таблицата на източника. В този случай една сложна формула ще стигне до спасяването. Например, ние ще използваме една и съща таблица на източника, както в първия случай.
- Ние добавяме към таблицата на източника, съдържаща името на стоките и приходите от продажбата на всяка от тях, колоната "група". Както можете да видите, в този случай не можем да добавяме колони с изчисляването на индивидуалните и акумулиращите фракции.
- Ние произвеждаме първото клетъчно разпределение в колоната на групата, след което изпълнявате бутона "Insert Function", разположен в близост до редуцата с формула.
- Майстори активиране на функции. Придвижваме се към категорията "Връзки и масиви". Изберете функцията "избор". Направяме бутона "OK".
- Прозорецът на аргумента е активиран. Синтаксисът е представен, както следва:
= Избор (номер_нтекс; стойност1; стойност2; ...)
Задачата на тази функция е изтеглянето на една от посочените стойности в зависимост от номера на индекса. Броят на стойностите могат да достигнат 254, но ще се нуждаем само от три имена, които отговарят на категориите ABC-анализ: a, b, c. можем веднага да въведем символа "a" в полето "B" в "B" "Поле, поле" стойност3 "-" С ".
- Но с аргумента "индекс номер" ще трябва да бъде напълно объркан, като изгради няколко допълнителни оператора. Инсталирайте курсора в полето "Индекс номер". След това кликнете върху иконата с изглед на триъгълника, вляво от бутона "Insert Function". Отваря се списък с новоприети оператори. Нуждаем се от функция на търсенето. Тъй като не е в списъка, тогава кликнем върху надписа "Други функции ...".
- Прозорецът на Wizard Wizard е стартиран отново. Отново отидете в категорията "Връзки и масиви". Ние намираме позицията на "Search Board", разпределяйте го и направете кликване върху бутона "OK".
- Отваря се аргументите на аргументите на оператора за търсене. Синтаксисът на него има следната форма:
= Търсене на дъска (Search_name; преглед__massive; type_station)
Целта на тази функция е да се определи броят на позицията на посочения елемент. Това е, точно това, от което се нуждаем за полето "Индекс номер", функция.
В полето "Списък с масиви" можете незабавно да зададете следния израз:
{0: 0.8: 0,95}
Тя трябва да бъде именно в къдрави скоби, като формула на масива. Не е трудно да се отгатне, че тези числа (0; 0.8; 0.95) означават границите на натрупания дял между групите.
Полето "тип сравнение" не е задължително и в този случай няма да го напълним.
В полето "втора стойност" поставете курсора. След това отново през иконата, описана по-горе под формата на триъгълник, ние се придвижваме към магьосника на функциите.
- Този път в съветника за функции, ние правим преместване в категорията "Математически". Изберете името "Silent" и натиснете бутона "OK".
- Ще стартира прозорецът на функционалните аргументи. Посоченият оператор обобщава клетките, които отговарят на определеното състояние. Неговият синтаксис е:
= Безшумен (диапазон; критерий; regal_suming)
В полето "Обхват" въведете адреса на колоната "Приходи". За тези цели поставяме курсора в полето и след това като направим клипа на левия бутон на мишката, изберете всички клетки на съответната колона, с изключение на стойността "общо". Както можете да видите, адресът незабавно се появи в полето. Освен това трябва да направим тази връзка абсолютна. За да направите това, създайте своето разпределение и кликнете върху клавиша F4. Адресът е освободен от доларови знаци.
В полето "Критерий" трябва да зададем състояние. Въведете следния израз:
">"&
След това веднага след като въведем адреса на първата клетка на колоната "Приходи". Ние правим координати хоризонтално в този адрес Абсолютен, добавяйки знак за долар от клавиатурата пред писмото. Вертикалните координати отпуска роднина, т.е. не трябва да има знак пред цифрата.
След това не натискаме бутона "OK" и кликнете върху името на функцията за търсене във формулата.
- След това се връщаме към прозореца на аргументите на борда за търсене. Както виждаме, в полето "мрачно значение", данните, определени от оператора, мълчат. Но това не е всичко. Отидете в това поле и вече добавяте наличните данни, за да добавите знак "+" без кавички. След това въвеждаме адреса на първата клетка на колоната "Приходи". И отново ние правим координати хоризонтално с абсолютно и вертикално оставяме роднина.
След това вземаме цялото съдържание на полето "желаната стойност" в скобите, след което поставяме знака на дивизията ("/"). След това отново през иконата на триъгълника отидете на функцията за избор на функции.
- Подобно на последния път в магьосника за функция, ние търсим необходимия оператор в категорията "Математическа". Този път желаната функция се нарича "суми". Ние го маркираме и натискаме бутона "OK".
- Отваря се прозорецът на аргументите на оператора. Неговата основна цел е сумирането на данни в клетките. Синтаксисът на този оператор е доста прост:
= Суми (номер1; номер2; ...)
За нашите цели ще бъде необходима само полето "номер1". Ние въвеждаме в него координатите на колоната "Приходи", като елиминираме клетката, която съдържа резултатите. Вече извършихме такава операция в областта "обхват" на функцията. Както и в това време, координатите на гамата правят абсолютно, подчертавайки ги и натискането на ключа F4.
След това кликнете върху бутона "OK" в долната част на прозореца.
- Както можете да видите, комплексът на въведените функции направи изчисление и издаде резултата в първата клетка на колоната "група". Първият продукт е назначен за група "А". Пълната формула, прилагана от нас за това изчисление, е както следва:
= Избор (Търсеща дъска ((Silent ($ B $ 2: $ B $ 27; "> & $ b2) + $ b2) / суми ($ B $ 2: $ B $ 27); {0: 0.8: 0 , 95}); "А"; "Б"; "С")
Но, разбира се, във всеки случай координатите в тази формула ще бъдат различни. Следователно тя не може да се счита за универсална. Но, използвайки ръководството, което е посочено по-горе, можете да поставите координатите на всяка таблица и успешно да приложите този метод във всяка ситуация.
- Това обаче не е всичко. Изчислихме се само за първия ред на таблицата. За да попълните напълно групата "група", трябва да копирате тази формула в диапазона по-долу (премахване на клетката на низ "общо") с помощта на маркер за пълнене, както вече сте направили повече от веднъж. След като данните се извършват, може да се приеме ABC анализ.
Както виждаме, резултатите, получени с помощта на опцията, използвайки сложна формула, не се различават от тези резултати, които сме извършени чрез сортиране. Всички стоки са присвоени в същите категории, само в същото време редовете не са променили първоначалната си позиция.
Урок: Магистър на функциите в Excele
Програмата Excel е в състояние значително да облекчи анализа на ABC за потребителя. Това се постига чрез използване на такъв инструмент като сортиране. След това се изчислява индивидуалната специфична тежест, натрупана акция и, всъщност, разделянето на групи. В случаите, когато промяната в първоначалното положение на редовете в таблицата не е позволена, можете да приложите метода, като използвате сложна формула.