【本日のミッション】
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」です。
今回の場合は⑧「セルA3～C3」の範囲内で⑥検索値「項目A」は１番目に位置するので「１」を返します。そこから１マイナスしているので「０」という結果になります。
INDEX($A:$C,,MATCH(F6,$A$3:$C$3,0)))
の結果はセル範囲「A3～A8」になります。
今回の場合は、⑨「列A～C」の範囲内で⑪「1」列目、つまりA列のデータの入っているセル範囲「A3～A8」になります。
④COUNTA(INDEX($A:$C,,MATCH(F6,$A$3:$C$3,0)))-1
の結果は「5」になります。
⑫は上記説明より、セル範囲「A3～A8」になるため、空白セル以外のセルの個数は6個になります。そこからマイナス1しているので「5」になります。
上記結果を当てはめて、大分類に「項目A」が選択されたパターンを確認します。
（セルG6に設定されている数式の場合）
セル「①$A$3」から
「②1」行、「③0」列、移動したセルを基準に
「④5」行×「⑤1」列
のセル範囲「A4～A8]がリストとして表示されます。
なかなか理解しづらい関数の使い方ですね。
でも、これが使えると作業時間の削減になりますし、ミスも減りますね♪
参照
「数字が文字列として保存されています。」を一括で数字に変換
データが増減しても参照先を変更しない名前の管理（Excel）
連動するドロップダウンリストを名前の管理・入力規制・OFFSET・INDIRECTを使って「リストが表示されない」を解決（Excel）
Excel 複数セル内の改行（Alt+Enter）を一括で解除する方法
Excel関数を使って 縦の表に連動した（リンクした）横の表を一括で作成せよ
ワイルドカードとは。使い方いろいろ。（Excel）
■スポンサーリンク
■ランキングに参加しています。 ↓このブログを気に入っていただけましたら、ポチッとお願いします。 人気ブログランキングへ
