エクセル 使い方 関数

決定版-この記事だけで習得できる!エクセルのプルダウンリストの連動方法

悩む男

エクセルでプルダウンリストを作ったんだけど、2つ3つを連動させることはできないのかな?

今回はこんな疑問に答えます。

この記事で紹介する『プルダウンリストを連動させる方法」通りに行えば誰でも簡単にプルダウンリストを連動できます。

なぜなら実際の解除手順をエクセルの画像と共にわかりやすく解説しているからです。

この記事では、プルダウンリストの基礎知識と連動手順を紹介して、その後に具体的な例を画像と共に解説します。

記事を読み終えれば、プルダウンリストを連動する方法は習得でき、実際の業務に利用できます。

プルダウンリストとは何?

プルダウンリストの連動方法を紹介する前に、基礎知識を簡単に説明します。

先ずは、そもそもプルダウンリスト(ドロップダウンリスト)とはなんでしょう?

プルダウンリストとは?

エクセルのワークシートで入力したいセルをクリックした時に、入力内容の候補を表示するリストのこと。

プルダウンリストを作成することで、入力の手間を省いたり誤入力を防ぐことができます。半角・全角・スペース・書式も統一されますし、入力するデータのキーワードを覚えておく必要もなくなります。

このようにデータの表記崩れを防ぐために非常に有効な機能です。

つまり、入力作業の効率化ですね。

プルダウンリストの連動とはどういうこと?

次に、「連動」とはどういった意味でしょう?

連動とは?

複数のプルダウンリストを1つの選択値に合わせて、もう片方の選択肢を変化させる(連動させる)こと。

例として、都道府県があります。中部地方を選択すると愛知県や三重県になり、次に愛知県を選択すると、名古屋市や豊田市とリストが自動で表示されるように絞り込む方法です。

なんで連動させる?

それでは、なんで連動させるのでしょうか?

1つのリストではダメなのでしょうか?

連動させることによってどのようなメリットがあるのかみてみます。

連動のメリット

リスト内の選択肢を絞り込んで効率化。

1つのリスト内に選択肢が多すぎると、リストを開いたときにスクロールが必要になり、かえって面倒になります。

ドロップダウンリストを複数に分けて、前のリストで選択した値によって次のリストの内容が絞り込まれると使いやすくなります。

プルダウンリストを連動させる方法は2つ。

ここからが今回の記事のメインパート、どのように連動させるかです。

方法は2つあります。

方法は2つ

  1. 「名前の定義」と「INDIRECT関数」を組み合わせる方法
  2. 「OFFSET関数」と「MATCH関数」を組み合わせる方法

1つ目の「名前の定義」と「INDIRECT関数」を組み合わせる方法ですが、こちらはさらに項目が増えるか、増えないかで方法が異なります。

ポイント

  • 項目が増えない → そのままの方法。
  • 項目が増える  → 「テーブル」を使用する方法。

どちらの方法でも関数を使用することになります。事前に関数の基本的な使い方は身に付けてください。簡単な方法は1番の「名前の定義」と「INDIRECT」関数を使用する方法の方です。関数が苦手な方はこちらだけ読んでいただいても結構です。

先ずは、それぞれの重要用語の解説から。

名前の定義とは何か?

名前の定義

  • 特定のセル範囲に好きな名前をつけること。
  • そのセル範囲は名前で指定することができる。

名前の定義を使えば簡単に範囲選択できたり、関数の引数に設定できたり、入力規則のリスト範囲に指定できたり、メリットがたくさんあります。

名前の定義の仕方は3つ

名前の定義の方法は3つ

  1. 名前Boxから
  2. <数式>タブの「名前の定義」から
  3. <数式>タブの「範囲選択して作成」から

「名前Boxから」と「範囲選択して作成から」は効果がブック全体に及びます。

「名前の定義から」はブックとシート、どちらでも選べます。

テーブル化とは何か?

テーブル

データを効率的に管理するためのエクセルの機能。データベース形式の表をテーブルに変換すると、データの並べ替えや抽出、集計列の追加や列ごとの集計などをすばやく行うことができます。

テーブル化するにもいくつかの決まり事があります。

テーブル化の規則

  1. 先頭行は項目名
  2. 1行1件として入力
  3. データベースとそれ以外のデータは離して作成

