Без формул масиву (array formulas) можна обійтися, тому що це просто скорочений запис групи однотипних формул. Однак, у формул масиву є серйозна перевага: одна така формула може замінити один або декілька стовпців зі звичайними формулами.
Наприклад, можна знайти суму квадратів значень з діапазону А2: A12. просто записавши в осередку B14 формулу = СУММ (A2: A12 ^ 2). Для порівняння: щоб знайти суму квадратів, використовуючи звичайні формули, нам буде потрібно додатковий стовпець для обчислення квадратів значень і одна осередок для їх підсумовування (див. Файл прикладу або діапазон B2: B13 на малюнку нижче).
На відміну від введення звичайних формул, після введення формули масиву треба натиснути замість ENTER комбінацію клавіш CTRL + SHIFT + ENTER (тому, іноді, формули масиву також називаються формулами CSE - це перші літери від назви клавіш, що використовуються для введення C trl, S hift, E nter). Після цього формула буде обрамлена в фігурні дужки <> (Їх не вводять з клавіатури, вони автоматично з'являються після натискання CTRL + SHIFT + ENTER). Це обрамлення показано на малюнку вище (див. Рядок формул).
Якби ми натиснули просто ENTER. то отримали б повідомлення про помилку # значить. виникає при використанні невірного типу аргументу функції, тому що функція СУММ () приймає в якості аргументу тільки діапазон комірок (або формулу, результатом обчислення якої є діапазон, або константи). У нашому випадку ми як аргумент ввели НЕ діапазон, а якесь вираження, яке ще потрібно обчислити перед підсумовуванням, тому і отримали помилку.
Щоб глибше зрозуміти формули масиву проведемо експеримент:
- виділимо осередок B13. що містить звичайну формулу = СУММ ($ B $ 2: $ B $ 12);
- в Рядок формул виділимо аргумент функції СУММ (). тобто $ B $ 2: $ B $ 12;
- натиснемо клавішу F9. тобто обчислимо, виділену частину формули;
- отримаємо - масив квадратів значень з шпальти В. Масив - це просто набір якихось елементів (значень).
Тобто звичайна функція СУММ () як аргумент отримала якийсь масив (або точніше посилання на нього).
Тепер проведемо той же експеримент з формулою масиву:
- виділимо осередок, що містить формулу масиву = СУММ ($ A $ 2: $ A $ 12 ^ 2);
- в рядку формул виділимо аргумент функції СУММ (). тобто $ A $ 2: $ A $ 12 ^ 2;
- натиснемо клавішу F9. тобто обчислимо, виділену частину формули;
- отримаємо - той же масив, що і в першому випадку.
Тобто натискання CTRL + SHIFT + ENTER змусило EXCEL перед підсумовуванням зробити проміжні обчислення з діапазоном осередків (з масивом що містяться в ньому значень). Для самої функції СУММ () нічого не змінилося - вона отримала той же масив, тільки попередньо обчислений, а не прямо з діапазону комірок, як у випадку зі звичайною формулою. Зрозуміло, що замість функції СУММ () у формулі масиву може бути використана будь-яка інша функція MS EXCEL: СРЗНАЧ (). МАКС (). НАЙБІЛЬШИЙ () і т.п.
Вищенаведений приклад ілюструє використання функції масиву повертає єдине значення, тобто результат може бути виведений в одній комірці. Це досягається використанням функцій здатних «згорнути» обчислений масив до одного значення (СУМ (). СРЗНАЧ (). МАКС ()). Приклади таких функцій масиву наведені в статті Формули масиву, які повертають одне значення.
Формули масиву також можуть повертати відразу кілька значень. Зрозуміло, що для того щоб відобразити такий результат необхідно задіяти цілий діапазон комірок. Приклади таких функцій наведені в статті Формули масиву, які повертають кілька значень.
Переваги та недоліки формул масиву розглянуті в однойменній статті Формули масиву. Переваги і недоліки .
У файлі прикладу також наведено рішення даної задачі функцією СУММПРОИЗВ (). яка часто не вимагає введення її як формули масиву:
= СУММПРОИЗВ ($ A $ 2: $ A $ 12 ^ 2)
Тут, при введенні формули СУММПРОИЗВ () натискати CTRL + SHIFT + ENTER необов'язково.
Посилання на статті про формулах масиву на сайті Microsoft:
ПРИМІТКА:
При створенні іменували формул і правил умовного форматування формули масиву не можна ввести натискаючи CTRL + SHIFT + ENTER. Ці формули вводяться тільки в осередку аркуша. Однак, якщо формулою масиву присвоїти Ім'я. то EXCEL «зрозуміє», що потрібно з нею потрібно робити. Наприклад, якщо формулою = СУММ ($ A $ 2: $ A $ 12 ^ 2) присвоїти ім'я Сумма_квадратов, а потім в осередку вказати = Сумма_квадратов. то отримаємо правильний результат.
