Шестаков В.С. Оптимизация параметров горных машин. Учебное пособие (811777), страница 13
Текст из файла (страница 13)
Но такой вариантбудет затруднителен при большом числе данных, а также при наличии нескольких экстремумов. Причем минимальное значение не будет оставаться на одном месте при изменении исходных данных, абудет смещаться, поэтому при каждом изменении исходных данныхнеобходимо будет снова зрительно выявлять оптимальное решение,а это будет приводить к утомляемости пользователя. Поэтому желательно в составленную форму добавить средство для автоматического выявления наилучшего решения и выводить результаты втаблицу.
В представленном примере эти результаты выведены втаблицу под графиком функции.Для получения этой таблицы проделано следующее.1. В ячейку G24 введена функция поиска минимального значения в виде формулы =МИН(D13:D31) следующим образом:- щелчок по кнопке «Вставка функции» на стандартной панели инструментов;- выбор категории «статистические» в левом окне панели«Мастер функций»;- выбор функции «МИН» в левом окне панели «Мастерфункций»;- щелчок по полю «Число 1» панели задания аргументовПример опфункции «МИН»;тимизации7071-выделение диапазона «С13:С28» (навести курсор мыши наС13, нажить левую кнопку и, не отпуская ее, протянутькурсор до ячейки С28, затем отпустить кнопку);- щелчок по кнопке «ОК» панели задания аргументов функции «МИН».После этих действий в ячейке G24 будет выведено минимальное значение площади.2.
Выявим необходимые функции для определения диаметра ивысоты, соответствующих минимальной площади. Для этих целейпредназначены функции категории «Ссылки и массивы». Нам требуется по значению в ячейке G24 найти строку, в которой расположено такое же значение в столбце С в строках с 13 по 28. Рассмотрим список указанной категории.АДРЕС - создает адрес ячейки в виде текста, используя номерстроки и номер столбца. АДРЕС(номер_строки; номер_столбца;тип_ссылки;а1; имя_листа)ВЫБОР - использует номер_индекса, чтобы выбрать и вернутьзначение из списка аргументов-значений. Функция ВЫБОР используется, чтобы выбрать одно значение из списка, в котором можетбыть до 29 значений.ВПР – определяет значение в крайнем левом столбце таблицыи возвращает значение в той же строке из указанного столбца таблицы.ГПР - ищет значение в верхней строке таблицы или массивазначений и возвращает значение в том же столбце из заданнойстроки таблицы или массива.
Функция ГПР используется, когдасравниваемые значения расположены в верхней строке таблицыданных, а возвращаемые значения расположены на несколько строкниже. Если сравниваемые значения расположены в столбце слева отискомых данных, то следует использовать функцию ВПР.ДВССЫЛ - возвращает ссылку, заданную текстовой строкой.Ссылки немедленно вычисляются для вывода их содержимого. Используется, если требуется изменить ссылку на ячейку в формуле,не изменяя саму формулу.ИНДЕКС - возвращает значение или ссылку на значение изтаблицы или интервала.
Функция ИНДЕКС() имеет две синтаксические формы: ссылка и массив. Ссылочная форма всегда возвращаетссылку; форма массива всегда возвращает значение или массив зна72чений. ИНДЕКС(массив; номер_строки; номер_столбца) возвращает значение указанной ячейки или массив значений в аргументемассив. ИНДЕКС(ссылка; номер_строки; номер_столбца; номер_области) возвращает ссылку на указанную ячейку или ячейкив аргументе «ссылка».ПРОСМОТР - возвращает значение из строки, из столбца илииз массива. Функция ПРОСМОТР имеет две синтаксические формы: вектор и массив.
Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только однойстроки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбцаили строки. Форма массива функции ПРОСМОТР просматриваетпервую строку или первый столбец массива, находит указанноезначение и возвращает значение из аналогичной позиции последнейстроки или столбца массива.ПОИСКПОЗ - возвращает относительное положение элементамассива, который соответствует заданному значению указаннымобразом.ПОИСКПОЗ(искомое_значение;просматриваемый_массив; тип_сопоставления).
Искомое_значение - это значение, для которого ищется соответствие в аргументе просматриваемый_массив. Например, когда Вы ищете номер телефона в телефонной книге, Вы используете имя человека как искомое_значение,но значение, которое Вам нужно получить, - это сам номер телефона. Искомое_значение может быть значением (числом, текстом илилогическим значением) или ссылкой на ячейку, содержащую число,текст или логическое значение.
Просматриваемый_массив - этонепрерывный интервал ячеек, возможно, содержащих искомые значения. Просматриваемый_массив может быть массивом или ссылкой на массив. Тип_сопоставления- это число -1, 0 или 1.Тип_сопоставления указывает, как Microsoft Excel сопоставляетискомое_значениесо значениями в аргументе просматриваемый_массив. Если тип_сопоставления равен 1, то функцияПОИСКПОЗ находит наибольшее значение, которое равно илименьше, чем искомое_значение.
Просматриваемый_массив долженбыть упорядочен по возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ,ИСТИНА.. Если тип_сопоставления равен 0, то функцияПОИСКПОЗ находит первое значение, которое в точности равноаргументу искомое_значение. Просматриваемый_массив может73быть в любом порядке.·Если тип_сопоставления равен -1, тофункция ПОИСКПОЗ находит наименьшее значение, которое равнои больше, чем искомое_значение.
Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1,0, -1, -2, ..., и так далее.·Если тип_сопоставления опущен, топредполагается, что он равен 1.СТРОКА - возвращает номер строки, определяемой ссылкой.СТРОКА(ссылка). Ссылка - это ячейка или интервал ячеек, для которых определяется номер строки.
Например, СТРОКА(A3) равняется 3.Для нашей задачи при поиске диаметра и высоты, соответствующей минимальной площади могла бы быть использована функция ВПР, но для нее площадь должна располагаться в левом столбце, поэтому для применения этой функции придется переставитьстолбцы местами, что в данном случае не очень подходит. В инженерной практике аргумент ставит слева, а значение функции - правее. Аналогично происходит и с функцией ПРОСМОТР.Из приведенных функций хорошо подходит функцияИНДЕКС, но для ее применения требуется указание номера строки,поэтому дополнительно к ней необходима функция поиска строки,в которой находится минимальное значение площади.
Для поискастроки может быть применена функция ПОИСКПОЗ. Для упрощения написания формулы для вывода результатов работы функцииПОИСКПОЗ можно применить отдельную ячейку. В приведенномпримеревячейкеН24введенафункция=ПОИСКПОЗ(G24,C10:C28,0). Результатом расчета по этой формуле получено значение 9. В ячейке G25 введена формула=ИНДЕКС(А10:C28,Н24,1),авячейкеG26=ИНДЕКС(A10:C28,H24,2). Результаты расчета по этим формулампредставлены в примере расчета на рис. 4.3. Проверьте работу полученных формул. При изменении исходных данных в таблице всегда будет выведено оптимальное решение.2-й вариант возможног решения средствами Excel.
Для решения подобных задач по определению оптимального решения соответствующего минимуму или максимуму функций в Excel имеется специальная надстройка «Поиск решения».74Рассмотрим последовательность действий при постановке задачи и оптимизации модели с помощью процедуры поиска решения.1. В меню «Сервис» выберите команду «Поиск решения».2. Если команда «Поиск решения» отсутствует в меню «Сервис», установите надстройку «Поиск решения».2.1.
В меню «Сервис» выберите команду «Надстройки».2.2. Нажмите кнопку «Обзор», чтобы найти надстройку, которой нет в окне «Список надстроек».2.3. Установите в окне «Список надстроек» флажок тойнадстройки, которую необходимо загрузить, а затемнажмите кнопку OK.2.4. Следуйте инструкциям программы установки, если ониимеются.3. В поле «Установить целевую ячейку» введите адрес конечной ячейки или присвоенное ей имя. Конечная ячейка должна содержать формулу.4.
Выполните одно из следующих действий:o чтобы максимизировать значение конечной ячейки путемизменения значений влияющих ячеек, установите переключатель вположение максимальному значению;o чтобы минимизировать значение конечной ячейки путемизменения значений влияющих ячеек, установите переключатель вположение минимальному значению;o чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению ивведите в соответствующее поле требуемое число.5. В поле «Изменяя ячейки» введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейкидолжны быть прямо или косвенно связаны с конечной ячейкой.
Допускается задание до 200 изменяемых ячеек.6. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку «Предположить».7. В поле «Ограничения» введите все ограничения, накладываемые на поиск решения. Ограничения накладываются на значения изменяемых ячеек, конечных ячеек или других ячеек, прямоили косвенно связанных друг с другом, задаваемые при постановкезадачи.757.1. В разделе «Ограничения» диалогового окна «Поиск решения» нажмите кнопку «Параметры».7.2. В поле «Ссылка на ячейку» введите адрес или имя ячейки, на значение которой накладываются ограничения(рис. 4.4).Рис. 4.4. Панель для задания и изменения ограничений7.3 Выберите из раскрывающегося списка условный оператор (<=, =, >=, цел или двоич), который должен располагаться между ссылкой и ограничением.7.4.
В поле «Ограничение» введите число, ссылку на ячейкуили формулу.7.5. Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку «Добавить», Чтобы принять ограничение и вернуться в диалоговое окно «Поиск решения», нажмите кнопку OK.После выполнения указанных действий в панели будетинформация, показанная на рис. 4.5.8. Нажмите кнопку «Выполнить».
После щелчка по кнопке«Выполнить» на экран выведется панель, показанная на рис. 4.6.Панель используется для настройки вывода итогового сообщения инайденного решения. Чтобы сохранить найденное решение на листе, выберите в диалоговом окне вариант «Сохранить найденное решение». Для восстановления исходных данных, выберите вариант«Восстановить исходные значения».Отчеты - служит для указания типа отчета, размещаемого на отдельном листе книги. Могут быть выведены следующие отчеты.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
