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

Диаграмма Парето позволяет определить, какие 20% причин формируют до 80% результата, будь то дефекты продукции, источники затрат или категории обращений клиентов. В Excel такую диаграмму можно построить за 10–15 минут, если правильно подготовить таблицу, рассчитать накопительный процент и настроить комбинированный график с дополнительной осью. Ошибка на любом из этапов – от некорректной сортировки до неверной формулы – приводит к искажению приоритетов.

Для построения потребуется исходная таблица из двух столбцов: категории и числовые значения (количество, сумма, частота). Минимальный объем данных – 5–7 строк, иначе анализ теряет смысл. Перед созданием диаграммы необходимо отсортировать значения по убыванию и добавить столбец накопительного итога с расчетом доли каждой категории в общем объеме.

В статье разобран практический алгоритм: от подготовки структуры данных до настройки вторичной оси и отображения линии 80%. Каждый шаг сопровождается конкретными действиями в интерфейсе Excel и формулами, которые можно сразу применить к своим данным без дополнительной доработки.

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

Подготовка исходной таблицы: как правильно оформить данные для диаграммы Парето

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

Каждая строка представляет одну уникальную категорию. Повторы наименований недопустимы: если данные собирались построчно (например, по каждой жалобе), их необходимо предварительно агрегировать с помощью сводной таблицы или функции СУММЕСЛИ, чтобы получить итог по каждой группе.

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

Заголовки формулируются кратко и однозначно: «Причина», «Количество», «Сумма, руб.». Нельзя оставлять пустую строку над таблицей или вставлять вспомогательные комментарии внутри диапазона – Excel будет воспринимать их как часть данных.

Числовой столбец проверяется на корректность формата: тип ячеек – Числовой или Денежный, без текстового формата. Если значения выровнены по левому краю, это признак текстового типа; такие данные необходимо преобразовать через «Текст по столбцам» или умножением на 1 в отдельной ячейке.

Перед дальнейшими действиями рассчитывается общий итог показателя. В нижней строке добавляется формула =СУММ(B2:B14) или соответствующий диапазон. Эта сумма понадобится для вычисления накопительного процента и контроля корректности расчетов.

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

Сортировка категорий по убыванию значений перед построением диаграммы

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

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

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

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

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

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

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

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

Расчет накопительного процента с помощью формул Excel

После сортировки данных добавляется третий столбец – Накопительный %. В ячейке C2 вводится формула, которая делит сумму значений от первой строки до текущей на общий итог показателя. Предварительно общий итог фиксируется, например в ячейке B15, формулой =СУММ(B2:B14).

Для первой строки используется формула вида =СУММ($B$2:B2)/$B$15. Абсолютная ссылка на первую ячейку диапазона и на итог позволяет корректно протянуть расчет вниз без смещения базы сравнения.

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

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

Если итоговая ячейка не используется отдельно, можно встроить расчет общей суммы прямо в формулу, например =СУММ($B$2:B2)/СУММ($B$2:$B$14). Такой подход упрощает структуру таблицы, но увеличивает вычислительную нагрузку при больших массивах данных.

При работе с динамическими диапазонами рекомендуется оформить данные как таблицу (Ctrl+T). В этом случае формула принимает структурированный вид, а диапазон автоматически расширяется при добавлении новых строк.

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

Создание комбинированной диаграммы на основе столбцов и линии накопительного итога

Для построения выделяется диапазон из трех столбцов: категории, числовые значения и накопительный процент, без строки общего итога. Если таблица расположена в A1:C14, диапазон выделения – A1:C14, где первая строка содержит заголовки.

На вкладке Вставка выбирается тип Комбинированная диаграмма. В открывшемся окне для числового столбца устанавливается тип «Гистограмма с группировкой», а для накопительного процента – «График».

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

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

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

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

Ширина зазора между столбцами настраивается через параметры ряда данных. Оптимальное значение – 50–70%, что делает диаграмму компактной и облегчает визуальное сопоставление высоты столбцов.

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

Настройка вторичной оси для отображения накопительного процента

Линия накопительного процента должна быть привязана к отдельной вертикальной оси, иначе масштаб столбцов исказит отображение долей. Для проверки щелкните по линии и откройте «Формат ряда данных» – параметр Вторичная ось должен быть активирован.

Правая вертикальная ось настраивается вручную. В параметрах оси устанавливаются границы: минимальное значение – 0, максимальное – 1, если используется доля, либо 0 и 100 при процентном формате. Это исключает автоматическое масштабирование выше 100%.

Основные деления задаются с шагом 0,1 или 10%, что позволяет визуально отслеживать достижение порога 80%. Мелкие деления можно отключить, чтобы не перегружать график лишними линиями.

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

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

