Excel9 мин·
ABC-анализ в Excel — пошаговая инструкция с формулами
Как провести ABC-анализ товаров, клиентов, запасов в Excel. Принцип Парето 80/20, формулы, сортировка, присвоение групп.
Что такое ABC-анализ
ABC-анализ делит товары (клиентов, запасы) на три группы по вкладу в общий результат:
- Группа A (20% позиций → 80% дохода) — ключевые, максимум внимания
- Группа B (30% позиций → 15% дохода) — средние, стандартное управление
- Группа C (50% позиций → 5% дохода) — хвост, минимум ресурсов
Основа — принцип Парето: 20% усилий дают 80% результата.
Шаг 1: Подготовка данных
Нужны два столбца:
- A: Наименование (товар, клиент, SKU)
- B: Показатель (выручка, количество продаж, прибыль)
Отсортируйте по показателю по убыванию (самые «большие» сверху).
Шаг 2: Доля каждой позиции
В столбце C — доля каждого товара в общей сумме:
=B2/СУММ($B$2:$B$100)
💡 Совет: Знаки $ фиксируют диапазон суммы. Протяните формулу вниз.
Шаг 3: Накопительный итог
В столбце D — кумулятивная доля (нарастающим итогом):
Первая строка:
=C2
Вторая строка и далее
Каждая следующая строка прибавляет свою долю к предыдущей:
=D2+C3
💡 Совет: Последняя строка должна быть 100% (или очень близко). Если нет — проверьте сортировку.
Шаг 4: Присвоение группы
В столбце E — группа на основе накопительного итога:
=ЕСЛИ(D2<=0.8;"A";ЕСЛИ(D2<=0.95;"B";"C"))
💡 Совет: Границы 80% / 95% — классические. Можно адаптировать под бизнес: 70/90, 80/95, другие.
Шаг 5: Визуализация
Постройте диаграмму Парето:
1. По оси X — товары (в порядке убывания)
2. Левая ось Y — столбцы с абсолютными значениями
3. Правая ось Y — линия накопительного итога (%)
Линия показывает, как быстро набираются 80%. Чем круче подъём в начале — тем выраженнее эффект Парето.
Заключение
ABC-анализ — 5 шагов: отсортировать → доля → нарастающий итог → группа → диаграмма. Результат — понимание, на чём зарабатываете, и куда направить ресурсы. Формулы простые, а эффект для бизнеса — огромный. Fast Formula поможет автоматизировать расчёты для вашей таблицы.
🤖
Не хотите запоминать формулы?
Опишите задачу на русском — AI сгенерирует формулу с правильными ссылками на вашу таблицу. 5 запросов бесплатно.
Попробовать бесплатно →Читайте также
ТОП-20 формул Excel, которые должен знать каждый бухгалтер
Подборка самых нужных формул Excel для бухгалтерии: СУММЕСЛИ, ВПР, ЕСЛИ, СЧЁТЕСЛИ и другие. С примерами и объяснениями на русском.
Как сделать ВПР (VLOOKUP) в Google Таблицах — полное руководство
Подробное руководство по VLOOKUP в Google Sheets: синтаксис, примеры, ошибки, альтернативы (INDEX/MATCH, XLOOKUP). С картинками и объяснениями.