Как сделать поиск решения в excel online

Загрузка надстройки "Поиск решения" в Excel

"Поиск решения" — это программная надстройка для Microsoft Office Excel, которая доступна при установке Microsoft Office или приложения Excel.

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

В Excel 2010 и более поздних версий выберите Файл > Параметры.

Примечание: В Excel 2007 нажмите кнопку Microsoft Office кнопку и выберите Excel параметры.

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

Нажмите кнопку Перейти.

В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

Если надстройка Поиск решения отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы найти ее.

Если появится сообщение о том, что надстройка "Поиск решения" не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.

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

В меню Сервис выберите Надстройки Excel.

В поле Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

Если надстройка Поиск решения отсутствует в списке поля Доступные надстройкинажмите кнопку Обзор, чтобы найти ее.

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

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

В настоящее время надстройка "Поиск решения", предоставляемая компанией Frontline Systems, недоступна для Excel на мобильных устройствах.

"Поиск решения" — это бесплатная надстройка для Excel 2013 с пакетом обновления 1 (SP1) и более поздних версий. Для получения дополнительной информации найдите надстройку "Поиск решения" в Магазине Office.

В настоящее время надстройка "Поиск решения", предоставляемая компанией Frontline Systems, недоступна для Excel на мобильных устройствах.

"Поиск решения" — это бесплатная надстройка для Excel 2013 с пакетом обновления 1 (SP1) и более поздних версий. Для получения дополнительной информации найдите надстройку "Поиск решения" в Магазине Office.

В настоящее время надстройка "Поиск решения", предоставляемая компанией Frontline Systems, недоступна для Excel на мобильных устройствах.

"Поиск решения" — это бесплатная надстройка для Excel 2013 с пакетом обновления 1 (SP1) и более поздних версий. Для получения дополнительной информации найдите надстройку "Поиск решения" в Магазине Office.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Поиск решения EXCEL. Знакомство

history 23 марта 2015 г.
    Группы статей

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

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

Поиск решения будем рассматривать в MS EXCEL 2010 (эта надстройка претерпела некоторые изменения по сравнению с предыдущей версией в MS EXCEL 2007) . В этой статье рассмотрим:

  • создание оптимизационной модели на листе MS EXCEL
  • настройку Поиска решения;
  • простой пример (линейная модель).

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

Команда Поиск решения находится в группе Анализ на вкладке Данные .

Если команда Поиск решения в группе Анализ недоступна, то необходимо включить одноименную надстройку. Для этого:

  • На вкладке Файл выберите команду Параметры , а затем — категорию Надстройки ;
  • В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти;
  • В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

Примечание . Окно Надстройки также доступно на вкладке Разработчик . Как включить эту вкладку читайте здесь .

После нажатия кнопки Поиск решения в группе Анализ, откроется его диалоговое окно .

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

О моделях

Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.

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

Ниже приведен небольшой ликбез по этой теме.

Надстройка Поиск решения помогает определить лучший способ сделать что-то :

  • "Что-то" может включать в себя выделение денег на инвестиции, загрузку склада, доставку товара или любую другую предметную деятельность, где требуется найти оптимальное решение.
  • "Лучший способ" или оптимальное решение в этом случае означает: максимизацию прибыли, минимизацию затрат, достижение наилучшего качества и пр.

Вот некоторые типичные примеры оптимизационных задач:

  • Определить план производства , при котором доход от реализации произведенной продукции максимальный;
  • Определить схему перевозок , при которой общие затраты на перевозку были бы минимальными;
  • Найти распределение нескольких станков по разным видам работ , чтобы общие затраты на производство продукции были бы минимальными;
  • Определить минимальный срок исполнения всех работ проекта (критический путь).

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

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

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

Подготовка оптимизационной модели в MS EXCEL

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

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

Приведем алгоритм работы с Поиском решения , который советуют сами разработчики ( ]]> www.solver.com ]]> ):

  • Определите ячейки с переменными модели (decision variables);
  • Создайте формулу в ячейке, которая будет рассчитывать целевую функцию вашей модели (objective function);
  • Создайте формулы в ячейках, которые будут вычислять значения, сравниваемые с ограничениями (левая сторона выражения);
  • С помощью диалогового окна Поиск решения введите ссылки на ячейки содержащие переменные, на целевую функцию, на формулы для ограничений и сами значения ограничений;
  • Запустите Поиск решения для нахождения оптимального решения.

Проделаем все эти шаги на простом примере.

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

Необходимо загрузить контейнер товарами, чтобы вес контейнера был максимальным. Контейнер имеет объем 32 куб.м. Товары содержатся в коробках и ящиках. Каждая коробка с товаром весит 20кг, ее объем составляет 0,15м3. Ящик — 80кг и 0,5м3 соответственно. Необходимо, чтобы общее количество тары было не меньше 110 штук.

Данные модели организуем следующим образом (см. файл примера ).

Переменные модели (количество каждого вида тары) выделены зеленым. Целевая функция (общий вес всех коробок и ящиков) – красным. Ограничения модели: по минимальному количеству тары (>=110) и по общему объему ( =СУММПРОИЗВ(B8:C8;B6:C6) – это общий вес всех коробок и ящиков, загруженных в контейнер. Аналогично рассчитываем общий объем — =СУММПРОИЗВ(B7:C7;B8:C8) . Эта формула нужна, чтобы задать ограничение на общий объем коробок и ящиков ( =СУММ(B8:C8) . Теперь с помощью диалогового окна Поиск решения введем ссылки на ячейки содержащие переменные, целевую функцию, формулы для ограничений и сами значения ограничений (или ссылки на соответствующие ячейки). Понятно, что количество коробок и ящиков должно быть целым числом – это еще одно ограничение модели.

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

Совет : в статье " Поиск решения MS EXCEL. Экстремум функции с несколькими переменными. Граничные условия заданы уравнениями " показано решение задачи, в которой функция и граничные условия заданы в явном виде, т.е. математическими выражениями типа F(x1, x2, x3)=x1+2*x2+6*x3, что существенно облегчает построение модели, т.к. не требуется особо осмыслять задачу: можно просто подставить переменные x в поле переменные, а ограничения ввести в соответствующее поле окна Поиска решения.

Резюме

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

Поиску решения не удалось найти решения (Solver could not find a feasible solution)

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

Примечание . О влиянии нелинейности модели на результаты расчетов можно прочитать в последнем разделе статьи Поиск решения MS EXCEL (4.3). Выбор места открытия нового представительства .

В любом случае (линейном или нелинейном), Вы должны сначала проанализировать модель на непротиворечивость ограничений, то есть условий, которые не могут быть удовлетворены одновременно. Чаще всего это связано с неправильным выбором соотношения (например, =) или граничного значения. Если, например, в рассмотренном выше примере, значение максимального объема установить 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

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