Поиск решения в excel 2010 как сделать

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

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

Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». Для версии 2007 года, следует нажать на кнопку Microsoft Office в левом верхнем углу окна. В открывшемся окне, переходим в раздел «Параметры».

В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».

Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».

После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».

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

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

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

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

Целевая и искомая ячейка должны быть связанны друг с другом с помощью формулы. В нашем конкретном случае, формула располагается в целевой ячейке, и имеет следующий вид: «=C10*$G$3», где $G$3 – абсолютный адрес искомой ячейки, а «C10» — общая сумма заработной платы, от которой производится расчет премии работникам предприятия.

После того, как таблица подготовлена, находясь во вкладке «Данные», жмем на кнопку «Поиск решения», которая расположена на ленте в блоке инструментов «Анализ».

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

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

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

Поэтому, ставим переключатель в позицию «Значения», и в поле слева от него прописываем число 30000. Как мы помним, именно это число по условиям составляет общую сумму премии для всех работников предприятия.

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

В поле «В соответствии с ограничениями» можно выставить определенные ограничения для данных, например, сделать значения целыми или неотрицательными. Для этого, жмем на кнопку «Добавить».

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

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

• Дополнительные настройки можно задать, кликнув по кнопке «Параметры».

Здесь можно установить точность ограничения и пределы решения. Когда нужные данные введены, жмите на кнопку «OK». Но, для нашего случая, изменять эти параметры не нужно.

1. После того, как все настройки установлены, жмем на кнопку «Найти решение».

Далее, программа Эксель в ячейках выполняет необходимые расчеты.

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

Независимо от выбранного варианта, установив галочку «Вернутся в диалоговое окно параметров», вы можете опять перейти к настройкам поиска решения. После того, как выставлены галочки и переключатели, жмем на кнопку «OK».

Если по какой-либо причине результаты поиска решений вас не удовлетворяют, или при их подсчете программа выдаёт ошибку, то, в таком случае, возвращаемся, описанным выше способом, в диалоговое окно параметров.

Пересматриваем все введенные данные, так как возможно где-то была допущена ошибка. В случае, если ошибка найдена не была, то переходим к параметру «Выберите метод решения».

Тут предоставляется возможность выбора одного из трех способов расчета: «Поиск решения нелинейных задач методом ОПГ», «Поиск решения линейных задач симплекс-методом», и «Эволюционный поиск решения». По умолчанию, используется первый метод.

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

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

В чем-то данный инструмент напоминает функцию «Подбор параметра…», но в то же время, имеет и существенные различия с ним.

Отблагодарите автора, поделитесь статьей в социальных сетях.

источник

Источник: https://HobbyManiya.ru/faq/gde-poisk-resheniya-v-excel-2010/

Оптимизация доставки

30832 13.11.2012 Скачать пример

Постановка задачи

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

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

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

Подразумевается, что:

