Анализ «что-если»

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

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

Таблица подстановки — это специальная таблица, в которой отображается, как изменение одной или двух входящих в формулу переменных влияет  на результат этой формулы. Excel  предусматривает  создание  таблиц  подстановки  двух  видов:  в  первом  варианте тестируется   изменение    значения   одной   или   нескольких   формул   при   изменении отдельного  параметра, а для второго варианта отображается изменение  значения только одной формулы, но при варьировании значений нескольких переменных. Чтобы создать любую из этих таблиц,  необходимо в меню Данные (Data) выбрать команду Таблица подстановки (Table).

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

Предположим,  вы  рассматриваете  предложение  по  покупке  дома,  которое  потребует взятия  в  банке  ссуды  в  размере  $200  000  с  рассрочкой  на  30  лет.  Вам  необходимо вычислить  размер  ежемесячных  выплат  при  различных  процентных  ставках.  Таблица подстановки для одной переменной, изображенная на рис. 18.1,  предоставляет нужную информацию.

Рис. 18.1. Построение таблицы подстановки начинается с ввода ряда различных процентных ставок и записи формулы с функцией ПЛТ

Итак, для решения поставленной задачи нужно выполнить следующие действия:

1.   Поместить в ячейки ВЗ:В9 значения потенциальных процентных  ставок, как на рис.

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

2.   Ввести величину кредита в ячейку вне таблицы данных. В нашем  примере мы ввели значение $200 000 в ячейку С1. Это позволит затем легко изменять величину ссуды при пробе различных сценариев.

3.   Ввести соответствующую формулу. В данном случае в ячейке С2 запишем формулу

=ПЛТ(А2/12;360;С1), где А2/12 — месячный процент, 360 — срок погашения ссуды в месяцах, а С1 — ссылка на величину суммы займа.

ПРИМЕЧАНИЕ

Обратите внимание, что эта формула ссылается на ячейку А2, которая в настоящий момент пуста. При обсчете формул Excel присваивает пустым ячейкам значение 0, поэтому функция в  ячейке  С2  возвращает  величину   ежемесячных  выплат,  рассчитанную  при  нулевой процентной ставке.  То  есть ячейка А2, называемая ячейкой ввода, — это фиксированная ячейка, через которую Excel будет в дальнейшем производить расчет,  подставляя в нее значения  из  входного  диапазона.  На  самом  деле,  так  как  хранящееся  в  этой  ячейке значение не изменяется, в ее качестве может выступать любая другая ячейка вне диапазона таблицы данных.

4.   Закончив ввод исходных значений и формулы, выделите диапазон таблицы данных — минимальный прямоугольный блок, захватывающий формулу и весь входной диапазон. В нашем случае нужно выделить диапазон В2:С9 и в меню Данные выбрать команду Таблица подстановки.

5.   На экране появится диалоговое окно Таблица подстановки (Table),  представленное на  рис.  18.2.  Местонахождение  ячейки  ввода   указывается  в  поле  Подставлять значение по столбцам в (Row  Input Cell) или в поле Подставлять значения по строкам в  (Column Input Cell). Ячейка ввода — это ячейка, на которую  ссылается формула таблицы подстановки, в нашем примере ею является ячейка А2. Если входной диапазон является строкой, ссылка на  входную ячейку задается в поле Подставлять значения  по  столбцам  в,  если  же  наоборот  —  столбцом  (как  в  данном  случае), используется поле Подставлять значение по строкам в.

Рис. 18.2. В диалоговом окне Таблица подстановки задается входная ячейка

6.   Осталось нажать кнопку  ОК. Excel поместит результаты  вычисления  формулы для каждого входного значения в свободные  ячейки диапазона таблицы подстановки. В нашем примере Excel выведет семь значений в диапазон СЗ:С8, как показано на рис. 18.3.

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

При     создании     таблицы     подстановки     программа     занесла     формулу     массива

{ТАБЛИЦА=(;А2)} в каждую ячейку диапазона результатов, а в целом в диапазон СЗ:С9. По этой формуле вычисляется значение функции ПЛТ для каждого из значений входного диапазона в столбце В. После  построения таблицы можно в любой момент изменять исходные значения, и результат вычислений сразу же будет отображаться на экране.

ПРИМЕЧАНИЕ

Функция ТАБЛИЦА является скрытой (internal) функцией Excel. Это означает, что ее нельзя выбрать из списка диалогового окна Мастера функций или ввести вручную.

Таблица подстановки с несколькими формулами

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

Рассмотрим на примере такой важный вопрос, как покупка дома. Пусть необходимая для этого сумма берется в банке и ее размер — $180 000 со сроком погашения 30 лет. Сейчас вам важно определить размер ежемесячных платежей по кредиту, а также сравнить его с выплатами для ссуды в $200 000 (рис. 18.3). Здесь достаточно расширить представленную ранее таблицу, включив в нее обе формулы, в следующем порядке:

