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

Содержание

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

Таблица данных в Excel представляет собой диапазон, который оценивает изменение одной или двух переменных в формуле. Другими словами, это Анализ «что если», о котором мы говорили в одной из прошлых статей (если Вы ее не читали — очень рекомендую ознакомиться по этой ссылке), в удобном виде. Вы можете создать таблицу данных с одной или двумя переменными.

Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене — $50 и определенный % книг по более низкой цене — $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.

Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа «что если» в Excel.

Таблица данных с одной переменной.

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

1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).

2. Введите различные проценты в столбце А.

3. Выберите диапазон A12:B17.

Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.

4. На вкладке Данные, кликните на Анализ «что если» и выберите Таблица данных из списка.

5. Кликните в поле «Подставлять значения по строкам в: «и выберите ячейку C4.

 
Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.

Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода («Подставлять значения по столбцам в: «) мы оставляем пустой.

6. Нажмите ОК.

Результат.

 
Вывод: Если Вы продадите 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% соответсвенно.

8. Нажмите ОК.

Результат.

Вывод: Если Вы продадите 60% книг по высокой цене в размере $50, то Вы получите общую выручку $3 800, если Вы продадите 80% по высокой цене в размере $60, то получите $5 200 и так далее.

Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.

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

Остались вопросы — задавайте их в комментариях ниже, также не забывайте подписываться на нас в социальных сетях.

Таблицы подстановки в Эксель (Excel) на простом примере

Для анализа данных при выборе оптимального варианта финансового решения зачастую применяются Таблицы подстановки в Excel.Они позволяют проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.

Таблицы подстановки данных можно использовать для

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

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

На конкретном примере

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

Порядок работы

=ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.

  • Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:

=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.

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

  • После подготовки исходных данных перейти к тому месту рабочего листа, где будут располагаться рассчитываемые значения в зависимости от изменения одной переменной и от различных рассчитываемых формул (смотрите рисунок ниже). В верхней строке будущей таблицы над местом расположения результатов указать адрес каждой формулы, для которой нужно получить список результатов. Вместо адреса можно поместить в ячейку саму формулу (ячейки В9 и С9 содержат формулы). Слева расположить различные значения исходных данных, которые необходимо протестировать. Подготовку исходного диапазона данных — различных величин процентной ставки в рассматриваемом примере можно осуществить с помощью маркера автозаполнения.
  • Выделить ячейки, которые будут содержать таблицу. При этом самым левым столбцом таблицы должен быть столбец исходных значений, а в самой верхней строкой должна быть строка анализируемых формул.

Расчет ежемесячного платежа в ЭксельРасчет ежемесячного платежа в ЭксельРасчет платежей

  • Далее вам нужно выделить диапазон ячеек A9:C18 , после чего перейти на вкладку данные, «анализ что-если» таблица данных. Первое поле «подставлять значения по столбцам в» оставить пустым, а в поле «подставлять значения по строкам в» указать ячейку с величиной процентной ставки зафиксировав ее знаками доллара $B$4.

Подстановка данных в ЭксельПодстановка данных в ЭксельПодстановка данных

Результат расчетов:

Результат расчетов в ЭксельРезультат расчетов в ЭксельРезультат

Глава 21. Таблицы данных

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

В этой главе рассматривается таблица данных, которая создает массив, как результат работы функции Таблица (эту функцию нельзя ввести в ячейки вручную). Таблица данных – это быстрый и простой способ выполнить анализ «что если» для сложных взаимосвязанных вычислений на основе формул. Эта функция позволяет изменять одну или две формулы входов и отображение того, что результаты. В главах 4, 5 и 10 уже были приведены примеры таблиц данных, которые использовались в сочетании с функциями базы данных.

Рис. 21.1. Ячейка В6 содержит формулу, которая косвенно зависит от значения ячейки В2

Скачать заметку в формате Word или pdf, примеры в формате Excel

Анализ «что если» на основе Таблицы с одной переменной

На рис. 21.1 в ячейки В6 используется функция ПЛТ, косвенно зависящая от значения ячейки В2. Если вы измените годовую ставку ставка, функция ПЛТ обновит значение в ячейке В6. Цель состоит в том, чтобы одновременно увидеть, как месячный платеж будет меняться при пяти различных годовых ставках. Хотя это можно сделать путем написания формулы, функция Таблица может быть полезна по двум причинам:

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

