Бысто делаем АВС-анализ в Excel

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

Что такое ABC-анализ в Excel

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

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

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

  • А— элементы, составляющие которых имеют 80 % всего удельного веса.
  • В — категория, удельный вес которой варьируется в пределах от 5-15%.
  • С — это оставшиеся элементы, составляющие менее 5% удельного веса.

Обратите внимание! В данной статье будут рассмотрены принципы проведения классического ABC-анализа. На практике элементы могут быть разбиты на 4 или 5 групп.

Способ 1: анализ при помощи сортировки

Этим методом в MS Excel проще всего выполнить ABC-анализ. Общий принцип проведения расчётов можно охарактеризовать следующим образом:

  1. Выделить исходный табличный массив ЛКМ и в разделе «Главная» нажать на кнопку «Сортировка и фильтр».
bystro-delaem-avs-analiz-v-excel
Путь к сортировке изначальной таблицы в Эксель
  1. В развернувшемся списке щёлкнуть по строке «Настраиваемая сортировка».
bystro-delaem-avs-analiz-v-excel
Выбор настраиваемой сортировки
  1. В отобразившемся меню сортировки в поле «Порядок» необходимо выставить значение «По убыванию», а также поставить галочку в строчку «Мои данные» содержат заголовки. После проведения этих действия потребуется кликнуть по ОК внизу окна.
bystro-delaem-avs-analiz-v-excel
Настройка основных параметров сортировки
  1. Проверить, что все значения в табличном массиве отсортировались от большего к меньшему.
bystro-delaem-avs-analiz-v-excel
Финальный результат отсортированного массива
  1. Теперь к первоначальной таблице надо добавить дополнительный столбец «Удельный вес», в котором и будут производиться дальнейшие расчёты по ABC-анализу. Для этого нужно нажать по названию последнего столбика ПКМ и вставить ещё один столбец, выбрав соответствующий вариант.
  2. Для расчёта удельного веса необходимо сумму выручки от продажи конкретного товара разделить на общую выручку от все продукции. В первой ячейке добавленного столбца прописываем формулу «=В2/$В$28» и нажимаем на «Enter» для подтверждения. Значения в выражении будут отличаться в зависимости от задачи. В данном алгоритме разобран конкретный пример, показанный на изображении ниже.
bystro-delaem-avs-analiz-v-excel
Формула для расчёта удельного веса в Excel
  1. Растянуть формулу на оставшиеся ячейки таблички, чтобы автоматически заполнить их.
bystro-delaem-avs-analiz-v-excel
Растягивание выражение на все строки табличного массива

Важно! Удельный вес должен отображаться в процентах. Поэтому общую формулу надо умножить ещё на 100%.

bystro-delaem-avs-analiz-v-excel
Применение процентного формата для удельного веса
  1. Теперь к таблице надо добавить столбец, в котором будет рассчитываться накопленная доля. Т.е. к каждому последующему значению в массиве будет прибавляться предыдущее число. Подробнее на скриншоте ниже.
bystro-delaem-avs-analiz-v-excel
Вычисление накопленной доли в Эксель
  1. Растянуть формулу для автозаполнения, когда в табличном массиве будут вычислены первые два параметра.
  2. После выполнения вышеуказанных манипуляций пользователю останется только сгруппировать полученные данные по группам A, B, С в соответствии с проводимым анализом. Для этого к таблице надо добавить ещё один столбец под названием «Группа».
  3. Все, что больше 80%, обозначается буквой А. Если получившееся значение находится в пределах от 80% до 95%, то его следует обозначить буквой В. Напротив остальных элементов ставится буква С.
bystro-delaem-avs-analiz-v-excel
Обозначение групп А, В, С для посчитанных значений
  1. При необходимости посчитанные группы можно выделить другим цветом для их выделения.

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

Выполнить ABC-анализ в Microsoft Office Excel можно без применения функции сортировки, т.е. пользователю не придётся менять расположение строк в исходном табличном массиве. Второй метод выполнения поставленной задачи заключается в использовании сложной формулы. В качестве примера будет приведена таблица из первого способа. Алгоритм действий подразделяется на следующие этапы:

  1. Создать исходную таблицу, для которой необходимо сделать расчёт.
  2. Добавить к созданному массиву по рассмотренной выше схеме дополнительный столбик с названием «Группа».
