Dynamiske arrays er den største ændring af Excel-formler i årevis. Måske den største forandring nogensinde. Dette skyldes, at dynamiske arrays nemt lader dig arbejde med flere værdier på samme tid i en formel. Denne artikel giver et overblik med flere eksempler.
Den vigtigste fordel ved dynamiske arrays er evnen til at arbejde med flere værdier på samme tid i en formel. Dette er en stor opgradering og velkommen forandring. Dynamiske arrays løser nogle virkelig svære problemer i Excel og vil fundamentalt ændre den måde, regneark er designet på. Når du først har set, hvordan de fungerer, vil du aldrig vende tilbage.
Tilgængelighed
Dynamiske arrays og de nye funktioner nedenfor er kun tilgængelige Excel 365 og Excel 2021. Excel 2019 og tidligere tilbyder ikke dynamiske array-formler. For nemheds skyld bruger jeg “dynamiske arrays” (Excel 365) og “tidligere Excel” (2019 eller tidligere) til at skelne mellem versioner nedenfor.
Nye funktioner
Som en del af den dynamiske array-opdatering indeholder Excel nu 8 nye funktioner, som direkte udnytter dynamiske arrays til at løse problemer, der traditionelt er svære at løse med konventionelle formler:
| Funktion | Formål |
| FILTRER | Filtrer data og returner matchende poster |
| SLUMPMATRIX | Generer matrix af tilfældige tal |
| SEKVENS | Generer matrix af sekventielle numre |
| SORTER | Sortér område efter kolonne |
| SORTER.EFTER | Sorter området efter et andet område eller array |
| ENTYDIGE | Udtræk unikke værdier fra en liste eller et område |
| XOPSLAG | Moderne erstatning for VLOOKUP |
| XSAMMENLIGN | Moderne erstatning for SAMMENLIGN-funktionen |
Bemærkninger:
- XOPSLAG og XSAMMENLIGN var ikke med i den oprindelige gruppe af nye dynamiske array-funktioner, men de kører godt sammen med den nye dynamiske array-motor. XOPSLAG erstatter LOPSLAG og tilbyder en moderne, fleksibel tilgang, der udnytter arrays. XSAMMENLIGN er en opgradering af SAMMENLIGN-funktionen, der giver nye muligheder til INDEX og SAMMENLIGN formler.
- LET-funktionen er nu også en del af de nye dynamiske array-funktioner.
- Flere funktioner er på vej.
Eksempel
Før vi går i detaljer, lad os så først se på et simpelt eksempel. Nedenfor bruger vi den nye ENTYDIGE-funktion til at udtrække unikke værdier fra området B5:B15 med en enkelt formel indtastet i D2:
=ENTYDIGE(A2:A10) // returnerer unikke værdier i A2:A10
Resultatet er en liste over de fem unikke farver, som optræder i A2:A10.
Som alle formler opdateres ENTYDIGE automatisk, når data ændres. Nedenfor er Gul i A10 erstattet Violet. Resultatet fra ENTYDIGE inkluderer nu Violet:
Overløb – én formel, mange værdier
I dynamisk Excel vil formler, der returnerer flere værdier, “overløber” disse værdier direkte i regnearket. Dette vil umiddelbart være mere logisk for formelbrugere. Det er også en fuldt dynamisk adfærd – når kildedata ændres, vil overløb resultater straks opdateres.
Det rektangel, der omslutter værdierne, kaldes “overløbs området”. Du vil bemærke, at overløbs området har en særlig fremhævelse. I eksemplet med ENTYDIGE ovenfor er overløbs området D2:D7.
Når data ændres, vil overløbs området udvides eller trække sig sammen efter behov. Du kan muligvis se nye værdier tilføjet, eller eksisterende værdier forsvinde. På denne måde er et overløbs området et slags nyt dynamisk område.
I tidligere Excel kan du derimod se flere resultater returneret af matrixformlen i formellinjen, hvis du bruger F9 til at inspicere formlen. Medmindre formlen er indtastet som en flercellet matrixformel, vises kun én værdi i regnearket. Denne adfærd har altid gjort matrixformler svære at forstå. Overløb gør matrixformler mere intuitive.
Bemærk: når overløb er blokeret af andre data, vil du se en #OVERLØB-fejl. Når du har gjort plads til overløbs området, overløbes formlen automatisk.
Reference for overløb område
For at henvise til et overløbs område skal du bruge et hash-tegn (#) efter den første celle i området. For at referere til resultaterne fra ENTYDIGE-funktionen ovenfor, skal du f.eks. bruge:
=D2# // refererer ENTYDIGE resultater
Dette er det samme som at henvise til hele overløbs området, og du vil se denne syntaks, når du skriver en formel, der refererer til et komplet overløbs område.
Du kan føre en reference for et overløbs område direkte ind i andre formler. For at tælle antallet af farver returneret af ENTYDIGE kan du f.eks. bruge:
=TÆLV(D2#) // antal unikke farver
Når overløbs området ændres, vil formlen afspejle de seneste data.
Massiv forenkling
Tilføjelsen af nye dynamiske matrixformler betyder, at visse formler kan forenkles drastisk. Her er et par eksempler:
- Udtræk og angiv unikke værdier
- Tæl unikke værdier
- Filtrer og udtræk poster
- Uddrag delvise sammenligninger
Den enes magt
En af de mest kraftfulde fordele ved tilgangen “én formel, mange værdier” er mindre afhængighed af absolutte eller blandede referencer. Da en dynamisk matrixformel overløber resultater i regnearket, forbliver referencer uændrede, men formlen genererer korrekte resultater.
For eksempel, nedenfor bruger vi FILTRER-funktionen til at udtrække poster i gruppen “Afdeling”. I celle E2 indtastes en enkelt formel:
=FILTRER(A2:C8;A2:A8="Alfa") // referencerne er relative
Bemærk, at begge områder er ulåste relative referencer, men formlen fungerer perfekt.
Dette er en stor fordel for mange brugere, fordi det gør processen med at skrive formler meget enklere. Et andet godt eksempel, kan du se i multiplikationstabellen længere nede.
Kædefunktioner
Ting bliver virkelig interessante, når du kæder mere end én dynamisk array-funktion sammen. Måske vil du sortere de returnerede resultater efter ENTYDIGE? Nemt. Indsæt bare funktionen ENTYDIGE i funktionen SORTER på denne måde:
Som før, når kildedata ændres, vises der automatisk nye unikke resultater, pænt sorteret.
Medfødt adfærd
Det er vigtigt at forstå, at dynamisk array-adfærd er medfødt og dybt integreret. Når en formel returnerer flere resultater, overføres disse resultater til flere celler i regnearket. Dette inkluderer ældre funktioner, der ikke oprindeligt er designet til at arbejde med dynamiske arrays.
For eksempel, i tidligere Excel, hvis vi giver funktionen LÆNGDE en række tekstværdier, vil vi se et enkelt resultat. I dynamisk Excel, hvis vi giver LÆNGDE-funktionen en række værdier, vil vi se flere resultater. Billedet nedenfor viser den gamle adfærd til venstre og den nye adfærd til højre:
Dette er en meget stor ændring, der kan påvirke alle slags formler. For eksempel er funktionen LOPSLAG designet til at hente en enkelt værdi fra en tabel ved hjælp af et kolonneindeks. Men i dynamisk Excel, hvis vi giver LOPSLAG mere end ét kolonneindeks ved hjælp af en matrixkonstant som denne:
=LOPSLAG("København";F5:H7;{1\2\3};0)
Med andre ord, selvom LOPSLAG aldrig blev designet til at returnere flere værdier, kan det nu gøre det takket være den nye formelmotor i den dynamiske Excel.
Alle formler
Bemærk endelig, at dynamiske arrays fungerer med alle formler, ikke kun funktioner. I eksemplet nedenfor indeholder celle C5 en enkelt formel:
=A2:A11*B1:K1
Resultatet løber ind i et 10 gange 10 område, der omfatter 100 celler:
Hvis tallene i intervallerne A2:A11 og A2: B1:K1 i sig selv er dynamiske arrays (dvs. oprettet med SEKVENS-funktionen), kan overløbs referenceoperatoren bruges på denne måde:
=A2#*B1# // returnerer samme 10 x 10 matrix
Arrays bliver mainstream
Med udrulningen af dynamiske arrays vil ordet “array” dukke op meget oftere. Faktisk kan du se “array” og “område” bruges næsten i flæng. Du vil se arrays i Excel omgivet af krøllede parenteser som dette:
{1\2\3} // vandret array
{1;2;3} // lodret array
Array er et programmeringsudtryk, der refererer til en liste af elementer, der vises i en bestemt rækkefølge. Grunden til, at arrays dukker op så ofte i Excel-formler, er, at arrays kan udtrykke værdierne i en række celler.
Array-operationer bliver vigtige
Da dynamiske Excel-formler nemt kan arbejde med flere værdier, bliver matrixoperationer vigtigere. Udtrykket “array-operation” refererer til et udtryk, der kører en logisk test eller matematisk operation på et array. For eksempel tester udtrykket nedenfor, om værdier i A2:A7 er lig med “København”.
=A2:A7="København" // By = "København"
fordi der er 6 celler i A2:A7, er resultatet 6 SAND/FALSK-værdier i en matrix:
{SAND;FALSK;SAND;FALSK;SAND;FALSK}
Matrixoperationen nedenfor kontrollerer for værdier større end 25:
=B2:B7>25 // værdier > 25
Den endelige array-operation kombinerer første opgave og anden opgave i et enkelt udtryk:
=(A2:A7="København")*(B2:B7>25)
Bemærk: Excel tvinger automatisk værdierne SAND og FALSK til 1 og 0 ved en matematikoperation.
For at bringe dette tilbage til dynamiske matrixformler i Excel, viser eksemplet nedenfor, hvordan vi kan bruge nøjagtig den samme matrixoperation inde i FILTRER-funktionen som inkluderingsargumentet:
FILTRER returnerer de to poster, hvor By = “København” og Værdi > 25.
Nye og gamle matrixformler
I den dynamiske Excel er der ingen grund til at indtaste matrixformler med Ctrl + Shift + Enter. Når en formel oprettes, kontrollerer Excel, om formlen muligvis returnerer flere værdier. Hvis det er tilfældet, vil den automatisk blive gemt som en dynamisk matrixformel, men du vil ikke se krøllede parenteser. Eksemplet nedenfor viser en typisk matrixformel indtastet i dynamisk Excel:
Hvis du åbner den samme formel i en tidligere Excel, vil du se krøllede parenteser:
Hvis du går den anden vej, når en “traditionel” matrixformel åbnes i dynamisk Excel, vil du se de krøllede parenteser i formellinjen. For eksempel viser skærmen nedenfor en simpel matrixformel i en tidligere Excel:
Men hvis du indtaster formlen igen uden ændringer, fjernes de krøllede parenteser, og formlen returnerer det samme resultat:
Konklusionen er, at matrixformler indtastet med Ctrl + Shift+ Enter (CSE) stadig fungerer for at opretholde kompatibilitet, men du bør ikke have behov for at indtaste matrixformler med CSE i dynamisk Excel.
Tegnet @
Med introduktionen af dynamiske arrays vil du se @-tegnet forekomme oftere i formler. Tegnet @ muliggør en adfærd kendt som “implicit skæringspunkt”. Implicit skæring er en logisk proces, hvor mange værdier reduceres til én værdi.
I tidligere Excel er implicit skæring en tavs adfærd, der bruges (når det er nødvendigt) til at reducere flere værdier til et enkelt resultat i én celle. I dynamisk Excel er det typisk ikke nødvendigt, da flere resultater kan vises i regnearket. Når det er nødvendigt, aktiveres implicit skæringspunkt manuelt med tegnet @.
Når du åbner regneark, der er oprettet i en ældre version af Excel, kan du muligvis se @-tegnet tilføjet automatisk til eksisterende formler, der har potentialet til at returnere mange værdier. I tidligere Excel vil en formel, der returnerer flere værdier, ikke overløbes i regnearket. Tegnet @ fremtvinger den samme adfærd i dynamisk Excel, så formlen opfører sig på samme måde og returnerer det samme resultat, som den gjorde i den originale Excel-version.
Med andre ord tilføjes @ for at forhindre, at en ældre formel overløber med flere resultater i regnearket. Afhængigt af formlen kan du muligvis fjerne @-tegnet, og formlens opførsel ændres ikke.
Opsummering
- Dynamiske arrays vil gøre visse formler meget nemmere at skrive.
- Du kan nu let filtrere matchende data, sortere og udtrække unikke værdier med formler.
- Dynamiske array-formler kan kædes (indlejres) for at udføre ting som filtrering og sortering.
- Formler, der returnerer mere end én værdi, overløbes automatisk.
- Det er ikke nødvendigt at bruge Ctrl + Shift + Enter ved indtastningen af en matrixformel.
- Dynamiske matrixformler er kun tilgængelige i Excel 365.
