Структура производства

Откройте файл «к лабораторной работе №9». На листе Структура производства представлена модель предприятия, выпускающего телевизоры, стерео- и акустические системы и использующее общий склад комплектующих. В решаемых здесь задачах необходимо найти оптимальное соотношение объёмов выпуска изделий с учётом ограниченности запаса комплектующих. Следует также учитывать уменьшение удельной прибыли при увеличении объёмов производства в связи с дополнительными затратами на сбыт.

Задача максимизации прибыли (ячейка D18) решается путём определения структуры выпуска продукции (т.е. изменяя значения в диапазоне ячеек D9:F9) с учётом запаса комплектующих (значения в диапазоне С11:С15 не должны превышать соответствующие значения из диапазона В11:В15). Для решения этой задачи необходимо выполнить следующие действия.

4.1. В окне Поиск решения щёлкните по кнопке Восстановить.

4.2. В поле Установить целевую ячейку введите D18, щёлкнув левой кнопкой мыши по соответствующей ячейке.

4.3. Выберите переключатель максимальному значению,а в поле Изменяя ячейки и выделите диапазон D9:F9. Нажмите кнопку Добавить.

4.4. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку выделите диапазон С11:С15, а в поле Ограничение выделите диапазон В11:В15, щёлкните по кнопке Добавить.

4.5. В поле Ссылка на ячейку введите диапазон D9:F9, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите 0. Нажмите кнопку ОК.

4.6. В окне Поиск решения нажмите кнопку Параметры и удалите (если он есть) флажок Линейная модель. Нажмите ОК.

4.7. Нажмите кнопку Выполнить.

4.8. Создайте отчёт «Результаты» по результатам поиска решения. Для этого, в окне Результаты поиска решения в списке Тип отчёта выберите Результаты, затем нажмите ОК.

Внимание! Отчёт будет создан на новом листе.

5. График занятости.На одноимённом листе представлена модель, в которой требуется составить график занятости персонала, обеспечивающего потребности в работниках при минимальных затратах на оплату труда. В данном примере ставки одинаковы, поэтому снижение числа ежедневно занятых сотрудников приводит к уменьшению затрат на оплату труда при пятидневной рабочей неделе с двумя выходными подряд.

Решение задачи минимизации расходов на оплату труда (ячейка D20) путём определения числа работников в группах с разными графиками работы (диапазон D7: D 13), при этом необходимо учитывать, что число работников в группе не может быть отрицательным и дробным числом, а число ежедневно занятых работников (диапазон F15:L15) не должно быть меньше ежедневной потребности (диапазон F17:L17). Выбор линейной модели ускорит получение результата

5.1. В окне Поиск решения щёлкните по кнопке Восстановить.

5.2. В поле Установить целевую ячейку введите D20, щёлкнув левой кнопкой мыши по соответствующей ячейке.

5.3. Выберите переключатель минимальному значению,а в поле Изменяя ячейки выделите диапазон D7: D 13. Нажмите кнопку Добавить.

5.4. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку выделите диапазон D7: D 13, в раскрывающемся списке выберите цел., щёлкните по кнопке Добавить.

5.5. В поле Ссылка на ячейку введите D7: D13, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите 0. Нажмите кнопку Добавить.

5.6. В поле Ссылка на ячейку введите F15:L15, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите F17:L17. Нажмите кнопку ОК.

5.7. В окне Поиск решения нажмите кнопку Параметры и установите флажок Линейная модель. Нажмите ОК.

5.8. Нажмите кнопку Выполнить.

5.9. Создайте отчёт «Устойчивость» по результатам поиска решения. Для этого, в окне Результаты поиска решения в списке Тип отчёта выберите Устойчивость, затем нажмите ОК.

6. Управление капиталом.В данном примере представлена модель, в которой требуется с наибольшей доходностью разместить дополнительные средства в 1-, 2- и 6-месячных депозитах, учитывая собственные потребности в средствах. Одной из задач является управление средствами и краткосрочными вложениями с максимальной прибылью при сохранении достаточного резерва для покрытия расходов. Более доходными могут оказаться долгосрочные депозиты. Однако краткосрочные депозиты представляют более гибкие возможности управления финансовыми средствами.

