Приклади формул масивів і рекомендації

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

• Формула масиву для кількох клітинок

  

• У цьому прикладі ми обчислюємо загальний обсяг продажу купе й седанів для кожного продавця за допомогою введення формули =F10:F19*G10:G19 у клітинці H10.

  Якщо натиснути клавішу Enter, результати буде розподілено за клітинками H10:H19. Зверніть увагу, що діапазон розгортання буде взято в рамку, якщо виділити будь-яку клітинку в межах цього діапазону. Ви також можете помітити, що формули в клітинках H10:H19 неактивні. Вони потрібні лише для довідки. Тому якщо потрібно змінити формулу, потрібно вибрати клітинку H10, у якій розташовано основну формулу.

• Формула масиву з однією клітинкою

  

  У клітинці H20 зразка книги введіть або скопіюйте та вставте формулу =SUM(F10:F19*G10:G19), а потім натисніть клавішу Enter.

  У цьому випадку програма Excel перемножує значення масиву (діапазон клітинок F10 – G19), а потім за допомогою функції SUM підсумовує результати. Загальний обсяг збуту становить 1 590 000$.

  Цей приклад демонструє, наскільки корисною може бути така формула. Наприклад, у вас 1 000 рядків даних. Усі ці дані або їх частину можна підсумувати, створивши формулу масиву в одній клітинці, не додавати формулу до кожного із 1 000 рядків Також зверніть увагу на те, що формула для однієї клітинки в клітинці H20 не залежить від формули для кількох клітинок (у клітинках H10 – H19). Це вказує на ще одну перевагу формул масивів – гнучкість. Ви можете змінити формулу в стовпці H. Це не вплине на формулу в стовпці H20. Також може бути корисно мати незалежні підсумки (як показано в цьому прикладі), оскільки вони допомагають перевірити точність результатів.

• Динамічні формули масивів також мають такі переваги:

  • Узгодженість.    Якщо клацнути будь-яку клітинку в діапазоні під клітинкою H10, відобразиться та сама формула. Така послідовність допомагає досягти більшої точності.

  • Безпека.    Компонент формули масиву для кількох клітинок не можна змінити. Наприклад, клацніть клітинку H11 і натисніть клавішу Delete. Excel не змінюватиме вихідні дані масиву. Щоб її змінити, потрібно вибрати верхню ліву клітинку в масиві або клітинку H10.

  • Менший розмір файлу.    Часто замість кількох проміжних формул можна використовувати одну формулу масиву. Наприклад, у прикладі з продажем автомобілів для обчислення результатів у стовпці E використовують одну формулу масиву. Якби для обчислення тих самих результатів ви використовували стандартні формули (наприклад, =F10*G10, F11*G11, F12*G12 тощо), вам довелося б задіяти 11 різних формул. Це не має великого значення, але що робити, якщо потрібно підсумувати тисячі рядків? У такому випадку це може стати проблемою.

  • Ефективність.    Функції масиву є дуже ефективним способом побудувати складні формули. Формула масиву =SUM(F10:F19*G10:G19) є тим самим, що й: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

  • Розгортання.    Динамічні формули масиву автоматично розгортатимуться у вихідному діапазоні. Якщо вихідні дані зберігаються в таблиці Excel, динамічні формули масивів будуть автоматично змінювати розмір під час додавання та вилучення даних.

  • Помилка #РОЗГОРТАННЯ!    У динамічних масивах з’явилася помилка #РОЗГОРТАННЯ!,яка вказує, що цільовий діапазон розгортання з якоїсь причини заблоковано. Після вирішення проблеми блокування формулу буде автоматично розгорнуто.

Константи-масиви – це складова формул масивів. Для створення констант-масивів, слід ввести список елементів, а потім самостійно взяти його у фігурні дужки ({ }), наприклад:

={1\2\3\4\5} або ={"січень"\"лютий"\"березень"}

Якщо записати елементи через кому, створюється горизонтальний масив (рядок). Якщо записати елементи через крапку з комою, створюється вертикальний масив (стовпець). Щоб створити двовимірний масив, елементи в кожному рядку потрібно записати через кому, а в кожному стовпці – через крапку з комою.

У наведених нижче вправах показано, як створювати горизонтальні, вертикальні та двовимірні константи. Ми покажемо приклади використання функції SEQUENCE для автоматичного створення констант-масивів, а також використання введених вручну констант-масивів.

• Створення горизонтальної константи

  Відкрийте книгу з попередніми прикладами або створіть нову. Виділіть будь-яку пусту клітинку та введіть =SEQUENCE(1,5). Функція SEQUENCE створює масив з 1 рядком і 5 стовпцями так само, як і формула ={1\2\3\4\5}. З’явиться такий результат:

  

• Створення вертикальної константи

  Виберіть будь-яку пусту клітинку з рядком під нею та введіть =SEQUENCE(5) або ={1:2:3:4:5}. З’явиться такий результат:

  

• Створення двовимірної константи

  Виберіть будь-яку пусту клітинку з рядком праворуч і під нею та введіть =SEQUENCE(3,4). З’явиться такий результат:

  

  Ви також можете ввести: або={1\2\3\4:5\6\7\8:9\10\11\12}, але зверніть увагу на місце, де ви ставите крапку з комою замість ком.

  Як бачите, параметр SEQUENCE надає значні переваги в порівнянні з введенням значень константи-масиву вручну. Здебільшого це заощаджує час, але також дає змогу зменшити кількість помилок, які виникають під час введення вручну. Його також легше читати, особливо тому, що крапки з комами важко відрізнити від роздільників у вигляді ком.

Ось приклад використання константи-масиву в складі більшої формули. У зразку книги перейдіть до аркушу Константа у формулі або створіть інший аркуш.

