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

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

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

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

Починаючи з оновлення за вересень 2018 для Office 365, будь-яка формула, яка може повертати кілька результатів, автоматично проллється їх, або через сусідні клітинки. Ця зміна поведінки також супроводжується кількома новими динамічними функціями масиву. Динамічні формули масивів, незалежно від того, чи вони використовують наявні функції або динамічні функції масиву, потрібно вводити в одну, а потім підтвердити, натиснувши клавішу ввід. Раніше у попередніх версіях масиву потрібно спочатку вибрати весь діапазон виведення, а потім підтвердити формулу за допомогою сполучення клавіш CTRL + SHIFT + "введіть". Вони зазвичай називаються формулами CSE .

Формули масиву можна використовувати для виконання складних завдань, наприклад:

  • Швидко створюйте зразки наборів даних.

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

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

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

У наведених нижче прикладах показано, як створити кілька формул масивів із кількома клітинками та окремими клітинками. Якщо це можливо, ми додали приклади деяких динамічних функцій масиву, а також наявні формули масивів, введені як динамічні та успадковані масиви.

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

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

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

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

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

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

    Під час натискання клавіші вводувідобразяться результати, наведені в розділі клітинки H10: H19. Зверніть увагу, що діапазон розливу виділяється рамкою, якщо вибрати будь-яку з них у діапазоні розливу. Ви також можете помітити, що формули в клітинках H10: H19 затінено сірим кольором. Вони просто існують для довідки, тому, якщо потрібно налаштувати формулу, потрібно вибрати клітинку H10, де живе зразок формули.

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

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

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

    У цьому випадку програма 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, формули динамічного масиву автоматично змінюють розмір під час додавання або видалення даних.

    • #SPILL! помилка    Динамічні масиви представили #SPILL! помилка, що вказує на те, що призначений діапазон розливу буде заблоковано з якоїсь причини. Коли ви розв'яже блокада, формула буде автоматично проливати.

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

= {1, 2, 3, 4, 5} або = {"Січень", "Лютий", "Березень"}

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

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

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

    Відкрийте книгу з попередніми прикладами або створіть нову. Виділіть будь-яку пусту клітку та наберіть = послідовність (1; 5). Функція ПОСЛІДОВНОСТІ будує 1 рядок на 5-му стовпчастій сторінці так само, як = {1, 2, 3, 4, 5}. З'явиться такий результат:

    Створення горизонтального масиву констант зі = ПОСЛІДОВНІСТЮ (1,5) або = {1, 2, 3, 4, 5}

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

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

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

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

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

    Створення 3-го рядка на 4-му масиві констант з = ПОСЛІДОВНІСТЮ (3; 4)

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

    Як можна побачити, параметр «ПОСЛІДОВНІСТЬ» пропонує значні переваги в разі введення значень констант для масиву. У першу чергу, він заощаджує час, але також може допомогти зменшити помилки в ручному записі. Крім того, простіше читати, особливо як двокрапку, щоб відрізнити роздільники від коми, можна важко розрізнити.

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

У клітинці D9 введено = послідовність (1; 5; 3; 1), але також можна ввести 3, 4, 5, 6 і 7 у клітинках A9: H9. Для цього певного номера немає нічого особливого, ми просто вибрали щось інше, ніж 1-5 для диференціації.

У стільниковому E11; (у клітинках) вкажіть формулу = SUM (D9: H9 * порядковий (1,5))або = SUM (D9: H9 * {1; 2; 3; 4; 5}). У формулах повернуто 85.