В данной модели конечная сумма рассчитывается исходя из начальной суммы (прошлого месяца), плюс погашаемые депозиты, минус новые депозиты и с учётом ежемесячных потребностей самого предприятия.

Решим задачу максимизации дохода по процентам (ячейка Н8) путём определения девяти сумм:

Ø Ежемесячные суммы для 1-месячных депозитов (ячейки В14:G14);

Ø Суммы квартальных депозитов в 1- и 4-м месяцах (ячейки В15:Е15);

Ø Сумму шестимесячного депозита в 1-м месяце (ячейка В16).

Максимум дохода необходимо найти при условии выполнения ограничений: сумма каждого депозита (перечисленные выше ячейки) не может быть отрицательной, конечные суммы по каждому месяцу (диапазон ячеек В18:Н18) не должны быть меньше 100 тыс. руб. Для решения этой задачи выполним следующие действия.

6.1. В окне Поиск решения щёлкните по кнопке Восстановить.

6.2. В поле Установить целевую ячейку введите Н8, щёлкнув левой кнопкой мыши по соответствующей ячейке.

6.3. Выберите переключатель максимальному значению,а в поле Изменяя ячейки и выделите диапазон В14:G14, затем, удерживая нажатой клавишу Ctrl, последовательно выделите ячейки В15, Е15 и В16. Нажмите кнопку Добавить.

6.4. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку выделите диапазон В14:G14, в раскрывающемся списке выберите >=, в поле Ограничение введите 0, щёлкните по кнопке Добавить.

6.5. В поле Ссылка на ячейку введите В15:В16, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите 0. Нажмите кнопку Добавить.

6.6. В поле Ссылка на ячейку введите Е15, в раскрывающемся списке выберите элемент >=, а в поле Ограничение введите 0. Нажмите кнопку ОК.

6.7. В поле Ссылка на ячейку выделите диапазон В18:Н18, в раскрывающемся списке выберите >=, а в поле Ограничение введите 100000 щёлкните по кнопке Добавить.

6.8. В окне Поиск решения нажмите кнопку Параметры и установите флажок Линейная модель. Нажмите ОК.

6.9. Нажмите кнопку Выполнить.

6.10. Создайте отчёт «Пределы» по результатам поиска решения. Для этого, в окне Результаты поиска решения в списке Тип отчёта выберите Пределы, затем нажмите ОК.

 

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

Решим задачу максимизации прибыли по акциям (ячейка Е18) путём определения доли каждой акции (диапазон Е10:Е14), при этом должны выполняться ограничения: доли акций не должны быть отрицательными числами и их сумма (ячейка Е16) должна равняться 1, а суммарное изменение акций (ячейка G18) не должна превышать 7,1%. Для решения этой задачи необходимо выполнить следующие действия.

7.1. В окне Поиск решения щёлкните по кнопке Восстановить.

7.2. В поле Установить целевую ячейку введите Е18, щёлкнув левой кнопкой мыши по соответствующей ячейке.

7.3. Выберите переключатель максимальному значению,а в поле Изменяя ячейки и выделите диапазон Е10:Е14. Нажмите кнопку Добавить.

7.4. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку выделите диапазон Е10:Е14, в раскрывающемся списке выберите >=, в поле Ограничение введите 0, щёлкните по кнопке Добавить.

7.5. В поле Ссылка на ячейку введите Е16, а в поле Ограничение введите 0,071. Нажмите кнопку ОК.

7.6. В окне Поиск решения нажмите кнопку Параметры и снимите (если он установлен) флажок Линейная модель. Нажмите ОК.

7.7. Нажмите кнопку Выполнить, затем ОК.

8. Самостоятельная работа.На листе «Транспортная задача» представлена модель, в которой требуется минимизировать затраты на перевозку товаров от производителей на торговые склады. При этом необходимо учесть возможности поставок каждого из производителей при максимальном удовлетворении запросов потребителей.

