Надстройка Поиск решений в Excel 2007 не является стандартной. Она предназначается для сложных вычислений, когда имеется больше одной неизвестной. Поэтому она не включается в обычный набор параметров программы. Но если в ней есть необходимость, то она способна предложить пользователю эффективную работу и высокую продуктивность.
Поиск решений в Excel 2007 является надстройкой программы. Это означает, что в обычной конфигурации, выпускаемой производителем, этот пакет не устанавливается. Его нужно загружать и настраивать отдельно. Дело в том, что чаще всего пользователи обходятся без него. Также надстройку нередко называют «Решатель», поскольку она способна вести точные и быстрые вычисления, зачастую независимо от того, насколько сложная задача ей представлена.
Если версия Microsoft Office является оригинальной, тогда проблем с установкой не возникнет. Пользователю нужно сделать несколько переходов:
Параметры→Сервис→Надстройки→Управление→Надстройки Excel.
Откроется окно, в котором есть кнопка перехода. После клика на нее на экране появится список всех предлагаемых надстроек, как установленных, так и неиспользуемых. Теперь нужно найти Поиск решений, затем поставить галочку. Инструмент активизирован, можно пользоваться им в любое время.
Для чего можно использовать Поиск решений в Excel 2007, и стоит ли вообще его устанавливать? Когда у пользователя присутствует зависящая от нескольких параметров, надстройка будет подбирать решения задачи в соответствии с исходными данными. Таковыми может оказаться переменная, неизвестная или, например, итоговое значение. То есть, пользователь может иметь начальные характеристики и ответ, а программа подберет ход решения, предоставит формулу.
Таким образом, посредством надстройки можно найти:
Программа открывает большие возможности, но ею нужно научиться правильно пользоваться.
Помимо решателя, в Excel есть такая функция, как подбор параметра. Она рекомендована к использованию в случаях, когда имеется только одно неизвестное значение. Эта возможность программы требует намного меньше ресурсных компьютерных затрат, поэтому быстрее выдаст результат.
Поиск решений в Excel 2007 применяется для самых сложных задач, где имеется несколько неизвестных, часто встречаются переменные. В общей постановке их можно сформулировать следующим образом:
Также нужно указать на ячейки, с которыми следует проводить вычисления. Есть возможность решать несколько разных задач, если задать программе соответствующие параметры.
Чтобы функция Поиска решений в Excel 2007 работала так, как необходимо пользователю, нужно ввести правильные параметры. Обычно они ограничиваются 1-3 характеристиками, но с более сложными задачами потребуется глобальная настройка.
Параметры в Поиске решений программы Office Excel 2007 могут быть следующими:
После того как все настройки выбраны, обязательно нужно нажать кнопку сохранения.
Работа такой надстройки, как Поиск решения в Excel, осуществляется в соответствии с заданными характеристиками вычисления. Наиболее важной из них является метод. Есть два их варианта. «Метод Ньютона» является настройкой по умолчанию. Он способен работать с большей памятью, но меньшими интеграциями. Поэтому для стандартных и не особо сложных уравнений он вполне подойдет.
Также есть «метод сопряженных градиентов». Здесь запрашивается меньше памяти, но требуется больше интеграций. Следовательно, при его использовании можно решать самые сложные уравнения, использовать масштабные формулы и функции.
Есть обязательный элемент, без которого не сможет функционировать надстройка Поиска решений в программе Excel 2007 - формулы. Они представляют собой такое выражение, которое выполняет то или иное вычисление. Без равенства формул не существует. Поэтому программа не начнет распознавать таковую, когда отсутствует соответствующий знак.
Формула может включать в себя следующее:
Решение формул осуществляется слева направо при соблюдении всех математических правил.
Формулы являются уравнениями, которые способствуют выполнению вычислений программы. Если таковые не вводить, то не будет работать Поиск решения в Excel. Задачи, соответственно, тоже не станут решаться. Поэтому для удачного выполнения поставленного задания необходимо правильно ввести формулу.
Вычисление начинается со знака равенства. К примеру, если в ячейке указывается «=КОРЕНЬ(номер клетки)», то будет использована соответствующая функция.
После того как была напечатана основная формула со знаком «=», нужно указать на данные, с которыми она будет взаимодействовать. Это может быть одна или несколько ячеек. Если формула подходит для 2-3 клеток, то объединить их можно, используя знак «+».
Чтобы найти нужную информацию, можно воспользоваться функцией поиска. Например, если нужна формула с буквой «A», то ее и надо указывать. Тогда пользователю будут предложены все данные, ее в себя включающие.
В заключении в программе Excel 2007 нужно сохранить заданные параметры решения задач. Сделать это можно несколькими способами. Стандартный вариант с кликом на соответствующую кнопку подойдет в том случае, если для всех данных используется один метод вычислений.
Когда нужно решить сразу несколько уравнений, к примеру, найти минимум и максимум функций, то нужно сохранять не все вычисление, а его модели. Затем пользователь сможет применить их к тому или иному решению.
«Поиск решения» - это надстройка в табличном редакторе Microsoft Office Excel. Он используется для поиска оптимального значения формулы в одной выбранной ячейке электронной таблицы. По умолчанию эта надстройка отключена в Excel, но может быть в любое время активирована средствами самого редактора, без установки каких-то дополнительных приложений.
Надстройка Excel «Поиск решения» – это аналитический инструмент, который позволяет нам быстро и легко определить, когда и какой результат мы получим при определенных условиях. Возможности инструмента поиска решения намного выше, чем может предоставить «подбор параметра » в Excel.
Основные отличия между поиском решения и подбором параметра:
Мы можем изменять переменные значения в ячейках B1 и B2 так, чтобы подобрать необходимые условия для накопления необходимой суммы денег.
Надстройка «Поиск решения» - позволяет нам одновременно использовать 2 этих варианта, чтобы быстро смоделировать наиболее оптимальные условия для достижения поставленной цели. Для этого:
Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.
Допустим, вы пошли в банк с этой таблицей, но банк отказывается поднять Вам процентную ставку. В таком случаи нам нужно узнать, насколько нам придется повысить сумму ежегодных вложений. Мы должны установить ограничение на ячейку с одним переменным значением. Но перед началом измените значения в переменных ячейках на исходные: в B1 на 5%, а в B2 на -1000$. А теперь делаем следующее.
Поиск решения - это надстройка Microsoft Excel, с помощью которой можно найти оптимальное решение задачи с учетом заданных пользователем ограничений.
Поиск решения будем рассматривать в (эта надстройка претерпела некоторые изменения по сравнению с предыдущей версией в .
В этой статье рассмотрим:
Команда Поиск решения находится в группе Анализ на вкладке Данные .
Если команда Поиск решения
в группе Анализ
недоступна, то необходимо включить одноименную надстройку.
Для этого:
Примечание . Окно Надстройки также доступно на вкладке Разработчик . Как включить эту вкладку .
После нажатия кнопки Поиск решения в группе Анализ, откроется его диалоговое окно.
При частом использовании Поиска решения его удобнее запускать с Панели быстрого доступа, а не из вкладки Данные. Чтобы поместить кнопку на Панель, кликните на ней правой клавишей мыши и выберите пункт Добавить на панель быстрого доступа .
Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.
Совет . Перед использованием Поиска решения настоятельно рекомендуем изучить литературу по решению оптимизационных задач и построению моделей.
Ниже приведен небольшой ликбез по этой теме.
Надстройка Поиск решения помогает определить лучший способ сделать что-то :
Вот некоторые типичные примеры оптимизационных задач:
Для формализации поставленной задачи требуется создать модель, которая бы отражала существенные характеристики предметной области (и не включала бы незначительные детали). Следует учесть, что модель оптимизируется Поиском решения
только по одному показателю
(этот оптимизируемый показатель называется целевой функцией
).
В MS EXCEL модель представляет собой совокупность связанных между собой формул, которые в качестве аргументов используют переменные. Как правило, эти переменные могут принимать только допустимые значения с учетом заданных пользователем ограничений.
Поиск решения
подбирает такие значения этих переменных (с учетом заданных ограничений), чтобы целевая функция была максимальной (минимальной) или была равна заданному числовому значению.
Примечание
. В простейшем случае модель может быть описана с помощью одной формулы. Некоторые из таких моделей могут быть оптимизированы с помощью инструмента . Перед первым знакомством с Поиском решения
имеет смысл сначала детально разобраться с родственным ему инструментом .
Основные отличия Подбора параметра
от Поиска решения
:
Поиск решения
оптимизирует значение целевой функции. Под целевой функцией подразумевается формула, возвращающая единственное значение в ячейку. Результат формулы должен зависеть от переменных модели (не обязательно напрямую, можно через результат вычисления других формул).
Ограничения модели могут быть наложены как на диапазон варьирования самих переменных, так и на результаты вычисления других формул модели, зависящих от этих переменных.
Все ячейки, содержащие переменные и ограничения модели должны быть расположены только на одном листе книги. Ввод параметров в диалоговом окне Поиска решения
возможен только с этого листа.
Целевая функция (ячейка) также должна быть расположена на этом листе. Но, промежуточные вычисления (формулы) могут быть размещены на других листах.
Совет . Организуйте данные модели так, чтобы на одном листе MS EXCEL располагалась только одна модель. В противном случае, для выполнения расчетов придется постоянно сохранять и загружать настройки Поиска решения (см. ниже).
Приведем алгоритм работы с Поиском решения , который советуют сами разработчики (www.solver.com ):
Проделаем все эти шаги на простом примере.
Необходимо загрузить контейнер товарами, чтобы вес контейнера был максимальным. Контейнер имеет объем 32 куб.м. Товары содержатся в коробках и ящиках. Каждая коробка с товаром весит 20кг, ее объем составляет 0,15м3. Ящик - 80кг и 0,5м3 соответственно. Необходимо, чтобы общее количество тары было не меньше 110 штук.
Данные модели организуем следующим образом (см. файл примера ).
Переменные модели (количество каждого вида тары) выделены зеленым.
Целевая функция (общий вес всех коробок и ящиков) – красным.
Ограничения модели: по минимальному количеству тары (>=110) и по общему объему (<=32) – синим.
Целевая функция рассчитывается по формуле =СУММПРОИЗВ(B8:C8;B6:C6)
– это общий вес всех коробок и ящиков, загруженных в контейнер.
Аналогично рассчитываем общий объем - =СУММПРОИЗВ(B7:C7;B8:C8)
. Эта формула нужна, чтобы задать ограничение на общий объем коробок и ящиков (<=32).
Также для задания ограничения модели рассчитаем общее количество тары =СУММ(B8:C8)
.
Теперь с помощью диалогового окна Поиск решения
введем ссылки на ячейки содержащие переменные, целевую функцию, формулы для ограничений и сами значения ограничений (или ссылки на соответствующие ячейки).
Понятно, что количество коробок и ящиков должно быть целым числом – это еще одно ограничение модели.
После нажатия кнопки Найти решение будут найдены такие количества коробок и ящиков, при котором общий их вес (целевая функция) максимален, и при этом выполнены все заданные ограничения.
На самом деле, основной проблемой при решении оптимизационных задач с помощью Поиска решения
является отнюдь не тонкости настройки этого инструмента анализа, а правильность построения модели, адекватной поставленной задаче. Поэтому в других статьях сконцентрируемся именно на построении моделей, ведь «кривая» модель часто является причиной невозможности найти решение с помощью Поиска решения
.
Зачастую проще просмотреть несколько типовых задач, найти среди них похожую, а затем адаптировать эту модель под свою задачу.
Решение классических оптимизационных задач с помощью Поиска решения
рассмотрено .
Это сообщение появляется, когда Поиск решения
не смог найти сочетаний значений переменных, которые одновременно удовлетворяют всем ограничениям.
Если вы используете Симплекс метод решения линейных задач
, то можно быть уверенным, что решения действительно не существует.
Если вы используете метод решения нелинейных задач, который всегда начинается с начальных значений переменных, то это может также означать, что допустимое решение далеко от этих начальных значений. Если вы запустите Поиск решения
с другими начальными значениями переменных, то, возможно, решение будет найдено.
Представим, что при решении задачи нелинейным методом, ячейки с переменными были оставлены не заполненными (т.е. начальные значения равны 0), и Поиск решения
не нашел решения. Это не означает, что решения действительно не существует (хотя это может быть и так). Теперь, основываясь на результатах некой экспертной оценки, в ячейки с переменными введем другой набор значений, который, по Вашему мнению, близок к оптимальному (искомому). В этом случае, Поиск решения
может найти решение (если оно действительно существует).
Примечание . О влиянии нелинейности модели на результаты расчетов можно прочитать в последнем разделе статьи .
В любом случае (линейном или нелинейном), Вы должны сначала проанализировать модель на непротиворечивость ограничений, то есть условий, которые не могут быть удовлетворены одновременно. Чаще всего это связано с неправильным выбором соотношения (например, <= вместо >=) или граничного значения.
Если, например, в рассмотренном выше примере, значение максимального объема установить 16 м3 вместо 32 м3, то это ограничение станет противоречить ограничению по минимальному количеству мест (110), т.к. минимальному количеству мест соответствует объем равный 16,5 м3 (110*0,15, где 0,15 – объем коробки, т.е. самой маленькой тары). Установив в качестве ограничения максимального объема 16 м3, Поиск решения
не найдет решения.
При ограничении 17 м3 Поиск решения найдет решение.
Метод решения
Рассмотренная выше модель является линейной, т.е. целевая функция (M – общий вес, который может быть максимален) выражена следующим уравнением M=a1*x1+a2*x2, где x1 и x2 – это переменные модели (количество коробок и ящиков), а1 и а2 – их веса. В линейной модели ограничения также должны быть линейными функциями от переменных. В нашем случае ограничение по объему V=b1*x1+b2*x2 также выражается линейной зависимостью. Очевидно, что другое ограничение - Максимальное количество тары (n) – также линейно x1+x2
В этом случае необходимо выбрать метод для решения нелинейной задачи. Примеры нелинейных зависимостей: V=b1*x1*x1; V=b1*x1^0,9; V=b1*x1*x2, где x – переменная, а V – целевая функция.
Кнопки Добавить, Изменить, Удалить
Эти кнопки позволяют добавлять, изменять и удалять ограничения модели.
Кнопка Сбросить
Чтобы удалить все настройки Поиска решения
нажмите кнопку Сбросить
– диалоговое окно очистится.
Точность
При создании модели исследователь изначально имеет некую оценку диапазонов варьирования целевой функции и переменных. Принимая во внимание вычислений в MS EXCEL, рекомендуется, чтобы эти диапазоны варьирования были значительно выше точности вычисления (она обычно устанавливается от 0,001 до 0,000001). Как правило, данные в модели нормируют так, чтобы диапазоны варьирования целевой функции и переменных были в пределах 0,1 – 100 000. Конечно, все зависит от конкретной модели, но если ваши переменные изменяются более чем на 5-6 порядков, то возможно следует «загрубить» модель, например, с помощью операции логарифмирования.
«Поиск решений» в программе 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 не будет работать. Задачи также не будут решаться. Таким образом, для корректной работы нужно правильно ввести формулу. Расчет начинается с равенства. Если в ячейке указано «=КОРЕНЬ(номер клетки)», то используется соответствующая функция. Когда напечатана главная формула со знаком «=», необходимо указать на данные, с которыми она взаимодействует. Чтобы отыскать требуемую информацию, следует использовать функцию поиска.