• Светло-желтая таблица (C4:G6) описывает стоимость доставки одной единицы товара от каждого склада до каждого магазина.
• Лиловые ячейки (C15:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
• Красные ячейки (J10:J13) отображают емкость каждого склада – предельное количество товара, которое склад вмещает.
• Желтые (C13:G13) и синие (H10:H13) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
• Общая стоимость доставки (J18) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки — для подсчёта здесь используется функция СУММПРОИЗВ (SUMPRODUCT).

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

Решение

В математике подобные задачи выбора оптимального распределения ресурсов сформулированы и описаны уже давно. И, конечно же, давно разработаны способы их решения не тупым перебором (что очень долго), а за весьма небольшое количество итераций. Excel предоставляет пользователю такой функционал с помощью надстройки Поиск решения (Solver) с вкладки Данные (Data):

Если на вкладке Данные вашего Excel такой команды нет – ничего страшного — значит надстройка просто еще не подключена. Для ее активации откройте Файл, далее выберите Параметры – Надстройки – Перейти (Options — Add-Ins — Go To). В открывшемся окне поставьте галочку напротив нужной нам строки Поиск решения (Solver).

Запустим надстройку:

В этом окне нужно задать следующие параметры:

• Оптимизировать целевую функцию (Set target cell) – тут необходимо указать конечную главную цель нашей оптимизации, т.е. розовую ячейку с общей стоимостью доставки (J18). Целевую ячейку можно минимизировать (если это расходы, как в нашем случае), максимизировать (если это, например, прибыль) или попытаться привести к заданному значению (например, вписаться ровно в выделенный бюджет).
• Изменяя ячейки переменных (By changing cells) – здесь укажем зеленые ячейки (C10:G12), варьируя значения которых мы хотим добиться нашего результата – минимальных затрат на доставку.
• В соответствии с ограничениями (Subject to the Constraints) – список ограничений, которые надо учитывать при проведении оптимизации. Для добавления ограничений в список нужно нажать кнопку Добавить (Add) и ввести условие в появившееся окно. В нашем случае, это будет ограничение на спрос: и ограничение на предельный объем складов:

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

Так, например, Excel легко может устроить вам «оптимизацию» стоимости доставки, предложив возить товары из магазинов обратно на склад — расходы при этом станут отрицательными, т.е.

мы получим прибыль! 🙂

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

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

В выпадающем списке Выберите метод решения (Select a solving method) дополнительно требуется подобрать подходящий математический метод для решения на выбор из трех вариантов:

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

Наша задача явно относится к линейным: доставили 1 шт — затратили 40 р., доставили 2 шт — затратили 80 р. и т.д., так что симплекс-метод будет наилучшим выбором.

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

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

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

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

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

Тогда при выполнении оптимизации Excel попытается приблизиться к решению, насколько это возможно, а затем выдаст сообщение о невозможности найти решение.

Тем не менее, даже в этом случае мы имеем массу полезной информации – в частности можем видеть «слабые звенья» наших бизнес-процессов и понять направления совершенствования.

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

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

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

Источник: https://www.planetaexcel.ru/techniques/11/122/

Шаг 186.
Процедура поиска решения

   
На этом шаге мы рассмотрим процедуру поиска решения.

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

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

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

• Значение в целевой ячейке зависит от других значений в ячейках и результатов расчета по формулам. Нужно определить все исходные параметры, при которых значение в целевой ячейке будет максимальным или заранее определенным.
• Целевая ячейка зависит от группы ячеек, которые называются изменяемыми ячейками. Их значения надо подобрать так, чтобы получить желаемый результат в целевой ячейке.
• Решение (значение изменяемых ячеек) должно находиться в определенных пределах или удовлетворять определенным ограничениям.

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

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

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

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

   У компании есть несколько ограничений, которые она должна учитывать:

• Общий объем производства — всего 300 единиц изделий в день.
• Компании нужно произвести 50 единиц изделия А для выполнения существующего заказа.
• Компании нужно произвести 40 единиц изделия В для выполнения планового заказа.
• Поскольку сбыт изделий С относительно небольшой, то должно быть изготовлено не больше 40 единиц этого изделия.

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

1. Введите в рабочий лист исходные данные и формулы (рис. 1).
2. Вызовите диалоговое окно Поиск решения.
3. Укажите целевую ячейку.
4. Укажите изменяемые ячейки.
5. Задайте ограничения.
6. При необходимости измените опции процедуры поиска решения.
7. Позвольте процедуре поиска решения выполнить поставленную задачу.

   Чтобы запуcтить процедуру поиска решения, выберите команду Сервис | Поиск решения.

Примечание. Если в пункте меню Сервис отсутствует опция Поиск решения, нужно выполнить команду Сервис | Надстройки и в открывшемся диалоговом окне Надстройки установить галочку рядом с опцией Поиск решения.

   В результате выполнения указанных действий появится диалоговое окно Поиск решения (рис. 2).

Рис. 2. Диалоговое окно Поиск решения

   В этом примере в целевой ячейке D6 вычисляется общая прибыль по трем видам изделий. В поле Установить целевую ячейку укажите адрес ячейки D6 или щелкните в рабочем листе на этой ячейке.

Поскольку наша цель — максимизировать значение в этой ячейке, установите переключатель Равной максимальному значению.

Затем определите изменяемые ячейки, которые в данном случае находятся в диапазоне В3:В5.

   Дальше введите ограничения задачи. Ограничения добавляются по одному за один раз и отображаются в окне Ограничения. Для добавления ограничений щелкните на кнопке Добавить. Появится диалоговое окно Добавление ограничения (рис. 3).

Рис. 3. Диалоговое окно Добавление ограничения

   В этом диалоговом окне нужно ввести ссылку на ячейку, оператор и значение. Первое ограничение — общий объем продукции должен быть равен 300 единицам изделий.

Введите адрес ячейки B6, выберите оператор равно (=) из раскрывающегося списка операторов и введите в качестве значения число 300.

Для добавления очередного ограничения щелкните на кнопке Добавить. В таблице 1 приведены все ограничения для этой задачи.

Таблица 1.Список ограничений

Ограничения
Сокращенная запись

Объем выпуска 300 единиц	В6 = 300
Не меньше 50 единиц изделия А	B3 >= 50
Не меньше 40 единиц изделия В	B4 >= 40
Не больше 40 единиц изделия С	B5

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

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

Диалоговое окно Результаты поиска решения показано на рис. 4.

Рис.4. Диалоговое окно Результат поиска решения

   После решения задачи Вы можете выбрать одну из следующих возможностей:

• Заменить исходные данные значения в изменяемых ячейках на те, которые были найдены в результате решения задачи.
• Восстановить исходные значения в изменяемых значениях.
• Создать несколько отчетов о процедуре поиска решения (для выбора нескольких отчетов из списка нажмите клавишу Shift и щелкните на нужном типе отчета).
• Щелкнуть на кнопке Сохранить сценарий для сохранения решения в виде сценария, который может быть использован в средстве Диспетчер сценариев.

   Если Вы указали, что нужно создать отчет, он будет помещен на новом рабочем листе, которому будет присвоено соответствующее имя. На рис. 5 показан отчет по результатам поиска решения.

В разделе отчета Ограничения будет указано состояние всех ограничений.

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

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

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

Рис. 6. Диалоговое окно Параметры поиска решения

   Данное окно содержит следующие параметры процедуры поиска решения:

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

•    На следующем шаге мы рассмотрим примеры задач поиска решений.
• Предыдущий шаг
  Содержание
  Следующий шаг

Источник: http://it.kgsu.ru/MSExcel/excel186.html

Поиск решения в Excel 2010. Где находится надстройка Поиск решения Excel

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

В чем значимость пакетов по для кабинета?

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

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

Ну и сам Microsoft Excel 2010 работает намного резвее.

Казалось бы, совершенно еще не так давно секретарши осваивали премудрости MS Office 2007, как состоялся триумфальный релиз Office 2010, который добавил злосчастным мигрени. Но не следует считать, что новенькая версия программки «подкидывает» своим юзерам только только трудности.

Пример тому – «поиск решения» в Excel 2010. Эта надстройка не только лишь полезна, да и помогает сделать вашу работу с табличным редактором куда продуктивнее, позволяя решать намного более сложные задачки. В особенности он комфортен для оптимизации, которая животрепещуща для многих современных компаний.

Почему конкретно 2010 версия?

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

Не считая того, в состав сегодняшней версии этого табличного редактора были введены новые типы формул, которые окажутся более полезными бухгалтерам и экономистам. Это событие опять-таки указывает «реверанс» Microsoft в сторону конкретно корпоративных юзеров. Беря во внимание, что нижеприведенные ситуации свойственны конкретно для их, то ничего необычного в этом нет.

Принципиально! Если вы ранее не применяли «поиск решения» в Excel 2010, то данную надстройку необходимо устанавливать раздельно.

Как его установить?

К счастью, сделать это совсем нетрудно. Если вы используете Ексель 2003 и старше, то для этого нужно зайти в пункт меню «Сервис», а потом избрать там пункт «Надстройки». А где в Excel «поиск решений», если идет речь о более поздних и современных версиях программки?

В случае же внедрения Ексель от 2007 версии кнопку «Поиск решения» можно найти во вкладке «Данные».

Как с этим работать?

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

Итак, как работает «поиск решения» в Excel 2010? Представим, что перед вами стоит задачка – рассредотачивание премии в вашей организации.

Сделать это можно пропорционально распределив размер премиальной выплаты размеру оплаты труда каждого определенного работника.

С чего начинается работа?

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

После чего запускается надстройка «поиск решения» в Excel 2010, после этого в открывшемся окне выставляются требуемые значения.

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

К счастью, кардинальных различий там нет, так что длительно разбираться не придется.

Какие характеристики имеются в диалоговом окне?

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

Во-1-х, это сама мотивированная ячейка. Направьте внимание! В отличие от неких иных операций, которые могут иметь в виду внедрение сходу нескольких полей для вывода данных, в данном случае она может быть только одна!

Во-2-х, непременно имейте в виду, что вариантов оптимизации может быть сходу несколько. Направьте внимание на наибольшее либо мало вероятное итоговое значение, также на определенный итог. Если вам нужен последний вариант, то следует точно указать предпочитаемый итог в поле ввода.

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

Как добавить ограничения?

Если вам требуется добавить какие-то ограничения, то нужно использовать кнопку «Добавить». Учтите, что при задании таких значений необходимо быть максимально внимательным. Потому что в Excel «Поиск решения» (примеры для которого мы рассматриваем) употребляется в довольно ответственных операциях, очень принципиально получать очень правильные значения.

Кстати, конкретно от ограничений эти самые результаты и зависят. Имейте в виду, что задавать их можно не только лишь для каких-либо отдельных ячеек, да и для целых их диапазонов. Какие варианты символов и формул можно при всем этом использовать?

Вами могут применяться знаки: «=», «>=», «

Источник: https://tipsboard.ru/poisk-resheniya-v-excel-2010-gde-naxoditsya-nadstrojka-poisk-resheniya-excel/

"Поиск решения" в Excel 2010. Где находится надстройка "Поиск решения" Excel

Компьютеры 9 марта 2014

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

В чем важность пакетов по для офиса?

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

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

Да и сам Microsoft Excel 2010 работает намного быстрее.

Казалось бы, совсем еще недавно секретарши осваивали премудрости MS Office 2007, как состоялся триумфальный релиз Office 2010, который добавил несчастным головной боли. Но не следует считать, что новая версия программы «подкидывает» своим пользователям только лишь сложности.

Пример тому — «поиск решения» в Excel 2010. Эта надстройка не только полезна, но и помогает сделать вашу работу с табличным редактором куда продуктивнее, позволяя решать намного более сложные задачи. Особенно он удобен для оптимизации, которая актуальна для многих современных компаний.

Почему именно 2010 версия?

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

Кроме того, в состав нынешней версии сего табличного редактора были введены новые типы формул, которые окажутся наиболее полезными бухгалтерам и экономистам. Это обстоятельство опять-таки показывает «реверанс» Microsoft в сторону именно корпоративных пользователей. Учитывая, что нижеприведенные ситуации характерны именно для них, то ничего удивительного в этом нет.

Важно! Если вы до этого не применяли «поиск решения» в Excel 2010, то данную надстройку нужно устанавливать отдельно.

Как его установить?

К счастью, сделать это совершенно несложно. Если вы используете Ексель 2003 и старше, то для этого необходимо зайти в пункт меню «Сервис», а затем выбрать там пункт «Надстройки». А где в Excel «поиск решений», если речь идет о более поздних и современных версиях программы?

В случае же применения Ексель от 2007 версии кнопку «Поиск решения» можно отыскать во вкладке «Данные».

Как с этим работать?

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

Итак, как же работает «поиск решения» в Excel 2010? Предположим, что перед вами стоит задача — распределение премии в вашей организации.

Сделать это можно пропорционально распределив размер премиальной выплаты размеру оплаты труда каждого конкретного работника.

С чего начинается работа?

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

После этого запускается надстройка «поиск решения» в Excel 2010, после чего в открывшемся окне выставляются требуемые значения. Обратите внимание, что в разных версиях офисного пакета внешний вид этих диалоговых окон может сильно различаться, так что вам придется самостоятельно разбираться в ситуации. К счастью, кардинальных отличий там нет, так что долго разбираться не придется.

Какие параметры имеются в диалоговом окне?

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

Во-первых, это сама целевая ячейка. Обратите внимание! В отличие от некоторых прочих операций, которые могут подразумевать использование сразу нескольких полей для вывода данных, в этом случае она может быть только одна!

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

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

Как добавить ограничения?

Если вам требуется добавить какие-то ограничения, то необходимо использовать кнопку «Добавить». Учтите, что при задании таких значений нужно быть предельно внимательным. Так как в Excel «Поиск решения» (примеры для которого мы рассматриваем) используется в достаточно ответственных операциях, чрезвычайно важно получать максимально правильные значения.

Кстати, именно от ограничений эти самые результаты и зависят. Имейте в виду, что задавать их можно не только для каких-то отдельных ячеек, но и для целых их диапазонов. Какие варианты знаков и формул можно при этом использовать?

Вами могут применяться знаки: «=», «>=», «

Источник: http://monateka.com/article/144238/

Программа Поиск решений

Поиск решенийМощным средством анализа данных Excel является надстройка Solver (Поиск решения). С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине).

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

