[[20231222211905]]　『条件付きの関数設定』（よし）　ページの最後に飛ぶ

『条件付きの関数設定』（よし）

絞込み機能付きのドロップダウンに関してです。
ドロップダウンリストが大量にある場合、セルに任意の文字を入力すると、リスト内から該当するものだけを検索できるドロップダウンがあると思います。

例えば、
A1のセルに東京都と選択した場合、A2で都内23区を検索できるドロップダウンリストに。
同じくA1で愛知県を選択した場合、A2で県内の市を検索できるドロップダウンリストにすることは可能なのでしょうか。

A1が東京都の場合、この関数、愛知県の場合はこの関数のように設定できれば良いと思うのですが、方法がわかりません。

拙い説明で申し訳ありませんが、ご教授願います。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

https://www.pc-koubou.jp/magazine/70114
を参考に。
（リンク） 2023/12/22(金) 22:10:31

参考のURLありがとうございます。
このURLの資料で説明をすると、各OS毎に担当者が数百人いるとして、担当者を選ぶドロップダウンを検索機能付きにしたいというご質問です。

（よし） 2023/12/23(土) 09:32:04

ドロップダウンリストがH列にあるとします。
行数は20行と仮定します。
I列を作業列とします。

I1: =IFERROR(INDEX($H$1:$H$20,AGGREGATE(15,6,ROW($A$1:$A$20)/(FIND($A$1,$H$1:$H$20)>0),ROW(A1))),"")

A2を選択してリスト入力を設定します。

リスト入力の式:=OFFSET($I$1,0,0,20-COUNTIF($I$1:$I$20,""),1)

（メジロ） 2023/12/23(土) 10:40:47

質問が重複していたら申し訳ありません。

A1セル
行政区（東区、西区、南区、北区を選択できるドロップダウンリスト）
A2セル
町名

A2セルのをそれぞれの区に対応した、検索機能付きのドロップダウンリストにしたいです。

行政区毎の検索機能付きドロップダウンリストは、完成していますが、それぞれの行政区対応したものが作成出来ず苦慮しています。

（よし） 2023/12/26(火) 08:28:21

（メジロ）さんの式は試してみましたか。
＞A2セルのをそれぞれの区に対応した
それは無理でしょ。
どこかにそれに対応する一覧表でも作成しないとできないのでは。

（IT） 2023/12/26(火) 08:59:21

 （メジロ）さんの式で出来ますけど、ご自身のリスト一覧の配置が						
 どうなっているかによって、不足している式がまだあるかと思います。

 仮の話として、リストシートには４７都道府県だから						
 他のリスト等も入れると５０列前後になりますか？						
 で...１県あたり５０行から２００行を想定している？

 でももって、検索できるようにと言う希望としては、都道府県を						
 例にとって言うと…区,市,町,村 のキーワードになると思いますが

 手順１：都道府県で選ぶ						
 手順２：区,市,町,村 のキーワードで更に絞込						
 手順３：５～２０前後まで？？？抽出して						
         最後はドロップダウンから選択する？？

 つまり、５０件から～１００件でドロップダウンで見つけるのは						
 大変だが、５～２０件前後まで絞れば良いイメージですか？						
 もしくは、区,市,町,村 にもう少し追加で絞込の対象になりそうな						
 １文字で、東,田,川,中 とか数文字を追加するような？
（あみな） 2023/12/26(火) 09:19:50

 ＞行政区（東区、西区、南区、北区を選択できるドロップダウンリスト）

 都道府県 全国に47県
 市区町村 約1,700
 --- ここまでなら3連動 ---

 行政区まで絞り込むですか？西区だけでもパット調べただけでも
 下記のような住所が該当しますけど…これだと4連動が必要な気がする

 大阪市西区新町4丁目
 名古屋市西区花の木二丁目
 福岡市西区西都

 う～ん、本当に全国の地名を使用するのか、ただの例え話なのか
 良く見えないですが…とにかくあなたのリスト一覧シートの情報が
 わからないと、誰も明確な回答はし難いような気がしますね。
