Проведение АВС-анализа
материал для подготовки к егэ (гиа)

Проведение АВС-анализа ассортимента товаров с использованием программы Microsoft Excel

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

Ключевые слова: АВС-анализ, ассортимент товаров, Microsoft Excel, принцип Парето, оптимизация ассортимента, управление запасами, классификация товаров.

Введение

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

АВС-анализ — метод, позволяющий классифицировать ресурсы предприятия по степени их важности . В основе метода лежит принцип Парето, согласно которому 20% всех товаров дают 80% оборота . Этот метод широко используется при планировании и формировании ассортимента на различных уровнях производственных систем, систем снабжения и сбыта .

Табличный редактор Microsoft Excel является наиболее доступным и удобным инструментом для проведения АВС-анализа, поскольку содержит необходимый функционал для обработки данных: формулы (ЕСЛИ, ВПР/ГПР, РАНГ), сортировку табличных данных, построение диаграмм и сводных таблиц .

Теоретические основы АВС-анализа

Сущность метода

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

Классификация групп

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

Однако данное процентное соотношение является ориентировочным. Различные варианты процентных соотношений категорий A, B, C могут быть: 80-15-5, 70-20-10, 50-30-20 .

Области применения

Анализировать таким методом можно не только ассортимент продукции, но и другие категории ресурсов компании: поставщиков, складские запасы, покупателей, длительные периоды продаж — всё, что имеет достаточное количество статистических данных .

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

Пошаговая методика проведения АВС-анализа в Excel

Шаг 1. Подготовка исходных данных

Первый этап заключается в формировании отчета о реализации продукции в Excel. В учетной базе данных составляется отчет по реализации ассортиментной линейки продукции в аналитике выбранных параметров .

При подготовке данных необходимо:

• Удалить дубли и новинки, по которым еще не накопилась статистика .
• Убрать пробелы и другие лишние символы из числовых значений .
• Расположить позиции от наибольшего значения к наименьшему .

Для анализа могут быть выбраны различные критерии:

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

Шаг 2. Расчет показателей структуры ассортимента

На втором этапе собираются данные о структуре выбранных критериев ассортимента продукции, так как АВС-анализ проводится именно на основе удельных значений ассортиментной единицы в общей массе показателя .

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

text

Доля = Значение показателя ассортиментной единицы / Общая сумма показателей × 100%

В Excel формула для первой строки может выглядеть следующим образом:

text

=B2/СУММ($B$2:$B$N)

где $B$2:N — фиксированный диапазон суммирования .

Шаг 3. Расчет накопленного итога

Накопленный итог показывает, какой процент от общей выручки дают все позиции от первой строки до текущей. По этому столбцу видно, на какой строчке сумма переваливает за 80% и 95% — там проходят границы групп .

Для расчета накопленного итога используется формула:

text

=СУММ($C$2:C3)

где C2 — первая ячейка со значением доли .

Шаг 4. Присвоение групп А, В, С

Границы групп, как правило, соответствуют пропорциям: группа А — позиции, которые в сумме дают до 80% результата; группа В — от 80% до 95%; группа С — все остальное .

Для автоматического присвоения групп используется функция ЕСЛИ :

text

=ЕСЛИ(D2<=0,8;"A";ЕСЛИ(D2<=0,95;"B";"C"))

Каждая компания может устанавливать свои границы ассортиментных групп .

Многокритериальный АВС-анализ

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

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

Вариант 1: Раздельный анализ по каждому фактору

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

Однако этот вариант не дает ответа на вопрос, какой ассортимент выводить или ротировать, когда возникает выбор между двумя товарами с рейтингом BC или CB .

Вариант 2: Интегральный показатель с нормированием

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

text

Нормированное значение = (Значение показателя – Минимум группы) / (Максимум группы – Минимум группы)

Для этого из значения продаж конкретного товара вычитается минимум продаж группы, и полученное значение делится на разницу между максимумом и минимумом продаж группы .

После нормирования показатели суммируются (или взвешиваются в соответствии с важностью критерия), и по полученному интегральному показателю проводится стандартный АВС-анализ. При использовании нескольких категорий количество аналитических групп ранжирования увеличивается геометрически: при двух категориях — 9 групп, при трех — 27 групп .

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

Совмещение с другими методами управления запасами