Чтобы создать таблицу данных:

  1. Создайте заголовки А9:В9. В ячейке В10 введите формулу =В6. В ячейки А11:А15 введите значения годовой ставки для анализа. Выделите диапазон А10:В15.
  2. Пройдите по меню ДАННЫЕ –> Анализ «что если» –> Таблица данных, чтобы открыть диалоговое окно Таблица данных, или нажав и удерживая клавишу Alt, последовательно нажмите Ы, Ё, Т (после нажатия Alt в меню будут появляться подсказки).
  3. Поскольку вы анализируете влияние годовой ставки, укажите ссылку на нее в поле Подставлять значение по строкам в (рис. 21.2). Вы говорите Таблице данных, заменить значение из ячейки В2 в процессе расчета ПЛТ и вместо него подставить в формулу значения из диапазона А11:А15.
  4. Нажмите ОК.

Рис. 21.2. Диалоговое окно Таблица данных

Если вы выделите диапазон В11:В15 и взглянете на строку формул, то увидите формулу массива Таблица со ссылкой на ячейку В2. Функцию Таблица нельзя ввести с клавиатуры; она автоматически создается при использовании диалогового окна Таблица данных.

Рис. 21.3. Функцию Таблица можно ввести только с помощью диалогового окна Таблица данных

На рис. 21.4 ячейки в диапазон E3:I3 содержат различные формулы, которые прямо или косвенно ссылаются на число проданных штук (в ячейке В3). Используя Таблицу данные можно выполнить анализ «что если» для пяти формул. Причем все они основываются на одной и той же переменной, расположенной в диапазоне D4:D12.

Рис. 21.4. Одна переменная Таблицы данных может работать на нескольких формулах; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Две переменные в Таблице данных

В следующем примере задача – найти минимальное значение по двум критериям. Для этого используется Таблица данных на основе двух переменных (рис. 21.5). Поместите формулу в левом верхнем углу Таблицы данных (в нашем примере – в ячейке F8), и введите настройте в диалоговом окне Таблица данных, как показано на рис. 21.5.

Рис. 21.5. Таблица данных с двумя переменными

Второй пример (рис. 21.6) вы уже видели в главе 5. Там использовалась формула массива. Например, в ячейке F9: =ИНДЕКС($C$2:$C$15;ПОИСКПОЗ($E9&F$8;$A$2:$A$15&$B$2:$B$15;0)). Решение на основе Таблицы данных проще, и работает быстрее.

Рис. 21.6. Использование Таблицы данных, как альтернатива ВПР по двум параметрам

Одно заключительное замечание по поводу Таблицы данных: существует параметр, который позволяет отключить автоматическое обновление Таблиц данных, при этом другие формулы будут пересчитываться автоматически. Если ваш файл «тормозит», пройдите по меню ФАЙЛ –> Параметры, перейдите на вкладку Формулы, и выберите опцию автоматически, кроме таблиц данных (рис. 21.7). Когда вы всё же захотите обновить вычисления в Таблице данных, нажмите F9.

Рис. 21.7. Отключение автоматического вычисления Таблиц данных

 

Что, если анализ с таблицами данных

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

Существует два типа таблиц данных —

  • Таблицы данных с одной переменной
  • Таблицы данных с двумя переменными

Если в вашей проблеме анализа более двух переменных, вам нужно использовать Scenario Manager Tool of Excel. Подробнее см. Главу « Что, если анализ с помощью менеджера сценариев» в этом руководстве.

Таблицы данных с одной переменной

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

пример

Существует кредит в размере 5 000 000 на срок 30 лет. Вы хотите знать ежемесячные платежи (EMI) для различных процентных ставок. Вам также может быть интересно узнать сумму процентов и основной суммы, которая выплачивается во второй год.

Анализ с таблицей данных с одной переменной

Анализ с использованием таблицы данных с одной переменной необходимо выполнить в три этапа:

Шаг 1 — Установите необходимый фон.

Шаг 2 — Создать таблицу данных.

Шаг 3 — Выполнить анализ.

Позвольте нам понять эти шаги в деталях —

Шаг 1: Установите необходимый фон

  • Предположим, что процентная ставка составляет 12%.

  • Перечислите все необходимые значения.

  • Назовите ячейки, содержащие значения, чтобы формулы имели имена вместо ссылок на ячейки.

  • Установите расчеты для EMI, совокупного интереса и совокупного принципала с помощью функций Excel — PMT, CUMIPMT и CUMPRINC соответственно.

Предположим, что процентная ставка составляет 12%.

Перечислите все необходимые значения.

Назовите ячейки, содержащие значения, чтобы формулы имели имена вместо ссылок на ячейки.

Установите расчеты для EMI, совокупного интереса и совокупного принципала с помощью функций Excel — PMT, CUMIPMT и CUMPRINC соответственно.

Ваш рабочий лист должен выглядеть следующим образом —

Установить необходимый фон

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

Шаг 2: Создать таблицу данных

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

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

Создать таблицу данных

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

  • Введите первую функцию ( PMT ) в ячейку на одну строку выше и одну ячейку справа от столбца значений. Введите другие функции ( CUMIPMT и CUMPRINC ) в ячейки справа от первой функции.

    Теперь две строки над значениями процентной ставки выглядят следующим образом:

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

Введите первую функцию ( PMT ) в ячейку на одну строку выше и одну ячейку справа от столбца значений. Введите другие функции ( CUMIPMT и CUMPRINC ) в ячейки справа от первой функции.

Теперь две строки над значениями процентной ставки выглядят следующим образом:

Тип Функции

Таблица данных выглядит следующим образом:

Ниже таблицы данных

Шаг 3. Выполните анализ с помощью инструмента «Таблица данных анализа« что, если »».

  • Выберите диапазон ячеек, который содержит формулы и значения, которые вы хотите заменить, т.е. выберите диапазон — E2: h23.

  • Нажмите вкладку ДАННЫЕ на ленте.

  • Нажмите «Что, если анализ» в группе «Инструменты данных».

  • Выберите Data Table в раскрывающемся списке.

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

Нажмите вкладку ДАННЫЕ на ленте.

Нажмите «Что, если анализ» в группе «Инструменты данных».

Выберите Data Table в раскрывающемся списке.

Сделать анализ

Откроется диалоговое окно « Таблица данных ».

  • Щелкните значок в поле ввода столбца.
  • Нажмите на ячейку Interest_Rate , которая является C2.

Таблица данных

Вы можете видеть, что входная ячейка Column берется как $ C $ 2. Нажмите ОК.

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

Заполнить таблицу данных

Если вы можете заплатить EMI 54 000, вы можете заметить, что для вас подходит процентная ставка 12,6%.

Таблицы данных с двумя переменными

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

пример

Существует кредит в 50 000 000. Вы хотите знать, как различные комбинации процентных ставок и сроков кредитования повлияют на ежемесячный платеж (EMI).

Анализ с таблицей данных с двумя переменными

Анализ с помощью таблицы данных с двумя переменными должен быть выполнен в три этапа —

Шаг 1 — Установите необходимый фон.

Шаг 2 — Создать таблицу данных.

Шаг 3 — Выполнить анализ.

Шаг 1: Установите необходимый фон

  • Предположим, что процентная ставка составляет 12%.

  • Перечислите все необходимые значения.

  • Назовите ячейки, содержащие значения, чтобы формула имела имена вместо ссылок на ячейки.

  • Установите расчет для EMI с помощью функции Excel — PMT .

Предположим, что процентная ставка составляет 12%.

Перечислите все необходимые значения.

Назовите ячейки, содержащие значения, чтобы формула имела имена вместо ссылок на ячейки.

Установите расчет для EMI с помощью функции Excel — PMT .

Ваш рабочий лист должен выглядеть следующим образом —

Установить фон

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

Шаг 2: Создать таблицу данных

Тип = EMI в ячейке F2.

Установить EMI

  • Введите первый список входных значений, т. Е. Процентные ставки, вниз по столбцу F, начиная с ячейки под формулой, т. Е. F3.

  • Введите второй список значений ввода, т. Е. Количество платежей в строке 2, начиная с ячейки справа от формулы, т. Е. G2.

    Таблица данных выглядит следующим образом —

Введите первый список входных значений, т. Е. Процентные ставки, вниз по столбцу F, начиная с ячейки под формулой, т. Е. F3.

Введите второй список значений ввода, т. Е. Количество платежей в строке 2, начиная с ячейки справа от формулы, т. Е. G2.

Таблица данных выглядит следующим образом —

Введите входные значения

Выполните анализ с помощью таблицы данных инструмента «Что, если»

  • Выберите диапазон ячеек, который содержит формулу и два набора значений, которые вы хотите заменить, т.е. выберите диапазон — F2: L13.

  • Нажмите вкладку ДАННЫЕ на ленте.

  • Нажмите «Что, если анализ» в группе «Инструменты данных».

  • Выберите Data Table из выпадающего списка.

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

Нажмите вкладку ДАННЫЕ на ленте.

Нажмите «Что, если анализ» в группе «Инструменты данных».

