複数行の値を返すvlookup関数をVBAのユーザー定義関数で作る

公開日: 一般的な計算

vlookup関数を使うと、条件に一致する行が複数ある場合、一番上の行しか抽出できません。
そこで、条件に一致する行「すべて」を抽出できるようなユーザー定義関数を作ります。

vlookup関数を使うと1行しか抽出できない

たとえば、次のように、請求明細から請求書に自動転記をすることを考えます。

arrayvlookup_1_1

このような転記をする場合、真っ先に思い浮かぶのがvlookup関数だと思います。

ところが、vlookup関数を使うと、同じNoの行が複数ある場合には、最初の行しか転記することができません。

arrayvlookup_1_2

条件に合う複数行を転記できるユーザー定義関数を作る

エクセルには、このような条件に合う複数の行を転記できるような関数はないので、自力でarrayvlookupという関数を作ってしまいます。

この関数を使うと、次のように、該当する行が複数行でも全て転記することができます。

arrayvlookup_1_3

この関数を入力する方法

この関数は、通常の関数とは少し入力方法が違います。
いわゆる「配列数式」と同じように入力をします。

1.同じ数式を入力したい範囲(=縦1列)を選択

まず、最初に同じ数式を入力したい範囲を選択します。

今回のarrayvlookup関数の場合は、縦1列に同じ数式を入力しますので、縦1列を選択します。

arrayvlookup_1_4
2.数式を入力します

実際に数式を入力します。
書式は、通常のvlookup関数とまったく一緒です。

arrayvlookup_1_5
3.Ctrl+Shift+Enterを押す

数式を入力し終わったら、数式を確定させるのですが、通常の数式の場合とは違い「Ctrl」、「Shift」の両方のキーを押しながら「Enter」キーを押します。

arrayvlookup_1_6

これで、arrayvlookup関数の入力ができました。

数式を変更したいとき

数式を変更したいときには、次のようにします。
たとえば、B3セル~B5セルの数式をC3セル~C5セルにコピーすると、正しい数式になりません。

arrayvlookup_1_7

そこで、C3セル~C5セルの数式を変更してみようと思います。

1.同じ数式を入力したい範囲(=縦1列)を選択

同じ数式が入力されている「範囲」を選択します。
今回のarrayvlookup関数の場合は、縦1列に同じ数式が入力されていますので、縦1列を選択します。

arrayvlookup_1_8
2.数式を編集する

F2キーを押すか、数式バーをクリックすると数式を編集できます

arrayvlookup_1_9
3.Ctrl+Shift+Enterを押す

数式を編集し終わったら、入力時と同様に「Ctrl」、「Shift」の両方のキーを押しながら「Enter」キーを押します。

arrayvlookup_1_10

これで、arrayvlookup関数が入っているセルの修正ができました。

数式を削除したいとき

数式を変更したいときには、次のようにします。

1.同じ数式を入力したい範囲(=縦1列)を選択

同じ数式が入力されている「範囲」を選択します。
今回のarrayvlookup関数の場合は、縦1列に同じ数式が入力されていますので、縦1列を選択します。

たとえば、C3セル~C5セルを選択すると次のようになります。

arrayvlookup_1_11
2.数式を削除する

Delキーを押す、あるいは右クリックから「数式と値のクリア」を選択すると、数式を削除できます。

arrayvlookup_1_12

arrayvlookup関数-VBAソースコード

arrayvlookup関数のソースコードは次のとおりです。
下記ソースコードを、標準モジュールに貼り付けてください。

Function ARRAYVLOOKUP(Key As Variant, R As Range, C As Long, _
                      Optional T As Boolean = True, _
                      Optional maxretY As Long = 20) As Variant
  '検索の型はTRUEを指定しても動作は変わらない
  Dim ret As Variant
  ReDim ret(1 To maxretY, 1 To 1) As Variant
  
  Dim targetR As Range
  Set targetR = Intersect(R, R.Worksheet.UsedRange)

  Dim Data As Variant
  Data = targetR
  
  Dim y As Long
  Dim retY As Long
  retY = 1
 
  For y = LBound(Data) To UBound(Data)
    If Data(y, 1) = Key Then
      ret(retY, 1) = Data(y, C)
      retY = retY + 1
      If retY > maxretY Then
        Exit For
      End If
    End If
  Next
    
  If retY > 1 Then
    For y = retY To maxretY
      ret(y, 1) = ""
    Next
  Else
    ret(1, 1) = CVErr(xlErrNA)
  End If
  
  ARRAYVLOOKUP = ret
End Function

関数の引数について

基本的にはvlookup関数と同じ感覚で使えるのですが、vlookup関数と違う点が2つあります。

4つ目の引数:検索の型をTRUEにしても動きは変わらない

4つめの引数(検索の型)をTRUEにしても、arrayvlookup関数の動作は変わりません。
vlookup関数の近似値検索のようなことはできませんので、ご注意ください。

5つ目の引数:結果を返す最大行数を指定する(初期値20)

arrayvlookup関数には、vlookup関数にはない5つ目の引数があります。
5つ目の引数は、arrayvlookup関数で返す最大の行数を指定できます。

省略した場合には最大行数は20に設定されます。この場合、arrayvlookup関数を20行超の範囲に入力すると、21行目以降がエラーになってしまいます。

