エクセル 3段階のリストを連動させる方法(2つ目のリスト項目に重複がある場合)
複数のプルダウンを連動させる手順 連動させるシートの例 具体的にはこのような例で利用します。 地方と都道府県のセルがあり、 地方のセルを選択すると都道府県の選択肢が変化します。 プルダウンで地方を選択すると 都道府県のプルダウンに選択した地方に対応する選択肢が表示される 地方が変わると都道府県も変化 簡単な方法 セル範囲名とINDIRECT関数 地方のプルダウン設定手順 まずこのような地方と都道府県の一覧表を用意します。 地方と都道府県の一覧表 次に地方の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択します。 メニューの「データ」、「データの入力規則」の位置 次に「入力値の種類」より「リスト」を選択し、「元の値」に地方データのセル範囲を設定します。 「データの入力規則ウインドウ」の「入力値の種類」と「元の値」の位置 これによりプルダウンで地方を選択できる状態になります。 プルダウンで地方を選択 都道府県のプルダウン設定手順 都道府県を地方ごとにを設定します。 それには都道府県を地方ごとに範囲選択します。 なお空白セルは選択範囲から除く必要があります。 その状態で数式バーの左にあるセル名欄に地方の名前を入力します。 この名前は一覧表の地方名と一致する必要があります。 具体的なセルの配置 これを地方の数だけ繰り返し、このような状態にします。 一覧表のセル範囲とセル範囲名の対応 なおセル範囲名の設定を間違えた場合はする必要があります。 最後に都道府県の入力セルにプルダウンを設定します。 都道府県の入力セルを選択し、メニューの「データ」より「データの入力規則」を選択します。 「入力規則」の画面を起動し、入力値の種類を「リスト」に変更します。 そして元の値にを利用し下記の数式を設定します。 （プルダウンメニューが連動） プルダウンで地方を選択 数式の説明（読み飛ばし可） は文字列のやアドレスをセル参照に変換する関数です。 「元の値」に指定したを使用することは可能ですが セルを指定するとセル範囲でなくただの文字列として扱われます。 それでは動作しないのででセル範囲に変換する必要があります。 高度な方法 OFFSET関数とMATCH関数 セル範囲を設定する方法との相違点（メリット・デメリット） 先述のセル範囲の方法では地方ごとにセル範囲名を設定する手間があります。 （連動元の選択肢の数だけ設定の手間） また選択肢が増えたり変わる場合もセル範囲からやり直す必要があります。 とを使用した方法ではそれらの問題が解決します。 しかしそのトレードオフで数式が複雑になり理解が難しくなります。 方法 メリット デメリット セル範囲名 ・設定が簡単で分かりやすい。 ・選択肢が多いとセル範囲名設定の手間が膨大になる。 ・選択肢の変更があるとセル範囲名の設定変更が必要。 と ・設定の手間が少なく、選択肢の変更でも作業が発生しない ・数式が複雑 よってこれから紹介する方法は連動元プルダウンの選択肢が多い場合か 頻繁に変更が発生する場合にのみ検討する方法になります。 設定手順（連動先の選択肢が横方向の場合） 地方（連動元）の設定はセル範囲のものと変わりません。 都道府県（連動先）の入力規則の設定で下記のような数式を設定します。 ただしこの方法は都道府県（連動先）が横方向に並んでいる場合のみです。 縦方向の場合、数式を変える必要があります。 設定手順（連動先の選択肢が縦方向の場合） このような数式になります。 北海道は1件のみのため多くの空白が発生 これは最初の方法のは選択肢に空白を含まない範囲を選ぶことで 空白を発生させないことが可能です。 しかしとの例では連動先の最大選択肢数として 固定値（例は9）を入れた影響で全ての選択で空白を含めた9個の選択肢が 表示される状態になってしまいます。 これに対応するには入力規則に設定する数式をを入れて改造します。 ただし数式が更に長く複雑になるため、このままで問題ない場合は そのままにしておくのもよいでしょう。 まず選択肢が横方向の場合の数式です。 太字が数式の改修箇所です。 これは一度目のとで連動元の選択肢に該当する個数を で算出し、（ここが太字の改修箇所） それを二度目のとの取得件数に使用しています。 これにより余計な空白を除外することが可能です。 連動先の選択肢から不要な空白を除外した状態 ただし選択肢が空白セルなしに詰められている必要があります。 選択肢の途中に空白が混ざっていると、空白削除不可 このような状態になってしまうためこれが発生しないように 横方向なら左に、縦方向なら上に空白無しに詰めるようにしましょう。 関連記事 応用例 BMI判定をVLOOKUP関数で行う方法.
次の