У клітинці D9 ми ввели формулу =SEQUENCE(1,5,3,1), але ви також можете ввести 3, 4, 5, 6 і 7 у клітинки A9:H9. У цьому конкретному виборі числа немає нічого особливого. Ми просто вибрали значення, яке не дорівнює значенням від 1 до 5.

У клітинці E11 введіть =SUM(D9:H9*SEQUENCE(1,5)) або =SUM(D9:H9*{1\2\3\4\5}). Формули повертають 85.

Функція SEQUENCE створює еквівалент константи-масиву {1\2\3\4\5}. Оскільки Excel у першу чергу виконує операції з елементами, узятими в дужки, далі будуть використовуватися значення клітинок в D9:H9 і оператор множення (*). На цьому етапі формула помножить значення в збереженому масиві на відповідні значення в константі. Це еквівалент такої формули:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5) або =SUM(3*1,4*2,5*3,6*4,7*5)

Нарешті, функція SUM додає значення та повертає 85.

Якщо операція має виконуватися повністю в пам’яті без використання збереженого масиву, замініть цей масив на іншу константу масиву:

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)) або =SUM({3\4\5\6\7}*{1\2\3\4\5})

Елементи, які можна використовувати в константах-масивах

• Константи-масиви можуть містити числа, текст, логічні значення (наприклад, TRUE та FALSE), а також значення помилок, як-от #N/A. Числа можна записувати як цілі значення, а також у десятковому та експоненційному форматах. Якщо до константи додається текст, його необхідно брати в прямі лапки ("текст”).

• Константи масивів не можуть містити додаткові масиви, формули або функції. Іншими словами, вони можуть містити лише текст або числа, відокремлені комою або крапкою з комою. Якщо ввести щось на кшталт {1\2\A1:D4} або {1\2\SUM(Q2:Z8)}, у програмі Excel з’явиться попередження. Крім цього, числові значення не можуть містити коми, дужки, знаки відсотка та долара.

Іменовані константи-масиви – один із найзручніших видів таких констант. Іменованими константами легше оперувати, крім того, вони можуть дещо спростити розуміння формул масивів. Щоб надати ім’я константі-масиву та використати її у формулі, зробіть ось що.

Виберіть Формули > Визначені імена > Визначити ім’я. У полі Ім’я введіть "Квартал1". У полі Посилання введіть таку константу (фігурні дужки додаються вручну):

={"січень"\"лютий"\"березень"}

Діалогове вікно повинно мати такий вигляд:

Натисніть кнопку OK, а потім виберіть будь-який рядок із трьома пустими клітинками та введіть =Квартал1.

З’явиться такий результат:

Якщо потрібно, щоб результати розгорталися вертикально, а не горизонтально, використовуйте функцію =TRANSPOSE(Квартал1).

Якщо ви хочете відобразити список із 12 місяців, наприклад, який можна використовувати під час створення фінансового звіту, використовуйте функцію SEQUENCE як основу для поточного року. Відмінною особливістю цієї функції є те, що, незважаючи на те що відображається лише місяць, за ним розташовується допустима дата, яку можна використовувати в інших обчисленнях. Ці приклади наведено на аркушах Константа іменованого масиву та Швидкий зразок набору даних у зразку книги.

EXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"ммм")

Функцію DATE використовують для створення дати на основі поточного року, функція SEQUENCE дає змогу створити константу-масив із 1 по 12 із січня по грудень, а функція TEXT перетворює формат відображення в "ммм" (січень, лютий, березень і т. д.). Якщо ви хочете показати повну назву місяця, наприклад "Січень", використовуйте формат "мммм".

Використовуючи іменовану константу як формулу масиву, не забувайте вводити знак рівності, наприклад =Квартал1, а не Квартал1. Якщо цього не зробити, програма Excel вважатиме цей масив текстовим рядком і формула не працюватиме. Також пам’ятайте, що можна використовувати комбінації функцій, тексту та чисел. Усе залежить від того, наскільки креативно ви хочете все зробити.

У наступному прикладі показано кілька способів використання констант у формулах масивів. У деяких прикладах для перетворення рядків на стовпці (і навпаки) використовується функція TRANSPOSE.

• Множення кожного елемента масиву

  Введіть =SEQUENCE(1,12)*2 або ={1\2\3\4:5\6\7\8:9\10\11\12}*2

  Ви також можете виконати ділення за допомогою знаку /, додавання за допомогою знаку + і віднімання за допомогою знаку -.

• Піднесення до квадрата елементів масиву

  Введіть =SEQUENCE(1,12)^2 або ={1\2\3\4:5\6\7\8:9\10\11\12}^2

• Пошук квадратного кореня квадратів елементів у масиві

  Введіть =SQRT(SEQUENCE(1,12)^2) або =SQRT({1\2\3\4:5\6\7\8:9\10\11\12}^2)

• Транспонування одновимірного рядка

  Введіть =TRANSPOSE(SEQUENCE(1,5)) або =TRANSPOSE({1\2\3\4\5})

  Ви ввели константу горизонтального масиву, але функція TRANSPOSE перетворює цю константу на стовпець.

• Транспонування одновимірного стовпця

  Введіть =TRANSPOSE(SEQUENCE(5,1)) або =TRANSPOSE({1:2:3:4:5})

  Ви ввели константу вертикального масиву, але функція TRANSPOSE перетворює цю константу на рядок.

• Транспонування двовимірної константи

  Введіть =TRANSPOSE(SEQUENCE(3,4)) або =TRANSPOSE({1\2\3\4:5\6\7\8:9\10\11\12})

  Функція TRANSPOSE перетворить кожний рядок на низку стовпців.

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

