Друзі, в попередньому пості про про округлення чисел я обіцяв розповісти про формулах масивів. Це потужний і складний інструмент, про який знають і користуються не багато. Такі формули дозволяють виконувати розрахунки, які не можна виконати іншими засобами Ексель, знизити громіздкість таблиць, виключивши проміжні розрахунки. Давайте ж не буду мучити Вас очікуванням, переходимо до вивчення матеріалу.
Як випливає із самої назви, формули масивів - це обчислення, пов'язані з обробкою масивів (матриць) даних. Деякі операції з масивами можна виконати за допомогою функцій. а деякі, можна порахувати тільки з застосувань формул масивів.
Наприклад, щоб порахувати середнє значення чисел в діапазоні А1: А10. можна використовувати функцію СРЗНАЧ (Діапазон)
Середнє значення в Excel
Як відомо, функція СРЗНАЧ ігнорує порожні клітинки, але враховує нульові. А що якщо їх не потрібно враховувати? Ось тоді допоможе формула масиву. І ми з Вами порахуємо правильне середнє значення прямо зараз! Вам необхідно розуміти один факт: в формулах масивів замість одного значення для розрахунків можна вказати цілий діапазон.
Давайте запишемо формулу: = СРЗНАЧ (ЯКЩО (A1: A10 = 0; »»; A1: A10)) і натиснемо Ctrl + Shift + Enter. Такий комбінацією ми сигналізуючи Excel, що застосовується формула масиву. Програма оберне формулу в фігурні дужки, позначаючи застосування саме формули масиву. Чи не дописуйте фігурні дужки самі, це не спрацює, потрібно саме натиснути комбінацію клавіш. Як же працює ця формула:
- Зазвичай першим аргументом функції «ЯКЩО» записується умова. Наприклад, «A1 = 0», і якщо значення в комірці А1 дорівнює нулю, виконується другий аргумент, в іншому випадку - третій. Ми ж передали функції посилання не на одну клітинку, а на масив з десяти значень (А1: А10). Функція «ЯКЩО» перевірить кожну з них і всі нульові замінить символом нового рядка. Результатом буде масив з десяти чисел або порожніх значень. Такий список функція СРЗНАЧ вже відпрацює правильно;
- Отриманий масив передається в функцію СРЗНАЧ, і тепер вона вважає середнє значення чисел без урахування нулів, тому що їх уже немає.
Як бачите, результат обчислення вийшов інший. Нескладно перевірити, що саме це число ми очікували отримати.
Розглянемо ще один приклад, схожий на попередній.
У нас є щоденні продажі за 20 днів. Потрібно порахувати суму продажів в ті дні, коли каса перевищила 5000. У звичайних умовах нам знадобиться проміжний розрахунок. Так, в колонці С ми перевіряємо, якщо продажу вище п'яти тисяч - повертаємо цю суму, якщо нижче - записуємо нуль. Далі, в осередку С23 підсумовуємо все числа стовпчика С і отримуємо шуканий результат.
Тепер подивіться, як ми зробили всі ці дії одночасно в одній лише осередку Е4 і отримали такий же результат! Зверніть увагу на просту формулу в рядку формул:
Виходить, проміжний розрахунок робити і не потрібно було, він лише засмічує таблицю, ускладнюючи структуру сторінки. Очевидна перевага за формулою масиву, правда?
Загальний сенс застосування формул такий, що Ви замінюєте одне значення на масив, а Excel виконує обчислення для кожного з елементів масиву. Це потрібно розуміти і усвідомлювати, тоді формули масивів Вам будуть по плечу. Дуже практичними виходять формули масивів із застосуванням названих діапазонів замість звичайних посилань.
Експерти MS Excel вибудовують з подібних формул величезні комбінації, виконуючи в одній комірці стільки операцій, скільки звичайні користувачі уміщають на кілька аркушів. Так що, експериментуйте, пробуйте різні варіанти застосування, і не забувайте: для завершення введення натискаємо Ctr + Shift + Enter. замість клавіші «Введення».