Адным з ключавых метадаў менеджменту і лагістыкі з'яўляецца ABC-аналіз. З яго дапамогай можна класіфікаваць рэсурсы прадпрыемства, тавары, кліентаў і г.д. па ступені важнасці. Пры гэтым па ўзроўні важнасці кожнай вышэйпералічанай адзінцы прысвойваецца адна з трох катэгорый: A, B або C. Праграма Excel мае ў сваім багажы інструменты, якія дазваляюць палегчыць правядзенне такога роду аналізу. Давайце разбярэмся, як імі карыстацца, і што ж сабой уяўляе ABC-аналіз.
Выкарыстанне ABC-аналізу
ABC-аналіз з'яўляецца свайго роду удасканаленым і прыстасаваным да сучасных умоў варыянтам прынцыпу Парэта. Паводле методыкі яго правядзення, усе элементы аналізу дзеляцца на тры катэгорыі па ступені важнасці:
- катэгорыя A - элементы, якія маюць у сукупнасці больш 80% ўдзельнай вагі;
- катэгорыя B - элементы, сукупнасць якіх складае ад 5% да 15% ўдзельнай вагі;
- катэгорыя C - пакінутыя элементы, агульная сукупнасць якіх складае 5% і менш ўдзельнай вагі.
Асобныя кампаніі ўжываюць больш прасунутыя методыкі і разбіваюць элементы не на 3, а на 4 ці 5 груп, але мы будзем абапірацца на класічную схему ABC-аналізу.
Спосаб 1: аналіз пры дапамозе сартавання
У Эксэля ABC-аналіз выконваецца пры дапамозе сартавання. Усе элементы адсартоўвацца ад большага да меншага. Затым падлічваецца назапашвальны удзельная вага кожнага элемента, на падставе чаго яму прысвойваецца пэўная катэгорыя. Давайце на канкрэтным прыкладзе высветлім, як паказаная методыка прымяняецца на практыцы.
У нас маецца табліца з пералікам тавараў, якія прадпрыемства рэалізуе, і адпаведным колькасцю выручкі ад іх продажу за пэўны перыяд часу. Унізе табліцы падбіты вынік выручкі ў цэлым па ўсіх назвах тавараў. Стаіць задача, выкарыстоўваючы ABC-аналіз, разбіць гэтыя тавары на групы па іх важнасці для прадпрыемства.
- Вылучаем табліцу з дадзенымі курсорам, заціснуўшы левую кнопку мышкі, выключаючы шапку і выніковую радок. Пераходзім ва ўкладку "Дадзеныя". Вырабляем пстрычка па кнопцы "Сартаванне", Размешчанай у блоку інструментаў "Сартаванне і фільтр" на стужцы.
Можна таксама паступіць па-іншаму. Вылучаем паказаны вышэй дыяпазон табліцы, затым перамяшчаемся ва ўкладку "Галоўная" і выконваем пстрычка па кнопцы "Сартаванне і фільтр", Размешчанай у блоку інструментаў "Рэдагаванне" на стужцы. Актывуецца спіс, у якім выбіраем у ім пазіцыю "Наладжвальная сартаванне".
- Што датычыцца прымянення любога з вышэйзгаданых дзеянняў запускаецца акно налады сартавання. Глядзім, каб каля параметру "Мае дадзеныя ўтрымліваюць загалоўкі" была ўсталяваная галачка. У выпадку яе адсутнасці, усталёўваем.
У полі "Слупок" паказваем найменне той калонкі, у якой змяшчаюцца дадзеныя па выручцы.
У полі "Сартаванне" трэба паказаць, па якім канкрэтным крытэры будзе выконвацца сартаванне. Пакідаем прадусталяваныя налады - "Значэнні".
У полі "Парадак" выстаўляем пазіцыю "Па змяншэнні".
Пасля творы названых настроек націскаем на кнопку "OK" у ніжняй частцы акна.
- Пасля выканання названага дзеянні ўсе элементы былі адсартаваныя па выручцы ад большага да меншага.
- Цяпер нам варта разлічыць удзельная вага кожнага з элементаў для агульнага выніку. Ствараем для гэтых мэтаў дадатковы слупок, які так і назавем "Удзельная вага". У першай вочку дадзенай калонкі ставім знак "=", Пасля чаго паказваем спасылку на вочка, у якой знаходзіцца сума выручкі ад рэалізацыі адпаведнага тавару. Далей усталёўваем знак дзялення ("/"). Пасля гэтага паказваем каардынаты ячэйкі, у якой утрымліваецца выніковая сума рэалізацыі тавараў па ўсім прадпрыемству.
Улічваючы той факт, што азначанай формулу мы будзем капіяваць у іншыя вочкі слупка "Удзельная вага" пасродкам маркера запаўнення, то адрас спасылкі на элемент, які змяшчае выніковую велічыню выручкі па прадпрыемстве, нам трэба зафіксаваць. Для гэтага робім спасылку абсалютнай. Вылучаем каардынаты названай вочкі ў формуле і ціснем на клавішу F4. Перад каардынатамі, як мы бачым, з'явіўся знак даляра, што сведчыць пра тое, што спасылка стала абсалютнай. Пры гэтым трэба ўлічыць, што спасылка на велічыню выручкі першага ў спісе тавару (тавар 3) Павінна заставацца адноснай.
Затым, каб зрабіць вылічэнні, ціснем на кнопку Enter.
- Як бачым, удзельная вага выручкі ад першага тавару, названай у спісе, адлюстраваўся ў мэтавай вочку. Каб вырабіць капіраванне формулы ў дыяпазон ніжэй, ставім курсор у правы ніжні кут вочка. Адбываецца яго трансфармацыя ў маркер запаўнення, які мае выгляд невялікага крыжык. Ціснем левую кнопку мышы і перацягваюць маркер запаўнення ўніз да канца калонкі.
- Як бачым, увесь слупок запоўнены дадзенымі, якія характарызуюць удзельная вага выручкі ад рэалізацыі кожнага тавару. Але велічыня ўдзельнай вагі адлюстроўваецца ў лікавым фармаце, а нам трэба трансфармаваць яго ў працэнтны. Для гэтага вылучаем змесціва слупка "Удзельная вага". Затым перамяшчаемся ва ўкладку "Галоўная". На стужцы ў групе налад "Лічба" маецца поле адлюстроўвае фармат дадзеных. Па змаўчанні, калі вы не выраблялі дадатковых маніпуляцый, там павінен быць усталяваны фармат "Агульны". Пстрыкаем па піктаграме ў выглядзе трыкутніка, размешчанай справа ад гэтага поля. У якое адкрылася спісе фарматаў выбіраем пазіцыю "Адсоткавы".
- Як бачым, усе значэння слупка былі ператвораныя ў працэнтныя велічыні. Як і належыць, у радку "Разам" паказана 100%. Удзельная вага тавараў чакана размяшчаецца ў Стоўбцах ад большага да меншага.
- Цяпер нам варта стварыць слупок, у якім бы адлюстроўвалася назапашаная доля з нарастаючым вынікам. Гэта значыць, у кожнай радку да індывідуальнага ўдзельнай вазе канкрэтнага тавару будзе дадавацца ўдзельная вага ўсіх тых тавараў, якія размешчаны ў пераліку вышэй. Для першага тавара ў спісе (тавар 3) Індывідуальны удзельная вага і назапашаная доля будуць роўнымі, а вось ва ўсіх наступных да індывідуальнага паказчыку трэба будзе дадаць назапашаную долю папярэдняга элемента спісу.
Такім чынам, у першым радку пераносім у слупок "Назапашаная доля" паказчык з калонкі "Удзельная вага".
- Далей усталёўваем курсор у другую вочка слупка "Назапашаная доля". Тут нам прыйдзецца прымяніць формулу. ставім знак "Роўна" і складаем змесціва ячэйкі "Удзельная вага" гэтай жа радкі і змесціва ячэйкі "Назапашаная доля" з радка вышэй. Усе спасылкі пакідаем адноснымі, гэта значыць, не вырабляем з імі ніякіх маніпуляцый. Пасля гэтага выконваем пстрычка па кнопцы Enter для вываду выніковага выніку.
- Зараз трэба скапіяваць дадзеную формулу ў вочкі дадзенага слупка, якія размешчаны ніжэй. Для гэтага ўжываем маркер запаўнення, да якога мы ўжо звярталіся пры капіяванні формулы ў Стоўбцах "Удзельная вага". Пры гэтым, радок "Разам" захопліваць ня трэба, бо назапашаны вынік у 100% будзе адлюстроўвацца на апошнім тавары з спісу. Як бачым, усе элементы нашага слупка пасля гэтага былі запоўненыя.
- Пасля гэтага ствараем слупок "Гурт". Нам трэба будзе згрупаваць тавары па катэгорыях A, B і C згодна з названай назапашанай долі. Як мы памятаем, усё элементы размяркоўваюцца па групах па наступнай схеме:
- A - да 80%;
- B - наступныя 15%;
- З - тыя, што засталіся 5%.
Такім чынам, ўсіх таварах, назапашаная доля ўдзельнай вагі якіх уваходзіць у мяжу да 80%, Прысвойваем катэгорыю A. Таварах з назапашаным удзельнай вагой ад 80% да 95% прысвойваем катэгорыю B. Пакінутай групе тавараў са значэннем больш 95% назапашанага ўдзельнай вагі прысвойваем катэгорыю C.
- Для нагляднасці можна вырабіць заліванне названых груп рознымі кветкамі. Але гэта ўжо па жаданні.
Такім чынам, мы разбілі элементы на групы па ўзроўні важнасці, выкарыстоўваючы пры гэтым ABC-аналіз. Пры выкарыстанні некаторых іншых методык, пра што гаварылася ўжо вышэй, ужываюць разбіццё на большую колькасць груп, але сам прынцып разбіцця пры гэтым застаецца практычна нязменным.
Урок: Сартаванне і фільтраванне ў Эксэля
Спосаб 2: выкарыстанне складанай формулы
Безумоўна, прымяненне сартавання - гэта найбольш распаўсюджаны спосаб правядзення ABC-аналізу ў Эксэля. Але ў некаторых выпадках патрабуецца правесці дадзены аналіз без перастаноўкі радкоў месцамі ў зыходнай табліцы. У гэтым выпадку на дапамогу прыйдзе складаная формула. Для прыкладу будзем выкарыстоўваць тую ж зыходную табліцу, што і ў першым выпадку.
- Дадаем да зыходнай табліцы, якая змяшчае найменне тавараў і выручку ад продажу кожнага з іх, калонку "Гурт". Як бачым, у дадзеным выпадку мы можам не дадаваць слупкі з разлікам індывідуальных і накапляльных доляй.
- Вырабляем вылучэнне першай ячэйкі ў Стоўбцах "Гурт", Пасля чаго выконваем пстрычка па кнопцы "Уставіць функцыю", Размешчанай каля радкі формул.
- вырабляецца актывацыя майстры функцый. Перамяшчаемся ў катэгорыю "Спасылкі і масівы". выбіраем функцыю "ВЫБАР". Робім пстрычка па кнопцы "OK".
- Актывуецца акно аргументаў функцыі ВЫБАР. Сінтаксіс яе прадстаўлены наступным чынам:
= ВЫБАР (Номер_индекса; Значение1; Значение2; ...)Задачай дадзенай функцыі з'яўляецца выснова аднаго з названых значэнняў, у залежнасці ад нумара азначніка. Колькасць значэнняў можа дасягаць 254, але нам спатрэбіцца ўсяго тры найменні, якія адпавядаюць катэгорыям ABC-аналізу: A, B, З. Можам адразу ўводзіць у поле "Значение1" сімвал "A", У поле "Значение2" - "B", У поле "Значение3" - "C".
- А вось з аргументам "Нумар індэкса" прыйдзецца грунтоўна павазіцца, ўбудаваць у яго некалькі дадатковых аператараў. Усталёўваем курсор у полі "Нумар індэкса". Далей ціснем па піктаграме, якая мае выгляд трыкутніка, злева ад кнопкі "Уставіць функцыю". Адкрываецца спіс нядаўна выкарыстоўваюцца аператараў. Нам патрэбна функцыя ПОИСКПОЗ. Бо ў спісе яе няма, то ціснем па надпісе "Іншыя функцыі ...".
- Зноў вырабляецца запуск вокны майстры функцый. Зноў пераходзім у катэгорыю "Спасылкі і масівы". Знаходзім там пазіцыю "ПОИСКПОЗ", Вылучаем яе і робім пстрычка па кнопцы "OK".
- Адкрываецца акно аргументаў аператара ПОИСКПОЗ. Сінтаксіс яго мае наступны выгляд:
= ПОИСКПОЗ (Искомое_значение; Просматриваемый_массив; Тип_сопоставления)Прызначэнне дадзенай функцыі - гэта вызначэнне нумары пазіцыі названага элемента. Гэта значыць, як раз тое, што нам трэба для поля "Нумар індэкса" функцыі ВЫБАР.
У полі "Праглядаю масіў" адразу можна задаць наступнае выраз:
{0:0,8:0,95}Яно павінна быць менавіта ў фігурных дужках, як формула масіва. Не цяжка здагадацца, што гэтыя лікі (0; 0,8; 0,95) Пазначаюць межы назапашанай долі паміж групамі.
поле "Тып супастаўлення" не павінна быць абавязковым і ў дадзеным выпадку мы яго запаўняць не будзем.
У полі "Пазовам значэнне" усталёўваем курсор. Далей зноў праз апісаную вышэй піктаграму ў выглядзе трыкутніка перамяшчаемся ў майстар функцый.
- На гэты раз у майстру функцый вырабляем перасоўванне ў катэгорыю "Матэматычныя". выбіраем найменне "СУММЕСЛИ" і ціснем на кнопку "OK".
- Запускаецца акно аргументаў функцыі СУММЕСЛИ. Ўказаны аператар сумуе ячэйкі, якія адказваюць вызначаным умове. Яго сінтаксіс такі:
= СУММЕСЛИ (дыяпазон; крытэр; диапазон_суммирования)У полі "Дыяпазон" ўводзім адрас калонкі "Выручка". Для гэтых мэтаў усталёўваем курсор у полі, а затым, вырабячы заціск левай кнопкі мышы, вылучаем усе вочкі адпаведнага слупка, выключаючы значэнне "Разам". Як бачым, адрас тут жа адлюстраваўся ў поле. Акрамя таго, нам трэба зрабіць дадзеную спасылку абсалютнай. Для гэтага вырабляем яе вылучэнне і ціснем на клавішу F4. Адрас вылучыўся знакамі даляра.
У полі "Крытэрый" нам трэба задаць ўмова. Ўпісваем наступнае выраз:
">"&Затым адразу ж пасля яго заносім адрас першай ячэйкі слупка "Выручка". Робім каардынаты па гарызанталі ў дадзеным адрасе абсалютнымі, дапісаўшы перад літарай знак даляра з клавіятуры. Каардынаты па вертыкалі пакідаем адноснымі, гэта значыць, перад лічбай ніякага знака быць не павінна.
Пасля гэтага не ціснем на кнопку "OK", А клікаем па найменні функцыі ПОИСКПОЗ у радку формул.
- Затым мы вяртаемся ў акно аргументаў функцыі ПОИСКПОЗ. Як бачым, у поле "Пазовам значэнне" з'явіліся дадзеныя зададзеныя аператарам СУММЕСЛИ. Але гэта яшчэ не ўсё. Пераходзім у гэта поле і ўжо да наяўных дадзеных дадаем знак "+" без двукоссяў. Затым ўносім адрас першай ячэйкі слупка "Выручка". І зноў робім каардынаты па гарызанталі дадзенай спасылкі абсалютнымі, а па вертыкалі пакідаем адноснымі.
Далей бярэм усё змесціва поля "Пазовам значэнне" у дужкі, пасля чаго ставім знак дзялення ("/"). Пасля гэтага зноў праз піктаграму трыкутніка пераходзім да акна выбару функцый.
- Як і ў мінулы раз у запусцім майстру функцый шукаем патрэбны аператар у катэгорыі "Матэматычныя". На гэты раз шуканая функцыя называецца "Сум". Вылучаем яе і ціснем на кнопку "OK".
- Адкрываецца акно аргументаў аператара сум. Яго галоўнае прызначэнне - гэта сумаванне дадзеных у вочках. Сінтаксіс гэтага аператара даволі просты:
= Сум (Число1; Число2; ...)Для нашых мэтаў спатрэбіцца толькі поле "Число1". Ўводзім ў яго каардынаты дыяпазону слупка "Выручка", Выключаючы вочка, якая змяшчае вынікі. Падобную аперацыю мы ўжо праводзілі ў поле "Дыяпазон" функцыі СУММЕСЛИ. Як і ў той раз, каардынаты дыяпазону робім абсалютныя, вылучыўшы іх, і націснуў на клавішу F4.
Пасля гэтага ціснем па клавішы "OK" унізе акна.
- Як бачым, комплекс уведзеных функцый вырабіў вылічэнне і выдаў вынік у першую вочка слупка "Гурт". Першаму тавару была прысвоена група "A". Поўная формула, ужытая намі для дадзенага вылічэнні, выглядае наступным чынам:
= ВЫБАР (ПОИСКПОЗ ((СУММЕСЛИ ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / сум ($ B $ 2: $ B $ 27); {0: 0,8: 0,95} ); "A"; "B"; "C")Але, вядома, у кожным канкрэтным выпадку каардынаты ў дадзенай формуле будуць адрознівацца. Таму яе нельга лічыць універсальнай. Але, выкарыстоўваючы тое кіраўніцтва, якое было прыведзена вышэй, можна ўставіць каардынаты любой табліцы і з поспехам прымяняць дадзены спосаб у любой сітуацыі.
- Зрэшты, гэта яшчэ не ўсё. Мы вырабілі разлік толькі для першага радка табліцы. Для таго, каб цалкам запоўніць дадзенымі слупок "Гурт", Трэба скапіяваць гэтую формулу ў дыяпазон ніжэй (выключаючы вочка радкі "Разам") З дапамогай маркера запаўнення, як мы ўжо рабілі не раз. Пасля таго, як дадзеныя будуць унесены, ABC-аналіз можна лічыць выкананым.
Як бачым, вынікі, атрыманыя пры дапамозе варыянту з ужываннем складанай формулы, ані не адрозніваюцца ад тых вынікаў, якія мы праводзілі шляхам сартавання. Ўсіх таварах прысвоены тыя ж самыя катэгорыі, толькі пры гэтым радкі не змянілі свайго пачатковага становішча.
Урок: Майстар функцый у Эксэля
Праграма Excel здольная значна палегчыць правядзенне ABC-аналізу для карыстальніка. Гэта дасягаецца выкарыстаннем такога інструмента, як сартаванне. Пасля гэтага вырабляецца падлік індывідуальнага ўдзельнай вагі, назапашанай долі і, уласна, разбіццё на групы. У тых выпадках, калі змяненне першапачатковага становішча радкоў у табліцы не дапускаецца, можна ўжыць метад з выкарыстаннем складанай формулы.
