Формула Excel array - это формула, которая выполняет вычисления для значений в одном или нескольких массивах, а не для одного значения данных. В программах для работы с электронными таблицами массив представляет собой диапазон или серию связанных значений данных, которые обычно находятся в соседних ячейках рабочего листа.
Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Microsoft 365.
Что такое формулы массива?
Формулы массива аналогичны обычным. Они:
- Начинайте со знака равенства (=)
- Используйте тот же синтаксис, что и обычные формулы
- Используйте те же математические операторы
- Действовать в том же порядке
Однако формулы массива заключаются в фигурные скобки { }. И вы не можете просто ввести их; вы должны добавить их, нажав клавиши Ctrl, Shift и Enter после ввода формулы в клетка или клетки. По этой причине формулу массива иногда называют формулой CSE в Excel.
Каждый раз, когда вы редактируете формулу массива, фигурные скобки исчезают. Чтобы вернуть их, снова нажмите клавиши Ctrl, Shift и Enter.
Существует два основных типа формул массива:
- Формулы массива с одной ячейкой, которые выполняют несколько вычислений в одной ячейке рабочего листа
- Формулы массива с несколькими ячейками, которые находятся более чем в одной ячейке рабочего листа
Как создать формулу массива
- Введите формулу в ячейку.
- Удерживайте нажатыми клавиши Ctrl и Shift на клавиатуре.
- Нажмите и отпустите клавишу Enter, чтобы создать формулу массива.
- Отпустите клавиши Ctrl и Shift.
- Если все сделано правильно, формула будет заключена в фигурные скобки.
Формулы массива с одной ячейкой
Формула массива одной ячейки использует функцию, такую как СУММ, СРЗНАЧ или СЧЕТ, чтобы объединить вывод формулы массива с несколькими ячейками в одно значение в одной ячейке. Ниже приведен пример:
{=СУММ(A1:A2B1:B2)}
Приведенная выше формула складывает произведение A1B1 и A2B2, а затем возвращает один результат в виде одна ячейка рабочего листа. Другой способ представить эту формулу:
=(A1B1)+(A2B2)
Формулы многоячеечного массива
Как следует из их названия, формулы массива с несколькими ячейками находятся в нескольких ячейках рабочего листа и возвращают массив в качестве ответа. Другими словами, одна и та же формула находится в двух или более ячейках и возвращает разные ответы в каждой ячейке.
Каждая копия или экземпляр формулы массива выполняет одни и те же вычисления в каждой ячейке, в которой она находится, но каждая из них использует разные данные. Таким образом, каждый из них дает разные результаты. Пример формулы массива с несколькими ячейками:
{=A1:A2B1:B2}
Если приведенная выше формула массива находится в ячейках C1 и C2 на листе, то результаты будут следующими:
- Данные в A1 умножаются на данные в B1, и результаты отображаются в ячейке C1..
- Данные в A2 умножаются на данные в B2, и результаты появляются в ячейке C2..
Формулы массивов и функции Excel
Вы можете использовать многие встроенные функции Excel, такие как СУММ, СРЗНАЧ и СЧЕТ в формуле массива. Есть также несколько функций, таких как TRANSPOSE, которые всегда должны быть формулой массива для правильной работы. (Функция TRANSPOSE копирует данные из строки в столбец или наоборот.)
Вы также можете расширить полезность многих функций, таких как ИНДЕКС и ПОИСКПОЗ или МАКС и IF, используя их вместе в формуле массива.
Создание простой формулы массива с одной ячейкой
Формулы массива с одной ячейкой обычно сначала выполняют вычисление для нескольких ячеек, а затем используют такую функцию, как СРЗНАЧ или СУММ для объединения вывод массива в один результат.
Игнорировать значения ошибок при поиске данных
В этой формуле массива для нахождения среднее значение для существующих данных, игнорируя значения ошибок, такие как DIV/0! и NAME?
Подсчет ячеек данных
Используйте функции СУММ и ЕСЛИ в формуле массива для подсчета ячеек данных, которые удовлетворяют одному из нескольких условий; этот метод отличается от использования функции Excel СЧЁТЕСЛИМН, которая требует, чтобы все заданные условия были выполнены, прежде чем она подсчитает ячейку.
Найти наибольшее положительное или отрицательное число
В этом примере функция MAX и функция IF объединяются в формуле массива, которая находит наибольшее или максимальное значение для диапазона данные, когда они соответствуют определенным критериям. Здесь наибольшее значение представляет самое медленное время.
Найти наименьшее положительное или отрицательное число
Подобно приведенному выше примеру, вы можете комбинировать функции MIN и IF в формуле массива, чтобы найти наименьшее или минимальное значение для диапазона данных, когда он соответствует определенным критериям.
Найти среднее или медианное значение
Функция МЕДИАНА в Excel находит среднее значение для списка данных. Комбинируя его с функцией IF в формуле массива, вы можете найти среднее значение для разных групп связанных данных.
Создайте формулу поиска с несколькими критериями
Эта формула массива включает вложение функций ПОИСКПОЗ и ИНДЕКС для поиска определенной информации в базе данных.
Создайте формулу поиска слева
Функция ВПР обычно ищет только данные, расположенные в столбцах справа, но, комбинируя ее с функцией ВЫБРАТЬ, вы может создать формулу поиска слева, которая будет искать столбцы данных слева от аргумента Lookup_value.
При вводе массива в качестве аргумента для функции, которая обычно содержит только одно значение или ссылку на ячейку, вы можете напрямую вводить фигурные скобки, а не использовать Ctrl+Shift+Enterкомбинация клавиш, как в приведенном выше примере.
