В этой статье вы узнаете, как создать динамическую диаграмму Парето.

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

Разберемся на примере:

  • У нас есть данные о жалобах школы, и нам нужно создать диаграмму Парето.

img1

Нам нужен вспомогательный столбец Cumulative% в столбце C; формула в ячейке C2 будет = СУММ ($ B $ 2: B2) / СУММ ($ B $ 2: $ B $ 11) * и перетащите формулу вниз

img2

Для создания динамического Парето; На диаграмме нам нужны три ячейки, в которых мы будем делать некоторые вычисления. Прежде всего, мы создадим полосу прокрутки и свяжем ее с ячейкой B16. Щелкните правой кнопкой мыши полосу прокрутки и выберите Управление форматом и введите значения, как показано на снимке ниже

img3

|||| === В ячейке B14 формула: = B16 / 100 для вычисления * Цель

В ячейке B15; у нас есть следующая формула для * Cumulative%

* = ИНДЕКС ($ C $ 2: $ C $ 11, ЕСЛИ ОШИБКА (MATCH ($ B $ 14, $ C $ 2: $ C $ 11,1), 0) +1)

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

img4

В ячейке D2 формула: * = IF ($ B $ 15> = C2, B2, NA ())

img5

В ячейке E2 формула: * = IF ($ B $ 15 <C2, B2, NA ())

  • Наконец, все готово для создания диаграммы Парето; нам нужно выбрать диапазон A1: A11 и C1: E11

img6

На вкладке «Дизайн» нажмите «Тип диаграммы». «Применить тип линейной диаграммы к совокупному%» и нажмите «Вторичная ось»

img7

Нам нужно сделать кумулятивный% равным 100%, поскольку он показывает 120%. Щелкните правой кнопкой мыши по вторичной оси и выберите «Ось формата». * Выберите максимальное значение как 1 вместо 1,2

img8

img9

Теперь все готово к просмотру динамической диаграммы Парето; единственная проблема заключается в том, что когда вы нажмете на полосу прокрутки, вы обнаружите, что панель сместится из своего исходного положения, потому что есть две серии Чтобы решить эту проблему, мы нажмем на панель Broken Benches (Highlighted%) Bar и щелкните правой кнопкой мыши по ней выберите Format Data Series В поле «Перекрытие серий» введите 100%, см. Снимок ниже

img10

  • Чтобы увидеть, какой целевой% выбран; в ячейку J24 введите формулу как

img11

* = «Цель» & ТЕКСТ (B14, «0%»)

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

img12