Примена АБЦ анализе у Мицрософт Екцелу

Једна од кључних метода управљања и логистике је АБЦ-анализа. Уз помоћ, можете класификовати ресурсе предузећа, производа, купаца итд. по степену важности. Истовремено, према степену важности, свака од наведених јединица има једну од три категорије: А, Б или Ц. Екцел има у алаткама за пртљаг који олакшавају извођење ове врсте анализа. Хајде да схватимо како их користити, а шта је АБЦ анализа.

Користећи АБЦ анализу

АБЦ-анализа је врста побољшане и прилагођене варијанти модерног стања Парето принципа. Према методологији њеног понашања, сви елементи анализе су подељени у три категорије у смислу важности:

• Категорија А - елементи који у укупном броју садрже више од 80% специфичне тежине;
• Категорија Б - елементи, чији агрегат износи од 5% до 15% специфичне тежине;
• Категорија Ц - преостали елементи, укупни агрегат који је 5% и мањи од специфичне тежине.

Неке компаније примјењују напредније технике и раскидају елементе не у 3, већ у 4 или 5 група, али се ослањамо на класичну шему АБЦ анализа.

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

У Екцел-у АБЦ анализа се врши сортирањем. Сви елементи су сортирани од већих до мањих. Тада се израчунава кумулативна специфична тежина сваког елемента, на основу кога је одређена категорија додељена. Хајде да сазнамо на конкретном примеру како се ова техника примјењује у пракси.

Имамо сто са листом роба које компанија продаје и одговарајућим износом прихода од њихове продаје у одређеном временском периоду. На дну табеле је ударио укупан приход за сва имена производа. Вриједност задатка, кориштењем АБЦ-анализе, је да се ова роба разбије у групе по важности за предузеће.

1. Изаберите табелу са датим курсором, држећи леви тастер миша, искључујући заглавље и завршну линију. Идите на картицу "Подаци" . Кликните на дугме "Сортирај" у пољу за сортирање и филтрирање на траци.

   Такође можете дјеловати другачије. Изаберите горњи распон таблица, а затим пређите на картицу "Почетна" и кликните на дугме "Сортирај и филтрирај" који се налази у "Уреди" алатку на траци. Листа је активирана у којој изаберемо ставку "Цустом сорт" у њему .

2. Када примените неку од горе наведених радњи, покреће се прозор сортирања. Да видимо да је опција "Моји подаци садржи заглавље" означена. У случају одсуства, утврдимо.

   У пољу "Колона" уносимо назив колоне, који садржи податке о приходима.

   У пољу "Сортирај", морате навести специфичне критеријуме за сортирање. Остављамо подразумеване поставке - "Вредности" .

   У пољу "Поруџбина" поставили смо позицију "Падајући" .

   Након постављања наведених поставки, кликните на дугме "ОК" на дну прозора.

3. Након извршене одређене акције, сви елементи су сортирани по приходима од највећег до мањег.
4. Сада морамо израчунати специфичну тежину сваког од елемената за укупну укупну вредност. У ову сврху креирамо додатну колону, коју ћемо назвати "специфична тежина" . У првој ћелији ове колоне поставили смо знак "=" , након чега се наводи веза са ћелијом у којој се налази износ прихода од продаје одговарајућег производа. Затим смо поставили знак за поделу ( "/" ). Након тога наведите координате ћелије, која садржи укупан износ продаје робе у целом предузећу.

   Узимајући у обзир чињеницу да ћемо ову формулу копирати у друге ћелије у колони "Специфична тежина" помоћу маркера за пуњење, потребно је да утврдимо адресу везе на елемент који садржи укупан износ прихода од стране предузећа. За то смо направили референтни апсолут. Изаберите координате наведене ћелије у формули и притисните Ф4 . Пре координата, као што видимо, појавио се знак за долар, што указује на то да је веза постала апсолутна. Треба напоменути да референца на приход прве ставке на листи ( роба 3 ) треба да остане релативна.

   Затим, да направите рачуне, притисните тастер Ентер .

