Ladda ner exempelarbetsboken
Denna handledning ger dig introduktion till dynamiska matrisformler i Excel och Google Sheets.
Introduktion
I september 2022 introducerade Microsoft Dynamic Array Formulas till Excel. Deras syfte är att göra det lättare att skriva komplexa formler och med mindre risk för fel.
Dynamiska matrisformler är avsedda att så småningom ersätta matrisformler, dvs avancerade formler som kräver användning av Ctrl + Shift + Enter (CSE).
Här är en snabb jämförelse mellan Array Formula och Dynamic Array Formula som används för att extrahera en lista med unika avdelningar från vår lista i intervall A2: A7.
Legacy Array Formula (CSE):
Följande formel anges i cellen D2 och skrivs in genom att trycka på Ctrl + Skift + Retur och kopiera ner den från D2 till D5.
| 1 | {= IFERROR (INDEX ($ A $ 2: $ A $ 7, MATCH (0, COUNTIF ($ D $ 1: D1, $ A $ 2: $ A $ 7), 0)), "")} |
Dynamisk matrisformel:
Följande formel är endast inmatad i cellen D2 och skrivs in genom att trycka på Enter. Från en snabb blick kan du se hur enkelt och rakt fram det är att skriva en Dynamic Array Formula.
| 1 | = UNIK (A2: A7) |
Tillgänglighet
Från och med augusti 2022 är Dynamic Array Formulas endast tillgängliga för Office 365 -användare.
Spill och spillområde
Dynamiska matrisformler fungerar genom att returnera flera resultat till ett cellintervall baserat på en enda formel i en cell.
Detta beteende kallas “Spills” och cellintervallet där resultaten placeras kallas “Spillområde”. När du väljer någon cell inom spillområdet markerar Excel den med en tunn blå kant.
I exemplet nedan, formeln för dynamisk matris SORTERA är i cellen D2 och resultaten har spillts i intervallet D2: D7
| 1 | = SORT (A2: A7) |
Resultaten av formeln är dynamiska, vilket innebär att om en förändring sker i källområdet, ändras resultaten också och spillområdet ändras.
#SPILL!
Du bör notera att om ditt spillområde inte är helt tomt returneras ett #SPILL -fel.
När du väljer #SPILL -felet markeras formelns önskade spillområde med en streckad blå kant. Om du flyttar eller tar bort data i den icke-tomma cellen tas detta fel bort så att formeln kan spillas.
Spillreferensnotation
För att referera till en formels spillområde placerar vi # symbol efter cellreferensen för den första cellen i utsläppet.
Du kan också referera till spill genom att markera alla celler i spillområdet och en referens till spill skapas automatiskt.
I exemplet nedan vill vi räkna antalet anställda i vårt företag med hjälp av formeln COUNTA efter att de har ordnats alfabetiskt med hjälp av den dynamiska matrisformeln SORTERA.
Vi går in i SORTERA formel i D2 för att beställa de anställda i vår lista:
| 1 | = SORT (A2: A7) |
Vi går sedan in i COUNTA formel i G2 att räkna antalet anställda:
| 1 | = COUNTA (D2#) |
Notera användningen av # i D2# för att referera till resultaten som SORT spillde i intervall D2: D7.
Nya formler
Nedan är hela listan över de nya Dynamic Array -formlerna:
- UNIK - Returnerar en lista med unika värden från ett intervall
- SORTERA - Sorterar värden i ett intervall
- SORTERA EFTER - Sorterar värden baserat på ett motsvarande intervall
- FILTRERA - Filtrerar ett intervall baserat på de angivna kriterierna
- RANDARRAY - Returnerar en rad slumpmässiga tal mellan 0 och 1
- SEKVENS - Genererar en lista med sekventiella nummer som 1, 2, 3, 4, 5
Dynamisk matrisformelfinns i Google Kalkylark
Alla ovanstående exempel fungerar exakt likadant i Google Kalkylark som i Excel.
