連動するドロップダウンリストを名前の管理・入力規制・OFFSET・INDIRECTを使って「リストが表示されない」を解決（Excel）

【本日のミッション】

Excel にて「名前の管理」「データの入力規制」を使って参照元のデータが増減しても数式を変更しなくてもよい連動ドロップダウンリストを設定せよ。

ミッションの文章が長すぎてイメージがわきませんね。
下記図でご説明しますね。

最終的に作りたいのはセルF5～G15の表です。

大分類(項目A～項目C）の選択内容によって対応する小分類のリストを表示させたいのです。

項目A→セルA4～A8の内容の小分類リストを表示
項目B→セルB4～B10の内容の小分類リストを表示
項目C→セルC4～C6の内容の小分類リストを表示

しかも、小分類のデータが増減しても、それに対応する設定にしておくこと。

■スポンサーリンク

■名前の管理・データの入力規制・OFFSET・INDIRECTを使用してうまくいかなかった

これは失敗した話です。恐らく皆様も同じ経験をなさっているのではないでしょうか。
解決方法を知りたい方は次の項目へお進みください。

データが増減しても参照先を変更しない名前の管理（Excel）

を利用して、名前の管理でリストのデータ数が変動しても大丈夫なように設定しました。

大分類：=OFFSET(Sheet1!$A$3,0,0,1,COUNTA(Sheet1!$3:$3))
項目A：=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-1,1)
項目B：=OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B:$B)-1,1)
項目C：=OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C:$C)-1,1)

それからデータの入力規制のリストを設定。

セルF6~F15：「=大分類」
セルG6~G15：「=INDIRECT（F6）」

これでバッチリ！と思ったのですが、リスト表示されたのはF6～F15の大分類だけ。

小分類はリスト表示すらされませんでした。

項目A～項目Cの名前の管理を下記のように設定すると小分類のリストも表示されるのですが、これではデータが増減する度に変更しないといけません。

項目A：=Sheet1!$A$4:$A$8
項目B：=Sheet1!$B$4:$B$10
項目C：=Sheet1!$C$4:$C$6

■名前の管理・データの入力規制・OFFSET・INDIRECTを使用してスマートに解決！

データの増減に備えてなんとか手間のかからない設定にしたい！
ということで、下記のような設定で解決できました。

１．大分類を「名前の管理」で設定
データが増減しても参照先を変更しない名前の管理（Excel）を参考に、大分類を名前の管理に設定します。

大分類：「=OFFSET(Sheet1!$A$3,0,0,1,COUNTA(Sheet1!$3:$3))」

２．セルF6～F15に入力規制を設定します。
セルF6～F15を選択し、[データ]－[データツール]ー[データの入力規制]をクリックします。

データの入力規制に下記のように設定します。

入力値の種類：リスト
元の値：「=大分類」
ドロップダウンリストから選択するにチェック

この設定で、大分類のリストが出てくるようになります。

３．セルG6～G15に入力規制を設定します。

入力値の種類：リスト
元の値：=OFFSET($A$3,1,MATCH(F6,$A$3:$C$3,0)-1,COUNTA(INDEX($A:$C,,MATCH(F6,$A$3:$C$3,0)))-1,1)
※MATCH関数の1つ目の引数は必ず相対値（$の付いていない値）にしておきます。
ドロップダウンリストから選択するにチェック

設定は以上です。
大分類の選択結果に応じて、対応する小分類のリストが連動表示されるようになりました。
リスト件数も、データ件数と同じです♪

数式が複雑すぎてよくわかりませんので、次の項目でご説明させていただきます。

■スポンサーリンク

■入力規制の数式説明（セルG6に設定されている数式の場合）

セル「①$A$3」から

「②1」行、「③MATCH(F6,$A$3:$C$3,0)-1」列、移動したセルを基準に

「④COUNTA(INDEX($A:$C,,MATCH(F6,$A$3:$C$3,0)))-1」行×「⑤1」列

の範囲ということになります。

③MATCH(F6,$A$3:$C$3,0)-１

の結果は「0」です。


MATCH(⑥検索値,⑦範囲 ,⑧照合の型)
範囲内で検索値を探し、見つかった値が範囲内で何番目に位置するかを数値で返します。
⑥検索値：今回の場合は「項目A」になります。
　　　　　入力規制を設定しているセル全てに対応するように
　　　　　引数は必ず相対値（$の付いていない値）にしておきます。
⑧範囲：セルA3～C3
⑧照合の型：「０」の場合は完全に一致する値のみ検索します。

今回の場合は⑧「セルA3～C3」の範囲内で⑥検索値「項目A」は１番目に位置するので「１」を返します。そこから１マイナスしているので「０」という結果になります。

INDEX($A:$C,,MATCH(F6,$A$3:$C$3,0)))

の結果はセル範囲「A3～A8」になります。


INDEX(⑨配列, ⑩行番号, [⑪列番号])
対象範囲の中から行番号と列番号の交差するセルの参照を取得します。

というのがINDEX関数の基本的な使い方なのですが、今回は特殊な使い方をしています。

「⑩行番号」と「⑪列番号」の、どちらか一方に「０」（または省略（空白））にすると、
指定された行（または列）に含まれる全データ範囲を取得することができます。

⑨配列：A～C列
⑩行番号：省略（空白）することで⑪で指定した列に含まれる全データ範囲を取得します。
⑪列番号：上記③MATCH(F6,$A$3:$C$3,0)-１を参照ください。
　　　　　結果は「1」列目、つまりA列になります。

今回の場合は、⑨「列A～C」の範囲内で⑪「1」列目、つまりA列のデータの入っているセル範囲「A3～A8」になります。

④COUNTA(INDEX($A:$C,,MATCH(F6,$A$3:$C$3,0)))-1

の結果は「5」になります。


COUNTA(範囲 1, [範囲 2], ...)
範囲に含まれる空白ではないセルの個数を返します。

⑫は上記説明より、セル範囲「A3～A8」になるため、空白セル以外のセルの個数は6個になります。そこからマイナス1しているので「5」になります。

上記結果を当てはめて、大分類に「項目A」が選択されたパターンを確認します。
（セルG6に設定されている数式の場合）

セル「①$A$3」から

「②1」行、「③0」列、移動したセルを基準に

「④5」行×「⑤1」列

のセル範囲「A4～A8]がリストとして表示されます。

なかなか理解しづらい関数の使い方ですね。

でも、これが使えると作業時間の削減になりますし、ミスも減りますね♪

■スポンサーリンク

■ランキングに参加しています。
↓このブログを気に入っていただけましたら、ポチッとお願いします。

にほんブログ村