Решение задач оптимизации в excel`е. Оптимизация excel

Прогнозирование и оптимизация в Excel

МОДУЛЬ 1. ПРОГНОЗИРОВАНИЕ ПОКАЗАТЕЛЕЙ

В этом модуле научимся использовать разные способы анализа трендов и учитывать в расчетах коэффициенты сезонности:

элементарное прогнозирование по методу «скользящего среднего»
составление прогнозов с помощью встроенных функций на основе линейных зависимостей
учет влияния одного или нескольких факторов на расчетный показатель
специальные функции для нелинейного прогноза – резкое увеличение или уменьшение показателя (экспоненциальный тренд)
учет коэффициентов сезонности в прогнозах

МОДУЛЬ 2. ИСПОЛЬЗОВАНИЕ БИЗНЕС-СЦЕНАРИЕВ

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

оптимизация количества копий рабочих таблиц при использовании встроенного в Excel Диспетчера сценариев

сравнительный анализ вариантов финансовой модели

МОДУЛЬ 3. ОПТИМИЗАЦИЯ ПОИСКА РЕШЕНИЙ

В настоящем модуле познакомимся с мощнейшими инструментами Excel, которые не просто помогут, но буквально спасут при решении тяжелейших задач многопараметрической оптимизации:

использование функций кредитования ПЛТ и БС (аннуитетный платеж)
подбор параметров кредита
правила построения двухфакторной Таблицы данных
автоматизированное выделение приблизительно-допустимых значений
проверка обоснованности потребности в финансировании расходов торговых и производственных предприятий
оптимизация распределения ресурсов с учетом их эффективности

МОДУЛЬ 4. ОЦЕНКА ИНВЕСТИЦИОННЫХ ПРОЕКТОВ

В этом блоке вопросов подробно разберем перечень и методику расчета показателей для правильной и достоверной оценки эффективности капитальных вложений

использование формул массивов для оптимизации подготовительных расчетов
использование встроенных функций Excel дисконтирования ВСД и ЧПС
оптимизация расчета показателей доходности и сроков окупаемости

www.centrmasterov.ru

Оптимизируем таблицу Excel | ПТИЦА-СЕКРЕТАРЬ

Рассмотрим основные нюансы, делающие работу в редакторе таблиц Excel плодотворной. Конечно, Excel специально создан для работы с таблицами, но таблица таблице рознь.

Часто работают с «одноразовой таблицей», которая создается только для того, чтобы распечатать ее и сохранить для отображения информации в наглядном виде.

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

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

Исходная таблица Excel

Рис.1

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

1. В строках и столбцах таблицы не должно быть объединенных ячеек. Таблица должна состоять из одной верхней строки – ШАПКИ таблицы и некоторого множества строк с данными – ТЕЛА таблицы (см. рис. 2).Это продиктовано тем, что при наличии объединений нескольких ячеек в одну Excel не сможет понять, для каких именно ячеек пользователь применяет действие (например, сортировку). При этом он выдаст сообщение об ошибке.

Образцовая таблица Excel

Рис.2

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

Разделяем данные в таблице Excel

Рис.3

3. Заголовок таблицы должен отделяться от шапки хотя бы одной пустой строкой. См. рис. 4.Если заголовок таблицы разместить в строке, прилегающей к телу таблицы, то при некоторых операциях Excel автоматически поймет заголовок как часть таблицы, а шапку отнесет к остальным данным. Это приведет к ошибкам.

Положение заголовка в таблице Excel

Рис.4

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

Facebook

Twitter

Вконтакте

Google+

machula.ru

Решение задач оптимизации в excel`е

