Формули масивів – це потужні формули, які дають змогу виконувати складні обчислення, які часто не можна виконувати зі стандартними функціями аркуша. Їх також називають формулами Ctrl-Shift-Enter або CSE, тому що для їх введення потрібно натиснути сполучення клавіш Ctrl+Shift+Enter. За допомогою формул масивів можна зробити, здавалося б, неможливе, наприклад
- Підрахуйте кількість символів у діапазоні клітинок.
- Підсумуйте числа, які відповідають певним умовам, наприклад найменші значення діапазону або числа, які перебувають між верхньою та нижньою межами.
- підсумувати кожне n-е значення в діапазоні.
В Excel передбачено два типи формул масивів: формули масивів, які виконують кілька обчислень для створення одного результату та формули масивів, які обчислюють кілька результатів. Деякі функції аркуша повертають масиви значень або вимагають масив значень як аргумент. Докладні відомості див. в статті Рекомендації та приклади формул масивів.
Примітка.
Якщо у вас є поточна версія Microsoft 365, можна просто ввести формулу у верхню ліву клітинку вихідного діапазону, а потім натиснути клавішу Enter , щоб підтвердити формулу як формулу динамічного масиву. В іншому разі формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. у статті Приклади формул масивів і рекомендації.
Створення формули масиву, яка обчислює один результат
За допомогою цього типу формули масиву можна спростити модель аркуша, замінивши кілька різних формул на одну формулу масиву.
Клацніть клітинку, у яку потрібно ввести формулу масиву.
Введіть потрібну формулу.
У формулах масиву використовується стандартний синтаксис формул. Усі вони починаються зі знака рівності (=), і ви можете використовувати будь-які вбудовані функції Excel у формулах масивів.
Наприклад, ця формула обчислює загальне значення масиву цін на акції та акцій і розміщує результат у клітинці поруч із фразою "Загальне значення".
Спочатку формула перемножує спільні ресурси (клітинки B2 – F2) на їхні ціни (клітинки B3 – F3), а потім додає ці результати, щоб створити загальний підсумок 35 525. Це приклад формули масиву для однієї клітинки, оскільки формула міститься лише в одній клітинці.
Натисніть клавішу Enter (якщо у вас є поточна передплата на Microsoft 365); в іншому разі натисніть клавіші Ctrl+Shift+Enter.
Якщо натиснути клавіші Ctrl+Shift+Enter, excel автоматично вставить формулу між { } (пара відкриваючих і закриваючих фігурних дужок).Примітка.
Якщо у вас є поточна версія Microsoft 365, можна просто ввести формулу у верхню ліву клітинку вихідного діапазону, а потім натиснути клавішу Enter , щоб підтвердити формулу як формулу динамічного масиву. В іншому разі формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. у статті Приклади формул масивів і рекомендації.
Створення формули масиву, яка обчислює кілька результатів
Щоб обчислити кілька результатів за допомогою формули масиву, введіть масив у діапазон клітинок із однаковою кількістю рядків і стовпців, які використовуватимуться в аргументах масиву.
Виділіть діапазон клітинок, у який потрібно ввести формулу масиву.
Введіть потрібну формулу.
У формулах масиву використовується стандартний синтаксис формул. Усі вони починаються зі знака рівності (=), і ви можете використовувати будь-які вбудовані функції Excel у формулах масивів.
У наведеному нижче прикладі формула мультидіаграма ділиться на акції за ціною в кожному стовпці, а формула розташована у вибраних клітинках рядка 5.
Натисніть клавішу Enter (якщо у вас є поточна передплата на Microsoft 365); в іншому разі натисніть клавіші Ctrl+Shift+Enter.
Якщо натиснути клавіші Ctrl+Shift+Enter, excel автоматично вставить формулу між { } (пара відкриваючих і закриваючих фігурних дужок).Примітка.
Якщо у вас є поточна версія Microsoft 365, можна просто ввести формулу у верхню ліву клітинку вихідного діапазону, а потім натиснути клавішу Enter , щоб підтвердити формулу як формулу динамічного масиву. В іншому разі формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. у статті Приклади формул масивів і рекомендації.
Якщо потрібно додати нові дані до формули масиву, див. статтю Розгортання формули масиву. Ви також можете спробувати:
- Правила змінення формул масивів (вони можуть бути вигадливими)
- Видалення формули масиву (також натисніть клавіші Ctrl+Shift+Enter)
- Використання констант-масивів у формулах масивів (вони можуть бути зручні)
- Іменування константи-масиву (вони можуть спростити використання констант)
Спробуйте
Якщо потрібно погратися з константами-масивами, перш ніж випробувати їх із власними даними, скористайтеся зразками даних тут.
У книзі нижче наведено приклади формул масивів. Щоб найкраще працювати з прикладами, завантажте книгу на комп'ютер, клацнувши піктограму Excel у нижньому правому куті, а потім відкрийте її в класичній програмі Excel.
Скопіюйте таблицю нижче та вставте її в Excel у клітинці A1. Обов'язково виділіть клітинки E2:E11, введіть формулу =C2:C11*D2:D11, а потім натисніть клавіші Ctrl+Shift+Enter, щоб зробити її формулою масиву.
| Продавець | Тип автомобіля | Кількість проданих автомобілів | Ціна за одиницю | Загальний обсяг продажів |
|---|---|---|---|---|
| Мороз | Седан | 5 | 2200 | =C2:C11*D2:D11 |
| Купе | 4 | 1800 | ||
| Омельченко | Седан | 6 | 2300 | |
| Купе | 8 | 1700 | ||
| Єрьоменко | Седан | 3 | 2000 | |
| Купе | 1 | 1600 | ||
| Попкова | Седан | 9 | 2150 | |
| Купе | 5 | 1950 | ||
| Горноженко | Седан | 6 | 2250 | |
| Купе | 8 | 2000 |
Створення формули масиву для кількох клітинок
- У зразку книги виділіть клітинки E2–E11. Ці клітинки міститимуть результати.
Ви завжди вибираєте клітинку або клітинки, які міститимуть результати, перш ніж вводити формулу.
І завжди ми маємо на увазі 100 відсотків часу.
- Введіть цю формулу. Щоб ввести її в клітинку, просто почніть вводити текст (натисніть знак рівності), і формула з'явиться в останній вибраній клітинці. Ви також можете ввести формулу в рядку формул:
=C2:C11*D2:D11 - Натисніть сполучення клавіш Ctrl+Shift+Enter.
Створення формули масиву для однієї клітинки
- У зразку книги клацніть клітинку B13.
- Введіть цю формулу за допомогою будь-якої з наведених вище дій із кроку 2:
=SUM(C2:C11*D2:D11) - Натисніть сполучення клавіш Ctrl+Shift+Enter.
Формула перемножує значення в діапазонах клітинок C2:C11 і D2:D11, а потім додає результати для обчислення загального підсумку.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті tech Excel або отримати підтримку в спільнотах.