Формулы excel в таблице: Функции Excel (по категориям)

Расширенные формулы Excel — voxt.ru

Список 10 лучших расширенных формул и функций Excel

Вы можете скачать этот расширенный шаблон формул Excel здесь — Расширенный шаблон формул Excel

# 1 — Формула ВПР в Excel

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

Вы можете использовать ВПР, как показано ниже.

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

Перетащите формулу в остальные ячейки.

Существует три основных типа ВПР:

1. У вас не может быть основного столбца справа от столбца, для которого вы хотите заполнить значение из другой таблицы. В этом случае столбец «Зарплата сотрудника» не может находиться перед идентификатором сотрудника.
2. В дублированных значениях в основном столбце таблицы B первое значение будет заполнено в ячейке.
3. Если вы вставляете новый столбец в базу данных (например, вставляете новый столбец перед зарплатой сотрудника в таблице B), вывод формулы может отличаться в зависимости от позиции, которую вы указали в формуле (в приведенном выше случае результат будет пустым)

# 2 — Формула ИНДЕКС в Excel

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

Мы можем использовать расширенную формулу Excel, как показано ниже:

Эту же формулу ИНДЕКС можно использовать для получения значения по строке. При использовании номера строки и столбца синтаксис будет выглядеть примерно так:

Приведенная выше формула вернет «Раджеш Вед».

Заметка: Если вставить еще одну строку в 5^th строка, формула вернет «Чандан Кале». Следовательно, вывод будет зависеть от любых изменений, происходящих в таблице данных с течением времени.

# 3 — Формула MATCH в Excel

Эта расширенная формула Excel возвращает номер строки или столбца, если в заданном диапазоне есть определенная строка или число. В приведенном ниже примере мы пытаемся найти «Раджеш Вед» в столбце «Имя сотрудника».

Формула будет такой, как указано ниже:

Функция ПОИСКПОЗ вернет 5 в качестве значения.

3^rd аргумент используется для точного совпадения. Вы также можете использовать +1 и -1 в зависимости от ваших требований.

Заметка: Можно комбинировать ИНДЕКС и ПОИСКПОЗ, чтобы преодолеть ограничение ВПР.

# 4 — ЕСЛИ И формула в Excel

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

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

Результатом будет 0.

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

# 5 — Формула ЕСЛИ ИЛИ в Excel

Точно так же мы также можем использовать функцию OR в excel вместо AND, если нам нужно выполнить одно из многих условий.

В приведенных выше случаях, если выполняется какое-либо из условий, ячейка будет заполнена как 1 else 0. Мы также можем заменить 1 или 0 некоторыми подстроками в двойных кавычках («»).

# 6 — Формула СУММЕСЛИ в Excel

В некоторых анализах вам может потребоваться отфильтровать некоторые наблюдения при применении функции суммы или подсчета. В таких случаях нам на помощь приходит расширенная функция СУММЕСЛИ в excel. Он фильтрует все наблюдения на основе определенных условий, указанных в этой расширенной формуле Excel, и суммирует их. Ex. Что, если мы хотим узнать сумму зарплат только тех сотрудников, у которых ID сотрудника больше 3.

Применяя формулу СУММЕСЛИМН:

Формула возвращает результат как 322000.

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

# 7 — СЦЕПИТЬ формулу в Excel

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

Для этого мы можем использовать формулу CONCATENATE.

Приведенная выше формула приведет к «1Аман Гупта».

У нас может быть еще один вариант, если поставить один дефис между ID и NAME. Ex. СЦЕПИТЬ (B3, «-«, C3) приведет к «1-Аман Гупта». Мы также можем использовать это в ВПР, когда ПРОСМОТР в значении Excel представляет собой смесь более чем одной переменной.

# 8 — Формула LEFT, MID и RIGHT в Excel

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

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

Применение формулы ПРАВИЛЬНО в Excel то же самое. Просто мы будем смотреть на символ справа от строки. Однако в случае функции MID в Excel мы должны указать начальную позицию требуемой текстовой строки и длину строки.

