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

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

Для чего нужен принцип Парето

Принцип Парето получил своё название по фамилии известного итальянского учёного и экономиста, Вильфредо Парето (1848-1923). В основе принципа лежит правило: 20 % усилий дают 80 % результата, и наоборот – 80 % усилий дают всего 20 % результата.

Этот принцип может дать ценную и важную информацию в ситуации, когда есть множество проблем, и требуется выбрать, какую из них следует решать в первую очередь. Или когда требуется определить, какой товар/услуга приносит больше всего прибыли. Так же, принцип может пригодиться в сложных и неоднозначных ситуациях. Чаще всего его применяют в бизнесе – для рационализации и подсчета использования ресурсов.

К примеру, вас назначили главой в команду, имеющую затруднения в работе с новым проектом. В ходе опроса работников об основных трудностях и препятствиях в выполнении работы и изучении отчётной документации составляется список проблем. После того, как список проанализирован, становится возможным составить перечень и провести ранжирование наиболее часто встречающихся затруднений в работе. В процессе изучения становится понятно, что отсутствие взаимопонимания между работниками и акционерами проекта является причиной большей части проблем из списка, а второй по численности проблемой является недостаток технического оснащения (компьютеров, серверов и др.). Другие проблемы играют гораздо меньшее значение. Так, становится понятно, что наладив коммуникацию работников и акционеров проекта, получится устранить большую часть противоречий и проблем, а решив вопрос с техникой – избавиться примерно от 90 % препятствий для успешной реализации проекта. Увидеть возможные способы решения затруднений поможет составление диаграммы Парето.

Использование диаграммы Парето в Microsoft Excel

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

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

Построение таблицы для диаграммы Парето

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

  • training fees (плата за обучение),
  • hardware (компьютерная аппаратура),
  • office supplies (канцелярские товары),
  • software (программное обеспечение),
  • mileage (затраты на проездные),
  • other (другое).

Точно вводим имеющиеся числа в столбцы A – category (категория) и B – amount (сумма). Для расположения данных в порядке убывания следует выделить столбец B с числами, нажать на правую кнопку мыши, с помощью команды data (данные) отсортировать их в порядке убывания стоимости sort by (сортировать по), largest to smallest (сортировка по убыванию).

Далее, следует использовать функцию =SUM() для получения общей суммы затрат. Требуется добавить ячейки B3:B8 для получения суммы всех расходов. Возможно выбрать ячейку B9 и нажать ALT + “=”, что сделает тоже самое, что и команда SUM. Получаем общую сумму.

Создание диаграммы Парето в Excel (пример)
Вводим данные и высчитываем сумму затрат

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

Создание диаграммы Парето в Excel (пример)
Общая сумма затрат

Далее, требуется создать столбец cumulative amount (накопленная сумма). Надо ввести первую сумму в ячейку C3 – 3750 или B3. Каждое последующее число должно складываться с предыдущим значением в столбце C. Следовательно, в ячейке C4 вводим  = C3 + B4, после чего нажимаем «Enter». Для автоматического заполнения оставшихся данных – делаем двойной щелчок на autofill handle (маркер автозаполнения), или, выделив его левой кнопкой мыши, тянем вниз.

Создание диаграммы Парето в Excel (пример)
Создаем столбец накопленной суммы
Создание диаграммы Парето в Excel (пример)
Применяем маркер автозаполнения для подсчёта совокупного процента

Далее, создаём столбец cumulative % (совокупный процент). Для данного столбца его можно использовать. Для этого применяются два ранее созданных столбца: B – сумма, C – кумулятивная сумма. В ячейку D3 вводим = C3 / $ B $ 9, после нажимаем «Enter». Знак «$» создает absolute reference (абсолютная ссылка), поэтому общая сумма в ячейке B9 не изменится при перетаскивании формулы вниз.

Создание диаграммы Парето в Excel (пример)
Высчитываем кумулятивный %

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

Создание диаграммы Парето в Excel (пример)
Используем автозаполнение на показатель кумулятивного %

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

Чтобы построить диаграмму Парето в Excel, действуйте в соответствии с приведенной ниже схемой:

1. Выделяем ячейки от B2 до D8. Сочетание клавиш «Alt» и F1 автоматически создаёт диаграмму из выделенных данных.

Создание диаграммы Парето в Excel (пример)
Выделяем данные для построения диаграмы
Создание диаграммы Парето в Excel (пример)
Автоматически созданный из данных график

2. Жмём правой кнопкой мыши по диаграмме и выбираем Select Data (выбрать данные).

Создание диаграммы Парето в Excel (пример)
Окно выбора данных

3. Выбираем data source (источник данных).

4. Выбираем накопленную сумму и подтверждаем удаление.

5. На самом графике выбираем совокупный %, жмём на него правой кнопкой мыши и меняем Change chart series type (Изменить тип диаграммы).

Создание диаграммы Парето в Excel (пример)
Линейный график данных

6. Выбираем line graph (линейный график).

Создание диаграммы Парето в Excel (пример)
Окно выбора типа графика
Создание диаграммы Парето в Excel (пример)
Так выглядит график с добавленным показателем кумулятивного %

По итогу получается гистограмма с плоским линейным графиком вдоль оси X. Для получения кривой для совокупного % требуется добавить вертикальную ось. Действуем следующим образом:

7. Жмём правой кнопкой мыши на строку c совокупным % и выбираем Format data series (формат ряда данных).

8. Выбираем Secondary Axis (Вторичная ось) в разделе Series options (параметры ряда).

Создание диаграммы Парето в Excel (пример)
Окно параметров ряда

9. Далее нажимаем Close (закрыть).

Таким образом мы создаём полную диаграмму Парето. На данной диаграмме синими столбцами обозначены затраты на нужды работников от больших к меньшим (то, что было отсортировано на этапе составления таблицы). Зеленая линия отображает накапливаемый процент расходов – видно, что пороговая линия в 80% останавливается на канцелярских товарах.

Создание диаграммы Парето в Excel (пример)
Так выглядит готовая диаграмма Парето

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

Особенности подбора данных для диаграммы Парето

Перед созданием таблицы для диаграммы Парето, следует обратить внимание на следующие важные факторы:

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

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

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

20 + семь =

ehhu.ru w0o.ru 4-man.ru Adblock
detector