Решение задач оптимизации в EXCEL`е

Введение. Для решения задач поиска корней уравнения или где требуется отыскать оптимальное решение (например, дающее минимальное или максимальное значения), удовлетворяющее при этом целому ряду дополнительных условий на значения используемых параметров. Для решения таких задач, требующих применения методов, нелинейного программирования (управления), является имеющаяся в MS Excel Надстройка "Поиск решения". Задачи линейного программирования возникают, например, при планировании перевозок, распределения производственных ресурсов, оптимизации структуры производства и т.п.

Активация и использование надстроек

Надстройки – это компоненты Microsoft Excel, предоставляющие доступ к дополнительным функциям и командам. Двумя из наиболее популярных надстроек являются "Пакет анализа" и "Поиск решения", которые реализуют функции расширенного анализа данных. Чтобы использовать эти надстройки, их необходимо установить и активировать.

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

Установка и активация надстроек "Пакет анализа" и "Поиск решения"

Откройте вкладку Файл.
Нажмите кнопку Параметры и выберите категорию Надстройки.
Убедитесь, что в нижней части диалогового окна Параметры Excel в поле Управление выбран элемент Надстройки Excel, и нажмите кнопку Переход.

В диалоговом окне Надстройки установите флажки Пакет анализа и Поиск решения и нажмите кнопку ОК (см. рис. 1б).
Если отобразится сообщение о том, что надстройку не удается запустить, и выведется предложение установить ее, нажмите кнопку Да, чтобы установить надстройку.

Рис. 1а. Окно Настройки

Примечание. Поскольку в этом случае устанавливаются две надстройки, приглашение установить надстройку будет показано дважды: первый раз для пакета анализа и второй для надстройки "Поиск решения".

Обратите внимание, что на вкладку Данные добавлена группа Анализ, которая находится справа. Она содержит кнопки команд для надстроек Анализ данных и Поиск решения (см. рис. 1б и 1в).

Рис. 1б. Группа Анализ на вкладке Данные

Теперь можно приступить к работе с этими надстройками.

Рис. 1в. Вкладка "Анализ данных", после установки

надстроек "Анализ данных" и "Поиск решения"

Начало работы с надстройкой "Поиск решения"

На листе введите формулу и выделите ячейку с этой формулой.

На вкладке Данные в группе Анализ нажмите кнопку Поиск решения.
В диалоговом окне Параметры поиска решения выберите вариант Максимум, Минимум или Значение. Если выбран вариант Значение, введите значение в поле.
В поле Изменяя ячейки введите ячейку или диапазон ячеек, для которых необходимо применить поиск решения.
Нажмите кнопку Добавить, чтобы добавить ограничение для ячейки, и введите условие для этой ячейки.
Нажмите кнопку Добавить еще раз, чтобы добавить дополнительные ограничения ячейки (у одной ячейки может быть несколько ограничений), и по завершении нажмите кнопку ОК. Затем нажмите кнопку Решить.

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

Для того, чтобы использовать надстройку "Поиск решения", не обязательно быть знатоком методов линейного и нелинейного программирования. Нужно знать и понимать какие задачи могут быть решены этими методами.

От пользователя требуется умение с помощью серии диалоговых окон правильно сформулировать условия задачи, и если решение существует, то "Поиск решения" отыщет его.

Укажем некоторые возможности надстройки "Поиск решения":

Надстройка "Поиск решения" позволяет использовать одновременно большое количество изменяемых ячеек (в общей сложности до 200 ячеек).

Надстройка "Поиск решения" позволяет задавать ограничения для изменяемых ячеек. Например, при поиске решения, обеспечивающего максимальную прибыль, вы можете задать дополнительные условия (ограничения), например, на общий доход, который находился бы в диапазоне от 20 до 30%, или чтобы расходы не превышали заданной суммы. Подобного рода условия называются ограничениями для решаемой задачи.

Надстройка "Поиск решения" позволяет отыскать оптимальные (минимальные или максимальные) решения, т.е. наилучшие из возможных решений.

Задачи, для решения которых можно воспользоваться надстройкой "Поиск решения", имеют ряд общих свойств:

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

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

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

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

Рассмотрим использование надстройки "Поиск решения" на примере задач.

Задача. Найдём минимум функции y=(x-2)2.

Аналитическое решение. Сразу отметим, что эта задача на минимизацию функции. Находим производную и приравниваем её к нулю. Получим уравнение y'=2(x-2)=0. Решаем это уравнение. Ответ: xopt=2, ymin=0.

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

Рис. 2. Таблица значений и график y=(x-2)2

Можно, например, взять отрезок [1,5; 2.5] и точку из этого отрезка равную 1,5. И заполняем ячейки согласно рис. 3. В ячейку B2 вводим начальное приближение аргумента, в ячейку B3 (целевая ячейка) нашу функцию, т.е. =(B3-2)^2. На рис. 3. мы видим значение этой функции в точке 1,5.

Рис.3. Ввод исходных данных

Выделяем ячейку B3 и набираем команду Данные/Поиск решения. Появится окно Параметры поиска решения (см. рис. 4). В поле Оптимизировать целевую функцию мы видим ссылку на ячейку B3. Программа автоматически установила нужные параметры: Минимум, ячейку, где будет находится решение B2, метод решения (окно Выберите метод решения). Щелкаем по кнопке Найти решение. В ячейке B2 мы видим xopt=2 и минимальное значение функции равное нулю (см. рис 5).

Рис. 4. Установка параметров оптимизации

После нажатия кнопки Найти решение, появится окно Результаты поиска решения (см. рис. 5),

Рис. 5. Окно Результаты поиска решения

и в ячейках B2 и B3 мы видим решение нашей задачи (см. рис. 6).

Рис. 6. Решение задачи

Следовательно, xopt=2, а ymin=0.

Условная оптимизация

Как правило, задачи на оптимум решаются при ограничениях (условная оптимизация).

Задача. Найдём минимум функции y=(x-2)2, при ограничении x≥2,5.

Рис. 7. Таблица данных и график функции при x>=2,5

В ячейку B2 вводим начальное приближение аргумента, например, равно 3 (см. рис. 8).

Рис. 8. Ввод исходных данных

Для ввода ограничений в окне Параметры поиска решения есть кнопка Добавить. Также как и в предыдущем примере Выделяем ячейку B3 и набираем команду Данные/Поиск решения. Появится окно Параметры поиска решения. В поле Оптимизировать целевую функцию мы видим ссылку на ячейку B3. Программа автоматически установила нужные параметры: Минимум, ячейку, где будет находится решение B2, метод решения. Теперь щелкаем мышкой по кнопке Добавить, после чего в открывшемся окне Добавление ограничения в поле Ссылка на ячейку указать ячейку (аргумент), на которую накладывается ограничение. Далее необходимо выбрать тип ограничения из раскрывающегося списка в центре диалогового окна, а затем в поле указать накладываемое ограничение (в данном случае число 2,5) (см. рис. 9).

Рис. 9. На ячейку B2 накладывается ограничение

Для подтверждения выполненных действий следует щелкнуть по кнопке ОК. Щелчок по кнопке Добавить также будет подтверждением, однако при этом диалоговое окно Добавление ограничения не будет закрыто и появится возможность ввести ещё одно ограничение. Так следует поступать в случаях, когда вводимых ограничений несколько.

Система вернёт нас обратно к окну Параметры поиска решения, где в поле В соответствии с ограничением, мы видим введённое ограничение на аргумент (см. рис. 10).

Рис. 10. Поиск решения с ограничением на ячейку B2

В ячейке B2 мы видим xopt=2,5 и минимальное значение функции равное 0,25 (см. рис 11).

Рис. 11. Окно Результаты поиска решения

Рис. 12. Решение

Естественно было ожидать, что минимальное значение будет достигнуто на границе (см. рис. 2).

Многомерная оптимизация

Задача. Найти минимум функции f(x,y)=(x-2)2+(y-3)2.

В ячейку B18 вводим начальное приближение аргумента x0, например x=1, в ячейку B19 вводим начальное приближение аргумента y0, например, y=1, а в ячейку B20 (целевая ячейка) нашу функцию, т.е. =(B18-2)2+(B19-3)2. На рис. 13. мы видим значение этой функции в точке (1,1).

Рис. 13. Ввод исходных данных

Выделяем ячейку B20 и набираем команду Данные/Поиск решения. Появится окно Параметры поиска решения (см. рис. 14). В поле Оптимизировать целевую функцию мы видим ссылку на ячейку B20. Установим нужные параметры: Минимум, ячейки, где будет находится решение B18, B19 метод решения (окно Выберите метод решения).

Рис. 14. Установка параметров оптимизации

Щелкаем по кнопке Найти решение. В ячейках B18 и B19 мы видим решение: xopt=2, yopt=3 и минимальное значение функции равное нулю, которое находится в ячейке B20 (см. рис 15).

Рис. 15. Окно Результаты поиска решения

Условная оптимизация

Задача. Найти минимум функции f(x,y)=(x-2)2+(y-3)2 при ограничении x+y=7. На лекции мы рассматривали этот пример. Эту задачу мы свели задаче обычного минимума, когда из уравнения, задающего ограничение на переменные, выразили одну из переменных и затем подставили это выражение в целевую функцию. Теперь решим данную задачу в Excel.

Как и прежде заносим в таблицу начальные значения для аргументов функции x и y (ячейки B18 и B19), формулу (целевую функцию) в ячейку B20 и формулу =B18+B19 (т.е. x+y) в ячейку B21 (это ограничение без правой части).

Рис. 16. Ввод исходных данных

Далее по аналогии с предыдущими примерами. Выбираем команду Данные/Поиск решения. Вводим параметры оптимизации (см. рис. 17 и 18).

Рис. 17. Установка ограничения x+y=7

Рис. 18. Результат установки ограничения

Рис. 19. Окно Результаты поиска решения

Следовательно, xopt=3, yopt=4, fmin=2 при ограничении x+y=7.

Поделитесь с Вашими друзьями:

psihdocs.ru