Pole vzorce jsou jednou z nejvýkonnějších funkcí Excelu. Pokud jste obeznámeni s maticovými vzorci, budete rádi, když budete vědět, že můžete vytvořit funkce VBA, které vrátí pole.
Vrácení pole názvů měsíců
Začněme jednoduchým příkladem. Funkce MonthNames vrací 12prvkové pole – uhodli jste – názvů měsíců.
Funkce MonthNames()
MonthNames = Array(“leden”, “únor”, “březen”, _
"duben", "květen", "červen", "červenec", "srpen", _
"září", "říjen", "listopad", "prosinec")
End Function
Chcete-li použít funkci MonthNames v listu, musíte ji zadat jako maticový vzorec s 12 buňkami. Vyberte například rozsah A2:L2 a zadejte =MonthNames() . Poté stiskněte Ctrl+Shift+Enter a zadejte maticový vzorec do všech 12 vybraných buněk. Podívejte se na výsledek.
Pomocí funkce MonthNames vrátíte 12prvkové pole.
Pokud chcete, aby se názvy měsíců zobrazovaly ve sloupci, vyberte 12 buněk ve sloupci a použijte tento maticový vzorec. (Nezapomeňte jej zadat stisknutím Ctrl+Shift+Enter.)
=TRANSPOSE(názvy měsíců())
Můžete si také vybrat jeden měsíc z pole. Zde je vzorec (nikoli vzorec pole), který zobrazuje čtvrtý prvek pole: April.
=INDEX(Názvy měsíců(),4)
Vrácení seřazeného seznamu
Předpokládejme, že máte seznam jmen, která chcete zobrazit v seřazeném pořadí v jiném rozsahu buněk. Nebylo by hezké, kdyby to za vás udělala funkce listu?
Tato vlastní funkce dělá právě to: Vezme jako argument rozsah buněk s jedním sloupcem a poté vrátí pole těchto buněk seřazených. Rozsah A2:A13 obsahuje některá jména. Rozsah C2:C13 obsahuje tento vícebuněčný maticový vzorec. (Nezapomeňte, že vzorec musíte zadat stisknutím Ctrl+Shift+Enter.)
Použití vlastní funkce k vrácení seřazeného rozsahu.
=Seřazeno(A2:A13)
Zde je kód pro funkci Sorted:
Funkce seřazena (Rng jako rozsah)
Dim SortedData() jako varianta
Dim Cell As Range
Dim Temp As Variant, i As Long, j As Long
Dim NonEmpty As Long
Přeneste data do SortedData
Pro každou buňku v Rng
If Not IsEmpty(Cell) Then
NonEmpty = NonEmpty + 1
ReDim Zachovat seřazená data (1 až neprázdná)
SortedData(NonEmpty) = Cell.Value
End If
Další buňka
Seřaďte pole
Pro i = 1 To Neprázdné
Pro j = i + 1 To Neprázdné
Pokud SortedData(i) > SortedData(j) Pak
Teplota = SortedData(j)
SortedData(j) = SortedData(i)
SortedData(i) = Temp
End If
Další j
Příště já
' Transponujte pole a vraťte je
Sorted = Application.Transpose(SortedData)
End Function
Funkce Sorted začíná vytvořením pole s názvem SortedData. Toto pole obsahuje všechny neprázdné hodnoty v rozsahu argumentů. Dále je pole SortedData setříděno pomocí algoritmu bublinového třídění. Protože pole je vodorovné pole, musí být transponováno, než jej funkce vrátí.
Tříděná funkce pracuje s rozsahem libovolné velikosti, pokud je v jediném sloupci nebo řádku. Pokud jsou nesetříděná data v řadě, váš vzorec musí použít funkci TRANSPOSE aplikace Excel k zobrazení seřazených dat vodorovně. Například:
=TRANSPOSE(Seřazeno(A16:L16))