gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 220 гостей онлайн
Вставить в таблицу Excel. Диаграмма Парето в Excel.

Диаграмма Парето в Excel.

      Есть много способов провести анализ данных в Excel. Здесь рассмотрим, как построить диаграмму Парето в Excel, чтобы провести анализ эффективности нашей деятельности. Например, какие товары приносят бо′льшую прибыль, какие причины, ошибки мешают работе, тормозят бизнес, какие усилия не приносят результат, т.д. 
      Закон Парето  – это правило 80/20 (закон природы). Суть закона  в том, что всего 20%  эффективных действий (затрат) дают 80% хорошего результата, большой прибыли. А остальные 80% (затрат) усилий дают всего 20% результата, прибыли. Выявив наиболее эффективные моменты нашей деятельности, мы сможем их развивать для улучшения качества нашей работы.
      Диаграмма Парето в Excel помогает выявить эти 20% эффективных действий, наименование товара, который дает основную прибыль, выявить основные причины ошибок, которые мешают получить хороший результат наших действий.
      Ну, всё, хватит теории. Приступим к практике.
      С помощью диаграммы Парето проведем анализ продаж в Excel.
 Мы составим диаграмму Парето по продажам продуктов. У нас есть такой отчет по продажам. Мы отсортировали его от большего к меньшему (в порядке убывания). Кнопка функции «Сортировка и фильтр» находится на закладке «Главная» в разделе «Редактирование».В столбце C устанавливаем формулу, которая посчитает, сколько процентов дохода дает каждый товар из общей суммы дохода по всем перечисленным в таблице товарам. Например, сумма по столбцу B берётся за 100%. И высчитывается, сколько процентов прибыли приносит каждый отдельный товар. Формулу напишем такую. =B2/$B$8*100
      Теперь нам нужно просчитать проценты для диаграммы Парето нарастающим итогом в столбце D. Формула будет считать процент в ячейке D2 как обычно. В ячейке D3 будет стоять результат ячейки D2+D3, т.д. В результате в последней ячейке столбца D будет стоять 100%. Формулы в ячейках столбца D будут такие, смотрите на рисунке столбец E.Это мы рассмотрели подробный вариант, чтобы можно было понять, какие данные нужны для составления диаграммы Парето.
      Вместо этих двух формул можно написать одну. В столбце F устанавливаем формат ячейки – процентный. В ячейке F2 пишем такую формулу. =СУММ($B$2:B2)/СУММ($B$2:$B$7)
Копируем формулу по столбцу. Получилось так. Результат такой же, как в столбце D.А в столбце G напишем - «80%». Это порог эффективности по закону Парето.
Итак, уберем из таблицы столбцы C, D, E, т.к. они у нас были для изучения. Добавим столбец "80%". У нас должна быть такая рабочая таблица с данными для диаграммы Парето.
Строим диаграмму обычным способом. Как построить диаграмму, настроить ее, т.д., смотрите  в статье «Как сделать диаграмму в Excel».
      Выделяем таблицу с шапкой таблицы. На закладке «Вставка» в разделе «Диаграммы» нажимаем кнопку «Гистограмма».  Получилась такая диаграмма.
Легенду можно убрать вниз. Теперь нам нужно перенести проценты (нарастающий процент прибыли) на вспомогательную ось. Нажимаем на диаграмму. На закладке «Работа с диаграммами» - «Макет» в разделе «Текущий фрагмент» выбираем из списка «Ряд «нарастающий процент прибыли»». Этот ряд выделится.
Нажимаем на выделенный ряд  правой мышкой, выбираем функцию «Формат ряда данных». В появившемся диалоговом окне ставим галочку у слов «По вспомогательной оси».
Получилась такая диаграмма.
Меняем тип диаграммы для ряда «Нарастающий процент прибыли».  Нажимаем на эту диаграмму (у нас, в примере, столбцы красного цвета) правой мышкой и выбираем функцию «Изменить тип диаграммы для ряда». Выбираем «График с маркерами». Затем, через функцию «Формат ряда данных» из контекстного меню, настраиваем ширину линии, цвет, размер, вид маркера, т.д. Получилось так.Осталось настроить ряд «Порог эффективности». Тоже переносим на вспомогательную линию, меняем тип графика на «Линия». Из контекстного меню выбираем функцию «Формат ряда данных» и настраиваем цвет, ширину, вид  линии, т.д. У нас получилась такая диаграмма Парето.
Диаграмма Парето, пример в Excel. Вместо линии "80%" на диаграмме, можно вставить прямую линию. Тогда в таблице столбец делать не нужно.  На закладке "Вставка" в разделе "Иллюстрации" нажимаем на кнопку "Фигуры". Выбираем прямую линию. Изменим цвет линии на более яркий. Но, при перемещении диаграммы, линия не переместится.
      Ну, и самое главное - вывод.
Смотрим на пересечение желтой и красной линий. 80% прибыли приносит продажа первых двух товаров – это хлеб и молоко. Остальные товары приносят всего 20% прибыли и, только, место на полках занимают.))
      Изменим цифры прибыли в таблице. Диаграмма Парето изменится так.Диаграмма Парето в Excel.Теперь по новым данным 80% прибыли приносят три товара – хлеб, молоко, огурцы.
Так можно контролировать процесс любой деятельности – прибыль, брак, ошибки, т.д.
      В Excel можно сделать диаграмму, которая будет сравнивать плановые и фактические данные. Диаграмма поможет планировать наши действия. Читайте об этой диаграмме в статье "Диаграмма в Excel план-факт".
      Можно сделать диаграмму, которая будет отражать этапы большого долгосрочного проекта, этапы работы. Диаграммой Ганта можно сделать, даже, график отпусков. Смотрите статью "Диаграмма Ганта в Excel".
      Другие примеры анализа с помощью диаграммы Парето, например, как провести анализ времени или выявить причины сбоя, ошибок, т.д., смотрите в статье "Диаграмма Парето, пример в Excel".