АВС-анализ часто комбинируют с другими методами управления запасами. Например, в проектах по оптимизации запасов применяются следующие методы :

1. ABC Classification — основана на годовом обороте (спрос × стоимость).
2. Safety Stock (SS) — страховой запас, рассчитываемый по формуле SS = Z × σ × √LT, где Z — коэффициент уровня обслуживания (например, 1,65 для 95% уровня обслуживания), σ — стандартное отклонение спроса, LT — время выполнения заказа .
3. Reorder Point (ROP) — точка перезаказа, рассчитываемая по формуле ROP = (Средний дневной спрос × LT) + SS .
4. Economic Order Quantity (EOQ) — экономичный размер заказа, рассчитываемый по формуле EOQ = √((2 × Demand × Ordering Cost) ÷ (Unit Cost × Holding Rate)) .

Построение кривой Парето

Для визуализации результатов АВС-анализа строится кривая Парето. В Excel для этого необходимо :

1. Рассчитать кумулятивный процент по каждому товару (накопленный итог).
2. Построить точечную диаграмму (XY chart), где на оси X откладывается кумулятивный процент товаров, а на оси Y — кумулятивный процент стоимости .
3. Нанести границы групп А, В и С на диаграмму .

Метод касательной для определения границ групп

Для определения границ групп А, В и С может использоваться графический метод касательной к кривой ABC :

1. Соединяется начало системы координат и конец графика прямой.
2. Проводится касательная к кривой ABC, параллельная этой прямой. Абсцисса точки касания показывает границу между группами А и В, а ордината — долю реализации продуктов группы А в общей реализации.
3. Соединяется точка касания с концом кривой, и проводится новая касательная, параллельная этой линии. Абсцисса точки касания указывает границу между группами В и С .

Практические примеры

Пример 1. Простой АВС-анализ по одному критерию

Для проведения простого АВС-анализа достаточно следующих шагов :

1. Данные сортируются по убыванию суммы (выручки от реализации) с помощью фильтра в Excel .
2. Рассчитывается доля каждого параметра в общей сумме.
3. Рассчитывается накопленный итог.
4. Присваиваются группы с помощью функции ЕСЛИ .

Пример 2. Многокритериальный анализ с использованием функции ВПР

Для проведения многокритериального АВС-анализа используется следующий алгоритм :

1. Таблица с отчетом копируется на три новых листа.
2. На каждом листе проводится АВС-анализ по одному критерию.
3. Создается сводная таблица, куда с помощью функции ВПР подставляются значения групп :

text

=ВПР(Диапазон ассортиментных единиц; Наименование листа с данными и диапазон; Номер столбца; ЛОЖЬ)

4. Проводится комплексное ранжирование с использованием функционала сортировки данных (Данные/Сортировка), где последовательно указываются наиболее важные для компании критерии .

Выводы и практические рекомендации

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

1. Выявить приоритетные позиции ассортимента (группа А), требующие повышенного внимания, постоянного наличия на складе и активного продвижения .
2. Определить позиции средней значимости (группа В), требующие точечного стимулирования продаж .
3. Выявить малозначимые позиции (группа С), затраты на которые могут быть сокращены или которые могут быть выведены из ассортимента без ущерба для бизнеса .
4. Оптимизировать товарные запасы, снижая объемы закупок по позициям группы С и увеличивая — по позициям группы А .
5. Принимать обоснованные ценовые и маркетинговые решения, в том числе перераспределять рекламный бюджет в пользу наиболее эффективных категорий .

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

Список литературы

1. Чеглаков П. М. ABC-анализ продаж товаров в Excel // Планово-экономический отдел. — 2018. — № 11. 
2. Цветаева О. ABC-анализ ассортимента продукции предприятия с помощью Excel // Справочник экономиста. — 2019. — № 8. 
3. Гребенников А. А. ABC-анализ ассортимента продукции предприятия с помощью Excel // Справочник экономиста. — 2021. — № 1. 
4. ABC-анализ ассортимента продукции // Планово-экономический отдел. — 2025. — № 8. 
5. Науменко М. Как сделать ABC-анализ в Excel и зачем он нужен // Журнал Битрикс24. — 2026. — 1 июня. 
6. Scaler Topics. ABC Inventory Analysis using Excel (Example). — 2023. 
7. TaranArora. Grocery Inventory Analysis & Dashboard. — GitHub. — 2025.