Выберите Data Table из выпадающего списка.

Выполнить анализ

Откроется диалоговое окно «Таблица данных».

  • Нажмите на значок в поле ввода строки Row.
  • Нажмите на ячейку NPER , которая является C3.
  • Снова щелкните значок в поле ввода строки Row.
  • Затем щелкните значок в поле ввода столбца Column.
  • Нажмите на ячейку Interest_Rate, которая является C2.
  • Снова щелкните значок в поле ввода столбца Column.

Поле ввода столбца

Вы увидите, что входная ячейка строки берется как $ C $ 3, а входная ячейка столбца принимается как $ C $ 2. Нажмите ОК.

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

Переименовать поля ввода ячеек

Если вы можете заплатить EMI 54 000, вам подойдет процентная ставка 12,2% и 288 EMI. Это означает, что срок кредита будет 24 года.

Расчет таблицы данных

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

Ускорение расчетов на рабочем листе

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

  • Из параметров Excel.
  • С ленты.

Из параметров Excel

  • Нажмите вкладку ФАЙЛ на ленте.
  • Выберите Options из списка на левой панели.

Откроется диалоговое окно «Параметры Excel».

На левой панели выберите Формулы .

Выберите параметр Автоматический, кроме таблиц данных в разделе Расчет рабочей книги в разделе Параметры расчета . Нажмите ОК.

Нажмите вкладку ФОРМУЛ на ленте.

Нажмите Параметры расчета в группе Расчеты.

Выберите « Автоматически, кроме таблиц данных» в раскрывающемся списке.

4 техники анализа данных в Microsoft Excel

Юлия Перминова

Тренер Учебного центра Softline с 2008 года.

1. Сводные таблицы

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

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

Как работать

Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.

  1. Откройте файл с таблицей, данные которой надо проанализировать.
  2. Выделите диапазон данных для анализа.
  3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
  4. Должно появиться диалоговое окно «Создание сводной таблицы».
  5. Настройте отображение данных, которые есть у вас в таблице.

Сводные таблицы

Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.

Сводные таблицы в Excel

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

Пример сводной таблицы в Excel

Можно её детализировать, например, по странам. Переносим «Страны».

Сводная таблица в Microsoft Excel

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

Как создать сводную таблицу

2. 3D-карты

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

Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

Как работать

  1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
  2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
  3. Выделите диапазон данных для анализа.
  4. На вкладке «Вставка» есть кнопка 3D-карта.

3D-карты

Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.

3D-карты в Excel

Круговая диаграмма по годам

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

Круговая диаграмма

3. Лист прогнозов

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

Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).

Как работать

  1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
  2. Выделите два ряда данных.
  3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
  4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
  5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

Лист прогнозов в Excel

4. Быстрый анализ

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

Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

Как работать

  1. Откройте таблицу с данными для анализа.
  2. Выделите нужный для анализа диапазон.
  3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

Быстрый анализ в Excel

В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.

Быстрый анализ в MS Excel

Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

Быстрый анализ в MS Excel

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

Читайте также:

Аналитические таблицы в excel примеры

Получение нескольких результатов с помощью таблицы данных

​Смотрите также​​Исходная таблица:​ «Офис»). Выполняем действия,​ принципу составляем формулы​анализ продаж, анализ брака​ от 1 до​ его нужно сделать​ нахождения объемов продаж​ для создания Сводной​ Но, в результате​ вычислений. Для этого​Суммировать по​В разделе​На новый лист​введите ссылку на​ указанных ниже.​ переменной в одну​Примечание:​Создадим сводную таблицу на​ изображенные на рисунке:​ для «конца года»​

​ выпускаемой продукции, анализ​ 10 дней, в​ в 2-х вариантах:​ по каждому Товару,​ таблицы достаточно выделить​ эффект от освоения​ щелкните стрелку справа​и​​Укажите, куда следует поместить​​, чтобы поместить сводную​ ячейку ввода для​Если таблица данных ориентирована​ или несколько формул​ Мы стараемся как можно​ новом листе и​На панели быстрого доступа​ и «пассива». Копируем​ времени, которое сотрудники​ период 11-20 дней​​ один для партий​​ мы не заботились​

Общие сведения

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

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

​ ссылка на ячейку​​ этих формул с​ актуальными справочными материалами​ отображения среднего значения​ В выпадающем меню​ В новых столбцах​ или выявить резерв​Чтобы разгруппировать значения выберите​ другой – для​ – все Продажи,​ меню Работа с​

​ этой статье разберемся,​​Параметры полей значений​