INDIRECT関数とは何か?

INDIRECT関数

セルの参照を文字列で指定する関数。

書式:=INDIRECT(参照文字列)

つまり指定したセルの文字列を取り出して表示することです。

OFFSET関数とは何か?

OFFSET関数

指定したセル、セル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を返します。

書式:=OFFSET(基準,行数,列数,[高さ],[幅])

検索・行列関数の1つです。INDIRECT関数と似ていますが、こちらは移動した位置にあるセル範囲を返します。

MATCH関数とは何か?

MATCH関数

指定した範囲内で探したい数値や文字列がどの位置にあるかを列・行番号で返す関数です。

書式:MATCH(検査値,検査範囲,[照合の種類])

照合の種類:1=以下、0=完全一致、-1=以上

MATCH関数を使用することで探したいデータが表の何行目、何列目にあるのかを調べることができます。

プルダウンリストを連動させる手順

それでは実際の手順を紹介します。

名前の定義  x INDIRECT関数(項目が増えない場合)の手順

手順

  1. 基準となるリストのデータに名前を定義する。(名前Aとします)
    1. 最初のプルダウンリストに設定するリストを選択する。
    2. <数式>タブの「名前の定義」を選択。
    3. 表示された「新しい名前」ダイアログボックスの名前の欄に任意の名前をつけてOK ボタンを押す。
  2. 選択範囲から名前を作成する(名前Bとします)
    1. 2つ目のプルダウンリストに設定するデータを名前Aを含んで選択する。
    2. <数式>タブの「選択範囲から作成」を選択。
    3. 「以下に含まれる値から名前を作成」のチェックBOXにチェックをいれてOKボタンを押す。
  3.  1つ目のプルダウンリストを作成する。
    1. 設定したいセルを選択。
    2. <データ>タブの「データの入力規則」を選択。
    3. 表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」に名前Aを入力。OKボタンを押す。
  4. 連動させるリストを作成する。
    1. 設定したいセルを選択。
    2. <データ>タブの「データの入力規則」を選択。
    3. 表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」に『=INDIRECT(手順3のセル番号)』と入力してOKを押す。
  5. アラートを消す。
    1. 「元の値はエラーと判断されます。続けますか?」と表示されたアラートで「はい」を押す。
  6. 完了。

名前の定義  x INDIRECT関数(項目が増える場合)の手順

手順

  1. データをテーブル化します。
    1. テーブルにしたいデータのどこかを選択して、<ホーム>タブの「テーブルとして書式設定」を選択。
    2. 任意のスタイルを選択。
    3. 「テーブルとして書式設定」ダイアログボックスで選択範囲を確認し、「先頭行をテーブルの見出しとして使用する」にチェックを入れてOKを押す。
  2. ここからは先ほどの(項目が増えない場合)と同じです。

OFFSET関数 x INDIRECT関数 の手順

手順

  1. 1つ目のプルダウンリストを作成する。
    1. 設定したいセルを選択。
    2. <データ>タブの「データの入力規則」を選択。
    3. 表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」右端の「↑」を選択。
    4. プルダウンリストに設定したいデータを選択して「Enter」を押す。
    5. OKボタンを押す。
  2. 連動させるプルダウンリストを作成する。
    1. 設定したいセルを選択。
    2. <データ>タブの「データの入力規則」を選択。
    3. 表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」にOFFSET関数とMATCH関数を入力。
  3. アラートを消す。
    1. 「元の値はエラーと判断されます。続けますか?」と表示されたアラートで「はい」を押す。
  4. 完了。

できるExcel関数 Office 365/2019/2016/2013/201 データ処理の効率アップに役立つ本 [ 尾崎裕子 ]

価格:1,738円
(2020/10/21 00:05時点)

具体例:画像と共に解説。

それでは、具体例を画像と共に詳しく解説していきます。今回はデパートやモールを例に説明します。大きなデパートでしたらそれぞれのフロア別に商品が分けられています。1階に食料品、2階に衣類、3階に電化製品などです。電化製品の階でも、パソコンや洗濯機などが各コーナーに分かれています。このように枝分かれするデータをプルダウンリストの連動で設定します。

デパートで解説

