Как создать диаграмму Парето в Excel

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

1. Прежде всего, отсортируйте данные в ячейках по убыванию. Для этого нажмите левой кнопкой мыши на верхнюю ячейку в столбце Count (количество жалоб), затем на вкладке Данные (Data) найдите и нажмите кнопку ЯА (ZA) – сортировка по убыванию.
2. Далее подсчитаем количество с накоплением (Cumulative Count). Для этого введите представленную ниже формулу в ячейку С5 и протяните её вниз по всему столбцу.
3. Далее посчитаем накопленный процент (Cumulative %). Для этого введите представленную ниже формулу в ячейку D4 и протяните её вниз по всему столбцу.

Примечание: В ячейке С13 отображается общее число жалоб. При протягивании формулы вниз абсолютная ссылка $С$13 остаётся неизменной, в то время как относительная С4 меняется на С5, С6, С7 и т.д.

4. Выделите данные в столбцах A, B и D. Для этого нажмите и удерживайте клавишу Ctrl во время выделения нужного диапазона ячеек в каждом столбце.
5. На вкладке Вставка (Insert) щёлкните по кнопке Гистограмма (Column), затем в выпадающем меню выберите Гистограмма с группировкой (Clustered Column).
6. Кликните правой кнопкой мыши по красным столбцам (накопленный %) и в контекстном меню выберите Изменить тип диаграммы для ряда (Change Series Chart Type).
7. Выберите тип График с маркерами (Line with Markers).
8. Кликните правой кнопкой мыши по красной линии и выберите Формат ряда данных (Format Data Series).
9. Выберите пункт По вспомогательной оси (Secondary Axis).
10. Кликните правой кнопкой мыши по вспомогательной оси (ось процентов) и выберите Формат оси (Format Axis), установите Максимум (Maximum) равным 100 и нажмите Закрыть (Close).

В результате мы получаем следующее:

Заключение: Диаграмма Парето показывает, что 80% всех жалоб составляют жалобы 20% видов: Слишком дорого (Overpriced) и Маленькие порции (Small portions). Другими словами, мы видим принцип Парето в действии.