エクセルVBAでのvlookup関数の使い方

質問

エクセルでvlook up関数を用いたVBAを書こうと思っているのですが上手くいきません。
詳しい方がいらっしゃいましたら教えて頂けませんでしょうか。
使用しているエクセルは2010です。

いろいろ調べながら書いてみたのですが、繰り返しの処理かエラー処理が悪いようでVBAを実行することが出来ませんでした。

シート１にあるコマンドボタンを押すことで別シート（シート２、３）に入力されている値をもってくるというVBAを考えています。

Sheet1（取扱商品）にはA列に商品番号、B列に商品名、C列に管理者が入力されています。
D～H列には属性１～５というヘッダーだけがあり、コマンドボタンを押すことで、別シートから商品の属性が入ります。

Sheet1（取扱商品）
商品番号　商品名　管理者　属性１　属性２　属性３　属性４　属性５
００１　　桜　　　安部
００２　　薔薇　　安部
００２　　薔薇　　伊藤
００４　　梅　　　上田
００７　　水仙　　伊藤
０１０　　牡丹　　榎本
．．．（以下続く）

Sheet2（データベース１）
商品番号　属性１　属性４
００１　　Ａ１　　Ａ４
００２　　　　　　Ｂ４
００４　　Ｄ１　　　　
００８　　Ｈ１　　Ｈ４
．．．（以下続く）

Sheet3（データベース２）
商品番号　属性２　属性３　属性５
００１　　Ａ２　　　　　　Ａ５
００３　　Ｃ１　　Ｃ３　　Ｃ５
００４　　　　　　Ｄ３
０１１　　Ｋ１　　Ｋ３
．．．（以下続く）

各シートの商品番号は１対１で対応していません。
Sheet1は商品番号が重複する場合があります。Sheet2、3は重複しません。

コマンド実行後のイメージは下記のような形です。
対応する商品番号、属性がない場合はnull入るようにしたいと思っています。

Sheet1（取扱商品）
商品番号　商品名　管理者　属性１　属性２　属性３　属性４　属性５
００１　　桜　　　安部　　Ａ１　　Ａ２　　　　　　Ａ４　　Ａ５
００２　　薔薇　　安部　　　　　　　　　　　　　　Ｂ４　　　　
００２　　薔薇　　伊藤　　　　　　　　　　　　　　Ｂ４　　　　
００４　　梅　　　上田　　Ｄ１　　　　　　Ｄ３　　　　　　　　
００７　　水仙　　伊藤　　　　　　　　　　　　　　　　　　　　
０１０　　牡丹　　榎本　　　　　　　　　　　　　　　　　　　　
．．．（以下続く）

要領が得ないところがあるかと思いますが、皆様のお知恵を拝借致したく、教えて頂ければ幸いです。
どうかよろしくお願い致します。

回答No.7

kagakusuki · ベストアンサー

>先日は気づかず流してしまったのですが、IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D)
>をのみを実行すると、#VALUEエラーとなってしまいます。

　まず、

COUNTIF(Sheet2!$B$1:$C$1,D$1)

の部分は、Sheet2!B1:C1の範囲（Sheet2において「属性１」と「属性４」という項目名が入力されている範囲）に、D1セルに入力されている「属性１」という項目名が幾つ含まれているかをカウントしています。
　IF関数等では、判定式の部分の計算結果が数値の0となった場合には、数値の0をFALSEと同じものとして扱い、「偽の場合」の処理が行われます。（VLOOKUP関数等の「検索方法」のFALSEも同様）
　そして、判定式の部分の計算結果が0以外の数値（負の数や小数値も含まれます）となった場合には、その0以外の数値をTRUEと同じものとして扱い、「真の場合」の処理が行われます。
　つまり、

IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),

で始まるIF関数をD列のセルに入力するという事は、(D$1は列番号の指定が相対参照、行番号の指定が絶対参照となっているのですから)
「その関数が入力されている列の1行目のセルに入力されている値（この場合は項目名）と同じ値が、Sheet2の項目名が入力されている欄の中に、1つでも存在した場合には、『真の場合』の処理を行い、1つも存在しなかった場合には、『偽の場合』の処理を行う」
という関数となります。
　そして、「真の場合」の所にはSheet2!$A:$Cの列範囲が、「偽の場合」の所にはSheet3!$A:$Dの列範囲が指定されているのですから、「1行目のセルに入力されている項目名と同じ項目名が、Sheet2の項目名の中に存在した場合」には、

VLOOKUP($A2,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D),MATCH(D$1,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A$1:$C$1,Sheet3!$A$1:$D$1),0),FALSE)

のVLOOKUP関数の検索範囲として、Sheet2!$A:$Cの列範囲が指定される事になります。
　同様に、「1行目のセルに入力されている項目名と同じ項目名が、Sheet2の項目名の中に存在した場合」には、VLOOKUP関数の検索範囲として、Sheet3!$A:$Dの列範囲が指定される事になります。
　要するに、IF関数を使って単独のセルではなく、セル範囲や列範囲を指定している訳です。

お礼

ご回答ありがとうございます。
毎回丁寧に教えて頂き、とても勉強になります。

If文では0を偽の場合として処理をするということを知り驚きです。
ご説明頂いて、記載されていた構文で範囲が指定されるという理解がよく分かりました。
とても自分では思いつくことも出来ない考えでした。

自分でもそういった数式を組めるように勉強したいと思います。
VBAに関してはもっと基本的なところを学び直した方が良さそうですが。。

本当にありがとうございました！

回答No.6

keithin

＞Vlookup関数の最後の&"""は値を文字列化しているのでしょうか

マクロ以前にワークシート上でＶＬＯＯＫＵＰ関数を使う際の知識の問題ですが，ＶＬＯＯＫＵＰ関数をFALSEの設定で計算させると，次の３通りの結果が現れます。

１）検索値（たとえば００１）が対象の左端列に見つかり，かつ検索結果として実際にデータが記入されている場合
　→見つかった値（たとえばＡ１）が計算されます

２）検索値（たとえば００２）が対象の左端列に見つかり，しかし検索結果がデータベース１の００２の属性１のように空欄の場合
　→関数の計算結果はゼロになります

３）検索値（たとえば００３）が対象の左端列に見つからない場合
　→関数の計算結果はエラー値になります

次に，回答したマクロの後半部分をコメントアウトして「マクロに記入させた計算式の実際の内容と，それぞれの計算結果」をキチンと目で見て確認し，VLOOKUP関数の動作を理解して下さい。

たとえば
=VLOOKUP(A2,Sheet2!A:C,2,FALSE)&""
のように記入された関数式は，

１）結果があればＡ１のようにそれをを表示します
２）結果が空欄の場合，上述ではゼロになるとお話ししましたが，&""の作用によって空白が計算されます
３）検索値がそもそもシート２や３に無ければ，#VALUEエラーが計算されています

１）の結果も，確かに＆””の作用によって文字化されています。しかし元のご相談の例示のように対象データが「Ａ１」のように文字列であれば，何も変わりありません。
実はご相談の例示がウソでＡ１とかの部分に数値が入ってるのもあったんです，という場合は確かに数値が文字化されてしまうので，別途の考慮が必要となります。

＞.value = .value

回答したマクロでは，前半部分でそれぞれの列にVLOOKUP関数を投入して計算させ，後半部分では
・ご質問の直接の回答として，VLOOKUP関数の計算結果を「その場で値化する」
・エラー値が計算された(検索値がデータベースに記載が無かった)セルをクリアする
という処置を行っています。

お礼

ご回答ありがとうございます。
お礼が遅くなりまして申し訳ありません。

普段、エクセル関数としてvlookupを使う時は確かにIF文やIserrorを使いながら不要な値をnull化していました。
数値を文字列化するのに&""を用いれば良いということは初めて知りました。
いつもはtrim関数でとりあえずしのいでいました。

丁寧なご回答ありがとうございました。
大変参考になりました。
これからもっと勉強していきたいと思います！

回答No.5

kagakusuki

因みに、回答No.2において私が提示させて頂いた関数では、参照先が空欄となっていた場合には

ISERROR(1/(VLOOKUP($A2,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D),MATCH(D$1,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A$1:$C$1,Sheet3!$A$1:$D$1),0),FALSE)<>"")

の部分の処理結果が

ISERROR(1/(VLOOKUP($A2,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D),MATCH(D$1,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A$1:$C$1,Sheet3!$A$1:$D$1),0),FALSE)<>""))
　　↓
ISERROR(1/(""<>""))
　　↓
ISERROR(1/(FALSE))
　　↓
ISERROR(1/0)
　　↓
ISERROR(#DIV/0!)
　　↓
TRUE

となりますので、IF関数と組み合わせる事で、参照先が空欄の時には、結果が0ではなく、空欄となる様にしています。
　それと同時に、Sheet1のA列に入力されている商品番号の中に、万が一、Sheet2のA列か、Sheet3のA列に入力し忘れていたものがあった場合には、

ISERROR(1/(VLOOKUP($A2,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D),MATCH(D$1,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A$1:$C$1,Sheet3!$A$1:$D$1),0),FALSE)<>"")

の部分の処理結果が

ISERROR(#N/A)
　　↓
TRUE

となりますので、IF関数と組み合わせる事で、空欄となる様にしています。

お礼

丁寧なご回答ありがとうございます。
すごく勉強になります！！

Match関数にそういった使い方があることを初めて知りました。
また、普段使う.End(xlUp).Rowにそんな障害があるのにビックリです。

先日は気づかず流してしまったのですが、IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D)
をのみを実行すると、#VALUEエラーとなってしまいます。

VLOOKUP($A2,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D),MATCH(D$1,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A$1:$C$1,Sheet3!$A$1:$D$1),0),FALSE)
の内容からsheet2または3で範囲をしていると思うのですが、そこで理解が止まってしまいました。

もちろんvlookup全体ではしっかり実行出来るため、余計わからなくなってしまいました。

もし宜しければ、教えて頂けませんでしょうか。

回答No.4

kagakusuki

ついでに他の回答者様の御回答に対する疑問についても回答しておきます。

>Vlookup関数の最後の&"""は値を文字列化しているのでしょうか。

