Du skal kende lidt til formler i Excel i forvejen for at forstå dette blogindlæg. Men når du gør det, vil du forhåbentlig være enig med mig at dynamiske matrixformler er en kæmpe Excel nyhed, der åbner op for nye måde at løse opgaverne på.
Eksempel 1 - Dynamisk Excel matrixformel i én celle
Jeg tager udgangspunkt i en liste, der viser mandatfordelingen til folketingsvalgene i 2015 og 2019 i Danmark:
I eksemplet er det gennemsnitlige udsving i mandattal pr. parti udregnet i celle D13 ud fra beregnet udsving i mandattal for hvert parti i cellerne D3:D12. Det er en klassisk Excel beregnings model.
Jeg vil nu udregne gennemsnittet uden at oprette en beregnet kolonne først. Formlen kan oprettes som en dynamisk matrixformel i én celle (D13). – Formlen afsluttes med Enter lige som alle andre formler:
Virkelig smart metode til at spare beregnede kolonner! I tidligere versioner skulle formlen afsluttes med Ctrl+Shift+Enter – statisk matrixformel
Eksempel 2 – Excel dynamisk matrixformel med overløbsområde
Matrixformler kan også oprettes for et celleområde med flere celler. Det efterfølgende eksempel illustrerer, hvor nyttigt det er med dynamiske matrixformler for celleområder. Jeg tager udgangspunkt i en lodret liste med afdelingsnavne i kolonne A, og jeg ønsker nu at afdelings navne skal vises vandret et andet sted i regnearket med brug af funktionen TRANSPONER:
Formlen oprettes stadigvæk i én celle – B2 – og resten af matrixformlens resultater viser sig i overløbsområdet i de efterfølgende celler. Funktionen TRANSPONER er ikke ny, men formlen skulle tidligere oprettes som en statisk matrixformel for celleområdet, her formlen {=TRANSPONER(A2:A6)} for celleområdet B2:F2.
Det var ikke optimalt blandt andet fordi hele celleområdet med formlen skulle markeres, før formlen blev oprettet med genvejstasterne Ctrl+Shift+Enter. Man endte typisk ud i at undlade brug af TRANSPONER og i stedet nøjes med at kopiere data og derefter indsætte transponeret – eller at bruge komplicerede forskydningsfunktioner.
De dynamiske matrixformler spiller rigtig godt sammen med Excel tabeller, der jo netop også er dynamiske. I eksemplet er der nu oprettet en Excel tabel med tabelnavnet tAfdelinger i kolonne A, og matrixformlen i celle B2 henviser nu til tabelnavnet:
Når der indsættes en nyt element i tabellen, udvides denne dynamisk, hvilket slår igennem i den dynamiske matrixformel. Her er der tastet IT nederst i Excel tabellen, hvorefter matrixformlen i celle B2 tilsvarende viser IT i overløbsområdet:
Nu begynder betegnelsen Dynamisk matrixformeler i Excel for alvor at give mening!
Eksempel 3 – Funktionen SORTER
Udover, at de dynamiske matrixformler puster nyt liv i matrixformler og matrixfunktioner som TRANFORMER, er der også kommet en række nye funktioner til brug i matrixformler, bl.a. funktionen SORTER. Jeg tager udgangspunkt i en liste med navne sorteret efter Efternavn. I eksemplet er der tale om en Excel tabel med tabelnavnet tNavne i kolonnerne A:B:
Jeg ønsker nu en liste i kolonne D:E med navnene sorteret efter Fornavn. Den sorterende liste oprettes i celle D2 med funktionen SORTER. – I sin enkleste form har funktionen kun ét argument; nemlig området, der skal sorteres – her tabellen tNavne:
Excel tabellen i kolonnerne A:B er ikke absolut nødvendig, men vældig nyttig lige som i det foregående eksempel: Hvis der tilføjes et navn i Excel tabellen, medtages navnet også automatisk i den sorterede liste:
Opgaven med at vise en sorteret liste ved siden af den oprindelige liste er vanskelig at løse med almindelige formler, især hvis der også skal tages hensyn til tomme celler, dubletter og blanding af tal og tekst.
Afslutning
Der er flere nye funktioner i Excel, der er ekstremt nyttige i dynamiske matrixformler, bl.a. FILTRER, SEKVENS og ENTYDIGE. Du kan læse mere om disse i mit andet indlæg om Dynamiske matrixformler: Dynamiske matrixformler i Excel - Del 2
+45 33 14 71 44
