48) Подбор параметра. Таблицы подстановки.

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

Инструмент Подбор параметра позволяет методом последовательных итераций найти приближенное решение некоторой целевой функции (уравнения) с одним неизвестным.

Следует отметить, что уравнение может иметь более одного решения. Поэтому рекомендуется выполнить подбор параметра для разных начальных значений Х, указывая положительные, отрицательные, большие и малые значения. В нашем примере установите начальное значение А2=–1 и повторите подбор. Решение будет таким же.

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

Следует сформировать таблицу подстановки с одной переменной, чтобы введенные значения были расположены либо в столбце (ориентированные по столбцу), либо в строке (ориентированные по строке). Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода.

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

Выполните одно из следующих действий.

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

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

Выделите диапазон ячеек, содержащий формулы и значения подстановки.

В меню Данные выберите команду Таблица.

Выполните одно из следующих действий:

если значения в таблице расположены по столбцам, введите ссылку на ячейку ввода в поле Подставлять значения по строкам в;

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

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

В ячейку листа введите формулу, которая ссылается на две ячейки ввода.

В приведенном ниже примере, где исходные значения формулы введены в ячейки B3, B4 и B5, формулу =ПЛТ(B3/12;B4;-B5) следует ввести в ячейку C2.

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

В приведенном ниже примере различные процентные ставки следует ввести в ячейки C3, C4 и C5.

Введите значения подстановки для второй переменной правее формулы в той же строке.

В приведенном ниже примере срок погашения ссуды (в месяцах) следует ввести в ячейки D2 и E2.

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

В приведенном ниже примере следует выделить диапазон C2:E5.

В меню Данные выберите команду Таблица подстановки.

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

В приведенном ниже примере в поле Подставлять значения по столбцам в следует ввести B4.

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

В приведенном ниже примере в поле Подставлять значения по строкам в следует ввести B3.

Нажмите кнопку OK.