Надстройка поиск решения и подбор нескольких параметров Excel. Функция Microsoft Excel: поиск решения Примеры и задачи на поиск решения в Excel

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

Что такое Поиск решений?

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

Если версия Microsoft Office является оригинальной, тогда проблем с установкой не возникнет. Пользователю нужно сделать несколько переходов:

Параметры→Сервис→Надстройки→Управление→Надстройки Excel.

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

Зачем нужен Решатель?

Для чего можно использовать Поиск решений в Excel 2007, и стоит ли вообще его устанавливать? Когда у пользователя присутствует зависящая от нескольких параметров, надстройка будет подбирать решения задачи в соответствии с исходными данными. Таковыми может оказаться переменная, неизвестная или, например, итоговое значение. То есть, пользователь может иметь начальные характеристики и ответ, а программа подберет ход решения, предоставит формулу.

Таким образом, посредством надстройки можно найти:

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

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

Как работает Решатель?

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

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

Найти неизвестные→несколько «x».
При условии, что→формула или функция.
При ограничениях→здесь обычно указывается неравенство, либо минимальные/максимальные значения.

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

Настройка параметров Поиска решений

Чтобы функция Поиска решений в Excel 2007 работала так, как необходимо пользователю, нужно ввести правильные параметры. Обычно они ограничиваются 1-3 характеристиками, но с более сложными задачами потребуется глобальная настройка.

Параметры в Поиске решений программы Office Excel 2007 могут быть следующими:

Максимальное время - количество секунд, которые пользователь выделяет программе на решение. Оно зависит от сложности задачи.
Максимальное число интеграций. Это количество ходов, которые делает программа на пути к решению задачи. Если оно увеличивается, то ответ не будет получен.
Погрешность или точность, чаще всего применяется при решении (к примеру, до 0,0001).
Допустимое отклонение. Используется при работе с процентами.
Неотрицательные значения. Применяется тогда, когда решается функция с двумя правильными ответами (например, +/-X).
Показ результатов интеграций. Такая настройка указывается в случае, если важен не только результат решений, но и их ход.
Способ поиска - выбор оптимизационного алгоритма. Обычно применяется «метод Ньютона».

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

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

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

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

Формула в Excel

Есть обязательный элемент, без которого не сможет функционировать надстройка Поиска решений в программе Excel 2007 - формулы. Они представляют собой такое выражение, которое выполняет то или иное вычисление. Без равенства формул не существует. Поэтому программа не начнет распознавать таковую, когда отсутствует соответствующий знак.

Формула может включать в себя следующее:

Функция. Это стандартная формула, где присутствует определенный и конкретный порядок действий, поменять который не удастся.
Ссылка. Она указывает на количество клеток, которые нужно решить. При этом ячейки могут располагаться хаотично или в определенном порядке.
Оператор. Это символ, который задает тип вычисления (+ - сложение, * - умножение и т.д.).
Константа. Постоянное значение, которое никогда не меняется. Также для его получения не нужно производить вычисления.

Решение формул осуществляется слева направо при соблюдении всех математических правил.

Создание формулы

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

Вычисление начинается со знака равенства. К примеру, если в ячейке указывается «=КОРЕНЬ(номер клетки)», то будет использована соответствующая функция.

После того как была напечатана основная формула со знаком «=», нужно указать на данные, с которыми она будет взаимодействовать. Это может быть одна или несколько ячеек. Если формула подходит для 2-3 клеток, то объединить их можно, используя знак «+».

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

Заключение

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

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

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

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

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

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

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

