Как составить таблицу значений функции двух переменных в excel
Таблица данных – это диапазон ячеек, по которому видно, как изменение одной или двух переменных в формулах повлияет на результаты вычисления этих формул. Таблицы данных позволяют быстро вычислять несколько результатов за одну операцию, а также просматривать и сравнивать результаты всех вариантов формулы на одном листе.
Таблицы данных входят в набор команд, которые называются средствами анализа "что если". При использовании таблиц данных вы выполняете анализ "что если". Средства анализа "что если" изменяют значения в ячейках и показывают, как эти изменения повлияют на результаты формул на листе. Например, вы можете использовать таблицу данных для изменения процентной ставки и срока ссуды с целью определения возможных сумм ежемесячных платежей.
В Excel предлагаются средства анализа "что если" трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Подбор параметров отличается от сценариев и таблиц данных тем, что при его использовании берется результат и определяются возможные входные значения для его получения. Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, в таблицах данных все результаты представлены в одной таблице на одном листе. С помощью таблиц данных можно легко и быстро проверить диапазон возможностей. Поскольку при этом используются всего одна или две переменные, вы можете без труда прочитать результат и поделиться им в табличной форме.
В таблице данных может быть не больше двух переменных. Для анализа большего количества переменных необходимо использовать сценарии. Хотя таблица данных ограничена только одной или двумя переменными (одна для подстановки значений по столбцам, вторая — по строкам), она позволяет использовать множество разных значений переменных. В сценарии можно использовать не более 32 разных значений, но вы можете создавать сколько угодно сценариев.
Общие сведения о таблицах данных
Вы можете создавать таблицы данных с одной или двумя переменными в зависимости от числа переменных и формул, которые необходимо проверить. Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, таблицу данных с одной переменной можно использовать, чтобы узнать, как разные процентные ставки повлияют на размер ежемесячного платежа, вычисляемый с использованием функции ПЛТ. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.
Дополнительные сведения см. в статье Функция ПЛТ.
Ячейка D2 содержит формулу для расчета платежа =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейку ввода B3.
Таблица данных с одной переменной
список значений, которые Excel подставить в ячейку ввода B3.
Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.
Ячейка C2 содержит формулу для расчета платежа =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.
Таблица данных с двумя переменными
список значений, которые Excel подставить в ячейку ввода строки (B4).
список значений, которые Excel подставить в ячейку ввода столбца B3.
Таблицы данных пересчитываются всякий раз при пересчете листа, даже если в них не были внесены изменения. Для ускорения пересчета листа, содержащего таблицу данных, можно изменить параметры вычислений так, чтобы автоматически пересчитывался лист, но не таблицы.
Введите в отдельном столбце или в отдельной строке список значений, которые нужно подставлять в ячейку ввода. Оставьте по обе стороны от значений несколько пустых строк и столбцов.
Выполните одно из указанных ниже действий.
Ориентация таблицы данных
Необходимые действия
По столбцу (значения переменной находятся в столбце)
Введите формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее столбца значений.
На рисунке в разделе "Обзор" показана ориентированная по столбцу таблица данных с одной переменной, формула находится в ячейке D2.
Примечание: Если требуется исследовать влияние различных значений на другие формулы, введите дополнительные формулы в ячейки справа от первой формулы.
По строке (значения переменной находятся в строке)
Введите формулу в ячейку, расположенную на один столбец левее первого значения и на одну ячейку ниже строки значений.
Примечание: Если требуется исследовать влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.
Выделите диапазон ячеек с формулами и значениями, которые нужно заменить. На первом рисунке в разделе "Обзор" это диапазон C2:D5.
В Excel 2016 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.
В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.
Выполните одно из действий, указанных ниже.
Ориентация таблицы данных
Необходимые действия
Введите ссылку на ячейку ввода в поле Подставлять значения по строкам. На первом рисунке ячейка ввода — это B3.
Введите ссылку на ячейку ввода в поле Подставлять значения по столбцам.
Примечание: После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке ячейки результатов имеют денежный формат.
Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
Выполните одно из действий, указанных ниже.
Ориентация таблицы данных
Необходимые действия
По столбцу (значения переменной находятся в столбце)
Введите новую формулу в пустую ячейку, расположенную в верхней строке таблицы справа от имеющейся формулы.
По строке (значения переменной находятся в строке)
Введите новую формулу в пустую ячейку, расположенную в первом столбце таблицы под имеющейся формулой.
Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
В Excel 2016 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.
В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.
Выполните одно из действий, указанных ниже.
Ориентация таблицы данных
Необходимые действия
Введите ссылку на ячейку ввода в поле Подставлять значения по строкам.
Введите ссылку на ячейку ввода в поле Подставлять значения по столбцам.
В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода.
В ячейку на листе введите формулу, которая ссылается на две ячейки ввода. В приведенном ниже примере, где исходные значения формулы введены в ячейки B3, B4 и B5, введите формулу =ПЛТ(B3/12;B4;-B5) в ячейку C2.
Введите один список входных значений в том же столбце под формулой. В данном примере нужно ввести разные процентные ставки в ячейки C3, C4 и C5.
Введите второй список справа от формулы в той же строке. Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.
Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5). В данном примере выделяется диапазон C2:E5.
В Excel 2016 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.
В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.
В поле Ячейка ввода строки введите ссылку на ячейку ввода для входных значений в строке. Введите B4 в поле Ячейка ввода строки.
В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце. Введите B3 в поле Ячейка ввода столбца.
Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На рисунке ниже ячейка C2 содержит формулу для расчета платежа =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.
список значений, которые Excel подставить в ячейку ввода строки (B4).
список значений, которые Excel подставить в ячейку ввода столбца B3.
Важно: Если выбран этот вариант вычисления, при пересчете книги таблицы данных не пересчитываются. Чтобы выполнить пересчет таблицы данных вручную, выделите содержащиеся в ней формулы и нажмите клавишу F9. Чтобы использовать эту клавишу в Mac OS X версии 10.3 или более поздней, сначала необходимо отключить ее назначение в Exposé. Дополнительные сведения см. в Excel сочетания клавиш Windows.
В меню Excel выберите пункт Параметры.
В разделе Формулы и списки выберите пункт Вычисление, а затем — параметр Автоматически, кроме таблиц данных.
Таблица данных в Excel представляет собой диапазон, который оценивает изменение одной или двух переменных в формуле. Другими словами, это Анализ "что если", о котором мы говорили в одной из прошлых статей (если Вы ее не читали - очень рекомендую ознакомиться по этой ссылке), в удобном виде. Вы можете создать таблицу данных с одной или двумя переменными.
Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене - $50 и определенный % книг по более низкой цене - $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.
Таблица данных с одной переменной.
Что бы создать таблицу данных с одной переменной, выполните следующие действия:
1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).
2. Введите различные проценты в столбце А.
3. Выберите диапазон A12:B17.
Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.
4. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.
5. Кликните в поле "Подставлять значения по строкам в: "и выберите ячейку C4.
Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.
Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода ("Подставлять значения по столбцам в: ") мы оставляем пустой.
Вывод: Если Вы продадите 60% книг по высокой цене, то Вы получите общую выручку в размере $3 800, если Вы продадите 70% по высокой цене, то получите $4 100 и так далее.
Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.
Таблица данных с двумя переменными.
Что бы создать таблицу с двумя переменными, выполните следующие шаги.
1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).
2. Внесите различные варианты высокой цены в строку 12.
3. Введите различные проценты в столбце А.
4. Выберите диапазон A12:D17.
Мы будем рассчитывать выручку от реализации книг в различных комбинациях высокой цены и % продаж книг по высокой цене.
5. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.
6. Кликните в поле "Подставлять значения по столбцам в: " и выберите ячейку D7.
7. Кликните в поле "Подставлять значения по строкам в: " и выберите ячейку C4.
Мы выбрали ячейку D7, потому что высокая цена на книги задается именно в этой ячейке. Мы выбрали ячейку C4, потому что процент продаж по высокой цене задается именно в этой ячейке. Вместе с формулой в ячейке A12, Excel теперь знает, что он должен заменять значение ячейки D7 начиная с $50 и в ячейке С4 начиная с 60% для расчета общей выручки, до $70 и 100% соответсвенно.
Вывод: Если Вы продадите 60% книг по высокой цене в размере $50, то Вы получите общую выручку $3 800, если Вы продадите 80% по высокой цене в размере $60, то получите $5 200 и так далее.
Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.
Спасибо за внимание. Теперь Вы сможете более эффективно применять один из видов анализа "что если" , а именно формирование таблиц данных с одной или двумя переменными.
Остались вопросы - задавайте их в комментариях ниже, также не забывайте подписываться на нас в социальных сетях.
Как создать таблицу данных с двумя переменными в Excel?
Таблица данных с двумя переменными помогает нам проанализировать, как сочетание двух разных переменных влияет на общую таблицу данных. Само слово предполагает две переменные, включенные в эту таблицу данных. Проще говоря, когда две переменные меняются, что влияет на результат. В одной таблице данных переменных изменяется только одна переменная, но здесь две переменные изменяются одновременно.
Примеры
Давайте рассмотрим несколько примеров, чтобы увидеть, как мы можем создать таблицу данных с двумя переменными в Excel.
Вы можете скачать этот шаблон Excel таблицы данных с двумя переменными здесь — Шаблон таблицы данных с двумя переменными Excel
Предположим, вы берете ссуду в банке и обсуждаете с менеджером банка вашу процентную ставку и срок выплаты. Вам необходимо проанализировать при разных процентных ставках и в разные периоды погашения, какую ежемесячную сумму EMI вам нужно платить.
Кроме того, предположим, что вы наемный работник и после всех ваших ежемесячных обязательств вы можете сэкономить максимум рупий. 18 500 / -.
Первоначальное предложение формирует банк как мех.
При процентной ставке 22% ежемесячный EMI за 3 года составляет 19 095.
Создайте такую таблицу.
Теперь в ячейку F8 дайте ссылку на ячейку B5 (которая содержит расчет EMI).
Выберите таблицу данных, которую мы создали для создания сценариев.
Перейдите в раздел «Данные», затем выберите «Что, если», анализ и таблица данных.
Теперь щелкните Таблица данных. Откроется диалоговое окно, показанное ниже.
Мы расположили наши новые таблицы, как разные процентные ставки по вертикали и разные годы по горизонтали.
В нашем исходном расчете процентная ставка находится в ячейке B4, а ячейка количества лет — в ячейке B2.
Следовательно, для ячейка ввода строки дайте ссылку на B2 (который содержит годы, а в нашей таблице годы расположены по горизонтали) и для ячейка ввода столбца дайте ссылку на B4 (который содержит процентную ставку, а в нашей таблице процентная ставка находится вертикально)
Теперь нажмите ОК. Это мгновенно создаст таблицу сценария.
Итак, теперь перед вами все сценарии. Ваша ежемесячная экономия составляет 18500 в месяц.
Вариант 1: Если вам не нужны лишние деньги.
Вам нужно договориться с банком о процентной ставке 18,5% годовых на 3 года. Если вы можете договориться об этой ставке, вам необходимо ежемесячно платить EMI в размере рупий. 18202.
Вариант 2: Если вам нужны лишние деньги.
В этом нестабильном мире вам все время нужны деньги. Так что нельзя потратить все 18500 сбережений на зарплату.
Если вы хотите, допустим, 3000 в месяц в качестве свободных денежных средств, вам нужно договориться с банкиром о максимальной сумме 15,5% в течение 3,5 лет. В этом случае вам необходимо ежемесячно платить 15 499 EMI в месяц.
Вау!! Такой полезный инструмент есть у нас в Excel. Мы можем проанализировать и подобрать план или идею в соответствии с нашими пожеланиями.
Предположим, вы вкладываете деньги в паевые инвестиционные фонды посредством SIP-планирования. Ежемесячно вы инвестируете в 4500. Вам необходимо провести анализ, чтобы узнать, какой будет доход от инвестиций через определенные годы.
Вы не уверены, когда прекратить вкладывать деньги и какой процент ожидаете.
Ниже приведены основные сведения для анализа чувствительности.
Примените функцию FV, чтобы узнать будущую стоимость после 25 лет инвестиций.
Хорошо, будущая стоимость ваших инвестиций через 25 лет составит 65 лакхов.
Теперь вам нужно знать, в разные годы и по разным ставкам, какой будет возврат инвестиций. Создайте такую таблицу.
Теперь дайте ссылку на ячейку F4 из B5 (которая содержит будущую стоимость наших первоначальных инвестиций).
Выберите таблицу, которую мы создали.
Перейдите в раздел «Данные», затем выберите «Что, если», анализ и таблица данных.
Теперь щелкните Таблицу данных. Откроется диалоговое окно, показанное ниже.
в СТРОКА, ячейка ввода выберите укажите ссылку на ячейку B2 (которая содержит количество лет). Причина, по которой мы выбрали эту ячейку, потому что мы создали новую таблицу, и в этой таблице наши годы в формате строки, то есть по горизонтали.
в COLUMN, входная ячейка select дать ссылку на ячейку B4 (которая содержит ожидаемый процент возврата). Причина, по которой мы выбрали эту ячейку, потому что мы создали новую таблицу, и в этой таблице наши ожидаемые проценты представлены в формате столбца, т. Е. По вертикали.
Нажмите Ok, это создаст для вас таблицу сценариев.
Посмотрите на ячейки, которые я выделил. В первой попытке нам нужно подождать 25 лет, чтобы получить сумму в 65 лакхов при доходности 10,5%. Однако при доходности 13% мы получим эту сумму через 22 года. Точно так же при ставке возврата 15% мы получаем эту сумму всего за 20 лет.
Вот как мы можем провести анализ чувствительности, используя таблицу данных с двумя переменными в Excel.
Динамические таблицы в excel — это таблицы, в которых при вставке нового значения таблица сама регулирует свой размер, для создания динамической таблицы в excel у нас есть два разных метода, один из которых создает таблицу данных из table, а другой — с помощью функции смещения, в динамических таблицах отчеты и сводные таблицы также изменяются по мере изменения данных в динамической таблице.
Динамические таблицы в Excel
Под динамикой понимается процессорная система, характеризующаяся постоянным изменением или изменением активности. Точно так же в Excel, когда мы создаем списки или данные в книге и составляем из них отчет, но если мы добавляем какие-либо данные или удаляем их, перемещаем или изменяем данные, то весь отчет может быть неточным. В Excel есть решение для этого в виде динамических таблиц.
Теперь возникает вопрос, зачем нам динамический диапазон или динамические таблицы. Ответ на этот вопрос заключается в том, что всякий раз, когда список или диапазон данных обновляется или изменяется, это не гарантирует, что отчет будет изменен в соответствии с изменением данных.
По сути, есть два основных преимущества динамических таблиц:
- Динамический диапазон будет автоматически расширяться или сужаться в соответствии с изменением данных.
- Сводные таблицы на основе динамической таблицы в Excel могут автоматически обновляться при обновлении сводной таблицы.
Как создать динамические таблицы в Excel?
Существует два основных способа использования динамических таблиц в Excel: 1) Использование ТАБЛИЦ и 2) Использование функции OFFSET.
Используя таблицы, мы можем создать динамическую таблицу в Excel и построить сводную таблицу на основе динамической таблицы.
пример
У нас есть следующие данные,
Если мы создадим сводную таблицу с этим нормальным диапазоном данных от A1 до E6, то, если мы вставим данные в строку 7, они не отразятся в сводной таблице.
Итак, сначала мы сделаем динамический диапазон.
Так как у наших данных есть заголовки, не забудьте установить флажок «Моя таблица имеет заголовки» и нажать ОК.
В сводной таблице обновите сводную таблицу.
В нашей динамической сводной таблице автоматически обновляются данные о продукте 6 в сводной таблице.
Мы также можем использовать функцию OFFSET для создания динамических таблиц в Excel. Давайте посмотрим на один такой пример.
пример
У меня есть прайс-лист на мои продукты, который я использую для своих расчетов,
Выберите данные и дайте им имя
Теперь, когда я обращаюсь к прайс-листу набора данных, он переводит меня к данным в диапазоне B2: C7, в котором есть мой прайс-лист. Но если я обновлю другую строку данных, она все равно приведет меня к диапазону B2: C7, потому что наш список статичен.
Мы будем использовать функцию смещения, чтобы сделать диапазон данных динамическим.
В Ссылке написать следующую формулу:
= смещение (Sheet2! $ B $ 2,1,0, counta (Sheet2! $ B: $ B) -1,2)
= смещение (
Вот как мы можем использовать функцию смещения для создания динамических таблиц.
Таблица данных — это диапазон ячеек, в котором можно изменять значения в некоторых ячейках и вы можете найти разные ответы на вопрос. Хороший пример таблицы данных с использованием функции 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.
Читайте также: