Формула массива Excel — это формула, которая выполняет вычисления для значений в одном или нескольких массивах, а не в одном значении данных. В программах для работы с электронными таблицами массив представляет собой диапазон или серию связанных значений данных , которые обычно находятся в соседних ячейках на рабочем листе.
Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Office 365.
Что такое формулы массива?
Формулы массива похожи на обычные. Oни:
- Начните со знака равенства ( = )
- Используйте тот же синтаксис, что и обычные формулы
- Используйте те же математические операторы
- Следуйте тому же порядку работы
Однако формулы массивов заключены в фигурные скобки {}. И вы не можете просто ввести их; Вы должны добавить их, нажав Ctrl , Shift , , и введите ключи после ввода формулы в ячейку или ячейки. По этой причине формулу массива иногда называют формулой CSE в Excel.
Каждый раз, когда вы редактируете формулу массива, фигурные скобки исчезают. Чтобы вернуть их, снова нажмите клавиши Ctrl , Shift и Enter .
Существует два основных типа формул массива:
- Формулы массива из одной ячейки, которые выполняют несколько вычислений в одной ячейке листа
- Формулы из нескольких ячеек массива, которые находятся в нескольких ячейках листа
Как создать формулу массива
-
Введите формулу в ячейку.
-
Удерживайте клавиши Ctrl и Shift на клавиатуре.
-
Нажмите и отпустите клавишу Enter, чтобы создать формулу массива.
-
Отпустите клавиши Ctrl и Shift .
-
Если все сделано правильно, фигурные скобки будут окружать формулу.
Формулы для отдельных ячеек
Формула массива одной ячейки использует функцию, такую как SUM , AVERAGE или COUNT , для объединения вывода формулы массива из нескольких ячеек в одно значение в одной ячейке. Ниже приведен пример:
{= СУММ (А1: А2 * В1: В2)}
Приведенная выше формула складывает произведение A1 * B1 и A2 * B2 , а затем возвращает один результат в одной ячейке на листе. Другой способ представления этой формулы:
= (А1 * В1) + (А2 * В2)
Multi-Cell Array Formulas
Как следует из их названия, формулы массива из нескольких ячеек находятся в нескольких ячейках рабочего листа, и они возвращают массив в качестве ответа. Другими словами, одна и та же формула находится в двух или более ячейках и возвращает разные ответы в каждой ячейке.
Каждая копия или экземпляр формулы массива выполняет одинаковые вычисления в каждой ячейке, в которой она находится, но каждая использует разные данные. Поэтому каждый из них дает разные результаты. Пример формулы множества ячеек:
{= А1: А2 * В1: В2}
Если приведенная выше формула массива находится в ячейках C1 и C2 на листе, то результаты будут следующими:
- Данные в A1 умножаются на данные в B1 , а результаты появляются в ячейке C1 .
- Данные в A2 умножаются на данные в B2 , а результаты появляются в ячейке C2 .
Формулы массивов и функции Excel
Вы можете использовать многие встроенные функции Excel, такие как SUM , AVERAGE и COUNT , в формуле массива. Есть также несколько функций, таких как TRANSPOSE , которые всегда должны быть формулой массива, чтобы работать правильно. (Функция TRANSPOSE копирует данные из строки в столбец или наоборот.)
Вы также можете расширить полезность многих функций, таких как INDEX и MATCH или MAX и IF , используя их вместе в формуле массива.
Создать простую формулу массива из одной ячейки
Формулы массива с одной ячейкой обычно сначала выполняют вычисление с несколькими ячейками, а затем используют функцию, такую как AVERAGE или SUM, чтобы объединить выходные данные массива в один результат.
Игнорировать значения ошибок при поиске данных
Эта формула массива использует функции AVERAGE , IF и ISNUMBER, чтобы найти среднее значение для существующих данных , игнорируя значения ошибок, такие как # DIV / 0! и #NAME ?
Подсчет ячеек данных
Используйте SUM и ПЧ функции в формуле массива для подсчета клеток данных , которые соответствуют одному из нескольких условий; Этот метод отличается от использования функции COUNTIFS в Excel , которая требует, чтобы все установленные условия были выполнены до того, как он подсчитает ячейку.
Найти наибольшее положительное или отрицательное число
Этот пример сочетает в себе MAX функции и ПЧ функции в формуле массива , который будет найти наибольшее или максимальное значение для диапазона данных , когда она отвечает определенным критериям. Здесь наибольшее значение представляет самое медленное время.
Найдите наименьшее положительное или отрицательное число
Как и в примере выше, вы можете объединить функции MIN и IF в формуле массива, чтобы найти наименьшее или минимальное значение для диапазона данных, когда оно соответствует определенным критериям.
Найти среднее или среднее значение
Функция MEDIAN в Excel находит среднее значение для списка данных. Комбинируя его с функцией IF в формуле массива, вы можете найти среднее значение для разных групп связанных данных.
Создайте формулу поиска с несколькими критериями
Эта формула массива включает в себя вложение функций MATCH и INDEX для поиска конкретной информации в базе данных.
Сделать левую формулу поиска
ВПР функция обычно только поиск данных , расположенных в колонках справа, но, комбинируя его с ВЫБРАТЬ функции , вы можете создать левую формулу подстановки , которая будет искать столбцы данных слева от Lookup_Value аргумента.
При вводе массива в качестве аргумента для функции, которая обычно содержит только одно значение или ссылку на ячейку, вы можете вводить фигурные скобки напрямую, а не использовать комбинацию клавиш Ctrl + Shift + Enter , как в приведенном выше примере.