Решать задачу минимизации общих транспортных расходов (ячейка В20) следует путём определения структуры перевозок (т.е. изменяя объёмы перевозок от каждого из заводов к каждому складу – значения в диапазоне С8:G10), при этом количество перевезённых грузов (диапазон В8:В10) не может превышать производственных возможностей заводов (диапазон В16:В18), и количество доставляемых грузов (диапазон С12:G12) не должно быть меньше потребностей складов (диапазон С14:G14). Кроме того, должно выполняться «техническое» требование – значение объемов перевозок (диапазон С8: G10) не могут быть отрицательными числами. Создайте отчёт по результатам поиска решения.

9. Прогноз и отображение линий тренда на диаграмма. Тренд (или тенденция) - направленное движение цены в том или ином направлении. Основная линия тренда это один из наиболее простых технических инструментов, используемых в графическом анализе. Линии тренда позволяют графически отображать тенденции данных и прогнозировать их дальнейшие изменения. Подобный анализ называется также регрессионным анализом. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений.

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

9.1. Необходимо проследить изменение курса валют в течение второго полугодия 2009 года. Для этого подготовьте таблицу в соответствии с рисунком 8.

	А	В	С
	месяц	евро	доллар
	июль	44,88	31,84
	август	45,08	31,49
	сентябрь	44,82	30,96
	октябрь	43,70	29,50
	ноябрь	43,62	29,14
	декабрь	43,80	29,90

Рис. 8. Таблица изменения стоимости валют

9.2. Постройте график по данным таблицы.

9.3. Измените вертикальный формат оси:

- минимальное значение: фиксированное 20,0

- максимальное значение: фиксированное 50,0

- цена основных делений: фиксированное 1,0

- цена промежуточных делений: фиксированное 0,5

9.4.Добавьте на график линию тренда. Для этого перейдите на вкладку Макет, нажмите кнопку Анализ и выберите Линия тренда.

9.5.В открывшемся окне выберите Линейное приближение, а в следующем окне выберите евро.

9.6. Аналогично постройте линию тренда для доллара, выбрав Линейный прогноз.

9.7.Щёлкните правой кнопкой мыши по линии тренда евро и в левой части открывшегося окна Формат линии тренда в разделе Прогноз в окошке вперёд на: периодов впишите 6,0. отметьте галочкой строку поместить на диаграмму величину достоверности аппроксимации (R^2).

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

 

Возможные проблемы при работе со средствами «Поиск решения»

Проблемы до начала работы со средством «Поиск решения»

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

Активный документ не является рабочим листом или защищен. Это сообщение появляется тогда, когда текущий рабочий лист защищен от изменений. Решение проблемы — снимите защиту с листа.

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

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

Сообщения об ошибках диалогового окна «Поиск решения»

Неправильный ввод данных в диалоговом окне Поиск решения может инициировать такие сообщения об ошибках:

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

Целевая ячейка должна быть единственной и располагаться на активном листе. Это сообщение появляется в случае, если в диалоговом окне Поиск решения вы щелкнули на кнопке Выполнить, но в поле Установить целевую ячейку содержится ссылка на диапазон ячеек, а не на одну ячейку, или это ссылка на ячейку, которая находится на листе, отличном от текущего. Решение проблемы — введите правильный адрес в поле Установить целевую ячейку.

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

Смежные ячейки должны находиться на активном листе. Это сообщение появляется тогда, когда в поле Изменяя ячейки имеется ссылка на ячейки рабочего листа, отличного от текущего листа. Решение проблемы — правильно введите адреса изменяемых ячеек.

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

Отсутствует или неправильно содержимое поля ссылки на ячейку. Сообщение появляется в случае, если в диалоговом окне Добавление ограничения (или в окне Изменение ограничения) вы щелкнули на кнопке ОК, но в поле Ссылка на ячейку отсутствует адрес ячейки или диапазона. Решение проблемы — измените содержимое поля Ссылка на ячейку или щелкните на кнопке Отменить, а затем снова откройте это диалоговое окно.

