Лабораторная работа № 3. Определение вклада фактора в  общий результат на основе диаграммы Парето

 

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

 

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

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

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

Постановка задачи

1. Ознакомиться с назначением и порядком работы с MS Excel для построения диаграммы Парето в соответствии с заданием по варианту.

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

3. На основе диаграммы Парето определить вклады факторов в общий результат.

4. Составить отчет исследований по пунктам 2-3.

5. Ответить на контрольные вопросы для самопроверки.

 

Порядок выполнения работы

1. В среде Excel составить таблицу 3.1  исходных и расчетных данных задачи. Вклады факторов в общий результат во втором столбце необходимо расположить в порядке их убывания с помощью команды «Сортировка - По убыванию». 

2. В первом столбце указать наименование соответствующего фактора. Для построения диаграммы Парето необходимо рассчитать в процентах долю вклада каждого фактора от общей суммы вкладов (столбец 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% результата.

3. Для построения диаграммы Парето выделим данные первого, второго и четвертого столбцов (для выборочного выделения используют клавишу «Ctrl»), не выделяя сумму. 

 

Таблица 3.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

 

4. Для Excel 2003 необходимо в режиме «Мастера диаграмм » (или «Вставка → Диаграмма») выбрать тип диаграммы («Нестандартные → График гистограмма 2»), позволяющей отобразить трехосевую диаграмму (рисунок 3.1).

 

Рисунок 3.1 – Диаграмма Парето

 

Для Excel 2007 необходимо на вкладке «Вставка» выбрать команду «Гистограмма→Гистограмма с группировкой». Выделить ряд «Данные Паретто» и нажатием правой кнопки мыши выбрать команду «Формат ряда данных». В разделе «Параметры ряда» установить пункт «Построить ряд» в значение «По вспомогательной оси». Далее зарываем данное диалоговое окно.

Снова выделите ряд «Данные Паретто» и в контекстном меню выберите пункт «Изменение типа диаграммы». В появившемся окне выберите тип диаграммы «График».

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

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

 

Варианты заданий

Построить диаграмму Парето согласно варианту. Данные для вариантов взяты из Интернета и книги[10.2.1].

1. В таблице 3.2 представлены объемы финансовых потерь от различных видов материальных атак для расчета диаграммы Парето.

 

Таблица 3.2 – Объем потерь от различных видов информационных атак

Порядковый номер	Факторы	Вклад фактора (ден. ед.)
1	Вирусы	15 691 460
2	Неавторизованный доступ к данным	10 617 000
3	Кража ноутбуков и мобильных устройств	6 642 660
4	Кража конфиденциальных данных	6 034 000
5	DOS-атаки	2 922 010
6	Финансовое мошенничество	2 556 900
8	Телекоммуникационное мошенничество	1 849 810
9	Боты внутри организации	1 262 410
10	Проникновение в систему	923 700
12	Проникновение в беспроводную сеть	758 000
13	Неавторизованный доступ к данным	647 510
14	Саботаж	422 500
15	Неправомерное использование web-приложений	269 500
16	Другое	251 310
17	Общая сумма	50 848 770

 

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

 

Таблица 3.3 – Объем произведенной продукции по отраслям промышленности

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

 

3. В таблице 3.4 представлены мировые объемы заражения пользовательских компьютеров в сети Интернет за октябрь 2010 года. Всего было проверено: 84 690 812 733, из них инфицировано: 32 677 797.

 

Таблица 3.4 – Вредоносные файлы, обнаруженные в октябре на компьютерах пользователей сети Интернет

Порядковый номер	Наименование вредоносной программы	Количество инфицированных компьютеров
1	Win32.HLLP.Neshta	8277556
2	Win32.HLLP.Whboy.45	6770512
3	Trojan.DownLoader.42350	4116047
4	ACAD.Pasdoc	1616962
5	JS.Nimda	1334519
6	Exploit.Cpllnk	862594
7	Win32.HLLM.Dref	714726
8	Win32.Virut	596437
9	Trojan.Click.64310	469055
10	Win32.HLLP.Rox	458892
11	Win32.Antidot.1	450340

 

4. В таблице 3.5 представлено количество различных видов дефектов с видеокамерами.

Таблица 3.5 – Виды дефектов цифровых видеокамер

Порядковый номер	Наименование дефекта	Число дефектов
1	Плохое закрепление болтов	320
2	Загрязнение направляющих линз	120
3	Внешние царапины	70
4	Неточное соединение деталей	59
5	Неаккуратная сборка узлов	15
6	Дефекты электрических контактов	8
7	Дефекты комплектации	3
8	Загрязнение внешней поверхности	2
9	Прочие дефекты	3

 

5. Грузооборот по Оренбургской области за 1999 г. представлен в таблице 3.6.

 

Таблица 3.6 – Объем погрузки по основным видам грузов

Порядковый номер	Наименования видов грузов	Объем погрузки, тыс.тонн
1	Кокс	126,0
2	Нефть и нефтепродукты	4895,0
3	Черные металлы	2097,0
4	Лесные грузы	26,0
5	Химические и минеральные удобрения	29,0
6	Зерно и продукты перемола	919,0
7	Цемент	239,0
8	Лом черных металлов	187,0
9	Комбикорма	25,0
10	Строительные грузы	5553,0

 

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

 

Таблица 3.7 – Распределение субъектов хозяйственной деятельности

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

 

7. В таблице 3.8 представлены факторы, нарушающие работоспособность типографских станков.

Таблица 3.8 – Факторы, нарушающие работоспособность типографских станков

Порядковый номер	Наименование фактора	Количество случаев
1	Дефекты печати	38
2	Некачественная бумага	13
3	Ошибки при верстке	12
4	Повреждение упаковки картриджей	7
5	Неправильный выбор типа бумаги	4
6	Проблемы с типографской краской	3
7	Повреждение при перевозке	3
8	Повреждение на конвейере	2
9	Неправильная балансировка печатных станков	1

 

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

 

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

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

 

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

 

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

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

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

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

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

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