5. Као што видите, у циљној ћелији приказан је удио прихода од првог производа наведеног на листи. Да копирате формулу у опсег испод, поставите курсор у доњи десни угао ћелије. Претвара се у маркер пуњења који изгледа као мали крст. Кликните на леви тастер миша и превуците маркер за пуњење до краја колоне.
6. Као што видите, цела колона је испуњена подацима који карактеришу специфичну тежину прихода од продаје сваког производа. Али вредност специфичне тежине је приказана у нумеричком формату и морамо да је трансформишемо у проценат. Да бисте то урадили, изаберите садржај стуба "Специфична тежина" . Затим пређите на картицу Почетак . На траци у групи за подешавање "Број" налази се поље које приказује формат података. Подразумевано, уколико нисте извршили додатне манипулације, треба поставити формат "Генерал" . Кликом на икону у облику троугла, налази се десно од овог поља. У отвореној листи формата изаберите ставку "Интерес" .
7. Као што видите, све вриједности ступаца су конвертоване у процентуалне вриједности. Као што је и очекивано, линија "Тотал" означава 100% . Очекује се да ће специфична тежина роба бити у колони од највећег до мања.
8. Сада морамо да направимо колону у којој се приказује акумулирани део с кумулативним тоталом. То је, у свакој линији, специфична тежина свих оних производа који се налазе на горњој листи биће додати појединачној специфичној тежини одређене робе. За први производ на листи ( тачка 3 ), појединачно учешће и акумулирана дионица ће бити једнаки, али за све након појединачног индикатора потребно је додати акумулирани дио претходног елемента листе.

   Дакле, у првој линији пренесите на колону "Акумулирана дионица" индикатор из колоне "Специфична тежина" .

9. Затим поставимо курсор у другу ћелију колоне "Акумулирана дионица" . Овде морамо да применимо формулу. Ставили смо знак "једнака" и додали садржај ћелије "Специфична тежина" истог реда и садржаја ћелије "Акумулирана дионица" из линије изнад. Све референце су остављене релативне, тј. Не манипулишемо њима. Након тога, кликните на дугме Ентер да бисте приказали коначни резултат.
10. Сада требате копирати ову формулу у ћелије ове колоне, које су постављене испод. Да би то учинили, примењујемо маркер за пуњење, на који смо већ користили приликом копирања формуле у колони "Специфична тежина" . У овом случају линија "Тотал" није потребна за снимање, јер ће се акумулирани резултат у 100% приказати на последњем производу са листе. Као што видите, сви елементи наше колоне су били попуњени након тога.
11. Након тога креирајте колону "Група" . Ми ћемо морати груписати робу према категоријама А , Б и Ц према акумулираном уделу. Као што се сећамо, сви елементи су груписани према следећој шеми:
    • А - до 80% ;
    • Б - следећих 15% ;
    • Ц - преосталих 5% .

    Дакле, сву робу, акумулирани део специфичне тежине која улази у границу на 80% , додјељује се категорији А. Роба са акумулираном специфичном тежином од 80% до 95% додјељује се категорији Б. Осталој групи робе у вриједности од преко 95% акумулиране специфичне тежине додјељује се категорија Ц.

12. За јасноћу, ове групе можете попунити различитим бојама. Али ово је по вољи.

Тако смо елементе поделили у групе према степену важности, користећи АБЦ анализу. Користећи неке друге технике, као што је већ речено, примјењује се партиционирање на више група, али принцип о дијељењу остаје практично непромењен.

Лекција: Сортирање и филтрирање у Екцелу

Метод 2: Коришћење комплексне формуле

Наравно, употреба сортирања је најчешћи начин извршавања АБЦ анализе у Екцелу. Али у неким случајевима, ова анализа је неопходна без промјене редова на местима у изворној табели. У овом случају, комплексна формула ће доћи до спашавања. На пример, користићемо исту изворну табелу као у првом случају.

1. Додамо на почетну таблицу, која садржи назив робе и прихода од продаје сваке од њих, колону "Група" . Као што видите, у овом случају не можемо додати колоне са обрачуном појединачних и акумулативних акција.
2. Изаберите прву ћелију у колони "Група" , а затим кликните на дугме "Убаци функцију" поред траке формуле.
3. Активира се чаробњак за функције . Прелазимо у категорију "Референце и низови" . Изаберите функцију "СЕЛЕЦТ" . Кликните на дугме "ОК" .
4. Прозор аргумента СЕЛЕЦТ функције је активиран. Синтакса је следећа:

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

   Задатак ове функције је излаз из једне од наведених вредности, зависно од индексног броја. Број вриједности може достићи 254, али нам требају само три имена која одговарају категоријама АБЦ-анализе: А , Б , Ц. У пољу "Валуе1" можемо одмах унети симбол "А" у поље "Валуе2" - "Б" , у пољу "Валуе3" - "Ц" .

5. Али са аргументом "Индексни број" морат ће се темељно потиснути, изградивши у њему неколико додатних оператора. Поставите курсор у поље "Индек нумбер" . Затим кликните на икону која изгледа као троугао, лево од дугмета "Убаци функцију" . Приказује се листа недавно коришћених оператора. Потребна нам је МАТЦХ функција. Пошто списак не ради, кликнемо на натпис "Остале функције ..." .
6. Поново покренути Чаробњак за прозор функције . Поново идите у категорију "Референце и низови". Нађемо ту позицију "СЕАРЦХ" , одаберите је и кликните на дугме "ОК" .
7. Отвара прозор ОПЕРАТОР-овог аргумента. Њена синтакса је следећа:

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

   Сврха ове функције је да одреди број положаја наведеног елемента. То јест, само оно што нам је потребно за поље "Број индекса" функције СЕЛЕЦТ .

   У пољу "Скенирани низ" можете одмах поставити следећи израз:

   {0:0,8:0,95}

   Мора бити у завртањима, попут формуле низа. Није тешко претпоставити да ови бројеви ( 0 ; 0,8 ; 0,95 ) означавају границе акумулираног удела између група.

   Поље "Матцхинг типе" је необавезно и у овом случају га нећемо попунити.

   У пољу "Сеарцх валуе" поставили смо курсор. Затим, поново кроз троугао приказан горе, прелазимо на Чаробњак за функције .