# 9 — Формула смещения в Excel

Эта расширенная функция Excel с комбинацией других функций, таких как СУММ или СРЕДНЕЕ, может придать расчетам динамичный характер. Его лучше всего использовать в случаях, когда мы вставляем непрерывные строки в существующую базу данных. СМЕЩЕНИЕ Excel предоставляет нам диапазон, в котором нам нужно указать ссылочную ячейку, количество строк и столбцов. Ex. Если мы хотим вычислить среднее значение для первых 5 сотрудников в компании, где у нас есть зарплата сотрудников, отсортированных по идентификатору сотрудника, мы можем сделать следующее. Приведенный ниже расчет всегда дает нам зарплату.

• Это даст нам сумму зарплат первых 5 сотрудников.

# 10 — Формула TRIM в Excel

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

Результатом будет «Чандан Кале» без пробела перед Чанданом.

УЗНАТЬ БОЛЬШЕ >>

Post Views: 326

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Похожие записи

Прокрутить вверх

Почему Эксель не считает формулу: что делать

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

Содержание

• Решение 1: меняем формат ячеек
• Решение 2: отключаем режим “Показать формулы”
• Решение 3: активируем автоматический пересчет формул
• Решение 4: исправляем ошибки в формуле
• Заключение

Решение 1: меняем формат ячеек

Очень часто Excel отказывается выполнять расчеты из-за того, что неправильно выбран формат ячеек.

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

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

Очевидно, что формат ячеек нужно изменить, и делается это следующим образом:

1. Чтобы определить текущий формат ячейки (диапазон ячеек), выделяем ее и, находясь во вкладке “Главная”, обращаем вниманием на группу инструментов “Число”. Здесь есть специальное поле, в котором показывается формат, используемый сейчас.
2. Выбрать другой формат можно из списка, который откроется после того, как мы кликнем по стрелку вниз рядом с текущим значением.

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

1. Выбрав ячейку (или выделив диапазон ячеек) щелкаем по ней правой кнопкой мыши и в открывшемся списке жмем по команде “Формат ячеек”. Или вместо этого, после выделения жмем сочетание Ctrl+1.
2. В открывшемся окне мы окажемся во вкладке “Число”. Здесь в перечне слева представлены все доступные форматы, которые мы можем выбрать. С левой стороны отображаются настройки выбранного варианта, которые мы можем изменить на свое усмотрение. По готовности жмем OK.
3. Чтобы изменения отразились в таблице, по очереди активируем режим редактирования для всех ячеек, в которых формула не работала. Выбрав нужный элемент перейти к редактированию можно нажатием клавиши F2, двойным кликом по нему или щелчком внутри строки формул. После этого, ничего не меняя, жмем Enter.

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

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

Решение 2: отключаем режим “Показать формулы”

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

1. Переключаемся во вкладку “Формулы”. В группе инструментов “Зависимость формул” щелкаем по кнопке “Показать формулы”, если она активна.
2. В результате, в ячейках с формулами теперь будут отображаться результаты вычислений. Правда, из-за этого могут измениться границы столбцов, но это поправимо.

Решение 3: активируем автоматический пересчет формул

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

1. Заходим в меню “Файл”.
2. В перечне слева выбираем раздел “Параметры”.
3. В появившемся окне переключаемся в подраздел “Формулы”. В правой части окна в группе “Параметры вычислений” ставим отметку напротив опции “автоматически”, если выбран другой вариант. По готовности щелкаем OK.
4. Все готово, с этого момента все результаты по формулам будут пересчитываться в автоматическом режиме.

Решение 4: исправляем ошибки в формуле

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

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

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

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

Распространенные ошибки

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

• #ДЕЛ/0! – результат деления на ноль;
• #Н/Д – ввод недопустимых значений;
• #ЧИСЛО! – неверное числовое значение;
• #ЗНАЧ! – используется неправильный вид аргумента в функции;
• #ПУСТО! – неверно указан адрес дапазона;
• #ССЫЛКА! – ячейка, на которую ссылалась формула, удалена;
• #ИМЯ? – некорректное имя в формуле.

