Excel2016で配列を返すVBA関数の操作

配列数式は、Excelの最も強力な機能の1つです。配列数式に精通している場合は、配列を返すVBA関数を作成できることを知って幸せです。

月名の配列を返す

簡単な例から始めましょう。MonthNames関数は、月の名前の12要素の配列を返します。

関数MonthNames（）
  MonthNames = Array（ "January"、 "February"、 "March"、_
   「4月」、「5月」、「6月」、「7月」、「8月」、_
   「9月」、「10月」、「11月」、「12月」）
終了機能

ワークシートでMonthNames関数を使用するには、12セルの配列数式として入力する必要があります。たとえば、範囲A2：L2を選択し、= MonthNames（）と入力します。次に、Ctrl + Shift + Enterを押して、選択した12個のセルすべてに配列数式を入力します。結果を確認してください。

Excel2016で配列を返すVBA関数の操作

MonthNames関数を使用して12要素の配列を返します。

月の名前を列に表示する場合は、列の12個のセルを選択し、この配列数式を使用します。（Ctrl + Shift + Enterを押して入力することを忘れないでください。）

= TRANSPOSE（MonthNames（））

配列から1か月を選択することもできます。配列の4番目の要素である4月を表示する数式（配列数式ではありません）を次に示します。

= INDEX（MonthNames（）、4）

ソートされたリストを返す

別のセル範囲に並べ替えられた順序で表示する名前のリストがあるとします。ワークシート関数でそれを実行できると便利ではないでしょうか。

このカスタム関数はまさにそれを行います。引数として単一列のセル範囲を取り、ソートされたセルの配列を返します。範囲A2：A13にはいくつかの名前が含まれています。範囲C2：C13には、このマルチセル配列数式が含まれています。（Ctrl + Shift + Enterを押して数式を入力する必要があることに注意してください。）

Excel2016で配列を返すVBA関数の操作

カスタム関数を使用して、並べ替えられた範囲を返します。

=ソート済み（A2：A13）

Sorted関数のコードは次のとおりです。

ソートされた関数（Rng As Range）
  Dim SortedData（）As Variant
  範囲としての薄暗いセル
  Dim Temp As Variant、i As Long、j As Long
  空でない限り薄暗い
'データをSortedDataに転送する
  Rngの各セルについて
    IsEmpty（Cell）でない場合は
      NonEmpty = NonEmpty + 1
      ReDim Preserve SortedData（1 To NonEmpty）
      SortedData（NonEmpty）= Cell.Value
    終了する場合
  次のセル
'配列を並べ替える
  i = 1からNonEmptyの場合
    j = i +1からNonEmptyの場合
      SortedData（i）> SortedData（j）の場合
        Temp = SortedData（j）
        SortedData（j）= SortedData（i）
        SortedData（i）= Temp
      終了する場合
    次のj
  次の私
'配列を転置して返します
  ソート済み= Application.Transpose（SortedData）
終了機能

Sorted関数は、SortedDataという名前の配列を作成することから始まります。この配列には、引数範囲内のすべての空白以外の値が含まれています。次に、SortedData配列は、バブルソートアルゴリズムを使用してソートされます。配列は水平配列であるため、関数によって返される前に転置する必要があります。

ソートされた関数は、単一の列または行にある限り、任意のサイズの範囲で機能します。並べ替えられていないデータが連続している場合、数式はExcelのTRANSPOSE関数を使用して、並べ替えられたデータを水平方向に表示する必要があります。例えば：