Таблицы подстановки данных

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

Создание таблицы подстановки с одной переменной

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

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

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

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

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

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

4 Выберите команду Таблица подстановки в меню Данные. Появится окно диалога «Таблица подстановки».

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

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

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

Например,используйте таблицу подстановки с одной переменной, если необходимо отобразить то, как различные ставки влияют на месячные выплаты по закладным. В следующем примере ячейка D2 содержит формулу платежа =ППЛАТ(B3/12,B4,-B5), которая ссылается на ячейку ввода B3.

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

Создание таблицы подстановки с двумя переменными

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

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

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

3 Выделите диапазон ячеек, содержащий формулу и оба набора данных подстановки. 4 Выберите команду Таблица подстановки в меню Данные. 5 В поле Подставлять значения по столбцам в введите ссылку на ячейку ввода для значений подстановки в строке.

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

В следующем примере ячейка C2 содержит формулу платежа =ППЛАТ(B3/12,B4,-B5), которая использует две ячейки ввода: B3 и B4.

Добавление формулы в существующую таблицу подстановки с одной переменной

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

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

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

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

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

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

Если же данные расположены в строке, то эта ссылка вводится в поле Подставлять значения по строкам в.

Удаление рассчитанных значений из таблицы подстановки данных

Удаление отдельного значения невозможно. Так как рассчитанные значения содержатся в массиве, то необходимо удалить все значения. Убедитесь, что не выделены формулы и значения подстановки, если только не удаляется вся таблица.

1 Выделите в таблице все рассчитанные значения.

2 Выберите пункт Очистить в меню Правка, а затем – команду Содержимое.

Удаление всей таблицы подстановки данных

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

2 Выберите пункт Очистить в меню Правка, а затем – команду Все.

Сценарии

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

Создание сценария

1Выберите команду Сценарии в меню Сервис.

2 Нажмите кнопку Добавить.

3 Введите необходимое имя в поле Название сценария.

4 Введите ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки.

5 Установите необходимые флажки в наборе флажков Защита.

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

7 Введите необходимые значения в диалоговом окне Значения ячеек сценария.

8 Чтобы создать сценарий, нажмите кнопку OK.

Для создания дополнительных сценариев нажмите кнопку Добавить, а затем повторите шаги с 3 по 7. После завершения создания сценариев нажмите кнопку OK, а затем – кнопку Закрыть в диалоговом окне Диспетчер сценариев.

Правка сценария

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

1 Выберите команду Сценарии в меню Сервис. 2 Укажите название изменяемого сценария, а затем нажмите кнопку Изменить.

3 Исправьте необходимое. 4 Введите значения для изменяемых ячеек в диалоговом окне Значения ячеек сценария.

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

Удаление сценария

1 Выберите команду Сценарии в меню Сервис. 2 Укажите название удаляемого сценария, а затем нажмите кнопку Удалить.

Подбор параметра

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

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

Поиск определенного результата для ячейки с помощью подбора параметра другой ячейки

1 Выберите команду Подбор параметра в меню Сервис.

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

3 Введите искомый результат в поле Значение.

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

Поиск решения

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

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

Например, используя средства поиска решения, можно максимизировать прибыль, показанную в ячейке F7, с помощью изменения квартального бюджета (ячейки B5:E5), при бюджетном ограничении (ячейка F5) до 40000.