基準となる最初のプルダウンリストを各フロアの「食料品、衣類、電化製品」で設定し、連動されるプルダウンリストを各フロアの商品(果物、パソコンなど)にします。

下記のエクセルデータを元に設定します。

具体例の表

名前の定義  x INDIRECT関数(項目が増えない場合)の方法

1,基準となるリストのデータに名前を定義する。

最初のプルダウンリストに設定するリストを選択する。A1セルからA3セルです。

最初のプルダウンリスト

<数式>タブの「名前の定義」を選択。

名前の定義

表示された「新しい名前」ダイアログボックスの名前の欄で「フロア」と名前をつけてOK ボタンを押す。

新しい名前

名前Boxから確認すると、A1セルからA3セルが「フロア」と名前が付けられています。

名前Boxに直接入力しても名前を付けられます。

名前Box

2,選択範囲から名前を作成する。

2つ目のプルダウンリストに設定するデータを「フロア」を含んで選択する。つまり全てのデータを選択して名前を作成します。

表

<数式>タブの「選択範囲から作成」を選択。

選択範囲から作成

表示されたダイアログボックスの「以下に含まれる値から名前を作成」の「左端列」のチェックBOXにチェックをいれてOKボタンを押す。

選択範囲から名前

今回は1つ目のリストデータが左端に作ってあるため「左端列」にチェックを入れます。上端行にデータを作成すれば「上端行」にチェックを入れます。

「選択範囲から作成」で名前を作成すると上記設定の場合、「食料品」、「衣類」、「電化製品」の3つの名前が作成され、項目を含まない右方向の範囲が(果物、パソコン)が設定されます。

電化製品
700

上記画像はB1-D1セルを選択したものです。名前Boxを見ると項目名の「電化製品」と表示されます。

3,1つ目のプルダウンリストを作成する。

設定したいセルを選択。B5セルにします。

<データ>タブの「データの入力規則」を選択。

データの入力規則を選択

表示されたダイアログボックスで「入力値の種類」で「リスト」、

「元の値」に「=フロア」と入力。OKボタンを押す。

フロアと入力

1つ目のプルダウンリストができました。

プルダウンリストができた
700

4,連動させるリストを作成する。

設定したいセルを選択。B6セルにします。<データ>タブの「データの入力規則」を選択。表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」に『=INDIRECT(手順3のセル番号)』と入力してOKを押す。

INDIRECT関数

5,アラートを消す。

B5セルが空白の場合「元の値はエラーと判断されます。続けますか?」とアラートが表示されますが「はい」を押す。

アラート

6,完了。動作確認。

動作の確認をします。1つ目のプルダウン リストで「食料品」を選択すると、「果物、野菜、冷凍食品」が選択できるようになりました。

動作確認

名前の定義  x INDIRECT関数(項目が増える場合)の方法

1,データをテーブル化します。

実は先ほどのデパートの具体例の画像ですとテーブル化できません。

テーブル化するための表を変える

テーブル化の規則を思い出してください。先頭行は項目名でなければいけません。テーブル化するときによく陥るミスです。ですので、テーブル化できるように表の行と列を入れ替えたものが下記の画像です。このデータをテーブル化していきます。

テーブル化するための表

1-1,テーブルにしたいデータのどこかを選択して、<ホーム>タブの「テーブルとして書式設定」を選択。

表をテーブルに変換するには、テーブルに変換したいセル範囲のどこかのセルを選択状態にしておけばテーブルかしたい範囲を自動でエクセルが認識します。

テーブルとして書式設定

1-2,任意のスタイルを選択。

表示されたスタイルから好きな色を選びます。自分の見やすい色で結構です。

色のスタイル

今回は「中間」の青色にします。

1-3,「テーブルとして書式設定」ダイアログボックスで選択範囲を確認し、「先頭行をテーブルの見出しとして使用する」にチェックを入れてOKを押す。

テーブルとして書式設定画面

テーブルに変換できました。

テーブルにできた

ここからは「名前の定義  x INDIRECT関数(項目が増えない場合)の手順」と同じですので省略します。

OFFSET関数  x MATCH関数の方法

1,1つ目のプルダウンリストを作成する。

1-1,設定したいセルを選択。

1-2,<データ>タブの「データの入力規則」を選択。

データの入力規則MATCH関数