Кроме того, результаты работы программы могут быть оформлены в виде отчета.

Программа ^ (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных та нелинейных задач оптимизации, используется с 1991 года.

Разработчик программы ^ компания FrontlineSystem уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3).

Высокая эффективность их применения объясняется интеграциею программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса.

По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

Процедура поиска решения

1. Создайте таблицу с формулами, которые устанавливают связи между ячейками.2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду:

— В Excel 2007 Данные/Анализ/Поиск решения;

— В Excel 2003 и ниже Сервис > Поиск решения. Поле Установить целевую ячейку открывшегося диалогового окна надстройки ^ будет содержать адрес целевой ячейки.

3. Установите переключатели Равной, задающие значение целевой ячейки, – максимальному значению, минимальному значению или значению. В последнем случае введите значение в поле справа.4. Укажите в поле Изменяя ячейки, в каких ячейках программа должна изменять значения в поисках оптимального результата.5. Создайте ограничения в списке Ограничения. Для этого щелкните на кнопке Добавить и в диалоговом окне Добавление ограничения определите ограничение.6. Щелкните на кнопке Параметры, и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)7. Щелкнув на кнопке Выполнить, запустите процесс поиска решения. 8. Когда появится диалоговое окно Результаты поиска решения, выберите переключатель Сохранить найденное решение или Восстановить исходные значения.9. Щелкните на кнопке ОК. 

