Користење на ABC анализа во Microsoft Excel

Еден од клучните методи за управување и логистика е ABC анализата. Со негова помош, можете да ги класифицирате ресурсите на претпријатието, стоки, клиенти, итн. по степен на важност. Во исто време, според нивото на важност, на секоја од горенаведените единици му е доделена една од трите категории: А, Б или Ц. Ексел има во своите алатки за багаж што го олеснуваат спроведувањето на овој вид анализи. Ајде да дознаеме како да ги искористиме, и што претставува анализа на ABC.

Користење на анализа на ABC

Анализата на ABC е еден вид подобрена и прилагодена на модерната верзија на принципот Парето. Според методологијата на нејзино спроведување, сите елементи на анализата се поделени во три категории според степенот на важност:

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

Некои компании применуваат понапредни техники и ги раздвојуваат елементите во 3 или 4 или 5 групи, но ние ќе се потпреме на класичната шема на анализа на ABC.

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

Во Excel, ABC анализата се изведува со употреба на сортирање. Сите предмети се сортираат од најголеми до најмали. Потоа, се пресметува кумулативна специфична тежина на секој елемент, врз основа на кој му се доделува одредена категорија. Ајде да откриеме, користејќи специфичен пример, како се применува оваа техника во пракса.

Имаме табела со список на стоки што компанијата ги продава и соодветниот износ на приход од нивната продажба за одреден временски период. На дното на табелата, се погодени вкупните приходи за сите производи од стоки. Задачата, користејќи ABC анализа, е да ги подели овие производи во групи според нивната важност за претпријатието.

1. Изберете ја табелата со покажувачот на податоци, држејќи го левото копче на глувчето, исклучувајќи го заглавието и последниот ред. Одете на јазичето „Податоци“. Кликнете на копчето. „Подреди“сместена во блокот со алатки Сортирајте и филтрирајте на снимката.

   

   Можете исто така да направите поинаку. Изберете го горенаведениот опсег на табелата, а потоа преместете го на јазичето „Дома“ и кликнете на копчето Сортирајте и филтрирајтесместена во блокот со алатки „Уредување“ на снимката. Активирана е листа во која избираме позиција во неа. Прилагодено сортирање.



2. При примена на кое било од горенаведените активности, се стартува прозорецот за поставки за сортирање. Ние изгледаме така што околу параметарот "Моите податоци содржат заглавија" поставена е ознака за проверка. Во случај на отсуство, инсталирајте.

   На полето Колона наведете го името на колоната што ги содржи податоците за приходите.

   На полето „Подреди“ треба да одредите според кој специфичен критериум ќе се изврши сортирањето. Ги оставаме претходно дефинираните поставки - „Вредности“.

   На полето "Нарачај" постави позиција "Опаѓање".

   Откако ќе ги направите наведените поставки, кликнете на копчето „ОК“ на дното на прозорецот.



3. По извршувањето на наведената акција, сите елементи беа подредени по приход од најголем до најмал.



4. Сега треба да ја пресметаме специфичната тежина на секој од елементите за вкупниот број. Ние создаваме дополнителна колона за овие цели, што ќе ги наречеме „Специфична тежина“. Во првата ќелија од оваа колона, ставете знак "=", по што укажуваме врска до ќелијата во која се наоѓа износот на приходите од продажба на соодветниот производ. Следно, поставете го знакот за поделба ("/") После тоа, наведете ги координатите на ќелијата, која ја содржи вкупната количина на продажба на стоки низ претпријатието.

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

   Потоа, за да направите пресметки, кликнете на копчето Внесете.



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



6. Како што можете да видите, целата колона е исполнета со податоци што го карактеризираат уделот на приходите од продажба на секој производ. Но, специфичната тежина е прикажана во нумерички формат, и ние треба да ја претвориме во процент. За да го направите ова, изберете ја содржината на колоната „Специфична тежина“. Потоа се префрламе на јазичето „Дома“. На лентата во групата за поставки „Број“ Има поле што го прикажува форматот на податоци. Стандардно, ако не сте извршиле дополнителни манипулации, форматот треба да се постави таму „Генерал“. Ние кликнуваме на иконата во форма на триаголник што се наоѓа десно од ова поле. Во списокот со формати што се отвора, одберете ја позицијата „Интерес“.



7. Како што можете да видите, сите вредности на колоната се претворија во процентни вредности. Како што се очекуваше, во линија „Тотал“ означен 100%. Процентот на стоки се очекува да биде во колоната од поголема до помала.



