Надстройка поиск решения и подбор нескольких параметров Excel. Поиск решения в эксель


Поиск решений в Excel — пример использования сервиса поиск решений в Excel

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

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

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

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

Где в Excel поиск решений

Надстройка Поиск решений поставляется вместе с Excel, но по умолчанию отключена. Чтобы включить его, перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры, выберите Надстройки -> Управление: Надстройки Excel -> Перейти. В окне Надстройки устанавливаем галочку напротив поля Поиск решения, жмем ОК.

Теперь во вкладке Данные появилась новая группа Анализ с кнопкой Поиск решения.

Пример использования Поиска решения

Данный пост основан на примере использования Надстройки Поиск решения. Файл совместим со всеми версиями Excel.

Определение проблемы

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

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

Для начала требуется определить каждый пункт к какой-нибудь группе.

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

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

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

Наконец, нам необходимо свести сумму групп и работать с разницей между ними.

Наша задача минимизировать разницу между суммами групп.

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

Для большей наглядности я добавил условное форматирование для ячеек, имеющих значение >0.

Проблема в том, что количество возможных комбинаций 28, т.е. 256 вероятных ответов на вопрос. Если на каждый из них тратить по 5 секунд, это займет у нас 21,3 минуты, предполагая, что мы сможем выдержать темп и запомнить лучшую комбинацию.

Вот где Поиск решения находит применение.

Поиск оптимального решения в Excel

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

Наши правила

Наше основное требование – это минимизировать разницу между двумя группами. В нашем примере она находится в ячейке G11 – Группа B минус Группа A. Нам нужно, чтобы значение в ячейке G11 было настолько малым насколько это возможно, но больше или равно 0.

Мы также знаем, что пункт может находиться либо в Группе A, либо в Группе B, к тому он не может быть дробным. Таким образом у нас два ограничения для каждого элемента:

Во-первых: Значение элемента в колонке Итог должна равняться единице.

Во-вторых: Значения элементов в группах должны быть целыми.

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

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

В этом разделе описано окно надстройки Поиск решения и его использования для определения проблемы.

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

Заполненное окно Поиска решения

Оптимизировать целевую функцию

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

До

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

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

Другой способ наложения ограничения – изменить G11 на =ABS(G10-F10). При этом мы сможем установить маркер на Минимум, как результат достижения целевой функции.

Но пока мы остановимся на формуле =G10-F10 и установим маркер в значение равным 0.

Изменяя ячейки переменных

Изменяемые ячейки – ячейки, которые надстройка попытается изменить, чтобы решить задачу. В нашем случае это привязка элемента к конкретной группе: $C$2:$D$9.

В соответствии с ограничениями

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

Нам необходимо добавить несколько ограничений в наш список:

  1. В колонке Итого каждый элемент должен равняться 1
  2. Элементы групп должны быть целым числом
  3. Сумма значений столбца Итого должна равняться 8

Чтобы наложить ограничения, жмем кнопку Добавить

  1. Для каждой ячейки диапазона E2:E9 устанавливаем ограничение значения равным 1
  2. Для каждой ячейки диапазона C2:D9 устанавливаем ограничение значение целое число.
  3. Необходимо добавить ограничение на сумму обоих групп, ячейка E10 = 8.

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

Загрузить/сохранить параметры поиска решений

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

Запуск поиска оптимального решения в Excel

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

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

Два параметра, которые необходимо будет менять время от времени:

Точность ограничения: значение от 0 до 1, где, чем больше цифра, тем больше ограничение

Целочисленная оптимальность: показывает насколько далеко от целого числа ограничение имеет право быть.

Запуск модели

Чтобы запустить надстройку нажмите кнопку Найти решение в основном окне.

В строке состояния вы увидите ряд статических данных, которые будут отображать внутреннюю работу надстройки. Как правило, они быстро меняются, и читать их сложно. Если модель сложная, то работа может остановится на некоторое время, надстройка обычно восстанавливается от этих проблем сама.

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

Теперь у вас есть 4 варианта на выбор:

— Запустить отчет

— Сохранить сценарий

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

— Сохранить найденное решение

Запустить отчет

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

Обратите внимание, что в зависимости от установленных вами ограничений, будут доступны различные отчеты.

Сохранить сценарий

Если вы нажмете кнопку Сохранить сценарий, Excel откроет следующее диалоговое окно:

Где необходимо ввести название вашего сценария модели и нажать кнопку ОК.

Все сценарии доступны в Диспетчере сценариев, который находится во вкладке Данные в группе Работа с данными –> Анализ что-если -> Диспетчер сценариев.

