Matrixformler er en af Excels mest kraftfulde funktioner. Hvis du er fortrolig med matrixformler, vil du være glad for at vide, at du kan oprette VBA-funktioner, der returnerer en matrix.
Returnerer en række månedsnavne
Lad os starte med et simpelt eksempel. Funktionen Månedsnavne returnerer en matrix med 12 elementer af - du gættede det - månedsnavne.
Funktion Månedsnavne()
Månedsnavne = Array("januar", "februar", "marts", _
"April", "Maj", "Juni", "Juli", "August", _
"September", "Oktober", "November", "December")
Afslut funktion
For at bruge funktionen Månedsnavne i et regneark, skal du indtaste det som en 12-cellers matrixformel. Vælg f.eks. område A2:L2 og indtast =MonthNames() . Tryk derefter på Ctrl+Shift+Enter for at indtaste matrixformlen i alle 12 markerede celler. Tjek resultatet.
Brug af funktionen Månedsnavne til at returnere et array med 12 elementer.
Hvis du ønsker, at månedsnavnene skal vises i en kolonne, skal du vælge 12 celler i en kolonne og bruge denne matrixformel. (Glem ikke at indtaste det ved at trykke på Ctrl+Shift+Enter.)
=TRANSPOSER(Månedsnavne())
Du kan også vælge en enkelt måned fra arrayet. Her er en formel (ikke en matrixformel), der viser det fjerde element i matrixen: April.
=INDEX(Månedsnavne(),4)
Returnerer en sorteret liste
Antag, at du har en liste over navne, du vil vise i sorteret rækkefølge i et andet celleområde. Ville det ikke være rart at have en regnearksfunktion til at gøre det for dig?
Denne brugerdefinerede funktion gør netop det: Den tager et enkelt kolonneområde af celler som sit argument og returnerer derefter en række af disse celler sorteret. Område A2:A13 indeholder nogle navne. Område C2:C13 indeholder denne flercellede matrixformel. (Husk at du skal indtaste formlen ved at trykke på Ctrl+Shift+Enter.)
Brug af en brugerdefineret funktion til at returnere et sorteret område.
=Sorteret(A2:A13)
Her er koden til funktionen Sorteret:
Funktion sorteret (Rng som område)
Dim SortedData() som variant
Dæmp celle som rækkevidde
Dim Temp As Variant, i As Long, j As Long
Dim ikke-tom så længe
' Overfør data til SortedData
For hver celle i Rng
Hvis ikke er tom (celle) så
NonEmpty = NonEmpty + 1
ReDim Bevar SortedData(1 til NonEmpty)
SortedData(NonEmpty) = Cell.Value
Afslut Hvis
Næste celle
' Sorter arrayet
For i = 1 Til Ikke-tom
For j = i + 1 til ikke-tom
Hvis SortedData(i) > SortedData(j) Så
Temp = SortedData(j)
SortedData(j) = SortedData(i)
SortedData(i) = Temp
Afslut Hvis
Næste j
Næste i
' Transponer arrayet og returner det
Sorteret = Application.Transpose(SortedData)
Afslut funktion
Funktionen Sorteret starter med at skabe et array med navnet SortedData. Denne matrix indeholder alle de ikke-blanke værdier i argumentområdet. Derefter sorteres SortedData-arrayet ved hjælp af en boble-sorteringsalgoritme. Da arrayet er et vandret array, skal det transponeres, før det returneres af funktionen.
Den sorterede funktion fungerer med et område af enhver størrelse, så længe det er i en enkelt kolonne eller række. Hvis de usorterede data er i en række, skal din formel bruge Excels TRANSPOSE-funktion til at vise de sorterede data vandret. For eksempel:
=TRANSPOSER(Sorteret(A16:L16))