Vzorce poľa sú jednou z najvýkonnejších funkcií Excelu. Ak poznáte vzorce poľa, budete radi, že môžete vytvoriť funkcie VBA, ktoré vrátia pole.
Vrátenie poľa názvov mesiacov
Začnime jednoduchým príkladom. Funkcia MonthNames vracia 12-prvkové pole – uhádli ste – názvov mesiacov.
Funkcia názvy mesiacov()
Názvy mesiacov = Array(“Január”, “Február”, “Marec”, _
„apríl“, „máj“, „jún“, „júl“, „august“, _
„september“, „október“, „november“, „december“)
End Function
Ak chcete použiť funkciu MonthNames v pracovnom hárku, musíte ju zadať ako vzorec poľa s 12 bunkami. Napríklad vyberte rozsah A2:L2 a zadajte =MonthNames() . Potom stlačte Ctrl+Shift+Enter a zadajte vzorec poľa do všetkých 12 vybratých buniek. Pozrite si výsledok.
Použitie funkcie MonthNames na vrátenie 12-prvkového poľa.
Ak chcete, aby sa názvy mesiacov zobrazovali v stĺpci, vyberte 12 buniek v stĺpci a použite tento vzorec poľa. (Nezabudnite ho zadať stlačením Ctrl+Shift+Enter.)
=TRANSPOSE(názvy mesiacov())
Môžete si tiež vybrať jeden mesiac z poľa. Tu je vzorec (nie vzorec poľa), ktorý zobrazuje štvrtý prvok poľa: April.
=INDEX(Názvy mesiacov(),4)
Vrátenie zoradeného zoznamu
Predpokladajme, že máte zoznam mien, ktoré chcete zobraziť v zoradenom poradí v inom rozsahu buniek. Nebolo by pekné, keby to za vás spravila funkcia pracovného hárka?
Táto vlastná funkcia robí práve toto: Ako argument berie rozsah buniek s jedným stĺpcom a potom vráti pole týchto buniek zoradených. Rozsah A2:A13 obsahuje niektoré názvy. Rozsah C2:C13 obsahuje tento viacbunkový vzorec poľa. (Nezabudnite, že vzorec musíte zadať stlačením Ctrl+Shift+Enter.)
Použitie vlastnej funkcie na vrátenie zoradeného rozsahu.
=Sorted(A2:A13)
Tu je kód pre funkciu Sorted:
Funkcia triedená (Rng ako rozsah)
Dim SortedData() ako variant
Dim Cell As Range
Dim Temp As Variant, i As Long, j As Long
Dim NonEmpty As Long
Preneste údaje do SortedData
Pre každú bunku v Rng
If Not IsEmpty (Cell) Then
Neprázdne = neprázdne + 1
ReDim Preserve SortedData (1 až NonEmpty)
SortedData(NonEmpty) = Cell.Value
Koniec Ak
Ďalšia bunka
Zoraďte pole
Pre i = 1 To Neprázdne
Pre j = i + 1 To Neprázdne
Ak SortedData(i) > SortedData(j) Potom
Teplota = SortedData(j)
SortedData(j) = SortedData(i)
SortedData(i) = Temp
Koniec Ak
Ďalej j
Ďalej i
' Transponujte pole a vráťte ho
Sorted = Application.Transpose(SortedData)
End Function
Funkcia Sorted začína vytvorením poľa s názvom SortedData. Toto pole obsahuje všetky neprázdne hodnoty v rozsahu argumentov. Ďalej sa pole SortedData triedi pomocou algoritmu bublinového triedenia. Pretože pole je horizontálne pole, musí byť transponované skôr, ako ho funkcia vráti.
Funkcia Sorted funguje s rozsahom ľubovoľnej veľkosti, pokiaľ je v jedinom stĺpci alebo riadku. Ak sú nezoradené údaje v riadku, váš vzorec musí použiť funkciu TRANSPOSE programu Excel na zobrazenie zoradených údajov vodorovne. Napríklad:
=TRANSPOSE(Zoradené(A16:L16))