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


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

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

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

arrayvlookup_1_1

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

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

arrayvlookup_1_2

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

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

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

arrayvlookup_1_3

この関数を入力する方法

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

１．同じ数式を入力したい範囲（＝縦１列）を選択

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

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

arrayvlookup_1_4
２．数式を入力します

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

arrayvlookup_1_5
３．Ctrl+Shift+Enterを押す

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

arrayvlookup_1_6

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

数式を変更したいとき

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

arrayvlookup_1_7

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

１．同じ数式を入力したい範囲（＝縦１列）を選択

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

arrayvlookup_1_8
２．数式を編集する

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

arrayvlookup_1_9
３．Ctrl+Shift+Enterを押す

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

arrayvlookup_1_10

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

数式を削除したいとき

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

１．同じ数式を入力したい範囲（＝縦１列）を選択

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

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

arrayvlookup_1_11
２．数式を削除する

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

arrayvlookup_1_12

arrayvlookup関数-VBAソースコード

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

Function ARRAYVLOOKUP(Key As Variant, R As Range, C As Long, _
                      Optional T As Boolean = True) As Variant
'検索の型はTRUEを指定しても動作は変わらないことに注意！
  Dim ret As Variant

  Dim maxretY As Long

  If TypeName(Application.Caller) = "Range" Then
    maxretY = Application.Caller.Rows.Count
  Else
    maxretY = 1
  End If
  
  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) Like 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関数と違う点があります。

1つ目の引数：ワイルドカードの仕様が違う

1つ目の引数にはワイルドカードが使えるのですが、次のような仕様になっており、ワイルドカードの仕様がvlookup関数とは違うのでご注意ください。

? 任意の1文字
* 0文字以上の任意の文字
# 半角数字
[AC] AまたはC
※[ ]の中の文字を変更することで、任意の文字を指定できます
[!AC] AとC以外の文字
※[! ]の中の文字を変更することで、任意の文字以外の文字を指定できます
4つ目の引数：検索の型をTRUEにしても動きは変わらない

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

ソースコードの解説

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

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

１．返り値用配列の確保
  Dim ret As Variant

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

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

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

２．返り値の行数の設定
  Dim maxretY As Long

  If TypeName(Application.Caller) = "Range" Then
    maxretY = Application.Caller.Rows.Count
  Else
    maxretY = 1
  End If
  
  ReDim ret(1 To maxretY, 1 To 1) As Variant

「Application.Caller」を使うと、呼び出し元のセル範囲が分かります。

そこで、「Application.Caller」の内容に応じて、変数ret（＝返り値を一時格納する変数）の配列長を決めています。

３．処理対象となる範囲（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についてのみ行います。

４．配列に処理対象となる範囲の値を入れる
  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)」

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

５．配列からデータを読み出し、返り値を格納
  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

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

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

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

3つめの引数（変数C）には「2」が指定されています。

ですから、仮に、Data(1,1）とA3セルの内容（変数Key）が等しい場合には、「Data(1,2)」の内容を変数retに格納することになります。

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

６．返り値の微調整
  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行も変数keyに該当する行がなかった場合）には、vlookupと同じように、返り値の初期値を「#N/A」に設定しておきます。

７．返り値の設定
  arrayvlookup = ret

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

まとめ

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

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

※2018/4/2ソース修正

エクセル基礎講座 「無料」動画マニュアル

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

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

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

無料動画講座　登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。

VBAvba, vlookup関数