INDEX MATCH関数をVBAで表現
INDEX MATCH関数は、VLOOKUP関数と似ていますが、より高度なことが出来る関数です。
前半では、VLOOKUP関数とINDEX MATCH関数の使い方を解説します。
後半は、INDEX MATCH関数をVBAで表現します。
VBAを触ったことが無い方でもこの記事だけで再現できるようにしてあります。
具体的な例題として、
上の売上表のお寿司の「名称」に対する、「コード」と「単価」を右のマスター表から取得し、売上表を埋めます。
VLOOKUP関数
VLOOKUP関数は、コード番号と商品名の一覧表があるとしたら、コード番号から商品名を取得することに使われることが多いです。VLOOKUPのVはViarticalつまり縦を意味しますので、一覧表を縦に検索していきます。
A3セル名称「サーモン」から、その単価を右のマスター表から取得するには、E3セルに次のように入力します。
=VLOOKUP(A3,I3:J12,2,FALSE)
A3が検索値、I3:J12が参照範囲、2が参照範囲の列番号になります。FALSEは完全一致を意味します。
つまり、参照範囲のA3サーモンに一致する行の2列目のセルの値を取得していることになります。
このように、右のマスターからサーモンに対する単価を取得できました。
I3:J12を選択した状態でF4キーをクリックしますと、＄記号がセルの行と列番号の頭につきます。
これは、コピーして他のセルに貼り付けても、参照範囲が変更されることがありません。
下の行にもコピーペーストします。
最下行のマグロのセルを確認しますと、検索値はA11に変更されていますが、参照範囲は、$I$3:$J$12で固定されていることが確認できます。
次に、
名称「サーモン」から、そのｺｰﾄﾞを右のマスター表から取得するには、どうするか？ですが、
残念ながらVLOOKUP関数は使えません。
VLOOKUP関数は、参照範囲の一番左の列を縦に探索して、参照範囲内の列の値を取得する関数ですので、
参照範囲外つまり参照範囲の左側にあるセルの情報を取得することはできません。
INDEX MATCH関数
そこでINDEX MATCH関数を使います。
D3セルに次のように入力します。
=INDEX(H3:J12,2,1)
H3:J12が参照範囲、2が行番号、1が列番号になります。
参照範囲の中の2行目の1列目の値を取得していることになります。
このように、右のマスターからサーモンに対するコードS002を取得することができました。
この行番号の2ですが、自動的に行番号を得る必要がありますので、この行番号の2を得るために、MATCH関数を使います。
このように2を書き換えます。
=INDEX(H3:J12,MATCH(A3,I3:I12,0),1)
2を MATCH(A3,I3:I12,0)に書き換えました。
A3が検索値、I3:I12が検索範囲、0が完全一致となります。
=MATCH(検索値, 検索範囲, [照合の種類])
検索範囲のA3に一致するセルが検索範囲の何行目にあるかを取得していることになります。
MATCH関数の照合の種類には、完全一致検索の場合、0を入れます。
こちらもコピーして、下の行に貼り付けて利用しますので、＄記号をつけます。
=INDEX($H$3:$J$12,MATCH(A3,$I$3:$I$12,0),1)
このようにINDEX,MATCH関数は、VLOOKUP関数と似ていますが、より高度なことが出来る関数だと思います。次に、VBAで VLOOKUP関数、INDEX,MATCH関数を表現します。
VBAで VLOOKUP関数、INDEX,MATCH関数を表現
キーボードのAltキーを押しながらF11キーを押してください。VBAのエディターが起動します。
もし、VBAのエディターが最大表示されていたら、右上の縮小をクリックして、縮小し、シートと並べて表示することをお薦めします。挿入、標準モジュールをクリックしてください。Module1上にプログラムコードを記載していきます。
|
1 2 3 4 5 6 7 8 9 10 |
Private Sub AAA() Dim i, j As Integer i = 3 For j = 3 To 12 If Cells(i, 1) = Cells(j, 9) Then Cells(i, 4) = Cells(j, 8) Cells(i, 5) = Cells(j, 10) End If Next j End Sub |
Dim i, j As Integer 変数を二つ宣言します。
iは、売上表内の行番号を格納します。
jは、マスター表内の行番号を格納します。
もし、エディターが非表示になったら、Altキーを押しながらF11キーを押せば、再表示されます。
売上表の3行目の「サーモン」に対する「コード」と「単価」を取得したいので、i=3とします。
右三角ボタンを押して、プログラムを実行します。
「名称」サーモンに対する「コード」S002と「単価」100を取得することが出来ました。
「サーモン」に一致する名称を、マスター3行目から12行目までの9列目を検索して、j行目で一致したら、j行目の8列目のｺｰﾄﾞと10列目のお値段を取得し、それぞれを売上のi行目の4列目と5列目に格納していることになります。
売上の3行目の「サーモン」から11行目の「マグロ」まで、同じ処理をしたいので、for next 文を外側にもう一つ書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Private Sub AAA() Dim i, j As Integer i = 3 For i = 3 To 11 For j = 3 To 12 If Cells(i, 1) = Cells(j, 9) Then Cells(i, 4) = Cells(j, 8) Cells(i, 5) = Cells(j, 10) End If Next j Next i End Sub |
右三角ボタンを押して、プログラムを実行すると、コードと単価を右のマスターから取得できていることが確認できます。
最後に、売上の行数が増えた場合、都度 for i = 3 to 11 の 11を書き換えるのもスマートではないです。
プログラムを書き換えます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
For i = 3 To 100 If Cells(i, 1) = "" Then Exit For End If For j = 3 To 12 If Cells(i, 1) = Cells(j, 9) Then Cells(i, 4) = Cells(j, 8) Cells(i, 5) = Cells(j, 10) End If Next j Next i |
変数iは3～100まで1づつ加算されていきますが、
i行目の1列目が空白だったら、forのループからEXIT 抜け出します。
このようにすれば、売上表が100行目まで増えても、プログラムを書き換える必要はありません。
VBAを有効にしたエクセルファイルを保存するときは、ファイル、名前を付けて保存、Excelマクロ有効ブック(*.xlsm)を選択し、好きなファイル名で保存してください。
ご拝読ありがとうございました。
