Задачи оптимизации (поиск решения)

Задание 1. Составление плана выгодного производства.

Фирма производит несколько видов продукции из одного и того же сырья – А, В и С. Реализация продукции А дает прибыль 10р., В - 15р. и С – 20р. на единицу изделия.

Продукцию можно производить в любых количествах, поскольку известно, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной

1) Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2) Создайте расчетную таблицу как на рис. 4. Введите исходные данные и формулы в электронную таблицу.

3) Расчетные формулы имеют такой вид:

(Расход сырья 1) = (количество сырья 1) * (норма расхода сырья А)+(количество сырья 1)*(норма расхода сырья В) + (количество сырья 1) *(норма расхода сырья С).

Обратите внимание, что значения количества сырья каждого вида пока не известны и будут подобраны в процессе решения задания.

(Общая прибыль по А) = (прибыль на ед.изделий А) * (количество А)

Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С)

4) В меню Сервис активизируйте команду Поиск решения и введите параметры поиска, как указано на рис 5.

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль»(E10), в качестве изменяемых ячеек – ячейки количества сырья (B9:D9)

5) Установите параметры поиска решения. Для этого кнопкой Параметры откройте диалоговое окно Параметры поиска решения, установите параметры Сходимость ® Линейная модель. Кнопкой Выполнить запустите Поиск решения.

6) Сохраните созданный документ под именем «План производства».

 

Связи между файлами. Консолидации данных в MS Excel

Задание 1. Задать связи между файлами.

1) Запустите редактор электронных таблиц Microsoft Excel

2) Создайте 3 электронных книги и назовите их (1 квартал, 2 квартал, полугодие).

3) В каждой книги создайте таблицу «Отчет о продажах» по образцу рис.1.

4) Проведите расчет Прибыли в каждой из книг: Прибыль = Доходы - Расходы.

5) Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал»

Формула для расчета: Доход за полугодие = Доход за 1 квартал + Доход за 2 квартал.

!!!Чтобы вставить в формулу адрес ячейки или диапазона ячеек из другого файла (файла-источника), щелкните мышью по этим ячейкам, при этом расположите окна файлов на экране так, чтобы они не перекрывали друг друга.

Полный адрес ячейки состоит из названия рабочей книги в квадратных скобках, имени листа, восклицательного знака и адреса ячейки на листе.

В ячейке В3 файла «Полугодие» формула для расчета полугодового дохода имеет следующий вид:

='[1 квартал.xls]Лист1'!$B$3+'[2 квартал.xls]Лист1'!$B$3

6)Аналогично рассчитайте полугодовые значения Расходов и Прибыли, используя данные файлов «1 квартал» и «2 квартал». Результаты работы представлены на рис.1. Сохраните текущие результаты расчетов.

Задание 2.

Обновить связи между файлами.

1) Закройте файл «Полугодие» предыдущего задания.

2) Измените значения «Доходы» в файлах первого и второго кварталов, увеличив их на 100 р.:

Доходы 1квартала =334,58 Доходы 2квартала =552,6. Сохраните изменения и закройте файлы.

3) Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи. Для обновления нажмите кнопку ДА. Проследите, как изменились данные файла «Полугодие»

4) Изучим процесс ручного обновления связи.

Вновь откройте файлы первого и второго кварталов и измените исходные данные Доходов, увеличив значения на 100 р. Сохраните изменения и закройте файлы.

5) Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи, нажмите кнопку Нет.

Для ручного обновления связи в меню Правка ® Связи, появится окно. В окне перечислены все файлы, данные из которых используются в активном файле «Полугодие».

6) Расположите его так, чтобы были видны данные файла «Полугодие», выберите Файл «1 квартал» и нажмите кнопку обновить и проследите как изменились данные файла «Полугодие»

Задание 3

Консолидация данных для подведения итогов по таблицам данных исходной структуры.

1) Откройте все три файла задания 2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку B3

2) Выполните команду Данные ® Консолидации. В появившемся окне Консолидация выберите функцию — «Сумма».

3) В строке «Ссылка» сначала выделите в файле «1 квартал» диапазон ячеек В3:В5 и нажмите кнопку Добавить, затем выделите в файле «2 квартал» диапазон ячеек В3:В5 и снова нажмите кнопку Добавить. В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации. Далее нажмите кнопку ОК. произойдет консолидированное суммирование данных за первый и второй кварталы.