Проведение анализа чувствительности с помощью таблицы данных в Excel
Инструмент Таблица данных в Excel — это мощный механизм для анализа чувствительности, позволяющий быстро рассчитать и сравнить множество результатов на основе различных комбинаций двух входных параметров, что идеально подходит для финансового моделирования и сценарного анализа.
Ниже описан настольный Excel для Windows; таблицы данных также есть в настольном Excel для Mac, где путь к команде может отличаться. Встроенное создание такой таблицы в веб-версии здесь не предполагается. Таблица данных анализа «что если» отличается от обычной таблицы Excel, создаваемой через Ctrl+T.
В отличие от "Подбора параметра", который ищет одно решение, таблица данных генерирует целую матрицу значений, показывая, как изменяется итоговый показатель при варьировании ключевых факторов.
Для самостоятельной проверки задайте B1=21000 (сумма), B2=60 (месяцы), B3=6% (годовая ставка), B5: =-ПЛТ(B3/12;B2;B1) / =-PMT(B3/12,B2,B1). В D5 введите =B5, в D6:D15 — суммы 21000…30000 с шагом 1000, в E5:J5 — сроки 36, 48, 54, 60, 66, 72. Выделите D5:J15: ячейка для строки — B2, для столбца — B1. Ставка остаётся фиксированной. В этом примере получатся 60 результатов, например для 21000 и 60 месяцев — около 405,99.
Как создать таблицу данных для анализа чувствительности
- Подготовьте структуру: Убедитесь, что формула, которую вы хотите проанализировать, находится в верхней левой ячейке будущего диапазона. В столбец под ней введите различные значения для первой переменной, а в строку справа — для второй.
Формула расчета платежа должна быть в верхнем левом углу анализа. Ниже введите значения от 21000 до 30000 с шагом 1000. Справа от верхней левой ячейки введите сроки: 36, 48, 54, 60, 66, 72. - Откройте инструмент: Выделите весь прямоугольный диапазон, включая формулу, верхнюю строку, левый столбец и будущие результаты. На вкладке Данные выберите Анализ "Что-если" → Таблица данных….
На вкладке 'Данные', в группе 'Анализ "Что-если"', выберите 'Таблица данных'. - Укажите ячейки для подстановки: В диалоговом окне:
- В поле Подставлять значения по строкам в: укажите ячейку, в которую Excel будет подставлять значения из верхней строки вашей таблицы.
- В поле Подставлять значения по столбцам в: укажите ячейку для значений из левого столбца.
Диалоговое окно таблицы данных запрашивает ячейку для подстановки по строкам и по столбцам. Поскольку сроки в месяцах расположены в верхней строке, укажите для них ячейку B2. Сумма кредита в левом столбце должна подставляться в ячейку B1. - Получите результат: Нажмите OK. Excel рассчитает формулу для комбинаций входных данных. Скорость и автоматическое обновление зависят от размера модели и режима вычислений.
Таблица пересчитывается автоматически в соответствующем режиме расчёта. В режиме «Автоматически, кроме таблиц данных» или при ручном расчёте результат может оставаться прежним до отдельного пересчёта. Выделите результаты таблицы и нажмите F9 для их обновления. Входные ячейки разместите на том же листе, что и таблица.
Продвинутый прием: Таблица данных для повторных случайных расчётов
Одномерная таблица данных может повторять расчёт модели со СЛЧИС / RAND. В качестве входной используют отдельную свободную ячейку, которая не участвует в модели, а в столбце сценариев задают номера повторов. Это позволяет собирать результаты случайной модели по нескольким прогонам.
- Создайте базовую модель: Например, модель игры "Подбрасывание пенни" на 250 раундов с формулой, использующей
СЛЧИС(). - Рассчитайте статистику: В отдельной строке вычислите ключевые показатели по итогам одной серии (максимум, минимум, среднее, серия побед/поражений, итоговый результат).
В ячейках G14:L14 рассчитана статистика по текущим 250 раундам игры: МАКС / MAX, МИН / MIN, СРЗНАЧ / AVERAGE, Win Streak, Lose Streak и Final result. - Настройте нестандартную таблицу данных:
- Пусть шесть итоговых формул модели находятся в G14:L14. Оставьте F14 пустой, а в F15:F44 введите номера повторов 1…30. Для простой проверки самой механики можно начать с G14=
СЛЧИС()/RAND()и одного столбца результата. - Для шести итогов выделите F14:L44, включая номера повторов и свободные ячейки будущих результатов G15:L44. Для одной формулы в G14 достаточно F14:G44.
- В диалоговом окне Таблица данных оставьте поле Подставлять значения по строкам в: пустым. В поле Подставлять значения по столбцам в: укажите свободную вспомогательную ячейку, например N1, на этом же листе, вне модели и таблицы; она не должна быть зависимостью итоговых формул.
Пустая ячейка F14 становится верхней левой ячейкой анализа. Для шести итогов и 30 повторов выделите F14:L44, предварительно заполнив F15:F44 числами 1…30. Снимок иллюстрирует принцип, его размер может отличаться. В диалоговом окне 'Таблица данных' оставьте поле для подстановки по строкам пустым, а для подстановки по столбцам укажите любую пустую ячейку. Это работает благодаря функции СЛЧИС() в модели. - Пусть шесть итоговых формул модели находятся в G14:L14. Оставьте F14 пустой, а в F15:F44 введите номера повторов 1…30. Для простой проверки самой механики можно начать с G14=
- Запустите моделирование: После OK и пересчёта в строках G15:L44 появятся результаты 30 прогонов модели. Различия появляются только при наличии пересчитываемой случайной части. Таблица не обеспечивает воспроизводимость или статистическую независимость произвольной модели; для фиксации полученных результатов скопируйте их и вставьте как значения.
Итог
Инструмент Таблица данных — это незаменимый помощник для проведения анализа чувствительности в Excel. Он не только автоматизирует расчеты для множества сценариев, но и, благодаря продвинутым техникам вроде таблицы с вспомогательной входной ячейкой, открывает двери для сложного статистического моделирования и симуляции случайных процессов, экономя время и повышая глубину анализа.