こんにちは、ふみです。みなさんは表計算ソフト「Microsoft Excel（エクセル）」で2つのドロップダウンリスト（プルダウンリスト）を使ってリストから情報を取得することはありますか？
一つのリストから所属と名前の入力をドロップダウンリストで選択できるようにするには、所属の入力用ドロップダウンリストとして重複しない所属名一覧のリスト、名前の入力用ドロップダウンリストとして所属名でフィルターがかかる名前一覧のリストの2つが必要になります。（Excel のフィルター機能はセルの値を指定することができないので、ドロップダウンリストで入力するデータに連動させることができません。）
Microsoft365 の Excel では所属名一覧のリストのような重複しないリストは UNIQUE関数、所属名でフィルターがかかるような、条件に一致するリストは FILTER関数を使うと簡単に作成できますが、ここでは UNIQUE関数や FILTER関数が使えない Office 2019 の Excel でもできる代替の方法を紹介します。
CONTENTS
重複しないリストを作る（UNIQUE関数の代替）
名簿のリストから重複しない所属名の一覧が表示されるように数式を設定します。
普段からデータベースを使っている方には SQL の DISTINCT に相当する数式と表現すると分かりやすいと思います。
Office 2019 の Excel で使用できない UNIQUE関数を使わずに一つのリストから重複しない値の一覧を作るには、数式を設定する列を2列用意します。
名簿のリストから重複しない所属名の一覧を作る手順は次のとおりです。
１．
シート「名簿」のA～D列に参照するリストがあり、A列に所属名があるとき、セルF2に次の数式を設定した後、ドラッグ＆ドロップでリストの下までコピーする。
=IF(($A2="")+COUNTIF($A$1:$A1,$A2),"",ROW()-COUNTBLANK($F$1:$F1))
２．
セルG1に次の数式を設定した後、ドラッグ＆ドロップでリストの下までコピーする。
=IF(ISERROR(MATCH(ROW(),$F:$F,0)),"",INDIRECT("A"&MATCH(ROW(),$F:$F,0)))
これで完成です。G列は所属名の入力用ドロップダウンリストとして活用できます。
所属名の入力をドロップダウンリストで選択できるようにする
シート「名簿」とは別のシートに名簿検索用のシートを作る例を紹介します。名簿検索用シートの名前は「入力」です。
シート「名簿」の G列に名前が記載されているリストがあった場合、名簿検索用シート「入力」のセルB3に入力する所属名をドロップダウンリストにする設定の手順は次のとおりです。
１．
セルB3をクリック後、「データ」のタブをクリック→「データの入力規則」→「データの入力規則(V)…」をクリックする。
２．
「設定」タブの「入力値の種類(A):」から「リスト」を選択→元の値(S)を名前リストの範囲「名簿!$G:$G」を入力（もしくはテキストボックス右側のボタンから所属名リストの範囲を選択）後、「OK」をクリックする。
これで名簿検索用シート「入力」のセルB3に重複しない所属名のドロップダウンリストが設定されます。
条件に一致したリストを作る（FILTER関数の代替）
所属名をドロップダウンリストから選択した時に、所属が該当する名前のリストが表示されるように数式を設定します。
普段からデータベースを使っている方には SQL の WHERE句 に相当する数式と表現すると分かりやすいと思います。
Excel のフィルター機能を使うと特定の所属名のリストが簡単にできますが、入力規則のドロップダウンリストで入力した値を活用して、Excel のフィルター機能を使うことができません。
関数を使って条件に一致したリストが表示されるように数式で設定する必要があります。
Office 2019 の Excel で使用できない FILTER関数を使わずに条件に一致したリストを作るには、数式を設定する列を、抽出するリストの列数 + 1列用意します。
名簿検索用シート「入力」のセルB3で入力した所属名に一致するリストを作る手順は次のとおりです。
１．
シート「名簿」の A～D列に参照するリストがあり、A列に所属名があるとき、セルH2に次の数式を設定した後、ドラッグ＆ドロップでリストの下までコピーする。（下の図は名簿検索用シート「入力」のセルB3に「A社」が入力された状態なので、A列が「A社」になっている行のセルに昇順の数字が表示される。）
=IF(($A2="")+($A2<>入力!$B$3),"",ROW()-COUNTBLANK($H$1:$H1))
２．
セルI1に次の数式を設定した後、ドラッグ＆ドロップでリストの下までコピーする。
=IF(ISERROR(MATCH(ROW(),$H:$H,0)),"",INDIRECT(ADDRESS(MATCH(ROW(),$H:$H,0),COLUMN(A:A))))
３．
この状態のままドラッグ＆ドロップでL列までコピーすると、名簿検索用シート「入力」のセルB3で入力した所属名に一致するリストが I～L列に表示される。
所属名が一致している名前をドロップダウンリストで指定して情報を取得する
所属の名前のリストが表示されるようになれば、所属名を選択した後に名前を選択して情報を取得することができるようになります。
ここでは「所属名の入力をドロップダウンリストで選択できるようにする」で作成した名簿検索用のシート「入力」に「条件に一致したリストを作る」で作成したリストを参照する数式を設定します。
所属名が一致している名前をドロップダウンリストで指定して情報を取得できるようにする設定の手順は次のとおりです。
１．
名簿検索用のシート「入力」のセルC3をクリック後、「データ」のタブをクリック→「データの入力規則」→「データの入力規則(V)…」をクリックする。
２．
「設定」タブの「入力値の種類(A):」から「リスト」を選択→元の値(S)を名前リストの範囲「名簿!$J:$J」を入力（もしくはテキストボックス右側のボタンから所属名リストの範囲を選択）後、「OK」をクリックする。
これでセルC3にセルB3で入力した所属に該当する人の名前のドロップダウンリストが設定されます。
３．
セルD3に次の数式を設定した後、ドラッグ＆ドロップでセルE3にコピーする。
=IF(COUNTA($B$3,$C$3)<2,"",VLOOKUP($C$3,名簿!$J:$L,2,FALSE))
４．
セルE3の数式のVLOOKUP関数の列番号「2」を「3」に変更して、表示内容を性別から年齢に変更する。変更後の数式は次のとおり。
=IF(COUNTA($B$3,$C$3)<2,"",VLOOKUP($C$3,名簿!$J:$L,3,FALSE))
以上で、2つのドロップダウンリストで指定して情報を取得できるようにする設定が完了します。
Excel関数の豆知識
IF関数は論理式が0の時に False、0以外は True
「重複しないリストを作る」で紹介した、最初のIF関数を使った空白処理の論理式「($A2="")+COUNTIF($A$1:$A1,$A2)」は足し算になっています。
この数式はセルA2が入力されていない時に1が加算され、さらにセルA1～A1にセルA2のデータの数を加算しています。
そして、IF関数は0以外の時に True になる性質をもっているので、セルA2が未入力か、同じ列の上方に重複データがあるときに数値が1以上になるので空白処理になります。
IF関数 、ROW関数、COUNTBLANK関数の組み合わせ
ROW関数はセルの行番号を取得する関数です。そして、COUNTBLANK関数は指定した範囲の空白セルの数を数える関数です。
IF関数で条件を指定してROW関数と COUNTBLANK関数を組み合わせて、1行目のセルから数式を設定するセルの1行上のセルまでを範囲指定すると、条件に一致するデータと同じ行に昇順の通し番号を割り振ることができます。
ISERROR関数 、MATCH関数の組み合わせ
ISERROR関数はエラーの時に True になる関数です。MATCH関数は検索値と一致するセルがないとエラー「#N/A」になるので、空白処理の論理式に ISERROR関数とMATCH関数を組み合わせて使うと、検索値と一致するセルがないときはエラーを表示する代わりにセルを空白にすることができます。
INDIRECT関数、MATCH関数の組み合わせ
参照文字列からアドレスを読み取ってセルを参照する場合はINDIRECT関数を使います。
INDIRECT関数はカッコ内の参照文字列からセルを参照する関数です。
指定する列番号の文字と MATCH関数で調べた行番号を「&」でつなげると、VLOOKUP関数のような使い方ができます。
INDIRECT関数、ADDRESS関数、MATCH関数、COLUMN関数の組み合わせ
INDIRECT関数+ MATCH関数の組み合わせは横方向にコピーしたときに相対参照で列番号の参照先をスライドさせることができません。
INDIRECT関数を横方向にコピーしたときに相対参照で列番号の参照先をスライドさせるには、INDIRECT関数に ADDRESS関数と MATCH関数、COLUMN関数を組み合わせて使います。
INDIRECT関数で任意の列番号を文字列で指定すると相対参照になりませんが、COLUMN関数の列番号を相対参照のアドレスで指定すると参照先を相対参照にすることができます。
INDIRECT関数で指定する列番号は英字の為、数値の列番号はそのまま組み合わせることができないので、つなぎに ADDRESS関数を使うと列番号を数値指定できるようになります。
セルの入力が2か所以上必要なときの空白処理
セルの入力が2か所以上必要なときの空白処理は COUNTA関数を使います。
「所属名が一致している名前をドロップダウンリストで指定して情報を取得する」では次の数式で使っています。
=IF(COUNTA($B$3,$C$3)<2,"",VLOOKUP($C$3,名簿!$J:$L,2,FALSE))
この数式はCOUNTA関数を使わない形にすると
=IF($B$3="","",IF($C$3="","",VLOOKUP($C$3,名簿!$J:$L,2,FALSE)))
となり、IF関数とVLOOKUP関数を囲むカッコの数が多くなり、数式が分かりにくくなってしまいます。
COUNTA関数は空白でないセルを数える関数です。ただし、空白処理されたセルの「""」は空白でないセルとして数えるので、空白処理のセルを参照するときは注意が必要です。
COUNTBLANK関数でもセルの入力が2か所以上必要なときの空白処理に使うことができますが、こちらは範囲の指定が「:」（コロン）のみで、アドレスを「,」（カンマ）で区切って指定することができません。
まとめ
- IF関数は0以外の時に True になる
- IF関数で条件を指定して ROW関数と COUNTBLANK関数を組み合わせると条件に一致するデータと同じ行に昇順の通し番号を割り振ることができる
- 空白処理の論理式に ISERROR関数と MATCH関数を組み合わせて使うと、検索値と一致するセルがないときにセルを空白にすることができる
- 任意の列番号の文字と MATCH関数で調べた行番号をつなげると、VLOOKUP関数のような使い方ができる
- INDIRECT関数を横方向にコピーしたときに相対参照で列番号の参照先をスライドさせるには、INDIRECT関数に ADDRESS関数と MATCH関数、COLUMN関数を組み合わせて使う
- セルの入力が2か所以上必要なときの空白処理は COUNTA関数を使う
ありがとうございました。
コメント