Если мы видим одну из вышеперечисленных ошибок, в первую очередь проверяем, все ли данные в ячейках, участвующих в формуле, заполнены корректно. Затем проверяем саму формулу и наличие в ней ошибок, в том числе тех, которые противоречат законам математики. Например, не допускается деление на ноль (ошибка #ДЕЛ/0!).

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

1. Отмечаем ячейку, содержащую ошибку. Во вкладке “Формулы” в группе инструментов “Зависимости формул” жмем кнопку “Вычислить формулу”.
2. В открывшемся окне будет отображаться пошаговая информация по расчету. Для этого нажимаем кнопку “Вычислить” (каждое нажатие осуществляет переход к следующему шагу).
3. Таким образом, можно отследить каждый шаг, найти ошибку и устранить ее.

Также можно воспользоваться полезным инструментом “Проверка ошибок”, который расположен в том же блоке.

Откроется окно, в котором будет описана причина ошибки, а также предложен ряд действий касательно нее, в т. ч. исправление в строке формул.

Заключение

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

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

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

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

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

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

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

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

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

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

Подробнее читайте в статье Введение в анализ возможных вариантов.

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

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

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

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

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

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

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

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

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

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

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

org/ItemList»>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   В следующем примере, в котором начальные значения формулы вводятся в ячейки B3, B4 и B5, введите формулу =ПЛТ(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.

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

org/ItemList»>

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

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

   Совет: При необходимости на вкладке Формулы щелкните стрелку Параметры расчета , затем щелкните Automatic Except Data Tables (в группе Calculation ).

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

Поиск цели

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

Решатель Excel

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

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

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

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

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

В этой рабочей книге ячейка 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 или получить поддержку в сообществе ответов.

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

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

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

• Таблица данных в Excel
• Создать одну таблицу переменных данных
• Создание таблицы данных с двумя переменными
• Таблица данных для оценки нескольких формул
• Таблицы данных Excel — 3 вещи, которые вы должны знать
• Удалить таблицу данных в Excel
• Изменить результаты таблицы данных
• Пересчитать таблицу данных вручную

Что такое таблица данных в Excel?

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

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

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

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

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

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

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

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

• B8 содержит формулу FV, которая вычисляет конечный баланс.
• B2 — это переменная, которую вы хотите протестировать (начальные инвестиции).

А теперь давайте проведем простой анализ «что, если», чтобы увидеть, каковы будут ваши сбережения через 5 лет, в зависимости от суммы ваших первоначальных инвестиций, в диапазоне от 1000 до 6000 долларов.

Вот шаги для создания таблицы данных с одной переменной:

1. Введите значения переменных либо в один столбец, либо в одну строку. В этом примере мы собираемся создать таблицу данных , ориентированную на столбцы , поэтому мы вводим значения наших переменных в столбец (D3:D8) и оставляем по крайней мере один пустой столбец справа для результатов.
2. Введите формулу в ячейку на одну строку выше и на одну ячейку справа от значений переменных (в нашем случае E2). Или свяжите эту ячейку с формулой в исходном наборе данных (если вы решите изменить формулу в будущем, вам нужно будет обновить только одну ячейку). Мы выбираем последний вариант и вводим эту простую формулу в E2: = B8
   

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

3. Выберите диапазон таблицы данных, включая формулу, ячейки со значениями переменных и пустые ячейки для результатов (D2:E8).
4. Перейдите на вкладку Data > группу Data Tools , нажмите кнопку What-If Analysis , а затем щелкните Data Table…
5. В диалоговом окне Таблица данных щелкните в ячейке ввода столбца (поскольку наши значения Инвестиции находятся в столбце) и выберите переменную ячейку, указанную в вашей формуле. В этом примере мы выбираем B3, который содержит первоначальную стоимость инвестиций.
6. Нажмите OK , и Excel немедленно заполнит пустые ячейки результатами, соответствующими значению переменной в той же строке.
7. Примените желаемый числовой формат к результатам ( Валюта в нашем случае), и все готово!

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

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

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

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

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

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

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

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

1. Введите формулу в пустую ячейку или свяжите эту ячейку с исходной формулой. Убедитесь, что у вас достаточно пустых столбцов справа и пустых строк ниже, чтобы вместить значения ваших переменных. Как и прежде, мы связываем ячейку E2 с исходной формулой FV, которая вычисляет баланс: = B8
2. Введите один набор входных значений под формулой в том же столбце (инвестиционные значения в E3:E8).
3. Введите другой набор значений переменных справа от формулы в той же строке (количество лет в F2:h3).

   На данный момент ваша таблица данных с двумя переменными должна выглядеть примерно так:

4. Выберите весь диапазон таблицы данных, включая формулу, строку и столбец значений переменных, а также ячейки, в которых будут отображаться вычисляемые значения. Мы выбираем диапазон E2:H8.
5. Создайте таблицу данных уже знакомым способом: Данные вкладка > Кнопка «Что если анализ» > Таблица данных…
6. В поле строки ввода введите ссылку на входную ячейку для значений переменных в строке (в этом примере это B6, содержащий лет значение).
7. В поле ввода столбца введите ссылку на входную ячейку для значений переменных в столбце (B3, содержащее значение Initial Investment ).
8. Щелкните OK .
9. При необходимости отформатируйте выходные данные так, как вам нужно (применив формат Валюта в нашем случае), и проанализируйте результаты:

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

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

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

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

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

1. В ячейке B10 вычислите процентов по следующей формуле: =B8-B3
2. Расположите исходные данные таблицы данных, как мы делали ранее: значения переменных в D3:D8 и E2 связаны с B8 (формула Balance ).
3. Добавьте еще один столбец в диапазон таблицы данных (столбец F) и свяжите F2 с B10 (формула процентов ):
4. Выберите диапазон расширенной таблицы данных (D2:F8).
5. Откройте диалоговое окно Data Table , нажав Вкладка «Данные» > Анализ «что, если» > Таблица данных…
6. В поле Column Input Cell введите ячейку ввода (B3) и нажмите OK .

Вуаля, теперь вы можете наблюдать влияние ваших значений переменных на обе формулы:

Таблица данных в Excel — 3 вещи, которые вы должны знать

Чтобы эффективно использовать таблицы данных в Excel, помните эти 3 простых факта:

1. Для успешного создания таблицы данных входные ячейки должны находиться на тот же лист , что и таблица данных.
2. Microsoft Excel использует функцию TABLE(row_input_cell, colum_input_cell) для вычисления результатов таблицы данных:
   • В таблице данных с одной переменной один из аргументов опущен в зависимости от макета (ориентированного на столбцы или на строки). Например, в нашей горизонтальной таблице данных с одной переменной формула имеет вид = ТАБЛИЦА (, B3) , где B3 — ячейка ввода столбца.
   • В таблице данных с двумя переменными оба аргумента на месте. Например, = ТАБЛИЦА (B6, B3) , где B6 — ячейка ввода строки, а B3 — ячейка ввода столбца.

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

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

Как удалить таблицу данных в Excel

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

Однако можно легко очистить весь массив от полученных значений. Вот как:

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

Готово! 🙂

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

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

1. Выберите все полученные ячейки.
2. Удалите формулу ТАБЛИЦА в строке формул.
3. Введите нужное значение и нажмите Ctrl + Enter.

Это вставит одно и то же значение во все выбранные ячейки:

Как только формула ТАБЛИЦА исчезнет, ​​прежняя таблица данных станет обычным диапазоном, и вы сможете редактировать любую отдельную ячейку в обычном режиме.

Как пересчитать таблицу данных вручную

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

Для этого перейдите на вкладку Формулы > группу Расчет , щелкните Параметры расчета , а затем щелкните Автоматически за исключением таблиц данных .

Это отключит автоматические расчеты таблицы данных и ускорит пересчет всей книги.

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

Вот как вы создаете и используете таблицу данных в Excel.