bystro-delaem-avs-analiz-v-excel
Добавление дополнительного столбца к исходному табличному массиву
  1. Левой клавишей манипулятора выделить первую ячейку в созданном столбце, а затем нажать на кнопку «Вставка функции», расположенной в программе слева от строки для ввода формул.
bystro-delaem-avs-analiz-v-excel
Действия по переходу в меню мастера функций
  1. В открывшемся окошке «Мастер функций» в поле «Категория» из выпадающего списка необходимо выбрать вариант «Ссылки и массивы».
  2. В разделе «Выберите функцию» нажать два раза по слову «Выбор», а затем кликнуть по ОК внизу окна.
bystro-delaem-avs-analiz-v-excel
Поиск функции «Выбор» в категории ссылок и массивов
  1. Теперь отобразится меню аргументов для указанной функции «Выбор». Здесь в строках «Значение1», «Значение2» и «Значение3» необходимо прописать вручную элементы для проводимого анализа, т.е. ввести символы A, B, C в каждое из поле соответственно.
bystro-delaem-avs-analiz-v-excel
Заполнение некоторых аргументов оператора «Выбор»
  1. Теперь переходим к заполнению самой первой строки «Номер индекса» в окошке аргументов функции. Сначала нужно будет щёлкнуть по значку пиктограммы справа от указанной строчки. Затем сверху главного меню Excel развернуть функцию «Выбор», нажав ЛКМ по стрелочке справа. В контекстном меню понадобится выбрать вариант «Другие функции…».
bystro-delaem-avs-analiz-v-excel
Первоначальные действия по заполнению аргумента «Номер индекса» функции «Выбор»
  1. После выполнения предыдущих действий ещё раз запустится окошко «Мастер функций». На этот раз в перечне значений надо выбрать функцию «ПОИСКПОЗ», щёлкнуть по ней два раза левой кнопкой мышки, чтобы выделить, а затем кликнуть по ОК внизу меню для подтверждения действия.
bystro-delaem-avs-analiz-v-excel
Выбор оператора «ПОИСКПОЗ»
  1. Откроется меню аргументов для функции «ПОИСКПОЗ». Это и есть аргумент «Номер индекса» в операторе «Выбор». Здесь в строчке «Просматриваемый массив» надо прописать выражение «{0:0,8:0,95}», а около поля «Искомое значение» нажать по пиктограмме и ещё раз аналогичным образом зайти в меню мастера функций.
bystro-delaem-avs-analiz-v-excel
Заполнение аргументов для «ПОИСКПОЗ»
  1. На этот раз в поле «Категория» потребуется выставить значение «Математические», выделить оператор «СУММЕСЛИ» и щёлкнуть по ОК внизу окошка.
bystro-delaem-avs-analiz-v-excel
Выбор оператора «СУММЕСЛИ»
  1. Далее окно аргументов откроется уже для функции «СУММЕСЛИ». Здесь пользователю надо поставить курсор мышки в поле «Диапазон», зажать ЛКМ и выделить все значения в графе «Выручка» исходного табличного массива.
  2. После того как диапазон ячеек будет указан, останется нажать с клавиатуры кнопку «F4», чтобы задать формуле абсолютность, навесив на нее знаки долларов.
  3. Во второй строчке «Критерий» надо записать сочетание «»>»&», а затем также выделить диапазон ячеек в столбце «Выручка» у исходной таблички.
bystro-delaem-avs-analiz-v-excel
Заполнение аргументов для «СУММЕСЛИ»
  1. Теперь в строке формул сверху окна надо ЛКМ кликнуть по оператору «ПОИСКПОЗ», чтобы вернуться к аргументам данной функции.
  2. К прописанной информации в поле Искомое значение необходимо дописать вручную знак «+» с клавиатуры и навесить на выражение абсолютность. Причем заключать символ в кавычки не нужно.
  3. После выполнения предыдущих шагов всю формулу в строке «Искомое значение» необходимо заключить в скобки, после них поставить значок деления с клавиатуры (/) и ещё раз кликнуть по пиктограмме справа от поля аргумента.
