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

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

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

Знайомство з формулами масивів

Формули масивів часто називають формулами «CSE» (Ctrl+Shift+Enter), тому що замість натискання клавіші Enter для закінчення формули, у цьому випадку необхідно натиснути комбінацію клавіш Ctrl+Shift+Enter.

Призначення формул масивів

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

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

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

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

Побіжний огляд масивів і формул масивів

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

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

Вправи

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

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

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

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

    Продавець
     

    Тип
    автомобіля

    Кількість
    проданих автомобілів

    Ціна
    за одиницю

    Загальний обсяг
    збуту

    Мороз

    Седан

    5

    33 000

    Купе

    4

    37 000

    Омельченко

    Седан

    6

    24 000

    Купе

    8

    21 000

    Єрьоменко

    Седан

    3

    29 000

    Купе

    1

    31 000

    Попкова

    Седан

    9

    24 000

    Купе

    5

    37 000

    Горноженко

    Седан

    6

    33 000

    Купе

    8

    31 000

    Формула (загальний підсумок)

    Загальний підсумок

    '=SUM(C2:C11*D2:D11)

    =SUM(C2:C11*D2:D11)

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

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

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

Якщо використовується програма Excel, переконайтеся, що Аркуш1 активовано, а потім виділіть клітинки E2:E11. Натисніть клавішу F2 і введіть формулу =C2:C11*D2:D11 у поточній клітинці (E2). Якщо натиснути клавішу Enter, формула вводиться тільки в клітинку E2, а її результат дорівнює 165 000. Після введення формули натисніть натомість клавіші Ctrl+Shift+Enter замість однієї клавіші Enter. Тепер результати відображаються в клітинках E2:E11. Зверніть увагу, що в рядку формул формула відображається як {= C2:C11*D2:D11}. Це свідчить про те, що це формула масиву, як показано в наведеній нижче таблиці.

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

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

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

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

=SUM(C2:C11*D2:D11)

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

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

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

  • Послідовність.    Якщо клацнути будь-яку клітинку в діапазоні під клітинкою Е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).

Введення та змінення формул масивів

Важливо!    Натискайте клавіші Ctrl+Shift+Enter щоразу, коли потрібно ввести або змінити формулу масиву. Це правило стосується формул для однієї та кількох клітинок.

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

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

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

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

  • Щоб видалити формулу масиву, виділіть усю формулу (наприклад, =C2:C11*D2:D11), натисніть клавішу Delete, а потім натисніть сполучення клавіш Ctrl+Shift+Enter.

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

Розширення формули масиву

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

На аркуші ми додали ще кілька рядків для продажу – з 12 по 17. І зараз необхідно оновити формули масиву, щоб вони включали також додаткові рядки.

Обов'язково виконуйте ці дії у програмі Excel для настільних комп'ютерів (після завантаження книги на комп'ютер).

Розгортання формули масиву
  1. Скопіюйте всю таблицю до клітинки A1 на аркуші Excel.

    Продавець
     

    Тип
    автомобіля

    Кількість
    проданих автомобілів

    Ціна
    за одиницю

    Загальний обсяг
    збуту

    Мороз

    Седан

    5

    33 000

    165 000

    Купе

    4

    37 000

    148 000

    Омельченко

    Седан

    6

    24 000

    144 000

    Купе

    8

    21 000

    168 000

    Єрьоменко

    Седан

    3

    29 000

    87 000

    Купе

    1

    31 000

    31 000

    Попкова

    Седан

    9

    24 000

    216 000

    Купе

    5

    37 000

    185 000

    Горноженко

    Седан

    6

    33 000

    198 000

    Купе

    8

    31 000

    248 000

    Сергієнко

    Седан

    2

    27 000

    Купе

    3

    30 000

    Климов

    Седан

    4

    22 000

    Купе

    1

    41 000

    Грачьов

    Седан

    5

    32 000

    Купе

    3

    36 000

    Загальний підсумок

  2. Виділіть клітинку E18, у клітинці А20 введіть формулу загального підсумку =SUM(C2:C17*D2:D17) і натисніть клавіші Ctrl+Shift+Enter.
    Відповідь має бути 2 131 000.

  3. Виділіть діапазон клітинок, який містить поточну формулу масиву (E2:E11), а також пусті клітинки (E12:E17), розташовані поруч із новими даними. Іншими словами, виділіть діапазон клітинок E2:E17.

  4. Натисніть клавішу F2, щоб активувати режим редагування.

  5. У рядку формул замініть C11 на C17, а D11 – на D17, а потім натисніть клавіші Ctrl+Shift+Enter.
    Програма Excel оновить формулу в діапазоні клітинок E2–E11 і додасть екземпляр формули до нових клітинок (E12–E17).

  6. У клітинці F17 ведіть формулу масиву = SUM(C2:C17*D2*D17), щоб вона стосувалася клітинок у рядках із 2-го по 17-й, і натисніть клавіші Ctrl+Shift+Enter, щоб застосувати формулу масиву.
    Загальний підсумок має бути 2 131 000.

Недоліки формул масивів

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

  • Іноді час від часу можна забути натиснути сполучення клавіш 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. Відкрийте книгу з попередніми прикладами або створіть нову.

  2. Виділіть клітинки A1 – E1.

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

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

    Цього разу ліву та праву фігурні дужки ({ }) необхідно вводити вручну.

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

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

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

  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. Натисніть ОКта виберіть рядок з трьох пустих клітинок.

  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, щоб активувати режим редагування.
    Формула масиву має все одно залишитися такою: =C8:E10.

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

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

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

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

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

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

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

Дані

Це

велика кількість клітинок, які

об'єднуються

для формування

єдиного речення.

Загальна кількість символів у клітинках A2:A6

=SUM(LEN(A2:A6))

Вміст найдовшої клітинки (A3)

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

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

=SUM(LEN(A2:A6))

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

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

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

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

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

    =SMALL(A5:A14,{1;2;3})

У клітинках А16 – А18 з’являться відповідні значення 400, 475 та 500.

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

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{1;2;3}))

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

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

  1. Виберіть клітинки з A1 по A3.

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

    =LARGE(A5:A14,ROW(INDIRECT("1:3")))

У клітинках А1 – А3 з’являться відповідно значення 3200, 2700 та 2000.

Тепер буле корисно дізнатися про функції 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(A1:A10;ROW(INDIRECT("1:3"))),  – починаючи із внутрішніх дужок. Функція INDIRECT повертає набір текстових значень, (значення 1 – 3). Функція ROW, у свою чергу, створює у стовпці масив із трьох клітинок. Функція LARGE використовує значення в діапазоні клітинок A5–A14 і обчислюється тричі (один раз для кожного посилання, яке повертає функція ROW). У масив із трьох клітинок у стовпці повертаються значення 3200, 2700 і 2000. Якщо потрібно отримати більше значень, до функції INDIRECT слід додати більший діапазон клітинок.

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

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

Ця формула працює, тільки коли діапазон даних містить один стовпець клітинок. На Аркуші3 введіть наведену нижче формулу в клітинці A16, а потім натисніть клавіші Ctrl+Shift+Enter:

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

Текст «набір клітин» відображається у клітинці А16.

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

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

(MAX(LEN( A6 : A9 ))

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

LEN( A6:A9 )

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

Нарешті, функція INDEX оперує такими аргументами: масив, номер рядка та номер стовпця в цьому масиві. Діапазон клітинок A6–A9 – це масив, функція 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(Дані))

На початок сторінки

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

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

Див. також

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

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

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

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

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

×