ＩＦとＶＬＯＯＫＵＰ関数

質問

ＶＬＯＯＫＵＰ関数を使って検索結果を出そうと思いましたが、
データ量が93000行Ｘ8列になりますので、
共通分けして複数のシート（A、Ｂ、Ｃ、Ｄ）に分けました。
あるセルに『Ａ』と入れると『シートＡ』の表から検索を行う、
あるセルに『Ｂ』と入れると『シートＢ』の表から検索するといった感じにするには、
ＩＦとＶＬＯＯＫＵＰを使用するなら、どのような式を入れたらいいのでしょうか？

また、他の関数を使う式があるのでしょうか？

マクロやＶＢＡも考えましたが、
あまり知識がありません。

データ量から考えると
マクロやＶＢＡで行った方がいいのでしょうか？

回答No.3

kagakusuki · ベストアンサー

>教えて頂いた数式で　
=IF(COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)=1,VLOOKUP($A1,INDIRECT(INDEX({"SheetA","SheetB","SheetC","SheetD"}

>ここまでは理解できましたが、この後の
COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)*2+COUNTIF(SheetC!$A:$A,$A1)*3+COUNTIF(SheetD!$A:$A,$A1)*4)&"!A:H"),COLUMNS($A:B),FALSE),IF(COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)>1,"重複あり",""))
は、何をしようとしている式なのでしょうか？
>『*2』などはどういう意味なのでしょそんざいしているかをかそうか？

　まず、IF関数の「論理式」の部分にある

COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)=1

という数式で、SheetA～SheetDのA列中に、「検索結果を表示させるシートのA1セルに入力した値」と同じ値を持つセルが幾つ存在しているのかをカウントし、カウント結果が1個の場合にはIF関数の「真の場合」に入力されている数式の計算結果を表示し、カウント結果が1個以外、即ち2個以上か0個の場合には「偽の場合」に入力されている数式の計算結果を表示します。
　IF関数の「偽の場合」の部分には、更に次のIF関数が入れ子になって入力されています。

IF(COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)>1,"重複あり","")

　こちらのIF関数の「論理式」の部分にある

COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)>1

という数式で、再度、SheetA～SheetDのA列中に、「検索結果を表示させるシートのA1セルに入力した値」と同じ値を持つセルが幾つ存在しているのかをカウントし、カウント結果が1個を超えている、即ち2個以上であった場合には、該当するデータが複数存在しているという事になり、複数あるデータの内のどれを優先して表示すべきなのかが判りませんから、検索を行わずに、入れ子の内側に入れたIF関数の「真の場合」に入力されている文字列である

重複あり

を表示します。
　又、カウント結果が0個であった場合、即ち、基データの中には該当するデータが存在していない場合には、入れ子の内側に入れたIF関数の「偽の場合」には、「""」しか入力されていませんから、何も表示されない事になります。

　それから、入れ子になっているIF関数の内の、外側の方のIF関数の「真の場合」に入力されている数式は

VLOOKUP($A1,INDIRECT(INDEX({"SheetA","SheetB","SheetC","SheetD"},COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)*2+COUNTIF(SheetC!$A:$A,$A1)*3+COUNTIF(SheetD!$A:$A,$A1)*4)&"!A:H"),COLUMNS($A:B),FALSE)

ですが、この中の

COUNTIF(SheetA!$A:$A,$A1)

の部分は、SheetAのA列中に「検索結果を表示させるシートのA1セルに入力した値」と同じ値を持つセルが幾つ存在しているのかをカウントする関数ですが、この部分によるカウントが行われるのは、IF関数の判定で、SheetA～SheetDのA列中には該当るデータが1個しか存在していない事が判明している場合にのみなのですから、結局

COUNTIF(SheetA!$A:$A,$A1)

の部分は、該当するデータがSheetAに存在するか否かを判定する働きをする事になります。
　同様に、

COUNTIF(SheetB!$A:$A,$A1)

COUNTIF(SheetC!$A:$A,$A1)

COUNTIF(SheetD!$A:$A,$A1)

の各部分では、SheetB、SheetC、SheetDの、何処に該当するデータが存在しているのかを判定する働きをしています。
　各COUNTIF関数には「*2」等の整数値を掛け算していますから、

COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)*2+COUNTIF(SheetC!$A:$A,$A1)*3+COUNTIF(SheetD!$A:$A,$A1)*4)

の部分の計算結果は、該当するデータが
SheetAに1個だけ存在する場合には　　1
SheetBに1個だけ存在する場合には　　2
SheetCに1個だけ存在する場合には　　3
SheetDに1個だけ存在する場合には　　4
となります。
　そして、

COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)*2+COUNTIF(SheetC!$A:$A,$A1)*3+COUNTIF(SheetD!$A:$A,$A1)*4)

の部分は、INDEX関数中の{ }で括られている配列中の、何番目の値を取り出すかを指定している箇所に、入力されていますから、該当するデータが
SheetAに存在する場合には、配列中の1番目の値である「SheetA」という文字列が、
SheetBに存在する場合には、配列中の2番目の値である「SheetB」という文字列が、
SheetCに存在する場合には、配列中の3番目の値である「SheetC」という文字列が、
SheetDに存在する場合には、配列中の4番目の値である「SheetD」という文字列
が、それぞれ取り出される事になります。
　そして、INDEX関数のすぐ後ろの部分には

&"!A:H"

となっていますから、INDEX関数で取り出した、各シート名を表す文字列に

!A:H

という文字列が連結される事により、各シートにおけるA列～H列のセル範囲を表す文字列となります。（例えば、該当するデータがSheetAに存在する場合には、「SheetA!A:H」という文字列データとなる）

　そして、

INDEX({"SheetA","SheetB","SheetC","SheetD"},COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)*2+COUNTIF(SheetC!$A:$A,$A1)*3+COUNTIF(SheetD!$A:$A,$A1)*4)&"!A:H"

の部分で表される文字列は、INDIRECT関数で参照するセル範囲を指定するための箇所に入力されていますから、該当するデータが
SheetAに存在する場合には、SheetA!A:Hのセル範囲が指定され、
SheetBに存在する場合には、SheetB!A:Hのセル範囲が指定され、
SheetCに存在する場合には、SheetC!A:Hのセル範囲が指定され、
SheetDに存在する場合には、SheetD!A:Hのセル範囲が指定される事になります。
　このINDIRECT関数は、VLOOKUP関数の検索範囲を指定するための箇所に入力されていますから、該当するデータが
SheetAに存在する場合には、SheetA!A:Hのセル範囲で、VLOOKUP関数の検索が行われ、
SheetBに存在する場合には、SheetB!A:Hのセル範囲で、VLOOKUP関数の検索が行われ、
SheetCに存在する場合には、SheetC!A:Hのセル範囲で、VLOOKUP関数の検索が行われ、
SheetDに存在する場合には、SheetD!A:Hのセル範囲で、VLOOKUP関数の検索が行われる事になります。
　又、VLOOKUP関数の、検索範囲中の何列目の値を表示させるのかを指定する箇所には、

COLUMNS($A:B)

が入力されていますから、検索結果を表示させるシートのB1セルをコピーして、右側に存在しているセルに貼り付けるだけで、検索範囲中の何列目の値を表示させるのかを指定する箇所が
B列に貼り付けた場合は　　COLUMNS($A:B)=2
C列に貼り付けた場合は　　COLUMNS($A:C)=3
　　　　　　・
　　　　　　・
　　　　　　・
G列に貼り付けた場合は　　COLUMNS($A:G)=7
H列に貼り付けた場合は　　COLUMNS($A:H)=8
という具合に、VLOOKUP関数で検索した行の、何列目の値を表示するのかが、自動的に指定される事になります。

お礼

お返事、ありがとうございます。
一つ一つ解きながら、やっと式を理解しました！！
そして、この式でやりたいことも達成できました！
すばらしい式ですね。
こんな方法もあるんだと勉強になりました。
大変助かりました。
ありがとうございますm(__)m

回答No.2

kagakusuki

次の様な関数とすれば、

>あるセルに『Ａ』と入れる

という操作は不要になりますし、SheetA～SheetDにデータを入力しておくだけで、共通分けがされていなくとも、検索結果を表示させる事が出来ます。

　今仮に、検索結果を表示させるシートにおいて、A1セルに（SheetA～SheetDの何れかのシートのA列の何行目かに、入力されているデータである）検索の基となる値を入力すると、検索結果を表示させるシートのB1～H1の範囲に、検索結果を表示させるものとします。
　まず、B1セルには次の関数を入力して下さい。

=IF(COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)=1,VLOOKUP($A1,INDIRECT(INDEX({"SheetA","SheetB","SheetC","SheetD"},COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)*2+COUNTIF(SheetC!$A:$A,$A1)*3+COUNTIF(SheetD!$A:$A,$A1)*4)&"!A:H"),COLUMNS($A:B),FALSE),IF(COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)>1,"重複あり",""))

　次に、B1セルをコピーして、C1～H1の範囲に貼り付けて下さい。

　以上です。

お礼

ご回答ありがとうございます。

教えて頂いた数式で　
=IF(COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)=1,VLOOKUP($A1,INDIRECT(INDEX({"SheetA","SheetB","SheetC","SheetD"}

ここまでは理解できましたが、この後の
COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)*2+COUNTIF(SheetC!$A:$A,$A1)*3+COUNTIF(SheetD!$A:$A,$A1)*4)&"!A:H"),COLUMNS($A:B),FALSE),IF(COUNTIF(SheetA!$A:$A,$A1)+COUNTIF(SheetB!$A:$A,$A1)+COUNTIF(SheetC!$A:$A,$A1)+COUNTIF(SheetD!$A:$A,$A1)>1,"重複あり",""))
は、何をしようとしている式なのでしょうか？
『*2』などはどういう意味なのでしょうか？

初心者なものですみません。
教えて下さい。

回答No.1

nora1962

EXCELのバージョンは上げられませんか？
EXCEL2007からならスペック上は1,048,576行までいけますのでメモリが十分にあれば最も簡単に対応出来ると思います。

後、ACCESSがあればデータをACCESSに移行することも考えられます。
ただ、EXCELのような自由度の高い計算は出来ませんが。

～EXCEL2003でどうしても対応しようというなら
入力列を2列にして(Aを検索シート、Bが検索データ）
=IF(A1="A",VLOOKUP(B1,SheetA!$A$1:$B$20,2,FALSE),IF(A1="B",VLOOKUP(B1,SheetB!$A$1:$B$20,2,FALSE),IF(A1="C",VLOOKUP(B1,SHEETC!$A$1:$B$20,2,FALSE))))

ですかね。

お礼

ご回答ありがとうございます。バージョンは上げられませんでした・・・。