Використання констант масивів у формулах. У цьому прикладі використано функцію = SUM (D9: H (* ПОРЯДКОВИЙ (1,5))

Функція ПОСЛІДОВНОСТІ будує еквівалент констант масиву {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 (послідовність (1; 5; 3; 1) * послідовність (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 з’явиться попередження. Крім цього, числові значення не можуть містити коми, дужки, знаки відсотка та долара.

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

Перейдіть на сторінку формули _ Gt_визначені імена _ gt/_ Визначте ім'я. У полі ім'я введіть квартал1. У полі Посилання введіть таку константу (фігурні дужки додаються вручну):

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

Тепер діалогове вікно має виглядати так:

Додавання іменованого масиву констант із формул _ Gt_ визначені імена _ Gt_name Manager _ gt/New

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

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

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

Якщо ви хочете, щоб результат розлито вертикально, а не по горизонталі, можна скористатися =транспонувати(квартал1).

Якщо потрібно відобразити список 12 місяців, наприклад, коли ви створюєте фінансовий звіт, ви можете створити один із цих поточних місяців за допомогою функції ПОСЛІДОВНОСТІ. Охайні відомості про цю функцію є те, що навіть якщо відображається лише місяць, існує дійсна дата, за допомогою якої можна використовувати інші обчислення. Ці приклади наведено на аркушах " іменований масив констант " і " швидкий зразок даних " у прикладі книги.

= TEXT (дата (рік (сьогодні ()), ПОСЛІДОВНІСТЬ (1, 12); 1); "МММ")

Створення динамічного списку на 12 місяців за допомогою комбінації функцій ТЕКСТУ, дати, року, сьогодні та ПОСЛІДОВНОСТІ.

Ця функція використовує функцію Date , щоб створити дату на основі поточного року, послідовність створює масив констант від 1 до 12 за січень до грудня, а функція TEXT перетворює формат відображення на "МММ" (січень, Лютий, Mar тощо). Якщо ви хочете відобразити повне ім'я місяця, наприклад у січні, ви використовуєте "mmmm".

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

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

  • Кілька елементів у масиві

    Наберіть = послідовність (1, 12) * 2або = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} * 2

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

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

    = Послідовність (1, 12) ^ 2або = {1, 2, 3, 4; 5; 6; 7; 8; 9, 10, 11, 12} ^ 2

  • Визначення квадратного кореня елементів із квадратними елементами в масиві

    Наберіть =SQRT(послідовність (1, 12) ^ 2)або = SQRT ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2)

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

    Виберіть = транспонувати (послідовність (1; 5))або = транспонувати ({1, 2, 3, 4, 5})

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

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

    Наберіть = транспонувати (послідовність (5; 1))або = транспонувати ({1; 2; 3; 4; 5})

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

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

    Укажіть = транспонувати (послідовність (3; 4))або = транспонувати ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12})

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

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

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

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

    Наберіть = послідовність (3, 6, 10, 10)або = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

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

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

    Використання оператора діапазону пролив (#) для посилання на наявний масив

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

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

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

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

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

    = SUM (LEN (C9: C13))

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

    = СЕРЕДНЄ (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) вказує на те, що значення походить від першого стовпця в масиві.

    Якщо ви хочете отримати вміст найменшого текстового рядка, ви замінюєте Макс у наведеному вище прикладі на min.

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

    У цьому прикладі показано, як знайти три найменших значення в діапазоні клітинок, де масив зразків даних у клітинках B9: B18has створено з: = Int (rarмасиву(10; 1) * 100). Зверніть увагу, що функція RARМАСИВ – це летючі функції, тож ви отримаєте новий набір випадкових чисел щоразу, коли програма Excel обчислює.

    Формула масиву Excel для пошуку значення n-найменше: = мала (B9 #, ПОСЛІДОВНІСТЬ (D9))

    Наберіть = маленький (B9 #, послідовність (D9), = малого (B9: B18; {1; 2; 3})

    Ця формула використовує константу масиву для обчислення невеликої функції тричі та Повертає найменше 3 членів масиву, що міститься в клітинках B9: B18, де 3 – це змінна величина у клітинці D9. Щоб отримати додаткові значення, можна збільшити значення в функції ПОСЛІДОВНОСТІ або додати більше аргументів до константи. З цією формулою також можна використовувати додаткові функції, зокрема SUM або AVERAGE. Наприклад:

    = SUM (МАЛИЙ (B9 #, ПОСЛІДОВНІСТЬ (D9))

    = СЕРЕДНЄ (МАЛА (B9 #, ПОСЛІДОВНІСТЬ (D9))

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

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

    Наберіть = LARGE (B9 #, Row (непрямі ("1:3"))) або = LARGE (B9: B18, Row (непрямі ("1:3")) )

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

    =ROW(1:10)

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

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

    Функція "непрямі" використовує текстові рядки як аргументи (тому діапазон 1:10 оточений лапками). Програма Excel не змінюватиме текстові значення після вставлення рядків або інших переміщень формули масиву. У результаті функція ROW завжди створюватиме масив потрібних цілих чисел. Можна так само легко використовувати ПОСЛІДОВНІСТЬ:

    = ПОСЛІДОВНІСТЬ (10)

    Розгляньмо формулу, яку ви використовували раніше – = LARGE (B9 #, ROW (непрямі ("1:3"))) – починаючи з внутрішніх дужок і роботи назовні: функція "непрямі" повертає набір текстових значень, у цьому випадку значення від 1 до 3. Функція ROW в області перетворити створює масив із трьома клітинками. Функція LARGE використовує значення в діапазоні B9: B18, і він обчислюється тричі, один раз для кожного посилання, повернутого функцією ROW. Якщо потрібно отримати додаткові значення, додайте до неї більший діапазон. Нарешті, як і в НЕВЕЛИКИХ прикладах, ви можете використовувати цю формулу з іншими функціями, такими як SUM і СЕРЕДНЄ.

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

    Функція SUM у програмі Excel не працює, коли ви намагаєтеся підсумувати діапазон, що містить значення помилки, наприклад #VALUE! або #N/A. У цьому прикладі показано, як підсумувати значення в діапазоні «дані», що містить помилки:

    Використовуйте масиви, щоб впоратися з помилками. Наприклад, = SUM (IF (ISERROR (дані), "", 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; збут)) буде підсумовувати всі значення, більші за 0 в діапазоні "Збут".

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

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

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

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

= SUM ((Sales>0) * (Sales<2500) * (продажі))

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

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

= SUM (IF ((Sales>0) + (Sales<2500); збут))

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

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

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

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

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

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

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

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

= SUM (1 * (MyData<>YourData))

Ця формула працює, тому що 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 + клавіша вводу.

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

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

підсумки у стовпці e обчислені за формулою масиву

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

У розділі "стільниковий D13 книги" введіть таку формулу, а потім натисніть клавіші CTRL + SHIFT + ВВІД:

=SUM(C2:C11*D2:D11)

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

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

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

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

  • Безпека.    Компонент формули масиву для кількох клітинок не можна змінити. Наприклад, клацніть елемент стільниковий E3 та натисніть клавішу DELETE. Потрібно вибрати увесь діапазон клітинок (Е2 – Е11) і змінити формулу для всього масиву або залишити його без змін. Для додаткової міри безпеки потрібно натиснути сполучення клавіш CTRL + SHIFT + "", щоб підтвердити будь-які зміни, внесені до формули.

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

У загальному разі формули масивів використовують стандартний синтаксис формули. Усі вони починаються з знака рівності (=), і ви можете використовувати більшість вбудованих функцій програми Excel у формулах масивів. Ключова різниця полягає в тому, що під час використання Формули масиву ви натискаєте сполучення клавіш CTRL + SHIFT + "клавішу ", щоб увести формулу. Після цього програма 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).

Увага!: Щоб увести формулу масиву, натисніть клавіші CTRL + SHIFT + клавішу вводу . Це правило стосується формул для однієї та кількох клітинок.

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

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

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

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

  • Щоб видалити формулу масиву, виділіть весь діапазон формул (наприклад, E2: e11;), а потім натисніть клавішу DELETE.

  • Не можна вставляти пусті клітинки або видаляти клітинки з Формули масиву з кількома клітинками.

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

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

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

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

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

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

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

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

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

Нижче наведено масив в одному рядку: {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 + "введіть":

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

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

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

    Константа горизонтального масиву у формулі

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

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

  2. У рядку формул введіть таку формулу, а потім натисніть сполучення клавіш CTRL + SHIFT + "введіть":

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

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

    константа вертикального масиву у формулі масиву

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

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

  2. У рядку формул введіть таку формулу, а потім натисніть сполучення клавіш CTRL + SHIFT + "введіть":

    ={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 + ВВІД:

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

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

    Формула масиву з константою масиву

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

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

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

синтаксис формули масиву з константою масиву

1. Функція

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

3. Оператор

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

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

Оскільки програма 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 + "введіть". Відобразиться той самий результат, що й у попередній вправі, у якій використовувалася така формула масиву:

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

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

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

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

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

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

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

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

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

    діалогове вікно «нове ім’я» з формулою

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

  5. Введіть таку формулу, а потім натисніть клавіші CTRL + SHIFT + ВВІД.

    =Квартал1

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

    іменований масив, введений як формула

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

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

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

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

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

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

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

  2. Введіть таку формулу, а потім натисніть клавіші CTRL + SHIFT + ВВІД:

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

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

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

  2. Введіть наведений нижче формулу масиву, а потім натисніть сполучення клавіш CTRL + SHIFT + ВВІД:

    ={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 + ВВІД:

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

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

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

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

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

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

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

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

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

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

    =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 + "введення", яка вводить цей масив чисел у діапазоні C8: E10 за допомогою формули масиву. На аркуші клітинки C8–E10 мають виглядати так:

    10

    20

    30

    40

    50

    60

    70

    80

    90

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

  4. У рядку формул введіть таку формулу, а потім натисніть сполучення клавіш CTRL + SHIFT + введіть:

    =C8:E10

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

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

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

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

  3. Натисніть сполучення клавіш CTRL + SHIFT + "клавішу", щоб вставити константу масиву як формулу масиву.

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

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

  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 + "введіть ", щоб побачити загальну кількість символів у клітинках a2: A6 (66).

  3. Виділіть клітинку A10, а потім натисніть сполучення клавіш CTRL + SHIFT + "введіть ", щоб побачити вміст найдовших клітинок a2: A6 (клітинка a3).

Наведена нижче формула використовується в клітинці A8 обчислює загальну кількість символів (66) у клітинках від a2 до A6.

=SUM(LEN(A2:A6))

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

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

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

  1. Укажіть деякі випадкові числа у клітинках a1: A11.

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

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

    = МАЛІ (A1: A11; {1; 2; 3})

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

= SUM (МАЛИЙ (A1: A10; {1; 2; 3})

= СЕРЕДНЄ (МАЛЕ (A1: A10; {1; 2; 3})

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

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

  1. Виберіть клітинки D1 через D3.

  2. У рядку формул введіть цю формулу, а потім натисніть сполучення клавіш CTRL + SHIFT + введіть:

    = LARGE (A1: A10; РЯДОК (НЕПРЯМІ ("1:3")))

Тепер буле корисно дізнатися про функції ROW та INDIRECT. Функція ROW використовується для створення масиву послідовних цілих чисел. Наприклад, щоб вибрати пусту стовпець із 10 клітинок у книзі "практика", введіть цю формулу масиву, а потім натисніть клавіші CTRL + SHIFT + введіть:

=ROW(1:10)

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

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

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

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

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

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

Поверніться до прикладу попереднього текстового рядка, у пустій і натисніть клавіші CTRL + SHIFT + клавіша вводу:

=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 * (MyData<>YourData))

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

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

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

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

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

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

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

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

Частина цієї статті базувалося на низці стовпців Excel Power User, написаних Колін Уілкокс, і адаптований з глав 14 і 15 у програмі Excel 2002, книгу, написану Джоном Walkenbach, колишнім MVP Excel.

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

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

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

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

Динамічні формули масивів із успадкованими формулами масиву CSE

Функція FILTER

Функція RANDARRAY

Функція SEQUENCE

Функція SINGLE

Функція SORT

Функція SORTBY

Функція UNIQUE

Помилки #SPILL! в Excel

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

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×