Формулите за масив са една от най-мощните функции на Excel. Ако сте запознати с формулите за масиви, ще се радвате да знаете, че можете да създавате VBA функции, които връщат масив.
Връщане на масив от имена на месеци
Нека започнем с прост пример. Функцията MonthNames връща 12-елементен масив от — познахте — имена на месеци.
Функция Имена на месеци()
Имена на месеци = Масив(„януари”, „февруари”, „март”, _
„април“, „май“, „юни“, „юли“, „август“, _
„Септември“, „Октомври“, „Ноември“, „Декември“)
Крайна функция
За да използвате функцията MonthNames в работен лист, трябва да я въведете като формула за масив от 12 клетки. Например, изберете диапазон A2:L2 и въведете =MonthNames() . След това натиснете Ctrl+Shift+Enter, за да въведете формулата на масива във всичките 12 избрани клетки. Вижте резултата.
Използване на функцията MonthNames за връщане на масив от 12 елемента.
Ако искате имената на месеците да се показват в колона, изберете 12 клетки в колона и използвайте тази формула за масив. (Не забравяйте да го въведете, като натиснете Ctrl+Shift+Enter.)
=TRANSPOSE(Имена на месеци())
Можете също да изберете един месец от масива. Ето формула (не формула за масив), която показва четвъртия елемент от масива: април.
=ИНДЕКС(имена на месеци(),4)
Връщане на сортиран списък
Да предположим, че имате списък с имена, които искате да покажете в сортиран ред в друг диапазон от клетки. Не би ли било хубаво да има функция за работен лист да направи това вместо вас?
Тази персонализирана функция прави точно това: приема диапазон от клетки от една колона като свой аргумент и след това връща масив от тези клетки, сортирани. Диапазон A2:A13 съдържа някои имена. Диапазонът C2:C13 съдържа тази формула за многоклетъчен масив. (Не забравяйте, че трябва да въведете формулата, като натиснете Ctrl+Shift+Enter.)
Използване на персонализирана функция за връщане на сортиран диапазон.
=Сортирано(A2:A13)
Ето кода за функцията Sorted:
Функция сортирана (Rng като диапазон)
Dim SortedData() като вариант
Затъмнена клетка като диапазон
Dim Temp като вариант, i толкова дълго, j колкото дълго
Dim NonEmpty As Long
' Прехвърлете данни към SortedData
За всяка клетка в Rng
Ако не е празно (клетка), тогава
Непразно = Непразно + 1
ReDim Запазване на сортирани данни (1 до NonEmpty)
SortedData(NonEmpty) = Cell.Value
Край, ако
Следваща клетка
' Сортирайте масива
За i = 1 до непразен
За j = i + 1 до непразен
Ако SortedData(i) > SortedData(j) Тогава
Temp = SortedData(j)
Сортирани данни(j) = Сортирани данни(i)
SortedData(i) = Temp
Край, ако
Следващото j
Следваща и
' Транспонирайте масива и го върнете
Сортирано = Application.Transpose(SortedData)
Крайна функция
Функцията Sorted започва със създаване на масив с име SortedData. Този масив съдържа всички непразни стойности в диапазона на аргументите. След това масивът SortedData се сортира, като се използва алгоритъм за сортиране на мехурчета. Тъй като масивът е хоризонтален масив, той трябва да бъде транспониран, преди да бъде върнат от функцията.
Сортираната функция работи с диапазон от всякакъв размер, стига да е в една колона или ред. Ако несортираните данни са в един ред, вашата формула трябва да използва функцията TRANSPOSE на Excel, за да покаже сортираните данни хоризонтално. Например:
=ТРАНСПОЗИРАНЕ(Сортирано(A16:L16))