Dynamiske matricer

Dynamiske matricer gør det let at arbejde med flere værdier i en enkelt formel. Denne dynamiske opførsel betyder at – anderledes end klassiske matrixformler – resultatet tilpasser sig: hvis størrelse på input-matricen eller en funktions argumenter ændres, så vil størrelsen af resultat-matricen også ændres. Det er ikke nødvendigt at skrive matrixformlen ind på ny, når input- og output-data vokser eller skrumper.

Dynamic arrays are produced by the results of the functions FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY, UNIQUE, XLOOKUP, XMATCH.

These functions return an array of data automatically, so there is no need to enter them using the Ctrl+Shift+Enter keyboard shortcut.

Dynamic array spilling

The dynamic array is said to spill in the spreadsheet when data is inserted as the result of the above functions. You can reference the resulting array data by writing the reference of the first element of the resulting array suffixed by the spill range symbol #.

• Range A2:A11 is the source data.

• Range B2:B11 is a dynamic array spilled as result of the SORT function. Every cell in B2:B11 has formula =SORT(A2:A11).

• Range C2:C11 is a dynamic array referenced the # spill suffix to the range B2:B11. Every cell in the range C2:C11 has formula =B2#

• Cell D2 shows reference error #REF! since range A2:A11 is not a dynamic array.

• Range E2:E11 is also a dynamic array referencing another dynamic array.