Изтеглете примерната работна книга
Този урок ще ви запознае с формулите за динамичен масив в Excel и Google Sheets.
Въведение
През септември 2022 г. Microsoft въведе формули за динамичен масив в Excel. Тяхната цел е да улеснят писането на сложни формули и с по -малък шанс за грешка.
Формулите за динамичен масив са предназначени в крайна сметка да заменят формули за масиви, т.е.разширени формули, които изискват използването на Ctrl + Shift + Enter (CSE).
Ето кратко сравнение между формулата на масива и формулата на динамичния масив, използвани за извличане на списък с уникални отдели от нашия списък в обхвата А2: А7.
Формула на наследствения масив (CSE):
Следната формула се въвежда в клетката D2 и се въвежда, като натиснете Ctrl + Shift + Enter и го копирате надолу от D2 да се D5.
| 1 | {= IFERROR (INDEX ($ A $ 2: $ A $ 7, MATCH (0, COUNTIF ($ D $ 1: D1, $ A $ 2: $ A $ 7), 0)), "")}} |
Формула за динамичен масив:
Следващата формула е само въвеждане в клетката D2 и въведете, като натиснете Enter. От един бърз поглед можете да разберете колко лесно и директно е да напишете формула за динамичен масив.
| 1 | = УНИКАЛНО (A2: A7) |
Наличност
От август 2022 г. формулите за динамичен масив са достъпни само за потребители на Office 365.
Обхват на разлив и разлив
Формулите за динамичен масив работят, като връщат множество резултати в диапазон от клетки въз основа на една формула, въведена в една клетка.
Това поведение се нарича „Разливане“ а диапазонът от клетки, където се поставят резултатите, се нарича „Обхват на разлива“. Когато изберете някоя клетка в обхвата на разливане, Excel я подчертава с тънка синя рамка.
В примера по -долу формулата за динамичен масив ВИД е в клетката D2 и резултатите са разпръснати в диапазона D2: D7
| 1 | = СОРТИРАНЕ (A2: A7) |
Резултатите от формулата са динамични, което означава, че ако настъпи промяна в обхвата на източника, резултатите също се променят и обхватът на разлива се преоразмерява.
#СПИЛ!
Трябва да имате предвид, че ако вашият диапазон на разливане не е напълно празен, се връща грешка #SPILL.
Когато изберете грешка #SPILL, желаният обхват на разлив във формулата е подчертан с прекъсната синя рамка. Преместването или изтриването на данните в непразната клетка премахва тази грешка, което позволява формулата да се разлее.
Референтна нотация за разлив
За да се позовем на обхвата на разлив на формула, поставяме # символ след препратката към клетката на първата клетка в разлива.
Можете също така да посочите разлива, като изберете всички клетки в обхвата на разлива и препратка към разлива ще бъде създадена автоматично.
В примера по -долу бихме искали да преброим броя на служителите в нашата фирма, използвайки формулата COUNTA след като са подредени по азбучен ред, използвайки формулата за динамичен масив ВИД.
Влизаме в ВИД формула в D2, за да поръчате служителите в нашия списък:
| 1 | = СОРТИРАНЕ (A2: A7) |
След това влизаме в COUNTA формула в G2 за преброяване на броя на служителите:
| 1 | = COUNTA (D2#) |
Обърнете внимание на използването на # в D2# за справка с резултатите, разпръснати от SORT в диапазон D2: D7.
Нови формули
По -долу е пълен списък на новите формули за динамичен масив:
- ЕДИНСТВЕН ПО РОДА СИ - Връща списък с уникални стойности от диапазон
- ВИД - Сортира стойности в диапазон
- СОРТБИ - Сортира стойности въз основа на съответния диапазон
- ФИЛТЪР - Филтрира диапазон въз основа на предоставените критерии
- RANDARRAY - Връща масив от случайни числа между 0 и 1
- ПОСЛЕДОВАТЕЛНОСТ - Генерира списък с последователни числа като 1, 2, 3, 4, 5
Формула за динамичен масиве в Google Таблици
Всички горни примери работят абсолютно същите в Google Sheets, както и в Excel.
