Застосування аналізу ABC у Microsoft Excel

Зміст

• Використання аналізу ABC
  • Спосіб 1: Аналіз за допомогою сортування
  • Спосіб 2: використання складної формули
• Питання та відповіді

Одним з ключових методів менеджменту і логістики є ABC-аналіз. З його допомогою можна класифікувати ресурси підприємства, товари, клієнтів і т.д. за ступенем важливості. При цьому за рівнем важливості кожної перерахованої вище одиниці присвоюється одна з трьох категорій: a, b або C. програма Excel має в своєму багажі інструменти, які дозволяють полегшити проведення такого роду аналізу. Давайте розберемося, як ними користуватися, і що ж собою являє ABC-аналіз.

Використання аналізу ABC

ABC-аналіз є свого роду вдосконаленим і пристосованим до сучасних умов варіантом принципу Парето. Згідно з методикою його проведення, всі елементи аналізу розбиваються на три категорії за ступенем важливості:

Окремі компанії застосовують більш просунуті методики і розбивають елементи не на 3, а на 4 або 5 груп, але ми будемо спиратися на класичну схему ABC-аналізу.

Спосіб 1: Аналіз за допомогою сортування

В Ексель ABC-аналіз виконується за допомогою сортування. Всі елементи відсортовуються від більшого до меншого. Потім підраховується накопичувальний питома вага кожного елемента, на підставі чого йому присвоюється певна категорія. Давайте на конкретному прикладі з'ясуємо, як зазначена методика застосовується на практиці.

У нас є таблиця з переліком товарів, які підприємство реалізує, і відповідною кількістю виручки від їх продажу за певний період часу. Внизу таблиці підбитий підсумок виручки в цілому по всіх найменуваннях товарів. Стоїть завдання, використовуючи ABC-аналіз, розбити ці товари на групи по їх важливості для підприємства.

1. Виділяємо таблицю з даними курсором, затиснувши ліву кнопку мишки, виключаючи шапку і підсумковий рядок. Переходимо у вкладку»дані" . Виробляємо клацання по кнопці " сортування» , розташованої в блоці інструментів " сортування та фільтр» на стрічці.

   Можна також вчинити по-іншому. Виділяємо вказаний вище діапазон таблиці, потім переміщаємося у вкладку»Головна" і виконуємо клацання по кнопці " сортування та фільтрування» , розташованої в блоці інструментів «редагування» на стрічці. Активується список, в якому вибираємо в ньому позицію " спеціальне сортування» .


2. При застосуванні будь-якого з вищевказаних дій запускається вікно настройки сортування. Дивимося, щоб близько параметра " Мої дані містять заголовки» встановлено галочку. У разі її відсутності, встановлюємо.

   У полі " стовпець» вказуємо найменування тієї колонки, в якій містяться дані по виручці.

   У полі " сортування» потрібно вказати, за яким конкретним критерієм буде виконуватися сортування. Ми залишаємо встановлені настройки – " Значення» .

   У полі «Порядок» виставляємо позицію " за спаданням» .

   Після твори зазначених налаштувань натискаємо на кнопку «OK» у нижній частині вікна.


3. Після виконання зазначеної дії всі елементи були відсортовані по виручці від більшого до меншого.

4. Тепер нам слід розрахувати питому вагу кожного з елементів для загального підсумку. Створюємо для цих цілей додатковий стовпець, який так і назвемо " питома вага» . У першій клітинці даної колонки ставимо знак «=» , після чого вказуємо посилання на комірку, в якій знаходиться сума виручки від реалізації відповідного товару. Далі встановлюємо знак ділення ( «/» ). Після цього вказуємо координати комірки, в якій міститься підсумкова сума реалізації товарів по всьому підприємству.

   Враховуючи той факт, що зазначену формулу ми будемо копіювати в інші комірки стовпця «питома вага» за допомогою маркера заповнення, то адреса посилання на елемент, що містить підсумкову величину виручки по підприємству, нам потрібно зафіксувати. Для цього робимо посилання абсолютної. Виділяємо координати зазначеної комірки у формулі і тиснемо на клавішу F4 . Перед координатами, як ми бачимо, з'явився знак долара, що свідчить про те, що посилання стало абсолютним. При цьому потрібно врахувати, що посилання на величину виручки першого в списку товару ( Товар 3 ) повинна залишатися відносною.

   Потім, щоб зробити обчислення, тиснемо на кнопку Enter .