8. Сега треба да создадеме колона во која ќе се прикаже акумулираната акција со кумулативен вкупен број. Тоа е, во секој ред, специфичната тежина на одреден производ ќе ја додаде специфичната тежина на сите оние производи што се наоѓаат во списокот погоре. За првата ставка во списокот (Производ 3) индивидуалната специфична тежина и акумулираната акција ќе бидат еднакви, но за сите последователни, акумулираниот дел од претходниот елемент на списокот ќе треба да се додаде на индивидуалниот индикатор.

   Значи, во првиот ред се префрламе во колоната Акумулирано споделување индикатор на колоната „Специфична тежина“.



9. Следно, поставете го покажувачот на втората ќелија во колоната. Акумулирано споделување. Тука треба да ја примениме формулата. Ставивме знак еднакви и додадете ја содржината на ќелијата „Специфична тежина“ ист ред и содржина на ќелии Акумулирано споделување од линијата погоре. Ние ги оставаме сите врски релативни, односно не ги манипулираме. После тоа, кликнете на копчето Внесете за прикажување на конечниот резултат.



10. Сега треба да ја копирате оваа формула во ќелиите на оваа колона, кои се наоѓаат подолу. За да го направите ова, користете го маркерот за пополнување, на што веќе се прибегнавме при копирање на формулата во колоната „Специфична тежина“. Во овој случај, линијата „Тотал“ нема потреба да се фати, бидејќи акумулираниот резултат во 100% ќе се прикаже на последната ставка од списокот. Како што можете да видите, после тоа беа исполнети сите елементи од нашата колона.



11. После тоа создаваме колона „Група“. Willе треба да ги групираме производите во категории А, Б и В според наведената акумулирана акција. Како што се сеќаваме, сите елементи се дистрибуираат во групи според следнава шема:
    • А - до 80%;
    • Б - следново 15%;
    • Со - останува 5%.

    Така, за сите стоки, акумулираниот дел од специфичната гравитација е вклучен во границата до 80%додели категорија А. Стоки со специфична тежина на 80% порано 95% додели категорија Б. Преостаната група на производи со вредност поголема од 95% акумулирана специфична категорија за доделување тежина В.



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

Така, ние ги поделивме елементите во групи според нивото на важноста, користејќи анализа на ABC. При користење на некои други техники, како што веќе беше споменато погоре, се користи поделба во поголем број групи, но принципот на поделба останува скоро непроменет.

Лекција: Excel сортирање и филтрирање

Метод 2: користете сложена формула

Се разбира, употребата на сортирање е најчестиот начин да се изврши анализа на ABC во Excel. Но, во некои случаи, неопходно е да се спроведе оваа анализа без да се преуредат редовите во првобитната табела. Во овој случај, сложена формула ќе дојде до спасување. Како пример, ќе ја користиме истата табела со извори како во првиот случај.

1. Додадете во оригиналната табела со името на стоката и приходите од продажба на секоја од нив, колона „Група“. Како што можете да видите, во овој случај не можеме да додадеме колони со пресметка на индивидуални и кумулативни акции.



2. Изберете ја првата ќелија во колоната „Група“и потоа кликнете на копчето "Вметни функција"лоцирани во близина на линијата на формулите.



3. Активирање е во тек Волшебници за функции. Се движиме во категоријата Референци и низи. Изберете функција „ИЗБОР“. Кликнете на копчето „ОК“.



4. Активиран е прозорецот за аргументи за функција. ИЗБОР. Нејзината синтакса е претставена како што следува:

   = ИЗБОР (Индекс_број; вредност 1; вредност 2; ...)

   Целта на оваа функција е да се излезе една од наведените вредности, во зависност од индексот. Бројот на вредности може да достигне 254, но потребни ни се само три имиња што одговараат на категориите на анализа на ABC: А, Б, Со. Можеме веднаш да влеземе во полето "Вредност 1" симбол „А“на полето "Вредност 2" - „Б“на полето "Вредност 3" - „Ц“.



5. Но, со аргумент Индекс број мора темелно да замаглувате со тоа со интегрирање на неколку дополнителни оператори во него. Поставете го покажувачот на полето Индекс број. Следно, кликнете на иконата во форма на триаголник лево од копчето "Вметни функција". Се отвора список на неодамна користени оператори. Ни треба функција ПРЕБАРУВАЕ. Бидејќи не е на списокот, кликнете на натписот "Други карактеристики ...".



6. Прозорецот започнува повторно. Волшебници за функции. Повторно се префрламе во категоријата Референци и низи. Пронајдете позиција таму „ПРЕБАРУВАЕ“, изберете го и кликнете на копчето „ОК“.



7. Отворен е прозорецот за аргументи на операторот ПРЕБАРУВАЕ. Нејзината синтакса е како што следува:

   = ПРЕБАРУВАЕ (Барав_валуација; Прегледано_раја; натпревар_тип)

   Целта на оваа функција е да се одреди бројот на позицијата на наведениот елемент. Тоа е, токму она што ни е потребно за теренот Индекс број функциите ИЗБОР.

   На полето Гледаше низа Веднаш можете да го наведете следниот израз:

   {0:0,8:0,95}

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

   Полето Тип на натпревар по избор и во овој случај нема да го пополниме.

   На полето "Барам вредност" поставете го покажувачот. Потоа повторно преку горенаведениот пиктограм во форма на триаголник преминуваме Волшебник на карактеристики.



8. Овој пат во Волшебник за функции преминете во категорија „Математички“. Изберете име СУМСКИ и кликнете на копчето „ОК“.



9. Започнува прозорецот за аргументи за функција СУМИ. Наведениот оператор ги сумира ќелиите кои исполнуваат одредена состојба. Нејзината синтакса е:

   = SUMMES (опсег; критериум; sum_range)

   На полето „Опсег“ внесете ја адресата на колоната „Приходи“. За овие цели, поставете го покажувачот во полето, а потоа, држејќи го левото копче на глувчето, изберете ги сите ќелии во соодветната колона, исклучувајќи ја вредноста „Тотал“. Како што можете да видите, адресата веднаш беше прикажана во полето. Покрај тоа, треба да ја направиме оваа врска апсолутна. За да го направите ова, изберете го и притиснете го копчето Ф4. Адресата се истакнуваше со знаци на долар.

   На полето „Критериум“ треба да поставиме услов. Внесуваме во следниот израз:

   ">"&

   Потоа веднаш по неа ја внесуваме адресата на првата ќелија на колоната „Приходи“. Ние ги правиме хоризонталните координати на оваа адреса апсолутни со додавање на знак за долар од тастатурата пред буквата. Ние ги оставаме вертикалните координати во однос, односно не треба да има никаков знак пред цифрата.

   После тоа, не кликнете на копчето „ОК“, и кликнете на името на функцијата ПРЕБАРУВАЕ во лентата со формули.



10. Потоа се враќаме во прозорецот за аргументи за функција ПРЕБАРУВАЕ. Како што можете да видите, на полето "Барам вредност" се појави податоци поставени од операторот СУМИ. Но, тоа не е сè. Одете на ова поле и додајте го знакот на постојните податоци. "+" без понуди Потоа ја внесуваме адресата на првата ќелија на колоната „Приходи“. И повторно, ги правиме хоризонталните координати на оваа врска апсолутни и ги оставаме вертикална релативна.

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



11. Како и последниот пат во бегство Волшебник за функции во потрага по посакуваниот оператор во категоријата „Математички“. Овој пат, саканата функција се нарекува СУМ. Изберете го и кликнете на копчето. „ОК“.



12. Отворен е прозорецот за аргументи на операторот СУМ. Неговата главна цел е да ги сумира податоците во ќелиите. Синтаксата за оваа изјава е прилично едноставна:

    = СУМ (Број 1; Број 2; ...)

    За наши цели е потребно само поле "Број 1". Внесете ги координатите на опсегот на колоните во него. „Приходи“со исклучок на ќелијата што ги содржи тоталите. Веќе извршивме слична операција на терен „Опсег“ функциите СУМИ. Како и во тоа време, ги правиме координатите на опсегот апсолутни со избирање на нив и притискање на копчето Ф4.

    После тоа, кликнете на копчето „ОК“ на дното на прозорецот.



13. Како што можете да видите, комплексот на воведени функции ја изврши пресметката и го врати резултатот во првата ќелија на колоната „Група“. Првиот производ беше доделен на група „А“. Целосната формула што ја користевме за оваа пресметка е како што следува:

    = ИЗБОР (ПРЕБАРУВАЕ ((РЕЗИМЕ ($ Б $ 2: $ Б $ 27; ">" & $ Б2) + $ Б2) / СУМ ($ Б $ 2: $ Б $ 27); {0: 0,8: 0,95} ); "А"; "Б"; "Ц")

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



14. Сепак, ова не е сè. Ние ја извршивме пресметката само за првиот ред на табелата. Со цел целосно да се дополни колона со податоци „Група“, треба да ја копирате оваа формула во опсегот подолу (со исклучок на ќелијата на ред „Тотал“) користејќи го маркерот за пополнување, како што сме направиле повеќе од еднаш. По внесувањето на податоците, анализата на ABC може да се смета за завршена.

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

Лекција: Волшебник за одлики на Excel

Excel може многу да ја олесни анализата на ABC за корисникот. Ова се постигнува со употреба на алатка како што е сортирање. После тоа, се пресметува индивидуалната специфична тежина, акумулираната акција и, всушност, поделбата во групи. Во случаи кога не е дозволено промена на почетната позиција на редови во табелата, можете да го примените методот со користење на сложена формула.