　VLOOKUP関数等の、他のセルの値を参照する関数を使用した際に、もしも、参照先のセルが空欄となっていた場合には、そのセルを参照した結果は、空欄とはならずに、数値の0が表示されます。
　この現象を防いで空欄とするために、VLOOKUP関数の末尾に

&""

を付けている訳です。
　因みに、「VLOOKUP関数&""」のままでは、参照したデータが数値の場合でも文字列データに変換されてしまいますが、

.value = .value

の処理が行われた段階で、元の数値データに戻ります。

回答No.3

kagakusuki

回答No.2です。

　済みません、動作に直接関わる事ではないため見落としておりましたが、下から2行目の所にある

Range("D3").Select

という部分は、マクロを組んで行く際に消し忘れたゴミですので、削除して下さい。

>検索範囲が広くなるとファイルサイズが大きくなりやすことと、検索した属性値に、例外があった場合には内容の修正が効くようにしたいと考えたためです。

　そういう事でしたら、回答No.2のマクロは単に回答No.2のワークシート関数を入力するだけのものですので、そのマクロの

End Sub

の直前の所に、

Sheets("SHeet1").Range("D2:H" & LR).Value = Sheets("SHeet1").Range("D2:H" & LR).Value

という1行を追加して、最後に同じ範囲の値のみを貼り付ける操作が行われる様にして頂く様御願い致します。

>""*?""は何を示しているのでしょうか。

　"*"も"?"も、どちらも曖昧検索を行う際に使用するワイルドカード文字です。

【参考URL】
　ワイルドカード文字 - Excel - Office.com
　　http://office.microsoft.com/ja-jp/excel-help/HP005203612.aspx

　* (アスタリスク) は任意の数の文字を表し、? (疑問符) 任意の1文字を表しています。
　つまり"*?"は「1文字以上の文字から成る何らかの文字列」を意味しています。
　MATCH関数は

MATCH(検査値, 検査配列, [照合の型])

という形式で記述される関数で、称号の型に「-1」を指定しますと、検査配列で指定されているセル範囲の中のデータが降順に並べられている場合において、検査値以上の最小の値が入力されているセルの位置が返されます。
　それでは、検査配列で指定されているセル範囲の中のデータの並び方が、降順となってはいない場合にはどうなるかと言いますと、通常は#N/Aエラーとなります。
　ところが、検査値として「検査配列で指定されているセル範囲の中の最小値」よりも小さな値を指定した場合には、#N/Aエラーとはならずに、後ろ（セル範囲が縦方向なら下、横方向なら右の）から順に調べて行って「検査値以上の値が（後ろから見て）最初に現れた位置」即ち、検査値として数値を指定した場合には、最も後ろに数値が入力されているセルの位置が返されます。
　同様に、検査値として「検査配列で指定されているセル範囲の中の最も小さい文字列」よりも小さい文字列を指定した場合には、最も後ろに文字列が入力されているセルの位置が返されます。
　尚、文字列の場合の大小とは、おおむね使われている文字コードの大小によって決まってきます。
　そして、何故なのかは知りませんが、"*?"は「1文字以上の文字から成る大抵の文字列」よりも小さな文字列として扱われるらしく、

=MATCH("*?",検査配列,-1)

としますと、検査配列の中で最後に文字列が入力されているセルの位置を求める事が出来ます。（但し、文字コードが20以下のものは除きます）
　因みに、照合の型に「1」を指定するか、照合の型を省略しますと、検査値以下の最大の値が検索されますから、検査値として9E+307(Excelで取り扱う事の出来る数値は10の307乗の桁まで。尚、9.9E+307は9E+307よりも大きな数値として扱われます)を指定しますと、検査配列の中で最後に数値が入力されているセルの位置を求める事が出来ます。

　VBAマクロでデータが入力されている中での最終行を求める際に、良く

.End(xlUp).Row

が使われますが、その方法で最終行を求め様とした場合、現在においてデータが入力されている最後の行よりも下に、過去において関数や値が入力された事のあるセルが存在していた場合には、現時点でデータが入力されているか否かに関わらず、データが入力されていた事があるセルが存在する行の位置を求めてしまう場合があるのです。
　そのため私は、