1.   Занесите новую формулу =ПЛT(A2/12;360;Dl) в ячейку D2. Обратите  внимание, что эта формула так же, как и первая, ссылается на ячейку А2.

2.   Введите величину ссуды ($180 000) в ячейку D1 и выделите диапазон таблицы B2:D9.

3.   Выберите  в  меню  Данные  команду  Таблица  подстановки  и   поместите  в  поле Подставлять значения по строкам в диалогового окна Таблица подстановки ссылку на ячейку А2 ($А$2). Результат вычислений представлен на рис. 18.4.

Рис. 18.4. В этой таблице подстановки рассчитываются размеры ежемесячных отчислений по разным кредитам для различных процентных ставок

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

До  сих  пор  нами  вычислялись  значения  по  одной  или  нескольким   формулам  при различных значениях одной переменной. Но, предположим, требуется рассчитать размер ежемесячных выплат по кредиту в $200 000 не только для разных процентных ставок, но и в  зависимости от срока выплат — нужно узнать, как их  продолжительность влияет на размер месячного платежа.

Чтобы создать такую таблицу, выполните следующие действия:

1.  Как  и  прежде,  введите  в  диапазон  ВЗ:В9  величины  процентных   ставок.  Затем заполните данными второе множество (продолжительность выплат по ссуде в месяцах) в строке, расположенной непосредственно выше первой ячейки диапазона процентных ставок, так, как это показано на рис. 18.5. В нашем  случае второй набор входных данных находится в диапазоне C2:F2.

2.  После этого вставьте значение размера ссуды в ячейку вне диапазона таблицы данных, например в ячейку 12. Теперь пришло время самой формулы. Поскольку мы оговорили, что таблица будет зависима от двух переменных, формулу следует вводить в ячейку, расположенную на пересечении строки и столбца, содержащих два множества входных значений, то есть в ячейку В2. В таблице подстановки с двумя переменными, в отличие от  таблицы  с  одной  переменной,  может   быть  только  одна  формула.  У  нас  она записывается так: =ПЛТ(А2/12;В1;12).

3.  Формула  немедленно  вернет  ошибку  #ДЕЛ/0,  поскольку  обе   ячейки,  А2  и  В1, используемые в качестве аргументов, пустые, и  из-за этого получается или чересчур большой,  или  слишком  маленький  результат,  который  программа  не  в  состоянии интерпретировать. Но, как вы увидите позже, подобное начало  никак  не влияет на выполнение дальнейших вычислений.

4.  Наконец,  выделяем  диапазон  таблицы  подстановки  —   минимальный  блок  прямоугольной формы, включающий в себя все входные значения и ячейку с формулой. В нашем случае — диапазон B2:F9.

5.  Теперь  нужно  выбрать  команду  Таблица  подстановки в  меню  Данные  и  задать ячейки ввода. Поскольку создается таблица с двумя переменными, таких ячеек должно быть две. Мы укажем в поле Подставлять значения по столбцам в ссылку на ячейку В1 ($В$1), а в поле Подставлять значения по строкам в — на ячейку А2.

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

6.  Осталось нажать клавишу Enter или кнопку ОК. Результат вычисления таблицы данных с двумя переменными (с небольшим форматированием) показан на рис. 18.6.

Рис. 18.6. Эта таблица подстановки вычисляет размер ежемесячных выплат по кредиту при различных процентных ставках и сроках погашения кредита

РЕШЕНИЕ ПРОБЛЕМ

Будьте внимательны, не перепутайте ячейки ввода в таблице с двумя переменными. Если такое вдруг произойдет, для вычислений будут  привлекаться входные ячейки из другого диапазона, что приведет к  неверным результатам. Так, в рассмотренном случае вместо процентных ставок из диапазона ВЗ:В9 в формулу будут подставлены временные интервалы из диапазона C2:F2, то есть сроки погашения. Вряд ли вы  хотите, чтобы затраты на дом составляли ежемесячно более чем 20 млн долларов! Чтобы не ошибиться, мы рекомендуем при определении ячеек  ввода посматривать на формулу. В нашем примере в формуле

=ПЛТ(А2/12,В1,12) содержимое ячейки А2 является первым аргументом,  то есть ставкой. Процентные ставки располагаются в столбце, поэтому ссылку на ячейку А2 нужно ввести в поле Подставлять значения по строкам в.

Редактирование таблиц подстановки

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

Диапазон-получатель  можно  скопировать  в  любую  другую  часть  рабочего  листа.  Это удобно, если в дальнейшем вы хотите изменить входные значения или формулы расчета, сохранив первоначальный итог. На рис. 18.7 мы скопировали значения из блока C3:F9 в ячейки C11:F17.  Копии уже не являются формулами массива, это обычные числовые константы — Excel автоматически заменяет формулы массива их числовыми значениями.

Рис. 18.7. При копировании диапазона результатов формулы массива автоматически замещаются числами

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий