Приклади формул масивів і рекомендації
Applies ToExcel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Excel для iPad Excel для iPhone

Формула масиву – це формула, яка може виконувати кілька обчислень над одним або кількома елементами масиву. Масив можна розглядати як рядок чи стовпець значень або комбінацію рядків і стовпців значень. Формули масивів можуть повертати один чи кілька результатів.

Починаючи з оновлення для Microsoft 365 за вересень 2018 р, будь-яка формула, яка може повертати кілька результатів, автоматично переносить їх униз або в сусідні клітинки. Ця зміна поведінки також супроводжується кількома новими функціями динамічного масиву. Формули динамічного масиву, незалежно від того, чи використовують вони наявні функції або функції динамічного масиву, необхідно вводити лише в одну клітинку, а потім підтверджувати за допомогою клавіші Enter. Раніше в застарілих формулах масиву спочатку потрібно було вибрати весь вихідний діапазон, а потім підтвердити формулу за допомогою комбінації клавіш Ctrl+Shift+Enter. Їх часто називають CSE-формулами.

За допомогою формул масивів можна виконувати складні завдання, зокрема:

  • швидко створити зразки наборів даних;

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

  • підсумувати лише ті числа, які відповідають певним умовам (наприклад, найменші значення діапазону або значення, які перебувають між верхньою та нижньою межами);

  • підсумувати кожне n-е значення в діапазоні.

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

Завантаження прикладів

Завантажте зразок книги з усіма прикладами формул масиву з цієї статті.

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

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

    Функція масиву для кількох клітинок у клітинці H10 =F10:F19*G10:G19 для визначення кількості автомобілів, проданих за ціною за одиницю

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

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

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

    Формула масиву для однієї клітинки, яка обчислює загальний підсумок: =SUM(F10:F19*G10:G19)

    У клітинці 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(1,5) або ={1,2,3,4,5}

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

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

    Створення вертикальної константи-масиву за допомогою функції =SEQUENCE(5) або ={1;2;3;4;5}

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

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

    Створення вертикальної константи-масиву за допомогою функції =SEQUENCE(3) або ={4;3,4;3;4;5}

    Ви також можете ввести: або={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.

Використання констант-масивів у формулах. У цьому прикладі використано формулу =SUM(D9:H(*SEQUENCE(1,5))

Функція 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.

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

Використовуйте іменовану константу-масив у формулі, наприклад =Quarter1, де "Квартал1" визначено як ={"Січень","Лютий","Березень"}

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

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

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

Використовуйте поєднання функцій TEXT, DATE, YEAR, TODAY і SEQUENCE, щоб створити динамічний список із 12 місяців

Функцію 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.

    Формула масиву Excel для пошуку N-го найменшого значення: =SMALL(B9#,SEQUENCE(D9))

    Введіть =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(Data),"",Data) підсумує діапазон з іменем Data, навіть якщо він містить помилки, наприклад #VALUE! або #NA!.

  • =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(Sales>0,Sales)) підсумує всі значення, більші за 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(Дані))

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

Підтвердження

Частини цієї статті створено на основі низки дописів "Досвідчений користувач Excel" Коліна Уілкокса (Colin Wilcox) і адаптованих розділів 14 і 15 книги Формули Excel 2002 Джона Уолкенбаха (John Walkenbach), колишнього фахівця MVP з Excel.

Потрібна додаткова довідка?

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

Додаткові відомості

Поведінка розгорнутих і динамічних масивів

Формули динамічного масиву та застарілі формули масиву CSE

Функція FILTER

Функція RANDARRAY

Функція SEQUENCE

Функція SORT

Функція SORTBY

Функція UNIQUE

#РОЗГОРТАННЯ! в Excel

Оператор неявного перетину: @

Огляд формул у програмі Excel

Потрібна додаткова довідка?

Потрібні додаткові параметри?

Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.