Home » Misc » Данные для таблицы excel

Данные для таблицы 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.


 

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

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

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

Сделайте следующее:

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

  2. Выполните одно из указанных ниже действий.

    • Если таблица данных ориентирована на столбец (значения переменных находятся в столбце), введите формулу в ячейку на одну строку выше и на одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбец, а формула содержится в ячейке D2.


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

    • Если таблица данных ориентирована на строку (значения переменных находятся в строке), введите формулу в ячейку на один столбец слева от первого значения и на одну ячейку ниже строки значений.

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

  3. Выделите диапазон ячеек с формулами и значениями, которые нужно заменить. На рисунке выше это диапазон C2:D5.

  4. На вкладке Данные нажмите кнопку Анализ >данных "что если" (в группе "Средства данных" или "Прогноз" Excel 2016 ).  

  5. Выполните одно из указанных ниже действий.

    • Если таблица данных ориентирована на столбец, введите ссылка на ячейку ячейку ввода в поле ячейки ввода Столбец. На рисунке выше ячейка ввода — B3.

    • Если таблица данных ориентирована на строку, введите ссылку на ячейку ввода в поле ячейки ввода Строка.

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

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

Выполните эти действия

  1. Сделайте это.

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

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

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

  3. На вкладке Данные нажмите кнопку Анализ> данных "что если" (в группе "Средства данных" или "Прогноз" Excel 2016 ).

  4. Сделайте следующее:

    • Если таблица данных ориентирована на столбец, введите ссылку на ячейку ввода в поле Ячейка ввода столбца.

    • Если таблица данных ориентирована на строку, введите ссылку на ячейку ввода в поле Ячейка ввода строки.

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

Сделайте следующее:

  1. В ячейку на этом сайте введите формулу, которая ссылается на две ячейки ввода.

    В следующем примере, в котором начальные значения формулы ввели в ячейки B3, B4 и B5, введите формулу =PMT(B3/12;B4;-B5) в ячейку C2.

  2. Введите один список входных значений в том же столбце под формулой.

    В данном примере нужно ввести разные процентные ставки в ячейки C3, C4 и C5.

  3. Введите второй список в той же строке, что и формула, справа от нее.

    Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.

  4. Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5).

    В данном примере выделяется диапазон C2:E5.

  5. На вкладке Данные в группе Средства данных или Прогноз (в Excel 2016 ) нажмите кнопку Анализ >"что если" (в группе "Средства данных" или "Прогноз" Excel 2016 ).  

  6. В поле Ячейка ввода строки введите ссылку на ячейку ввода для входных значений в строке.
    Введите ячейку B4 в поле Ячейка ввода строки.

  7. В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце.
    Введите B3 в поле Ячейка ввода столбца.

  8. Нажмите кнопку ОК.

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

Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На рисунке ниже ячейка C2 содержит формулу платежа =PMT(B3/12;B4;-B5),которая использует две ячейки ввода: B3 и B4.

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

Чтобы повысить производительность вычислений, выполните указанные здесь действия.

  1. Щелкните Файл > параметры > Формулы.

  2. В разделе Параметры вычислений в разделе Вычислениенажмите кнопку Автоматически, кроме таблиц данных.

    Совет: При желании на вкладке Формулы щелкните стрелку на кнопке Параметры вычислений ивыберите вариант Автоматически, кроме таблиц данных (в группе Вычисления).

Вы можете использовать несколько других средств 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.

Данные итогов в таблице Excel

Таблицы

Таблицы

Таблицы

Данные итогов в таблице Excel

  • Создание и форматирование таблиц
    Статья
  • Сортировка данных в таблице
    Статья
  • Фильтрация данных в диапазоне или таблице
    Статья
  • Данные итогов в таблице Excel
    Статья
  • Использование срезов для фильтрации данных
    Статья

Далее: Диаграммы

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. ..Меньше

