Как создать и использовать таблицу данных в Excel

В руководстве показано, как использовать таблицы данных для анализа «Что, если» в Excel. Узнайте, как создать таблицу с одной переменной и двумя переменными, чтобы увидеть влияние одного или двух входных значений на вашу формулу, и как настроить таблицу данных для одновременного вычисления нескольких формул.

Вы создали сложную формулу, зависящую от нескольких переменных, и хотите знать, как изменение этих входных данных изменяет результаты. Вместо того, чтобы тестировать каждую переменную по отдельности, создайте таблицу данных https://excel-home.ru/ анализа «Что, если» и быстро просмотрите все возможные результаты!

Что такое таблица данных в Excel?

В Microsoft Excel таблица данных является одним из инструментов анализа «Что, если», который позволяет опробовать различные входные значения для формул и посмотреть, как изменения в этих значениях влияют на выходные данные формул.

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

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

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

Как создать таблицу данных с одной переменной в Excel

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

Чтобы помочь вам лучше понять эту функцию, мы собираемся следовать конкретному примеру, а не описывать общие шаги.

Предположим, вы рассматриваете возможность внесения своих сбережений в банк, который ежемесячно выплачивает 5% процентов. Чтобы проверить различные варианты, вы создали следующий калькулятор сложных процентов, в котором:

  • B8 содержит формулу FV, которая вычисляет итоговый баланс.
  • B2 — это переменная, которую вы хотите протестировать (первоначальные инвестиции).

Исходные данные

А теперь давайте проведем простой анализ «что, если», чтобы увидеть, какими будут ваши сбережения через 5 лет в зависимости от суммы ваших первоначальных инвестиций, от 1000 до 6000 долларов.

Вот шаги по созданию таблицы данных с одной переменной:

  1. Введите значения переменных либо в один столбец, либо в одну строку. В этом примере мы собираемся создать таблицу данных, ориентированную на столбцы, поэтому мы вводим значения наших переменных в столбец (D3: D8) и оставляем по крайней мере один пустой столбец справа для результатов.
  2. Введите свою формулу в ячейку на одну строку выше и на одну ячейку справа от значений переменных (в нашем случае E2). Или привяжите эту ячейку к формуле в исходном наборе данных (если вы решите изменить формулу в будущем, вам потребуется обновить только одну ячейку). Мы выбираем последний вариант и вводим эту простую формулу в E2: =B8
    Настройка таблицы данных в Excel 

    Совет. Если вы хотите изучить влияние значений переменных на другие формулы, которые ссылаются на ту же ячейку ввода, введите дополнительные формулы справа от первой формулы, как показано в этом примере.
  3. Выберите диапазон таблицы данных, включая вашу формулу, ячейки значений переменных и пустые ячейки для результатов (D2: E8).
  4. Перейдите на вкладку Данные> Группа Инструменты данных, нажмите кнопку Анализ «Что, если«, а затем выберите Таблица данных …
    Таблица данных анализа "Что, если" в Excel
  5. В диалоговом окне «Таблица данных» щелкните в поле «Ячейка ввода столбца» (поскольку наши значения инвестиций находятся в столбце) и выберите ячейку переменной, на которую ссылается ваша формула. В этом примере мы выбираем B3, который содержит начальную стоимость инвестиций.
    Создание таблицы данных в Excel
  6. Нажмите OK, и Excel немедленно заполнит пустые ячейки результатами, соответствующими значению переменной в той же строке.
  7. Примените к результатам желаемый числовой формат (в нашем случае валюту), и все готово!

Теперь вы можете быстро просмотреть свою таблицу данных с одной переменной, изучить возможные балансы и выбрать оптимальный размер депозита:
Таблица данных с одной переменной в Excel

Яндекс.Метрика