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

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

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

Создание диаграммы Парето

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

Если выбрать два столбца чисел, а не одно из чисел и одну соответствующую текстовую категорию, Excel отобразит данные в виде интервалов, как на гистограмме. Затем вы можете настроить эти интервалы.

Выберите Вставка > Вставить диаграмму статистики, а затем в разделе Гистограмма, щелкните элемент Парето.

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

Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте диаграммы Парето, чтобы добавить на ленту область Работа с диаграммами.

Настройка интервалов

Щелкните правой кнопкой мыши горизонтальную ось диаграммы и выберите Формат оси >Параметры оси.

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

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

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

<c0>Автоматически</c0>. Это параметр по умолчанию для диаграммы Парето с одним столбцом данных. Длина интервала вычисляется по формуле Скотта.

<c0>Длина интервала</c0>. Введите положительное десятичное число, указывающее количество точек данных в каждом диапазоне.

<c0>Количество интервалов</c0>. Введите количество интервалов диаграммы Парето (включая интервалы для выхода значений за верхнюю и нижнюю границы). Длина интервала будет настроена автоматически.

<c0>Выход за верхнюю границу интервала</c0>. Установите этот флажок, чтобы создать интервал для всех значений, которые больше числа в соответствующем поле. Чтобы изменить это значение, введите десятичное число в поле.

<c0>Выход за нижнюю границу интервала</c0>. Установите этот флажок, чтобы создать интервал для всех значений, которые меньше или равны числу в соответствующем поле. Чтобы изменить это значение, введите десятичное число в поле.

Формулы для создания гистограмм в Excel 2016

Формула Скотта:

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

Выход за верхнюю границу интервала

Выход за нижнюю границу интервала

Создание диаграммы Парето

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

Если выбрать два столбца чисел, а не одно из чисел и одну соответствующую текстовую категорию, Excel отобразит данные в виде интервалов, как на гистограмме. Затем вы можете настроить эти интервалы. Подробные сведения см. в подстроке «Настройка Windows».

На ленте перейдите на вкладку Вставка и выберите (значок статистической диаграммы), а затем в разделе Гистограмма нажмите кнопку Парето.

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

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

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

Интерпретация довольно проста. Столбики – это отсортированные по убыванию значения отдельных элементов. График – соответствующие накопленные доли. Последнее значение равно 100%. Чтобы построить подобную диаграмму в Excel 2013 и более ранних версиях, нужно выполнить следующие действия:

1) отсортировать данные по убыванию, чтобы значения, имеющие наибольший вклад, были в начале списка;

2) рассчитать столбец с накопленными долями;

3) использовать комбинированную диаграмму, чтобы столбиками показать отдельные элементы, а графиком накопленные доли.

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

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

Построение диаграммы Парето в Excel 2016

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

Изобразим на основе этих данных гистограмму Парето (в Excel 2016). Вначале активируем любую ячейку в исходных данных. Затем через Рекомендуемые диаграммы либо напрямую выберем гистограмму Парето.

По умолчанию получим следующий вид диаграммы.

Полдела сделано. Осталось подобрать дизайн диаграммы и отдельных ее элементов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Создание диаграммы Парето в Microsoft Excel

Существуют задачи, которые нельзя решить с помощью Excel «методом научного тыка» или «сам всё знаю». Поэтому, порой приходится садиться за учебники и уходить в них с головой, чтобы последовательно освоить какой-нибудь новый метод. Вот так случилось и со мной, когда я, зная технологию построения диаграммы анализа Парето в предыдущей версии Excel 2003, не сумел «сходу» построить её в новой версии. Поэтому пришлось штудировать новые издания по Excel. Здесь я решил поделиться с вами моим опытом построения этой диаграммы.

Что такое анализ Парето и «с чем его едят»?

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

Результат анализа дает “принцип Парето” или принцип соотношения «20-80», который неоднократно подтверждался исследованиями в самых различных сферах жизни. Например, только 20% товаров определяют 80% всех доходов компании; 20% солдат совершают 80% подвигов; 80% дефектов возникает из-за 20% причин. Этот принцип можно научиться применять и в нашей повседневной действительности, например, чтобы что-нибудь сэкономить. Допустим, можно оценить долю действительно полезных вещей в доме, долю полезной информации в книге, долю важный файлов на жестком диске компьютера.

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

Пошаговая инструкция по созданию диаграммы Парето

1. Сперва необходимо подготовить данные. Предлагаю воспользоваться реальными данными, которые предлагает нам Госкомстат.
2. Открываем пункт «Национальные счета», затем подпункт «Валовой внутренний продукт» а там «Произведенный ВВП» — «Данные по разделам ОКВЭД» в текущих ценах.
3. Сохраняем файл с данными на свой жесткий диск и открываем его для редактирования.
4. Выделяем данные по разделам за 2012 год и копируем их на новый лист. Задаем подписи данных.
5. Создаем рядом два дополнительных столбца и подписываем их «Доля» и «Доля с нарастанием».
6. Производим сортировку данных по убыванию в столбце «Вклад сферы в валовую добавленную стоимость».
7. Внизу под данными столбца «Вклад сферы в валовую добавленную стоимость» подсчитываем сумму.
8. В столбце «Доля» создаем формулу отношения значения конкретного раздела к общей сумме. Копируем протягиванием формулу для всех ячеек столбца.
9. В столбце «Доля с нарастанием» берём результат значения первой ячейки столбца «Доля».
10. Во второй ячейке суммируем значение верхней ячейки и значение боковой ячейки из столбца «Доля».
11. Протягиваем ячейку, и заполняем все оставшиеся ячейки. В самой последней ячейке столбца «Доля с нарастанием» должно появиться значение «100%».
12. Скрываем столбец «Вклад сферы в валовую добавленную стоимость» со всеми данными.
13. Выделяем все оставшиеся данные со столбцами и нажимаем на «Вставка» -> «Гистограмма».
14. Выбираем столбец второго ряда (тот который идет вверх, и нажимаем правую кнопку мыши.
15. Здесь в контекстом меню выбираем «Изменить тип диаграммы для ряда» и тип диаграммы «График с маркерами».
16. Ограничиваем ось значений значением «100%».
17. Настраиваем графики в соответствии с вашими требованиями. Область диаграммы можно разместить на отдельном листе.

Скачать решение Парето

Вот в принципе и всё, что нужно сделать для построения диаграммы Парето в программе Microsoft Excel 2007. Надеюсь, что у вас всё получилось с первого раза. Если нет, то советую прочитать ещё раз все шаги построения. Также можно посмотреть видеоролик о том, как построить диаграмму Парето.