• Створення масиву з наявних значень

  У наведеному нижче прикладі пояснюється, як за допомогою формули масивів створювати масив з існуючого масиву.

  Введіть =SEQUENCE(3,6,10,10) або ={10\20\30\40\50\60:70\80\90\100\110\120:130\140\150\160\170\180}

  Оскільки потрібно створити масив чисел, не забудьте ввести { (ліву фігурну дужку), перш ніж вводити 10, і } (праву фігурну дужку), коли введете 180.

  Потім введіть =D9# або =D9:I11 у пустій клітинці. З’явиться масив клітинок 3 x 6 з однаковими значеннями в клітинках D9:D11. Символ # має назву оператор розгорнутого діапазону. Він використовується в Excel для посилання на весь діапазон масиву, замість того щоб вводити його.

  

• Створення константи масиву з наявних значень

  Ви можете отримати результати формули розгорнутого масиву й перетворити його на компоненти. Виберіть клітинку D9, а потім натисніть клавішу F2, щоб перейти в режим редагування. Потім натисніть клавішу F9, щоб вставити значення замість посилань на клітинки, які Excel перетворить на константу-масив. Якщо натиснути клавішу Enter, формула =D9# матиме такий вигляд: ={10\20\30:40\50\60:70\80\90}.

• Підрахунок символів у діапазоні клітинок

  У наведеному нижче прикладі показано, як у діапазоні клітинок підрахувати кількість символів. Включно з пробілами.

  

  =SUM(LEN(C9:C13))

  У цьому випадку функція LEN повертає довжину кожного рядка тексту в кожній із клітинок діапазону. Потім функція SUM підсумовує ці значення та відображає результат (66). Щоб отримати середню кількість символів, використовуйте таку формулу:

  =AVERAGE(LEN(C9:C13))