^

Максимальное время –служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.

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

Относительная погрешность – определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений.

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

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

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

• Неотрицательные значения – позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых не было задано соответствующее ограничение в диалоговом окне Добавить ограничение.
• Автоматическое масштабирование – используется, когда числа в изменяемых ячейках и в целевой ячейке существенно различаются.
• Показывать результаты итераций – приостанавливает поиск решения для просмотра результатов отдельных итераций.
• Загрузить модель – после щелчка на этой кнопке отрывается одноименное диалоговое окно, в котором можно ввести ссылку на диапазон ячеек, содержащих модель оптимизации.
• Сохранить модель – служит для отображения на экране одноименного диалогового окна, в котором можно ввести ссылку на диапазон ячеек, предназначенный для хранения модели оптимизации.
• Оценка линейная – выберите этот переключатель для работы с линейной моделью.
• Оценка квадратичная – выберите этот переключатель для работы с нелинейной моделью.

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

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

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

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

• по результатам
• по устойчивости
• по пределам

Тип выбирается по окончании поиска решений в диалоговом окне Результаты поиска решений в списке Отчеты. Можно выбрать сразу два или три типа с помощью мыши при нажатой клавиши . Каждый отчет будет создан на отдельном рабочем листе.^ Отчет по результатам содержит информацию о трех компонентах задачи оптимизации: целевой функции (Целевая ячейка), плана (Изменяемые ячейки), и ограничений (Ограничения).