Вернуться к модели

К тому же, вы можете вернуться к модели и:

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

— Сохранить найденное решение

Проверка результатов

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

Данная проверка на реалистичность должна начинаться с подтверждения, что все результаты удовлетворяют заданным критериям:

— Являются ли результаты примерно похожими на ваши ожидания?

— Не нарушены ли максимумы и минимумы?

Вам также могут быть интересны следующие статьи

exceltip.ru

Надстройка поиск решения и подбор нескольких параметров Excel

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

Основные отличия между поиском решения и подбором параметра:

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

Где находится поиск решений в Excel? По умолчанию данная надстройка не установлена. О том, как ее установить читайте: подключение надстройки «Поиск решения».

Примеры и задачи на поиск решения в Excel

Рассмотрим аналитические возможности надстройки. Например, Вам нужно накопить 14 000$ за 10 лет. На протяжении 10-ти лет вы хотите каждый год откладывать на депозитный счет в банке по 1000$ под 5% годовых. Ниже на рисунке построена таблица в Excel, по которой хорошо видно остаток накопленных средств на каждый год. Как видно при таких условиях депозитного счета и взносов накопления цель не будет достигнута даже через 10 лет. При решении данной задачи можно пойти двумя путями:
  1. Найти банк, который предлагает более высокую процентную ставку по депозитам.
  2. Увеличить размер ежегодных накопительных взносов на банковский счет.

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

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

  1. Перейдите в ячейку B14 и выберите инструмент: «Данные»-«Анализ»-«Поиск решения».
  2. В появившемся диалоговом окне заполните все поля и параметры так как указано ниже на рисунке. Не забудьте убрать галочку напротив опции: «Сделать переменные без ограничений неотрицательными». И нажмите «Найти решение».

Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.



Ограничение параметров при поиске решений

Допустим, вы пошли в банк с этой таблицей, но банк отказывается поднять Вам процентную ставку. В таком случаи нам нужно узнать, насколько нам придется повысить сумму ежегодных вложений. Мы должны установить ограничение на ячейку с одним переменным значением. Но перед началом измените значения в переменных ячейках на исходные: в B1 на 5%, а в B2 на -1000$. А теперь делаем следующее:

  1. Перейдите в ячейку B14 и выберите инструмент: «Данные»-«Анализ»-«Поиск решения».
  2. Напротив списка параметров: «В соответствии с ограничениями» нажмите на кнопку «Добавить».
  3. В появившемся окне «Добавление ограничения» заполните поля так как указано выше на рисунке. И нажмите ОК.
  4. Снова заполняем параметры и поля появившегося диалогового окна, как в предыдущем примере:
  5. Нажмите «Найти решение».

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

exceltable.com

Поиск решения в Excel | Эксель Практик

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

Поиск решения путем подбора данных находит наиболее эффективное значение. В нашем случае, максимальную прибыль. Я взял небольшую таблицу:Здесь, как видите, синим обозначена целевая ячейка, та, которую нужно максимизировать, изменяя расходы на рекламу (зеленые ячейки). Хитрость в том, что прибыль зависит от объема продаж (в штуках), а от него, в свою очередь зависят и расходы и доходы, которые формируют прибыль. Т.е., просто увеличив или уменьшив расходы на рекламу, вы не получите лучшего результата. В этом и состоит ценность Поиска решения - он делает перебор всех возможных значений по своему алгоритму и получает наилучший результат. Кто проходил обучение по моему самоучителю, уже в курсе, как это делается и как применяется.

Еще есть ограничение бюджет рекламных расходов в 40 тыс. долл. за 4 месяца.

Итак, приступим к технической части.

  1. Если вы никогда не пользовались этой надстройкой, придется ее сначала установить. Дело в том, что по умолчанию Поиск решения не ставится. Заходим Офис/Параметры Excel/Надстройки/Кнопка "Перейти". Мы уже заходили сюда, когда делали сумму прописью.
  2. Теперь, у вас на вкладке Данные появилась команда "Поиск решения". Нажимаем и видим такое окошко:
  3. Целевая ячейка - это там, которую мы хотим максимизировать, это результат. У нас это B15. Т.е. я хочу увидеть, какими должны быть расходы на рекламу, чтобы в январе у меня была максимальная прибыль. Ставим выбор "максимальному значению".
  4. Изменяя ячейки - ставим диапазон ячеек, от которых зависит итог. У меня это все расходы на рекламу, т.е. диапазон B11 - E11.
  5. Ограничения - ну без них никак. Excel мыслит больше математически, поэтому нам надо:
  • Поставить условия положительности изменяемых ячеек. B11:E11 > 0
  • Ограничить рекламный бюджет за 4 месяца. F11=40000
  1. Нажимаем на кнопку "Выполнить". Если что-то пошло не так, а это бывает, обнулите диапазон изменяемых ячеек, может помочь.