1-3,表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」右端の「↑」を選択。

データの入力規則の画面

1-4,プルダウンリストに設定したいデータを選択して「Enter」を押す。

プルダウンリストの設定方法

「電化製品、衣類、食料品」のA1,2,3セルを選択します。

1-5,OKボタンを押す。1つ目のプルダウンリストが設定できました。

設定できた

2,連動させるプルダウンリストを作成する。

2-1,設定したいセルを選択。B6セルに設定します。

品目のB6セル

2-2,<データ>タブの「データの入力規則」を選択。

データの入力規則4

2-3,表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」に「=OFFSET($A$1,MATCH(B5,A1:A3,0)-1,1,1,3)」を入力。OKを押す。

関数が2つ

複数のプルダウンリストを連動させるためにOFFSET関数とMATCH関数を組み合わせて使用しています。長い数式ですので分解して説明します。MATCH関数は探したい情報がどこにあるかを見つけるための関数です。

見つけたい1つ目のプルダウン リストの情報「B5セル」が何行目にあるかを探します。(今回の表では行ごとに項目が分かれているため「行」です。列毎に分かれていれば「列」を探します)

書式は:MATCH(検査値,検査範囲,[照合の種類]) でしたね。「検査値」が見つけたい情報、「検査範囲」が探す範囲、「照合の種類」が判定です。

B5セルを見つけるのに、「A1セルからA3セル」までの範囲で探します。判定は、照合の種類:1=以下、0=完全一致、-1=以上 でしたので、完全一致する「0」になります。

matchi関数の説明画面

次にOFFSET関数です。先ず簡単に関数の説明です。OFFSET関数は、「指定したセル、セル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を返します。」でしたね。

OFFSET関数の解説画面
700700

上の画像ですとOFFSET関数が返す値は「ここ」になります。範囲の基準から1行下がり、2列右に移動した1の高さ、幅のセルです。これを今回の数式で見ると書式は下記ですので、

書式:=OFFSET(基準,行数,列数,[高さ],[幅])

基準=「セル、セル範囲から」です。今回の表ですと「A1セル」です。絶対参照で括って「$A$1」にしてあります。

高さ=今回の表では「電化製品」を例にすると1行だけですので「1」です。

幅=今回の表では「電化製品」を例にすると3列ありますので「3」です。

行数=基準から上方向、又は下方向へ移動する距離を数値で指定します。

「0」を指定すると基準と同じ行、「1」だと基準の1つ下の行、「-1」だと基準より上の行を指定できます。

この行数を今回はMATCH関数で取り出します。今回の表では「電化製品」を例にすると「1」を返しますが、OFFSET関数の行数の基準となる数字は「0」ですので「-1」にしなければいけません。

offset関数

3,アラートを消す。

3-1,「元の値はエラーと判断されます。続けますか?」と表示されたアラートで「はい」を押す。

アラート画面

4,完了。動作確認。

動作確認中offset関数

1つ目のプルダウンリストで選択した「電化製品」の連動で2つ目のプルダウン リストの「品目」に「TV、パソコン、洗濯機」が選択できるようになりました。

まとめ。

以上でプルダウンリストの連動方法でした。少し長くなりましたが、この記事を復習していただければもうプルダウンリストの連動方法で悩むこともありません。簡単にまとめます。

プルダウンリストの連動には、先ず下記を理解したしてください。

ポイント

  1. プルダウンリストとは何か?
  2. プルダウンリストの連動とはどういうこと?
  3. なんで連動させる?

理解できたら実際の連動方法がこちら、

ポイント

プルダウンリストを連動させる方法は2つ。

  1. 名前の定義」と「INDIRECT関数」を組み合わせる方法
    • 項目が増える場合は「テーブル化」
  2. OFFSET関数」と「MATCH関数」を組み合わせる方法

そして最後に下記を理解できれば、あとは手順を確認するだけです。

ポイント

  • 名前の定義とは何か?
  • テーブル化とは何か?
  • INDIRECT関数とは何か?
  • OFFSET関数とは何か?
  • MATCH関数とは何か?

すこし難しい関数もでてきますが、一つ一つ身につけてください。

-エクセル, 使い方, 関数

© 2021 World from Excel Powered by AFFINGER5