Les fórmules de matriu són una de les funcions més potents d'Excel. Si esteu familiaritzat amb les fórmules de matriu, us agradarà saber que podeu crear funcions VBA que retornin una matriu.
Torna una sèrie de noms de mesos
Comencem amb un exemple senzill. La funció MonthNames retorna una matriu de 12 elements de, ho heu endevinat, noms de mesos.
Funció MonthNames()
MonthNames = Matriu ("gener", "febrer", "març", _
“Abril”, “Maig”, “Juny”, “Juliol”, “Agost”, _
"Setembre", "Octubre", "Novembre", "Desembre")
Funció final
Per utilitzar la funció MonthNames en un full de treball, l'heu d'introduir com a fórmula de matriu de 12 cel·les. Per exemple, seleccioneu l'interval A2:L2 i introduïu =MonthNames() . A continuació, premeu Ctrl+Maj+Enter per introduir la fórmula de matriu a les 12 cel·les seleccionades. Consulteu el resultat.
Utilitzant la funció MonthNames per retornar una matriu de 12 elements.
Si voleu que els noms dels mesos es mostrin en una columna, seleccioneu 12 cel·les en una columna i utilitzeu aquesta fórmula de matriu. (No us oblideu d'introduir-lo prement Ctrl+Maj+Enter.)
=TRANSPOSE(Noms del mes())
També podeu escollir un sol mes de la matriu. Aquí hi ha una fórmula (no una fórmula de matriu) que mostra el quart element de la matriu: abril.
=INDEX(Noms del mes(),4)
Torna una llista ordenada
Suposem que teniu una llista de noms que voleu mostrar ordenats en un altre rang de cel·les. No seria bo que una funció de full de treball ho fes per tu?
Aquesta funció personalitzada fa exactament això: pren un rang de cel·les d'una sola columna com a argument i després retorna una matriu d'aquestes cel·les ordenades. El rang A2:A13 conté alguns noms. L'interval C2:C13 conté aquesta fórmula de matriu multicel·la. (Recordeu que heu d'introduir la fórmula prement Ctrl+Maj+Enter.)
Ús d'una funció personalitzada per retornar un rang ordenat.
=Ordenat (A2:A13)
Aquí teniu el codi de la funció Sorted:
Funció ordenada (Rng com a rang)
Dim SortedData() com a variant
Dim Cell As Range
Dim Temp As Variant, i As Long, j As Long
Dim NonEmpty As Long
' Transfereix dades a SortedData
Per a cada cèl·lula en Rng
Si no és buida (cel·la), aleshores
No buit = No buit + 1
ReDim Conserva les dades ordenades (1 a no buit)
SortedData(NonEmpty) = Cell.Value
Acaba si
Cel·la següent
' Ordena la matriu
Per i = 1 a No buit
Per a j = i + 1 a No buit
Si SortedData(i) > SortedData(j), aleshores
Temp = Dades ordenades (j)
SortedData(j) = SortedData(i)
SortedData(i) = Temp
Acaba si
Següent j
A continuació i
' Transposa la matriu i torna-la
Ordenat = Application.Transpose(SortedData)
Funció final
La funció Sorted comença creant una matriu anomenada SortedData. Aquesta matriu conté tots els valors no en blanc de l'interval d'arguments. A continuació, s'ordena la matriu SortedData mitjançant un algorisme d'ordenació de bombolles. Com que la matriu és una matriu horitzontal, s'ha de transposar abans de ser retornada per la funció.
La funció Ordenada funciona amb un interval de qualsevol mida, sempre que estigui en una sola columna o fila. Si les dades no ordenades es troben en una fila, la fórmula ha d'utilitzar la funció TRANSPOSE d'Excel per mostrar les dades ordenades horitzontalment. Per exemple:
=TRANSPOSE(Ordenat(A16:L16))