arrayvlookup_1_13

もっと、多くの行数を表示したい場合には、次のように5つ目の引数を指定するか、VBAのソース自体を書き換えてください。

=ARRAYVLOOKUP(A3,$A$33:$G$39,2,FALSE,50)

ソースコードの解説

以下、簡単にソースコードの解説をしていきます。

なお、ソースコードを理解しないでもarrayvlookup関数は使えます
難しいと思ったら読み飛ばしてください。

1.返り値用配列の確保
 

  Dim ret As Variant
  ReDim ret(1 To maxretY, 1 To 1) As Variant 

返り値を入れる用の変数(ret)を準備します。

普通は、返り値は関数名(arrayvlookup)と同名の変数に代入して終わりです。
ところが、今回のように複数セル分の返り値を格納する場合には、いったん別の変数を経由してarayvlookupに格納しないといけません(そういう仕様のようです)。

そこで、retという変数を準備します。そして、いったんVariant形で変数を宣言した後に、2次元配列として変数を再宣言します。

maxretYは5つめの引数で指定する値なのですが、省略した場合には20が設定されるようになっています。
結果的に、変数retは通常は20行1列分の2次元配列となります。

2.処理対象となる範囲(range)の設定
 

  Dim targetR As Range
  Set targetR = Intersect(R, R.Worksheet.UsedRange)

処理対象となる「範囲」を設定します。

通常は、2つめの引数で受け取った変数Rを使えば問題はありません。
ただし「$B:$C」や「$B1:$C65535」といった、多数の行を含む引数を指定された場合に、後続処理で時間がかかってしまいます。

そこで、2つめの引数と、実際に使われている領域(UsedRange)に共通する部分(intersect)を、変数targetRに入れます。

たとえば、

  • 2つめの引数(変数R)の内容が「$B:$C」
  • UsedRangeが「$A1:$E999」

だとすると、変数targetRには、その共通部分である「$B$1:$C$999」が格納されます。

ここから後の処理は、targetRについてのみ行います。

3.配列に処理対象となる範囲の値を入れる
 

  Dim Data As Variant
  Data = targetR  

エクセルでは、セルの内容を見るという処理には非常に時間がかかります。
そこで、ここで、セルの内容を一気に、Dataという変数に取り込んでしまいます。

この文だけ見ると非常にわかりにくいですが、結果としてDataは2次元配列になります。

たとえば、targetRが「$B$1:$C$999」だとすると、Dataは999行×2列の2次元配列になります。

イメージとしては、Dataは「dim Data(1 to 999,1 to 2)」として変数を作ったのと、まったく同じ状態になると思ってください。

そして、その配列に、targetRで指定したセルの内容が格納されます。
たとえば、

  • B1セルの内容は「Data(1,1)」
  • B2セルの内容は「Data(2,1)」
  • C2セルの内容は「Data(2,2)」

にそれぞれ格納されます。

4.配列からデータを読み出し、返り値を格納
 

  retY = 1
 
  For y = LBound(Data) To UBound(Data)
    If Data(y, 1) = Key Then
      ret(retY, 1) = Data(y, C)
      retY = retY + 1
      If retY > maxretY Then
        Exit For
      End If
    End If
  Next

ここが、今回の処理の中心部分です。

Forループで、Data(1,1)、Data(2,1)、Data(3,1)と順番に、内容を確認していきます。
もし、その中で1つめの引数(変数key)と等しいものがあった場合には、返り値(変数ret)に3つめの引数(変数C)で指定した列のデータを格納していきます。

たとえば、「=arrayvlookup(A3,$A$11:$G$17,2,FALSE)」という数式が入力された場合を考えてみます。

仮にData(1,1)とA3セルの内容(変数Key)が等しい場合、3つめの引数(変数C)には「2」が指定されています。
ですから「Data(1,2)」の内容を変数retに格納することになります。

実際には、変数retは配列なので、もう少し複雑な処理をしていますが、複雑になるので説明は省略します。

5.返り値の微調整
 

  If retY > 1 Then
    For y = retY To maxretY
      ret(y, 1) = ""
    Next
  Else
    ret(1, 1) = CVErr(xlErrNA)
  End If

変数retは(5つ目の引数をしてしない場合)最大で20行分の値を格納できます。

たとえば、変数keyに該当する行が2行しかなかった場合、変数retの残り18行分は何も処理されないままになってしまいます。
そこで、その場合には、明示的に「””」(=空欄)を格納します。

なお、retY>1でない場合(=1行も変数keyに該当する行がなかった場合)には、vlookupと同じように、返り値の初期値を「#N/A」に設定しておきます。

6.返り値の設定
 

  arrayvlookup = ret

変数retの内容を返り値格納用変数arrayvlookupに代入します。

まとめ

vlookup関数で条件に一致する複数行の値が欲しいときには、ユーザー定義関数を作りましょう。

なお、この関数は「配列数式」と同じように、入力方法がやや特殊です。数式入力時に「Ctrl+Shift+Enter」と押す必要がありますので、気をつけてください。


  • twitter
  • facebook
  • はてなブックマーク

経理事務のためのエクセル基礎講座動画マニュアル無料配布中

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!