Формули масиву excel.

Відео: Формули масиву в Excel

В Excel можна писати формули не тільки в окремі осередки, але відразу в діапазоні осередків. Ці формули називають в Excel «формули масиву».
Масив в Excel - це будь-яка кількість осередків з даними (крім однієї), розташованих в рядках, стовпцях, діапазонах. Можна сказати, масив в Excel - це діапазон комірок. Що таке діапазон, як його виділяти, читайте в статті «Що таке діапазон в Excel» тут.
Тут розглянемо, як ввести формулу масиву в Excel, чим відрізняються прості формули від формул масиву. розберемо приклади формули масиву в Excel. Почнемо з простого. У нас така таблиця.
Перший приклад.
Щоб скласти ці числа, досить поставити Автосума. Вийде така формула. = СУММ (A1: A6) Результат вийшов = 21.
Тепер нам потрібно додати до кожного числа в стовпці А число 2. Потім, отримані суми скласти. Щоб так порахувати простою формулою, нам потрібно кудись внести число 2, м.б. в стовпець В, потім скласти все по рядках, потім Автосума скласти числа в стовпці - це займе дуже багато часу і сил.
Ось тут нам допоможе формула масиву.
У осередок А7 пишемо таку формулу. = СУММ (A1: A6 + 2)
Увага!
Як ввести формулу масиву в Excel.

Тепер, щоб ввести цю формулу, як формулу масиву, натискаємо НЕ «Enter», а поєднання клавіш - «Ctrl» + «Shift» + «Enter».
Формула вийде така. {= СУММ (А1: А6 + 2)}
Вся формула варто в фігурних дужках - це значить, що це формулу масиву. Excel розуміє як потрібно вважати, коли бачить фігурні дужки. А саме - до числа 1 + 2, до 2 + 2, до 3 + 2, до 4 + 2, до 5 + 2, до 6 + 2. Потім, отримані суми складе. Вийде результат = 33.

Коригування формули масиву в Excel.
Коригуємо формулу масиву, як звичайну формулу, АЛЕ, для її введення натискаємо НЕ «Enter», а поєднання клавіш - «Ctrl» + «Shift» + «Enter».
Другий приклад.
Якщо нам потрібно не додати до чисел стовпчика А число 2, а помножити числа стовпчика А на цифру 2, то у формулі так і пишемо. Вийде така формула. {= СУММ (A1: A6 * 2)} Результат вийде = 42.
Третій приклад.
Формулу масиву можна встановити не тільки в одній комірці, але і в діапазоні осередків. Наприклад, нам потрібно помножити числа стовпчика А на числа рядка 1. Як написати таку формулу докладно описано в статті «Як зробити таблицю множення в Excel» тут.
Четвертий приклад.
Нам потрібно скласти дані не з усіх комірок стовпчика, а з кожної другої або третьої, тощо осередки. Тут нам допоможе формула масиву. Дивіться статтю «Як в Excel скласти кожну другу осередок, рядок».П`ятий приклад.
Формулою масиву можна проводити складні операції в таблиці, наприклад, сортувати за умовою, використовуючи різні функції. Цей спосіб зручний тим, що при зміні даних в стовпці, автоматично зміняться дані сортування. Детальніше про таку формулі масиву читайте в статті «Сортування в Excel формулою» тут.
Шостий приклад.
За допомогою формули масиву можна видалити зі списку, стовпчика порожні клітинки. Дивись цей спосіб в статті «Як видалити порожні клітинки в Excel».
Сьомий приклад.
Ми бачимо, що багато варіантів застосування формули масиву. Розглянемо останній приклад, який допоможе написати алфавіт. Цей алфавіт стане в нагоді для списку, коли прізвища розподіляються по буквах. Детальніше про це дивіться в статті «Пов`язаний список, що випадає в Excel за алфавітом».
В Excel є кілька способів швидко дізнатися чи порахувати суму осередків. Дізнатися суму в Excel можна без установки формули. Є кілька способів установки і застосування формул суми, віднімання, тощо в Excel. Кілька способів ми розглянули в статті "Сума в Excel".