^ – начальное значение целевой функции при начальном опорном плане (3);

2 – максимальное или минимальное значение (в зависимости от задачи)  целевой функции. В нашем случае – 168,57 д. ед.;

^ – начальный опорный план;

4 – оптимальный план задачи. В нашем случае, чтобы получить максимальную выручку в размере 168,37 д. ед., нужно производить 57,14 единиц товара А и 71,43 единиц товара Б (понятно, что товар должен быть в целых единицах, но если бы мы задали такой параметр, то не получили отчеты, которые нужны для анализа и улучшение полученных результатов);

^ – показывает количество использованных ресурсов на производство при оптимальном плане;

6 – формулы ограничений;

7 – показывает влияние ограничений на конечный результат. Если статус «связанное», тогда данное ограничение влияет на полученный план, если «не связан» – значит не влияет. В нашем случае ресурс 1 и 4 имеют статус «не связан» – это значит, что эти ресурсы не ограничивают возможности в производстве, что не скажешь  про ресурс 2 и 3, которые использованы полностью;

^ – разница между имеющимся в наличие количеством ресурсов и использованных при полученном плане.

Отчет по устойчивости

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

^ – оптимальный план задачи. В нашем случае, чтобы получить максимальную выручку в размере 168,37 д. ед., нужно производить 57,14 единиц товара А и 71,43 единиц товара Б;

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