8. Овог пута у чаробњаку функције прелазимо у категорију "Математички" . Изаберемо назив "ЛЕТО" и кликните на дугме "ОК" .
9. Покренут је прозор аргумената функције СУМИФЕР . Наведени оператор сумира ћелије које испуњавају наведено стање. Његова синтакса је ово:

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

   У пољу "Опсег" унесите адресу колоне "Приходи" . За ову сврху поставили смо курсор у поље, а затим, након стега левог тастера миша, изаберите све ћелије одговарајуће колоне, искључујући вредност "Укупно" . Као што видите, адреса је одмах приказана у пољу. Поред тога, потребно је да ова веза буде апсолутна. Да бисте то урадили, направите његов избор и кликните на Ф4 тастер. Адреса се истицала знаком долара.

   У пољу "Критеријуми" морамо одредити стање. Уносимо следећи израз:

   ">"&

   Затим одмах након тога улазимо у адресу прве ћелије у колони "Приходи" . Хоризонталне координате у овој адреси направимо апсолутно, додајући знак за долар са тастатуре пре писма. Координате на вертикалном положају су релативне, тј. Пре него што бројка не буде знак.

   Након тога, немојте кликнути на дугме "ОК" , али кликните на име функције МАТЦХ у траци формуле.

10. Затим се вратимо на прозор аргумената МАТЦХ функције. Као што видите, у пољу "Претрага вриједности" појавили су се подаци које је унело оператор СУМЕСЕЛИ . Али то није све. Идите на ово поље и додајте знак "+" без цитата на постојеће податке. Затим уносимо адресу прве ћелије у колони "Приходи" . И поново направимо хоризонталне координате овог референтног апсолута, а вертикално остављамо релативну.

    Даље, узимамо цео садржај поља "Жељена вредност" у заграде, након чега стављамо знак поделе ( "/" ). Поново преко иконе троугла идите у прозор за избор функције.

11. Као и последњи пут у чаробњаку покренуте функције, тражимо потребног оператора у категорији "Математички" . Овога пута се жељена функција назива "СУММ" . Изаберите је и кликните на дугме "ОК" .
12. Отвара прозор аргумената оператора СУМ- а. Његова главна сврха је да се сумирају подаци у ћелијама. Синтакса овог оператора је сасвим једноставна:

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

    За наше потребе је потребно само поље "Нумбер1" . Унесите координате опсега колоне "Приходи" , искључујући ћелију која садржи укупне вредности. Већ смо извршили такву операцију у пољу "Опсег" функције СУММЕР . Као и тада, координате опсега су апсолутне, одабире их и притиском на тастер Ф4 .

    Након тога кликните на дугме "ОК" на дну прозора.

13. Као што видите, скуп функција улаза извршио је израчунавање и излази резултат у прву ћелију колоне "Група" . Први производ је додељен групи "А" . Комплетна формула коју смо применили за ову обрачуну је следећа:

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

    Али, наравно, у сваком конкретном случају, координате у овој формули ће се разликовати. Због тога се не може сматрати универзалним. Али, користећи горњи приручник, можете унети координате било које табеле и успешно применити овај метод у било којој ситуацији.

14. Међутим, ово није све. Израчунали смо само за први ред табеле. Да бисте у потпуности попунили колону "Група" са подацима, потребно је да копирате ову формулу на опсег испод (искључујући ћелију линије "Тотал" ) помоћу маркера за пуњење, као што смо то учинили више од једном. Након уноса података, АБЦ анализа се може сматрати испуњеном.

Као што видите, резултати добијени уз помоћ варијанте користећи комплексну формулу се не разликују од резултата које смо извршили сортирањем. Сви производи су додељени истим категоријама, али редови нису променили њихов почетни положај.

Лекција: Функцијски чаробњак у Екцелу

Екцел програм може у великој мери олакшати АБЦ-анализу за корисника. Ово се постиже коришћењем алата као што је сортирање. После тога, рачунају се појединачна тежина, акумулирани део и, заправо, групација. У случајевима када промена почетне позиције редова у табели није дозвољена, можете примијенити метод кориштењем сложене формуле.

• ← Коришћење функције СЕЛЕЦТ у програму Мицрософт Екцел
• Подесите дан у недељи по датуму у Мицрософт Екцелу →