2. Лабораторная работа № 2.

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

Цель работы: освоить технологию расчета и построения диаграммы Парето 

                         в  среде Excel

     2.1. Теоретическая часть

     Анализ Парето – это способ исследования и организации данных, предложенный в 1897 году итальянским экономистом Вильфредо Парето.  Анализ  Парето может быть применен для решения различных экономических и технических задач, например, для распределения  финансовых средств, для принятия мер при оптимизации ситуации. На основе анализа строится диаграмма Парето,  позволяющая выделить “жизненно важное меньшинство”  по сравнению с “незначительно важным большинством. В результате анализа был сформулирован так называемый “принцип Парето” или принцип соотношения “20-80”, который подтверждается количественными исследованиями в самых различных сферах жизни. Так, 20% товаров определяют 80% доходов компании; 20% преступников совершают 80% преступлений; 20% имеющейся одежды люди носят в течение 80% времени. Можно найти немало полезных идей в результате исследований  с применением этого принципа. Например, можно оценить долю действительно нужных книг в шкафу, долю полезной информации в газете, долю нужных файлов на диске компьютера.     

     Основной задачей настоящей работы является освоение технологии быстрого построения диаграммы Парето с использованием средств автоматизации приложения Exсel. Построение диаграммы Парето рассмотрим на примере некоторой обобщенной задачи выявления перечня факторов и вкладов (долей) факторов в общий результат.  Факторами могут быть перечень мер для повышения производительности труда, перечень заболеваний и ущерб от каждого заболевания для предприятия, перечень правонарушений и затраты на борьбу с этими правонарушениями.

     2.2. Порядок работы

     В среде Excel составить таблицу  исходных и расчетных данных задачи.

Таблица 1 – таблица расчета диаграммы Парето.    

Факторы	Вклад	Доля	Данные
	Фактора (ден.ед.)	фактора (%)	Парето (%)
Ф1	8768	35,77	35,77
Ф2	6543	26,69	62,46
Ф3	3456	14,10	76,55
Ф4	2341	9,55	86,10
Ф5	1231	5,02	91,12
Ф6	976	3,98	95,11
Ф7	834	3,40	98,51
Ф8	234	0,95	99,46
Ф9	132	0,54	100,00
сумма	24515

     Вклады факторов в общий результат во втором столбце необходимо расположить в порядке их убывания с помощью команды “Сортировка - По убыванию”.  В первом столбце указать наименование соответствующего фактора. Для построения диаграммы Парето необходимо рассчитать в процентах долю вклада каждого фактора от общей суммы вкладов (столбец 3) и данные Парето (столбец 4). Данные Парето получены постепенным накапливанием долей  каждого фактора. Первая строка столбца 4 совпадает со значением первой строки столбца 3. Вторая строка столбца 4 получена суммированием значения первой строки столбца 4 и значения второй строки столбца 3,  (35.77 + 26.69 = 62.46). Третья строка столбца 4 получена суммированием значения второй строки столбца 4 и значения третьей строки столбца 3 (62.46 + 14.10 = 76.55) и т.д.,  до получения последнего значения столбца 4. О корректности вычислений свидетельствует число 100 в строке последнего фактора, соответствующее 100% результата.

    Для построения диаграммы Парето выделим данные первого, второго и четвертого столбцов  (для выборочного выделения используют клавишу “Ctrl”), не выделяя сумму.  В режиме “Мастера диаграмм” (или “Вставка - Диаграмма”) выбрать тип диаграммы (“Нестандартные - График гистограмма 2”), позволяющей отобразить трехосевую диаграмму (рисунок 3).

 

Рис. 3 – Диаграмма Парето

 

     Проанализируем диаграмму. Проведем горизонтальную прямую, соответствующую 80% вкладов факторов до пересечения с графиком вкладов (в общем случае анализ проводится для любого числа процентов). Слева от точки пересечения размещены факторы, обеспечивающие 80% результата. На реальных данных можно проверить справедливость принципа Парето. Для ряда задач построение диаграммы в среде Excel позволяет моделировать различные варианты распределения вкладов и отслеживать их влияние на результат. При этом изменения вносятся в первый и второй столбцы таблицы 1. А распределение вкладов автоматически определяется в третьем и четвертом столбцах   таблицы, а также на диаграмме Парето.

     2.3. Задание

     Построить диаграмму Парето согласно варианту. Данные для вариантов взяты из книги: Социально-экономическое положение Оренбургской области. - Оренбург: Госкомстат России, Оренбургский областной комитет государственной статистики, 1999. –227 с.