Серая таблица (B3:G5) описывает стоимость доставки единицы от каждого склада до каждого магазина.
Лиловые ячейки (C14:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
Красные ячейки (J10:J11) отображают емкость каждого склада – предельное количество товара, которое склад может вместить.
Желтые (C12:G12) и синие (H10:H11) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
Общая стоимость доставки (E17) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки.

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

Решение

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

Если в меню Сервис или на вкладке Данные вашего Excel такой команды нет – ничего страшного - значит надстройка просто еще не подключена. Для ее подключения:

в Excel 2003 и старше - откройте меню Сервис – Надстройки (Tools – Add - Ins ) , в появившемся окне установите флажок Поиск решения (Solver ) и нажмите ОК . Excel активирует выбранную надстройку и в меню Сервис (Tools ) появится новая команда – Поиск решения (Solver ) .
в Excel 2007 и новее - нажать кнопку Офис , далее выбрать Параметры Excel – Надстройки – Перейти (Excel Options - Add-Ins - Go To) .

Запустим надстройку. Откроется вот такое окно:

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

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

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

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

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

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

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

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

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

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

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

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

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

Имеем классическую задачу когда фирма производит два вида продукции (товар А и товар Б) по определенной цене, на их производство требуется 4 вида ресурсов (ресурс 1, ресурс 2, ресурс 3, ресурс 4), которые есть в наличие на фирме в определенном количестве (Запас), также имеется информация сколько нужно каждого ресурса на производство единицы продукции, соответственно товара А и товара Б. Нужно найти, то количество товара А и товара Б, которое максимизирует доход (выручку) (см. рис.).

Далее нам надо сделать взаимосвязи между ограничениями, планом и целевой функцией. Для этого мы строим дополнительный столбец (Использовано), в котором вводим формулуСУММПРОИЗВ (Норма; План). Норма - это затраты определенного ресурса на производство единицы продукции товара А и Б, а План – количество продукции, которое мы ищем. В ячейки Доход вводим формулу СУММПРОИЗВ (Цена; План). Таким образом мы заполнили формулами столбец Использовано и ячейку Доход. Так как план это переменные от которых зависит количество использованных ресурсов и доход, то ячейки с формулами напрямую зависят от данных, которые там появятся в результате поиска решений. С выше сказанного можно сделать следующие выводы, что каждая задача оптимизации обязательно должна иметь три компоненты:

неизвестные (что ищем, то есть, план);

ограничение на неизвестные (область поиска);

целевая функция (цель, для которой ищем экстремум).

Мощным средством анализа данных Excel является надстройка Solver (Поиск решения) . С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета. Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных та нелинейных задач оптимизации, используется с 1991 года. Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:

количество неизвестных (decision variable) – 200;

количество формульных ограничений (explicit constraint) на неизвестные – 100;

количество предельных условий (simple constraint) на неизвестные – 400.

Разработчик программы Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Высокая эффективность их применения объясняется интеграциею программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса. По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007 , щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel , а затем выберите категорию Надстройки . В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти . В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК .

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

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

2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду: - В Excel 2007 Данные/Анализ /Поиск решения ;

В Excel 2003 и ниже Tools > Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки. 3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, - Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа. 4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата. 5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение.

6. Щелкните на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)

7. Щелкнув на кнопке Solver (Выполнить), запустите процесс поиска решения.

8. Когда появится диалоговое окно Solver Results (Результаты поиска решения), выберите переключатель Keep Solve Solution (Сохранить найденное решение) или Restore Original Values (Восстановить исходные значения). 9. Щелкните на кнопке ОК.

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

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

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

Что представляет собой «Поиск решений»?

Это надстройка программы. В обыкновенной конфигурации, которая выпускается производителем, данный пакет не предусмотрен. Он должен быть загружен и настроен отдельно. Зачастую пользователи обходятся без него. Кроме того, надстройку часто называют «Решателем», так как она ведет точные и оперативные вычисления, независимо от того, какой сложности является задача. В случае с оригинальной версией Microsoft Office проблемы с установкой не возникают. Пользователю необходимо выполнить следующие переходы: Параметры – Сервис – Надстройки – Управление − Надстройки Excel. В результате на экране отобразится окно, где будет присутствовать кнопка перехода. Если нажать на нее, появится список всех надстроек, которые предлагаются пользователю, как установленных, так и невостребованных. Далее потребуется отыскать «Поиск решений», а после этого установить около него отметку. Инструмент станет активным, его можно использовать когда угодно.

