Дисциплина : «Экономическая информатика» Лекция 8. Аналитическая работа с данными в табличном процессоре Excel РОССИЙСКАЯ АКАДЕМИЯ НАРОДНОГО ХОЗЯЙСТВА И ГОСУДАРСТВЕННОЙ СЛУЖБЫ Доктор технических наук, профессор Павлов Алексей Николаевич

Тема 4. 1.Какие сервисные программы предлагают разработчики табличного процессора Excel для решения типовых задач? 2.Из каких этапов состоит процесс подготовки и решения задач в Excel? 3.В чем заключается анализ данных при решении задачи по заданной модели? 4.Что такое оптимальное решение? 5.Как можно воспользоваться надстройками Excel, чтобы выбрать необходимый метод решения задачи?

Возможности табличного процессора Excel для решения задач Разработчики Excel предлагают несколько возможных способов решения той или иной задачи, которые наиболее часто встречаются в жизни управленцев при проведении анализа данных. Методы, заложенные в решение задач на Excel получили название «Что – если?». При попытке ответить на такой вопрос, потребовалось инструменты анализа разделить на три группы: Подбор параметров – методы позволяют найти значение функции, которое требуется ввести в формулу, описывающую зависимость связанных между собой величин, для получения нужного результата. Таблицы подстановок дают возможность вычисления, просмотра и сравнения результатов, полученных при различных вариантов значения аргумента. Поиск решения осуществляет вычисление оптимального значения целевой функции.

Надстройка - Подбор параметров Исследователь часто задает вопрос – Что надо сделать для того, чтобы получить нужный результат? Получить ответ позволяет средство Excel (программа, которая вызывается командой) Подбор параметров. Условиями применения данного средства являются: 1.Известен требуемый ответ; 2.Создана формула для вычисления этого ответа; 3.Существует одно входное значение для получения этого ответа. Применять средство Подбор параметров удобно для решения математических задач, например, для нахождения корней уравнений, решения систем уравнений, при решении финансовых задач, например, при оценке условий получения кредита, при решении экономических задач, например при вычислении оптимальных запасов на складе.

Решение экономической задачи на основе средства – Подбор параметров Задача – необходимо определить оптимальный размер партии деталей, которые запускаются в производство при условии, чтобы суммарные затраты на обработку деталей, хранения их на складе, были минимальными. Характерной особенностью задач такого рода является то, что величина денежных затрат на конечную цену изделия зависит от количества деталей, которые находятся в производстве и на складе. На экономические показатели влияют несколько факторов (затраты на хранение и затраты на производство). Склад хранит детали и обеспечивает производство, чем больше деталей на складе, тем выше затраты на их хранение Производство использует детали, которые целесообразно подавать партиями, чем больше партия деталей, тем меньше затрат на переналадку оборудования ? Как найти оптимальное соотношение.

Постановка задачи Обозначим переменной Х – количество деталей в партии, которая подается на производство. Затраты на хранение запасов деталей включает: налог на имущество, затраты на обслуживание склада, заработную плату сотрудников. Как правило, эта величина постоянная, рассчитывается на единицу хранения (деталь). Обозначим эту величину q [руб,шт.]. Следовательно, текущие затраты будут составлять: Zq = ½qx (1/2 – обозначает, что на складе должен быть страховой задел для запуска новой партии деталей в производство) Затраты на производство изделий с полученными деталями складывается из расходов на подготовку и переналадку оборудования – S, расходов на сборку изделия – r. Зная (задавшись) суммарным количеством потребности в деталях на весь цикл производства изделий величиной – D, можно составить уравнение для определения производственных затрат: Zp = DS/x +rD

Решение задачи математическим методом Общая величина затрат на хранение и производство равна: Поскольку нас интересует точка, где функция достигает минимума, то для нахождения решения, необходимо провести следующие действия: 1. Найдем первую производную функции Z по X 2. Приравняем нулю первую производную (в этой точке функция Z достигает минимума) 3. Вычислим значение X

Использование средства – Подбор параметра для решения экономических задач Постановка задачи: предприятие имеет возможность заключить договор с потребителем на выпуск определённого вида продукции. Для выполнения договора, потребуется взять кредит в банке для закупки комплектующих деталей. Но! Предприятие больше 1700руб в месяц не может выплачивать банку за кредит. Какие предложения должен сформулировать плановый, чтобы не упустить потенциального заказчика?

