В руководстве показано, как использовать таблицы данных для анализа «Что, если» в Excel. Узнайте, как создать таблицу с одной переменной и двумя переменными, чтобы увидеть влияние одного или двух входных значений на вашу формулу, и как настроить таблицу данных для одновременного вычисления нескольких формул.
Вы создали сложную формулу, зависящую от нескольких переменных, и хотите знать, как изменение этих входных данных изменяет результаты. Вместо того, чтобы тестировать каждую переменную по отдельности, создайте таблицу данных https://excel-home.ru/ анализа «Что, если» и быстро просмотрите все возможные результаты!
Что такое таблица данных в Excel?
В Microsoft Excel таблица данных является одним из инструментов анализа «Что, если», который позволяет опробовать различные входные значения для формул и посмотреть, как изменения в этих значениях влияют на выходные данные формул.
Таблицы данных особенно полезны, когда формула зависит от нескольких значений, и вы хотите поэкспериментировать с различными комбинациями входных данных и сравнить результаты.
В настоящее время существует одна таблица переменных данных и две таблицы переменных данных. Хотя таблица данных ограничена максимум двумя различными ячейками ввода, она позволяет проверять столько значений переменных, сколько вы хотите.
Как создать таблицу данных с одной переменной в Excel
Таблица данных с одной переменной в Excel позволяет проверять ряд значений для одной ячейки ввода и показывает, как эти значения влияют на результат связанной формулы.
Чтобы помочь вам лучше понять эту функцию, мы собираемся следовать конкретному примеру, а не описывать общие шаги.
Предположим, вы рассматриваете возможность внесения своих сбережений в банк, который ежемесячно выплачивает 5% процентов. Чтобы проверить различные варианты, вы создали следующий калькулятор сложных процентов, в котором:
- B8 содержит формулу FV, которая вычисляет итоговый баланс.
- B2 — это переменная, которую вы хотите протестировать (первоначальные инвестиции).
А теперь давайте проведем простой анализ «что, если», чтобы увидеть, какими будут ваши сбережения через 5 лет в зависимости от суммы ваших первоначальных инвестиций, от 1000 до 6000 долларов.
Вот шаги по созданию таблицы данных с одной переменной:
- Введите значения переменных либо в один столбец, либо в одну строку. В этом примере мы собираемся создать таблицу данных, ориентированную на столбцы, поэтому мы вводим значения наших переменных в столбец (D3: D8) и оставляем по крайней мере один пустой столбец справа для результатов.
- Введите свою формулу в ячейку на одну строку выше и на одну ячейку справа от значений переменных (в нашем случае E2). Или привяжите эту ячейку к формуле в исходном наборе данных (если вы решите изменить формулу в будущем, вам потребуется обновить только одну ячейку). Мы выбираем последний вариант и вводим эту простую формулу в E2:
=B8
Совет. Если вы хотите изучить влияние значений переменных на другие формулы, которые ссылаются на ту же ячейку ввода, введите дополнительные формулы справа от первой формулы, как показано в этом примере. - Выберите диапазон таблицы данных, включая вашу формулу, ячейки значений переменных и пустые ячейки для результатов (D2: E8).
- Перейдите на вкладку Данные> Группа Инструменты данных, нажмите кнопку Анализ «Что, если«, а затем выберите Таблица данных …
- В диалоговом окне «Таблица данных» щелкните в поле «Ячейка ввода столбца» (поскольку наши значения инвестиций находятся в столбце) и выберите ячейку переменной, на которую ссылается ваша формула. В этом примере мы выбираем B3, который содержит начальную стоимость инвестиций.
- Нажмите OK, и Excel немедленно заполнит пустые ячейки результатами, соответствующими значению переменной в той же строке.
- Примените к результатам желаемый числовой формат (в нашем случае валюту), и все готово!
Теперь вы можете быстро просмотреть свою таблицу данных с одной переменной, изучить возможные балансы и выбрать оптимальный размер депозита:
