word (1)

∙для Рабочего 2 – это Работа 1, стоимость которой равна 2;

∙для Рабочего 3 – это Работа 4, стоимость которой равна 1;

∙для Рабочего 4 – это Работа 2, стоимость которой равна 6. Суммарная стоимость работ будет равна 6 + 2 + 1 +6 = 15.

Условие контрольной задачи

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

Решение задачи оформите в следующем виде (рисунок 2.33):

Рис. 2.33 – Оформление ответа

ЗАДАНИЕ 2.8 Поиск решения. Определение объема производства

1. Создайте рабочую книгу. Переименуйте лист 1 – Объем производства. 2. Решите задачу.

Определите объем производства трех типов изделий, обеспечивающий получение максимальной прибыли, если известно, что объем производства изделий Типа 1 должен быть не менее 10 штук в месяц, а количество изделий Типа 2 должно быть вдвое больше изделий Типа 3. Месячные фонды времени по трем видам работ, которые используются при производстве изделий, соответственно составляют 300, 200, 250 (часов). Прибыль от реализации единицы изделия каждого типа составляет соответственно 50, 100, 110 (руб.). Нормы времени на каждый вид работы в зависимости от типа изделия приведены в таблице 2.5.

3.Переименуйте рабочий лист 2 – Вариант_N, где N – номер варианта контрольной задачи по теме.

4.Решите задачу, используя функцию Поиск решения. Варианты контрольной задачи приведены в таблице 2.6. Последняя цифра номера Вашей рабочей станции соответствует варианту Вашего задания. Условие задачи дано перед таблицей.

5.Сохраните файл с именем Ваша фамилия_Excel_Задание_8 в папке Excel (в личной папке).

МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ВЫПОЛНЕНИЮ ЗАДАНИЯ 2.8

Задание состоит в том, чтобы закрепить полученные навыки использования функции Поиск решения. Для примера предлагается задача определения объема производства изделий, обеспечивающего максимальную прибыль.

1. Подготовьте постановку задачи на рабочем листе (рисунок 2.34).

Рисунок 2.34 – Постановка задачи для определения объема производства изделий

Исходными данными для решения задачи являются:

∙нормы времени – ячейки B3:D5;

∙прибыль на единицу изделия – ячейки Е3:Е5.

В ячейки B6:D6 введите ограничение – месячный фонд времени по видам работ (таб-

лица 2.5).

Другие два ограничения будут введены позже при работе с функцией Поиск решения:

∙объем производства изделия Типа 1 должен быть не менее 10 штук в месяц;

∙объем производства изделия Типа 2 в два раза превышает объем производства изделия Типа 3.

Искомые значения – объем производства изделий обозначьте в ячейках F3:F5 (управляемые переменные).

В ячейках B7:D7 определите промежуточные переменные, то есть введите формулу расчета расхода времени по видам работ:

в ячейку В7: = B3*$F$3+B4*$F$4+B5*$F$5.

Далее тиражируйте формулу вправо:

∙в ячейку С7: = С3*$F$3+С4*$F$4+С5*$F$5;

∙в ячейку D7: = D3*$F$3+D4*$F$4+D5*$F$5.

Объем прибыли от реализации изделий трех типов можно вычислить по формуле:

= E3*F3+E4*F4+E5*F5

или

=СУММПРОИЗВ(E3:E5;F3:F5).

Введите ее в ячейку В8. Это будет целевая ячейка.

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

2. Активизируйте целевую ячейку (В8). Выполните команду: вкладка Данные – Поиск решения.

Вдиалоговом окне Поиск решения (рисунок 2.35) щелкните кнопку Параметры, вы-

берите метод решения: Поиск решения линейных задач симплекс-методом.

Убедитесь в правильности заданной адресации целевой ячейки ($В$8). Установите переключатель в строке Равной: максимальному значению.

Встроку Изменяя ячейки введите диапазон F3:F5 методом выделения (в функции Поиск решения используется абсолютная адресация ячеек).

Вокно Ограничения: последовательно введите ограничения, используя кнопку Доба-

вить.

В процессе постановки задачи Вы ввели значения ограничений в ячейки В6:D6. Сравните промежуточные переменные в ячейках В7:D7 со значениями в диапазоне

B6:D6:

∙$B$7<=$B$6,

∙$С$7<=$C$6,

∙$D$7<=$D$6.

∙ В то же время по условию задачи объем производства изделия Типа 1 (ячейка F3) должен быть не менее 10 штук в месяц: $F$3>=10.

Рисунок 2.35 – Диалоговое окно Поиск решения

