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

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

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

Примечание: Прежде чем начать создавать диаграмму Парето, убедитесь, что у вас есть установленная версия Microsoft Excel на вашем компьютере.

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

Шаг 1: Подготовка данных для диаграммы Парето

Чтобы создать диаграмму Парето в Excel, необходимо вначале подготовить данные, которые будут использоваться для построения диаграммы. Вот несколько шагов, которые помогут вам сделать это:

1. Определите категории:

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

2. Соберите данные:

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

Пример таблицы с данными:

3. Отсортируйте данные:

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

4. Вычислите сумму и процент:

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

5. Постройте диаграмму:

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

Пример диаграммы Парето:

Теперь у вас есть подготовленные данные для создания диаграммы Парето в Excel. Следующий шаг — выбрать программу Excel и начать создание диаграммы согласно вашим данным.

Шаг 2: Создание столбчатой диаграммы в Excel

1. Выделите столбец с категориями (оси x) и столбец с частотой (оси y).
2. Нажмите правой кнопкой мыши на выбранный диапазон ячеек и выберите пункт «Вставить».
3. В открывшемся меню выберите тип столбчатой диаграммы.
4. Excel автоматически создаст столбчатую диаграмму на вашем листе.
5. Проделайте следующие действия для изменения внешнего вида диаграммы:

а) Название диаграммы:

1. Кликните дважды на название диаграммы, чтобы изменить его на более понятное.

б) Оси:

1. Щелкните правой кнопкой мыши на любой оси и выберите «Формат оси».
2. В открывшемся окне настройки оси можно изменить следующие параметры: наименование оси, масштаб, деления и другие.

в) Легенда:

1. Чтобы изменить наименование легенды, щелкните на нее дважды и введите новое название.

г) Цвета столбцов:

1. Щелкните правой кнопкой мыши на любом столбце и выберите «Формат данных столбцов».
2. В открывшемся окне настройки столбцов можно изменить цвет, градиент и другие параметры.

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

Шаг 3: Сортировка данных по убыванию

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

1. Выделите столбец с данными, которые вы хотите отсортировать.

2. Нажмите на вкладку «Данные» в верхней панели меню.

3. В группе «Сортировка и фильтр» выберите «Сортировать по возрастанию» или «Сортировать по убыванию», в зависимости от того, как вы хотите отсортировать данные.

4. В появившемся окне «Сортировка» выберите столбец, по которому вы хотите отсортировать данные.

5. Убедитесь, что опция «Расширенные параметры» отключена и нажмите «ОК».

После выполнения этих действий, данные будут отсортированы по выбранному столбцу по убыванию.

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

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

Шаг 4: Добавление второй оси Y для диаграммы Парето

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

Чтобы добавить вторую ось Y, следуйте этим шагам:

1. Выделите график, щелкнув на нем.
2. Щелкните правой кнопкой мыши и выберите «Изменить данные диаграммы» в контекстном меню.
3. В диалоговом окне «Выбор источника данных» выберите «Редактировать» в разделе «Горизонтальные (категорийные) оси» и нажмите «ОК».
4. В открывшемся диалоговом окне «Редактирование источника данных» выделите ячейку со значениями накопленной суммы и нажмите «ОК».
5. Вернитесь в диалоговое окно «Выбор источника данных» и выберите «Редактировать» в разделе «Вертикальные (числовые) оси» и нажмите «ОК».
6. Выделите ячейки с долей каждого фактора и нажмите «ОК».

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

Поздравляю! Теперь вы знаете, как добавить вторую ось Y для диаграммы Парето в Excel.

Шаг 5: Расчет кумулятивного процента

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

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

1. Выберите пустую ячейку и введите формулу для расчета кумулятивного процента.
   • Например, если данные процентов находятся в ячейках B2:B6, а кумулятивный процент нужно расчитать в ячейках C2:C6, введите формулу =СУММ($B$2:B2) в ячейку C2 и скопируйте ее в ячейки C3:C6.
2. Нажмите клавишу Enter, чтобы применить формулу и получить кумулятивный процент для первой категории данных.
3. Скопируйте формулу в остальные ячейки C3:C6, чтобы получить кумулятивный процент для остальных категорий данных.

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