ABC анализ в Excel

Всем привет! АВС анализ в Эксель – один из самых основных способов классификации различных исходных данных. Ими могут быть средства, ресурсы, товары, и другие. Каждому из значений присваивается степень важности, согласно которой и будет проводиться АБС анализ – каждое значение получает оценку степени важности: А, В или С.


Примеры формул расчета АБС анализа

Excel с легкостью справится с поставленной задачей, даже несмотря на большое количество данных. Рассмотрим же на практике как провести АВС анализ в программном обеспечении Microsoft Excel
.
Проводимый анализ является улучшенным вариантом анализа Парето, согласно которому исходные данные можно разложить на несколько групп:

• Группа «А» - значения, которые представляют собой 80% от общей составляющей;

• Группа «В» - значения, которые представляют собой от 15% до 5% от общей составляющей;

• Группа «С» - значения, которые представляют собой 5% и меньше от общей составляющей.

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


Метод 1 - АВС анализ с использованием функции сортировки

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

Попробуем провести АВС анализ на практике. Внесем исходные данные по каждому товару – наименование товара и его стоимость. Нашей целью является провести АВС анализ и понять важность и себестоимость товаров для дальнейшей продажи.



Первым шагом будет сортировка товаров. Для этого выделим всю таблицу с исходными данными и перейдем во вкладку «Данные» - «Сортировка».




Эти же действия можно выполнить и другим способом – достаточно перейти во вкладку «Главная» - «Сортировка и фильтр», после чего выбрать настраиваемый тип сортировки.



В появившемся диалоговом окне нам нужно выставить несколько значений.

• Включить параметр «Мои данные содержат заголовки».

• Указать диапазон значений исходных данных в поле «Столбец».

• Выставить параметр «Сортировка» - «Значения».

• Последним шагом необходимо установить тип порядка сортировки – в нашем случае «По убыванию».

• Применяем операцию сортировки.



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



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

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

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





Так как удельный вес это процентная составляющая, то выставим представление данных в ячейки в процентах. Для этого выделяем весь столбец с удельным весом, перемещаемся во вкладку «Главная» - «Формат данных» - «Процентный».


Теперь наш столбец правильно представлен. Приступим к следующему шагу.


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

Пропишем формулу подсчета и заполним весь новый столбец «Накопленная доля» посредством маркера авто заполнения.


Последним шагом станет группирование удельного веса согласно правилам АВС анализа, которые описаны в теоретической части статьи.

Что получаем в итоге – проведенный АВС анализ, согласно которому все товары, накопленная доля удельного веса вошла в границу до 80% получила группу А, доля, составляющая от 80% до 95% получила группу В, и последние вошли в группу С.


Если хотите, можно залить группы разными цветами.


Теперь наглядно видно, какие группы товаров представляют большей уровень важности, а какие меньший. АВС анализ проведен успешно.


Метод 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-анализ можно считать выполненным.


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


Читайте также: Мастер функций в программе Microsoft Excel


Надеемся, вам стало ясно как работать с ABC анализом в Excel. Всем мир!

Рейтинг:(голосов:1)