Проведение анализа чувствительности с помощью таблицы данных в Excel

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

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

Модель с тремя переменными для расчета платежа по кредиту. Анализ чувствительности по сумме кредита от $21K до $30K и сроку от 36 до 72 месяцев.

Как создать таблицу данных для анализа чувствительности

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

Формула расчета платежа должна быть в верхнем левом углу анализа. Ниже введите значения от 21000 до 30000 с шагом 1000. Справа от верхней левой ячейки введите сроки: 36, 48, 54, 60, 66, 72.

Откройте инструмент: На вкладке Данные выберите Анализ "Что-если" → Таблица данных….

На вкладке 'Данные', в группе 'Анализ "Что-если"', выберите 'Таблица данных'.

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

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

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

Результаты анализа чувствительности: ежемесячные платежи от $341 до $902. На скриншоте цветовая шкала выделяет наибольшие значения красным.

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

Изменение чисел в верхней строке и левом столбце приводит к пересчету данных. На изображении при сроках от 48 до 63 месяцев и суммах от 23K до 24K платежи составляют от $418 до $553.

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

Этот элегантный трюк, предложенный профессором Саймоном Беннингой, позволяет использовать таблицу данных для многократного запуска моделей, основанных на случайных числах (например, СЛЧИС (RAND)()), и анализа статистики по множеству итераций.

Создайте базовую модель: Например, модель игры "Подбрасывание пенни" на 250 раундов с формулой, использующей СЛЧИС (RAND)().
Рассчитайте статистику: В отдельной строке вычислите ключевые показатели по итогам одной серии (максимум, минимум, среднее, серия побед/поражений, итоговый результат).

В ячейках G14:L14 рассчитана статистика по текущим 250 раундам игры: Max, Min, Average, Win Streak, Lose Streak и Final result. — В ячейках G14:L14 рассчитана статистика по текущим 250 раундам игры: МАКС (Max), МИН (Min), СРЗНАЧ (Average), Win Streak, Lose Streak и Final result.

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

Пустая ячейка F14 становится верхней левой ячейкой анализа. Выделите F14:K34. В диалоговом окне 'Таблица данных' оставьте поле для подстановки по строкам пустым, а для подстановки по столбцам укажите любую пустую ячейку. Это работает благодаря функции RAND() в модели. — Пустая ячейка F14 становится верхней левой ячейкой анализа. Выделите F14:K34. В диалоговом окне 'Таблица данных' оставьте поле для подстановки по строкам пустым, а для подстановки по столбцам укажите любую пустую ячейку. Это работает благодаря функции СЛЧИС (RAND)() в модели.

Запустите моделирование: После нажатия OK Excel выполнит вашу модель (250 раундов) для каждой строки в таблице (например, 30 раз), как если бы вы 30 раз нажали F9. В результате вы получите таблицу со статистикой для 30 независимых симуляций.

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

Итог

Инструмент Таблица данных — это незаменимый помощник для проведения анализа чувствительности в Excel. Он не только автоматизирует расчеты для множества сценариев, но и, благодаря продвинутым техникам вроде "таблицы из пустой ячейки", открывает двери для сложного статистического моделирования и симуляции случайных процессов, экономя время и повышая глубину анализа.

Проведение анализа чувствительности с помощью таблицы данных в Excel

Проведение анализа чувствительности с помощью таблицы данных в Excel

Как создать таблицу данных для анализа чувствительности

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

Итог

Похожие статьи

Генерация всех комбинаций с помощью функции BASE в Excel

Определение страны по телефонному номеру в Excel с помощью формулы

Пользовательские форматы чисел в Excel: полное руководство