1.     Крупными и средними предприятиями Оренбургской области за 1999 г. произведено продукции    на 37997.9 млн. руб. По отраслям промышленности соотношение произведенной продукции характеризуется данными, представленными в таблице 1.

Таблица 1

Наименование отраслей промышленности	Объем произведенной продукции (млн. руб.)
Электроэнергетика	3997,7
Топливная	17141,9
Черная металлургия	6658,7
Цветная металлургия	2224,5
Химическая и    нефтехимическая	893,6
Машиностроение	2620,7
Деревоперерабатывающая	76,8
Промышленность строительных материалов	1152,5
Легкая	577,0
Пищевая	1884,4
Мукомольно-крупяная	698,0

 

 

2.     Производство основных видов продукции пищевой промышленности в Оренбургской области в 1999  г. характеризуются данными, представленными в таблице 2.

Таблица 2

Наименование основных видов продукции	Объем произведенной продукции (тонн)
Мясо, включая субпродукты	4619
Колбасные изделия	5203
Масло животное	5286
Сыр и брынза	1373
Цельномолочная продукция	37841
Мука	243783
Крупа	37112
Хлеб и хлебобулочные изделия	57146
Кондитерские изделия	6782

 

3.     Производство зерна в хозяйствах Оренбургской области в 1999 г. представлены в таблице 3.

Таблица 3

Наименование вида культуры	Объем производства зерна (тыс. тонн)
Пшеница озимая и яровая	1556,4
Рожь озимая	156,9
Ячмень	627,0
Овес	125,3
Кукуруза	0,6
Просо	82,9
Гречиха	44,1
Зернобобовые культуры	4,2

 

4.     Грузооборот по Оренбургской области представлен объемом погрузки по основным видам грузов.  Данные за 1999 г. приведены в таблице 4.

Таблица 4

Наименования видов грузов	Объем погрузки, тыс.тонн
Кокс	126,0
Нефть и нефтепродукты	4895,0
Черные металлы	2097,0
Лесные грузы	26,0
Химические и минеральные удобрения	29,0
Зерно и продукты перемола	919,0
Цемент	239,0
Лом черных металлов	187,0
Комбикорма	25,0
Строительные грузы	5553,0

 

5.     Распределение субъектов хозяйственной деятельности по формам собственности в 2000 г. по Оренбургской области представлено в таблице 5.

Таблица 5

Субъекты хозяйственной деятельности по формам собственности	Объем субъектов, единиц
Государственная	3113
Муниципальная	2790
Общественных организаций	3490
Частная	21983
Смешанная российская	1951
Иностранных государств	8
Иностранных юридических лиц, граждан и лиц без гражданства	89
Смешанная с совместным российским и иностранным участием	150

 

6.     Распределение населения Оренбургской области по размеру среднедушевого денежного дохода представлено данными таблицы 6.

Таблица 6

Население со среднедушевыми денежными доходами в месяц, рублей	в % к общей численности населения
До 400,0	4,6
400,0-600,0	15,5
600,0-800,0	20,5
800,0-1000,0	18,4
1000,0-1200,0	13,8
1200,0-1600,0	15,8
1600,0-2000,0	6,7
Свыше 2000,0	4,7

 

7.     Данные о составе граждан, привлеченных на работу в Оренбургскую область в 1999 г. представлены в таблице 7.

Таблица 7

Государства-участники СНГ и другие страны	Численность иностранных граждан, человек
Азербайджан	10
Армения	40
Грузия	30
Казахстан	4
Кыргызстан	47
Таджикистан	106
Узбекистан	65
Украина	1
Страны бывшей Югославии	152
Болгария	21
Венгрия	57
Вьетнам	143
Германия	3
Индия	4
Китай	9
Словакия	482
Турция	48

 

8.     Данные  распределения домашних хозяйств по размеру занимаемого жилья по России в 1998 г. представлены в таблице 8.

Таблица 8

Размер общей площади в среднем на проживающего, кв.м/чел.	Распределение домашних хозяйств, % к общему количеству
до 9,0	5,8
9,1 – 11,0	7,8
11,1 – 13,0	10,0
13,1 – 15,0	11,7
15,1 – 20,0	21,7
20,1 – 25,0	14,5
25,1 – 30,0	9,1
30,1 – 40,0	9,8
40,1 и более	9,8

 

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

     2.4. Контрольные вопросы

1.     В чем заключается суть принципа Парето.

2.     Порядок построения диаграммы Парето.

3.     Привести собственный пример на применение принципа и диаграммы Парето.

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

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

 

 

 

 

возврат