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

Формулите на Excel, които връщат набор от стойности, известен също като масив, връщат тези стойности в съседни клетки. Това поведение се нарича преливане.

Формулите, които могат да връщат масиви с променлив размер, се наричат формули за динамични масиви . Формулите, които в момента връщат масиви, които преливат успешно, могат да се наричат формули за преливащи масиви .

Следват няколко бележки, които ще ви помогнат да разберете и използвате тези типове формули.

Какво означава преливане?

Забележка: По-старите формули за масиви, известни като формули за наследен масив, винаги връщат резултат с фиксиран размер – те винаги преливат в един и същ брой клетки. Поведението на преливане, описано в тази тема, не се отнася за наследените формули за масиви.

Преливането означава, че дадена формула е довела до множество стойности и тези стойности са поставени в съседните клетки. Например =SORT(D2:D11,1,-1), която сортира масив в низходящ ред, ще върне съответен масив с височина 10 реда. Но вие трябва само да въведете формулата в горната лява клетка, в случая F2, и тя автоматично ще прелее до клетка F11.

Сортиране на стойностите в клетки D2:D11 с =SORT(D2:D11;1;-1)

Основни точки

Когато натиснете Enter, за да потвърдите формулата си, Excel динамично ще оразмери изходния диапазон вместо вас и ще постави резултатите във всяка клетка в рамките на този диапазон.
Ако пишете формула за динамичен масив за действие върху списък с данни, може да е полезно да я поставите в таблица на Excel, след което използвайте структурирани препратки за препратка към данните. Това е така, защото структурираните препратки автоматично се настройват при добавяне или премахване на редове от таблицата.
Преливащите формули за масиви не се поддържат в самите таблици на Excel, така че трябва да ги поставите в мрежата извън таблицата. Таблиците са най-подходящи за задържане на редове и колони с независими данни.
След като въведете формула за преливащ масив, когато изберете произволна клетка в областта за преливане, Excel ще постави осветена граница около диапазона. Границата ще изчезне, когато изберете клетка извън областта.
Само първата клетка в областта за преливане може да се редактира. Ако изберете друга клетка в областта за преливане, формулата ще се вижда в лентата за формули, но текстът е „призрачен“ и не може да бъде променен. Ако е необходимо да актуализирате формулата, трябва да изберете клетката горе вляво в диапазона на масива, да я промените, както е необходимо, след което Excel автоматично ще актуализира останалата част от областта за преливане вместо вас, когато натиснете Enter.
Формулата се припокрива – формулите за масиви не могат да бъдат въведени, ако има нещо, което блокира изходния диапазон. И ако това се случи, Excel ще върне грешка #ПРЕЛИВАНЕ!, показваща, че има блокиране. Ако премахнете блокирането, формулата ще прелее според очакванията. В примера по-долу изходният диапазон на формулата припокрива друг диапазон с данни и се показва с пунктирана граница, припокриваща клетки със стойности, показващи, че не може да прелее. Премахнете блокиращите данни или ги копирайте някъде другаде и формулата ще прелее според очакванията.
Формулите за наследени масиви, въведени чрез CTRL+SHIFT+ENTER (CSE), все още се поддържат от съображения за обратна съвместимост, но вече не трябва да се използват. Ако искате, можете да конвертирате формулите за наследени масиви във формули за динамични масиви, като намерите първата клетка в диапазона на масива, копирате текста на формулата, изтриете целия диапазон на наследения масив и след това въведете отново формулата в горната лява клетка. Преди да надстроите формулите за наследени масиви до формули за динамични масиви, трябва да сте наясно с някои разлики в пресмятането между двете.
Excel има ограничена поддръжка за динамични масиви между работни книги и този сценарий се поддържа само когато и двете работни книги са отворени. Ако затворите работната книга източник, всички свързани формули за динамични масиви ще връщат #REF! грешка , когато бъдат обновени.