ABC анализа во Excel

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

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

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

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

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

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

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

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

1. Ние истакнуваме табела со курсорот со затворање на левото копче на глувчето, со исклучок на капачето и конечниот стринг. Одете во табулаторот "Податоци". Ние кликнете на копчето "Сортирај", кој се наоѓа во лентата со алатки "Сортирај и филтер" на снимката.

   

   Можете исто така да направите поинаку. Ние го одредуваме горниот опсег на маса, а потоа се преместиме во табулаторот "Дома" и кликнете на копчето "Сортирај и филтер" кое се наоѓа во алатникот за уредување на лентата. Листата е активирана во која е активирана позицијата "прилагодливо сортирање".



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

   Во полето "колона", наведете го името на колоната во која се содржани податоците од приходите.

   Во полето "Сортирај", треба да наведете, на кои ќе бидат сортирани специфични критериуми. Оставете ги претходно поставените поставки - "вредности".

   Во полето "Ред", изложба на позицијата "опаѓа".

   По производот на наведените поставки, кликнете на копчето "OK" на дното на прозорецот.



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



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

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

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



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



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



7. Како што гледаме, сите вредности на колоните беа трансформирани во процентуални вредности. Како што треба да биде, 100% наведено во низата "вкупно". Уделот на стоки се очекува во колоната од поголем до помал.



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

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



9. Следно, поставете го курсорот во втората ќелија на колоната "Акумулирана акција". Тука мораме да ја примениме формулата. Го ставивме знакот "еднаков" и преклопувајте ја содржината на клетката "Пропорцијата" на истата линија и содржината на клетката "Акумулирано учество" од низата погоре. Сите референци се резервирани роднини, односно не произведуваме манипулации со нив. После тоа, извршете кликнете на копчето Enter за да го прикажете конечниот резултат.



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



11. После тоа, креирајте колона "група". Ние ќе треба да ги групираме стоките по категорија А, Б и Ц според наведеното акумулирано учество. Додека се сеќаваме, сите елементи се дистрибуираат од групи според следната шема:
    • А - до 80%;
    • Б - следните 15%;
    • В - останатите 5%.

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



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

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

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

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

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

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



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



3. Магистерски активирање на функции. Ние се преселиме во категоријата "Линкови и низи". Изберете ја функцијата "Избор". Ние правиме кликнување на копчето "OK".



4. Прозорецот за аргумент е активиран. Синтаксата е претставена на следниов начин:

   = Избор (број_intex; вредност1; вредност2; ...)

   Задачата на оваа функција е повлекување на една од наведените вредности, во зависност од бројот на индексот. Бројот на вредности може да достигне 254, но ние ќе ни требаат само три имиња кои одговараат на ABC-анализа категории: A, B, C. Ние веднаш можеме да го внесеме симболот "А" во полето "Б" во "Б "Поле, поле" вредност3 "-" Ц ".



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



6. Прозорецот на прозорецот за волшебникот на функциите се стартува повторно. Повторно, одете во категоријата "Линкови и низи". Ние ја наоѓаме позицијата на "Одборот за пребарување", го распределувате и кликнете на копчето "OK".



7. Се отвора аргументите на аргументите на операторот за пребарување. Синтаксата на неа ја има следната форма:

   = Одбор за пребарување (search_name; Viewing__massive; type_station)

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

   Во полето "Оглас низа", веднаш можете да го поставите следниот израз:

   {0: 0.8: 0,95}

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

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

   Во полето "Втора вредност", поставете го курсорот. Следно повторно преку иконата опишана погоре во форма на триаголник, ние се движиме во волшебникот на функциите.



8. Овој пат во волшебникот за функции, ние се движиме во категоријата "Математички". Изберете го името "Silent" и притиснете го копчето "OK".



9. Прозорецот за аргументи на функција ќе биде лансиран. Наведениот оператор ги сумира клетките кои ја задоволуваат дефинитивната состојба. Неговата синтакса е:

   = Молчи (опсег, критериум, опсег_SUMING)

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

   Во полето "Критериум", треба да поставиме состојба. Внесете го следниов израз:

   ">"&

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

   После тоа, ние не го притискаме копчето "OK" и кликнете на името на функцијата за пребарување во стрингот на формулата.



10. Потоа се враќаме во прозорецот за аргументи на одборот за пребарување. Како што гледаме, во полето "Фоварно значење", податоците поставени од операторот молчеа. Но, тоа не е сè. Одете на ова поле и веќе ги додава достапните податоци за да додадете знак "+" без цитати. Потоа ја воведуваме адресата на првата ќелија на колоната "Приход". И повторно ги правиме координира хоризонтално со апсолутно, и вертикално остави роднина.

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



11. Како последен пат во волшебникот за работа, го бараме потребниот оператор во категоријата "Математичка". Овој пат саканата функција се нарекува "суми". Го нагласуваме и притиснете го копчето "OK".



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

    = Суми (број 1; број2; ...)

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

    После тоа кликнете на копчето "OK" на дното на прозорецот.



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

    = Избор (одбор за пребарување ((молчи ($ b $ 2: $ b $ 27; ">" & $ b2) + $ b2) / износи ($ b $ 2: $ b $ 27); {0: 0.8: 0 , 95}); "А"; "Б"; "Ц")

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



14. Сепак, ова не е сè. Ние пресметавме само за првиот ред на табелата. Со цел целосно да ја пополните групата "група", треба да ја копирате оваа формула во опсегот подолу (елиминирање на клетката на стрингот "Вкупно") користејќи го ознаката за полнење, како што веќе направивме повеќе од еднаш. Откако ќе се направат податоците, ABC анализата може да се смета за изведена.

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

Лекција: Мајстор на функции во Excele

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