実施環境
Microsoft Office Professional Plus 2016
0. 概要
Excel において「誰かに入力させるフォーマット」を作成する際、事前に指定した値以外を入力させたくない場合があります。
そのようなときに利用できる方法の 1 つとして、「データの入力規則」を利用してドロップダウンリストを作るという方法があります。
今回はこのドロップダウンリストについて、いろいろな方法で作っていきます。
1. 単純なドロップダウンリスト
まずは、単純なドロップダウンリストを作ってみます。
ドロップダウンリストを設定するセルを選択した状態で、「データ」タブの「データの入力規則」をクリックします。
すると、以下のようなウィンドウが開きます。
「設定」タブの「入力値の種類」を「リスト」に変更します。
「入力値の種類」はリスト以外にも選択肢があり、「日付」で特定の期間の日付だけを入れられるようにしたり、「文字列」で文字数制限を課したりすることもできます。
「元の値」に a,b,c と入力して「 OK 」をクリックしてみます。
すると、以下のようなドロップダウンリストが作成できました。
値はリスト選択でなく直接入力もできますが、リストにない値を入れようとすると以下のようなエラーが出て入力が弾かれます。
2. 入力必須のドロップダウンリスト
2.1. 空白を無視する
入力必須のドロップダウンリストを作りたい場合、一応、「空白を無視する」のチェックを外せば、セル内に入って値を削除したときにエラーが出るようにはできます。
ただ、デフォルト値が空白なら結局空白のままにするということができてしまいますし、実はセル内に入らず Delete キーで値を削除した場合はエラーが出なかったりします。
マクロで無理やり空白にさせないこともできなくはないですが、空白を許容する作りにするか、気付きやすくするレベルに抑えるほうが簡単です。
2.2. 条件付き書式
値が未入力であることに気付きやすくする工夫の例として、条件付き書式で空白の場合はセルに色を付ける、というものがあります。
条件付き書式は、「ホーム」タブの「条件付き書式」⇒「新しいルール」から設定できます。
「指定の値を含むセルだけを書式設定」をクリックし、「セルの値」を「空白」に変更します。
「書式」をクリックし、「塗りつぶし」タブから今回は黄色を選択、「 OK 」をクリックします。
これで「 OK 」をクリックすると、セルに何も値が入っていないときにセルが黄色となるようになります。
値を入れると、黄色が消えます。
ここで設定した条件付き書式は、「ルールの管理」から編集できます。
3. セル範囲からリストを読み込むドロップダウンリスト
3.1. セル範囲を直接指定
さて、先ほどまでリスト自体はドロップダウンリストの設定の中にべた書きしていました。
単純な ○ × であればそれで十分な場合もあるでしょうが、大抵の場合は後からリストの項目を編集したりといったことが発生します。
その場合、べた書きではメンテナンスが難しいので、まずはエクセルシート上のセル範囲からリストを読み込む形にしてみます。
こんな感じでエクセルシート上にリストを作成します。
データの入力規則設定で、「元の値」の右端にあるボタンをクリックし、先ほどのセル範囲をドラッグ & ドロップで選択して、右端のボタンをクリックします。
「 OK 」をクリックします。
これで、リストをセル範囲から作成できました。
セル範囲の値を変更すると、リストも自動的に変更されます。
3.2. セル範囲に別名を付けて指定
セル範囲に別名を付けてそれを指定するようにすれば、さらに管理しやすくなります。
セル範囲に別名を定義するには、セル範囲を選択した状態で「数式」タブの「名前の定義」をクリックします。
「名前」にわかりやすい別名を入力し、「 OK 」をクリックします。
データの入力規則で「元の値」を入力する際に、「数式」タブの「数式で使用」から先ほど定義した別名を選択することで、別名を利用してセル範囲の指定ができます。
ここで定義した別名は、「名前の管理」から編集できます。
4. 項目の追加・削除を容易に行えるドロップダウンリスト
4.1. 列・行を丸ごと指定
先ほどのセル範囲の指定の仕方だと、項目数の変更には対応が難しいです。
では、項目数の変更に対応するにはどうすればよいか。
最も単純な方法は、現在のリストより広めに範囲を取ること、特に列や行を丸ごと指定してしまう方法です。
ただし、このやり方だと、選択肢の下の方に空の選択肢が入ってきてしまいます。
また、行や列の先頭にヘッダがある場合、ヘッダも選択肢に含まれてしまいます。
別にきれいなドロップダウンリストを作る必要が無ければこれでも十分なのですが、空白を確実に除外するためには別の方法を取る必要があります。
4.2. OFFSET 関数
可変のドロップダウンリストを作る方法の 1 つは、 OFFSET 関数を使うことです。
OFFSET 関数はエクセル関数の 1 つで、以下のように定義することで対応するセル範囲を返します。
=OFFSET(基準となるセル,範囲の左上端が基準から何マス下にずれるか,範囲の左上端が基準から何マス右にずれるか,範囲の高さは縦何マスか(省略可),範囲の幅は横何マスか(省略可))
例えば以下のようなリストを考えます。
このリストはセル E1 がヘッダで、 E2 以降が値の範囲となります。
なので、
=OFFSET(E2,0,0,値の個数(上の画像では3),1)
とできれば値の範囲を特定できそうです。
では、値の個数を計算するにはどうすればよいか。
それには、 COUNTA 関数を使えば実現できます。
COUNTA 関数は、指定した範囲の中で空白でないセルの個数を返します。
今回の場合は、COUNTA(E:E)とすれば E 列の中で空白でないセルの個数を返すことができます。
なお、今回は E1 にヘッダがあるので、値の個数を計算するには COUNTA 関数の結果からヘッダ分の 1 を引く必要があります。
よって、指定すべき計算式は以下の通りになります。
$ はセルの指定を絶対指定にするための記号であり、これを入れることでドロップダウンリストを移動したりしたときに勝手に指定範囲も移動してしまうことを防ぎます。
手入力でもよいですが、セル番号を入力した後に F4 キーを押すことでも入れられます。
=OFFSET($E$2,0,0,COUNTA($E:$E)-1,1)
これで、リスト内の値の個数が変わっても対応できるドロップダウンリストができました。
ちなみに、この 4.2 の方法と 4.1 の方法は、どちらも 3.2 の別名定義と併用が可能です。
なお、計算式の結果として空文字を返したりしている場合は、上の計算式だとうまく除外できない場合があります。
この場合は計算式も少し工夫する必要があります。
例えば、 COUNTA 関数の代わりとして COUNTIF 関数を使用し、 COUNTIF($E:$E,"?*") とする ( ワイルドカードを用いた表現で、 1 文字以上を表している ) などで対応できます。
4.3. テーブル
他の実現方法として、「テーブル」を利用する方法があります。
これは Excel の機能の 1 つで、データの分析や管理を容易にするための機能です。
細かいテーブルの仕様については、ここでは割愛します。
テーブルを定義するには、テーブルにしたい範囲を指定して「挿入」タブから「テーブル」を選択します。
すると、以下のようなウィンドウが表示されます。
今回は先頭行が既にヘッダなので、「先頭行をテーブルの見出しとして使用する」にチェックをいれて「 OK 」をクリックします。
ここにチェックを入れない場合は新しいヘッダ行が自動で挿入されます。
すると、以下のようにテーブルが定義されます。
テーブル名は「デザイン」タブの「テーブル名」から確認、変更できます。
今回テーブル名は「テーブル1」、列のヘッダ文字列は「リスト」なので、 INDIRECT 関数 ( 文字列からセル範囲を指定する関数 ) を用いて以下のようにセル範囲を指定します。
=INDIRECT("テーブル1[リスト]")
これで、可変のドロップダウンリストを作成できました。
ちなみに、テーブルの範囲はテーブル右下の小さいツマミをドラッグ & ドロップすることにより手動で変更できます。
なお、別名定義を経由する場合は、 INDIRECT 関数無しでも指定可能です。
直接指定よりもこちらの方がスマートかもしれません。
一応この方法の制約として、リストが横の場合は対応できません。
もっとも、リストを横にして作ると横に長くなって見辛いので、そういう作りにすることはそう多くないと思います。
5. 参照先のリストが変わるドロップダウンリスト
5.1. OFFSET 関数
最後に、少々特殊なパターンとして、他のセルの値によって参照先のリストが変わるパターンも考えます。
4.2 と同じく OFFSET 関数を利用する場合で考えます。
IF 関数を用いて地道に条件分岐してもよいですが、他のセルで指定している値がヘッダ文字列の場合は MATCH 関数も使えます。
MATCH 関数は、検索したい値が最初に出てくるのが先頭から何番目の場所かを返す関数で、以下のように定義します。
=MATCH(検索したい値,検索範囲(1列か1行),検索種別(1なら以上、-1なら以下、0なら完全一致)(省略可))
今回 2 つのリストは横に並んでいるので、 OFFSET 関数の「何マス右にずれるか」の部分と「範囲の高さは縦何マスか」の部分にこの関数を入れ込めば、他のセルで指定したリストを持ってくることができます。
=OFFSET($E$2,0,MATCH($C$2,$E$1:$G$1,0)-1,COUNTA(OFFSET($E:$E,0,MATCH($C$2,$E$1:$G$1,0)-1))-1,1)
これで、使用するリストも可変にできました。
5.2. テーブル
4.3 と同じくテーブルを用いた方法も見てみます。
もちろん IF 関数を用いた地道な分岐もできますが、今回はテーブル名をヘッダ名と同じにしてみます。
すると、文字列を結合する CONCATENATE 関数を使って、以下のように指定することが可能になります。
=INDIRECT(CONCATENATE($C$2,"[",$C$2,"]"))
使用するリストも変更できることが確認できます。