Для чего необходим Решатель? Зачем используется «Поиск решений» в программе Excel 2007, и какова необходимость в его установке? Если у пользователя имеется целевая функция, которая зависит от нескольких параметров, надстройка подберет решения задачи, соответствуя исходным данным. Ними может оказаться переменная, неизвестная или, допустим, итоговое значение. Другими словами, пользователь будет иметь начальные характеристики, а также ответ. Что касается самой программы, она подберет ход решения, предоставит формулу. Стоит отметить, что с помощью надстройки существует возможность отыскать следующее:

Удачное распределение рабочих ресурсов для достижения максимальной прибыли по мере деятельности компании либо отдельного ее отдела, филиала;
распределение вложений при условиях минимизированных рисков;
решение задач, где присутствует больше одной неизвестной;
сохранение и загрузка модели решения − оптимальный вариант, который используется сотрудниками, вынужденными постоянно менять компьютер либо ноутбук;
решение одновременно нескольких задач с различными переменными, неизвестными, формулами, а также интегралами.

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

Принцип работы Решателя? Кроме Решателя, в программе Excel существует такая функция, как выбор параметра. Она необходима для использования тогда, когда существует лишь одно неизвестное значение. Такая возможность нуждается в меньших затратах ресурсов, поэтому результат выдается оперативнее. Поиск решений в программе Excel 2007 используется для задач наибольшей сложности, в которых присутствует несколько неизвестных, часто наблюдаются переменные. Таким образом, все можно представить так: Найти неизвестные − несколько «x». При условии, что − формула либо функция. При ограничениях в данном случае обычно указано на неравенство или минимальные/максимальные значения. Кроме того, следует указать на ячейки, с которыми нужно проводить вычисления. Существует возможность решать сразу несколько различных задач при условии задания программе соответствующих параметров.

Настройка параметров «Поиска решений» Для корректной работы функции «Поиска решений» в программе Excel 2007 работала следует ввести правильные параметры. Как правило, они ограничиваются несколькими (1−3) характеристиками, однако с задачами большей сложности нужна глобальная настройка. Параметры в «Поиске решений» Office Excel 2007 бывают такие:

1. Максимальное время – число секунд, выделяемые пользователем для программы на решение, зависит от уровня сложности задачи.
2. Максимальное число интеграций. В данном случае речь идет о количествах ходов, выполняемых программой при решении задачи. Когда параметр увеличивается, то ответ не доходит.
3. Погрешность либо точность, зачастую используется в ходе решения десятичных дробей (например, до 0,0001).
4. Допустимое отклонение. Применяется в процессе работы с процентами.
5. Неотрицательные значения. Используется в том случае, когда решается функция, имеющая два правильных ответа (допустим, +/-X).
6. Отображение результатов интеграций. Эта настройка предусматривается тогда, когда важен как результат решений, так и их ход.
7. Метод поиска – подбор оптимизационного алгоритма. Как правило, используется «метод Ньютона». Когда все настройки будут выбраны, необходимо нажать кнопку сохранения.

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

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

Формула в Excel

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

1. Функция. Стандартная формула, в которой имеется определенная и конкретная очередность действий, изменить ее не получится.
2. Ссылка. Указывает на число клеток, которые требуется решить. Ячейки при этом способны размещаться хаотично либо в определенном порядке.
3. Оператор. Представляет собой символ, задающий тип вычисления (+ – сложение, * – умножение и прочее.).
4. Константа. Является постоянным значением, которое всегда остается неизменным. Для его получения не требуется выполнять вычисления.

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

Создание формулы

Формулы представляют собой уравнения, которые необходимы для выполнения вычислений программы. Когда они не вводятся, «Поиск решения» в программе Excel не будет работать. Задачи также не будут решаться. Таким образом, для корректной работы нужно правильно ввести формулу. Расчет начинается с равенства. Если в ячейке указано «=КОРЕНЬ(номер клетки)», то используется соответствующая функция. Когда напечатана главная формула со знаком «=», необходимо указать на данные, с которыми она взаимодействует. Чтобы отыскать требуемую информацию, следует использовать функцию поиска.

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

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

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

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

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

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

Подготовка таблицы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Категории

Популярное