Download het voorbeeldwerkboek
Deze tutorial geeft je een introductie tot dynamische matrixformules in Excel en Google Spreadsheets.
Invoering
In september 2022 introduceerde Microsoft Dynamic Array Formulas in Excel. Hun doel is om het schrijven van complexe formules gemakkelijker te maken en met minder kans op fouten.
Dynamische matrixformules zijn bedoeld om uiteindelijk matrixformules te vervangen, d.w.z. geavanceerde formules die het gebruik van Ctrl + Shift + Enter (CSE) vereisen.
Hier is een snelle vergelijking tussen de matrixformule en de dynamische matrixformule die worden gebruikt om een lijst met unieke afdelingen uit onze lijst in bereik te halen A2:A7.
Legacy array-formule (CSE):
De volgende formule wordt in de cel ingevoerd: D2 en wordt ingevoerd door op Ctrl + Shift + Enter te drukken en het naar beneden te kopiëren van D2 tot D5.
| 1 | {=IFERROR(INDEX($A$2:$A$7, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$7), 0)), "")} |
Dynamische matrixformule:
De volgende formule wordt alleen in de cel ingevoerd: D2 en ingevoerd door op Enter te drukken. In één oogopslag ziet u hoe gemakkelijk en ongecompliceerd het is om een Dynamic Array Formula te schrijven.
| 1 | =UNIEK(A2:A7) |
Beschikbaarheid
Vanaf augustus 2022 zijn Dynamic Array Formulas alleen beschikbaar voor Office 365-gebruikers.
Morsen en morsen bereik
Dynamische matrixformules werken door meerdere resultaten terug te sturen naar een celbereik op basis van een enkele formule die in één cel is ingevoerd.
Dit gedrag wordt aangeduid als “morsen” en het bereik van cellen waar de resultaten worden geplaatst, wordt de . genoemd "Verspillingsbereik". Wanneer u een cel binnen het overloopbereik selecteert, markeert Excel deze met een dunne blauwe rand.
In het onderstaande voorbeeld is de dynamische matrixformule SOORT is in cel D2 en de resultaten zijn in het bereik gemorst D2:D7
| 1 | =SORTEREN(A2:A7) |
De resultaten van de formule zijn dynamisch, wat betekent dat als er een verandering optreedt in het bronbereik, de resultaten ook veranderen en het overloopbereik wordt aangepast.
#SPIL!
Houd er rekening mee dat als uw Spill Range niet helemaal leeg is, er een #SPILL-fout wordt geretourneerd.
Wanneer u de fout #SPILL selecteert, wordt het gewenste morsbereik van de formule gemarkeerd met een blauwe stippellijn. Als u de gegevens in de niet-lege cel verplaatst of verwijdert, wordt deze fout verwijderd, waardoor de formule kan worden gemorst.
Notatie gemorste referentie
Om te verwijzen naar het morsbereik van een formule, plaatsen we de # symbool na de celverwijzing van de eerste cel in de lekkage.
U kunt ook naar de lekkage verwijzen door alle cellen in het overloopbereik te selecteren en er wordt automatisch een verwijzing naar de overloop gemaakt.
In het onderstaande voorbeeld willen we het aantal werknemers in ons bedrijf tellen met behulp van de formule: AANTAL nadat ze alfabetisch zijn geordend met behulp van de dynamische matrixformule SOORT.
We gaan de binnen SOORT formule in D2 om de medewerkers in onze lijst te bestellen:
| 1 | =SORTEREN(A2:A7) |
We gaan dan de AANTAL formule in G2 om het aantal werknemers te tellen:
| 1 | =AANTAL.(D2#) |
Let op het gebruik van # in D2# om te verwijzen naar de resultaten die zijn gemorst door SORTEREN in bereik D2:D7.
Nieuwe formules
Hieronder vindt u de volledige lijst van de nieuwe dynamische matrixformules:
- UNIEK - Retourneert een lijst met unieke waarden uit een bereik
- SOORT - Sorteert waarden in een bereik
- SORTEER OP - Sorteert waarden op basis van een bijbehorend bereik
- FILTER - Filtert een bereik op basis van de opgegeven criteria
- RANDARRAY - Retourneert een array van willekeurige getallen tussen 0 en 1
- VOLGORDE - Genereert een lijst met opeenvolgende nummers zoals 1, 2, 3, 4, 5
Dynamische matrixformules in Google Spreadsheets
Alle bovenstaande voorbeelden werken in Google Spreadsheets precies hetzelfde als in Excel.