Решение задачи – выработка альтернативных вариантов Так как ограничением для предприятия является сумма погашения кредита, не более 1700 руб. в месяц, то можно предложить 2 варианта решения задачи, при условии, что банк не намерен менять условия предоставления кредита. Вариант 1. Уменьшить сумму, которую следует взять в банке для приобретения деталей.

Подбор параметра при использовании стандартной функции ПЛТ (платежи)

Надстройка - Поиск решения Постановка задачи: Необходимо увеличить прибыль предприятия за счет оптимальной загрузки производственных мощностей. Этапы решения поставленной задачи: 1.Провести обследование производственных подразделений. 2.Осуществить анализ возможностей предприятия и выявить ограничения, которые влияют на решение задачи. 3.Рассмотреть альтернативные варианты решения проблемы. 4.Построить модель решения задачи. 5.Провести расчеты. 6.Предложить наиболее подходящий вариант решения проблемы. Решения, приводящие к получению результата, который по тем или иным показателям предпочтительнее других, называются оптимальными.

Схема технологического процесса Поставщик деталей для изделия И1 Детали для изделия И2 Цех подготовки заготовок Цех фрезеровки заготовок Цех сверления заготовок Цех сборки заготовок Участок отладки Производственные подразделения, участвующие в выпуске изделий И1 и И2 Изделие И2Изделие И1 Этап – обследование предприятия Одновременно можно выполнять работы по созданию изделия 1 и изделия 2

Анализ возможных вариантов решения задачи Цех подготовки заготовок (П) Цех фрезеровки деталей (Ф) Цех сверления деталей (С) Цех сборки изделий (Р) Участок отладки (О) Лимит рабочего времени на месяц по участкам (час) Изделие типа И Нормы времени на обработку (час/шт.) Изделие типа И2 24,540,750 Рассмотрим варианты, предположим, что можно изготавливать только один вид изделия либо И1, либо И2 Сколько можно выпустить Реальный выпуск и потери Нет -100% % % 55 -8,3% 55 0% Сколько можно выпустить Реальный выпуск и потери 65 0% 65 -7,1% ,5% ,3% Нет -100% Затраты на производство (Тыс.руб.) Доход от реализации (Тыс.руб.) Прибыль (Тыс.руб.) Изделие И1 0,62,01,4 Изделие И2 1,60,41,2 Вариант 1 – выпускаем только Изделие типа И1, прибыль 1,4*55=77 тыс.руб. Вариант 2 – выпускаем только Изделие типа И2, прибыль 1,2*65=78 тыс.руб.

Формализация задачи ФСРОП Граф технологической схемы выпуска различных видов изделий И2И1 Предположим, что на рабочих местах можно одновременно вести обработку деталей для изделий И1 и И2. Требуется найти такое соотношение загрузки рабочих мест производством изделий И1 и И2, которое позволит получить максимальную прибыль при реализации продукции. Затраты на производство (Тыс.руб.) Доход от реализации (Тыс.руб.) Прибыль (Тыс.руб.) Изделие И1 0,62,01,4 Изделие И2 1,60,41,2 Х1Х1 Х2Х2

Математическая постановка задачи Обозначим через Х 1, Х 2 количество единиц продукции И1 и И2, производство которых планируется. Целевая функция – прибыль, приносимая производством (Х 1 и Х 2 ), будет равна Z = 1,4*X 1 + 1,2*X 2, где С 1 = 1,4 С 2 = 1,2 – прибыль от реализации единицы продукции вида И1 и И2 Введем ограничения для решения задачи: 1. Количество единиц продукции, может быть только положительным числом. X 1 0, X Оборудование не должно работать свыше выделенного лимита времени 0*X 1 + 2*X *X 1 + 4,5*X *X 1 + 4*X *X 1 + 0,75*X *X 1 + 0*X Цех подготовки Цех фрезерования Цех сверления Цех сборки Участок отладки Таким образом, задача сводится к нахождению неотрицательных значениях переменных Х 1 и Х 2, чтобы они удовлетворяли ограничениям – неравенствам, максимизируя линейную функцию этих переменных Z = C i *X i max

Графическое отображение решения оптимизационной задачи X 1 =>0 X 2 => X 1 =>0, X 2 =>0 0*X 1 + 2*X 2

Выполнение работ в Excel при решении оптимизационных задач 1. Установить надстройку Excel – Поиск решения Меню Сервис Надстройки 2. Ввести исходные данные для решаемой задачи 3. Установить зависимые ячейки и ввести формулы для вычислений 4. Вызвать программу Поиск решения и ввести ограничения для модели