bystro-delaem-avs-analiz-v-excel
Повторное заполнение оставшихся аргументов функции «ПОИСКПОЗ»
  1. В окне выбора функций в категории «Математические» найти оператор «СУММ», выделить его нажатием ЛКМ, а затем щёлкнуть по ОК.
bystro-delaem-avs-analiz-v-excel
Поиск функции «СУММ»
  1. Теперь заполняем аргументы для вновь выбранного оператора «СУММ». Рядом со строкой «Число1» надо нажать по пиктограмме и в исходной таблице выделить столбец «Выручка», указав его координаты. Также нужно навесить на выражение знаки долларов нажатием на клавишу «F4» с клавиатуры ПК, после чего кликнуть по ОК.
bystro-delaem-avs-analiz-v-excel
Заполнение аргументов оператора «СУММ»
  1. Проверить результат. Если вышеуказанные манипуляции проделаны правильно, то в первой ячейке добавленного столбца появится символ «А», характеризующий анализ.
bystro-delaem-avs-analiz-v-excel
Финальный результат проведения ABC-анализа методом сложной формулы

Дополнительная информация! Для заполнения оставшихся столбиков полученную формулу понадобится растянуть на весь табличный массив.

Способ 3: с помощью надстройки ABC Analysis Tool

Данная надстройка будет работать только в Microsoft Office Excel, начиная с версии 2013 года. При этом инструмент надо дополнительно скачивать с официального сайта разработчиков и устанавливать на свой ПК, вводя ключ продукта. В общем виде процесс проведения ABC-анализа в этом инструменте заключается в следующем:

  1. Запустить программу с компьютера.
  2. В поле «Наименование» ввести адреса ячеек таблицы, по которым проводится анализ.
  3. Выбрать автоматическое выполнение анализа для быстроты процесса.
  4. Проверить результат. В главном меню ПО отобразится график, поделённый на подгруппы A, B, C.
bystro-delaem-avs-analiz-v-excel
Результат работы надстройки ABC Analysis Tool

ABC-анализ — примеры использования

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

В закупках

В данной области анализируется определённое количество товаров, после чего посредством ABC-методики определяется требуемый объем закупок каждого вида продукции.

В продажах

Здесь ABC-анализ позволяет выделить наиболее продаваемые товары, а также понять, какая продукция не пользуется спросом. Ее можно будет исключить из общего ассортимента товаров в дальнейшем.

В цифровом маркетинге

Происходит ранжирование, распределение типов трафика по их вкладам в продажу. При этом при проведении ABC-анализа большая часть маркетингового бюджета переходит к группе А.

В SEO

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

Обратите внимание! ABC-анализ целесообразнее выполнять в сфере закупок и продаж.

Преимущества и недостатки ABC-анализа

У данного типа исследований есть несколько положительных сторон:

  • Простота реализации. АВС-анализ можно использовать практически на любом производственном процессе для повышения эффективности производства. Методика не требует внедрение больших финансовых затрат.
  • Результативность и высокая эффективность работы. ABC-анализ даёт точные результаты по текущему положению дел, позволяет руководителям проекта быстро принять решение по повышению его эффективности, производительности работы.
  • Минимальные затраты на выполнение, оптимизация времени.

Без недостатков не обошлось. Их у этой методики несколько:

  • Ориентирован на работу с простыми элементами, не способен группировать сложные объекты.
  • Сравнение элементов осуществляется только по их количественным показателям.
  • Серьёзное влияние на эффективность внешних экономических факторов.

Заключение

Таким образом, ABC-анализ представляет собой важнейшую характеристику любого производственного процесса, показывает важность внедрения того или иного элемента. Основные методы его выполнения в Microsoft Office Excel были рассмотрены выше. С представленной информацией рекомендуется внимательно ознакомиться, чтобы хорошо разбираться в данной теме.

ОфисГуру
Adblock
detector