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

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

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

Що означає розгортання?

Примітка.: Старі формули масивів, відомі як формули застарілого масиву, завжди повертають результат із фіксованим розміром – вони завжди розгортаються в такій самій кількості клітинок. Поведінка розгортання, описана в цій статті, не застосовується до застарілих формул масивів.

Розлита означає, що формула призвела до кількох значень, і ці значення розміщуються в сусідніх клітинках. Наприклад, =SORT(D2:D11;1;-1), який сортує масив за спаданням, поверне відповідний масив із висотою 10 рядків. Але в цьому випадку потрібно ввести формулу лише у верхню ліву клітинку або клавішу F2, і вона автоматично розгорнеться до клітинки F11.

Сортування значень у клітинках D2:D11 за допомогою формули =SORT(D2:D11;1;-1)

Основні моменти

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

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

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

  • Якщо ввести формулу розгорненого масиву, якщо виділити будь-яку клітинку в області розгортання, Excel навколо діапазону розташується виділена межа. Межа зникне, якщо виділити клітинку за межами області.

    Формула масиву з виділеним діапазоном вихідних даних синьою рамкою

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

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

  • Перекривання формул. Формули масивів не можуть бути введені, якщо діапазон вихідних даних блокується. якщо таке трапиться, Excel поверне помилку #SPILL!, яка вказує на те, що виникає блокування. Якщо видалити блокування, формула розгорнеться належним чином. У наведеному нижче прикладі діапазон виводу формули перекриває інший діапазон із даними та відображається з крапками-рамками, які перекривають клітинки зі значеннями, які вказують на те, що його не можна розгортати. Видаліть заблоковані дані або скопіюйте їх до іншого розташування, і формула розгорнеться належним чином.

    Зображення #SPILL! указує на те, що результат формули масиву перекриває блокування, яке перешкоджає розлиттю.

  • Застарілі формули масивів, введені за допомогою сполучення клавіш Ctrl+Shift+Enter (CSE), і надалі підтримуються з міркувань зворотної сумісності, але не потрібно використовувати. За потреби ви можете перетворити формули застарілого масиву на формули динамічного масиву, поміщивши першу клітинку в діапазоні масиву, скопіюйте текст формули, видаліть увесь діапазон застарілого масиву, а потім повторно введіть формулу у верхню ліву клітинку. Перш ніж оновити формули застарілого масиву до динамічних формул масивів, слід пам'ятати про деякі відмінності обчисленьміж ними.

  • Excel має обмежену підтримку перенесення динамічних масивів між книгами, і цей сценарій підтримується, лише якщо відкрито обидві книги. Якщо закрити вихідну книгу, формули пов’язаного динамічного масиву повертатимуть помилку #REF! під час оновлення.

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

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

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

Функція FILTER

Функція RANDARRAY

Функція SEQUENCE

Функція SORT

Функція SORTBY

Функція UNIQUE

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

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

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

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

Чи були ці відомості корисні?

Наскільки ви задоволені якістю перекладу?

Що вплинуло на ваші враження?

Маєте ще один відгук? (Необов'язково)

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

×