Объем производства изделий Типа 2 (ячейка F4) в два раза должен превышать объем производства изделий Типа 3 (ячейка F5). Кроме того, все искомые значения в диапазоне F3:F5 (управляемые переменные) являются целыми числами:

∙$F$4=2*$F$5,

∙$F$3:$F$5=целое.

Завершающее действие – щелкните кнопку Найти решение.

После чего программа осуществит поиск оптимального решения. Критерием валидности постановки задачи является сообщение программы в окне Результаты поиска решения:

«Целочисленное решение найдено в пределах допустимого отклонения. Все ограничения выполнены».

На рисунке 2.36 показан результат работы функции Поиск решения. Объем производства изделий составил:

∙200 – для изделия Типа 1;

∙88 – для изделия Типа 2;

∙44 – для изделия Типа 3.

Расход времени по видам работ составил 300, 152,8 и 244,4 (часов), что не нарушает ограничения по фонду времени. Количество изделий Типа 2 – 88, в два раза больше количества изделий Типа 3 – 44. При заданных условиях получена максимальная прибыль – 23640 рублей.

Рис. 2.36 – Результат работы функции «Поиск решения»

Условие контрольной задачи

Определить дневное количество приготовления бутербродов трех видов, обеспечивающее получение максимальной прибыли. Практика работы показала, что спрос на бутерброды 1 не менее N1 штук в день, а наиболее приемлемое соотношение количества бутербродов 2 и бутербродов 3 – N2/N3.

В табл. 2.6 «Варианты контрольной задачи» приведены значения: ∙ прибыли от реализации одного бутерброда каждого вида (Pi);

∙суточного запаса продуктов (Кi);

∙допустимого количества бутербродов (Ni);

∙норм расхода продуктов, используемых для приготовления одного бутерброда.

132 Работа в приложениях Microsoft Office

Таблица 2.6 – Варианты контрольной задачи

ИСТОЧНИКИ ИНФОРМАЦИИ

1.Алышанский, А.Н. Самоучитель Office 2010. Все программы пакета / П.П. Алышанский, А.Н. Тихомирова, Р.Г. Прокди. – М.: Наука и техника, 2011. – 624 с.

2.Берк, К. Анализ данных с помощью Microsoft Office Excel / К. Берк, П. Кейри. – М.:

Вильямс, 2009. – 560 с.

3.Васильев, А.А. Microsoft Office 2010. Самоучитель / А.А. Васильев, Ю.А. Стоцкий, И.С. Телина. – СПб.: Питер, 2011. – 432 с.

4.Васильев А.Н. Excel 2010 на примерах / А.Н. Васильев. – СПб.: БХВ-Петербург,

2010. – 432 с.

5.Грбачев, А.Г. Microsoft Excel. Работайте с электронными таблицами в 10 раз быстрее / А.Г. Горбачев, Кото П. – М.: ДМК Пресс, 2007. – 96 с.

6.Долженков, В. Самоучитель Excel 2010 / В. Долженков, А. Стученков. – СПб.: БХВ-

Петербург, 2011. – 406 с.

7.Захарченко, Н.И. Бизнес-статистика и прогнозирование в Microsoft Office Excel. Самоучитель / Н.И. Захарченко. – М.: Диалектика, 2008. – 208 с.

8.Киселев, С.В. Оператор ЭВМ. Офисные приложения MS Office / С.В. Киселев. – М.:

Академия, 2011. – 800 с.

9.Курбатова, Е.А. Microsoft Office 2010. Самоучитель / Е.А. Курбатова. – М.: Вильямс, Диалектика, 2010. – 416 с.

10. Сергеев, А.П. Microsoft Office 2010. Самоучитель / А.П. Сергеев. – М.: Вильямс, 2010. – 624 с.

11.Сурядный, А.С. Microsoft Office 2010. Новейший самоучитель / А.С. Сурядный. –

М.: Астрель, 2011. – 640 с.

12.Прокди, Р.Г. Windows 7 + Office 2010 / Р.Г. Прокди. – М.: Наука и техника, 2011. –

560с.

13.Тихомиров, А.Н. Весь Office 2010. 8 книг в 1: Полное руководство / А.Н. Тихомиров, П.В. Колосов, Р.Г. Прокди. – М.: Наука и техника. – 780 с.

14.http://www.3dnews.ru/software/office2010_tech_preview/ – Microsoft Office 2010: об-

зор версий и пакетов.

15.http://office.microsoft.com/ru-ru/ – Официальный сайт Microsoft Office.

16.http://ru.wikipedia.org/wiki/Microsoft_Office_2010/ – Электронная энциклопедия.