Функция поиск решения

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

Задача №1 Определить минимальную длину забора для прямоугольного участка в 600 кв. м.

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

Ячейки переменных данных Целевая ячейка Формула целевой ячейки

Рисунок 30 Создание ячеек переменных данных А1, А2 и целевой ячейки А3

Ячейка А4 будет показана на рисунке 31 она имеет в своём составе формулу площади данного участка, составленную из произведения значений, находящихся в ячейках А1 и А2.

Ячейка формулы площади Формула ячейки А4

Рисунок 31 Ячейка формулы площади данного участка

Далее выделив целевую ячейку А3 нажимаем кнопку «Данные» и находим кнопку «Поиска решений» нажимаем так же и ее на экране появиться окно функции «Параметры поиска решений» с выделенной оптимизированной целевой функцией ячейки А3. После этого заполняем окно необходимыми параметрами. В окне оптимизировать целевую функцию выбор сделан полуавтоматически при выделении целевой ячейки и открытии окна это видно из рисунка 32 и рисунка 33. Далее по порядку выбираем значение «Минимум» так, как и требуется из задания это видно в окне как, кнопки следующие за надписью «До:».

Выделенная целевая ячейка и наименование ячейки в окне поиска

Рисунок 32 Окно «Параметров поиска решения»

Далее выбираем стоку под наименованием «Изменяя ячейки переменных данных» заносим в её окно эти значения путем выделения нужных ячеек, а именно в нашем случае ячеек А1 и А2 их имена тут же появляются в этой строке. Всё это видно из рисунка 33. Но нам так же необходимо занести параметры ограничений что бы система расчетов могла знать в каких пределах следует выполнять вычисления. Это делается в окне «В соответствии с ограничениями» это делается с помощью кнопки «Добавить» слева от этого окна. Это видно из рисунка 34. На котором изображено окно «Добавление ограничений». В это окно заносятся имена ячеек причем все имена разделяются

Наименование ячеек переменных данных Наименование целевой ячейки

Выбор параметра требуемого значения

Рисунок 33 Заполнение окна «Параметров поиска решений»

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

Так как у нас взяты значения 1 и 600 то необходимы ограничения, что бы значения ячеек А1 и А2 каждое соответственно были как больше 1 так и меньше 600 и были целыми числами, и необходимо учесть значение площади из ячейки А4 все это отображено на рисунке 35.

Ячейка переменного параметра Знак условия Значение условия

Рисунок 34 Окно параметра добавления ограничений

Так же там видно, что метод поиска решений для нашего варианта выбран следующий с наименованием «Поиск решения нелинейных задач методом ОПГ (обобщенного приведенного градиента)», под этой кнопкой скрыты методы: «Поиск решения задач симплекс – методом» и «Эволюционный поиск решения». Значение «Сделать переменные без ограничений не отрицательными» оставляем без изменений, то есть включенным. После введенных параметров нажимаем кнопку «Найти решение». После приведение в действие табличного процессора Excel и не долгого времени, потраченного на расчеты на экране, появляются результаты расчетов в соответствующих ячейках. А именно в ячейках А1 и А2 длинна сторон участка в метрах, общая длина забора в метрах в целевой ячейке А3, ячейка же А4 не изменилась так как в нашем случае это величина известна и постоянна. Рядом мы видим окно «Результаты поиска решений» это видно из рисунка 36.

В этом окне мы видим описание метода с помощью которого были произведены расчеты, возможность сохранить новые или вернуть прежние параметры, в какой форме сохранить отчеты, сохранить ли сценарий или вернуться в диалоговое окно. Выделив кнопку «Результаты» в окне «Результаты поиска решения» → «Отчеты» и нажав кнопку «ОК» внизу листа появляется ярлычок с наименованием «Отчет о результатах 1», перейдя по которому ы окажемся на листе с результатами поиска рисунок 37 и рисунок 37а. На рисунках представлен лист, отчета на котором видно название отчета, параметры вычислений, показаны исходные и полученные данные, а также параметры ограничений в виде сводных таблиц. На этом решение поставленной задачи заканчивается. Результат решения, следующий что при площади участка в 600 м²,оптимальной длинной, является длинна в 98 м/п и величине сторон 24 и 25 метров.

Кнопка «Найти решение»

Запись параметров ограничений с именем ячейки и значением Наименование метода

Рисунок 35 Заполнение окна «В соответствии с ограничениями»

Ячейки с измененными значениями Описание метода и возможные операции

Рисунок 36 Окно «Результатов поиска решений»

Наименование отчета Текстовое описание с примерами

Рисунок 37 Лист отчета часть 1

Параметры ограничений

Рисунок 37а Лист отчета часть 2

Задача 2 Определить, какую прямоугольную площадь можно огородить прямоугольным забором длиной 100 м.

Эта задача является подобием первой задачи и здесь ее нет смысла описывать во второй раз, остановимся лишь на основных параметрах отличных от первой. На рисунке 38 показан ввод данных в ячейки А1 и А2 и формулы в ячейку А3 с формулой площади.

Целевая ячейка А3 Ячейки А1 и А2 для ввода данных

Рисунок 38 Ввод данных задачи №2

В ячейку А4 заводиться формула длинны данного забора рисунок 39

Формула длинны данного забора

Рисунок 39 Формула длинны данного забора

После повторения манипуляций, описанных в первой задаче мы выводим окно «Результата поиска решений» как показано на рисунке 40. И слева мы видим полученные результаты. Далее выделяем кнопку «Результаты», нажимаем кнопку «ОК», и результаты выводятся на другом листе в виде листа отчета, как и в предыдущей задаче. Рисунок 41 показывает часть листа отчета с введенными и новыми данными. Из полученных результатов решение задачи составляет, что при длине забора в 100 м/п оптимальным решением является длины сторон по 25 метров и огороженной площади в 625 м².