5. Як бачимо, питома вага виручки від першого товару, зазначеного в списку, відобразився в цільовій комірці. Щоб зробити копіювання формули в діапазон нижче, ставимо курсор в правий нижній кут осередку. Відбувається його трансформація в маркер заповнення, що має вигляд невеликого хрестика. Тиснемо ліву кнопку миші і перетягуємо маркер заповнення вниз до кінця колонки.

6. Як бачимо, весь стовпець заповнений даними, що характеризують питому вагу виручки від реалізації кожного товару. Але величина питомої ваги відображається в числовому форматі, а нам потрібно трансформувати його в процентний. Для цього виділяємо вміст стовпця " питома вага» . Потім переміщаємося у вкладку »Головна" . На стрічці в групі налаштувань " Число» є поле відображає формат даних. За замовчуванням, якщо ви не виробляли додаткових маніпуляцій, там повинен бути встановлений формат " загальний» . Клацаємо по піктограмі у вигляді трикутника, розташованої праворуч від цього поля. У списку форматів вибираємо позицію »відсоток" .

7. Як бачимо, всі значення стовпця були перетворені в процентні величини. Як і належить, в рядку " Всього» вказано 100% . Питома вага товарів очікувано розташовується в стовпці від більшого до меншого.

8. Тепер нам слід створити стовпець, в якому б відображалася накопичена частка з наростаючим підсумком. Тобто, в кожному рядку до індивідуальної питомої ваги конкретного товару буде додаватися питома вага всіх тих товарів, які розташовані в переліку вище. Для першого товару в списку ( Товар 3 ) індивідуальний питома вага і накопичена частка будуть рівними, а ось у всіх наступних до індивідуального показника потрібно буде додати накопичену частку попереднього елемента списку.

   Отже, в першому рядку переносимо в стовпець " накопичена частка» показник з колонки «питома вага» .


9. Далі встановлюємо курсор в другу клітинку стовпця " накопичена частка» . Тут нам доведеться застосувати формулу. Ставимо знак " дорівнює» і складаємо вміст комірки «питома вага» цієї ж рядки і вміст комірки " накопичена частка» з рядка вище. Всі посилання залишаємо відносними, тобто, не виробляємо з ними ніяких маніпуляцій. Після цього виконуємо клацання по кнопці Enter для виведення підсумкового результату.

10. Тепер потрібно скопіювати дану формулу в осередки даного стовпця, які розміщені нижче. Для цього застосовуємо маркер заповнення, до якого ми вже вдавалися при копіюванні формули в стовпці «питома вага» . При цьому, рядок " всього» захоплювати не потрібно, так як накопичений результат в 100% буде відображатися на останньому товарі зі списку. Як бачимо, всі елементи нашого стовпця після цього були заповнені.

11. Після цього створюємо стовпець «група» . Нам потрібно буде згрупувати товари за категоріями A , B і C відповідно до зазначеної накопиченої частки. Як ми пам'ятаємо, всі елементи розподіляються по групах за наступною схемою:
    

    Категорія	дія
    A	До 80%
    B	наступні 15%
    C	залишилися 5%

    Таким чином, всім товарам, накопичена частка питомої ваги яких входить в кордон до 80% , присвоюємо категорію A . Товарам з накопиченою питомою вагою від 80% до 95% присвоюємо категорію B . Залишилася групі товарів зі значенням більш 95% накопиченого питомої ваги присвоюємо категорію C .