（あみな） 2023/12/26(火) 10:19:14

申し訳ありません。
詳細をご説明します。

例えば
A1で行政区をドロップダウンで選択。（東区、西区、南区、北区）
A2でA1で選択した、行政区内の町村名をドロップダウンで選択。（町村名は多い行政区で１０００件以上）

ここで、町村名が１０００件以上ある場合に、例えば「東」とA2に入力すると、町村名に「東」を含む町村名に絞ったドロップダウン（検索機能付きドロップダウンと呼ぶことにします。）にすることができると思います。

実際に自分で作成した例だと、町村名のリストがH列にあると仮定してB1=IFERROR(SEARCH($A$2,LEFT(H1,2)),0)で「東」がリスト中にあるかないかを絞り、C1=IF(B1=0,"",COUNTIF($B$1:B1,">0"))で、何個あるかを表示し、
D1=IFERROR(INDEX(H:H,MATCH(ROW(B1),C:C,0)),0)で対象を表示します。

そしてE1に　E1=OFFSET($D$1,,,COUNTIF($D:$D,"*?")-1)これをA2に「データの入力規則」の「リスト」「元の値」に入れ込めば、東区の町村名が検索できる、検索機能付きのドロップダウンが完成しました。

ここまではできたのですが、これだと東区の場合でしか検索機能付きのドロップダウンができません。
A1で西区を選択した場合、そのほかの行政区を選択した場合に、別の町村名リストにも対応した検索機能付きのドロップダウンをA2に当てはめることはできるのでしょうか。

途中数式が間違っていたら申し訳ありません。
ご教授願います。

（よし） 2023/12/26(火) 11:02:39

補足です。

例えばA1で東区を選択した場合、A2で東区内の町村名を検索できる、検索機能付きドロップダウン
西区を選択した場合、A2で西区内の町村名を検索できる、検索機能付きドロップダウン

のような感じにできればよいと思っています。

対象は全国でなく、都道府県は固定です。
（よし） 2023/12/26(火) 11:21:59

 ＞A1のセルに東京都と選択した場合、A2で都内23区を検索できるドロップダウンリストに。
 ＞同じくA1で愛知県を選択した場合、A2で県内の市を検索できるドロップダウンリストにすることは可能

 都道府県は無いのですか...最初の条件と違うのね^^;
 サンプルデータを作るの大変...新町まで必要ですね。

 大阪市西区新町4丁目
　　　~~~~~~~~
（あみな） 2023/12/26(火) 11:55:54

最初から具体的に説明をすればよかったですね。

やりたいことはかわっていません。

説明不足で申し訳ありません
（よし） 2023/12/26(火) 12:03:50

 関数だけでは無理だと思います(たぶん)。
 冒頭に、あると思いますと言うのであれば、そのサイトを示して貰えますか？
（xyz） 2023/12/26(火) 12:08:21

https://kokodane.com/tec3_70m.htm

このURLの前編後編を参考にしました。

一つの行政区であれば、これで問題ありません。
（よし） 2023/12/26(火) 12:16:43

 おお～私の好きな...にゃんこ先生登場 上記URLの先生の事

 ちょっとまってよー...（よし）さんの式からして
 名前定義とか、INDIRECT関数は使用してなさそうだなー

 １回レイアウト想定を作成してみます。後程...
（あみな） 2023/12/26(火) 12:23:04

 ありがとうございます。オプション操作があることをうっかりしていました。
