Top-office11.ru

IT и мир ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Инструмент «Поиск решения» в Excel

Инструмент «Поиск решения» в Excel

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

1.Формализация задачи

  1. Для определения каких величин строится модель?
  2. В чем состоит цель, для достижения которой из множества всех допустимых значений переменных выбираются оптимальные?
  3. Каким ограничениям должны удовлетворять неизвестные?

Суммарная прибыль от производства столов и шкафов равна z = 6*x1 + 8*x2. Целью фабрики является определение среди всех допустимых значений х1 и х2 таких, которые максимизируют суммарную прибыль, т.е. целевую функцию z.

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

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

Кроме того, ограничение на трудоемкость не превышает количества затрачиваемых ресурсов

Таким образом, математическая модель данной задачи имеет следующий вид:

при следующих ограничениях:

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

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

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

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

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

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

 excel поиск решения примерыКак его установить

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

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

Параметры задачи в функции Поиска решений

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

Читать еще:  Дергается сам по себе курсор мыши в Windows 10

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

Средство Excel «Поиск решения»

Решение задач прикладной информатики в менеджменте.


Практическое занятие 5.


Средство Excel «Поиск решения»

Цель работы:
изучение постановки задачи оптимизации и средства «Поиск решение»

Задачи оптимизации параметров объекта исследования

Оптимизационные модели служат для поиска наилучших, в определенном смысле, вариантов. В этом случае среди параметров модели выделяют один или несколько, доступных нашему влиянию – независимые переменные или управляемые параметры X . Среди выходных характеристик Y выделяют такую, которая позволяет оценить качество объекта – критерий оптимальности Q k .

С учетом введенных обозначений задача оптимизации формализуется следующим образом:

Q * k = extr Q k
X
Y j ( х )<= Z j max

В зависимости от особенностей реального объекта, характера зависимости критерия оптимальности от независимых переменных и целей исследования различают задачи:

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

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

линейной (при линейной зависимости критерия качества от параметров) и нелинейной оптимизации;

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

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

Существуют многочисленные программы, предназначенные для решения оптимизационных задач. Одной из таких программ является средство Excel «Поиск решения».

Средство Excel «поиск решения»

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

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

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

Рисунок 1 Список подключенных настроек

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

Для выполнения операции Поиск решения нужно выполнить команду Сервис – Поиск решения и в диалоговом окне Поиск решения задать параметры решения:

адрес целевой ячейки, в которой будет подбираться значение;

критерий оптимальности (максимальное или минимальное значение) или значение, которое следует найти;

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

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

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

Кнопка «Параметры» позволяет изменить параметры поиска: способ поиска решения, время вычислений, точность определения результатов.

Читать еще:  Как удалить свои видео с YouTube

Рисунок 3 Диалог установки параметров поиска решения

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

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

сохранить найденной решение в исходной таблице;

восстановить исходные значения;

сохранить результаты в виде сценария;

сформировать отчет по результатам выполнения операции.

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

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

В программе Excel-2007 средство «Поиск решения» вызывается пиктограммой из группы Анализ , расположенной на вкладке Данные.

Определение критического объема реализации с помощью поиска решений

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

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

Скопируйте заголовок и первую строку таблицы анализа затрат – доходов:

Объем
реализации
Q к

В столбце «Баланс» введите формулу: Прибыль от реализации — Затраты

С помощью средства «Поиск решения» определите величину Объема реализации, обеспечивающую нулевой баланс.

Указания.
1) Целевая ячейка в нашем случае – ячейка, в которой вычислен баланс; требуется установить в ней нулевое значение путем изменения ячейки с объемом реализации.

2) Математическая модель рассматриваемой задачи линейна.

3) Задача без ограничений.

Замечание. Иногда требуется проверить, какие ячейки влияют на вычисление значение в другой ячейке. Чтобы наглядно увидеть взаимное влияние ячеек, можно использовать команду меню Сервис – Зависимости формул – Влияющие ячейки (Зависимые ячейки).

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

Запуск инструмента Поиск решения

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

Запуск поиска решений в Microsoft Excel

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

Переход к вводу целевой ячейки в Microsoft Excel

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

Выбор целевой ячейки в Microsoft Excel

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

Установка значения целевой ячейки в Microsoft Excel

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

Установка искомой ячейки в Microsoft Excel

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

Читать еще:  Как подключить один монитор к двум компьютерам

Добавление ограничения в Microsoft Excel

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

Параметры ограничения в Microsoft Excel

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

Установка неотрицательных значений в Microsoft Excel

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

Переход к параметрам поиска решений в Microsoft Excel

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

Параметры Поиска решения в Microsoft Excel

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

Переход к поиску решения в Microsoft Excel

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

Результаты поиска решений в Microsoft Excel

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

Выбор метода решения в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12369 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

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

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Ссылка на основную публикацию
Adblock
detector