12. Для наочності можна зробити заливку зазначених груп різними кольорами. Але це вже за бажанням.

Таким чином, ми розбили елементи на групи за рівнем важливості, використовуючи при цьому ABC-аналіз. При використанні деяких інших методик, про що говорилося вже вище, застосовують розбиття на більшу кількість груп, але сам принцип розбиття при цьому залишається практично незмінним.

Урок: сортування та фільтрація в Екселі

Спосіб 2: використання складної формули

Безумовно, застосування сортування є найпоширенішим способом проведення аналізу ABC в Екселі. Але в деяких випадках потрібно провести даний аналіз без перестановки рядків місцями у вихідній таблиці. У цьому випадку на допомогу прийде складна формула. Для прикладу будемо використовувати ту ж вихідну таблицю, що і в першому випадку.

1. Додаємо до вихідної таблиці, що містить найменування товарів і виручку від продажу кожного з них, колонку «група» . Як бачимо, в даному випадку ми можемо не додавати стовпці з розрахунком індивідуальних і накопичувальних часток.

2. Виробляємо виділення першої комірки в стовпці «група» , після чого виконуємо клацання по кнопці «Вставити функцію» , розташованої біля рядка формул.

3. Проводиться активація майстри функцій . Переміщаємося в категорію " посилання та масиви» . Вибираємо функцію »вибір" . Робимо клацання по кнопці «OK» .

4. Активується вікно аргументів функції вибір . Синтаксис її представлений наступним чином:

   =вибір (Номер_індекса; Значення1; Значення2;...)

   Завданням даної функції є висновок одного із зазначених значень, в залежності від номера індексу. Кількість значень може досягати 254, але нам знадобиться всього три найменування, які відповідають категоріям ABC-аналізу: A , B , з . Можемо відразу вводити в поле " Значення1» символ «A» , в полі " Значення2» &8212; «B» , в полі " Значення3» &8212; «C» .


5. А ось з аргументом " номер індексу» доведеться грунтовно повозитися, вмонтувавши в нього кілька додаткових операторів. Встановлюємо курсор в поле " номер індексу» . Далі тиснемо по піктограмі, що має вигляд трикутника, зліва від кнопки «Вставити функцію» . Відкривається список нещодавно використовуваних операторів. Нам потрібна функція ПОИСКПОЗ . Так як в списку її немає, то тиснемо по напису " інші функції...» .

6. Знову проводиться запуск вікна майстри функцій . Знову переходимо в категорію " посилання та масиви» . Знаходимо там позицію " ПОИСКПОЗ» , виділяємо її і робимо клацання по кнопці «OK» .

7. Відкривається вікно аргументів оператора ПОИСКПОЗ . Синтаксис його має наступний вигляд:

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

   Призначення даної функції – це визначення номера позиції зазначеного елемента. Тобто, якраз те, що нам потрібно для поля " номер індексу» функції вибір .

   У полі «перегляд масиву» відразу можна задати наступний вираз:

   {0:0,8:0,95}

   Воно повинно бути саме в фігурних дужках, як формула масиву. Не важко здогадатися, що ці числа ( 0 ; 0,8 ; 0,95 ) позначають межі накопиченої частки між групами.

   Поле " тип відповідності» не обов'язкове і в даному випадку ми його заповнювати не будемо.

   У полі " шукане значення» встановлюємо курсор. Далі знову через описану вище піктограму у вигляді трикутника переміщаємося в Майстер функцій .


8. Цього разу в Майстер функцій виробляємо переміщення в категорію »Математичні" . Вибираємо найменування «СУММЕСЛИ» і тиснемо на кнопку «OK» .