​ копий.​На новый лист​ выбрать вариант​​В поле​​ на ячейку ввода​​ помощью таблицы данных​​ на вашем языке.​ времени активности на​ выбираем команду «Прогрессия».​ устанавливаем процентный формат.​ времени на изготовление,​ пункт Разгруппировать в​ убыточных. Для этого:​ относящиеся к одному​ таблицами/ Конструктор/ Сервис​ как создавать и​.​

​При добавлении новых данных​или​На существующий лист​Подставлять значения по строкам​ в поле​ с одной переменной.​ Эта страница переведена​ сайте:​ Заполняем диалоговое окно.​Проанализируем динамику изменений в​ доставку, т.д. продукции,​ меню Работа со​Очистим ранее созданный отчет:​ Товару были просуммированы.​ выбрать пункт Сводная​ применять Сводные таблицы.​Затем измените функцию в​ в источник необходимо​

​На существующий лист​, а затем указать​, введите​Подставлять значения по строкам​ Например, можно использовать​ автоматически, поэтому ее​Произведем расчет для пользователя​ В столбце В​ абсолютных величинах. Делаем​ проекта, др.​ сводными таблицами/ Параметры/​ выделите любое значение​Если требуется, например,​ таблица.​Начнем с требований к​ разделе​ обновить все сводные​. При выборе варианта​ место для отображения​B3​. На приведенном выше​

​ таблицу данных с​ текст может содержать​ из GB (Великобритания):​

Базовые сведения о таблицах данных

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

​В появившемся окне нажмем​ исходной таблице.​​Операция​

​ таблицы, созданные на​На существующий лист​ сводной таблицы.​.​ рисунке ячейку ввода​ одной переменной для​ неточности и грамматические​Формула выглядит очень громоздкой,​Первый результат работы:​ в котором отразим​поможет отслеживать этапы​Аналогичную группировку можно провести​ пункт меню Работа​ партий каждого Товара,​ ОК, согласившись с​каждый столбец должен иметь​. Обратите внимание на​ его основе. Чтобы​вам потребуется указать​Нажмите кнопку​Нажмите кнопку​

​ — B3.​ просмотра различных процентной​ ошибки. Для нас​​ однако ее аргументы​​Снова открываем список инструмента​ разницу между значением​

​ проекта по датам,​ по полю Дата​​ со сводными таблицами/​

​ то нужно изменить​ тем, что Сводная​ заголовок;​ то, что при​ обновить одну сводную​ ячейку для вставки​ОК​ОК​Если таблица данных ориентирована​ ставки влияют на​ важно, чтобы эта​ генерируются автоматически при​ «Анализ данных». Выбираем​ на конец года​ составить график отпусков,​ поставки. В этом​

​ Параметры/ Действия/ Очистить/​ функцию итогов. Для​ таблица будет размещена​в каждый столбец должны​​ изменении метода вычисления​​ таблицу, можно​ сводной таблицы.​.​

​.​​ по строке, введите​

​ ежемесячный платеж по​ статья была вам​ выборе соответствующих ячеек.​ «Гистограмма». Заполняем диалоговое​ и на начало.​ т.д.​ случае окно Группировка​ Очистить все;​​ этого в Сводной​​ на отдельном листе.​ вводиться значения только​ Excel автоматически добавляет​щелкнуть правой кнопкой мыши​Нажмите кнопку​Чтобы добавить поле в​Пример таблицы данных с​

Создание таблицы данных с одной переменной

​ ссылку на ячейку​ ипотеке с помощью​ полезна. Просим вас​ Итоговые результаты расчета:​ окно:​Покажем изменения в относительных​Все про разные​ по полю будет​Ставим галочки в Списке​ таблице выделите любое​На отдельном листе появится​

