Toderelt.ru

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

Как пользоваться инструментом «Поиск решения» в Excel

ITGuides.ru

Функция поиска решения пригодится при необходимости определить неизвестную величину

Функция поиска решения пригодится при необходимости определить неизвестную величину

Табличный процессор Microsoft Excel может выполнять не только простые операции с числами (сложение, умножение), расчет суммы или среднего значения. У этой программы имеется очень мощный функционал, который позволяет решать задачи разной сложности. Например, Эксель может оптимизировать значения в таблице, подставляя их таким образом, чтобы они удовлетворяли определенным критериям. Для этого программа оснащена специальными средствами для анализа данных: первый — это подбор параметра, а второй — поиск решения.

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

Как включить функцию «Поиск решения»

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

funkciya-poisk-resheniya-v-excel-vklyuchenie-primer-ispolzovaniya-so-skrinshotami

  1. Открываем «Файл», нажимая на соответствующее название.
  2. Кликаем на раздел «Параметры».
  3. Затем выбираем подраздел «Надстройки». Тут будут отображены все надстройки программы, внизу появится надпись «Управление». С правой стороны от нее будет всплывающее меню, где следует выбрать «Надстройки Excel». Потом нажимаем «Перейти». 1
  4. На мониторе высветится дополнительное окно «Надстройки». Устанавливаем флажок возле искомой функции и кликаем «ОК».
  5. Нужная функция появится на ленте справа от раздела «Данные».

О моделях

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

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

Типовые задачи по оптимизации:

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

Важно! Чтобы формализовать поставленную задачу, необходимо создать модель, отражавшую основные параметры предметной области. В Эксель модель является комплексом формул, использующих переменные. Рассматриваемая опция подыскивает такие показатели, чтобы целевая функция была больше (меньше) либо равнялась указанному значению.

funkciya-poisk-resheniya-v-excel-vklyuchenie-primer-ispolzovaniya-so-skrinshotami

2

Подготовительный этап

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

Нашей задачей станет вычисление скидки, на которую умножаются суммы по реализации различной продукции. Эти 2 элемента связываются формулой, прописываемой так: =D13*$G$2. Где в D13 прописывается суммарное количество по реализации, а $G$2 – адрес искомого элемента.

funkciya-poisk-resheniya-v-excel-vklyuchenie-primer-ispolzovaniya-so-skrinshotami

3

Применение функции и ее настройка

