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

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

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

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

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

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

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

1. Введите формулу в пустую ячейку или свяжите эту ячейку с исходной формулой. Убедитесь, что у вас достаточно пустых столбцов справа и пустых строк внизу, чтобы разместить значения ваших переменных. Как и раньше, мы связываем ячейку E2 с исходной формулой FV, которая вычисляет баланс: =B8
2. Введите один набор входных значений под формулой в том же столбце (значения инвестиций в E3: E8).
3. Введите другой набор значений переменных справа от формулы в той же строке (количество лет в F2: H2).На этом этапе ваша таблица данных с двумя переменными должна выглядеть примерно так:
   
4. Выберите весь диапазон таблиц данных, включая формулу, строку и столбец значений переменных, а также ячейки, в которых будут отображаться вычисленные значения. Выбираем диапазон E2:H8.
5. Создайте таблицу данных уже знакомым способом: вкладка «Данные«> Кнопка «Что, если анализ«> Таблица данных …
6. В поле Ячейка ввода строки введите ссылку на ячейку ввода для значений переменных в строке (в этом примере это B6, содержащий значение Years).
7. В поле Ячейка ввода столбца введите ссылку на ячейку ввода для значений переменных в столбце (B3, содержащий начальную стоимость инвестиций).
8. Нажмите OK.
   
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) для вычисления результатов таблицы данных:
   • В таблице данных с одной переменной один из аргументов опущен в зависимости от макета (ориентированный на столбцы или ориентированный на строки). Например, в нашей горизонтальной таблице данных с одной переменной формула=TABLE(, B3), где B3 — ячейка ввода столбца.
   • В таблице данных с двумя переменными используются оба аргумента. Например, =TABLE(B6, B3)где B6 — ячейка ввода строки, а B3 — ячейка ввода столбца.

   Табличная функция вводится как формула массива. Чтобы убедиться в этом, выделите любую ячейку с вычисленным значением, посмотрите на строку формул и обратите внимание на {фигурные скобки} вокруг формулы. Однако это не обычная формула массива — вы не можете ввести ее в строке формул и не можете редактировать существующую. Это просто «для галочки».

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