Диаграмма Парето в Excel: как найти главные причины результата

Диаграмма Парето соединяет убывающие столбцы и линию накопленного процента. Она помогает увидеть, какие категории дают основной объём наблюдений, но не доказывает их причинность и не отменяет редкие критичные события. Разберём подготовку данных, встроенный инструмент Excel и проверочный расчёт на двенадцати причинах.

Комовников Никита

Эксперт по IT-решениям и автоматизации, специалист по Word, Excel, PowerPoint, 1С. Преподаватель курса «Excel + «Google Таблицы» с нуля до PRO»

Что показывает диаграмма Парето

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

Microsoft описывает диаграмму Парето как отсортированную гистограмму с линией накопленного процента. Инструмент отвечает на вопрос «где сосредоточен измеренный объём», а не «что является корневой причиной».

Когда она действительно полезна

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

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

Сформулируйте вопрос до таблицы

Плохой вопрос — «что у нас не так?». Рабочий вариант: «какие типы причин дали наибольшее число возвратов за последние восемь недель?» Укажите объект, показатель, период, источник и границу.

Формулировка определяет подготовку. Для суммы потерь нужно агрегировать деньги, для числа обращений — случаи. Нельзя сравнивать столбец рублей с линией доли количества.

Сохраните слой исходных наблюдений

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

Преобразуйте диапазон в таблицу Excel. Тогда новые строки попадут в расчёт, а фильтры помогут проверить период и дубли. Отдельный столбец отмечает исключённые записи и причину исключения.

Нормализуйте названия причин

«Долгая доставка», «доставка долго» и «задержка курьера» могут обозначать одну категорию, но объединение должен подтвердить владелец процесса. Создайте справочник соответствий и не правьте значения вручную в итоговой сводке.

Категории должны быть сопоставимы по уровню. Нельзя рядом ставить широкую «ошибку оператора» и узкую «неверную цифру в поле ИНН». Слишком широкую группу раскрывают вторым анализом.

Удалите дубли, а не повторные события

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

Запишите правило. Решение «оставить первую запись на заказ» и решение «считать каждое обращение» дают разные диаграммы, и оба могут быть корректными для разных вопросов.

Соберите итог по категориям

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

Отсортируйте значения по убыванию. При равенстве примените устойчивое правило, например алфавит. Это делает обновления воспроизводимыми.

Как построить встроенную диаграмму

1. Выделите столбец категорий и числовой показатель.
2. Откройте «Вставка» и группу статистических диаграмм.
3. Выберите «Парето».
4. Проверьте группировку категорий и подписи осей.
5. Добавьте понятный заголовок с периодом и показателем.

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

Ручная сборка для проверки

Рядом с отсортированными значениями создайте накопленный итог и накопленную долю. Для строки i: сумма от первой строки до i делится на общий итог. Столбцы строятся по значениям, линия — по доле на вторичной оси от 0% до 100%.

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

Пример по двенадцати причинам

Всего 148 случаев. Первые пять причин дают 114, или примерно 77%. Шестая переводит накопленную долю выше 80%.

Проверьте накопленную долю формулой

Если значения находятся в B2:B13, доля строки может рассчитываться как =СУММ($B$2:B2)/СУММ($B$2:$B$13). Скопируйте формулу вниз и задайте процентный формат. Последняя строка должна быть 100% с учётом округления.

Отдельно проверьте сумму первых пяти: 34 + 27 + 21 + 18 + 14 = 114. Деление 114 на 148 даёт 77,0%, а не готовое доказательство правила 80/20.

Как читать диаграмму без магии 80/20

Линия показывает концентрацию. Если пять категорий дают 77%, это означает, что работа с ними потенциально затрагивает большую долю наблюдений. Но стоимость исправления и управляемость могут различаться.

Порог 80% — ориентир для обсуждения, а не закон природы. Команда может выбрать четыре причины, если пятая неподконтрольна, или семь, если шестая и седьмая устраняются одним изменением.

Не потеряйте редкие критичные события

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

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

Превратите столбцы в проверяемые действия

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

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

Как обновлять диаграмму

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

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

Когда выбрать другой график

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

Если категорий больше двадцати, сначала укрупните или примените фильтр, но сохраните исходные значения. Объединение в «прочее» не должно скрывать отдельную критичную группу.

Типичные ошибки Excel

• Строить по несгруппированному списку.
• Смешивать количество и денежную сумму.
• Не сортировать категории.
• Считать процент от отфильтрованного вручную итога.
• Округлять до расчёта.
• Выводить десятки нечитаемых подписей.
• Объявлять верхний столбец корневой причиной.

Проверка данных важнее цвета. Сначала воспроизведите 148 случаев, затем настраивайте оформление.

Чек-лист готовой диаграммы

1. Вопрос, период и показатель указаны.
2. Сырые наблюдения сохранены.
3. Категории нормализованы.
4. Итог совпадает с источником.
5. Столбцы отсортированы.
6. Последняя накопленная доля равна 100%.
7. Оси и подписи читаемы.
8. Критичные редкие события вынесены отдельно.
9. Вывод содержит действие и проверку.

