Пример построения диаграммы Парето в Excel скачать шаблон

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

На каком принципе основана диаграмма Парето

Название диаграммы происходит от имени итальянского экономиста Вильфредо Парето (1848-1923), который обнаружил интересную связь, позже названную правилом 20/80. Ну, в 1887 году в Италии он заметил, что 80% богатства всей страны принадлежало 20% населения. Принцип, замеченный Парето, стал восприниматься позже в других областях экономической практики. Спустя 54 года Джозеф Джуран, изучая состояние «качества», снова пришел к выводу, что 80% проблем вызваны 20% причинами, что подтверждает тезис его предшественника. На практике сам принцип 20/80 не всегда распределяется равномерно. Тем не менее, его применение может быть полезным в нескольких аспектах деятельности компании, например, для устранения наиболее распространенных причин неудовлетворенности услугами компании. Или чтобы вычислить те 20% клиентов, которые приносят 80% прибыли и т.п.

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

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

Для построения диаграммы мы будем использовать довольно обширный диапазон данных о компаниях из CNBC. Наш анализ буде, посвящен переводу прибыли и активов за границу крупнейшими компаниями США, чтобы избежать уплаты налогов в США. Необходимо узнать соотношение количества компаний, и какая на них приходится доля денег (в процентах) находящихся в офшорных зонах. Данные, которые нам понадобятся для построения диаграммы - это название компании и суммы оффшорного перевода. На основе этих двух столбцов мы создадим дополнительные столбцы, полезные для создания диаграммы Парето в Excel.

Подготовка данных и формулы

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

Таблица показателей активов в оффшорных зонах для всех известных компаний:

показатели активов в оффшорных зонах.

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

вычисление роста доли.

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

вычисление точки пересечения соотношений.

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

вспомогательный столбец.

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

  1. Количество – по данному столбцу будет определятся количество выбранных пользователем компаний.
  2. Наименование – название компаний.
  3. Оффшорные ресурсы (млн. $) – показатели объемов оффшорных финансовых ресурсов, полученных по статистическим данным из внешних соответственных источников.
  4. Всего в офшорах – нарастающая сумма всех показателей объемов оффшорных финансов в соответствии с ростом количества компаний.
  5. % оффшор – здесь показано какую процентную долю по нарастанию количества составляет сумма оффшорных финансов по отношению к общему объему средств.
  6. Вспом. % оффшор – вспомогательный столбец для выборки процентной доли оффшорных ресурсов в зависимости от указанного количества компаний пользователем.

Порядок построения диаграммы Парето в Excel на основе данных

Используя подготовленную таким образом таблицу, мы создаем первую часть графика, благодаря которой получаем соответствующую горизонтальную ось X с количеством компаний. Для этого выделите два несмежных диапазона ячеек удерживая клавишу CTRL: A5:A291 и E5:E291 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Точечная»-«Точечная с гладкими кривыми»

создаем первую часть графика.

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

Как сделать динамическую диаграмму Парето в Excel

Метод получения эффекта заполнения под графиком будет реализован с помощью масштабирования от столбца (X) до 0-1000. На этом этапе лучше воздержаться от подробного описания всех шагов, связанных с построением дополнительных таблиц, потому что в конце статьи можно скачать диаграмму Парето в Excel. На данном этапе достаточно лишь показать все формулы:

  1. Вычисление входящего параметра формул, возвращающих значения необходимые для образования первой большой заполненной (статической-синей) диаграммы на графике.
    2. входящий параметр формул.
  2. Формула вычисления размеров по оси X для большой синей заполненной диаграммы:
    3. размеры по оси X для большой.
  3. Вычисление входящего параметра формул, возвращающих значения необходимые для образования второй малой заполненной (динамической-зеленой) диаграммы на графике.
    4. параметра формул для малой.
  4. Формула вычисления размеров по оси X для малой зеленой заполненной диаграммы:
    5. по оси X для малой диаграммы.
  5. Табличка для подписей пределов границ второй динамической зеленой диаграммы в месте точки пересечения соотношения количества компаний и процентной доли финансовых ресурсов в оффшорных зонах:
    6. подписи пределов границ.
  6. Табличка для вычисления столбцов границ пределов для второй динамической зеленой диаграммы в месте точки пересечения количества и доли %.
    7. вычисление столбцов границ пределов.

Таким образом мы подготовили все необходимые данные для построения графика Парето в Excel. Общая картина данных выглядит следующим образом:

Общая картина данных.

