При создании таблиц подстановки также распространены следующие ошибки

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

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

♦ При выделении области для создания таблицы подстановки в нее не включена ячейка с формулой.

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

Лабораторная работа №9

Тема: Поиск решения

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

Средство Поиск решения предназначено для решения задач оптимизации. Это средство широко применяется для решения финансовых, деловых задач и задач управления ресурсами (понимая «ресурсы» в самом широком смысле: персонал, оборудование, материалы, время и т.д.). Средство Поиск решения также применяется в научных исследованиях для решения математических уравнений и выполнения самых разнообразных расчетов.

Терминология:

· Целевая ячейка — ячейка с формулой, в которой Поиск решения установит заданное значение или для которой найдет минимально или максимально возможные значения.

· Целевая функция — это термин из теории оптимизации, который описывает цель, которую мы хотим достичь, решая данную задачу (и используя для этого Поиск решения). Здесь «цель» заключается в том, чтобы формула в целевой ячейке достигла определенного значения.

· Изменяемые ячейки — ячейки, значения в которых будет варьировать Поиск решения для того, чтобы достичь требуемого значения целевой функции.

· Ограничения— условия, налагаемые на возможные значения изменяемых ячеек.

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

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

Поскольку средство Поиск решения не всегда устанавливается при инсталляции программы Excel, прежде чем использовать это средство, убедитесь, что на Вкладке Данные есть команда Поиск решения. Если нет такой команды, нажмите правой кнопкой мыши главную кнопку программы, выберите Настройка панели быстрого доступа. В левой панели выберите команду Надстройки. Нажмите кнопку Перейти… и в открывшемся окне в списке Доступные надстройки установите флажок Поиск решения. Затем щелкните на кнопке ОК — на вкладке Данные должна появиться команда Поиск решения. Если в списке Доступные надстройки нет опции Поиск решения, необходимо переустановить саму программу Excel.

Использование средства «Поиск решения» для решения простых математических задач.

На рабочем листе подготовьте таблицы как на рисунке 1:

Рис. 1. Таблицы данных для вычисления расстояния и объёма.

1. Задача вычисления объёма

Предположим, что ширина параллелепипеда равна 4 и объём равен 80. необходимо найти длину и высоту параллелепипеда при условии, что все его параметры выражаются целыми числами.

1.1. На вкладке Данные в группе Анализ выберите кнопку Поиск решения и в открывшемся окне щёлкните по кнопке Восстановить.

1.2. В поле Установить целевую ячейку введите В6, щёлкнув левой кнопкой мыши по соответствующей ячейке.

1.3. Выберите переключатель значению и введите в соседнее поле число 80.

1.4. Поставьте курсор в поле Изменяя ячейки и выделите диапазон В3:В5. Нажмите кнопку Добавить.

1.5. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку введите В4. В соседнем раскрывающемся списке выберите элемент =, а в поле Ограничение введите число 4, щёлкните по кнопке Добавить.

1.6. В поле Ссылка на ячейку введите диапазон В3:В5. В раскрывающемся списке выберите элемент цел. и нажмите кнопку ОК. Вид диалогового окна Поиск решения должен совпадать с тем, что представлено на рисунке 2.

Рис. 2. Окно средства Поиск решения. Вычисление объёма

1.7. Нажмите кнопку Выполнить, затем ОК. Сравните полученные результаты с рисунком 3.

Рис. 3. Результаты поиска решения при вычислении объёма