Вы можете быстро подвести итоги в таблице Excel, включив строку итогов и выбрав одну из функций в раскрывающемся списке для каждого столбца. По умолчанию в строке итогов применяется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая позволяет включать или пропускать скрытые строки таблицы. Но вы также можете использовать другие функции.

  1. Щелкните любое место таблицы.

  2. Выберите Работа с таблицами > Конструктор и установите флажок Строка итогов.

  3. Строка итогов будет вставлена в нижней части таблицы.

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

  4. Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу:

    Excel создает следующую формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Midwest]). Это функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ для функции СУММ, которая является формулой со структурированными ссылками (такие формулы доступны только в таблицах Excel). См. статью Использование структурированных ссылок в таблицах Excel.

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

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

Вы можете быстро подвести итоги в таблице Excel, включив строку итогов и выбрав одну из функций в раскрывающемся списке для каждого столбца. По умолчанию в строке итогов применяется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая позволяет включать или пропускать скрытые строки таблицы. Но вы также можете использовать другие функции.

  1. Щелкните любое место таблицы.

  2. Выберите Таблица > Строка итогов.

  3. Строка итогов будет вставлена в нижней части таблицы.

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

  4. Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу:

    Excel создает следующую формулу: =ПРОМЕЖУТОЧНЫЕ. ИТОГИ(109;[Midwest]). Это функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ для функции СУММ, которая является формулой со структурированными ссылками (такие формулы доступны только в таблицах Excel). См. статью Использование структурированных ссылок в таблицах Excel.

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

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

Вы можете быстро подвести итоги в таблице Excel, включив параметр Переключить строку итогов.

  1. Щелкните любое место таблицы.

  2. Щелкните вкладку Конструктор таблиц > Параметры стилей > Строка итогов.

    Строка Итог будет вставлена в нижней части таблицы.

Настройка агрегатной функции для ячейки строки итогов

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Общие сведения о таблицах Excel

Видео: создание таблицы Excel

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

Форматирование таблицы Excel

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

Фильтрация данных в диапазоне или таблице

Преобразование таблицы в диапазон

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

Поля промежуточных и общих итогов в отчете сводной таблицы

Поля промежуточных и общих итогов в сводной таблице

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

Экспорт таблицы Excel в SharePoint

Обзор таблиц Excel — служба поддержки Майкрософт

Excel

Импорт и анализ данных

Столы

Столы

Обзор таблиц Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Больше. ..Меньше

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

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

Узнайте об элементах таблицы Excel

Таблица может включать следующие элементы:

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

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

  • Строки с полосами     Попеременное затенение или полоса в строках помогает лучше различать данные.

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

  • Итоговая строка     После добавления итоговой строки в таблицу Excel предоставляет раскрывающийся список Автосумма для выбора из таких функций, как СУММ, СРЗНАЧ и т. д. Когда вы выберете один из этих параметров, таблица автоматически преобразует их в функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая будет игнорировать строки, которые по умолчанию были скрыты фильтром. Если вы хотите включить в свои расчеты скрытые строки, вы можете изменить аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

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

    Другие способы изменения размера таблицы см. в разделе Изменение размера таблицы путем добавления строк и столбцов.

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

Вы можете создать в электронной таблице столько таблиц, сколько захотите.

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

  1. Выберите ячейку или диапазон данных.

  2. Выберите Главная > Форматировать как таблицу .

  3. Выберите стиль таблицы.

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

Также посмотрите видео о создании таблицы в Excel.

Эффективная работа с табличными данными

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

  • Использование структурированных ссылок     Вместо использования ссылок на ячейки, таких как A1 и R1C1, вы можете использовать структурированные ссылки, которые ссылаются на имена таблиц в формуле. Дополнительные сведения см. в разделе Использование структурированных ссылок с таблицами Excel.

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

Экспорт таблицы Excel на сайт SharePoint

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

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

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

См. также

Отформатировать таблицу Excel

Проблемы с совместимостью таблиц Excel

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

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

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

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

Примечание. Вы можете выполнять вычисления быстрее с таблицами данных и Visual Basic для приложений (VBA). Дополнительные сведения см. в статье Таблицы данных «что если» в Excel: более быстрые расчеты с помощью VBA.

Типы анализа "что, если"     

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

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

Таблица данных не может содержать более двух переменных. Если вы хотите проанализировать более двух переменных, вам следует вместо этого использовать сценарии. Хотя она ограничена только одной или двумя переменными (одна для ячейки ввода строки и одна для ячейки ввода столбца), таблица данных может включать любое количество различных значений переменных. Сценарий может иметь до 32 различных значений, но вы можете создать столько сценариев, сколько захотите.