Когда формула будет готова, необходимо использовать непосредственно саму функцию:

  1. Нужно переключиться в раздел «Данные» и нажать «Поиск решения».
  1. Откроются «Параметры», где задаются требуемые настройки. В строке «Оптимизировать целевую функцию:» следует указать ячейку, где выводится сумма по скидкам. Есть возможность прописать координаты самостоятельно или выбрать из документа.
  1. Далее нужно перейти к настройкам прочих параметров. В разделе «До:» есть возможность задать максимальную и минимальную границу либо точное число.
  1. Потом заполняется поле «Изменяя значения переменных:». Здесь вносятся данные искомой ячейки, которая содержит конкретное значение. Координаты прописываются самостоятельно или кликается соответствующая ячейка в документе.
  1. Затем редактируется вкладка «В соответствии с ограничениями:», где задаются ограничения применяемых данных. К примеру, исключаются десятичные дроби либо отрицательные числа.
  1. После открывается окно, которое позволяет добавлять ограничения при расчетах. В начальной строке указываются координаты ячейки либо целого диапазона. Следуя условиям задачи, указываются данные искомой ячейки, где выводится показатель скидки. Затем определяется знак сравнения. Устанавливается «больше либо равно», чтобы конечное значение не было со знаком «минус». «Ограничение», устанавливаемое в 3 строке, в такой ситуации равняется 0. Возможно выставить также ограничение посредством «Добавить». Последующие действия аналогичны.
  1. Когда выполнены вышеописанные действия, в самой большой строке появляется установленное ограничение. Перечень бывает большим и будет зависеть от сложности расчетов, однако в конкретной ситуации достаточно 1 условия.
  1. Кроме того, возможно выбирать другие дополнительные настройки. Внизу с правой стороны присутствует опция «Параметры», которая позволяет это сделать.
  1. В настройках можно выставить «Точность ограничения» и «Пределы решения». В нашей ситуации использовать эти опции нет нужды.
  1. Когда настройки завершены, запускается сама функция – нажимается «Найти решение».
  1. После программа проводит требуемые расчеты и выдает конечные расчеты в необходимых ячейках. Потом открывается окно с результатами, где сохраняются/отменяются итоги либо настраиваются параметры поиска по новой. Когда данные соответствуют требованиям, то найденное решение сохраняется. Если заранее установить отметку «Вернуться в диалоговое окно параметров поиска решения», будет открыто окно с настройками функции.
  1. Есть вероятность, что расчеты оказались ошибочными или есть необходимость в изменении исходных данных в целях получения других показателей. В такой ситуации требуется вновь открыть окно с настройками и перепроверить сведения.
  2. Когда данные точны, можно воспользоваться альтернативным методом. В этих целях нужно нажать на текущий вариант и из появившегося списка выбрать самый подходящий способ:
  • Поиск решения посредством обобщенного градиента для нелинейных задач. По умолчанию применяется такой вариант, однако возможно воспользоваться и другими.
  • Поиск решения для линейных задач на основе симплекс-метода.
  • Использование эволюционного поиска в целях выполнения задачи.

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

  1. Когда получена искомая скидка, остается ее применить для подсчета суммы скидок по каждому наименованию. В этих целях выделяется начальный элемент столбика «Сумма скидки», прописывается формула «=D2*$G$2» и жмется «Enter». Значки доллара проставляются, чтобы во время растягивания формулы на смежные строчки G2 не изменялась.
  1. Теперь будет получена сумма скидки для начального наименования. Затем следует навести курсор на угол ячейки, когда он станет «плюсом», зажимается ЛКМ и формула растягивается на необходимые строки.
  2. После этого таблица будет окончательно готова.
Читать еще:  Три сайта для создания кроссвордов онлайн

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

Данная опция полезна при применении различных вариантов ограничений.

  1. В меню «Параметры поиска решения» следует нажать «Загрузить/сохранить».
  2. Вводится диапазон для области модели и нажимается «Сохранить или Загрузить».

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

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

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

Нужно провести загрузку контейнера тарой, чтобы его масса была максимальной. Емкость обладает объемом в 32 куб. м. Наполненная коробка имеет вес в 20 кг, ее объем равен 0,15 куб. м. Ящик – 80 кг и 0,5 куб. м. Требуется, чтобы общее число тары составляло не менее 110 шт. Данные организовываются так:

funkciya-poisk-resheniya-v-excel-vklyuchenie-primer-ispolzovaniya-so-skrinshotami

18

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

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

Поиску решения не удалось найти решения

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

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

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

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

В вышеуказанном примере показатель максимального объема указан 16 куб. м вместо 32, потому такое ограничение противоречит показателям по минимальным количествам мест, поскольку ему будет соответствовать число 16,5 куб. м.

funkciya-poisk-resheniya-v-excel-vklyuchenie-primer-ispolzovaniya-so-skrinshotami

19

Заключение

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

Подготовительный этап

Добавить функцию на ленту программы – половина дела. Нужно еще понять принцип ее работы.

Итак, у нас есть данные про продаже товаров, представленные в табличном виде.

Подготовительный этап перед использованием фукнции

И перед нами стоит задача – назначить каждому товару скидку таким образом, чтобы сумма по всем скидкам составила 4,5 млн. рублей. Она должна отобразиться в отдельной ячейке, которая называется целевой. Ориентируясь на нее мы должны рассчитать остальные значения.

Подготовительный этап перед использованием фукнции

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

Читать еще:  Как сделать удаленный доступ к телефону

