Еден од клучните методи за менаџмент и логистика е ABC анализа. Со него можете да ги класифицирате ресурсите на претпријатието, производите, потрошувачите итн. во зависност од важноста. Во исто време, според нивото на важност, секоја од наведените единици е доделена на една од трите категории: A, B, или C. Excel има алатки во својот багаж што го олеснуваат спроведувањето на овој вид анализа. Ајде да видиме како да ги користиме, и што е анализата на ABC.
Користење на ABC анализа
ABC анализата е вид на подобрена и прилагодена на варијантата на современите услови на принципот на Парето. Според методологијата на неговото однесување, сите елементи на анализата се поделени во три категории по важност:
- Категорија A - елементи кои имаат заедничко повеќе 80% специфична тежина;
- Категорија Б. - елементи, чија вкупност е од 5% до 15% специфична тежина;
- Категорија C - останатите елементи, чиј вкупен агрегат е 5% и помалку специфична тежина.
Некои компании користат понапредни техники и ги делат елементите не во 3, туку во 4 или 5 групи, но ние ќе се потпреме на класичната шема на ABC анализата.
Метод 1: анализа со сортирање
Во Excel, ABC анализата се изведува со користење на сортирање. Сите предмети се подредени од најголеми до најмали. Тогаш се пресметува кумулативната специфична тежина на секој елемент, врз основа на која одредена категорија му се доделува. Ајде да користиме конкретен пример за да дознаеме како оваа техника се применува во пракса.
Имаме табела со листа на стоки што компанијата ги продава и соодветен износ на приходи од нивната продажба за одреден временски период. На дното на табелата, вкупниот приход е сумиран за сите стоки на стоки. Задачата е да се користи ABC-анализа за да се поделат овие производи во групи според нивната важност за компанијата.
- Изберете ја табелата со курсорот за податоци, држејќи го левото копче на глувчето, со исклучок на заглавието и последниот ред. Одете на јазичето "Податоци". Кликнете на копчето. "Сортирај"лоциран во блок на алатки "Сортирај и филтрирај" на лента.
Исто така можете да направите поинаку. Изберете го горниот опсег на табелата, а потоа преминете на табот "Дома" и кликнете на копчето "Сортирај и филтрирај"лоциран во блок на алатки Уредување на лента. Листата е активирана во која избираме позиција во неа. "Прилагодена сортирања".
- При примена на некое од горенаведените акции, се активира прозорецот за поставки за сортирање. Ние го разгледуваме параметарот "Моите податоци содржат заглавија" е поставен функционирањето. Во случај на негово отсуство, инсталирајте го.
Во полето "Колона" наведете го името на колоната во која податоците за приходите.
Во полето "Сортирај" треба да одредите според кои специфични критериуми ќе бидат подредени. Ги оставаме зададените поставки - "Вредности".
Во полето "Ред" поставете ја позицијата "Опаѓачки".
Откако ќе ги направите овие поставувања кликнете на копчето "Добро" на дното на прозорецот.
- По извршувањето на оваа акција, сите предмети беа подредени според приходите од највисоките до најниските.
- Сега треба да го пресметаме процентот на секој од елементите за вкупниот број. За овие цели создаваме дополнителна колона, која ќе ја наречеме "Сподели". Во првата ќелија на оваа колона се става знак "="по што укажуваме на повикување на ќелијата во која се наоѓа збирот на приносите од продажбата на релевантниот производ Потоа, поставете го знакот за поделба ("/"). Потоа ги посочуваме координатите на ќелијата, која го содржи вкупниот износ на продажба на стоки низ претпријатието.
Со оглед на фактот дека ќе ја копираме наведената формула на други клетки од колоната "Сподели" користејќи маркер за пополнување, адресата на врската до елементот кој го содржи вкупниот износ на приходи за претпријатието, треба да се поправи. За да го направите ова, направете ја врската апсолутна. Изберете ги координатите на наведената ќелија во формулата и притиснете го копчето F4. Како што можеме да видиме, знакот за долар се појави пред координатите, што покажува дека врската стана апсолутна. Треба да се напомене дека упатувањето на износот на приходот од првата точка во листата (Точка 3) мора да останат релативни.
Потоа, за да направите пресметки, кликнете на копчето. Внесете.
- Како што можете да видите, процентот на приходите од првиот производ наведени во листата се прикажува во целната ќелија. За да направите копија од формулата во опсегот подолу, поставете го курсорот во долниот десен агол на ќелијата. Се претвора во маркер за пополнување што изгледа како мал крст. Кликнете на левото копче на глувчето и повлечете ја рачката за пополнување до крајот на колоната.
- Како што можете да видите, целата колона е исполнета со податоци кои го опишуваат учеството на приходите од продажбата на секој производ. Но, вредноста на специфичната тежина е прикажана во нумерички формат, и ние треба да ја претвориме во процент. За да го направите ова, одберете ја содржината на колоната "Сподели". Потоа преминете на табот "Дома". На лентата во групата на поставки "Број" Постои поле кое го прикажува формат на податоци. Стандардно, ако не извршивте дополнителни манипулации, формата треба да биде поставена таму. "Општо". Ние кликнете на иконата во форма на триаголник кој се наоѓа десно од ова поле. Во листата со формати што се отвора, одберете ја позицијата "Интерес".
- Како што можете да видите, сите вредности на колоната се претворени во проценти. Како што треба, во линија "Вкупно" индицирано 100%. Процентот на стоки што се очекува да бидат лоцирани во колоната од поголеми до помали.
- Сега треба да креираме колона во која ќе се прикаже кумулативниот удел со кумулативен вкупен износ. Тоа е, во секој ред, специфичната тежина на сите оние стоки што се наоѓаат во горната листа ќе се додадат на поединечната специфична тежина на одреден производ. За првата ставка во списокот (Точка 3) поединечната специфична тежина и акумулираната акција ќе бидат еднакви, но за сите последователни, акумулираното учество на претходната ставка во листата ќе мора да се додаде во поединечниот индикатор.
Значи, во првиот ред пренесуваме на колоната "Акумулирано споделување" стапка на колона "Сподели".
- Потоа, поставете го курсорот во ќелијата од втората колона. "Акумулирано споделување". Тука мора да ја примениме формулата. Ние ставивме знак еднакво и преклопете ја содржината на ќелијата "Сподели" истиот ред и содржината на ќелијата "Акумулирано споделување" од линијата погоре. Сите врски се релативни, односно не ги извршуваме манипулации со нив. После тоа кликнете на копчето. Внесете за да го прикаже конечниот резултат.
- Сега треба да ја ископирате оваа формула на клетките од оваа колона, кои се наоѓаат подолу. За да го направите ова, користете го маркерот за пополнување, на кој веќе прибегнавме кон копирање на формулата во колоната "Сподели". Во исто време, стрингот "Вкупно" фаќањето не е неопходно, бидејќи акумулираниот резултат во 100% ќе бидат прикажани на последната ставка од листата. Како што можете да видите, сите елементи од нашата колумна после тоа беа пополнети.
- Потоа креираме колона "Група". Ние ќе треба да ги групираме производите во категории A, Б. и C според кумулативното учество. Како што се сеќаваме, сите елементи се поделени во групи според следната шема:
- A - до 80%;
- Б. - следново 15%;
- Со - останатите 5%.
Така, сите стоки, кумулативниот дел од специфичната тежина на која влегува во границата 80%доделите категорија A. Стоки со акумулирана специфична тежина на 80% до 95% доделите категорија Б.. Останатиот производ група со вредност на повеќе 95% натрупаната специфична тежина доделува категорија C.
- За јасност, можете да ги наполните овие групи во различни бои. Но, ова е изборно.
Така, ги раздвоивме елементите во групи според нивото на важност, користејќи ја ABC анализата. Кога користете некои други методи, како што е споменато погоре, применувајте партиција во повеќе групи, но принципот на поделба останува речиси непроменет.
Лекција: Сортирање и филтрирање во Excel
Метод 2: користење на комплексна формула
Се разбира, употребата на сортирање е најчестиот начин за спроведување ABC анализи во Excel. Но, во некои случаи е потребно да се спроведе оваа анализа без да се редефинираат редовите во изворната табела. Во овој случај, комплицирана формула ќе дојде до спасување. На пример, ние ќе ја користиме истата изворна табела како во првиот случај.
- Додадете во оригиналната табела која ги содржи името на стоката и средствата од продажбата на секоја од нив, колоната "Група". Како што можете да видите, во овој случај не можеме да додадеме колони со пресметка на индивидуални и кумулативни акции.
- Изберете ја првата ќелија во колоната. "Група"потоа кликнете на копчето. "Вметни ја функцијата"лоциран во близина на формулата бар.
- Се врши активација Функција мајстори. Премести во категорија "Линкови и низи". Изберете функција "Избери". Кликнете на копчето. "Добро".
- Прозорецот за аргументите за функции е активиран. ИЗБОР. Неговата синтакса е како што следува:
= SELECT (index_number; value1; value2; ...)Целта на оваа функција е да се изведе една од наведените вредности, во зависност од бројот на индексот. Бројот на вредности може да достигне 254, но потребни ни се само три имиња кои одговараат на категориите на ABC анализата: A, Б., Со. Ние веднаш може да влезе во полето "Value1" симболот "А"во полето "Value2" - "Б"во полето "Value3" - "Ц".
- Но со аргумент "Број на индекс" Ќе биде неопходно внимателно да се мешаат, имајќи вградено некои дополнителни оператори. Поставете го курсорот во полето "Број на индекс". Следно, кликнете на иконата која има форма на триаголник лево од копчето "Вметни ја функцијата". Се отвора листа на неодамна користени оператори. Потребна ни е функција НАТПРЕВАР. Бидејќи не е на листата, кликнете на насловот "Други карактеристики ...".
- Повторно го стартува прозорецот. Функција мајстори. Повторно одете во категоријата "Линкови и низи". Таму наоѓаме позиција "НАТПРЕВАР"изберете го и кликнете на копчето "Добро".
- Се отвори прозорец на оператор НАТПРЕВАР. Неговата синтакса е како што следува:
= НАТПРЕВАР (пронажечена вредност; разгледувана низа; тип на совпаѓање)Целта на оваа функција е да се одреди бројот на позицијата на наведениот елемент. Тоа е, токму она што ни треба за полето "Број на индекс" функции ИЗБОР.
Во полето "Проследена низа" Можете веднаш да го поставите следниов израз:
{0:0,8:0,95}Тоа треба да биде токму во кадрава заграда, како формула за низа. Не е тешко да се претпостави дека овие броеви (0; 0,8; 0,95) ги означуваат границите на акумулираниот удел помеѓу групите.
Поле "Тип на мапирање" не е задолжително и во овој случај ние нема да го пополниме.
Во полето "Бараната вредност" Поставете го курсорот. Потоа повторно, преку горенаведената икона во форма на триаголник, се преселуваме Функционален волшебник.
- Овој пат во Функционален волшебник преминете во категорија "Математичка". Изберете име "SUMMESLI" и кликнете на копчето "Добро".
- Се започнува прозорецот за аргумент за функции. Суми. Специфицираниот оператор ги сумира клетките кои ги исполнуваат наведените услови. Неговата синтакса е:
= SUMMES (опсег, критериум, range_summing)Во полето "Опсег" внесете ја адресата на колоната "Приходи". За овие цели го поставивме курсорот во полето, а потоа, откако го стегнавме левото копче на глувчето, ги избравме сите ќелии од соодветната колона, исклучувајќи ја вредноста "Вкупно". Како што можете да видите, адресата веднаш се прикажува во полето. Покрај тоа, ние треба да ја направиме оваа врска апсолутна. За да го направите ова, направете го својот избор и притиснете го копчето F4. Адресата е истакната со знаци за долар.
Во полето "Критериум" ние треба да поставите состојба. Внесете го следниов израз:
">"&Потоа веднаш по неа влегуваме во адресата на првата ќелија на колоната. "Приходи". Ние ги направиме хоризонталните координати во оваа адреса апсолутни, додавајќи знак за долар од тастатурата пред писмото. Вертикалните координати се релативни, односно нема знаци пред бројот.
После тоа, не притиснете го копчето "Добро", и кликнете на името на функцијата НАТПРЕВАР во формула бар.
- Потоа се враќаме во прозорецот за аргумент на функцијата. НАТПРЕВАР. Како што можете да видите, во полето "Бараната вредност" податоците се појавија од операторот Суми. Но, тоа не е сè. Одете на ова поле и додајте го знакот на постојните податоци. "+" без наводници. Потоа внесете ја адресата на првата ќелија од колоната. "Приходи". И повторно ги правиме хоризонталните координати на оваа врска апсолутно, а вертикално оставаме роднина.
Следно, земете ја целата содржина на полето "Бараната вредност" во загради, потоа ставете го знакот за поделба ("/"). После тоа, повторно преку иконата на триаголникот, одете во прозорецот за избор на функција.
- Како последен пат во трчање Функционален волшебник во потрага по саканиот оператор во категоријата "Математичка". Овој пат се повикува саканата функција "SUMM". Изберете го и кликнете на копчето. "Добро".
- Се отвори прозорец на оператор SUM. Неговата главна цел е збир на податоци во клетките. Синтаксата на оваа изјава е прилично едноставна:
= SUM (број 1, број2; ...)За нашите цели ни треба само поле. "Број1". Внесете ги координатите на опсегот на колоната "Приходи", со исклучок на ќелијата која ги содржи вкупните. Ние веќе извршивме слична операција во оваа област. "Опсег" функции Суми. Како во тоа време ние правиме апсолутни координати на опсегот со нивно избирање и притискање на копчето F4.
После тоа кликнете на копчето "Добро" на дното на прозорецот.
- Како што можете да видите, комплексот на внесените функции произведе пресметка и го даде резултатот во првата ќелија на колоната "Група". На првата ставка му била доделена група. "А". Целосната формула што ја користевме за оваа пресметка е како што следува:
= SELECT (MATCH ((SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ), "А", "Б", "Ц")Но, се разбира, во секој случај, координатите во оваа формула ќе бидат различни. Затоа, тоа не може да се смета за универзално. Но, користејќи го прирачникот што беше даден погоре, можете да ги внесете координатите на секоја табела и успешно да го примените овој метод во секоја ситуација.
- Сепак, ова не е сè. Ние пресметавме само за првиот ред од табелата. Целосно да се пополни колоната со податоци "Група", треба да ја ископирате оваа формула до опсегот подолу (со исклучок на ќелијата со ред "Вкупно") користејќи го маркерот за пополнување, како што направивме повеќе од еднаш. Откако ќе се внесат податоците, анализата на ABC може да се смета за комплетна.
Како што можете да видите, резултатите добиени со користење на варијанта со употреба на комплексна формула воопшто не се разликуваат од резултатите што ги извршивме со сортирање. Сите производи се доделуваат на истите категории, но линиите не ја менуваат нивната почетна позиција.
Лекција: Волшебник за функции на Excel
Excel во голема мера може да ја олесни ABC анализата за корисникот. Ова се постигнува со помош на алатки како сортирање. Потоа, се пресметува индивидуалната специфична тежина, акумулираниот удел и, всушност, поделбата во групи. Во случај кога промената на почетната позиција на редовите во табелата не е дозволена, можете да го примените методот со користење на комплексна формула.
