Анализ «что если» с помощью таблиц данных

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

Есть два типа таблиц данных -

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

Если у вас есть более двух переменных в вашей проблеме анализа, вам необходимо использовать инструмент диспетчера сценариев Excel. Дополнительные сведения см. В главе « Анализ« что-если »с помощью диспетчера сценариев» этого руководства.

Таблицы данных с одной переменной

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

Example

Имеется ссуда в размере 5 000 000 на срок 30 лет. Вы хотите знать ежемесячные платежи (EMI) для различных процентных ставок. Вам также может быть интересно узнать сумму процентов и основной суммы, выплачиваемой во второй год.

Анализ с помощью таблицы данных с одной переменной

Анализ с помощью таблицы данных с одной переменной необходимо выполнить в три этапа:

Step 1 - Установите необходимый фон.

Step 2 - Создайте таблицу данных.

Step 3 - Проведите анализ.

Давайте разберемся с этими шагами подробно -

Шаг 1. Установите необходимый фон

Предположим, что процентная ставка составляет 12%.
Перечислите все необходимые значения.
Назовите ячейки, содержащие значения, чтобы формулы имели имена вместо ссылок на ячейки.
Установите расчеты для EMI, накопительного процента и накопительной основной суммы с помощью функций Excel - PMT, CUMIPMT и CUMPRINC соответственно.

Ваш рабочий лист должен выглядеть следующим образом -

Вы можете видеть, что ячейки в столбце C названы так, как указано в соответствующих ячейках в столбце D.

Шаг 2: Создайте таблицу данных

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

Как вы заметили, над значениями процентной ставки есть пустая строка. Эта строка предназначена для формул, которые вы хотите использовать.

Введите первую функцию (PMT) в ячейке на одну строку выше и одну ячейку справа от столбца значений. Введите другие функции (CUMIPMT and CUMPRINC) в ячейках справа от первой функции.

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

Таблица данных выглядит так, как показано ниже -

Шаг 3. Проведите анализ с помощью инструмента таблицы данных анализа «Что, если»

Выберите диапазон ячеек, содержащий формулы и значения, которые вы хотите подставить, т.е. выберите диапазон - E2: H13.
Щелкните вкладку ДАННЫЕ на ленте.
Щелкните Анализ «Что, если» в группе «Инструменты для работы с данными».
В раскрывающемся списке выберите Таблица данных.

Data Table появится диалоговое окно.

Щелкните значок в поле ячейки ввода столбца.
Щелкните ячейку Interest_Rate, то есть C2.

Вы можете видеть, что ячейка ввода столбца принята как $ C $ 2. Щелкните ОК.

Таблица данных заполнена расчетными результатами для каждого из входных значений, как показано ниже -

Если вы можете заплатить EMI в размере 54 000, вы можете заметить, что процентная ставка 12,6% подходит для вас.

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

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

Example

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

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

Анализ с помощью таблицы данных с двумя переменными необходимо выполнить в три этапа:

Step 1 - Установите необходимый фон.

Step 2 - Создайте таблицу данных.

Step 3 - Проведите анализ.

Шаг 1. Установите необходимый фон

Предположим, что процентная ставка составляет 12%.
Перечислите все необходимые значения.
Назовите ячейки, содержащие значения, чтобы формула имела имена вместо ссылок на ячейки.
Установите расчет EMI с помощью функции Excel - PMT.

Ваш рабочий лист должен выглядеть следующим образом -

Вы можете видеть, что ячейки в столбце C названы так, как указано в соответствующих ячейках столбца D.

Шаг 2: Создайте таблицу данных

Тип =EMI в ячейке F2.

Введите первый список входных значений, то есть процентных ставок, в столбец F, начиная с ячейки под формулой, то есть F3.
Введите второй список входных значений, то есть количество платежей в строке 2, начиная с ячейки справа от формулы, то есть G2.

Таблица данных выглядит следующим образом -

Проведите анализ с помощью таблицы данных инструмента анализа «Что, если»

Выберите диапазон ячеек, содержащий формулу и два набора значений, которые вы хотите заменить, т.е. выберите диапазон - F2: L13.
Щелкните вкладку ДАННЫЕ на ленте.
Щелкните Анализ «Что, если» в группе «Инструменты для работы с данными».
Выберите Таблица данных из раскрывающегося списка.

Откроется диалоговое окно "Таблица данных".

Щелкните значок в поле ввода строки.
Щелкните ячейку NPER, то есть C3.
Снова щелкните значок в поле ввода строки.
Затем щелкните значок в поле ячейки ввода столбца.
Щелкните ячейку Interest_Rate, которая равна C2.
Снова щелкните значок в поле ячейки ввода столбца.

Вы увидите, что ячейка ввода строки принята как $ C $ 3, а ячейка ввода столбца принята как $ C $ 2. Щелкните ОК.

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

Если вы можете заплатить EMI в размере 54 000, вам подойдет процентная ставка в размере 12,2% и 288 EMI. Это означает, что срок кредита составит 24 года.

Расчеты таблицы данных

Таблицы данных пересчитываются каждый раз при пересчете содержащего их рабочего листа, даже если они не менялись. Чтобы ускорить вычисления на листе, содержащем таблицу данных, вам необходимо изменить параметры вычислений наAutomatically Recalculate рабочий лист, но не таблицы данных, как указано в следующем разделе.

Ускорение расчетов на листе

Вы можете ускорить вычисления на листе, содержащем таблицы данных, двумя способами:

Из параметров Excel.
Из ленты.

Из параметров Excel

Щелкните вкладку ФАЙЛ на ленте.
Выберите Параметры из списка на левой панели.

Откроется диалоговое окно "Параметры Excel".

На левой панели выберите Formulas.
Выберите вариант Automatic except for data tables под Workbook Calculationв разделе Параметры расчета. Щелкните ОК.

С ленты

Щелкните вкладку ФОРМУЛЫ на ленте.
Щелкните значок Calculation Options в группе Расчеты.
Выбрать Automatic Except for Data Tables в раскрывающемся списке.