Формули Excel, які повертають набір значень, також відомий як масив, повертають ці значення до сусідніх клітинок. Така поведінка називається розливом.
Формули, які можуть повертати масиви змінного розміру, називаються формулами динамічного масиву. Формули, які наразі повертають масиви, що успішно розливаються, можна називати формулами розлитого масиву.
Нижче наведено деякі нотатки, які допоможуть зрозуміти й використовувати формули такого типу.
Що означає розлив?
Примітка.: Старі формули масиву, відомі як застарілі формули масиву, завжди повертають результат фіксованого розміру– вони завжди вливаються в ту саму кількість клітинок. Описана в цій статті поведінка розгортання не застосовується до застарілих формул масивів.
Розлив означає, що формула дає кілька значень, і ці значення було розміщено в сусідніх клітинках. Наприклад, =SORT(D2:D11;1;-1), яка сортує масив за спаданням, повертає відповідний масив із висотою 10 рядків. Але в цьому випадку потрібно ввести формулу лише у верхній лівій клітинці або F2, і вона автоматично розіллється до клітинки F11.
Ключові моменти
-
Якщо натиснути клавішу Enter, щоб підтвердити формулу, Excel динамічно змінюватиме розмір діапазону виводу та поміщатиме результати в кожну клітинку в межах цього діапазону.
-
Якщо ви створюєте формулу динамічного масиву для роботи зі списком даних, її можна розмістити в таблиці Excel, потім використати структуровані посилання, щоб посилатися на дані. Це пов’язано з тим, що структуровані посилання автоматично налаштовуються під час додавання або видалення рядків із таблиці.
-
Формули розлитого масиву не підтримуються в таблицях Excel самі по собі, тому їх слід розташувати в сітці за межами таблиці. Таблиці найкраще підходять для рядків і стовпців незалежних даних.
-
Після введення формулу розлитого масиву, коли ви виберете будь-яку клітинку в області розгортання, Excel розмістить виділену межу навколо діапазону. Межа зникне, якщо вибрати клітинку за межами області.
-
Редагувати можна лише першу клітинку в області розливу. Якщо вибрати іншу клітинку в області розливу, формула відображатиметься в рядку формул, але текст буде "примарний", і його не можна буде змінити. Якщо потрібно оновити формулу, виділіть верхню ліву клітинку в діапазоні масиву, за потреби змініть її, після натискання клавіші Enter Excel автоматично оновить решту області розливу.
-
Формула перекривається. Формули масивів не можуть вводитися, якщо діапазон виводу щось блокує. У такому разі Excel поверне помилку #РОЗГОРТАННЯ!, яка вказує на блокування. Якщо видалити блокування, формула розіллється належним чином. У наведеному прикладі діапазон виводу формули перекриває інший діапазон із даними та відображається з пунктирною межею, що перекриває клітинки зі значеннями і вказує на те, що його не можна розгорнути. Видаліть дані, що блокують діапазон, або скопіюйте їх до іншого розташування, і формула розіллється належним чином.
-
Застарілі формули масиву, введені за допомогою CTRL+SHIFT+ENTER (CSE), усе ще підтримуються з міркувань сумісності, але їх краще не використовувати. За потреби ви можете перетворити застарілі формули масиву на формули динамічного масиву наступним чином.Виберіть першу клітинку в діапазоні масиву, скопіюйте текст формули, видаліть весь діапазон застарілого масиву, а потім знову введіть формулу у верхній лівій клітинці. Перш ніж оновлювати застарілі формули масиву до формул динамічного масиву, слід пам’ятати про деякі відмінності обчислення між ними.
-
Excel має обмежену підтримку перенесення динамічних масивів між книгами, і цей сценарій підтримується, лише якщо відкрито обидві книги. Якщо закрити вихідну книгу, формули пов’язаного динамічного масиву повертатимуть помилку #REF! під час оновлення.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
