Tips og tricks

Dynamiske matrixfunktioner i Excel

Dynamiske matrixfunktioner

Tidligere skulle du huske at trykke på Ctrl+Shift+Enter, når du indtastede matrixfunktioner, hvorefter disse blev vist med krøllede parenteser (Tuborg-klammer), men det er slut nu.

Excel introducerer lige nu seks nye dynamiske matrixformler, og der er flere på vej. Du kan bruge disse til at oprette en liste af entydige værdier (ved at fjerne dubletter), sortere en liste, udtrække en filtreret liste og meget mere, ligesom mange eksisterende funktioner kan benytte sig af over- løbsmatrixer.

Funktionerne er selvfølgelig dynamiske, så hvis dine data ændres, så vil de dynamiske matrixer også ændre sig.

  • ENTYDIGE [UNIQUE]
  • FILTRER [FILTER]
  • SEKVENS [SEQUENCE]
  • SLUMPMATRIX [RANDARRAY]
  • SORTER [SORT]
  • SORTER.EFTER [SORTBY]

To nye operatorer

I forbindelse med dynamiske matrixformler er der også indført to nye operatorer i Excel, som ganske kort gennemgås her, og derefter gennemgås nærmere i gennemgangen af de enkelte funktioner og eksempler.

  • Implicit skæringsoperator
  • Overløbsmatrixoperator

Implicit skæringsoperator

Den implicitte skæringsoperator, @, er indført som en opgradering af Excels formelsprog for at kunne understøtte dynamiske matrixer.

Hvis du åbner Excel-projektmapper, der er blevet oprettet i tidligere versioner af Excel, vil du i starten få vist en meddelelse om, at Excel har indsat den implicitte skæringsoperator for at tydeliggøre, hvad funktionen returnerer.

Hvad er en implicit skæring?

En implicit skæring reducerer flere værdier til én enkelt værdi, så du kan returnere denne til en celle i stedet for hele den matrix, som du henviser til. Dette har Excel i princippet altid gjort i baggrunden, men nu er der bare kommet en operator til at vise det.

Logikken virker således:

  • Hvis værdien er et enkelt element, så returneres elementet
  • Hvis værdien er et område, så returneres værdien fra cellen i samme række eller samme kolonne som formlen
  • Hvis værdien er en matrix, så returneres værdien fra øverste, venstre hjørne

I forbindelse med indførelse af dynamiske matrixer er Excel ikke længere begrænset til at returnere enkelte værdier fra formler, hvorfor det ikke længere er nødvendigt at benytte skjult baggrundsberegnet implicit skæringslogik.

NOTE @-symbolet bruges allerede i Excel-tabeller, hvorfor du måske allerede har stiftet bekendtskab med brugen af det. I Excel-tabeller henviser @-symbolet til, at formlen skal anvende en implicit skæring til at hente værdien fra en kolonne i samme række, som fx =[@Kolonnetitel].

Overløbsmatrixoperatoren og matrixreferencenotation

Hvis du har en dynamisk matrix i E3:G20, så kan det være lidt svært at referere til den, især hvis den jævnligt ændrer størrelse. Derfor kan du bruge overløbsmatrixoperatoren, #, til at referere til hele matrixen (kaldes også ofte blot for overløbsreference).

Formlen =E3 refererer til indholdet i celle E3, mens formlen =E3# refererer til hele den matrix, der starter i E3. Dette kaldes uofficielt matrixreferencenotation, og du vil se flere eksempler på dette senere i afsnittet.

NOTE Matrixreferencenotation understøttes ikke i referencer til andre projektmapper.

Du kan oprette overløbsreferencer på flere forskellige måder:

  • Indtast adressen på eller vælg den første celle i en overløbsmatrix for at oprette en reference til den celle, og skriv derefter selv et #-symbol efter selve adressen.
  • Markér alle cellerne i overløbsmatrixen ved hjælp af musen eller tastaturet for at oprette en overløbsreference.

NOTE Du kan fx bruge en overløbsreference til at referere til en sorteret liste over entydige værdier i en liste – fx i forbindelse med en datavalideringsliste i stedet for dynamiske navne, som tidligere har været måden at løse dette på i tidligere udgaver af Excel.

