Dynamiske Excel matrixformler - Del 2

Dette blogindlæg er en forsættelse af Dynamiske matrixformler i Excel - Del 1

En matrixformel kan nu blot oprettes i én celle og vil derefter automatisk blive vist i overløbsområdet i de efterfølgende celler! Der er kommet seks nye matrixfunktioner til:

• SORTER
• FILTRER
• SEKVENS
• ENTYDIGE
• SORTER.EFTER
• SLUMPMATRIX

Igen vil vi ikke prøve at gennemgå alle muligheder og finesser, men blot vise eksempler på opstillinger, der før har været besværlige i Excel, men som nu kan klares som en leg ved anvendelse af matrixfunktioner.

Funktionen SORTER er vist i et eksempel i det tidligere blogindlæg. I dette indlæg vises eksempler på brug af funktionerne FILTRER, SEKVENS og ENTYDIGE.

Eksempel 1 – Funktionen FILTRER

Funktionen FILTRER opbygger en liste som en filtrering af en anden liste.

Vi tager udgangspunkt i Excel-tabellen tindkøb, der viser indkøb fordelt på indkøbere. Vi ønsker at oprette en liste filtreret efter indkøbernavnet angivet i celle F1.

Overskrifterne fra tabellen kommer IKKE automatisk med i filtreringen. Derfor er der her først oprettet en matrixformel i celle E4 til overskrifterne – et simpelt cellelink til området med overskrifter i tabellen tIndkøb. Disse overskrifter vises efterfølgende i cellen E4 og i overløbsområdet til højre for E4:

Herefter oprettes den filtrerede liste ved anvendelse af en matrixformel ved brug af funktionen FILTRER i celle E5:

FILTRER formlen har her denne syntaks: FILTRER(matrix; inkluder)

• matrix angiver det område, der skal filtreres, her Excel-tabellen tIndkøb
• inkluder angiver et logisk udtryk for filtrering i kolonner, her tIndkøb[Indkøber]=F1

vi filtrerer kolonnen Indkøber efter værdien i celle F1)

Opgaven med at vise en filtreret liste et andet sted end den oprindelige liste kan fx også løses ved at filtrere den oprindelige liste ved brug af et Avanceret filter. Men dette er ikke altid optimalt, da det avancerede filter ikke opdateres automatisk.

Eksempel 2 – Funktionen SEKVENS og indikator for overløbsområde

Du opbygger ofte et regneark ved at oprette data og formler, der skal kopieres nedad i kolonner; men hvad hvis antallet af rækker ikke ligger fast? Funktionen SEKVENS opbygger et område med det antal rækker eller kolonner du ønsker og løser dermed en gammelkendt problemstilling i Excel.

Vi tager udgangspunkt i nedenstående eksempel. Der skal oprettes en liste med datoer, startende med datoen i celle B2 og med det antal rækker som er angivet i celle B3.

Først oprettes listen med datoer ved hjælp af en matrixformel ved brug af funktionen SEKVENS i celle D2:

FILTRER formlen har her denne syntaks: SEKVENS(rækker; ; start)

• rækker angiver antallet af rækker i området, her værdien i celle B3
• start angiver første værdi i området, her datoen i celle B2

Opgaven med at opbygge en liste med et variabelt antal rækker har altid være efterspurgt i Excel. Ofte er man endt ud i at kopiere værdier og formler nedad i listen efter behov, hvilket ikke er optimalt.

Nu oprettes der en matrixformel i celle E2 til ugenumre:

Bemærk #-tegnet i formlen for celle E2:

=ISOUGE.NR(D2#)

Uden #-tegn ville formlen blot returnere ét ugenummer i celle E2 svarende til datoen i celle D2. #-tegnet efter cellereferencen er en indikator for overløbsområde, der gør formlen i celle E2 til en dynamisk matrixformel, der viser resultater i overløbsområdet for cellen svarende til overløbsrådet for matrixformlen i celle D2.

Matrixformler med brug af indikator for overløbsområde er et super smart og dynamisk alternativ til den traditionelle metode med at oprette formler i øverste række og derefter kopiere nedad i kolonnerne.

Eksempel 3 – Funktionen ENTYDIGE

Funktionen ENTYDIGE opbygger en liste indeholdende unikke værdier fra en anden liste.

Her er der oprettet en liste med unikke sælgernavne fra Excel-tabellen tSalg, startende i celle E2:

Listen med unikke sælgernavne er oprettet ved hjælp af en matrixformel med funktionen ENTYDIGE i celle E2. Funktionen ENTYDIGE har her ét argument, tSalg[Sælger] – området med sælgernavne i tabellen tSalg.

Opgaven med at oprette en liste med unikke værdier kan også løses ved at oprette en Pivottabel eller et Avanceret filter; men det er ikke altid optimalt, fordi ingen af disse elementer opdateres automatisk.

Prøv disse matrixformler i dine egne tabeller og opdag deres store anvendelsesmuligheder!

Ønsker du at lærer mere om hvordan du benytter disse funktioner, så kan vi klart anbefale vores Excel kurser.