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

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

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

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

• Категорія A - елементи, що мають в сукупності більше 80% питомої ваги;
• Категорія B - елементи, сукупність яких становить від 5% до 15% питомої ваги;
• Категорія C - залишилися елементи, загальна сукупність яких становить 5% і менше питомої ваги.

Окремі компанії застосовують більш просунуті методики і розбивають елементи не на 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%;
    • З - решта 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» - «B», в поле «значеніє3» - «C».

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

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

   Призначення даної функції - це визначення номера позиції зазначеного елемента. Тобто, якраз те, що нам потрібно для поля «Номер індексу» функції ВИБІР.

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

   {0:0,8:0,95}

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

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

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

8. На цей раз в Майстрі функцій виробляємо переміщення в категорію «Математичні». Вибираємо найменування «СУММЕСЛИ» і тиснемо на кнопку «OK».
9. Запускається вікно аргументів функції СУММЕСЛИ. Зазначений оператор підсумовує осередку, що відповідають певній умові. Його синтаксис такий:

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

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

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

   ">"&

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

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

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

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

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

    =СУММ(Число1;Число2;…)

    Для наших цілей знадобиться тільки поле «Число1». Вводимо в нього координати діапазону стовпця "Дохід", виключаючи осередок, яка містить підсумки. Подібну операцію ми вже проводили в полі «Діапазон» функції СУММЕСЛИ. Як і того разу, координати діапазону робимо абсолютні, виділивши їх, і натиснувши на клавішу 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-аналізу для користувача. Це досягається використанням такого інструменту, як сортування. Після цього проводиться підрахунок індивідуального питомої ваги, накопиченої частки і, власне, розбивка на групи. У тих випадках, коли зміна початкового положення рядків в таблиці не допускається, можна застосувати метод з використанням складної формули.

• ← Застосування функції ВИБІР в Microsoft Excel
• Установка дня тижня по даті в Microsoft Excel →