.End(xlUp).Row

は、なるべく使わずに、Match関数を使って最終行を求める様にしているのです。

回答No.2

kagakusuki

それはワークシート関数のVLOOKUP関数をセルに入力したのでは駄目なのでしょうか？
　例えば次の関数をSheet1のD2セルに入力してから、Sheet1のD2セルをコピーして、Sheet1のD2～「Sheet1の表中で最も下の行のH列のセル」のセル範囲に貼り付けてますと、御望みの結果が得られると思います。

=IF(ISERROR(1/(VLOOKUP($A2,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D),MATCH(D$1,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A$1:$C$1,Sheet3!$A$1:$D$1),0),FALSE)<>"")),"",VLOOKUP($A2,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A:$C,Sheet3!$A:$D),MATCH(D$1,IF(COUNTIF(Sheet2!$B$1:$C$1,D$1),Sheet2!$A$1:$C$1,Sheet3!$A$1:$D$1),0),FALSE))

　或いは、下記のVBAを使われるとか

Sub Macro1()

Dim LR As Long
LR = Application.Evaluate("MAX(IF(COUNT(Sheet1!A:A),MATCH(9E+307,Sheet1!A:A),0),IF(COUNTIF(Sheet1!A:A,""*?""),MATCH(""*?"",Sheet1!A:A,-1),0))")
Sheets("SHeet1").Range("D2:H" & LR).FormulaR1C1 = _
"=IF(ISERROR(1/(VLOOKUP(RC1,IF(COUNTIF(Sheet2!R1C2:R1C3,R1C),Sheet2!C1:C3,Sheet3!C1:C4),MATCH(R1C,IF(COUNTIF(Sheet2!R1C2:R1C3,R1C),Sheet2!R1C1:R1C3,Sheet3!R1C1:R1C4),0),FALSE)<>"""")),"""",VLOOKUP(RC1,IF(COUNTIF(Sheet2!R1C2:R1C3,R1C),Sheet2!C1:C3,Sheet3!C1:C4),MATCH(R1C,IF(COUNTIF(Sheet2!R1C2:R1C3,R1C),Sheet2!R1C1:R1C3,Sheet3!R1C1:R1C4),0),FALSE))"
Range("D3").Select

End Sub

お礼

二通りの方法を教えていただき、有難うございます。

直接セルに数式を入力することは、私も考えたのですが、
検索範囲が広くなるとファイルサイズが大きくなりやすことと、
検索した属性値に、例外があった場合には内容の修正が効くようにしたいと考えたためです。

ご回答頂いたVBAですが、普段、相対参照をすることがあまり無かったので、勉強になりました。
完全には理解しきれていないところがありますが。。

1点教えて頂けますでしょうか。
""*?""は何を示しているのでしょうか。
素人の質問でしたら大変申し訳無いのですが、調べてみてもよく理解が出来ませんでした。

宜しくお願い致します。

回答No.1

keithin

作成例：
private sub CommandButton1_Click()
　dim lastrow as long
　lastrow = worksheets("Sheet1").range("A65536").end(xlup).row

　with worksheets("Sheet1")
　.range("D2:D" & lastrow).formula = "=VLOOKUP(A2,Sheet2!A:C,2,FALSE)&""""
　.range("E2:E" & lastrow).formula = "=VLOOKUP(A2,Sheet3!A:D,2,FALSE)&""""
　.range("F2:F" & lastrow).formula = "=VLOOKUP(A2,Sheet3!A:D,3,FALSE)&""""
　.range("G2:G" & lastrow).formula = "=VLOOKUP(A2,Sheet2!A:C,3,FALSE)&""""
　.range("H2:H" & lastrow).formula = "=VLOOKUP(A2,Sheet3!A:D,4,FALSE)&""""

　with .range("D2:H" & lastrow)
　　.value = .value
　　on error resume next
　　.specialcells(xlcelltypeconstants, xlerrors).clearcontents
　　on error goto 0
　end with
　end with

end sub

＃何を間違えてて出来なかったのか不明ですが、出来ないのを無理に使っても、結局遅いだけです。それに出来る事で実現したほうが、遥かに簡単です。

お礼

お礼が遅くなりました。
早速のご教示有難うございます。

Rangeからの範囲指定と繰り返しの処理の仕方がすごく勉強になりました。
I = 1 のように書かなくても良いのですね。
エラー処理あたりの後半の内容も目から鱗でした。
大変参考になりました。

再度の質問で恐縮ですが、2点教えて頂ければと思います。

Vlookup関数の最後の&"""は値を文字列化しているのでしょうか。
検討違いの質問でしたらすみません。

.value = .value はvlookupで検索した結果をsheet1にコピーしていると考えて宜しいのでしょうか。