Этот показатель, как изменится оптимальное значение ЦФ при выпуске продукции, которой нету в оптимальном плане.

Например, если нормированная стоимость товара А была бы -3 (хотя в нашем случае это 0), то принудительный выпуск 2 единиц товара А, которых нету в оптимальном плане привел к уменьшению Дохода на 2•3=6 и составлял бы 168,57-6= 162, 57 д. ед.

^ – коэффициенты ЦФ;

4, 5 – границы изменений значений  коэффициентов ЦФ при условии, что количество оптимальной продукции (план) не изменится. Например, если целевой коэффициент  товара А (КА) равен 1,15 (цена за 1 единицу товара), то изменяя его в рамках 1,15-0,43< КА 0,72< КАПоиск решений», после внесений в таблицу изменений данного коэффициента.

^ – количество использованных ресурсов;

7 – теневая цена(в нелинейной модели это множитель Лагранжа) касается ограничений, то есть, определенное значение указывает на «ценность» ограниченного ресурса в сравнении с другими ресурсами.

Этот показатель указывает как изменится оптимальное значение ЦФ (Доход) при изменении запасов ресурсов на 1 единицу.

Например, если увеличить запас ресурса 3 на 10 единиц, то доход увеличится  на 10•0,61=6,1 и будет составлять 168,57+6,1=174,67 д. ед.

^ – запасы ресурсов;

9, 10 – задают диапазон для 8, в котором действует теневая цена 7 (аналогично 4, 5). Например, диапазон ресурса 3:  200Отчет по пределам

Отчет по пределам состоит из двух частей, что касаются значений ЦФ (Целевое) и плана (Изменяемое).

1. ^ – значение ЦФ (Доход);
2. 2 – оптимальный план задачи;
3. 3 – наименьшее значения, которое может принять неизвестное (в нашем случае количество товара А и Б имеет Нижний предел 0, поскольку мы в Параметрах Поиска решений отметили Неотрицательные значения);
4. 6 – это значение, которое будет в целевой ячейке (Доход), если неизвестные будут равны Верхнему пределу.

4 – это значение, которое будет в целевой ячейке (Доход), если неизвестное будит равно Нижнему пределу;5 – это наибольшее значение, которое может содержать неизвестные, чтобы получить максимальную ЦФ;

Источник: http://mir.zavantag.com/matematika/20637/index.html