ABCанализ в Excel

ABC-анализ в Excel

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

Использование ABC-анализа

ABC-анализ — это своего рода усовершенствованная и адаптированная к современным условиям версия принципа Парето. В соответствии с их методологией поведения все элементы анализа делятся на три категории по важности:

  • Категория А — элементы общим весом более 80% от удельного веса;
  • Категория Б — элементы, совокупность которых составляет от 5% до 15% от удельного веса;
  • Категория C — прочие элементы, сумма которых составляет 5% и менее удельного веса.

Некоторые компании применяют более сложные техники и разбивают элементы не на 3, а на 4 или 5 групп, но мы будем полагаться на классическую ABC-схему для анализа.

Метод 1: анализ сортировкой

В Excel анализ ABC выполняется путем сортировки. Все предметы отсортированы от большего к меньшему. Затем рассчитывается совокупный удельный вес каждого элемента, на основании которого ему присваивается определенная категория. Давайте посмотрим на конкретном примере того, как эта техника применяется на практике.

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

ABC-анализ в Excel

  1. Выберите таблицу с указанным курсором, удерживая левую кнопку мыши, за исключением заголовка и последней строки. Откройте вкладку "Данные" . Нажмите на кнопку "Сортировка"расположен в поле "Сортировка и фильтрация" на ленте.

    ABC-анализ в Excel

    Вы также можете действовать по-другому. Выберите верхний диапазон таблицы, затем перейдите на вкладку "начало" и нажмите кнопку "Сортировка и фильтрация"расположен в поле "Редактирование" на ленте. Активируется список, в котором выбираем пункт "Пользовательская сортировка" в нем .

  2. ABC-анализ в Excel

  3. Когда вы применяете любое из вышеперечисленных действий, запускается окно сортировки. Посмотрим этот вариант "Мои данные содержат заголовки" проверено. В случае отсутствия устанавливаем.

    В поле "Столбец" укажите имя столбца, содержащего данные о доходах.

    В поле "Сортировка" необходимо указать конкретные критерии сортировки. Оставляем предустановленные настройки — "Ценности" .

    В поле "порядок" мы устанавливаем позицию "По убыванию" .

    После выполнения указанных настроек нажмите на кнопку "в порядке" внизу окна.

  4. ABC-анализ в Excel

  5. После выполнения этого действия все товары были отсортированы по выручке от наибольшего к наименьшему.
  6. ABC-анализ в Excel

  7. Теперь нам нужно рассчитать удельный вес каждого из элементов на общую сумму. Для этих целей создадим дополнительный столбец, который назовем "конкретный вес" . В первой ячейке этого столбца ставим знак "знак равно" , то указываем ссылку на ячейку, в которой указана сумма выручки от продажи товара. Затем установите дефис ( "/" ). Затем задайте координаты ячейки, в которой содержится сумма продаж товаров на предприятии.

    Учитывая тот факт, что мы скопируем эту формулу в другие ячейки столбца "Конкретный вес" Используя токен заполнения, нам нужно установить адрес ссылки на элемент, содержащий общую выручку предприятия. Вот почему мы делаем ссылку абсолютной. Выберите координаты указанной ячейки в формуле и нажмите F4. Перед координатами, как видим, появился знак доллара, означающий, что связь стала абсолютной. Следует отметить, что ссылка на выручку от первой позиции в списке (Товар 3) должна оставаться относительной.

    Затем, чтобы произвести расчеты, нажмите клавишу Enter.

  8. ABC-анализ в Excel

  9. Как видите, доля дохода от первого продукта, включенного в список, отображается в целевой ячейке. Чтобы скопировать формулу из диапазона ниже, поместите курсор в правый нижний угол ячейки. Он превращается в маркер заливки, который выглядит как маленький крестик. Щелкните левой кнопкой мыши и перетащите маркер заполнения до конца столбца.
  10. ABC-анализ в Excel

  11. Как видите, весь столбец заполнен данными, характеризующими удельный вес выручки от продаж для каждого продукта. Но значение удельного веса отображается в цифровом формате, и мы должны преобразовать его в проценты. Для этого выделите содержимое столбца "Конкретный вес" . Затем перейдите на вкладку «Главная». На панели инструментов в группе настроек "Число" есть поле, которое показывает формат данных. По умолчанию, если вы не производили дополнительных манипуляций, необходимо установить формат "Общее" . Щелкните значок в виде треугольника справа от этого поля. В открывшемся списке форматов выберите пункт "Интерес" .
  12. ABC-анализ в Excel

  13. Как видите, все значения столбцов были преобразованы в проценты. Как и ожидалось, порядок "Всего" показывает 100%. Ожидается, что удельный вес товаров будет в графе от наибольшего до наименьшего.
  14. ABC-анализ в Excel

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

    Итак, в первой строке перенесите в столбец "Накопленная доля" индикатор из колонки "Конкретный вес" .

  16. ABC-анализ в Excel

  17. Затем поместите курсор во вторую ячейку столбца "Накопленная доля" . Здесь мы должны применить формулу. Ставим знак "равный" и добавляем содержимое ячейки "конкретный вес" в той же строке и содержимое ячейки "Накопленная доля" из верхнего ряда. Все отчеты остаются относительными, т.е. мы ими не манипулируем. Затем нажмите кнопку Enter, чтобы увидеть окончательный результат.
  18. ABC-анализ в Excel

  19. Теперь вам нужно скопировать эту формулу в ячейки этого столбца, которые расположены ниже. Для этого используем маркер заливки, к которому мы уже прибегали при копировании формулы в столбец "Конкретный вес" . В этом случае порядок "Всего" это не обязательно для захвата, потому что накопленный результат в 100% будет показан на последнем продукте из списка. Как видите, после этого все элементы в нашей колонке были заполнены.
  20. ABC-анализ в Excel

  21. Затем создайте столбец "Группировка" . Придется сгруппировать товары по категориям A, B и C в соответствии с накопленной долей. Напомним, все элементы сгруппированы по следующей схеме:
    • А — до 80%;
    • Б — следующие 15%;
    • C — оставшиеся 5%.
    • Таким образом, все товары, накопленная доля удельного веса которых находится в пределах 80%, определяется как категория A. Товары с накопленным удельным весом от 80% до 95% получают категорию B. Остальная группа товаров стоимостью более 95% от накопленного удельный вес определяется для категории В.

      ABC-анализ в Excel

    • Для большей наглядности вы можете заполнить эти группы разными цветами. Но это необязательно.

    Метод 2: использование сложной формулы

    Конечно, использование сортировки является наиболее распространенным способом выполнения анализа ABC в Excel. Но в некоторых случаях этот анализ требуется без изменения строк мест в исходной таблице. В этом случае на помощь придет сложная формула. Например, мы будем использовать ту же исходную таблицу, что и в первом случае.

    1. Добавляем в исходную таблицу, содержащую наименование товаров и выручку от продаж каждого из них, в столбец "Группа" . Как видите, в этом случае мы не можем добавлять столбцы с расчетом индивидуальных и накопительных долей.
    2. ABC-анализ в Excel

    3. Выберите первую ячейку в столбце "Группа" , затем нажмите кнопку "Вставить функцию" в строку формул.
    4. ABC-анализ в Excel

    5. Мастер функций активирован. Переходим в категорию "Ссылки и массивы" . Выберите функцию "ВЫБРАТЬ" . Нажмите на кнопку "в порядке" .
    6. ABC-анализ в Excel

    7. Окно аргумента SELECT активируется. Синтаксис следующий:

      = ВЫБОР (Номер_индекса; Значение1; Значение2;…)

      Задача этой функции — вывести одно из заданных значений в зависимости от индекса. Количество значений может достигать 254, но нам нужно всего три названия, которые соответствуют категориям ABC-анализа: A, B, C. Мы можем сразу ввести в поле "Значение1" символы "А" в поле "Значение2" — "В" в поле "Значение3" — "C" .

    8. ABC-анализ в Excel

    9. Но с аргументом "Индекс" вам нужно будет тщательно поработать, встроив в него несколько дополнительных операторов. Поместите курсор в поле "Индекс" . Затем щелкните значок в виде треугольника слева от кнопки. "Вставить функцию" . Появится список недавно использованных операторов. Нам нужна функция ПОИСКПОЗ. Поскольку списка нет, нажимаем на подпись "Другие преимущества …" .
    10. ABC-анализ в Excel

    11. Окно помощника по функциям запускается снова. Снова перейти в категорию "Ссылки и массивы". Мы находим там позицию "ПОИСК" , выберите его и нажмите кнопку "в порядке" .
    12. ABC-анализ в Excel

    13. Открывает окно аргументов ОПЕРАТОРА. Его синтаксис следующий:

      = ПОИСК (Search_value; Browse_array; Mapping_Type)

      Цель этой функции — определить номер позиции указанного элемента. Это именно то, что нам нужно для поля "Индекс" функции ВЫБОР.

      В поле "Сканируемый массив" можно сразу задать следующее выражение:

      {0: 0,8: 0,95}

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

      Тип "Соответствующий тип" является необязательным и в этом случае мы не будем его заполнять.

      В поле "Значение поиска" настраиваем курсор. Затем снова через треугольник, показанный выше, мы переходим к функциональному помощнику.

    14. ABC-анализ в Excel

    15. На этот раз в мастере функций переходим в категорию "Математически" . Выбираем имя "ЛЕТО" и нажмите на кнопку "в порядке" .
    16. ABC-анализ в Excel

    17. Откроется окно аргументов SUMIFER. Указанный оператор суммирует ячейки, соответствующие указанному условию. Его синтаксис следующий:

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

      В поле "Сфера" введите адрес столбца "Доход" . Для этого поместите курсор в поле и после нажатия левой кнопки мыши выделите все ячейки в соответствующем столбце, кроме значения "Всего" . Как видите, адрес отображается сразу в поле. Мы также должны сделать эту связь абсолютной. Для этого сделайте свой выбор и нажмите клавишу F4. Адрес был отмечен знаками доллара.

      В поле "Критерии" мы должны указать одно условие. Вводим следующее выражение:

      "gt;"&

      Затем сразу же введите адрес первой ячейки в столбце "Доход" . Мы делаем горизонтальные координаты в этом адресе абсолютно, добавляя знак доллара на клавиатуре перед буквой. Вертикальные координаты остались относительными, т.е. перед цифрой нет знака.

      Тогда не нажимайте на кнопку "в порядке" , но щелкните имя функции ПОИСКПОЗ в строке формул.

    18. ABC-анализ в Excel

    19. Затем возвращаемся в окно с аргументами функции ПОИСКПОЗ. Как видите, в коробке "Значение поиска" появились данные, введенные оператором SUMESELI. Но это не все. Перейдите в это поле и добавьте символ "+" без кавычек к существующим данным. Затем введите адрес первой ячейки в столбце "Доход" . И снова мы делаем горизонтальные координаты этой ссылки абсолютными, а относительные координаты оставляем вертикальными.

      Затем берем все содержимое поля "Желаемое значение" в скобках, затем поставьте знак препинания ( "/" ). Затем снова через значок треугольника перейти в окно выбора функции.

    20. ABC-анализ в Excel

    21. Так же недавно в запущенном функциональном помощнике ищем нужного оператора в категории "Математически" . На этот раз вызывается желаемая функция "СУММ" . Выберите его и нажмите кнопку "в порядке" .
    22. ABC-анализ в Excel

    23. Открывает окно аргументов оператора SUM. Его основная цель — суммировать данные в ячейках. Синтаксис этого оператора довольно прост:

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

      Для наших целей необходимо только поле "Номер 1" . Введите координаты диапазона столбцов "Доход" , за исключением ячейки, содержащей итоги. Мы уже проводили такую ​​операцию в полевых условиях. "Диапазон" функции ЛЕТО. На тот момент координаты диапазона являются абсолютными, выделив их и нажав клавишу F4.

      Затем нажмите кнопку "в порядке" внизу окна.

    24. ABC-анализ в Excel

    25. Как видите, набор функций ввода выполнил расчет и отобразил результат в первой ячейке столбца. "Группа" . Первый товар был отнесен к группе "А" . Полная формула, которую мы применили для этого расчета, выглядит следующим образом:

      = ВЫБРАТЬ (ПОИСК ((СУММ ($ B $ 2: $ B $ 27;"gt;"&$ B2) + $ B2) / СУММ ($ B $ 2: $ B $ 27); {0: 0,8: 0,95});"А";"B";"C")

      Но, конечно, в каждом случае координаты в этой формуле будут разными. Поэтому его нельзя считать универсальным. Но с помощью приведенного выше руководства вы можете вставить координаты каждой таблицы и успешно применить этот метод в любой ситуации.

    26. ABC-анализ в Excel

    27. Но это не все. Мы рассчитывали только для первой строки таблицы. Чтобы заполнить этот столбец данными, необходимо скопировать эту формулу в приведенный ниже диапазон (кроме ячейки в строке "Всего" ), используя маркер заливки, как мы это делали не раз. После ввода данных ABC-анализ можно считать завершенным.

    ABC-анализ в Excel

    Как видите, результаты, полученные при использовании варианта с использованием сложной формулы, не отличаются от результатов, полученных нами при сортировке. Всем товарам присваиваются одинаковые категории, но строки не меняют свое исходное положение.

    ABC-анализ в Excel

    Урок: Мастер функций Excel

    Excel может значительно облегчить пользователю анализ ABC. Это достигается с помощью такого инструмента, как сортировка. Затем рассчитывается индивидуальный вес, накопленная доля и фактически группировка. В случаях, когда изменение начального положения строк в таблице не допускается, вы можете применить метод, используя сложную формулу.

    Понравилась статья? Поделиться с друзьями:
    Что нужно знать пользователю?