Узнайте больше в статье Введение в анализ "что, если".

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

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

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

На следующем рисунке ячейка D2 содержит формулу платежа =ПЛТ(B3/12,B4,-B5) , которая относится к входной ячейке B3.

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

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

На следующем рисунке ячейка C2 содержит формулу платежа =PMT(B3/12,B4,-B5) , в которой используются две входные ячейки, B3 и B4.


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

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

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

Выполните следующие действия:

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

  2. Выполните одно из следующих действий:

    • Если таблица данных ориентирована на столбцы (значения ваших переменных находятся в столбце), введите формулу в ячейку на одну строку выше и на одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбцы, а формула содержится в ячейке D2.

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

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

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

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

  4. На вкладке Данные нажмите Анализ «что, если» >   Таблица данных (в группе Инструменты данных или Прогноз в группе Excel 2016).

  5. Выполните одно из следующих действий:

    • Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Ячейка ввода столбца . На рисунке выше входная ячейка — B3.

    • Если таблица данных ориентирована на строки, введите ссылку на ячейку для входной ячейки в поле 9.0019 Строка ввода ячейки поле.

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

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

Выполните следующие действия

  1. Выполните одно из следующих действий:

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

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

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

  3. На вкладке Данные щелкните Анализ «что, если» > Таблица данных (в группе Инструменты данных или Прогноз в группе Excel 2016).

  4. Выполните одно из следующих действий:

    • Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Ячейка ввода столбца .

    • Если таблица данных ориентирована на строки, введите ссылку на ячейку для входной ячейки в поле 9.0019 Строка ввода ячейки поле.

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

Выполните следующие действия:

  1. В ячейку рабочего листа введите формулу, которая относится к двум входным ячейкам.

    В следующем примере, в котором начальные значения формулы вводятся в ячейки B3, B4 и B5, вы вводите формулу =PMT(B3/12,B4,-B5) в ячейку C2.

  2. Введите один список входных значений в том же столбце под формулой.

    В этом случае введите разные процентные ставки в ячейки C3, C4 и C5.

  3. Введите второй список в той же строке, что и формула, справа от нее.

    Введите условия кредита (в месяцах) в ячейки D2 и E2.

  4. Выберите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых вы хотите вычислить значения (D3:E5).

    В этом случае выберите диапазон C2:E5.

  5. На вкладке Данные в группе Инструменты данных или Прогноз (в Excel 2016) нажмите Анализ «что, если» группы Excel 2016).

  6. В поле Row input cell введите ссылку на входную ячейку для входных значений в строке.
    Введите ячейку B4 в поле Row input cell .

  7. В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце.
    Тип B3 в поле ввода столбца .

  8. Нажмите OK .

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

Таблица данных с двумя переменными может показать, как различные комбинации процентных ставок и условий кредита повлияют на ежемесячный платеж по ипотеке. На этом рисунке ячейка C2 содержит формулу платежа, =PMT(B3/12,B4,-B5) , который использует две входные ячейки, B3 и B4.

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

Выполните следующие действия, чтобы повысить производительность вычислений:

  1. Щелкните Файл > Параметры > Формулы.

  2. В разделе Параметры расчета в разделе Вычислить щелкните Автоматически, кроме таблиц данных .

    Совет:  При необходимости на вкладке Формулы щелкните стрелку Параметры расчета , затем нажмите Автоматические таблицы данных за исключением (в группе Расчет ).

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

Поиск цели

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

Решатель Excel

Вы можете использовать надстройку Excel Solver, чтобы найти оптимальное значение для набора входных переменных. Решатель работает с группой ячеек (называемых переменными решения или просто переменными ячейками), которые используются при вычислении формул в ячейках цели и ограничения. Решатель корректирует значения в ячейках переменных решения, чтобы удовлетворить ограничения на ячейки ограничений и получить результат, который вы хотите получить для целевой ячейки. Узнайте больше в этой статье: Определите и решите проблему с помощью Solver.

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

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

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

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

Learn more

Только новые статьи

Введите свой e-mail

Видео-курс

Blender для новичков

Ваше имя:Ваш E-Mail:

Содержание, карта.