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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица данных, ориентированная на строки

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

1. Введите значения переменных в строке, оставив по крайней мере один пустой столбец слева (для формулы) и одну пустую строку ниже (для результатов). В этом примере мы вводим значения переменных в ячейки F3:J3.
2. Введите формулу в ячейку, которая находится на один столбец левее первого значения переменной и на одну ячейку ниже (в нашем случае E4).
3. Создайте таблицу данных, как обсуждалось выше, но введите входное значение (B3) в поле ввода строки:
   
4. Нажмите ХОРОШОи вы получите следующий результат:
   

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

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

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

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

1. Введите формулу в пустую ячейку или свяжите эту ячейку с исходной формулой. Убедитесь, что у вас достаточно пустых столбцов справа и пустых строк ниже, чтобы вместить значения ваших переменных. Как и раньше, мы связываем ячейку E2 с исходной формулой FV, которая вычисляет баланс: =B8
2. Введите один набор входных значений под формулой в том же столбце (инвестиционные значения в E3:E8).
3. Введите другой набор значений переменных справа от формулы в той же строке (количество лет в F2:H2).

   На этом этапе ваша таблица данных с двумя переменными должна выглядеть примерно так:
   

4. Выберите весь диапазон таблицы данных, включая формулу, строку и столбец значений переменных, а также ячейки, в которых будут отображаться вычисляемые значения. Мы выбираем диапазон E2:H8.
5. Создадим таблицу данных уже привычным способом: Данные вкладка > Анализ «что, если» кнопка > Таблица данных…
6. в Ячейка ввода строки введите ссылку на входную ячейку для значений переменных в строке (в этом примере это B6, содержащая Годы ценность).
7. в Ячейка ввода столбца введите ссылку на входную ячейку для значений переменных в столбце (B3, содержащем Первоначальные инвестиции ценность).
8. Нажмите ХОРОШО.
   
9. При желании отформатируйте выходные данные так, как вам нужно (применив Валюта формат в нашем случае) и анализируем результаты:
   

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

Если вы хотите оценить более одной формулы одновременно, создайте таблицу данных, как показано в предыдущих примерах, и введите дополнительные формулы:

• Справа от первой формулы в случае вертикальной таблицы данных, организованной в столбцы
• Под первой формулой в случае горизонтальной таблицы данных, организованной в строки

Для корректной работы таблицы данных с несколькими формулами все формулы должны ссылаться на одну и ту же входную ячейку.

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

1. В ячейке B10 вычислите интерес по этой формуле: =B8-B3
2. Расположите исходные данные таблицы данных, как мы делали ранее: значения переменных в D3:D8 и E2 связаны с B8 (Остаток средств формула).
3. Добавьте еще один столбец в диапазон таблицы данных (столбец F) и свяжите F2 с B10 (интерес формула):
   
4. Выберите диапазон расширенной таблицы данных (D2:F8).
5. Открой Таблица данных диалоговое окно, нажав Данные вкладка > Анализ «что, если» > Таблица данных…
6. В поле «Ввод столбца» введите ячейку ввода (B3) и щелкните ХОРОШО.

Вуаля, теперь вы можете наблюдать влияние значений вашей переменной на обе формулы:

Таблица данных в Excel — 3 вещи, которые вы должны знать

Чтобы эффективно использовать таблицы данных в Excel, имейте в виду эти 3 простых факта:

1. Для успешного создания таблицы данных входные ячейки должны находиться на том же листе, что и таблица данных.
2. Microsoft Excel использует функцию TABLE(row_input_cell, colum_input_cell) для вычисления результатов таблицы данных:
   • В таблица данных с одной переменной, один из аргументов опускается, в зависимости от макета (ориентированного на столбцы или на строки). Например, в нашей горизонтальной таблице данных с одной переменной используется формула = ТАБЛИЦА (, B3), где B3 — входная ячейка столбца.
   • В таблица данных с двумя переменными, оба аргумента на месте. Например, = ТАБЛИЦА (B6, B3), где B6 — ячейка ввода строки, а B3 — ячейка ввода столбца.

   Функция ТАБЛИЦА вводится как формула массива. Чтобы убедиться в этом, выберите любую ячейку с вычисленным значением, посмотрите на строку формул и обратите внимание на {фигурные скобки} вокруг формулы. Однако это не обычная формула массива — вы не можете ввести ее в строку формул и не можете редактировать существующую. Это просто “для галочки”.

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

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

Как упоминалось выше, Excel не позволяет удалять значения в отдельных ячейках, содержащих результаты. Всякий раз, когда вы пытаетесь это сделать, появляется сообщение об ошибке “Невозможно изменить часть таблицы данных” появится.

Однако вы можете легко очистить весь массив от полученных значений. Вот как:

1. В зависимости от ваших потребностей, выберите все ячейки таблицы данных или только ячейки с результатами.
2. Нажмите клавишу Удалить.

Сделанный! 🙂

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

Поскольку в Excel невозможно изменить часть массива, вы не можете редактировать отдельные ячейки с вычисленными значениями. Вы можете заменить все эти значения только своими, выполнив следующие действия:

1. Выделите все получившиеся ячейки.
2. Удалите формулу ТАБЛИЦА в строке формул.
3. Введите нужное значение и нажмите Ctrl + Enter.

Это вставит одно и то же значение во все выбранные ячейки:

Как только формула ТАБЛИЦА исчезнет, ​​прежняя таблица данных станет обычным диапазоном, и вы сможете редактировать любую отдельную ячейку в обычном режиме.

Как пересчитать таблицу данных вручную

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

Для этого зайдите в Формулы вкладка > Расчет группу, нажмите кнопку Варианты расчета а затем щелкните Автоматически, кроме таблиц данных.

Это отключит автоматические расчеты таблицы данных и ускорит пересчет всей книги.

Чтобы вручную пересчитать вашу таблицу данных, выберите ее результирующие ячейки, т.е. ячейки с формулами TABLE(), и нажмите F9.

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

Вас также могут заинтересовать