Общее представление задачи о перевозках Предположим, что имеется три предприятия-изготовителя продукции, расположенные в разных географических пунктах. Задача предприятия – обеспечить заявки потребителей продукции, при этом, требуется составить такой план перевозок готовой продукции, чтобы суммарная стоимость всех перевозок грузов, была минимальной.

Формирование модели задачи a 1, a 2, a 3 – количество готовой продукции на предприятиях b 1, b 2, b 3, b 4, b 5 – заявки от потребителей на готовую продукцию (количество единиц) Исходные данные: 1) сведения о готовой продукции на предприятиях; 2) заявки от потребителей на количество продукции; 3) стоимость перевозки единицы груза от поставщика к потребителю c ij – стоимость перевозки единицы груза от i -ого поставщика к j -му потребителю

Математическая модель задачи 1. Предположим, что количество заказанной продукции равно сумме произведённой продукции на всех заводах. 2. Обозначим x ij – количество единиц продукции, отправляемой из i-го пункта отправления в j-й пункт назначения (нахождение значений переменных будет представлять решения задачи) 3. Суммарная стоимость всех перевозок должна быть минимальной: min 4. Делаем вывод, что суммарное количество продукции, доставляемой в каждый пункт назначения, не должно быть меньше, указанной в заявке от потребителя. Причём, суммарное количество продукции, направляемой из каждого предприятия во все пункты назначения, не должно превышать запас готовой продукции на этом предприятии.

Реализация транспортной задачи в Excel Сформируем две таблицы в Excel. В первой таблице будут отражены результаты решения задачи, а во второй запишем исходные данные. Диапазон ячеек C9:G11 содержат матрицу стоимости перевозок. В ячейки диапазона C7:G7 записаны количество продукции, заявленные в городах. В диапазоне ячеек B9:B11 указано количество готовой продукции на заводах. В ячейки B3:B5 потребуется ввести уравнения ограничений для поставщиков продукции, а в ячейках C6:G6 должны быть введены уравнения ограничений для потребителей. В ячейке B12 должна содержаться формула для вычисления целевой функции L.

Подготовка формул для решения транспортной задачи Формула ЯчейкаМатематическая записьЗапись в ячейке Excel B3X 11 + X 12 + X 13 +X 14 + X 15 a 1 =СУММ(C3:G3) B4X 21 + X 22 + X 23 +X 24 + X 25 a 2 =СУММ(C4:G4) B5X 31 + X 32 + X 33 +X 34 + X 35 a 3 =СУММ(C5:G5) C6X 11 + X 21 + X 31 >= b 1 =СУММ(C3:C5) D6X 12 + X 22 + X 32 >= b 2 =СУММ(D3:D5) E6X 13 + X 23 + X 33 >= b 3 =СУММ(E3:E5) F6X 14 + X 24 + X 34 >= b 4 =СУММ(F3:F5) G6X 15 + X 25 + X 35 >= b 5 =СУММ(G3:G5) C12C i1 *X 1j =C3*C9+C4*C10+C5*C11 D12C i2 *X 2j =D3*D9+D4*D10+D5*D11 E12C i3 *X 3j =E3*E9+E4*E10+E5*E11 F12C i4 *X 4j =F3*F9+F4*F10+F5*F11 G12C i5 *X 5j =G3*G9+G4*G10+G5*G11 B12C ij *X ij =СУММ(C12:G12)

Решение задачи с помощью надстройки Excel – Поиск решения 1. В созданные таблицы Excel внести формулы из столбца таблицы. 2. Ввести произвольные значения в ячейки C3:G5 (Лучше всего заполнить матрицу единицами). 3. Вызвать диалоговое окно Поиск решения (Для Excel 2003 – Меню Сервис Поиск решения; для Excel 2007/2010 – Меню Данные Поиск решения). 4. Определить перечень изменяемых ячеек 5. Обязательно сделать отметку, что задача решается на минимум 6. Добавить ограничения 7. Установить параметры поиска решения 8. Запустить решение задачи

Результат поиска оптимального решения Максимальное количество товара, которое можно доставить от завода (i=1) к потребителю (j=5) с минимальными затратами Суммарные затраты на доставку товара потребителям Пример решения

Общее представление задачи о перевозках Появляются новые задачи, которые требуют нахождения оптимального решения. 1. Как уложить изделия в транспорт, чтобы сократить расходы на перевозку? 2. Как выбрать оптимальный маршрут объезда потребителей одним видом транспорта?