Данные ячейки (искомая и целевая) связываем вместе формулой, которую пишем в целевой ячейке следующим образом: =D13*$G$2, где ячейка D13 содержит итоговую сумму по продажам всех товаров, а ячейка $G$2 – абсолютные (неизменные) координаты искомой ячейки.

Подготовительный этап перед использованием фукнции

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Программы для печати фотографий

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

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

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

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

Литература [ править | править код ]

  1. Акулич И. Л. Математическое программирование в примерах и задачах: Учеб. пособие для студентов эконом. спец. вузов. — М.: Высш. шк., 1986.
  2. Гилл Ф., Мюррей У., Райт М. Практическая оптимизация. Пер. с англ. — М.: Мир, 1985.
  3. Коршунов Ю. М., Коршунов Ю. М. Математические основы кибернетики. — М.: Энергоатомиздат, 1972.
  4. Максимов Ю. А.,Филлиповская Е. А. Алгоритмы решения задач нелинейного программирования. — М.: МИФИ, 1982.
  5. Максимов Ю. А. Алгоритмы линейного и дискретного программирования. — М.: МИФИ, 1980.
  6. Корн Г., Корн Т. Справочник по математике для научных работников и инженеров. — М.: Наука, 1970. — С. 575—576.

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

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

Начиная с этого .

enter image description here

Я получил эти результаты .

enter image description here

используя этот код .

Вещи для диагностики:

  • SolverReset вызвал очень странное поведение — при его использовании последующие вызовы SolverSolve заставили Excel переключиться на ручной расчет.
  • Первый вызов SolverOK создаст проблему, но последующие вызовы не изменят проблему.
  • SolverAdd, SolverChange и т. Д. Влияют на ограничения, но не (по-видимому) На базовую настройку проблемы.
  • Печать в чередующихся строках в VBA

Нужно распечатать таблицу любых чисел, используя VBA в excel.то есть пустую строку после каждой строки . Ниже приведено то, что я написал, чтобы напечатать таблицу в последовательных строках, но я не знаю, как я могу напечатать результат в альтернативных строках? Sub table() a = InputBox(Enter.

Существует код, который я использую для поиска определенных слов в строке VBA. В настоящее время я использую InSTR : tag1 = InStr(1, dish, keyword, vbTextCompare) Однако проблема заключается в том, что поиск сравнивает строки, а не слова. E.g. если я ищу яйцо в качестве ключевого слова в строке.

Похожие вопросы:

Можно ли выполнить рекурсивный поиск и замену определенной строки в скрипте word VBA? В частности, я хотел бы заменить все экземпляры : на:. Я много искал, но ничего не нашел. Я не прошу кодового.

Я унаследовал электронную таблицу Excel со многими функциями VBA, и неясно, где (или даже если) многие из них используются. Есть ли способ найти использование функции VBA или, возможно, поиск ее.

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

Есть ли способ искать только в строках java в Intellij IDEA ? Я искал и не мог найти ничего по этому поводу. Я должен рефакторинг запросов hibernate HQL написано как строки java. Если IDE.

Как лучше всего реализовать поиск по подстрокам в строках (около 5000 имен сущностей в приложении), которые сопоставляются с entityId. Я хочу реализовать его так, чтобы я мог помещать поисковые.

Нужно распечатать таблицу любых чисел, используя VBA в excel.то есть пустую строку после каждой строки . Ниже приведено то, что я написал, чтобы напечатать таблицу в последовательных строках, но я.

Существует код, который я использую для поиска определенных слов в строке VBA. В настоящее время я использую InSTR : tag1 = InStr(1, dish, keyword, vbTextCompare) Однако проблема заключается в том.

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

Я все еще довольно новичок в написании кода VBA, но я хотел бы решить следующую проблему: С помощью макро-рекордера я уже мог решить часть своей проблемы. Я могу искать разные тексты и заменять ALL.

Я учусь использовать excel VBA для автоматизации поиска в google с помощью моих кодов, показанных ниже. Однако я всегда сталкиваюсь с ошибкой времени выполнения 424: требуется объект в строке.

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector