Формула масиву виконує операцію над кількома значеннями замість одного значення. Кінцевим результатом формули масиву може бути або один елемент, або масив елементів, залежно від побудови формули. Для коректної роботи багато формул масиву потрібно вводити за допомогою control + shift + enter. Коли ви вводите формулу таким чином, ви побачите формулу, загорнуту в фігурні дужки () у рядку формул.
Що таке масив?
Масив - це сукупність більш ніж одного елемента. Масиви в Excel відображаються всередині фігурних дужок. Наприклад, (1; 2; 3) або ("червоний", "синій", "зелений"). Причина, по якій масиви настільки поширені в Excel, полягає в тому, що вони безпосередньо відображаються в діапазонах комірок. Вертикальні діапазони представлені у вигляді масивів, у яких використовуються крапки з комою, наприклад (100; 125; 150). Горизонтальні діапазони представлені у вигляді масивів, у яких використовуються коми, наприклад ("малий", "середній", "великий"). Двовимірний діапазон використовуватиме крапки з комою та коми.
Приклади
Формули масивів дещо важкі для розуміння, оскільки термінологія щільна і складна. Але самі формули масивів можуть бути дуже простими. Наприклад, ця формула масиву перевіряє діапазон A1: A5 на значення "a":
=OR(A1:A5="a")
Операція масиву - це порівняння кожної комірки в А1: А5 із рядком "а". Оскільки порівняння оперує кількома значеннями, воно повертає кілька результатів функції OR:
=OR((FALSE;FALSE;FALSE;TRUE;FALSE))
Якщо будь-який елемент результуючого масиву має значення TRUE, функція OR повертає значення TRUE.
Іноді формули масиву подають кілька значень як аргумент функції. Наприклад, ця формула масиву повертає загальну кількість символів у діапазоні B2: B11:
(=SUM(LEN(B2:B11)))
Функція LEN отримує кілька значень у діапазоні B2: B11 і повертає кілька результатів у масиві, подібному до цього, всередині SUM:
=SUM((3;3;5;4;5;4;6;5;4;4))
де кожен елемент масиву представляє довжину одного значення комірки. Потім функція SUM підсумовує всі елементи і повертає 43 як кінцевий результат.
Спеціальний синтаксис
У всіх версіях Excel, крім Excel 365, для правильної роботи потрібно вводити багато формул масивів особливим чином. Замість введення за допомогою клавіші "Enter" їх потрібно вводити за допомогою Control + Shift + Enter. Іноді ви побачите Control + Shift + Enter скорочено "CSE", як у "формулі CSE". Формула, введена таким чином, з’явиться з фігурними дужками з обох сторін:
=(formula)
Ці фігурні дужки відображаються автоматично в Excel. Переконайтеся, що ви не вводите фігурні дужки вручну.
Не всі формули масиву потрібно вводити за допомогою Control + Shift + Enter. Деякі функції, такі як SUMPRODUCT, запрограмовані для власної обробки операцій з масивами і зазвичай не потребують Control + Shift + Enter. Наприклад, обидві наведені нижче формули є формулами масиву, що повертають однаковий результат, але лише версія SUM вимагає Control + Shift + Enter:
=(SUM(LEN(A1:A5))) =SUMPRODUCT(LEN(A1:A5))
Excel 365
В Excel 365 формули масиву є власними і не потребують управління + shift + enter. Загальний вступ див. У розділі Формули динамічного масиву в Excel.