9. Запускається вікно аргументів функції SUMIF . Зазначений оператор підсумовує осередки, що відповідають певній умові. Його синтаксис такий:

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

   У полі " діапазон» вводимо адресу колонки «виручка» . Для цих цілей встановлюємо курсор в поле, а потім, зробивши затиск лівої кнопки миші, виділяємо всі осередки відповідного стовпця, виключаючи значення «всього» . Як бачимо, адреса тут же відобразився в полі. Крім того, нам потрібно зробити це посилання абсолютним. Для цього виробляємо її виділення і тиснемо на клавішу F4 . Адреса виділилася знаками долара.

   У полі «критерій» нам потрібно встановити умову. Вписуємо наступний вираз:

   ">"&

   Потім відразу ж після нього заносимо адресу першого осередку стовпця «виручка» . Робимо координати по горизонталі в даній адресі абсолютними, дописавши перед буквою знак долара з клавіатури. Координати по вертикалі залишаємо відносними, тобто, перед цифрою ніякого знака бути не повинно.

   Після цього не тиснемо на кнопку «OK» , а натискаємо по найменуванню функції ПОИСКПОЗ у рядку формул.


10. Потім ми повертаємося до вікна аргументів функції ПОИСКПОЗ . Як бачимо, в полі " шукане значення» з'явилися дані задані оператором SUMIF . Але це ще не все. Переходимо в це поле і вже до наявних даних додаємо знак «+» без лапок. Потім вносимо адресу першого осередку стовпця «виручка» . І знову робимо координати по горизонталі даного посилання абсолютними, а по вертикалі залишаємо відносними.

    Далі беремо весь вміст поля " шукане значення» в дужки, після чого ставимо знак ділення ( «/» ). Після цього знову через піктограму трикутника переходимо до вікна вибору функцій.


11. Як і минулого разу в запустився Майстер функцій шукаємо потрібний оператор в категорії »Математичні" . На цей раз шукана функція називається «сума» . Виділяємо її і тиснемо на кнопку «OK» .

12. Відкривається вікно аргументів оператора сума . Його головне призначення-це підсумовування даних в осередках. Синтаксис цього оператора досить простий:

    =sum(Число1; Число2;...)

    Для наших цілей знадобиться тільки поле " Число1» . Вводимо в нього координати діапазону стовпця «виручка» , виключаючи клітинку, яка містить підсумки. Подібну операцію ми вже проводили в полі " діапазон» функції SUMIF . Як і в той раз, координати діапазону робимо абсолютні, виділивши їх, і натиснувши на клавішу F4 .

    Після цього тиснемо по клавіші «OK» внизу вікна.


13. Як бачимо, комплекс введених функцій справив обчислення і видав результат в першу клітинку стовпця «група» . Першому товару була присвоєна група «A» . Повна формула, застосована нами для даного обчислення, виглядає наступним чином:

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

    Але, звичайно, в кожному конкретному випадку координати в даній формулі будуть відрізнятися. Тому її не можна вважати універсальною. Але, використовуючи те керівництво, яке було наведено вище, можна вставити координати будь-якої таблиці і з успіхом застосовувати даний спосіб в будь-якій ситуації.


14. Втім, це ще не все. Ми зробили розрахунок тільки для першого рядка таблиці. Для того, щоб повністю заповнити даними стовпець «група» , потрібно скопіювати цю формулу в діапазон нижче (виключаючи клітинку рядка «всього» ) за допомогою маркера заповнення, як ми вже робили не раз. Після того, як дані будуть внесені, ABC-аналіз можна вважати виконаним.

Як бачимо, результати, отримані за допомогою варіанту із застосуванням складної формули, нітрохи не відрізняються від тих результатів, які ми проводили шляхом сортування. Всім товарам присвоєні ті ж самі категорії, тільки при цьому рядки не змінили свого початкового положення.

Урок: Майстер функцій в Екселі

Програма Excel здатна значно полегшити проведення ABC-аналізу для користувача. Це досягається використанням такого інструменту, як сортування. Після цього проводиться підрахунок індивідуальної питомої ваги, накопиченої частки і, власне, розбиття на групи. У тих випадках, коли зміна початкового положення рядків в таблиці не допускається, можна застосувати метод з використанням складної формули.