Робота з розширеними формулами масивів

У цьому розділі наведено приклади розширених формул масивів.

Підсумування значень діапазону, який містить значення помилок

У програмі Excel функція SUM не працює, якщо користувач намагається підсумувати значення діапазону, який містить значення помилки (наприклад, #N/A). У цьому прикладі показано, як підсумувати значення в діапазоні "Дані", який містить помилки.

=SUM(IF(ISERROR(Дані);"";Дані))

Формула створює новий діапазон, який містить початкові значення мінус будь-які значення помилок. Розглянемо цю формулу, починаючи із внутрішніх функцій. Функція ISERROR шукає помилки в діапазоні клітинок (Дані). Функція IF повертає одне значення, якщо вказана умова отримує значення TRUE (істина), і інше значення, якщо умова отримує значення FALSE (хибність). У цьому прикладі функція повертає пусті рядки ("") для всіх значень помилок, тому що вони отримують значення TRUE, і решту значень із діапазону (Дані), тому що вони отримують значення FALSE, оскільки не містять значень помилок. Після цього функціяSUM обчислює підсумок для відфільтрованого масиву.

Підрахунок кількості значень помилок у діапазоні

У цьому прикладі використовується формула, подібна до попередньої, але вона повертає кількість значень помилок у діапазоні "Дані", а не відфільтровує їх.

=SUM(IF(ISERROR(Data);1;0))

Ця формула створює масив, який містить значення 1 для клітинок із помилками та значення 0 для клітинок без помилок. Формулу можна спростити та отримати такий самий результат, видаливши для функції IFтретій аргумент:

=SUM(IF(ISERROR(Data);1))

Якщо аргумент не вказувати та якщо клітинка не містить значення помилки, функція IF поверне значення FALSE (хибність). Формулу можна спростити ще більше:

=SUM(IF(ISERROR(Data)*1))

Ця версія працює, тому що TRUE*1=1, а FALSE*1=0.

Підсумування значень на основі умов

Інколи потрібно підсумувати значення на основі певних умов. Наприклад, ця формула масиву підсумовує лише додатні цілі числа в діапазоні "Продаж":

=SUM(IF(Продаж>0;Продаж))

Функція IF створює масив додатних і хибних значень. Функція SUM пропускає хибні значення, оскільки 0+0=0. Діапазон клітинок, який використовується в цій формулі, може містити будь-яку кількість рядків і стовпців.

Також можна підсумувати значення, які відповідають більше ніж одній умові. Наприклад, ця формула масиву обчислює значення, які більші за 0 і менші або дорівнюють 5:

=SUM((Продаж>0)*(Продаж<=5)*(Продаж))

Пам’ятайте, що ця формула повертатиме помилку, якщо діапазон містить одну або більше клітинок із нечисловими значеннями.

Також можна створювати формули масивів, які використовують тип умови АБО. Наприклад, можна підсумувати значення, менші за 5 і більші за 15:

=SUM(IF((Продаж<5)+(Продаж>15);Продаж))

Функція IF знаходить усі значення, менші за 5 і більші за 15, а потім передає їх функції SUM.

УВАГА! Функції AND і OR не можна використовувати у формулах масивів, тому що ці функції повертають один результат (TRUE або FALSE), а функції масивів працюють із масивами результатів. Цієї проблеми можна уникнути, використовуючи логіку з попередньої формули. Іншими словами, потрібно виконувати математичні операції, наприклад додавання або множення, зі значеннями, які відповідають умові OR (або) чи AND (і).