Решение задачи в программе Excel

Внимание! Здесь дана «формальная заготовка», т.е. клетки, строки и столбцы в приведенном здесь примере имеют названия общего характера без учета специфики задачи. (Это сделано, чтобы Вы не повторяли бездумно пример.) Вы должны прокомментировать все ячейки в точном соответствии со своей личной задачей. И не забудьте дать точное название «упущенной выгоде». Не забудьте и про единицы измерения! Общее пожелание – даже постороннему человеку при первом взгляде на лист Excel должно быть все ясно без дополнительный комментариев автора («ну, я это самое, имел в виду, что типа, ну…» – этого не надо, это несолидно).

Вначале создаем «заготовку» для расчетов:

1) вводим названия пунктов плана-распределения (они послужат и названиями для столбцов таблицы);

2) задаем коэффициенты функции полезности (целевой функции);

3) вводим произвольные (какие сами заходите – лишь бы ячейки не были пустыми) начальные параметры искомого плана или распределения;

заметьте – по математической форме задачи становится ясно, сколько рядов данных требуется для задания функции полезности (здесь – 2. а бывает и больше!);

4) в отдельной ячейке – задаем формулу для вычисления целевой функции (после расчетов в ней будет оптимальное значение полезности);

5) таблица коэффициентов для ограничений (нормы расхода ресурсов по задаче и т.п.), возможно, таблица разделится на части – для каждой группы ограничений;

6) левые части ограничений (в виде формул!!!) – они по способу задания (см. математическую форму задачи) похожи на функцию полезности;

7) правые части ограничений (лимиты ресурсов и т.п. – из задачи);

8) предварительный или действующий план (распределение);

9) полезность по предварительному плану;

10) упущенная выгода (разность между оптимальным и имеющимся (предварительным, действующим и т.п.) значениями полезности) – ее знак не столь важен, важна интерпретация.

Внимание! Если на Вашем компьютере нет пункта меню Сервис/Поиск решения, то установите его – Сервис/Надстройки/√ Поиск решения.

Устанавливаем курсор на ячейку с функцией полезности и через меню Сервис/Поиск решения настраиваем процедуру отыскания оптимального плана:

1) отмечаем ячейку с функцией;

2) отмечаем целевой критерий (если функцию нужно минимизировать, то отмечаем «минимальному значению»);

3) выделяем на листе диапазон переменных;

4) добавляем ограничения (если переменные не обязательно целые, то только неравенства);

заметьте – ограничения можно задавать каждое отдельно, а можно массивами (это по существу задание ограничений в векторном виде);

5) через кнопку «параметры» устанавливаем «Неотрицательные значения»+Ок (это соответствует ограничениям );

6) нажимаем «Выполнить»;

7) в окне «Результаты поиска» (если все правильно – должно быть указано «Решение найдено…»!!!) нажимаем ОК.

Таким образом, получаем решение задачи: параметры плана – объемы выпуска и т.п., целевое значение, упущенная выгода вычислены компьютером. Можно приступать к отчету.

Внимание еще раз! В приведенном здесь примере даны лишь формальные подписи для строк, ячеек и пр. В своей работе выполните подписи ко всем ячейкам с учетом смысла задачи (например, не «упущенная выгода», а «ежедневная потеря прибыли, тыс.руб.»; не «правые части», а «запасы ресурсов» и т.д.), обязательно укажите единицы измерения.

Дата добавления: 2015-08-13