В итоге мы получим нужный результат

Если не получим, значит, надо пересмотреть условия, возможно, что-то некорректно проставлено или задача не имеет решения.

Видео по теме (5 минут):

Скачать пример.

Эксель Практик«Глаза боятся, а руки делают»

P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.

 

 

 

 

 

 

 

 

excelpractic.ru

Excel 2007. Поиск решений в Excel 2007

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формула в Excel

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

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

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

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

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

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

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

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

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

Заключение

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

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

fb.ru

Надстройка Microsoft Excel "Поиск решения"

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

Краткое описание надстройки

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

Как установить надстройку?

Надстройку "Поиск решения" можно установить двумя способами. Стандартные надстройки, такие как "Поиск решения" и "Пакет анализа" устанавливаются вместе с MS Office или MS Excel. Если при первоначальной установке стандартная надстройка не была установлена, то следует запустить процесс установки повторно. Рассмотрим установку надстройки "Поиск решения" на примере Microsoft Office 2010. В версиях 2003 и 2007 все делается аналогично.

Итак, запускаем установочный диск с пакетом приложений MS Office 2010 и выбираем опцию "Добавить или удалить компоненты".

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

Опять жмем кнопку "Продолжить" и ожидаем пока надстройка установится.

Как подключить надстройку?

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

Вызов этого окна несколько различается в зависимости от версии приложения. Подробно об этом написано в отдельной статье "Как установить надстройку для Excel 2003/2007/2010?" со скриншотами для каждой из трех версий приложения Excel,  поэтому не буду повторяться. Да, добавлю лишь несколько слов о втором способе установки этой надстройки. Можно отыскать на просторах Интернета файл с названием Solver.xla (это и есть надстройка "Поиск решения") и произвести установку в соответствии с описанием по ссылке выше.

Где найти надстройку "Поиск решения" в Excel 2003/2007/2010?

После установки и подключения надстройки в Excel 2007/2010 на вкладке "Данные" появляется группа "Анализ" с новой командой "Поиск Решения". В Excel 2003 - появляется новый пункт меню "Сервис" с одноименным названием. Поиск решения - стандартная надстройка, существуют также и другие надстройки для Excel, служащие для добавления в MS Excel различных специальных возможностей.

macros-vba.ru

Решение задач в Excel с помощью средств «Поиск решения» и «Подбор параметра»

 

 

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

Основные надстройки, поставляемые вместе с пакетом Excel:

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

– Мастер суммирования. Позволяет автоматизировать создание формул для суммирования данных в столбце таблицы и использовать частичные суммы.

– Мастер подстановок. Автоматизирует создание формулы для поиска данных в таблице по названию столбца и строки и позволяет использовать поиск с параметром.

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

Средство Поиск решения.Запускается командой Данные → Анализ → Поиск решения. Элементы диалогового окна:

установить целевую ячейку –адрес ячейки с целевой функцией;

равной – значение, к которому стремиться целевая функция;

изменяя ячейки –адреса влияющих ячеек;

параметры –открывает окно для задания ограничений на значения влияющих ячеек.

Средство Подбор параметра.Запускается командой Данные → Работа с данными → Анализ «что-если» → Подбор параметра.

 

 

Практическая часть

 

Задание 5.12. Решить систему нелинейных уравнений с помощью средства Поиск решения.

(1)

Выполнение.

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

(2)

Вместо системы (1) будем решать уравнение (2). Решений будет два.

Чтобы применить метод Поиск решения необходимо, предварительно, найти начальное приближение решений. Для этого построим таблицу значений левой части уравнения (2) по переменным х и у на интервале (– 1.7; +1.7) с шагом 0.3. Границы интервала взяты на основании того, что корни уравнения лежат внутри круга, радиус которого приблизительно равен =1.73.

Для построения таблицы выполняем:

1. В ячейки А2:А14 вводим значения х (в интервале [–1.7, 1.7]), а в ячейки В1:N1 – значения y в таком же интервале.

2. В ячейку В2 вводится формула =($A2^2+B$1^2-3)^2+(2*$A2+3*B$1-1)^2 –уравнение (2).

3. Копируем формулу ячейки B2 вдиапозон B2:N14.

 