Overløbsmatrix

Fælles for alle de dynamiske matrixfunktioner er, at de skrives på normal vis i en helt almindelig celle, og de så selv fordeler formlen til det nødvendige antal celler, hvilket kaldes overløb.

Overløb

Formlen er i ovenstående illustration skrevet i celle D3, og har så selv lavet overløb i D4:D9. Du kan kende overløbet på den svage blå ramme omkring området, der også vises med en svag skygge.

Det er værd at notere sig følgende i forbindelse med dynamiske matrixer:

  • Når du trykker på Enter for at bekræfte din formel, så tilpasses størrelsen på outputområdet dynamisk, og resultaterne placeres i hver sin celle i regnearket.
  • Hvis du arbejder med en liste af inddata, så kan det være en fordel at definere dine inddata som en Excel-tabel, og så bruge strukturerede referencer til at referere til dataene med, så inddataområdet kan vokse, uden at du behøver at ændre dine dynamiske matrixformler.
  • Excel-tabeller understøtter ikke i sig selv overløbsmatrixer, så du skal skrive dine dynamiske matrixformler uden for tabelområdet.
  • Du kan kun rette dynamiske matrixformler i den øverste celle i matrixen. Hvis du markerer en af de andre celler i matrixen, vil du stadig kunne se formlen i formellinjen, men den vil blive vist i gråtone og kan ikke redigeres.
  • Hvis uddataområdet ikke er stort nok til at rumme den dynamiske matrixformel, så vil du få vist en #OVERLØB-fejlmeddelelse.
  • De nu gammeldags {}-matrixer, hvor du skulle trykke på Ctrl+Shift+Enter for at bekræfte formlen, understøttes stadig, men bør ikke bruges længere.
  • Excel understøtter kun dynamiske matrixformler på tværs af flere projektmapper i begrænset omfang, og de understøttes kun, når begge projektmapper er åbne.

De dynamiske matrixfunktioner

Jeg gennemgår de forskellige dynamiske matrixfunktioner i hvert deres afsnit, og jeg vil starte med funktionen ENTYDIGE.

ENTYDIGE

Funktionen ENTYDIGE [UNIQUE] gør det mulig at oprette en overløbsmatrix over entydige værdier, dvs. den fjerner duplikerede værdier, ved hjælp af en meget simpel formel.

= ENTYDIGE( Matrix ; [Efter_kol] ; [Præcis_en_gang] )

= UNIQUE( array; [by_column] ; [occurs_once] )

  • Matrix er den matrix, du vil returnere entydige rækker eller kolonner
  • Efter_kol er et valgfrit argument, der gør det muligt at vælge, om du vil sammenligne rækker eller kolonner, når matrix refererer til flere kolonner. Standardværdien er FALSK for at sammenligne rækkevist, men sættes til SAND for at sammenligne kolonnevist.
  • Præcis_en_gang er et valgfrit argument, der giver dig mulighed for kun at returnere værdier, der kun optræder præcist én gang i matrixen. Som standard har dette argument værdien FALSK for at returnere alle entydige værdier, men sættes til SAND for at returnere alle værdier, der kun forekommer én gang.

Eksempler

Lad mig vise dig nogle eksempler på brugen af denne funktion.

Eksempel 1

ENTYDIGE

I ovenstående eksempel bruges funktionen ENTYDIGE [UNIQUE] til at returnere en liste af entydige værdier i listen i kolonne B.

Eksempel 2

ENTYDIGE

I ovenstående eksempel har jeg ved at sætte argumentet Præcis_en_gang til SAND kun fået returneret de værdier i kolonne B, der kun forekommer én enkelt gang.

Eksempel 3

ENTYDIGE

I ovenstående eksempel bruger jeg &-operatoren til at samle fornavn og efternavn til det fulde navn.

Eksempel 4

ENTYDIGE

I dette sidste eksempel har jeg konverteret B5:B22 fra eksempel 1 til en Excel-tabel, og så kombineret de to nye dynamiske matrixformler ENTYDIGE [UNIQUE] og SORTER [SORT] til at vise en dynamisk sorteret liste med entydige navne.