VBAでワークシート関数のINDEX関数の使い方についてご説明します。
ワークシート関数の「INDEX」を使用する事で、簡単に指定した行と列の交差する値を取得する事ができます。
ExcelでMATCH関数と合わせて利用される事が多い関数かと思います。
単純な処理に思えますが、VBAでINDEX関数を再現するのは2重ループを組み込んだり意外と面倒です。
ExcelVBAの利点はワークシートという高機能なアプリケーションを、最初から使える事です。
そして、最大の利点はExcelに用意されている関数を使える事です。
その関数の一つ、「INDEX関数」の使い方についてご説明します。
1.INDEX関数とは
INDEX関数は指定したセル範囲内の指定した行と列が交差したセルの値を返す関数です。
また、配列形式でINDEX関数を使用した場合、セルの参照ではなく配列の要素の値を返します。
Excelではセル範囲はよく利用されますが、配列形式はなかなか利用する機会がありません。
ですがVBAでは配列形式で利用する機会も増えるかと思います。
INDEX関数の欠点
INDEX関数の欠点として、別ブックのデータを参照する場合にブックが閉じていると参照できない事です。
参照したいブックが開いている場合は正常に参照ができますが、ブックが閉じていると参照できないため、別ブックを参照する処理の場合には工夫が必要です。
また、Excelでは複数の範囲指定ができますが、VBAでは対応できません。
2.INDEX関数の使い方
INDEX関数は範囲の指定方法で省略できる引数が異なりますので、それぞれ記載したいと思います。
気を付けなければいけないのが、行と列の指定はワークシートの行と列ではなく指定した範囲の中の左上端から数えて何番目か指定します。
基本構文
INDEX関数は基本的に次のように記述します。
INDEX(範囲, 行番号, 列番号)
1行、もしくは1列の範囲
指定する行、列が1行目、1列目の場合省略が可能で次のように記述できます。
INDEX(範囲, 行番号)
INDEX(範囲, 列番号)
引数一覧
| 引数 | 省略 | 説明 |
| 範囲 | 必須 | 対象となる範囲のセルを指定します。 もしくは配列形式で指定します。 複数指定する事が可能です。 |
| 行番号 | 省略可能 | 範囲が1行の場合のみ省略可能です。 範囲内の取得したい行を指定します。 省略した場合は必ず列番号を指定します。 |
| 列番号 | 省略可能 | 範囲が1列の場合のみ省略可能です。 範囲内の取得したい列を指定します。 省略した倍は必ず行を指定します。 |
3.サンプルコード
次のような単純な番号が連続して入力されているデータを、複数用意したデータを使用します。
基本的なサンプルコード
1つ目の表の2行目の3列目を交差する値を取得します。
Sub Sample1()
MsgBox Application.WorksheetFunction.Index(Range(Cells(2, 2), Cells(4, 5)), 2, 3)
End Sub
変数に入れて範囲指定するサンプルコード
上記のコードでは範囲をそのまま指定していますが、長くなりますので変数に格納することでシンプルになります。
Sub Sample2()
Dim MyRng As Range
Set MyRng = Range(Cells(2, 2), Cells(4, 5))
MsgBox Application.WorksheetFunction.Index(MyRng, 2, 3)
End Sub
ループで取得するサンプルコード
行と列をループで１つずつ移動させた結果を取得します。
Sub Sample3()
Dim MyRng As Range
Dim MyStr As String
Dim i As Long
Dim n As Long
Set MyRng = Range(Cells(2, 2), Cells(4, 5))
n = 2
For i = 1 To 3
MyStr = MyStr & Application.WorksheetFunction.Index(MyRng, i, n) & vbCrLf _
n = n + 1
Next i
MsgBox MyStr
End Sub
コードの説明
「Set MyRng = Range(Cells(2, 2), Cells(4, 5))」で範囲を指定しています。
「n = 2」で列の指定を2から始めています。
「For i = 1 To 3」で1行目～3行目を指定します。
「Application.WorksheetFunction.Index(MyRng, i, n)」で「i」と「n」で行と列を加算しながら指定しています。