В соответствии с формулой (2) за начальные значения х и y берутся значения в тех ячейках заполненного диапазона, где функция принимает наименьшие значения. Под значения первого корня отводим ячейки А16:В16, а А17:В17 – под значения второго корня.

Для системы (1), в соответствии с полученной таблицей первое минимальное значение 0,4325. В ячейку А16мы вводим 1.3 – значение x, в В16 – 1.4 – значение y. В ячейку С16 вводим формулу =(А16^2+В16^2-3)^2+(2*A16+3*B16-1)^2.

Открываем окно Поиска решенийи устанавливаем: Целевая ячейка – $C16; Изменяя ячейки – $A16:$B16; установить параметр – Минимальному значению. Нажимаем кнопку Выполнить.

 

 

Значение корней уравнения появятся в ячейках А16 и В16. Второй корень находим аналогично, взяв следующее наименьшее значение 0,08.

Задание 5.13. Найти корни кубического уравнения (полинома) с одним неизвестным с помощью средства Подбор параметра.

Выполнение.

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

1. Введем в ячейку A2 значение –1 , а в A3 – значение: – 0.8.

2. Используя маркер заполнения, заполним ячейки до А12.

3. В ячейку B2 вводим формулу: = A2^3 – 0,01*A2^2 – 0,7044*A2 + 0,139104.

4. Заполняем диапазон B3:B12.

5. По полученным значениям строим график заданного полинома.

 

 

Мы увидим, что для нашего случая полином меняет знак на интервалах [-1,-0.8], [0.2, 0.4] и [0.6, 0.8], т.е. пересекается с осью x. Интервалов три – столько корней имеет уравнение третьей степени. Корни локализованы.

Теперь зададим точность нахождения значений корней. На вкладке Office → Параметры Excel → Формулы → Параметры вычислений задаем относительную погрешность 0,00001 и предельное число итераций 1000 (число последовательных приближений).

Отводим на новом рабочем листе ячейку С2 под первый корень, соответственно ячейки C3 и C4 под второй и третий корни полинома.

Корни будем находим методом последовательных приближений. Поэтому в ячейку С2 вводим сначала значение, являющееся первым приближением к искомому корню. В нашем случае возьмем первый отрезок и в нем среднее значение, т.е. – 0,9. Соответственно в ячейки С3 и С4 вводим приближенные значения для второго и третьего корней: +0,3 и +0,7.

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

 

= C2^3 – 0,01*C2^2 – 0,7044*C2 + 0.139104.

 

Копируем эту формулу в ячейки D3 и D4 для второго и третьего корней полинома. С помощью инструмента Подбор параметранаходим первый корень:

1. Выбираем команду Данные → Работа с данными → Анализ «что-если» → Подбор параметра. На экране появится диалоговое окно.

 

 

2. В поле Установить в ячейке введем ссылку на ячейку D2, в которой введена формула, вычисляющая значение левой части полинома.

3. В поле Значение вводим 0 – значение из правой части уравнения.

4. В поле Изменяя значение ячейки введем С2 –ссылка на ячейку, отведенную под первый корень.

5. Нажимаем ОК.

Получим окно с результатами:

 

 

Закрыв окно, найденное приближенное значение корня помещается в ячейку D2. В данном случае оно равно –0,92034.

Аналогично, повторив действия 1–5 для каждого из оставшихся корней, в ячейках D3 и D4 находим их значения. Соответственно, они равны 0,21021 и 0,72071.

 

 

Похожие статьи:

poznayka.org

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

5

Практикум по Excel-2. Занятие 5

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

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

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

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

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

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

Q*k = extr Qk XYj(х)<= Zj max 

где X ={x1, x2, … xn} Qk =Yj (X)

}

(1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        Отчетный период

        22.00

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

        Прибыль отреализации

        Постоянныезатраты

        Переменныезатраты

        Затраты

        Баланс

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

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

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

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

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

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

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

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

    x2+y2+z2-1=0

    2x2+y2-2z=0

    3x2-4y+z2=0

    при начальных приближениях: x=1, y=1, z=1
    1. Контрольные вопросы
      1. Перечислите основные элементы оптимизационной математической модели.

      2. Опишите возможный порядок построения оптимизационной модели.

      3. Как классифицируются задачи оптимизации?

      4. Что означает понятие «линейная модель»?

      5. Какие задачи позволяет решать средство Excel Поиск решения?

      6. Каким образом можно настроить средство Поиск решения на решение линейной задачи оптимизации?

    Александр Ю. Алексеев

    refdb.ru



О сайте

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