​ в одном формате​

  1. ​ его название в​в любом месте​ОК​ сводную таблицу, установите​ двумя переменными​ для ячейки ввода​ функции ПЛТ. Ввод​ уделить пару секунд​Определим наибольшее значение с​

  2. ​Второй результат работы:​ величинах. В новом​

    • ​ диаграммы смотрите в​​ выглядеть так:​​ полей у полей​ значение поля Продажи,​ заготовка Сводной таблицы​ (например, столбец «Дата​ раздел​ ее диапазона и​. Excel создаст пустую​ флажок рядом с​Таблица данных с двумя​ в поле​ значений переменных в​ и сообщить, помогла​

      ​ помощью соответствующей формулы:​Построить таблицу статистических характеристик​ расчетном столбце найдем​ статье «Как сделать​Теперь Сводная таблица показывает,​​ Группа, Продажи и​​ вызовите правой клавишей​

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

Contextures

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

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

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

Вместо проверки результатов с помощью одной формулы можно установить
таблицу данных и сравните результаты бок о бок. В этом руководстве
вы создадите две таблицы данных — одну с 1 переменной, а другую с
2 переменных.

Создание таблицы данных с 1 переменной

В этом примере вы создадите таблицу данных, которая показывает ежемесячный
платежи на срок от 1 до 6 лет.Количество платежей
будет варьироваться от 12 до 72.

ПРИМЕЧАНИЕ. Ячейки ввода должны находиться на том же листе, что и данные.
стол.

Информация о кредите находится в ячейках C2: C4 с указанием количества платежей.
в ячейке C3.

Для настройки таблицы данных:

  1. В ячейках B8: B13 введите количество платежей по кредитам на условиях
    От 1 до 6 лет
  2. В ячейке C7 введите функцию PMT, ссылаясь на информацию о ссуде.
    ячейки: = PMT (C2 / 12, C3, C4)
  3. Выберите ячейки B7: C13 — ячейки заголовка и ячейки для
    результаты
  4. На вкладке «Данные» ленты в группе «Работа с данными» щелкните «Что, если».
    Анализ, а затем щелкните Таблица данных.

    ribbon data table command

  5. Щелкните поле ячейки ввода столбца, а затем щелкните ячейку C3,
    который содержит переменную количества платежей.

    data table input cell

  6. Щелкните OK, чтобы закрыть диалоговое окно.
  7. Выберите ячейки с ежемесячными платежами и отформатируйте их как «Валюта».
    На снимке экрана ниже формат: «Валюта» с отрицательным
    цифры в квадратных скобках и в красном цвете.

    data table results

  8. Щелкните одну из ячеек с рассчитанными ежемесячными платежами и нажмите
    Строка формул показывает, что ячейка содержит функцию ТАБЛИЦА с
    ячейка C3 в качестве второго аргумента. Фигурные скобки в начале
    и конец формулы указывают, что это формула массива

Очистить таблицу данных

Поскольку значения таблицы данных находятся в массиве, вы не можете редактировать или
очистить отдельные ячейки.Если вы попытаетесь изменить одну ячейку, вы увидите
сообщение об ошибке — «Невозможно изменить часть таблицы данных». Если вы хотите
чтобы удалить всю таблицу или полученные значения, следуйте инструкциям
ниже.

Чтобы удалить таблицу данных с рабочего листа:

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

Для очистки только результирующих значений:

  1. Выберите все результирующие значения в таблице данных.
  2. На клавиатуре нажмите клавишу Delete

Создание таблицы данных с 2 переменными

В этом примере вы создадите таблицу данных с двумя переменными. Это
покажет ежемесячные платежи на срок от 1 до 6
лет, а процентные ставки от 2% до 6%.

ПРИМЕЧАНИЕ. Ячейки ввода должны находиться на том же листе, что и данные.
стол.

Информация о кредите находится в ячейках C2: C4, а процентная ставка — в
C2, а количество платежей в ячейке C3.

Для настройки таблицы данных:

  1. В ячейках B8: B13 введите количество платежей по кредитам на условиях
    От 1 до 6 лет
  2. В ячейках C7: G7 введите процентные ставки от 2% до 6%
  3. В ячейке B7 введите функцию PMT, ссылаясь на информацию о ссуде.
    ячейки: = PMT (C2 / 12, C3, C4)
  4. Выберите ячейки B7: G13 — ячейки заголовка и ячейки для
    результаты
  5. На вкладке «Данные» ленты в группе «Работа с данными» щелкните «Что, если».
    Анализ, а затем щелкните Таблица данных.
  6. Щелкните в поле ячейки ввода строки, а затем щелкните ячейку C2, которая
    содержит переменную процентной ставки.
  7. Щелкните поле ячейки ввода столбца, а затем щелкните ячейку C3,
    который содержит переменную количества платежей.

    data table 2 input cells

  8. Щелкните OK, чтобы закрыть диалоговое окно.
  9. Выберите ячейки с ежемесячными платежами и отформатируйте их как «Валюта».На снимке экрана ниже формат: «Валюта» с отрицательным
    цифры в квадратных скобках и в красном цвете.

    data table 2 variables

  10. Щелкните одну из ячеек с рассчитанными ежемесячными платежами и нажмите
    Строка формул показывает, что ячейка содержит функцию ТАБЛИЦА с
    ячейка C2 в качестве первого аргумента и C3 в качестве второго аргумента. В
    фигурные скобки в начале и в конце формулы означают, что
    это функция массива.

Загрузить образец файла

Нажмите здесь, чтобы загрузить данные
файл образца таблицы. Файл заархивирован и в формате xlsx с
нет макросов.

Дополнительные уроки по функциям

СУМ

ВПР

ИНДЕКС и МАТЧ

Функции подсчета

КОСВЕННАЯ

Видео о функциях

.

Таблица данных с двумя переменными в Excel

Таблица данных с двумя переменными в Excel (содержание)

Таблица данных с двумя переменными в Excel

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

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

Таблица данных с двумя переменными

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

Таблица данных с двумя переменными в Excel, пример № 1

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

Предварительные требования: Запишите пример расчета. Как упоминалось в учебном пособии Пример №1.Предположим, что общая сумма кредита составляет 1000000,00 фунтов стерлингов, процентная ставка (годовая), например, 6%, номер месяца для EMI / платежа, например, 1 месяц, и примените формулы PMT (= PMT (C3 / 12, C4, C2)) для расчета EMI .

Результат представлен ниже:

Шаг 1: Запишите желаемое количество месяцев в столбце E и сумму кредита в строке 4

Шаг 2: Введите все желаемые пользователем месяцы в столбец F

Шаг 3: Введите всю желаемую сумму ссуды в строку 5 -го листа.

Шаг 4: Выберите ячейку F5 и назначенное значение ячейки C5, нажмите Enter.

.

Создание модели данных в Excel

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

Прежде чем вы сможете начать работать с моделью данных, вам необходимо получить некоторые данные.Для этого мы воспользуемся интерфейсом Get & Transform (Power Query), поэтому вы можете сделать шаг назад и посмотреть видео или следовать нашему руководству по Get & Transform и Power Pivot.


Где Power Pivot?


Где получить и преобразовать (Power Query)?

  • Excel 2016 и Excel для Microsoft 365 — Get & Transform (Power Query) интегрирован с Excel на вкладке Data .

  • Excel 2013 — Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в File > Options > Add-Ins , затем в раскрывающемся списке Manage в нижней части панели выберите COM Add-Ins > Go . Отметьте Microsoft Power Query для Excel , затем OK , чтобы активировать его.Вкладка Power Query будет добавлена ​​на ленту.

  • Excel 2010 — Загрузите и установите надстройку Power Query. После активации на ленту будет добавлена ​​вкладка Power Query .

Начало работы

Во-первых, вам нужно получить некоторые данные.

  1. В Excel 2016 и Excel для Microsoft 365 используйте Data > Get & Transform Data > Get Data для импорта данных из любого количества внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, содержащая несколько связанных таблиц.

    В Excel 2013 и 2010 перейдите к Power Query > Получить внешние данные и выберите свой источник данных.

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

    Get & Transform (Power Query) Navigator

  3. Выберите одну или несколько таблиц, затем щелкните Загрузить .

    Если вам нужно отредактировать исходные данные, вы можете выбрать опцию Edit . Дополнительные сведения см. В разделе: Введение в редактор запросов (Power Query).

Теперь у вас есть модель данных, которая содержит все импортированные вами таблицы, и они будут отображаться в сводной таблице Список полей .

Примечания:

  • Модели создаются неявно при одновременном импорте двух или более таблиц в Excel.

  • Модели создаются явно, когда вы используете надстройку Power Pivot для импорта данных. В надстройке модель представлена ​​в виде вкладок, аналогичных Excel, где каждая вкладка содержит табличные данные. См. Раздел Получение данных с помощью надстройки Power Pivot, чтобы узнать об основах импорта данных с помощью базы данных SQL Server.

  • Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и щелкните Добавить в модель данных в Power Pivot.Вы можете сделать это, если хотите использовать функции Power Pivot, такие как отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.

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

  • Советы по уменьшению размера модели данных см. В разделе Создание модели данных с эффективным использованием памяти с помощью Excel и Power Pivot.

  • Для дальнейшего изучения см. Учебник: Импорт данных в Excel и Создание модели данных.

Создание отношений между таблицами

Следующим шагом является создание отношений между вашими таблицами, чтобы вы могли извлекать данные из любой из них. Каждая таблица должна иметь первичный ключ или уникальный идентификатор поля, например, идентификатор студента или номер класса. Самый простой способ — перетащить эти поля, чтобы соединить их в Power Pivot Diagram View .

  1. Перейдите к Power Pivot > Управляйте .

  2. На вкладке Home выберите Diagram View .

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

  4. Затем перетащите поле первичного ключа из одной таблицы в другую.Следующий пример — это диаграмма наших студенческих столов:

    Power Query Data Model Relationship Diagram View

    Мы создали следующие ссылки:

    • tbl_Students | Студенческий билет> tbl_Grades | Студенческий билет

      Другими словами, перетащите поле Student ID из таблицы Student в поле Student ID в таблице Grades.

    • tbl_Semesters | ID семестра> tbl_Grades | Семестр

    • tbl_Classes | Номер класса> tbl_Grades | Номер класса

    Примечания:

    • Имена полей не обязательно должны быть одинаковыми для создания связи, но они должны быть одного типа данных.

    • Разъемы в Diagram View имеют цифру «1» с одной стороны и «*» с другой. Это означает, что между таблицами существует связь «один ко многим», которая определяет, как данные используются в ваших сводных таблицах. См .: Отношения между таблицами в модели данных, чтобы узнать больше.

    • Соединители только указывают на наличие связи между таблицами.Они фактически не покажут вам, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите в Power Pivot > Manage > Design > Relationships > Manage Relationships . В Excel вы можете перейти к Data > Relationships .

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

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

  1. В Power Pivot перейдите к Управление .

  2. На вкладке Home выберите PivotTable .

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

  4. Щелкните ОК , и Excel добавит пустую сводную таблицу с панелью списка полей, отображаемой справа.

    Power Pivot PivotTable Field List

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

Добавить существующие несвязанные данные в модель данных

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

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

  2. Используйте один из следующих подходов для добавления данных:

  3. Щелкните Power Pivot > Добавить в модель данных .

  4. Щелкните Insert > PivotTable , а затем отметьте Add this data to the Data Model в диалоговом окне Create PivotTable.

Диапазон или таблица теперь добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. В разделе Добавление данных с помощью связанных таблиц Excel в Power Pivot.

Добавление данных в таблицу Power Pivot

В Power Pivot нельзя добавить строку в таблицу путем прямого ввода новой строки, как это можно сделать на листе Excel.Но вы можете добавлять строки, копируя и вставляя или обновляя исходные данные и обновляя модель Power Pivot.

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

См. Также

Учебные руководства Get & Transform и Power Pivot

Введение в редактор запросов (Power Query)

Создание модели данных с эффективным использованием памяти с помощью Excel и Power Pivot

Учебник: импорт данных в Excel и создание модели данных

Узнайте, какие источники данных используются в модели данных книги

Отношения между таблицами в модели данных

.

Вычислить несколько результатов с помощью таблицы данных

Подставляя разные числа в ячейку, вы можете быстро найти разные ответы на проблему. Отличный пример — использование функции PMT с разными процентными ставками и сроками ссуды (в месяцах), чтобы выяснить, какую часть ссуды вы можете позволить себе на дом или машину. Вы вводите свои числа в диапазон ячеек, называемый таблицей данных.

Здесь таблица данных — это диапазон ячеек B2: D8.Вы можете изменить значение в B4, сумму кредита и ежемесячные платежи в столбце D автоматически обновляются. Используя процентную ставку 3,75%, D2 возвращает ежемесячный платеж в размере 1 042,01 доллара США по следующей формуле: = PMT (C2 / 12, 3 доллара США, 4 доллара США).

This range of cells, B2:D8, is a data table

Вы можете использовать одну или две переменные, в зависимости от количества переменных и формул, которые вы хотите проверить.

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

В этой живой книге ячейка D2 содержит формулу оплаты = PMT (C2 / 12, 3 доллара B, 4 доллара B). Ячейка B3 — это переменная , ячейка , где вы можете указать другую продолжительность срока (количество периодов ежемесячных платежей). В ячейке D2 функция PMT подставляет процентную ставку 3.75% / 12, 360 месяцев и ссуда в размере 225 000 долларов США, при этом ежемесячный платеж составляет 1042,01 доллара США.

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

В этой живой книге ячейка C3 содержит формулу оплаты = PMT ($ B $ 3/12, $ B $ 2, B4), в которой используются две ячейки переменных, B2 ​​и B3.В ячейке C2 функция PMT подставляет процентную ставку 3,875% / 12, 360 месяцев и ссуду в размере 225 000 долларов США и вычисляет ежемесячный платеж в размере 1 058,03 долларов США.

.

Отправить ответ

avatar
  Подписаться  
Уведомление о