Давайте перейдем к следующей части – настройка диаграммы Парето. На следующем шаге скопируйте все данные из столбца «Большая площадь» в диапазоне ячеек L3:M293 и нажмите на созданный график, чтобы активировать его. После чего выберите инструмент: «ГЛАВНАЯ»-«Буфер обмена»-«Вставить»-«Специальная вставка» или нажмите комбинацию горячих клавиш CTRL+ALT+V. В результате на график добавиться еще один ряд данных:

настройка.

Мы снова нажимаем на график, подготовленный таким образом, как показано выше на рисунке и используя спрайт на вкладке «Работа с диаграммами»-«КОНСТРУКТОР»-«Тип»-«Изменить тип диаграммы», мы меняем точечный тип большого графика на диаграмму с областями, представленной на оси вспомогательных данных:

Изменить тип диаграммы.

Выполните эти же действия скопировав на график данные из двух столбцов P4:Q293 таблицы «Малая площадь»:

скопировав на график данные.

В результате получим следующий вид графика Парето:

предварительный вид графика.

Теперь нам необходимо убрать лишнюю правую вертикальную ось Y значений процентов:

убрать правую ось Y.

Как вы можете видеть на первый взгляд, область под линией еще не идеально подобрана. Чтобы изменить это, установите масштаб горизонтальной оси в диапазоне от 0 до 286 (общий диапазон анализируемых строк). Это решение не совсем идеально, потому что оно не позволяет отображать более широкий масштаб легенды. Чтобы определить более широкий масштаб шкалы, добавьте вспомогательные строки в количестве, соответствующем предполагаемому масштабу, в таблицы, на которых построена диаграмма. Делаем настройку оси X:

настройка оси X.

Меняем цвета заливок областей и делаем прозрачный цвет для синей линии чтобы скрыть ее:

Меняем цвета заливок.

График почти готов.

Информативный результат на диаграмме Парето в Excel

На этом этапе мы добавим к нему еще точечный график, который будет состоять из одной точки, из которой будем вести вертикальные и горизонтальные полосы границ погрешностей. Данный точечный график будет использовать значения из таблички «Точка пересечения». Для этого выделите график и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Данные»-«Выбрать данные»-«Добавить» и заполните параметрами поля ввода так как показано ниже на рисунке:

точечный график Добавить.

В 2 шага добавляем предел погрешностей для точки пересечения (вертикальную и горизонтальную линию):

  1. Выбираем ряд данных (из одной точки) «Точка пересечения» с помощью инструмента: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Элементы диаграммы»-«Ряд Точка пересечения».
    2. Точка пересечения.
  2. Добавляем ему новый элемент – «поредел погрешностей» с помощью инструмента: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Предел погрешностей»-«Дополнительные параметры предела погрешностей»:
    3. поредел погрешностей.

В настройках формат предела погрешностей указываем пользовательскую величину погрешности взяв значения из ячеек I2 и J2.

Таким образом мы настроили вертикальную ось погрешностей, а сейчас настроим горизонтальную. Для этого выберите инструмент «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Элементы диаграммы»-«Ряд Точка пересечения Предел погрешностей по X»:

параметры предела погрешностей X.

После чего снова выбераем инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Предел погрешностей»-«Дополнительные параметры предела погрешностей»:

Добавить элемент диаграммы.

Также как мы добавляли точку пересечения точечным графиком ряда данных из одной точки, аналогичным способом добавляем еще 2 точки, то есть еще 2 ряда данных точечного графика, которые берут данные из таблички XY+-:

добавляли точку пересечения точечным графиком.

Теперь эти две дополнительные точки будут использоваться для отображения координат точки пересечения (вертикальный и горизонтальный этикет подписи данных).

Сначала выделяем одну из точек уже знакомым нам инструментом: инструмент «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Текущий фрагмент»-«Элементы диаграммы»-«Ряд подпись вертикаль». А затем добавляем подпись значений ряда: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Подписи данных»-«Справа». Делаем правый щелчок мышкой по подписи и из появившегося контекстного меню выбираем опцию: «Формат подписей данных». В настройке подписей отмечаем галочку «значения из ячеек» и указываем ссылку на значения из ячейки B2:

Формат подписей данных.

Выполняем аналогичные действия для ряда «подпись горизонталь» только указываем ссылку на ячейку B3. Динамическую диаграмму Парето копируем на отдельный лист под названием «График» и любуемся готовым результатом:

Скачать диаграмму Парето.

Скачать шаблон диаграммы Парето в Excel

Диаграмма интерактивная пользователь вводит в ячейку F2 необходимое количество компаний, которое нужно выбрать из общей базы (на листе Данные). Жмем Enter и сразу обновляется результат визуализации данных с точными маркерами текущих значений соотношения количества компаний и их процентной доли оффшорных финансовых ресурсов от общего капитала в офшорных зонах.


en ru