（xyz） 2023/12/26(火) 13:01:20

 データーの確認です。東京都を題材とします。
 （よし）さんの全行政区域のデータは、[ パターン1 ]のように並んでいるですか？
        …それとも[ パターン2 ]でしょうか？

 ※[ パターン1 ] : 1列に、全行政区域が並んでいる( 下記が例 )

    |[H]         
 [1]|行政区域    
 [2]|千代田区▲町
 [3]|八王子市○町
 [4]|立川市○町  
 [5]|立川市□町  
 [6]|小笠原村★島
 [7]|千代田区○町
 [8]|八王子市▲町

 ※[ パターン2 ] : 町区域   毎にリスト一覧が整理されている

 ◆Sheet1 ( B1,B2,B3は、ドロップダウン選択切替セル )

    |[A]     |[B]       |[C]  |[D]  |[E]       |[F]  |[G]  |[H]     
 [1]|行政区  |中央区    |     |     |町区域キー|▲   |○   |□    |★ 
 [2]|町区域  |▲        |     |     |          |     |     |        
 [3]|最終選択|中央区▲町|     |     |          |     |     |        
 [4]|    
 [5]|項目1   |項目2     |項目3|項目4|項目5     |項目6|項目7|最終選択
 [6]|-       |-         |-    |-    |-         |-    |-    |■選択  
 [7]|-       |-         |-    |-    |-         |-    |-    |■選択  
 [8]|-       |-         |-    |-    |-         |-    |-    |■選択

 ※項目列は適当なデータとする
 ※町区域は、サンプル説明として F1～G1 範囲...入力規則で設定するキー( 別にここにある必要はないが... )
 ※■選択  が最後に選択したキーによって抽出される住所

 ◆Sheet2( 都内の町区域が、別シートに列毎に綺麗に整頓されている )

    |[A]     |[B]       |[C]       |[D]         |[E]       |[F]         |[G]       |[H]         
 [1]|        |          |          |区          |区        |市          |市        |村          
 [2]|東京都  |区市町村  |▲○□★等|千代田区    |中央区    |八王子市    |立川市    |小笠原村    
 [3]|千代田区|補助式　　|補助式　　|千代田区▲町|中央区▲町|八王子市▲町|立川市▲町|小笠原村★島
 [4]|中央区  |補助式　　|補助式　　|千代田区▲町|中央区▲町|八王子市▲町|立川市▲町|            
 [5]|文京区  |補助式　　|          |千代田区○町|中央区○町|八王子市○町|立川市○町|            
 [6]|八王子市|補助式　　|          |千代田区○町|中央区○町|八王子市○町|立川市○町|            
 [7]|立川市  |補助式　　|          |千代田区□町|中央区□町|八王子市□町|立川市□町|            
 [8]|小笠原村|補助式　　|          |千代田区□町|中央区□町|八王子市□町|立川市★町|

 ※A列には、都内の行政区分( 62区分 )が縦に入っている例…Sheet1のB1セルで入力規則で選択するキー
 ※D列から右に、町区分で整理されている例
（あみな） 2023/12/26(火) 14:50:32

私の場合、パターン2で作成しています。
（よし） 2023/12/26(火) 16:32:04

 ※[ パターン2 ]　レイアウト一覧の説明を１番にしてれば、とっくに（メジロ）さんが
   式を提示してくれて終わってる案件ですね。（ 多分ね ）

 ※[ パターン2 ] と同じ内容で良いので、上のサンプルと同じレイアウトの作成を
   新規BOOKに作成して準備してください。

 ちょっと私、でかけないといけないので...帰って来るまでに誰かやってくれるかもですよ。
 私ならちょっと夜...遅めになります。
（あみな） 2023/12/26(火) 16:50:52

 > 例えばA1で東区を選択した場合、A2で東区内の町村名を検索できる、検索機能付きドロップダウン
 > 西区を選択した場合、A2で西区内の町村名を検索できる、検索機能付きドロップダウン
 それぞれができているのなら、入力規則のリストに設定する式は、
 A1セルの値に応じてIF関数で場合分けして指定する式にすればよいのでは？
 多数あるなら、IFS関数を使ったほうがいいでしょうか？ 
