Формули масивів – це потужні формули, які дають змогу виконувати складні обчислення, що часто не можна виконати за допомогою стандартних функцій аркуша. Їх також називають формулами 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 автоматично вставить формулу між { } (парою відкритих і закритих фігурних дужок).
Створення формули масиву, яка обчислює кілька результатів
Щоб обчислити кілька результатів за допомогою формули масиву, введіть масив у діапазон клітинок, кількість рядків і стовпців у якому відповідає кількості рядків і стовпців у масиві аргументів.
Виділіть діапазон клітинок, у який потрібно ввести формулу масиву.
Введіть формулу, яку потрібно використовувати.
У формулах масиву використовується стандартний синтаксис формул. Усі вони починаються знаком рівності (=), і у формулах масивів можна використовувати будь-які вбудовані функції Excel.
У наведеному нижче прикладі формула помножує акції на ціни в кожному стовпці, і формула міститься у вибраних клітинках рядка 5.
Натисніть клавішу Enter (якщо у вас є активна передплата на Microsoft 365); В іншому випадку натисніть клавіші Ctrl+Shift+Enter.
Якщо натиснути клавіші Ctrl+Shift+Enter, Excel автоматично вставить формулу між { } (парою відкритих і закритих фігурних дужок).
Якщо потрібно включити у формулу масиву нові дані, див. статтю Рекомендації та приклади формул масивів. Ви також можете спробувати:
- Правила змінення формул масивів (вони можуть бути вибагливими)
- Видаліть або вилучіть формулу у програмі Excel (тут також можна натиснути сполучення клавіш Ctrl+Shift+Enter)
- Використання констант-масивів у формулах масивів (вони можуть бути зручними)
- Надання імені константі-масиву (вони можуть спростити використання констант)
Спробуйте
Якщо потрібно поекспериментувати з константами-масивами, перш ніж використовувати їх для обробки власних даних, скористайтеся зразками даних у цьому розділі.
Скопіюйте наведену нижче таблицю та вставте її у програму Excel у клітинку A1. Обов'язково виділіть клітинки E2:E11, введіть формулу =C2:C11*D2:D11та натисніть клавішу Enter (якщо у вас є активна передплата на Microsoft 365); в іншому випадку натисніть клавіші 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 - Натисніть клавішу Enter (якщо у вас є дійсна передплата на Microsoft 365); В іншому випадку натисніть клавіші Ctrl+Shift+Enter. Якщо натиснути клавіші Ctrl+Shift+Enter, Excel автоматично вставить формулу між { } (парою відкритих і закритих фігурних дужок).
Створення формули масиву для однієї клітинки
- У книзі зі зразком клацніть клітинку B13.
- Введіть формулу, використовуючи один із методів із кроку 2 вище:
=SUM(C2:C11*D2:D11) - Натисніть клавішу Enter (якщо у вас є дійсна передплата на Microsoft 365); В іншому випадку натисніть клавіші Ctrl+Shift+Enter. Якщо натиснути клавіші Ctrl+Shift+Enter, Excel автоматично вставить формулу між { } (парою відкритих і закритих фігурних дужок).
Формула перемножує значення в діапазонах клітинок C2:C11 і D2:D11, а потім додає результати, щоб обчислити загальний підсумок.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.