От одной диаграммы к уверенной аналитике

Парето даёт один полезный срез. Для регулярной аналитики нужны очистка данных, формулы, сводные таблицы, несколько типов диаграмм и контроль ошибок. Курс «Excel + «Google Таблицы» с нуля до PRO» помогает собрать эту систему; преподаватель — Комовников Никита.

Хорошая диаграмма Парето воспроизводима: исходные 148 наблюдений превращаются в проверенные категории, убывающие столбцы, линию до 100% и ограниченный список действий.

Сначала сформулируйте вопрос анализа

Диаграмма Парето полезна, когда нужно ранжировать категории по одному измеримому результату: числу дефектов, сумме потерь, минутам простоя или обращениям. Запишите вопрос полностью: «Какие причины сформировали наибольшую сумму возвратов за второй квартал по интернет-магазину?» Это фиксирует показатель, период и контур.

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

Нормализуйте названия категорий

Перед сводной таблицей очистите пробелы, регистр, опечатки и синонимы. «Повреждение упаковки», «упаковка повреждена» и «мятая коробка» могут описывать одно явление, но объединять их нужно по утверждённому словарю, а не по догадке аналитика. Исходное значение сохраните рядом с нормализованным.

Категория «прочее» не должна становиться самой крупной. Просмотрите входящие в неё записи и уточните классификатор. Если причины действительно неизвестны, выделите «не установлено» — это самостоятельный сигнал о качестве регистрации. Пустые значения не удаляйте молча: посчитайте их долю и решите, можно ли доверять ранжированию.

Обеспечьте сопоставимость периода и охвата

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

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

Частота и ущерб — две разные диаграммы

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

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

Одинаковые значения и устойчивый порядок

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

Пограничная категория около 80% не получает магического статуса. Покажите фактическую накопленную долю и включите категорию целиком либо объясните другое правило. Нельзя делить одну категорию только ради красивого попадания линии в отметку. Порог — ориентир для концентрации, а не доказательство причинности.

Проверьте вторичную ось и накопленную долю

Линия должна начинаться с доли первой категории и завершаться на 100%. Если последняя точка отличается, проверьте диапазон формулы, пустые строки и фильтры. Вторичная ось задаётся от 0% до 100%; автоматический масштаб может визуально преувеличить рост.

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

Детализация верхней категории

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

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

Превратите диаграмму в цикл улучшения

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

Храните версии графика рядом с журналом изменений процесса. Если абсолютное число снизилось, но объём операций тоже упал, оцените нормированный показатель. Если доля категории выросла только потому, что другие проблемы исчезли, это не обязательно ухудшение. Решение принимается по исходной метрике, а не по одному положению столбца.

Структура исходной таблицы в Excel

Храните события в длинном формате: одна строка — одно наблюдение, отдельные столбцы — дата, категория, значение, подразделение, продукт и комментарий. Не создавайте по столбцу на каждый месяц и не объединяйте ячейки. Преобразуйте диапазон в таблицу Excel, чтобы новые строки попадали в расчёт и фильтры.

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

Редкие критические события не прячутся за Парето

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

На диаграмме можно использовать отдельную пометку, но не смешивать критичность с высотой столбца. В управленческом выводе разделите два списка: крупнейшие по выбранной метрике и события, требующие реакции по пороговому правилу. Тогда правило 80/20 не становится оправданием игнорировать малочисленные, но недопустимые случаи.

Зафиксируйте пороги до построения графика и назначьте владельца каждой критичной категории. После обновления данных отдельный контроль проверяет, что ни одно такое событие не исчезло из отчёта из-за фильтра или укрупнения классификатора.

Для соседней задачи используйте объяснение принципа Парето: там сохранён отдельный контекст, который не дублирует выполненный здесь практический разбор.

Практика вместо теории

От двенадцати причин — к системе анализа данных в Excel

Курс «Excel + «Google Таблицы» с нуля до PRO» помогает освоить очистку, формулы, сводные таблицы и визуализацию. Преподаватель — Комовников Никита; по итогам обучения выдаётся Удостоверение о повышении квалификации.

Смотреть программу

Вопросы и ответы

• Диаграмма Парето всегда подтверждает правило 80/20?

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

• Можно ли строить диаграмму по денежным потерям?

  Да, если все столбцы отражают одну денежную метрику, а накопленная доля рассчитана от той же суммы.

• Зачем сохранять сырые наблюдения?

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

• Что делать с категорией «Прочее»?

  Раскрывать её, если доля растёт или внутри есть критичные события. Она не должна быть постоянной корзиной необработанных записей.

• Почему редкие причины нельзя игнорировать?

  Частота — только один критерий. Редкое событие может иметь высокий финансовый, правовой или безопасностный риск.

• Диаграмма показывает корневую причину?

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

По теме