Data table excel как использовать
Таблица данных — это диапазон ячеек, в котором можно изменять значения в некоторых ячейках и вы можете найти разные ответы на вопрос. Хороший пример таблицы данных с использованием функции PMT с различными суммами займа и процентными ставками для расчета доступной суммы по ипотеке на домашний. Экспериментировать с различными значениями, чтобы увидеть соответствующий вариант результатов, — это распространенная задача при анализе данных.
В Microsoft Excel таблицы данных являются частью набора команд, которые называются What-If анализа. При построении и анализе таблиц данных проводится анализ "что если".
Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе. Например, с помощью таблицы данных можно изменять процентную ставку и срок погашения кредита для оценки возможных сумм ежемесячных платежей.
Примечание: Вы можете выполнять более быстрые вычисления с таблицами данных и Visual Basic для приложений (VBA). Дополнительные сведения см. в Excel What-If данных: более быстрые вычисления с помощью VBA.
Типы анализа "что если"
Существует три типа средств анализа "что если" в Excel: сценарии,таблицы данных и поиск целей. В сценариях и таблицах данных для вычисления возможных результатов используются наборы входных значений. При поиске конечная цель используется один результат и вычисляются возможные входные значения, которые будут его результатом.
Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, таблицы данных показывают все результаты в одной таблице на одном из них. С помощью таблиц данных можно легко и быстро проверить диапазон возможностей. Поскольку при этом используются всего одна или две переменные, вы можете без труда прочитать результат и поделиться им в табличной форме.
В таблице данных может быть не больше двух переменных. Для анализа большего количества переменных необходимо использовать сценарии. Хотя она ограничена только одной или двумя переменными (одна для ячейки ввода строки и одна для ячейки ввода столбца), таблица данных может включать сколько угодно различных значений переменных. В сценарии можно использовать не более 32 разных значений, но вы можете создавать сколько угодно сценариев.
Подробнее об этом читайте в статье Введение в What-If анализа.
Создайте таблицы данных с одной или двумя переменными в зависимости от количества переменных и формул, которые необходимо проверить.
Таблицы данных с одной переменной
Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, таблицу данных с одной переменной можно использовать для того, чтобы узнать, как разные процентные ставки влияют на ежемесячный платеж по ипотеке с помощью функции PMT. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.
На рисунке ниже ячейка D2 содержит формулу платежа =PMT(B3/12;B4;-B5),которая ссылается на ячейку ввода B3.
Таблицы данных с двумя переменными
Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.
На рисунке ниже ячейка C2 содержит формулу платежа =PMT(B3/12;B4;-B5),которая использует две ячейки ввода: B3 и B4.
Вычисления таблицы данных
При пересчете на нем также пересчитыются все таблицы данных, даже если в них не было изменений. Чтобы ускорить вычисление таблицы данных, можно изменить параметры вычислений таким образом, чтобы автоматически пересчитылся не таблицы данных, а таблицы данных. Дополнительные сведения см. в разделе Ускорение вычислений на сайте, который содержит таблицы данных.
Таблица данных с одной переменной содержит входные значения в одном столбце (с ориентацией по столбцам) или в строке (по строкам). Любая формула в таблице данных с одной переменной должна ссылаться только на ячейка ввода.
Введите список значений, которые нужно подставить, в ячейку ввода : вниз на один столбец или в одну строку. Оставьте несколько пустых строк и столбцов по обе стороны от значений.
Выполните одно из указанных ниже действий.
Если таблица данных ориентирована на столбец (значения переменных находятся в столбце), введите формулу в ячейку на одну строку выше и на одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбец, а формула содержится в ячейке D2.
Если вы хотите изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки справа от первой формулы.
Если таблица данных ориентирована на строку (значения переменных находятся в строке), введите формулу в ячейку на один столбец слева от первого значения и на одну ячейку ниже строки значений.
Если вы хотите изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.
Выделите диапазон ячеек с формулами и значениями, которые нужно заменить. На рисунке выше это диапазон C2:D5.
На вкладке Данные нажмите кнопку Анализ >данных "что если" (в группе "Средства данных" или "Прогноз" Excel 2016 ).
Выполните одно из указанных ниже действий.
Если таблица данных ориентирована на столбец, введите ссылка на ячейку ячейку ввода в поле ячейки ввода Столбец. На рисунке выше ячейка ввода — B3.
Если таблица данных ориентирована на строку, введите ссылку на ячейку ввода в поле ячейки ввода Строка.
Примечание: После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке ячейки результатов отформатированы как валюта.
Добавление формулы в таблицу данных с одной переменнойФормулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
Выполните эти действия
Если таблица данных ориентирована на столбец, введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных.
Если таблица данных ориентирована на строки, введите новую формулу в пустую ячейку под существующей формулой в первом столбце таблицы данных.
Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
На вкладке Данные нажмите кнопку Анализ> данных "что если" (в группе "Средства данных" или "Прогноз" Excel 2016 ).
Если таблица данных ориентирована на столбец, введите ссылку на ячейку ввода в поле Ячейка ввода столбца.
Если таблица данных ориентирована на строку, введите ссылку на ячейку ввода в поле Ячейка ввода строки.
В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода.
В ячейку на этом сайте введите формулу, которая ссылается на две ячейки ввода.
В следующем примере, в котором начальные значения формулы ввели в ячейки B3, B4 и B5, введите формулу =PMT(B3/12;B4;-B5) в ячейку C2.
Введите один список входных значений в том же столбце под формулой.
В данном примере нужно ввести разные процентные ставки в ячейки C3, C4 и C5.
Введите второй список в той же строке, что и формула, справа от нее.
Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.
Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5).
В данном примере выделяется диапазон C2:E5.
На вкладке Данные в группе Средства данных или Прогноз (в Excel 2016 ) нажмите кнопку Анализ >"что если" (в группе "Средства данных" или "Прогноз" Excel 2016 ).
В поле Ячейка ввода строки введите ссылку на ячейку ввода для входных значений в строке.
Введите ячейку B4 в поле Ячейка ввода строки.
В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце.
Введите B3 в поле Ячейка ввода столбца.
Пример таблицы данных с двумя переменными
Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На рисунке ниже ячейка C2 содержит формулу платежа =PMT(B3/12;B4;-B5),которая использует две ячейки ввода: B3 и B4.
Ускорение вычислений на сайте, который содержит таблицы данныхПри этом вычисления в таблице данных не будут происходить при пересчете всей книги. Чтобы выполнить пересчет таблицы данных вручную, выделите содержащиеся в ней формулы и нажмите клавишу F9.
Чтобы повысить производительность вычислений, выполните указанные здесь действия.
Щелкните Файл > параметры > Формулы.
В разделе Параметры вычислений в разделе Вычислениенажмите кнопку Автоматически, кроме таблиц данных.
Совет: При желании на вкладке Формулы щелкните стрелку на кнопке Параметры вычислений ивыберите вариант Автоматически, кроме таблиц данных (в группе Вычисления).
Вы можете использовать несколько других средств Excel анализа "что если" при определенных целях или больших наборах переменных данных.
Подбор параметров
Если вы знаете, какой результат следует ожидать от формулы, но точно не знаете, какое входные значения должна получить формула, используйте функцию Goal-Seek формулы. Дополнительные сведения см. в статье Использование средств поиска целью для поиска нужного результата путем изменения входного значения.
Excel Решатель
С помощью надстройки Excel "Поиск решения" можно найти оптимальное значение для набора входных переменных. Над решением работает группа ячеек (называемых переменными решения или просто ячейками переменных), которые используются при вычислении формул в ячейках целей и ограничений. Надстройка "Поиск решения" изменяет значения в ячейках переменных решения согласно пределам ячеек ограничения и выводит нужный результат в целевой ячейке. Подробнее об этом читайте в статье Определение и решение проблемы с помощью "Решение".
Подключив разные числа к ячейке, вы можете быстро получить ответы на различные вопросы. Хороший пример — использование функции PMT с различными процентными ставками и периодами займа (в месяцах), чтобы выяснить, какая часть кредита вы можете позволить себе дом или автомобиль. Числа введите в диапазон ячеек, который называется таблицей данных.
Таблица данных — это диапазон ячеек B2:D8. Значение В4, сумму займа и ежемесячные платежи в столбце D можно изменить автоматически. Используя процентную ставку 3,75 %, D2 возвращает ежемесячный платеж в размере 1042,01 долларов США по формуле =PMT(C2/12;$B$3,$B$4).
В зависимости от количества переменных и формул, которые нужно проверить, можно использовать одну или две переменные.
Используйте проверку с одной переменной, чтобы увидеть, как различные значения одной переменной в формуле изменяют результаты. Например, можно изменить процентную ставку для ежемесячного платежа по ипотеке с помощью функции PMT. Значения переменных (процентные ставки) введите в одном столбце или строке, а результаты будут показаны в ближайшем столбце или строке.
В этой книге ячейка D2 содержит формулу платежа = PMT(C2/12;$B$3;$B$4). Ячейка B3 — это ячейка переменной, в которой можно подключить другую продолжительность терминов (количество периодов ежемесячных платежей). В ячейке D2 функция ПЛТ подсоеднит процентную ставку 3,75%/12, 360 месяцев и кредит на сумму 225 000 долларов США и вычислит ежемесячный платеж в размере 1 042,01 долларов США.
Используйте проверку с двумя переменными, чтобы увидеть, как разные значения двух переменных в формуле изменяют результаты. Например, можно проверить разные комбинации процентных ставок и количества периодов ежемесячного платежа для расчета платежа по ипотеке.
В этой книге ячейка C3 содержит формулу платежа =PMT($B$3/12;$B$2;B4), которая использует две ячейки переменных: B2 и B3. В ячейке C2 функция ПЛТ подключается к процентной ставке 3,875%/12, 360 месяцев и займу в размере 225 000 долларов США и вычисляет сумму ежемесячного платежа в размере 1 058,03 долларов США.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Сводка
В этой статье описывается, как создавать и использовать таблицы с двумя входами в Microsoft Excel. Эти таблицы позволяют проверить, как изменения в двух переменных влияют на одну формулу.
Дополнительные сведения
При создании таблицы с двумя входами в диалоговом окне Таблица укажите ячейки ввода строки и ячейки ввода столбцов.
В Microsoft Office Excel 2007 г. диалоговое окно Table называется диалоговое окно Data Table.
Чтобы создать простую таблицу с двумя входами, выполните следующие действия:
Создайте новую книгу.
В ячейках B15:B19 введите следующие данные:
В ячейках C14:G14 введите следующие данные:
В ячейке B14 введите следующую формулу:
=A14*2+A15
A14 — это ячейка ввода столбца (которая заменяет значения 1, 2, 3, 4 и 5), а A15 — ячейку ввода строки (которая заменяет значения 6, 7, 8, 9 и 10). Эти входные ячейки должны располагаться за пределами таблицы; они могут содержать или не содержать данные. Поскольку эта таблица настроена в ячейках B14:G19, и поскольку A14 и A15 находятся за пределами таблицы, они являются допустимым входным столбцом и ячейками ввода строки.
В меню Data щелкните Таблица.
В Excel 2007 г. щелкните вкладку Данные, щелкните Анализ What-If, а затем нажмите таблицу данных.
В поле Ввод строки введите A15. В поле Ячейка ввода столбца введите A14.
Вы видите следующие результаты:
В качестве примера возьмем ячейку C15. Фактические значения, используемые в формуле, от ячеек B15:B19 (ячейки ввода строки) и ячейки C15:G14 (ячейки ввода столбца). Формула со значениями в ней будет 1*2+6 (в общей сложности 8). Excel внутренне заменяет значения в ячейках ввода строки и столбца в формулу в ячейке B14.
0 отображается в ячейке B14, хотя ячейка B14 не является форматом номеров. Чтобы дублировать пустое значение в ячейке B14, выполните следующие действия:
Ссылки
Дополнительные сведения об использовании таблиц данных можно найти в следующих статьях:
Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table.
Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 below calculates a total profit of 60 * $50 + 40 * $20 = $3800.
One Variable Data Table
To create a one variable data table, execute the following steps.
1. Select cell B12 and type =D10 (refer to the total profit cell).
2. Type the different percentages in column A.
3. Select the range A12:B17.
We are going to calculate the total profit if you sell 60% for the highest price, 70% for the highest price, etc.
4. On the Data tab, in the Forecast group, click What-If Analysis.
5. Click Data Table.
6. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.
We select cell C4 because the percentages refer to cell C4 (% sold for the highest price). Together with the formula in cell B12, Excel now knows that it should replace cell C4 with 60% to calculate the total profit, replace cell C4 with 70% to calculate the total profit, etc.
Note: this is a one variable data table so we leave the Row input cell blank.
Conclusion: if you sell 60% for the highest price, you obtain a total profit of $3800, if you sell 70% for the highest price, you obtain a total profit of $4100, etc.
Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:B17 and press Delete.
Two Variable Data Table
To create a two variable data table, execute the following steps.
1. Select cell A12 and type =D10 (refer to the total profit cell).
2. Type the different unit profits (highest price) in row 12.
3. Type the different percentages in column A.
4. Select the range A12:D17.
We are going to calculate the total profit for the different combinations of 'unit profit (highest price)' and '% sold for the highest price'.
5. On the Data tab, in the Forecast group, click What-If Analysis.
6. Click Data Table.
7. Click in the 'Row input cell' box (the unit profits are in a row) and select cell D7.
8. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.
We select cell D7 because the unit profits refer to cell D7. We select cell C4 because the percentages refer to cell C4. Together with the formula in cell A12, Excel now knows that it should replace cell D7 with $50 and cell C4 with 60% to calculate the total profit, replace cell D7 with $50 and cell C4 with 70% to calculate the total profit, etc.
Conclusion: if you sell 60% for the highest price, at a unit profit of $50, you obtain a total profit of $3800, if you sell 80% for the highest price, at a unit profit of $60, you obtain a total profit of $5200, etc.
Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:D17 and press Delete.
Часто при расчетах в Excel у нас возникают ситуации, когда нужно просчитать формулу при изменении переменной или переменных. За примерами далеко ходить не надо:
- Какой будет ежемесячный платеж, если берется кредит на 100 тыс. руб. на 3 года при различных процентных ставках.
- Сколько необходимо платить в месяц, если взять в кредит на год, два, три, четыре, пять при различных процентных ставках банка.
Задача 1. А ведь ничего сложного
На самом деле, ничего сверхъестественного в вычислениях нет. Для расчета ежемесячного платежа по аннуитету используется финансовая функция Excel ПЛТ (PMT). Соответственно, зная желаемую сумму займа и срок кредитования, можно рассчитать ежемесячный платеж.
Расчет ежемесечного платежа с помощью ПЛТ
Общая сумма выплат рассчитывается как ежемесячный платеж умноженный на количество периодов (всего 36 месяцев), проценты переплаты – это общая сумма выплат минус сумма займа.
Теперь, для того, чтобы сделать аналогичные расчеты, достаточно добавить желаемые варианты годового процента, в формулах расчета правильно воспользоваться абсолютными и относительными ссылками, ссылаясь на данные для расчета, и воспользоваться автозаполнением.
Благодаря правильному использованию различных типов ссылок и автозаполнению, можно сделать подсчет для различных процентных ставок
Шаг 1. Делается первый необходимый расчет и создается заготовка под заполнение данными остальных расчетов. Здесь, кстати, абсолютно не важно каким типом ссылок пользоваться, поскольку автозаполнение применятся не будет.
Также, поскольку у нас расчет идет по одной переменной, то заполняем только одно поле диалогового окна, а второе оставляем пустым.
Выбор ссылки на переменную в диалоговом окне
После расчета в строке формул находится только специальная функция ТАБЛИЦА
Задача 2. Рассчитать ежемесячный платеж при различных сроках займа и различных процентных ставках.
Подготовительная таблица выглядит следующим образом.
Заготовка для прогноза значений с двумя переменными
При выделении таблицы важно, чтобы в левой верхней ячейке находилась формула с расчетом, в которую должны будут подставляться новые значения переменных.
Расстановка ссылок на переменные в диалоговом окне
Конечно, данный инструмент работает не только с функцией расчет платежа по аннуитету ПЛТ, а с любой формулой, где необходимо проследить изменения в зависимости от изменения определенных переменных.
Читайте также: