エクセル 複数条件の絞り込み結果をプルダウンに

質問

エクセルで県名・業種・会社名が入力されたシートがあります。
これを別シートで、県名と業種をプルダウンで選ぶことにより、会社名をプルダウンで表示させて選択可能にしたいのですが、どうすればいいでしょうか？

名前定義をして入力規則で、県名で絞り込み、業種から選択しようかと思ったのですが、データ数が多いので名前定義のリストを作るだけでも大変です。
他のやり方で（あまり難しくなく）、県名と業種で絞り込んだものをプルダウンリストに表示させる方法はないでしょうか？
よろしくお願いします。

回答No.7

imogasi · ベストアンサー

#2です。
（質問者の補足が在ったりしたので）３段階の絞り込みをやってみました。
小生はＶＢＡの利用に流れ込みそうなのですが、それを使わない方法を考えた末です。
今回は、すべてＳｈｅｅｔ１での話です。
まず下記の通り、やってみてください。根気が要りますが。
データー入力規則の機能しか使ってません。
その他に、INDIRECT関数と＆（文字列結合子）を使ってます。
下記仕組みを整えたのちに、Ａ，Ｂ、Ｃ列の３列にドロップダウンリストで選択（入力）します。
データ例　神奈川の社名が入ってないのは、小生のデータ作成の手抜きのためです。
府県 業種 社名
東京 　　Ｂ TB3
神奈川 Ｇ
千葉　 Ｚ CZ2
神奈川 Ｈ
千葉 　　Ｙ CY１
上記は入力後（選択後）の例です。
ーー
第１ステップ
次のデータ表を作って用意します。
Ｄ列
府県　＜－－第１行目＝名前定義の名前になる
東京
神奈川
千葉
ーー
Ｅ，Ｆ，Ｇ列
東京業種 神奈川業種 千葉業種　＜－－第１行目＝名前定義の名前になる
Ａ Ｇ Ｘ
Ｂ Ｈ Ｙ
Ｃ Ｉ Ｚ
Ｄ Ｊ
Ｋ
ーー
Ｈ列からＮ列まで
東京Ａ社名 東京B社名 東京C社名 東京D社名 千葉X社名 千葉Y社名 千葉Z社名　＜－－第１行目＝名前定義の名前になる
TA１ TB1 TC１ TD1 CX1 CY１ CZ1
TA２ TB2 TC２ TD2 CX2 CY２ CZ2
TA３ TB3 TC３ TD3 CX3 CY３ CZ3
TA４ TB4 　　TD4 CX4 　　CZ4
TB5 　　　 CX5
神奈川分の社名は、今般は手抜きで省略したが、本来は、神奈川？？社名などを何列分かを作って、テストしてください。
ーー
第２ステップ
名前定義を、Ｈ列からＨ列までについて
数式ー選択範囲から作成ー最上行
で行う。
ーー
第３ステップ
リストの「元の値」に入れる式は (記号と関数名は半角で入力に注意）
Ａ，Ｂ、Ｃ列の３回それぞれ設定します
ーー
Ａ２：Ａ１０を範囲指定しておいて
（１）府県の列（Ａ列）　=府県
ーー
Ｂ２：Ｂ１０を範囲指定しておいて
（２）業種の列（Ｂ列） =INDIRECT(A2&"業種")
ーー
Ｃ２：Ｃ１０を範囲指定しておいて
（３）社名の列（Ｃ列）=INDIRECT(A2&B2&"社名")　
＝＝＝
●リストの「元の値」の　データ作成が面倒ですが。
●業種を英字で定義しているのは、他の府県のものと実際は同じ業種名になるが、区別するため、「ワザと」したもので、意図を理解してください。
●この仕組みが理解出来たら、データ表は別シートにまとめるなどやってみてください。Sheet2!A2とかに式が変わったり、名前定義が、シート範囲から、ブック範囲に広げる必要があると思います。

回答No.11

msMike

[No.10補足]へのコメント、
》 …事前にSheet1 のA列都道府県、B列業種の無重複化
》 されたものをリストとして入力しておけばいいという
》 意味ですか？
そのつもりでしたが、貴方の添付図の Sheet1 のセルC3 の A社と セル C6 の D社は何れも東京だから、Sheet1 の A列を無重複にすることは出来ないでしょ？

修正したのは、Sheet2 におけるドロップダウンリストが
東京・大阪・神奈川・東京・大阪・東京のように、大阪/東京が重複表示されるのを防いだだけです。そのこと、お嫌いだったのでは？

懐メロ♪君待てども♪の歌詞じゃないけど、もう
♪諦めましょう、諦めましょう♪
「もう少し」は、「もう」ご遠慮ください。

お礼

何度も質問して、ご迷惑をおかけして申し訳ありません。
今回は諦めますが、時間の余裕ができたら式の意味を自分でちゃんと勉強して、教えていただいた事を無駄にしないようにしようと思います。
お手数をおかけしました。本当にありがとうございました。

回答No.10

msMike

[No.9補足]へのコメント、
貴方のお蔭で、たった今、大変な間違いをしていたことに気付かされました。
ステップ５を下記のように訂正させてください。

５．下記のセルにそれぞれ右側の[入力規則]([入力値の種類]を“リスト”
￣￣とし、[元の値]ボックスに下記の数式を入力)を設定
￣￣￣ A2: =都道府県A
￣￣￣ B2: =OFFSET(Sheet2!$K$1,1,,COUNTA(業種A),)

つまり、セル A2、B2 の部分は Sheet1 と全く同じにしてください。
ドロップダウンリストの表示項目数が多くなりますが、「東京・大阪・神奈川・東京・大阪・東京」のような重複リストにはなりません。
元のままだと、重複リストにはなるけど、表示される項目数は少なくはなります。でもカッコ悪いですよね。
本当は、Sheet1 で選択された都道府県、業種の無重複化されたものを Sheet2 で使うのがイチバンですが、残念乍ら私はその能力を持ち合わせていないのです。

ご迷惑をお掛けしました。

補足

ありがとうございます。
しつこくて申し訳ないのですが、もう少しご指導ください。

ステップ5を修正しました。
表示リストが重複しなくなりました。

> 本当は、Sheet1 で選択された都道府県、業種の無重複化されたものを Sheet2 で使うのがイチバンです
ということは、Sheet2のJ列（都道府県A）は、今は全都道府県を表示させていますが、事前にSheet1 のA列都道府県、B列業種の無重複化されたものをリストとして入力しておけばいいという意味ですか？

そして、この修正後にも、Sheet2において、A2から東京を選択して表示させ、B2から製造業を選択して表示させてみても、C2にA社のみが表示されD社が表示されない件が解決しません。
F2、G2、H2にも何も表示されません。
C2にA社とD社が表示されて選択できるようになるわけではないのですか？

回答No.9

msMike

[No.6補足]へのコメント、
》 …入力してあるデータが書き換わってしまいます。
何処がどう変わるかを明記すべし！

》 Sheet2のE列～H列が作業列となっているところが、よくわかり
￣￣ません。
「よくわかりません」だけじゃ、私の方こそ全く分からない！

》 現状ではSheet2のA2のドロップダウンリストではA2: =都道府県
￣￣と表示されます。
その表示は「A2: =都道府県」ですか？それとも「=都道府県」ですか？
ステップ５を熟読されたい。注意書きの括弧の部分を外すと、書いたのは「下記のセルにそれぞれ右側の[入力規則]を設定」になりますよね。つまり、セル「A2:」の右側に書いた式「=都道府県」を入力しましたか？
全く同じではないけど、似たような設定をステップ３でもしました。Sheet1のA2はドギャン表示されとるンですか？

五月蠅いことを言いヤガルと思うなら、潔く諦めませう。
前回の末尾で独白したように、貴方がお望みの(マクロなしの)解決策は、生易しいものではないのです。

補足

回答ありがとうございます。
返信遅くてすみません。もう少しお願いします。

○入力してあるデータが書き換わる件
オートフィルがちゃんと理解できていませんでした。
選択して右下にある十字ボタンを下に引っ張っただけだったので、全ての行が「東京／製造業／A社」となってしまっていました。
A2、B2、C2をそれぞれコピーして、「形式を選択して貼り付け」から入力規則だけをコピーして解決しました。

○Sheet2のA2のドロップダウンリストでA2: =都道府県と表示される件
『セル「A2:」の右側に書いた式「=都道府県」を入力』が理解できていなくて、入力式を「A2: =都道府県」としていました。
Sheet1、2とも「=都道府県」の形に修正しました。
Sheet2のA2でドロップダウンリストが表示されるようになりました。
しかし、ここでリストには上から東京・大阪・神奈川・東京・大阪・東京の6つが表示されます。
この動きは正しいんでしょうか？
東京・大阪・神奈川の３つだけではないのでしょうか？

○ Sheet2のE列～H列が作業列となっている事がわからない件
上記の修正以後、A2から東京を選択して表示させ、B2から製造業を選択して表示させてみると、C2にA社のみが表示されました。
E2にもA社が表示されましたが、F2、G2、H2は何も表示されません。
イメージではC2にA社とD社が表示されて選択できるのかと思いますが、何がいけないのでしょうか？

回答No.8

chayamati

今晩は
　何時からか分かりませんが、エクセルにテーブル機能が追加されています
　テーブル先頭行の項目名の右端に▼が付きます
　この▼で「並び替え」と「フィルタ」機能です。　
　設定は下記の通り4回のマウス操作で完成です。
　　項目名の行も含めてセル範囲で選択後
　　→ホームリボン→表示されたスタイル
　　→先頭行をテーブルとして使用するに(チェックボックス チェック)
　　→ＯＫ
関数もVBAも要りません。お試しあれ

回答No.6

msMike

添付図参照

Sheet1 において、
１．範囲 A1:C100 選択 ⇒ Alt+MC ⇒“上端行”のみにチェック入れ ⇒ [OK]

Sheet2 において、
２．範囲 J1:L48 選択 ⇒ Alt+MC ⇒“上端行”のみにチェック入れ ⇒ [OK]

Sheet1 において、
３．下記のセルにそれぞれ右側の[入力規則]([入力値の種類]を“リスト”
￣￣とし、[元の値]ボックスに下記の数式を入力)を設定
￣￣￣ A2: =都道府県A
￣￣￣ B2: =OFFSET(Sheet2!$K$1,1,,COUNTA(業種A),)
￣￣￣ C2: =OFFSET(Sheet2!$L$1,1,,COUNTA(社名A),)
４．範囲 A2:C2 を必要なだけ下方にオートフィル

Sheet2 において、
５．下記のセルにそれぞれ右側の[入力規則]([入力値の種類]を“リスト”
￣￣とし、[元の値]ボックスに下記の数式を入力)を設定
￣￣￣ A2: =都道府県
￣￣￣ B2: =業種
６．範囲 A2:B2 を必要なだけ下方にオートフィル
７．次式を入力したセル E2 を右方にH列までオートフィル
￣￣ =IFERROR(INDEX(社名,SMALL(IF((都道府県=$A2)*(業種=$B2),ROW(都道府県)),COLUMN(A1))-1),"")
￣￣【お断り】上式は必ず配列数式として入力のこと
８．範囲 E2:H2 を必要なだけ下方にオートフィル
９．式 =TEXTJOIN("/",TRUE,E2,F2,G2,H2) を入力したセル C2 を必
￣￣要なだけ下方にオートフィル

【独白】「あまり難しくなく、…表示させる方法はないでしょうか？」ですって、トンでもありません！疲れましたぁ～

補足

返答が遅くなって申し訳ありません。
会社が休暇でエクセルが使える環境にありませんでした。

書いていただいた通りにやってみようとしたのですが、できませんでした。
3、4のSheet1において、3で設定した入力規則を
4でオートフィルで下に引っ張ると、入力してあるデータが書き換わってしまいます。
オートフィルって、セルの右下に出る十字マークをクリックしたまま引っ張るコピーですよね。
Sheet2のE列～H列が作業列となっているところが、よくわかりません。
式の意味を理解できないせいかと思います。すみません。

A列で都道府県をドロップダウンで選択したら、次にB列で業種を選択して、
C列でその都道府県と業種に該当する社名が表示されるようにして、
最終的には社名を選択したいのですが。
現状ではSheet2のA2のドロップダウンリストではA2: =都道府県と表示されます。

回答No.5

msMike

確認させてください。
「データ数が多いので┅大変」とのことですが、都道府県は最多でも47件、最も多いのはどのデータの何件ですか？

回答No.4

kkkkkm

沢山あると名前の定義が面倒ですよね。名前の定義をマクロでやってしまうという手があります。
たとえば以下のサイトのような仕様でリストを作成するとします。
http://office-qa.com/Excel/ex204.htm

マクロで上記サイトの説明のSTEP2と STEP4の名前の定義を作成します。
Sub Test()
Dim i As Long, j As Long
Dim LastRow As Long

For i = 5 To 13 Step 4
Range(Cells(2, i), Cells(2, i + 2)).Name = Cells(1, i).Value
For j = 0 To 2
LastRow = Cells(Rows.Count, i + j).End(xlUp).Row
Range(Cells(3, i + j), Cells(LastRow, i + j)).Name = Cells(1, i).Value & Cells(2, i + j).Value
Next
Next
End Sub

参考サイトの STEP2にあるような表も元のデータからマクロで作成することもできますが、面倒でなければ手動で作成して名前の定義だけマクロでもかなり楽になると思います。

回答No.3

nishi6

添付図の前提で説明します。

セルA2:C2　=　範囲名：県名
　この下の入力は分かると思います。
セルI2:M2　=　範囲名：会社名
　この一番上の「東京製造業」等は、「製造業」では重複するので、
　県名を付けて特定できるようにしています。この下の入力も分かると思います。
範囲名：業種…数式＞名前の管理で入力します
　=OFFSET(Sheet1!$A$3,0,MATCH(Sheet1!$E3,県名,0)-1,COUNTA(OFFSET(Sheet1!$A:$A,0,MATCH(Sheet1!$E3,県名,0)-1))-1,1)

セルE3の入力規則はリストで、元の値=「=県名」
セルF3の入力規則はリストで、元の値=「=業種」
セルG3の入力規則はリストで、元の値=「=OFFSET($I$3,0,MATCH($E3&$F3,会社名,0)-1,COUNTA(OFFSET($I:$I,0,MATCH($E3&$F3,会社名,0)-1))-1,1)」
下にコピーしてください。

当方、Win10、Excel2010です。ご参考に。

回答No.2

imogasi

「プルダウン」というのを、質問者はよく使うが、これはコントロール（ＧＵＩの部品）と、付随した仕組みのことで、ＶＢＡなどでなら、その十分な能力を発揮するものです。
ーー
初心者的な質問者は、ＶＢＡでなく、「入力規則」のリストの応用として、これを考えざるを得ないでしょう。エクセルの「入力規則の応用だ」（しかない）という意識はありますか。
ーー
第I段階に条件を絞っていって（本件では府県を１つ選ぶにあたる）、その選択された府の中の候補の業種（府県ごとに限定される、絞られる）を、第２段階で
提示して、その中から１つ選んで、その業種該当の複数会社を提示して、１つ選択させる。
これは説明が長くなる。しかしＷＥＢには解説がある。照会の仕方は、
Ｇｏｏｇｌｅで「入力規則　多段階」で照会し
＞4段階ドロップダウンリストの ... · ‎1段階目のドロップダウン ... · ‎2段階は1段階目と連動 .前の選択で次の選択肢が変わる連動ドロップダウンリスト
＞エクセル 3段階のリストを連動させる方法(2つ目のリスト項目に重複がある場合)
といった記事をじっくり読んで、応用すべきです。
ＶＢＡでＳＱＬなどを使うならば、小手先の工夫をしなくて済む課題だが、
それを使えないなら、先人が工夫した方法を真似るしかない。
ーー
シートには２次元の表はつくれますが、３次元の表は作れない。そこを名前の定義など使って切り抜けるわけです。
https://whiteleia.com/%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB-3%E6%AE%B5%E9%9A%8E-%E3%83%AA%E3%82%B9%E3%83%88-%E9%80%A3%E5%8B%95-2/
の考えなど使えませんか。

補足

ご指摘の通りの初心者で、入力規則の応用で2段階のドロップダウンリストを作ったことしかありません。（プルダウンという表現したのが、たぶん間違っているんですよね？）
今回、入力規則の応用で3段階のドロップダウンリストを作ろうと思いました。すると名前定義の表を作るにあたり47都道府県×15業種が必要になり、名前の付け方もnishi6さんのおっしゃる通り「東京製造業」にしなければなりません。大変だな、他にやり方はないのかなと思って探している時にたまたまDGET関数というのを見かけて、表示させた条件を参照して検索結果１つを特定して表示できるなら、これを複数の検索結果をドロップダウンリストに表示させる方法があったらいいのにと素人考えで思って質問した次第です。
VBAは単語としては聞いたことがありますが、内容は全くわかりません。やるなら一から勉強しないといけなくなるので、素人には無理だよという事であれば今回は諦めて入力規則で頑張ってみようかと思いますが、いかが思われますか？

回答No.1

aokii

県名と業種で絞り込んだものをプルダウンリストに表示させる方法の詳細な説明は難しいので、できれば、名前の定義でセル範囲に名前を付けてから、プルダウンリストを利用することをお勧めします。