Заголовок правой оси добавляется через элементы диаграммы. Рекомендуемая формулировка – «Накопительный процент», без дублирования единиц измерения, если формат уже задан в процентах.

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

После настройки убедитесь, что последняя точка линии совпадает с верхним пределом шкалы и отражает 100%. Это подтверждает корректную привязку и правильный масштаб вторичной оси.

Форматирование диаграммы: подписи данных, шкалы и границы 80%

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

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

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

Основная вертикальная ось масштабируется в соответствии с диапазоном данных. Автоматический максимум корректируется вручную, если верхний столбец занимает менее 70% высоты графика – это позволяет визуально выровнять пропорции.

Для фиксации границы 80% добавляется дополнительный вспомогательный ряд. В таблицу вводится постоянное значение 0,8 (или 80%) для всех категорий, после чего этот ряд отображается как линия на вторичной оси.

Линия границы форматируется пунктиром или более тонкой толщиной по сравнению с основной линией накопления. Цвет выбирается контрастный, но не доминирующий над столбцами.

Легенда редактируется вручную: оставляются только обозначения «Значение», «Накопительный %» и «80%». Избыточные подписи удаляются через параметры элементов диаграммы.

Итоговая диаграмма должна позволять за несколько секунд определить, какие категории формируют основную долю показателя и на каком элементе линия накопления пересекает уровень 80%.

Проверка корректности расчетов и интерпретация полученной диаграммы Парето

Контроль начинается с сопоставления итоговой суммы исходных данных и значения, использованного в формулах накопительного процента. Если применялась отдельная ячейка с итогом, она должна совпадать с функцией СУММ по диапазону без учета строки границы 80%.

Проверка накопительных значений выполняется по трем критериям:

• первая строка накопительного процента равна доле первой категории в общем объеме;
• каждое следующее значение больше предыдущего;
• последняя строка показывает 100% или 1 при отображении в долях.

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

Интерпретация выполняется через анализ точки пересечения линии накопления с уровнем 80%. Алгоритм определения приоритетов следующий:

1. Найти первую категорию, при которой накопительный показатель превышает 80%.
2. Определить количество категорий до этой точки включительно.
3. Сопоставить их вклад с общим числом всех позиций.

Если 80% достигаются, например, на 4-й категории из 12, это означает концентрацию основной доли показателя в 33% позиций. При равномерном распределении линия будет расти почти линейно, без выраженного перелома – такой результат указывает на отсутствие ярко выраженных приоритетов.

Завершающая проверка включает визуальную оценку: столбцы должны убывать слева направо, линия – плавно возрастать, граница 80% – четко пересекать график. Несоответствие хотя бы одному из этих признаков свидетельствует о нарушении сортировки или формул расчета.

Вопрос-ответ:

Почему линия накопительного процента на диаграмме не доходит до 100%?

Чаще всего причина связана с округлением или неправильным диапазоном формулы. Проверьте, что в расчете используется полный диапазон числовых данных без строки с итогом и без вспомогательной линии 80%. Убедитесь, что последняя формула накопления делит сумму всех категорий на общий итог. Если отображается 99%, проверьте количество знаков после запятой — при двух знаках Excel может округлять значение 0,999 до 99%.

Можно ли построить диаграмму Парето на основе сводной таблицы?

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

Как добавить горизонтальную линию 80% без изменения исходных данных?

Добавьте новый ряд данных через «Выбрать данные» и укажите диапазон с постоянным значением 0,8 или 80%, созданный в отдельном столбце. Этот столбец может находиться вне основной таблицы. После добавления измените тип нового ряда на «График» и привяжите его ко вторичной оси. Такой способ не влияет на расчет накопительных значений.

Почему после добавления новых строк диаграмма отображает не все категории?

Причина в фиксированном диапазоне источника данных. Откройте «Выбрать данные» и проверьте, расширяется ли диапазон автоматически. Если нет, оформите исходный массив как таблицу (Ctrl+T) — в этом случае новые строки будут включаться в диаграмму без ручной корректировки ссылок. Также проверьте, что формула накопительного процента охватывает добавленные ячейки.

Как интерпретировать ситуацию, когда 80% достигаются только на последней категории?

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

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

Если несколько категорий имеют одинаковое значение, каждая из них включается в расчет накопительного процента последовательно. Формула суммирования должна учитывать все предыдущие строки. Например, если первая категория 50, а вторая тоже 50, и общий итог 200, то накопительный процент первой строки = 50/200 = 25%, второй = (50+50)/200 = 50%. Такая последовательность сохраняет корректный рост линии на диаграмме и позволяет видеть, как одинаковые значения распределяются в общем объеме.

Можно ли использовать диаграмму Парето для анализа расходов компании по статьям бюджета?

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