Алгоритм создания компьютерной модели решения задачи

Лабораторная работа N 2

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

Цель работы: освоить технологию расчета и построения диаграммы Парето в среде 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% результата.
4. Для построения диаграммы Парето выделим данные первого, второго и четвертого столбцов (для выборочного выделения используют клавишу “Ctrl”), не выделяя сумму. В режиме “Мастера диаграмм ” (или “Вставка-Диаграмма) выбрать тип диаграммы (Нестандартные-График гистограмма 2), позволяющей отобразить трехосевую диаграмму
5. Проанализируем диаграмму. Проведем горизонтальную прямую, соответствующую 80% вкладов факторов до пересечения с графиком вкладов (в общем случае анализ проводится для любого числа процентов). Слева от точки пересечения размещены факторы, обеспечивающие 80% результата. На реальных данных можно проверить справедливость принципа Парето.

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