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

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

forest_paw 26.02.2026 1159

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

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

Ниже описан настольный Excel для Windows; таблицы данных также есть в настольном Excel для Mac, где путь к команде может отличаться. Встроенное создание такой таблицы в веб-версии здесь не предполагается. Таблица данных анализа «что если» отличается от обычной таблицы Excel, создаваемой через Ctrl+T.

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

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

Для самостоятельной проверки задайте 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.

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

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

Таблица пересчитывается автоматически в соответствующем режиме расчёта. В режиме «Автоматически, кроме таблиц данных» или при ручном расчёте результат может оставаться прежним до отдельного пересчёта. Выделите результаты таблицы и нажмите F9 для их обновления. Входные ячейки разместите на том же листе, что и таблица.

Изменение сумм и сроков в заголовках меняет рассчитанные платежи после пересчёта таблицы.
Изменение сумм и сроков в заголовках меняет рассчитанные платежи после пересчёта таблицы.

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

Одномерная таблица данных может повторять расчёт модели со СЛЧИС / RAND. В качестве входной используют отдельную свободную ячейку, которая не участвует в модели, а в столбце сценариев задают номера повторов. Это позволяет собирать результаты случайной модели по нескольким прогонам.

  1. Создайте базовую модель: Например, модель игры "Подбрасывание пенни" на 250 раундов с формулой, использующей СЛЧИС().
  2. Рассчитайте статистику: В отдельной строке вычислите ключевые показатели по итогам одной серии (максимум, минимум, среднее, серия побед/поражений, итоговый результат).
    В ячейках 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. Настройте нестандартную таблицу данных:
    • Пусть шесть итоговых формул модели находятся в 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. Снимок иллюстрирует принцип, его размер может отличаться. В диалоговом окне 'Таблица данных' оставьте поле для подстановки по строкам пустым, а для подстановки по столбцам укажите любую пустую ячейку. Это работает благодаря функции RAND() в модели.
    Пустая ячейка F14 становится верхней левой ячейкой анализа. Для шести итогов и 30 повторов выделите F14:L44, предварительно заполнив F15:F44 числами 1…30. Снимок иллюстрирует принцип, его размер может отличаться. В диалоговом окне 'Таблица данных' оставьте поле для подстановки по строкам пустым, а для подстановки по столбцам укажите любую пустую ячейку. Это работает благодаря функции СЛЧИС() в модели.
  4. Запустите моделирование: После OK и пересчёта в строках G15:L44 появятся результаты 30 прогонов модели. Различия появляются только при наличии пересчитываемой случайной части. Таблица не обеспечивает воспроизводимость или статистическую независимость произвольной модели; для фиксации полученных результатов скопируйте их и вставьте как значения.
Вы смоделировали 250 подбрасываний, повторенных 30 раз. Видна статистика по всем итерациям, что дает картину диапазона возможных исходов.
Вы смоделировали 250 подбрасываний, повторенных 30 раз. Видна статистика по всем итерациям, что дает картину диапазона возможных исходов.

Итог

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

Поделиться:

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