Q&A
ありがとうございます！
②のやり方で実現できました。
とても丁寧に解説頂きましたのでベストアンサーにさせて頂きました！
Excelの関数で、複数条件に一致したデータを別シートに表示させる関数の組み合わせについて教えて頂きたいです。
流れとしては
1.マスタシートに都道府県コードと市町村名コード、コードに対応する都道府県名と市町村名が入っている。
都道府県名は一意だが、市町村名は同一コードが複数存在する。
また、後からマスタシートにデータが更新される可能性がある。※参考資料１
2.記載シートのB列セルに値を入れると１の都道府県名が表示される※参考資料２
3.記載シートのD列に値を入れると、記載シートの２で入力したB列セルの値と一致し、かつD列に入れた値と一致する市町村名を
表示する
２はVLOOKUP関数（範囲指定にOFFSET関数とCOUNTA関数を使用）で求めることができたのですが、３がどの関数を組み合わせればいいのか分かりません。
この場合はMATCH関数を利用する方法も考えたのですが、範囲指定が上手くいきませんでした。
どの関数を組み合わせればいいのか教えて頂けないでしょうか。
参考資料1
参考資料2
回答2件
0
「マスタ」シートに列を1列追加して良ければ、VLOOKUPで簡単に出来ます。
①「マスタ」シートのF列あたり（場所的にはC列とD列の間に追加が良いかも）に、B列（県コード）とC列（市町村コード）を文字列連結した値を入れる。
（ユニークにするために間にスペース入れるとかして、例えば、"1021 01"とか）
②上記①で追加した列と、E列（市町村名）に対して、「記載」シートのB列（県コード）とD列（市町村コード）を文字列連結した値をキーに、VLOOKUPする。
蛇足
「マスタ」シートの県コードと県名も、県マスタみたいなのに集約した方が良いのでは、と思いました。
投稿2019/01/23 22:28
編集2019/01/23 22:29
総合スコア1083
0
ベストアンサー
VLookupの限界
VLookup関数にはいくつかの制約（前提条件）があります。
①マスタの左端列がキーとなる項目になっており、かつ②その左端列でソートされている場合に正しく機能します。
まず①の制約により、複数列のキー項目を組み合わせた検索ができません。
そして市町村コードの検索においては、C列を左端として、さらにキー重複しないよう県コードで範囲を限定する必要があります。
マスタ範囲を絞り込むための検索が必要となってしまいそうです。
ここらへんがVLookup関数の限界になってきます。
この限界を超える方法を３つご紹介します。
①「INDEX関数&MATCH関数」
VLookup関数は単一キーでマスタ検索する場合には便利なのでよく使われています。
しかし上記の前提条件を満たせない場合など、より高度な検索を行いたい場合には、MATCH関数とINDEX関数を組み合わせる方法があります。
Vlookupのような制約もなく、より高度な検索ができるのですが若干敷居が高くなるのも事実です。
※各関数の説明は、MATCHとINDEXでググればあちこちで解説されていますので、ここでは割愛させていただきます。
例えば記載シートのE4セルに
=INDEX(マスタ!E$4:E$12, MATCH(B4&C4, INDEX(マスタ!B$4:B$12&C$4:C$12,),))
といった式を記述します。
これは当初aikon_marimoさんが列追加で提案されていた手法を、列追加なしで実現しているような方法になります。
MATCH関数の検索値を記載シートのB4＋C4の値とし、検索範囲もB4:B12＋C4:C12としてしまっています。
初見では「そんな無茶な結合ができるのか！？」と思うでしょうが、ここではできてしまうのですね。
②「INDEX関数&SUMPRODUCT関数」
こちらも複数キー検索ではよく使われる手法です。
aikon_marimoさんのコメント欄で紹介されているページも、この手法だと思います。
難点はSUMPRODUCT関数の挙動が直感的に掴みにくいというところでしょうか。
記述が難しいのでまず説明からすると、式は以下のような構造になります。
=INDEX([値を取得する範囲], SUMPRODUCT((条件１)*(条件2)*(…条件n)*ROW(行範囲)))
例えば提示頂いたシートの場合、
・値を取得する範囲⇒E1:E12
・条件１⇒マスタ!B4:B12が記載シートのB列の値と一致
・条件２⇒マスタ!C4:C12が記載シートのC列の値と一致
・行範囲⇒マスタ!B4:B12 ※これは検索結果から行番号を取得するためのものなので、列はあまり関係ありません。
式にすると
=INDEX(マスタ!E$1:E$12, SUMPRODUCT((マスタ!B$4:B$12 = B4)*(マスタ!C$4:C$12 = C4) * ROW(マスタ!B$4:B$12)))
こうなります。
条件１と２について、列は違いますが行範囲は同じになっています。これが１つ目のミソです。
そして検索結果を特定するために行番号を使用しているので、マスタ範囲は「マスタ!B2:E12」なのにINDEX関数でE列の値を範囲指定する際「マスタ!E1:E12」と1行目から対象にしているところが２つ目のミソになります。
ご覧のとおり少し敷居が高いのですが、使い慣れてくると逆にわかりやすいのかもしれません。
③「VLookup関数」でがんばる
Vlookup関数を使う以上その制約には縛られるのですが、提示いただいたシートのようにきれいにソーティングされているのであればがんばって何とかならないこともありません。
まず式から紹介すると、例えば記載シートのE4セルは以下のようになります。
=VLOOKUP(C4, INDIRECT("マスタ!C"&MATCH(B4, マスタ!B:B, 0)&":E"&MATCH(B4, マスタ!B:B, 1)), 3)
解説すると、まず
MATCH(B4, マスタ!B:B, 0)とMATCH(B4, マスタ!B:B, 1)で、県コードの先頭行、最終行をそれぞれみつけています。
例えば1022を検索すると、9と12が返されます。
それを踏まえて、取得した行番号を文字列に結合します。
"マスタ!C" & 9 &":E" & 12
これでできあがる文字列は"マスタ!C9:E12"です。
その文字列をINDIRECT関数でセル範囲に変換します。
INDIRECT("マスタ!C9:E12")
そのセル範囲をマスタ（検索範囲）としてVLookupを行い、3列目(E列)の値を取得します。
=VLOOKUP(C4, マスタ!C9:E12, 3)
以上ですが、やはりかなりゴリゴリのやり方になってしまいますね。
とてもオススメできるものではありません。
以上、参考になれば幸いです。
投稿2019/01/24 06:12
総合スコア3013
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2019/01/23 22:39
2019/01/23 23:14
2019/01/23 23:23
2019/01/23 23:30
退会済みユーザー
2019/01/24 10:32