Существуют задачи, которые нельзя решить с помощью Excel «методом научного тыка» или «сам всё знаю». Поэтому, порой приходится садиться за учебники и уходить в них с головой, чтобы последовательно освоить какой-нибудь новый метод. Вот так случилось и со мной, когда я, зная технологию построения диаграммы анализа Парето в предыдущей версии Excel 2003, не сумел «сходу» построить её в новой версии. Поэтому пришлось штудировать новые издания по Excel. Здесь я решил поделиться с вами моим опытом построения этой диаграммы.
Анализ Парето является одним из известных современных способов исследования и организации данных, который предложил ещё в далеком 1897 году итальянский экономист Вильфредо Парето. Этот анализ можно применять при решении разных экономических задач, например, для распределения финансовых средств, при принятии важного решения. Результаты анализа диаграммы Парето, позволяют выделить «жизненно важное меньшинство» и исключить «незначительно важное большинство».
Результат анализа дает «принцип Парето» или принцип соотношения «20-80», который неоднократно подтверждался исследованиями в самых различных сферах жизни. Например, только 20% товаров определяют 80% всех доходов компании; 20% солдат совершают 80% подвигов; 80% дефектов возникает из-за 20% причин. Этот принцип можно научиться применять и в нашей повседневной действительности, например, чтобы что-нибудь сэкономить. Допустим, можно оценить долю действительно полезных вещей в доме, долю полезной информации в книге, долю важный файлов на жестком диске компьютера.
Если у вас что-то не получится, то вы в любом случае сможете скачать готовое решение и диаграмму Парето.
Пошаговая инструкция по созданию диаграммы Парето
- Сперва необходимо подготовить данные. Предлагаю воспользоваться реальными данными, которые предлагает нам Госкомстат.
- Открываем пункт «Национальные счета», затем подпункт «Валовой внутренний продукт» а там «Произведенный ВВП» — «Данные по разделам ОКВЭД» в текущих ценах.
- Сохраняем файл с данными на свой жесткий диск и открываем его для редактирования.
- Выделяем данные по разделам за 2012 год и копируем их на новый лист. Задаем подписи данных.
- Создаем рядом два дополнительных столбца и подписываем их «Доля» и «Доля с нарастанием».
- Производим сортировку данных по убыванию в столбце «Вклад сферы в валовую добавленную стоимость».
- Внизу под данными столбца «Вклад сферы в валовую добавленную стоимость» подсчитываем сумму.
- В столбце «Доля» создаем формулу отношения значения конкретного раздела к общей сумме. Копируем протягиванием формулу для всех ячеек столбца.
- В столбце «Доля с нарастанием» берём результат значения первой ячейки столбца «Доля».
- Во второй ячейке суммируем значение верхней ячейки и значение боковой ячейки из столбца «Доля».
- Протягиваем ячейку, и заполняем все оставшиеся ячейки. В самой последней ячейке столбца «Доля с нарастанием» должно появиться значение «100%».
- Скрываем столбец «Вклад сферы в валовую добавленную стоимость» со всеми данными.
- Выделяем все оставшиеся данные со столбцами и нажимаем на «Вставка» -> «Гистограмма».
- Выбираем столбец второго ряда (тот который идет вверх, и нажимаем правую кнопку мыши.
- Здесь в контекстом меню выбираем «Изменить тип диаграммы для ряда» и тип диаграммы «График с маркерами».
- Ограничиваем ось значений значением «100%».
- Настраиваем графики в соответствии с вашими требованиями. Область диаграммы можно разместить на отдельном листе.
Вот в принципе и всё, что нужно сделать для построения диаграммы Парето в программе Microsoft Excel 2007. Надеюсь, что у вас всё получилось с первого раза. Если нет, то советую прочитать ещё раз все шаги построения. Также можно посмотреть видеоролик о том, как построить диаграмму Парето.