• Вміст найдовшої клітинки в діапазоні C9:C13

  =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

  Ця формула працює, тільки коли діапазон даних містить один стовпець клітинок.

  Розгляньмо цю формулу, починаючи із внутрішніх елементів. Функція LEN повертає довжину кожного елемента в діапазоні клітинок D2:D6. Функція MAX обчислює найбільше значення серед цих елементів, яке відповідає найдовшому текстовому рядку в клітинці D3.

  А тепер усе трохи ускладнюється. Функція MATCH обчислює зсув (відносне положення) клітинки, яка містить найдовший текстовий рядок. Для цього їй потрібні три аргументи: шукане_значення, масив_який_переглядається та тип_зіставлення. Функція MATCH шукає в масиві (масив, який переглядається) певне значення (шукане значення). У цьому прикладі шукане значення – це найдовший текстовий рядок:

  MAX(LEN(C9:C13)

  Цей рядок розміщено в цьому масиві:

  LEN(C9:C13)

  Аргумент типу зіставлення в цьому випадку має значення 0. Тип зіставлення може мати значення 1, 0 або -1.

  • Якщо вказано значення 1, буде повернено найбільше значення, яке менше або дорівнює шуканому значенню.

  • Якщо вказано значення 0, буде повернено перше значення, яке точно дорівнює шуканому значенню.

  • Якщо вказано значення -1, буде повернено найменше значення, яке більше або дорівнює вказаному шуканому значенню.

  • Якщо тип зіставлення не вказано, програма Excel присвоїть цьому аргументу значення 1.

  Нарешті, функція INDEX оперує такими аргументами: масив, номер рядка та номер стовпця в цьому масиві. Діапазон клітинок C9:C13 – це масив, функція MATCH– адреса клітинки, а останній аргумент (1) указує на те, що значення походить із першого стовпця в масиві.

  Якщо потрібно отримати вміст із найменшого текстового рядка, замініть значення MAX у наведеному вище прикладі на MIN.

• Пошук n найменших значень у діапазоні

  У цьому прикладі показано, як знайти три найменших значення в діапазоні клітинок, у яких масив зразків даних у клітинках B9:B18 створено за допомогою такої формули: =INT(RANDARRAY(10,1)*100). Зверніть увагу, що функція RANDARRAY – це мінлива функція, тому ви отримуватимете новий набір випадкових чисел під час кожного обчислення в Excel.

  

  Введіть =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})

  У цій формулі за допомогою константи масиву тричі обчислюється функція SMALL, яка повертає третє найменше значення в масиві, що міститься в діапазоні B9:B18. 3 – це значення змінної в клітинці D9. Щоб знайти більше значень, збільште значення у функції SEQUENCE або додайте більше аргументів до константи. З цією формулою також можна використовувати додаткові функції, зокрема SUM або AVERAGE. Наприклад:

  =SUM(SMALL(B9#,SEQUENCE(D9))

  =AVERAGE(SMALL(B9#,SEQUENCE(D9))

• Пошук n найбільших значень у діапазоні

  Щоб знайти найбільші значення в діапазоні, функцію SMALL слід замінити на функцію LARGE. У наведеному нижче прикладі також використовуються функції ROW та INDIRECT.

  Введіть =LARGE(B9#,ROW(INDIRECT("1:3"))) або =LARGE(B9:B18,ROW(INDIRECT("1:3")))

  Тепер буде корисно дізнатися про функції ROW та INDIRECT. Функція ROW використовується для створення масиву послідовних цілих чисел. Наприклад, виберіть пустий рядок і введіть:

  =ROW(1:10)

  Формула створить стовпець із 10 послідовних цілих чисел. Щоб побачити можливу проблему, вставте рядок над діапазоном, який містить формулу масиву (над рядком 1). Програма Excel змінить посилання на рядки, і формула тепер створить цілі числа від 2 до 11. Щоб вирішити цю проблему, до формули потрібно додати функцію INDIRECT:

  =ROW(INDIRECT("1:10"))

  У функції INDIRECT у ролі аргументів використовуються текстові рядки (саме тому діапазон 1:10 узято в лапки). Програма Excel не змінюватиме текстові значення після вставлення рядків або інших переміщень формули масиву. У результаті функція ROW завжди створюватиме масив потрібних цілих чисел. Ви так само легко можете використовувати функцію SEQUENCE:

  =SEQUENCE(10)

  Розглянемо формулу, яку ви використовували раніше – =LARGE(B9#,ROW(INDIRECT("1:3"))), починаючи з внутрішніх елементів. Функція INDIRECT повертає набір текстових значень (значення 1–3). Функція ROW у свою чергу створює в стовпці масив із трьох клітинок. Функція LARGE використовує значення в діапазоні клітинок B9:B18 і обчислюється тричі (один раз для кожного посилання, яке повертає функція ROW). Якщо потрібно отримати більше значень, до функції INDIRECT слід додати більший діапазон клітинок. Як і в прикладах з функцією SMALL, цю формулу також можна використовувати з іншими функціями, зокрема SUM і AVERAGE.

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

  У програмі 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.

Інколи потрібно підсумувати значення на основі певних умов.

Наприклад, ця формула масиву підсумовує лише додатні цілі числа в діапазоні з назвою "Збут", який представляє клітинки E9:E24 в прикладі вище:

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

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

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

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

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

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

=SUM(IF((Продаж>0)+(Продаж<2500),Продаж))

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

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

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

Функція IF створює масив значень, які не дорівнюють 0, а потім передає ці значення функції AVERAGE.

Ця формула масиву порівнює значення у двох діапазонах клітинок з назвами «Мої_дані» та «Ваші_дані» і повертає кількість відмінностей між ними. Якщо вміст двох діапазонів однаковий, формула повертає значення 0. Для цієї формули потрібно, щоб діапазони клітинок мали такий самий розмір і вимір. Наприклад, якщо діапазон клітинки "Мої_дані" має 3 рядки та 5 стовпців, клітинка "Ваші_дані" також повинна містити 3 рядки та 5 стовпців.

=SUM(IF(Мої_дані=Ваші_дані,0,1))

Формула створює масив такого самого розміру, що й порівнювані діапазони. Функція IF заповнює цей діапазон значеннями 0 і 1 (0 – відмінності, 1 – збіги). Після цього функція SUM повертає суму значень у діапазоні.

Формулу можна спростити так:

=SUM(1*(Мої_дані<>Ваші_дані))

Ця формула працює, тому що TRUE*1=1, а FALSE*1=0 (так само як і формула, що підраховує кількість помилок у діапазоні).

Ця формула масиву повертає номер рядка, який містить максимальне значення в діапазоні "Дані", що складається з одного стовпця.

=MIN(IF(Дані=MAX(Дані);ROW(Дані);""))

Функція IF створює масив, який відповідає діапазону "Дані". Якщо відповідна клітинка містить максимальне значення діапазону, масив міститиме номер цього рядка. В іншому разі масив міститиме пустий рядок (""). Функція MIN використовує новий масив як другий аргумент і повертає найменше значення, яке відповідає номеру рядка максимального значення в діапазоні "Дані". Якщо діапазон "Дані" містить кілька однакових максимальних значень, формула повертає рядок із першим значенням.

Щоб повернути фактичну адресу клітинки з максимальним значенням, використовуйте таку формулу:

=ADDRESS(MIN(IF(Дані=MAX(Дані);ROW(Дані);""));COLUMN(Дані))

Схожі приклади наведено в зразку книги на аркуші Відмінності між наборами даних.

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

• Формула масиву для кількох клітинок

Скопіюйте всі клітинки наведеної нижче таблиці та вставте їх у клітинку A1 на пустому аркуші.

Продавець	Тип автомобіля	Кількість проданих автомобілів	Ціна за одиницю	Загальний обсяг  продажів
Мороз	Седан	5	33000
	Купе	4	37000
Омельченко	Седан	6	24000
	Купе	8	21000
Єрьоменко	Седан	3	29000
	Купе	1	31000
Попкова	Седан	9	24000
	Купе	5	37000
Горноженко	Седан	6	33000
	Купе	8	31000
Формула (загальний підсумок)			Загальний підсумок
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. Щоб побачити загальний обсяг продажу купе й седанів для кожного продавця, виділіть діапазон клітинок E2:E11, введіть формулу =C2:C11*D2:D11, а потім натисніть сполучення клавіш Ctrl+Shift+Enter.

2. Щоб побачити загальний підсумок продажу, виділіть клітинку F11, введіть формулу =SUM(C2:C11*D2:D11), а потім натисніть сполучення клавіш Ctrl+Shift+Enter.

Якщо натиснути сполучення клавіш Ctrl+Shift+Enter, формулу в Excel буде взято у фігурні дужки ({ }) і її екземпляр буде додано до кожної клітинки виділеного діапазону. Це відбувається дуже швидко, і в стовпці Е відображається загальний обсяг збуту кожного типу автомобілів для кожного продавця. Якщо вибрати E2, потім – E3, E4 тощо, формула залишається тією самою: {=C2:C11*D2:D11}. 

• Створення формули масиву для однієї клітинки

У клітинці D13 книги введіть формулу подану нижче, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

У цьому випадку програма Excel перемножує значення масиву (діапазон клітинок C2–D11), а потім за допомогою функції SUM підсумовує результати. Загальний обсяг збуту становить 1 590 000$. Цей приклад демонструє, наскільки корисною може бути така формула. Наприклад, у вас 1 000 рядків даних. Усі ці дані або їх частину можна підсумувати, створивши формулу масиву в одній клітинці, не додавати формулу до кожного із 1 000 рядків

Також зверніть увагу на те, що формула для однієї клітинки в клітинці D13 не залежить від формули для кількох клітинок (у клітинках E2–E11). Це вказує на ще одну перевагу формул масивів – гнучкість. Ви можете змінити формули в стовпці Е або взагалі видалити цей стовпець, і це зовсім не вплине на формулу в клітинці D13.

Формули масивів мають також такі переваги:

• Узгодженість.    Якщо клацнути будь-яку клітинку в діапазоні під клітинкою Е2, відобразиться та сама формула. Така послідовність допомагає досягти більшої точності.

• Безпека.    Компонент формули масиву для кількох клітинок не можна змінити. Наприклад, клацніть клітинку E3 та натисніть клавішу Delete. Потрібно вибрати увесь діапазон клітинок (Е2 – Е11) і змінити формулу для всього масиву або залишити його без змін. З міркувань безпеки знадобиться натиснути сполучення клавіш Ctrl+Shift+Enter, щоб підтвердити змінення формули.

• Менший розмір файлу.    Часто замість кількох проміжних формул можна використовувати одну формулу масиву. Наприклад, у книзі для обчислення результатів у стовпці Е використовується одна формула масиву. Якби для обчислення тих самих результатів ви використовували стандартні формули (наприклад, =C2*D2, C3*D3, C4*D4...), вам довелося б задіяти 11 різних формул.

Загалом у формулах масиву використовується стандартний синтаксис формул. Усі вони починаються знаком рівності (=), і в формулах можна використовувати більшість вбудованих функцій Excel. Основна відмінність полягає в тому, що для введення формули масиву потрібно натиснути сполучення клавіш Ctrl+Shift+Enter. Після цього програма Excel бере формулу масиву у фігурні дужки. Якщо такі дужки ввести самостійно, формула перетвориться на текстовий рядок і не працюватиме.

Функції масиву є дуже ефективним способом побудувати складні формули. Формула масиву =SUM(C2:C11*D2:D11) є тим самим, що й: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Працюючи з формулами для кількох клітинок, також слід пам’ятати про таке:

• Діапазон клітинок, у яких відображатимуться результати, необхідно виділити до того, як ви почнете вводити формулу. Ви робили це, коли створили формулу масиву для кількох клітинок, коли виділяли діапазон клітинок E2 – E11.

• Вміст окремої клітинки у формулі масиву змінити не можна. Щоб переконатися в цьому, виділіть клітинку Е3 у книзі із зразком і натисніть клавішу Delete. Програма Excel покаже повідомлення про те, що ви не можете змінити частину масиву.

• Усю формулу масиву переміщати та видаляти можна, але її певну частину – ні. Іншими словами, Щоб зменшити формулу масиву, потрібно видалити наявну формулу та ввести нову.

• Щоб видалити формулу масиву, виділіть усю формулу (наприклад, E2:E11), а потім натисніть клавішу Delete.

• У формулі масиву для кількох клітинок не можна видаляти клітинки та вставляти пусті клітинки.

Інколи потрібно розширити формулу масиву. Виберіть першу клітинку в наявному діапазоні масиву та продовжуйте, доки не виберете весь діапазон, до якого потрібно розширити формулу. Натисніть клавішу F2, щоб змінити формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter, щоб підтвердити формулу після зміни діапазону формул. Важливо вибрати весь діапазон, починаючи з верхньої лівої клітинки масиву. Верхня ліва клітинка – це клітинка, яку буде змінено.

Попри всі переваги формули масивів мають також свої недоліки:

• Час від часу можна забути натиснути сполучення клавіш Ctrl+Shift+Enter. Це може трапитися навіть із самими досвідченими користувачами програми Excel. Пам’ятайте, що потрібно натискати це сполучення клавіш, щоб ввести або змінити формулу масиву.

• Інші користувачі ваших книг можуть не розуміти цих формул. На практиці формули масивів зазвичай не пояснюються на аркуші. Якщо іншим користувачам доведеться вносити зміни до ваших книг, не використовуйте формули масивів або переконайтеся, що ці користувачі розуміють, як їх змінювати за необхідності.

• Залежно від швидкості обробки даних і обсягу пам’яті комп’ютера великі формули масивів можуть обчислюватися повільно.

Константи масивів – це складова формул масивів. Для створення константи масивів, слід ввести список елементів, а потім самостійно взяти його у фігурні дужки ({ }), наприклад:

={1\2\3\4\5}

Тепер ви знаєте, що для створення формули масивів потрібно натиснути сполучення клавіш Ctrl+Shift+Enter. Оскільки константи-масиви – це складова формул масивів, константи потрібно брати у фігурні дужки та вводити ці символи вручну. Після цього, щоб ввести всю формулу, слід натиснути клавіші Ctrl+Shift+Enter.

Якщо записати елементи через кому, створюється горизонтальний масив (рядок). Якщо записати елементи через крапку з комою, створюється вертикальний масив (стовпець). Щоб створити двовимірний масив, елементи в кожному рядку потрібно записати через кому, а в кожному стовпці – через крапку з комою.

Ось приклад масиву в одному рядку: {1\2\3\4}. Ось приклад масиву у одному стовпці: {1:2:3:4}. А це приклад масиву із двома рядками та чотирма стовпцями: {1\2\3\4:5\6\7\8}. У масиві з двох рядків перший ряд – 1, 2, 3 та 4, а другий ряд – це 5, 6, 7 і 8. Єдина крапка з комою розділяє два рядки між 4 та 5.

Константи масивів, як і формули масивів, можна використовувати з будь-якими вбудованими функціями програми Excel. У наведених нижче розділах пояснюється, як створюється кожний тип константи та як використовувати ці константи з функціями Excel.

У наведених нижче вправах показано, як створювати горизонтальні, вертикальні та двовимірні константи.

Створення горизонтальної константи

1. На пустому аркуші виберіть клітинки від A1 до E1.

2. У рядку формул введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   ={1\2\3\4\5}

   У цьому випадку потрібно ввести відкриті та закриті фігурні дужки ({ }), і Excel додасть другий набір.

   З’явиться такий результат:

   

Створення вертикальної константи

1. Виділіть у книзі стовпець із п’яти клітинок.

2. У рядку формул введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   ={1:2:3:4:5}

   Відобразиться такий результат:

   

Створення двовимірної константи

1. У книзі виділіть блок клітинок чотири стовпці завширшки та три рядки заввишки.

2. У рядку формул введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   ={1\2\3\4:5\6\7\8:9\10\11\12}

   Відобразиться такий результат:

   

Використання констант у формулах

Ось простий приклад використання констант:

1. Створіть новий аркуш у зразку книги.

2. У клітинці A1 введіть 3, а потім введіть 4 в клітинці B1, 5 у клітинці C1, 6 у клітинці D1 і 7 у клітинці E1.

3. У клітинці A3 введіть формулу, наведену нижче, і натисніть сполучення клавіш Ctrl+Shift+Enter:

   =SUM(A1:E1*{1\2\3\4\5})

   Зверніть увагу, що програма Excel бере константу в ще одні фігурні дужки, тому що ви ввели її як формулу масиву.

   

   У клітинці А3 з’явиться значення 85.

У наступному розділі пояснюється, як працює ця формула.

Щойно введена формула складається з кількох частин.

1. Функція

2. Збережений масив

3. Оператор

4. Константа масиву

Останній елемент у дужках це константа масиву: {1\2\3\4\5}. Пам’ятайте, що програма Excel не позначає константи масиву дужками, вам потрібно їх вводити самостійно. Також пам’ятайте про те, що коли ви додаєте константу до масиву формули, потрібно натиснути сполучення клавіш Ctrl+Shift+Enter, щоб ввести формулу.

Оскільки Excel у першу чергу виконує операції з елементами, узятими в дужки, далі будуть використовуватися значення, які зберігаються в книзі (діапазон A1:E1), і відповідний оператор. На цьому етапі формула помножить значення в збереженому масиві на відповідні значення в константі. Це еквівалент такої формули:

=SUM(A1*1;B1*2;C1*3;D1*4;E1*5)

Нарешті, функція SUM додає значення, і сумі 85 з’являється у клітинці A3.

Якщо операція має виконуватися повністю в пам’яті без використання збереженого масиву, замініть цей масив на іншу константу масиву:

=SUM({3\4\5\6\7}*{1\2\3\4\5})

Щоб спробувати цей варіант, скопіюйте функцію, виділіть пусту клітинку в книзі, вставте формулу в рядок формул і натисніть сполучення клавіш Ctrl+Shift+Enter. Відобразиться той самий результат, що й у попередній вправі, у якій використовувалася така формула масиву:

=SUM(A1:E1*{1\2\3\4\5})

Константи масивів можуть містити числа, текст, логічні значення (наприклад, «Істина» та «Хибність») і значення помилок (наприклад, #н/д). Числа можна записувати як цілі значення, а також у десятковому та науковому форматах. Якщо до константи додається текст, його необхідно брати у прямі лапки (" ).

Константи масивів не можуть містити додаткові масиви, формули або функції. Іншими словами, вони можуть містити лише текст або числа, відокремлені комою або крапкою з комою. Якщо ввести щось на кшталт {1\2\A1:D4} або {1\2\SUM(Q2:Z8)}, у програмі Excel з’явиться попередження. Крім цього, числові значення не можуть містити коми, дужки, знаки відсотка та долара.

Іменовані константи-масиви – один із найзручніших видів таких констант. Іменованими константами легше оперувати, крім того, вони можуть дещо спростити розуміння формул масивів. Щоб надати ім’я константі-масиву та використати її у формулі, зробіть ось що.

1. На вкладці Формули у групі Визначені імена натисніть кнопку Визначити ім’я.
   Відкриється діалогове вікно Нове ім’я.

2. У полі Ім’я введіть Квартал1.

3. У полі Посилання введіть таку константу (фігурні дужки додаються вручну):

   ={"січень"\"лютий"\"березень"}

   Вміст діалогового вікна матиме такий вигляд:

   

4. Натисніть OK і виберіть рядок із трьох пустих клітинок.

5. Введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter.

   =Квартал1

   Відобразиться такий результат:

   

Використовуючи іменовану константу як формулу масиву, не забувайте вводити знак рівності. Якщо цього не зробити, програма Excel вважатиме цей масив текстовим рядком, і формула не працюватиме. Також пам’ятайте, що можна використовувати комбінації тексту та чисел.

Якщо константи масивів не працюють, перевірте їх на наявність наведених нижче проблем.

• Деякі елементи можуть бути не відокремлені відповідним символом. Якщо пропустити кому або крапку з комою чи поставити їх в іншому місці, константа масиву створиться неправильно або з’явиться попередження.

• Ви можете вибрати діапазон клітинок, кількість яких не відповідає кількості елементів у константі. Наприклад, якщо виділити стовпець із шести клітинок для константи на п’ять клітинок, у пустій клітинці з’являється значення помилки #н/д. І навпаки, якщо виділити недостатню кількість клітинок, програма Excel пропустить значення, які не мають відповідної клітинки.

У наступному прикладі показано кілька способів використання констант у формулах масивів. У деяких прикладах для перетворення рядків на стовпці (і навпаки) використовується функція TRANSPOSE.

Множення кожного елемента масиву

1. Створіть новий аркуш і виділіть блок пустих клітинок чотири стовпці завширшки та три рядки заввишки.

2. Введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   ={1\2\3\4:5\6\7\8:9\10\11\12}*2

Піднесення до квадрата елементів масиву

1. Виділіть блок пустих клітинок чотири стовпці завширшки та три рядки заввишки.

2. Введіть наведену нижче формулу масиву, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   ={1\2\3\4:5\6\7\8:9\10\11\12}*{1\2\3\4:5\6\7\8:9\10\11\12}

   Також можна ввести таку формулу масиву (використовується оператор "кришки" – ^):

   ={1\2\3\4:5\6\7\8:9\10\11\12}^2

Транспонування одновимірного рядка

1. Виділіть стовпець із п’яти пустих клітинок.

2. Введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   =TRANSPOSE({1\2\3\4\5})

   Ви ввели константу горизонтального масиву, але функція TRANSPOSE перетворює цю константу на стовпець.

Транспонування одновимірного стовпця

1. Виділіть рядок із п’яти пустих клітинок.

2. Введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   =TRANSPOSE({1:2:3:4:5})

Ви ввели константу вертикального масиву, але функція TRANSPOSE перетворює цю константу на рядок.

Транспонування двовимірної константи

1. Виділіть блок пустих клітинок три стовпці завширшки та чотири рядки заввишки.

2. Введіть наведену нижче константу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   =TRANSPOSE({1\2\3\4:5\6\7\8:9\10\11\12})

   Функція TRANSPOSE перетворить кожний рядок на низку стовпців.

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

Створення масивів і констант масивів із наявних значень

У наступному прикладі пояснюється, як за допомогою формули масивів створювати зв’язки між діапазонами клітинок на різних аркушах. Цей приклад також демонструє, як створювати константу масиву з того самого набору значень.

Створення масиву з наявних значень

1. На аркуші Excel виділіть діапазон клітинок C8:E10 і введіть таку формулу:

   ={10\20\30:40\50\60:70\80\90}

   Оскільки потрібно створити масив чисел, не забудьте ввести { (ліву фігурну дужку), перш ніж вводити 10, і } (праву фігурну дужку), коли введете 90.

2. Після цього натисніть сполучення клавіш Ctrl+Shift+Enter, щоб ввести цей масив чисел у діапазоні клітинок C8:E10 за допомогою формули масиву. На аркуші клітинки C8–E10 повинні мати такий вигляд:

   10	20	30
   40	50	60
   70	80	90

3. Виділіть діапазон клітинок C1–E3.

4. Введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   =C8:E10

   Масив клітинок 3x3 відображатиметься в клітинках C1–E3 з тими ж значеннями, які введено в діапазоні C8–E10.

Створення константи масиву з наявних значень

1. Виділивши клітинки C1:C3, натисніть клавішу F2, щоб активувати режим редагування. 

2. Натисніть клавішу F9, щоб посилання на клітинки перетворилися на значення. Програма Excel перетворить значення на константу масиву. Тепер формула повинна мати такий вигляд: ={10\20\30:40\50\60:70\80\90}.

3. Натисніть сполучення клавіш Ctrl+Shift+Enter для того, щоб ввести константу масиву як формулу масиву.

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

У наступному прикладі показується, як у діапазоні клітинок підрахувати кількість символів, включно із пробілами.

1. Скопіюйте таблицю повністю та вставте її на аркуш у клітинку A1.

   Дані
   Це
   велика кількість клітинок, які
   об'єднуються
   для формування
   єдиного речення.
   Загальна кількість символів у клітинках A2:A6
   =SUM(LEN(A2:A6))
   Вміст найдовшої клітинки (A3)
   =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

2. Виділіть клітинку A8, а потім натисніть сполучення клавіш Ctrl+Shift+Enter, щоб побачити загальну кількість символів у клітинках A2:A6 (66).

3. Виділіть клітинку A10, а потім натисніть клавіші Ctrl+Shift+Enter, щоб побачити вміст найдовшої клітинки діапазону A2:A6 (клітинка А3).

У клітинці A8 для підрахунку загальної кількості символів (66) у клітинках з A2 по A6 використовується така формула.

=SUM(LEN(A2:A6))

У цьому випадку функція LEN повертає довжину кожного рядка тексту в кожній із клітинок діапазону. Потім функція SUM підсумовує ці значення та відображає результат (66).

Пошук n найменших значень у діапазоні

У цьому прикладі показується, як знайти три найменші значення в діапазоні клітинок.

1. Введіть випадкові числа в клітинки A1:A11.

2. Виділіть клітинки від C1 до C3. У цьому наборі клітинок відображатимуться результати, які поверне формула масиву.

3. Введіть наведену нижче формулу, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

   =SMALL(A1:A11,{1:2:3})

У цій формулі за допомогою константи-масиву тричі обчислюється функція SMALL, яка повертає найменше (1), друге найменше (2) і третє найменше (3) значення в масиві, що містяться в діапазоні A1:A10. Щоб знайти більше значень, до константи слід додати більше аргументів. З цією формулою також можна використовувати додаткові функції, зокрема SUM або AVERAGE. Наприклад:

=SUM(SMALL(A1:A10,{1\2\3})

=AVERAGE(SMALL(A1:A10,{1\2\3})

Пошук n найбільших значень у діапазоні

Щоб знайти найбільші значення в діапазоні, функцію SMALL слід замінити на функцію LARGE. У наступному прикладі також використовуються функції ROW та INDIRECT.

1. Виділіть клітинки від D1 до D3.

2. У рядку формул введіть наведену нижче формулу та натисніть сполучення клавіш Ctrl+Shift+Enter:

   =LARGE(A1:A10,ROW(INDIRECT("1:3")))

Тепер буле корисно дізнатися про функції ROW та INDIRECT. Функція ROW використовується для створення масиву послідовних цілих чисел. Наприклад, виділіть пустий стовпець із 10 клітинок у книзі з вправами, введіть наведену нижче формулу масиву в клітинках A5–A14, а потім натисніть сполучення клавіш Ctrl+Shift+Enter:

=ROW(1:10)

Формула створить стовпець із 10 послідовних цілих чисел. Щоб побачити можливу проблему, вставте рядок над діапазоном, який містить формулу масиву (над рядком 1). Програма Excel змінить посилання на рядки, і формула створить цілі числа від 2 до 11. Щоб вирішити цю проблему, до формули потрібно додати функцію INDIRECT:

=ROW(INDIRECT("1:10"))

У функції INDIRECT у ролі аргументів використовуються текстові рядки (саме тому діапазон 1:10 узято у прямі лапки). Програма Excel не змінюватиме текстові значення після вставлення рядків або інших переміщень формули масиву. У результаті функція ROW завжди створюватиме масив потрібних цілих чисел.

Розглянемо формулу, яку ви використовували раніше – =LARGE(A5:A14,ROW(INDIRECT("1:3"))), починаючи з внутрішніх елементів. Функція INDIRECT повертає набір текстових значень (значення 1–3). Функція ROW у свою чергу створює в стовпці масив із трьох клітинок. Функція LARGE використовує значення в діапазоні клітинок A5:A14 і обчислюється тричі (один раз для кожного посилання, яке повертає функція ROW). У масив із трьох клітинок у стовпці повертаються значення 3200, 2700 і 2000. Якщо потрібно отримати більше значень, до функції INDIRECT слід додати більший діапазон клітинок.

Як і в прикладах вище, цю формулу також можна використовувати з іншими функціями, зокрема SUM і AVERAGE.

Пошук найдовшого текстового рядка в діапазоні клітинок

Поверніться до попереднього прикладу текстового рядка, введіть наведену нижче формулу в пустку клітинку та натисніть сполучення клавіш Ctrl+Shift+Enter:

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

З’явиться текст "група клітинок".

Розгляньмо цю формулу, починаючи із внутрішніх елементів. Функція LEN повертає довжину кожного елемента в діапазоні клітинок A2:A6. Функція MAX обчислює найбільше значення серед цих елементів, яке відповідає найдовшому текстовому рядку в клітинці A3.

А тепер усе трохи ускладнюється. Функція MATCH обчислює зсув (відносне положення) клітинки, яка містить найдовший текстовий рядок. Для цього їй потрібні три аргументи: шукане_значення, масив_який_переглядається та тип_зіставлення. Функція MATCH шукає в масиві (масив, який переглядається) певне значення (шукане значення). У цьому прикладі шукане значення – це найдовший текстовий рядок:

(MAX(LEN(A2:A6))

Цей рядок розміщено в цьому масиві:

LEN(A2:A6)

Аргумент типу зіставлення має значення 0. Тип зіставлення може мати значення 1, 0 або -1. Якщо аргумент має значення 1, функція MATCH поверне найбільше значення, яке менше або дорівнює шуканому значенню. Якщо аргумент має значення 0, функція MATCH поверне перше значення, яке точно дорівнює шуканому значенню. Якщо аргумент має значення -1, функція MATCH знайде найменше значення, яке більше або дорівнює вказаному шуканому значенню. Якщо тип зіставлення не вказано, програма Excel присвоїть цьому аргументу значення 1.

Нарешті, функція INDEX оперує такими аргументами: масив, номер рядка та номер стовпця в цьому масиві. Діапазон клітинок A2–A6 – це масив, функція MATCH – адреса клітинки, а останній аргумент (1) указує на те, що значення походить із першого стовпця в масиві.

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

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

У програмі 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 (і).

Обчислення середнього значення, виключаючи з розрахунку нулі

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

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

Функція IF створює масив значень, які не дорівнюють 0, а потім передає ці значення функції AVERAGE.

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

Ця формула масиву порівнює значення у двох діапазонах клітинок з назвами «Мої_дані» та «Ваші_дані» і повертає кількість відмінностей між ними. Якщо вміст двох діапазонів однаковий, формула повертає значення 0. Для цієї формули потрібно, щоб діапазони клітинок мали такий самий розмір і вимір (Наприклад, якщо діапазон клітинки «Мої_дані» має 3 рядки та 5 стовпців, клітинка «Ваші_дані» також повинна містити 3 рядки та 5 стовпців).

=SUM(IF(Мої_дані=Ваші_дані,0,1))

Формула створює новий масив такого самого розміру, що й порівнювані діапазони. Функція IF заповнює цей діапазон значеннями 0 і 1 (0 – відмінності, 1 – збіги). Після цього функція SUM повертає суму значень у діапазоні.

Формулу можна спростити так:

=SUM(1*(Мої_дані<>Ваші_дані))

Ця формула працює, тому що TRUE*1=1, а FALSE*1=0 (так само як і формула, що підраховує кількість помилок у діапазоні).

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

Ця формула масиву повертає номер рядка, який містить максимальне значення в діапазоні "Дані", що складається з одного стовпця.

=MIN(IF(Дані=MAX(Дані);ROW(Дані);""))

Функція IF створює новий масив, який відповідає діапазону "Дані". Якщо відповідна клітинка містить максимальне значення діапазону, масив міститиме номер цього рядка. В іншому разі масив міститиме пустий рядок (""). Функція MIN використовує новий масив як другий аргумент і повертає найменше значення, яке відповідає номеру рядка максимального значення в діапазоні "Дані". Якщо діапазон "Дані" містить кілька однакових максимальних значень, формула повертає рядок із першим значенням.

Щоб повернути фактичну адресу клітинки з максимальним значенням, використовуйте таку формулу:

=ADDRESS(MIN(IF(Дані=MAX(Дані);ROW(Дані);""));COLUMN(Дані))