【本日のミッション】
Excel にて「名前の管理」「データの入力規制」「OFFSET」を使って参照元のデータが増減しても数式を変更しなくてもよい連動ドロップダウンリストを設定せよ。
目次
ミッションの概要
ミッションの文章が長すぎてイメージがわきませんね。
下記図でご説明しますね。
最終的に作りたいのはセルF5～G15の表です。
大分類(項目A～項目C）の選択内容によって、対応する小分類のリストを表示させたいのです。
項目A→セルA4～A10の内容の小分類リストを表示
項目B→セルB4～B6の内容の小分類リストを表示
項目C→セルC4～C8の内容の小分類リストを表示
しかも、小分類のデータが増減しても、それに対応する設定にしておくこと。
これをするために、名前の管理・データの入力規制を駆使したのですが、大分類は表示されても小分類はリスト表示されませんでした。どのような設定をしたらよいのでしょうか。
名前の管理・入力規制・OFFSET・MATCH・INDEXを使用して「リストが表示されない」を解決！
データの増減に備えてなんとか手間のかからない設定にしたい！
ということで、下記設定で解決できました。
大分類のみを名前の管理で設定
データ範囲が増減しても参照先が自動で変動する「名前の管理」の設定（Excel）を参考に、大分類のみを名前の管理に設定します。
大分類：「=OFFSET(名前の管理!$A$3,0,0,1,COUNTA(名前の管理!$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」列、移動したセルを基準（今回はセルA4）に
「④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～A10」になります。
INDEX(⑨配列, ⑩行番号, [⑪列番号]) 対象範囲の中から行番号と列番号の交差するセルの参照を取得します。 というのがINDEX関数の基本的な使い方なのですが、今回は特殊な使い方をしています。 「⑩行番号」と「⑪列番号」の、どちらか一方に「０」（または省略（空白））にすると、 指定された行（または列）に含まれる全データ範囲を取得することができます。 ⑨配列：A～C列 ⑩行番号：省略（空白）することで⑪で指定した列に含まれる全データ範囲を取得します。 ⑪列番号：上記③MATCH(F6,$A$3:$C$3,0)-１を参照ください。 結果は「1」列目、つまりA列になります。
今回の場合は、⑨「列A～C」の範囲内で⑪「1」列目、つまりA列のデータの入っているセル範囲「A3～A10」になります。
④COUNTA(INDEX($A:$C,,MATCH(F6,$A$3:$C$3,0)))-1
の結果は「7」になります。
COUNTA(範囲 1, [範囲 2], ...) 範囲に含まれる空白ではないセルの個数を返します。
⑫は上記説明より、セル範囲「A3～A10」になるため、空白セル以外のセルの個数は8個になります。そこからマイナス1しているので「7」になります。
上記結果を当てはめて、大分類に「項目A」が選択されたパターンを確認します。
（セルG6に設定されている数式の場合）
セル「①$A$3」から
「②1」行、「③0」列、移動したセルを基準（今回はセルA4）に
「④7」行×「⑤1」列
のセル範囲「A4～A10」がリストとして表示されます。
なかなか理解しづらい関数の使い方ですね。
でも、これが使えると作業時間の削減になりますし、ミスも減りますね♪
参照
「数字が文字列として保存されています。」を一括で数字に変換
データ範囲が増減しても参照先が自動で変動する「名前の管理」の設定（Excel）
連動するドロップダウンリストを名前の管理・入力規制・OFFSETを使って「リストが表示されない」を解決（Excel）
Excel 複数セル内の改行（Alt+Enter）を一括で解除する方法
Excel関数を使って 縦の表に連動した（リンクした）横の表を一括で作成せよ
ワイルドカードとは。使い方いろいろ。（Excel）
Excelの改ページの点線を消す方法
Excelの背景にある薄グレーの枠線（罫線）を消す（非表示）方法
非表示になってしまった列を表示させる方法
ファイルを開くと空の画面も同時に開くのを解決（Excel）
図 画像 グラフ ボタン などのオブジェクトが消えてしまった時の表示方法（Excel）
大量の画像やグラフ等のオブジェクトで画面表示が遅いのを解決（Excel）
保護されたシートに対して、このコマンドは使用できません。テーマが変えられない現象を解決（Excel）
離れた複数列をまとめて列番号（数字）で選択する方法（Excel VBA）
Excel VBA で選択した複数のセルにデータ・数式を（Ctrl+Enter のように）一気に入力する方法