（xyz） 2023/12/26(火) 21:02:01

 でも、...これ凄いキーの量になると思うんですが？本当にいいのかしら (・・∂) ?								
 キーもプルダウンで作成するとかだろうか？　う～ん...まいっかーやってみるか^^;

 ◆ Sheet1から設定

 手順1 :  入力規則の設定 (元の値に入れる式) 								
 B1 : 元の値=Sheet2!$A$3:$A$8								
 B2 : 元の値=$F$1:$I$1								
 B3 : 元の値=OFFSET(Sheet2!$C$3,0,0,149-COUNTIF(Sheet2!$C$3:$C$150,""),1)								
                                   ~~~~~                      ~~~~~								
 [注意] B3の式は、～～左側～～ を右より -1 して空白を一番下に作成する								
 [注意] 各範囲は、実際のデータ量に合わせてください。								
 [注意]  ( B1,B2,B3セルは、ドロップダウンを移動したい時は、切取 → 貼付けで )								
          もし移動した場合、2～4行目が広く空くので 2行程削除しても可

 [注意] 各範囲は、実際のデータに合わせてください。( 動作テスト後ね )								
 [注意]  ( B3セルは、コピーしてH6セルに張り付け → 空白にして → 下にコピー )								
          ※B3セルのドロップダウンは不要となります。

 ◆ Sheet2 の式を入力

 B3の式=IFERROR(INDEX($D$3:$H$200,ROW()-2,MATCH(Sheet1!$B$1,$D$2:$H$2,0))&"","")								
                            ~~~~~( ※1 )                    ~~~~~~~~( ※2 )

 ※1 : 実際のデータに合わせる( 動作テスト後でよいです ) 								
 ※2 : 2行目の見出し範囲

 C3の式=IFERROR(INDEX($B$3:$B$200,AGGREGATE(15,6,ROW($B$1:$B$200)/(FIND(Sheet1!$B$2,$B$3:$B$200)>0),ROW(A1))),"")                                                                                                   ~~~~~( ※3 )

 B3:C3範囲選択後、下にコピー( どれだけ下まで必要か？それはしりません )								
 B3,C3の式 : どちらも実際のデータに必要な分の行数を増やす( 動作テスト後でよいです )								
 [注意] ※3 : [ $B$1 ]の１は固定する

 これで、おしまい

 お好みで...確認をしやすくする為 ( 条件付き書式をSheet2に設定 )

 A3：最終列 AND 全列の最終行迄で範囲選択								
 数式へ=COUNTIF(A3,"*市*")								
 数式へ=COUNTIF(A3,"*村*")								
 数式へ=COUNTIF(A3,"*☆*")								
 ☆ : ここをキーにする等

 では、確認してください
（あみな） 2023/12/26(火) 21:28:48

 再掲します。
 > 例えばA1で東区を選択した場合、A2で東区内の町村名を検索できる、検索機能付きドロップダウン
 > 西区を選択した場合、A2で西区内の町村名を検索できる、検索機能付きドロップダウン
 それぞれができているのなら、入力規則のリストに設定する式は、
 A1セルの値に応じてIF関数で場合分けして指定する式にすればよいのでは？
 多数あるなら、IFS関数を使ったほうがいいでしょうか？

 ついでに言えば、（ここからが追加部分です）
 ・今出来上がっている、各場合のリスト指定のための式にそれぞれ名前をつけたうえで、 
 ・A2セルのリストに指定する式に、
   =IFS($A$1="東区",東区,$A$1="西区",西区,$A$1="北区",北区)
   とする
 ことでよいのではないかと思います。
（xyz） 2023/12/27(水) 09:36:51

 今回は簡単な例で確認したうえで発言しています。念のため。
（xyz） 2023/12/27(水) 20:43:35

コメント返信：

[ 一覧(最新更新順) ]

[[20231222211905]] 『条件付きの関数設定』（よし） ページの最後に飛ぶ

[[20231222211905]]　『条件付きの関数設定』（よし）　ページの最後に飛ぶ