Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

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

forest_paw 26.02.2026 223

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

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

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

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

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

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

  1. Подготовьте структуру: Убедитесь, что формула, которую вы хотите проанализировать, находится в верхней левой ячейке будущего диапазона. В столбец под ней введите различные значения для первой переменной, а в строку справа — для второй.
    2. Формула расчета платежа должна быть в верхнем левом углу анализа. Ниже введите значения от 21000 до 30000 с шагом 1000. Справа от верхней левой ячейки введите сроки: 36, 48, 54, 60, 66, 72.
    Формула расчета платежа должна быть в верхнем левом углу анализа. Ниже введите значения от 21000 до 30000 с шагом 1000. Справа от верхней левой ячейки введите сроки: 36, 48, 54, 60, 66, 72.
  2. Откройте инструмент: На вкладке Данные выберите Анализ "Что-если" → Таблица данных….
    3. На вкладке 'Данные', в группе 'Анализ
    На вкладке 'Данные', в группе 'Анализ "Что-если"', выберите 'Таблица данных'.
  3. Укажите ячейки для подстановки: В диалоговом окне:
    • В поле Подставлять значения по строкам в: укажите ячейку, в которую Excel будет подставлять значения из верхней строки вашей таблицы.
    • В поле Подставлять значения по столбцам в: укажите ячейку для значений из левого столбца.
    4. Диалоговое окно таблицы данных запрашивает ячейку для подстановки по строкам и по столбцам. Поскольку сроки в месяцах расположены в верхней строке, укажите для них ячейку B2. Сумма кредита в левом столбце должна подставляться в ячейку B1.
    Диалоговое окно таблицы данных запрашивает ячейку для подстановки по строкам и по столбцам. Поскольку сроки в месяцах расположены в верхней строке, укажите для них ячейку B2. Сумма кредита в левом столбце должна подставляться в ячейку B1.
  4. Получите результат: Нажмите OK. Excel мгновенно рассчитает формулу для всех комбинаций входных данных, заполнив таблицу результатами.
Результаты анализа чувствительности: ежемесячные платежи от $341 до $902. На скриншоте цветовая шкала выделяет наибольшие значения красным.
Результаты анализа чувствительности: ежемесячные платежи от $341 до $902. На скриншоте цветовая шкала выделяет наибольшие значения красным.

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

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

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

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

  1. Создайте базовую модель: Например, модель игры "Подбрасывание пенни" на 250 раундов с формулой, использующей СЛЧИС().
  2. Рассчитайте статистику: В отдельной строке вычислите ключевые показатели по итогам одной серии (максимум, минимум, среднее, серия побед/поражений, итоговый результат).
    3. В ячейках G14:L14 рассчитана статистика по текущим 250 раундам игры: Max, Min, Average, Win Streak, Lose Streak и Final result.
    В ячейках G14:L14 рассчитана статистика по текущим 250 раундам игры: МАКС / MAX, МИН / MIN, СРЗНАЧ / AVERAGE, Win Streak, Lose Streak и Final result.
  3. Настройте нестандартную таблицу данных:
    • В качестве верхней левой ячейки таблицы выберите пустую ячейку, расположенную слева от строки со статистикой.
    • Выделите диапазон, включающий эту пустую ячейку и диапазон со статистикой, а также пустые строки под ним для результатов итераций.
    • В диалоговом окне Таблица данных оставьте поле Подставлять значения по строкам в: пустым. В поле Подставлять значения по столбцам в: укажите любую пустую ячейку на листе.
    4. Пустая ячейка F14 становится верхней левой ячейкой анализа. Выделите F14:K34. В диалоговом окне 'Таблица данных' оставьте поле для подстановки по строкам пустым, а для подстановки по столбцам укажите любую пустую ячейку. Это работает благодаря функции RAND() в модели.
    Пустая ячейка F14 становится верхней левой ячейкой анализа. Выделите F14:K34. В диалоговом окне 'Таблица данных' оставьте поле для подстановки по строкам пустым, а для подстановки по столбцам укажите любую пустую ячейку. Это работает благодаря функции СЛЧИС() в модели.
  4. Запустите моделирование: После нажатия OK Excel выполнит вашу модель (250 раундов) для каждой строки в таблице (например, 30 раз), как если бы вы 30 раз нажали F9. В результате вы получите таблицу со статистикой для 30 независимых симуляций.
Вы смоделировали 250 подбрасываний, повторенных 30 раз. Видна статистика по всем итерациям, что дает картину диапазона возможных исходов.
Вы смоделировали 250 подбрасываний, повторенных 30 раз. Видна статистика по всем итерациям, что дает картину диапазона возможных исходов.

Итог

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

📖 Похожие материалы

Уроки курсов
Вопросы FAQ
Статьи блога
Теги: Анализ "Что-если" Анализ чувствительности Моделирование Таблица данных Финансовое моделирование
Поделиться:
Все статьи

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