Лабораторная работа N 1
Построение диаграммы Парето
Цель работы: освоить технологию расчета и построения диаграммы Парето в среде Excel
Теоретическая часть. Анализ Парето – это способ исследования и организации данных, предложенный в 1897 году итальянским экономистом Вильфредо Парето. Анализ Парето может быть применен для решения различных экономических и технических задач, например, для распределения финансовых средств, для принятия мер при оптимизации ситуации. На основе анализа строится диаграмма Парето, позволяющая выделить жизненно важное меньшинство по сравнению с незначительно важным большинством. В результате анализа был сформулирован так называемый “ принцип Парето ” или принцип соотношения “20-80”, который подтверждается количественными исследованиями в самых различных сферах жизни. Так, 20% товаров определяют 80% доходов компании; 20% преступников совершают 80% преступлений; 20% имеющейся одежды люди носят в течение 80% времени. Можно найти немало полезных идей в результате исследований с применением этого принципа. Например, можно оценить долю действительно нужных книг в шкафу, долю полезной информации в газете, долю нужных файлов на диске компьютера.
Основной задачей настоящей работы является освоение технологии быстрого построения диаграммы Парето с использованием средств автоматизации приложения Exсel. Построение диаграммы Парето рассмотрим на примере некоторой обобщенной задачи выявления перечня факторов и Вкладов (долей) факторов в общий результат. Факторами могут быть перечень мер для повышения производительности труда, перечень заболеваний и ущерб от каждого заболевания для предприятия, перечень правонарушений и затраты на борьбу с этими правонарушениями.
Задание № 1
В Excel создадим компьютерную модель решения задачи.
Компьютерная модель = ячейки данных + ячейки вычислений + ячейки результатов
Алгоритм создания компьютерной модели решения задачи
1. Выбрать на листе Excel ячейки и ввести в них данные с пояснениями
2. Последовательно решать задачу: выполнять действия, расчеты и вносить комментарии к ячейкам, в которых производились расчеты
3. Выделить ячейки с результатами (цветом, шрифтом и др.) и написать вывод
Рис. Способы размещения компьютерной модели на листе Excel
В среде Excel составить таблицу исходных и расчетных данных задачи.
Таблица расчета диаграммы Парето
| Действия | Пояснения и визуализация действий |
| 1.В Excel заполнить данные таблицы (отмечено серым). Вклады факторов в общий результат во втором столбце необходимо расположить в порядке их убывания (если они так не расположены) с помощью команды Данные-Сортировка-По убыванию. В первом столбце указать наименование соответствующего фактора. Для организации новой строки в ячейке, нажать Alt+enter | 
|
2. Чтобы подсчитать сумму факторов во втором столбце, выделить вклады факторов вместе с пока пустой ячейкой (куда поместится сумма) и нажать на кнопку  на панели инструментов
| 
|
| 3. Рассчитать в процентах долю вклада каждого фактора от общей суммы вкладов (столбец3). В ячейку 3 столбца (соответствующую Ф1) набрать формулу и протянуть вниз маркер заполнения строк в столбце | 
 
|
| 4. Рассчитать данные Парето (столбец4). Данные Парето получены постепенным накапливанием долей каждого фактора. Первая строка столбца 4 совпадает со значением первой строки столбца 3. Вторая строка столбца 4 получена суммированием значения первой строки столбца 4 и значения второй строки столбца 3, (35.77 + 26.69 = 62.46). Третья строка столбца 4 получена суммированием значения второй строки столбца 4 и значения третьей строки столбца 3 (62.46 + 14.10 = 76.55) и т.д., до получения последнего значения столбца 4. Для этого ввести формулу и протянуть вниз маркер заполнения строк в столбце |
 
|
| 5.О корректности вычислений свидетельствует число 100 в строке последнего фактора, соответствующее 100% результата. | 
|
| 6. Для построения диаграммы Парето выделим данные первого, второго и четвертого столбцов (для выборочного выделения используют клавишу “Ctrl”), не выделяя сумму. В режиме “Мастера диаграмм ” (или “Вставка-Диаграмма) выбрать тип диаграммы (Нестандартные-График гистограмма 2), позволяющей отобразить трехосевую диаграмму | 
|
| 7.Проанализируем диаграмму. Проведем горизонтальную прямую, соответствующую 80% вкладов факторов до пересечения с графиком вкладов (в общем случае анализ проводится для любого числа процентов). Слева от точки пересечения размещены факторы, обеспечивающие 80% результата. На реальных данных можно проверить справедливость принципа Парето. Сделать письменный вывод результатов исследования. | 
|
Для ряда задач построение диаграммы в среде Excel позволяет моделировать различные варианты распределения вкладов и отслеживать их влияние на результат. При этом изменения вносятся в первый и второй столбцы таблицы 1. А распределение вкладов автоматически определяется в третьем и четвертом столбцах таблицы, а также на диаграмме Парето.
Задание № 2
Построить диаграмму Парето согласно варианту. Сделать письменный вывод о проведенном самостоятельном исследовании
1.Крупными и средними предприятиями Оренбургской области за 1999 г. произведено продукции на 37997.9 млн. руб. По отраслям промышленности соотношение произведенной продукции характеризуется данными, представленными в таблице 2.2.
2.Производство основных видов продукции пищевой промышленности в Оренбургской области в 1999 г. характеризуются данными, представленными в
Таблице 2.3.
3. Производство зерна в хозяйствах Оренбургской области в 1999 г. представлены в таблице 2.4.
Таблица 2.4 - Исходные данные для варианта 3.
4.Грузооборот по Оренбургской области представлен объемом погрузки по основным видам грузов. Данные за 1999 г приведены в таблице 2.5.
Таблица 2.5. – Исходные данные для варианта 4
5.Распределение субъектов хозяйственной деятельности по формам собственности в 2000 г. по Оренбургской области представлено в таблице 2.6.
Таблица 2.6. – Исходные данные для варианта 5.
6. Распределение населения Оренбургской области по размеру среднедушевого денежного дохода представлено данными таблицы 2.7.
Таблица 2.7 – Исходные данные для варианта 6.
7.Данные о составе граждан, привлеченных на работу в Оренбургскую область
В 1999 г. представлены в таблице 2.8.
Таблица 2.8 – Исходные данные для варианта 7.
8.Данныераспределения домашних хозяйств по размеру занимаемого жилья по России в 1998 г. представлены в таблице 2.9.
Таблица 2.9. Исходные данные для варианта 8.
9.
Таблица 2.10. Исходные данные для варианта 9.
10.
Таблица 2.11. Исходные данные для варианта 10.
11.
Таблица 2.12. Исходные данные для варианта 11.
|
12.
Таблица 2.13. Исходные данные для варианта 12.
13.
Таблица 2.14. Исходные данные для варианта 13.
14.
Таблица 2.15. Исходные данные для варианта 14.
15.
Таблица 2.16. Исходные данные для варианта 15.
Задание №3
Определить факторы, влияющие на проведение Вашего научного исследования. Экспертно оценить вклад (важность) каждого фактора (в %) в реализацию Вашего исследовательского проекта. Применить принцип Парето для анализа влияния факторов.
Контрольные вопросы
1. Что такое анализ Парето?
2. В чем состоит принцип Парето?
3. Алгоритм построения диаграммы Парето