Ограничение должно быть числом, простой ссылкой или формулой с числовыми значениями. Это сообщение появляется, если в диалоговом окне Добавление ограничения (или в окне Изменение ограничения) вы щелкнули на кнопке ОК, но поле Ограничение пусто, содержит текст или адрес ячейки, которая содержит нечисловое значение. Исправление ошибки — измените содержимое поля Ограничение или щелкните на кнопке Отменить, а затем снова откройте это диалоговое окно.

Ссылка на целое ограничение должна содержать только смежные ячейки. Это сообщение появляется тогда, когда в диалоговом окне Добавление ограничения (или в окне Изменение ограничения) вы щелкнули на кнопке ОК, задав перед этим ограничение целочисленности (т.е. выбрав в раскрывающемся списке элемент цел) для содержимого ячеек, которые указаны в поле Ссылка на ячейку и которые и диалоговом окне Поиск решения не заданы как изменяемые ячейки. Решение проблемы — либо измените адреса изменяемых ячеек, либо измените адреса в поле Ссылка на ячейку, либо удалите ограничение целочисленности.

Сообщения об ошибках диалогового окна «Параметры поиска решения»

Средство не может найти подходящего решения. Средство Поиск решения не смогло найти набор значений изменяемых ячеек, которые удовлетворяли бы всем ограничениям в пределах точности, задаваемой параметром Относительное отклонение диалогового окна Параметры поиска решения. Это сообщение обычно появляется тогда, когда ограничения модели противоречивы. Причиной появления этого сообщения для нелинейных моделей может быть то, что процедура поиска не может найти допустимое решение вблизи начальных значе­ний изменяемых ячеек. В этом случае нужно ввести другие значения в изменяемые ячейки и повторно запустить Поиск решения.

При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения. При пересчете формул модели в ячейках рабочего листа обнаружены ошибки, такие как #ДЕЛ/0! или #ЧИСЛО!. В этих условиях Поиск решения останавливает свою рабо­ту. Решение проблемы — найдите ячейку, содержащую ошибку, и исправьте формулу. Можно также добавить ограничения, исключающие значения, при которых возникают ошибки.

Значения целевой ячейки не сходятся. Это сообщение означает, что значения целевой функции неограниченно возрастают (или убыва­ют), даже если все ограничения выполняются. Наиболее вероятной причиной появления такого сообщения являются пропущенные огра­ничения. Решение проблемы — проверьте, не пропустили ли вы ка­кое-либо ограничение и нет ли других логических ошибок, которые делают «неработающими» некоторые ограничения. Для нелинейной модели, возможно, была неправильно определена формула для вы­числения целевой функции.

Условия для линейной модели не выполняются. Это сообщение появ­ляется в том случае, если в диалоговом окне Параметры поиска решения установлен флажок Линейная модель, но расчеты, выполняемые сред­ством Поиск решения, не согласуются с линейной моделью. Решение проблемы — либо пересмотрите формулировку модели, чтобы опреде­лить и устранить источник нелинейности, либо примите нелинейную формулировку модели и откажитесь от линейной оптимизации.

Мало памяти для решения задачи. Это сообщение появляется, ког­да Поиск решения определяет, что для оптимизации модели недоста­точно памяти. Часто это сообщение появляется в тех случаях, когда в Excel открыто слишком много рабочих книг или в дополнение к Excel открыто много других приложений. Решение проблемы — попробуйте закрыть рабочие книги и приложения, не имеющие отношения к решаемой задаче.

Поиск остановлен (истекло заданное на поиск время) и Поиск решения остановлен (достигнуто максимальное число итераций). Эти со­общения появляются после того, как в диалоговом окне Текущее состояние поиска решения вы щелкнули на кнопке Стоп после остановки средства Поиск решения вследствие окончания време­ни, отведенного на процесс оптимизации, или выполнения макси­мального количества итераций (параметры Максимальное время и Предельное число итераций в диалоговом окне Параметры поиска решения), но достичь оптимального решения не удалось. Решение проблемы — можно увеличить значение указанных параметров, но сначала необходимо исследовать текущие решения и найти при­чину длительного процесса вычислений, быть может, это большой разброс возможных значений переменных решения (плохо мас­штабированная модель).