root 
Excelシートでも、アンケート・フォームでも、ドロップダウン・リストをみかけることは、よくあります。
最近は、Webアンケートで性別を質問する時にも、「男性/女性/ノン・バイナリー/答えたくない」など、多くの選択肢が用意されていて、多様性の広がりを感じます。
(※以前にビックリしたWebアンケート。システムのバグかもしれませんが、年齢の選択肢で、ドロップダウン・リストから、マイナス数値を選択することが可能でした。そこまでの多様性は要るのかしら???)
ところで、B to C 企業でも、B to B 企業でも、一般的には、数多くの顧客にささえられて、ビジネスは継続しています。そのため、顧客管理は重要な課題の1つです。
多くの企業が、顧客管理の入口として、商品(サービス)の「申込書」や「エントリーシート」のようなものを用意していることでしょう。
あなたの会社では、どのようなテンプレートをつかっていますか?
今回のお題は、「Excelで申込書を作るなら、ドロップダウン・リストを徹底的に活用しましょう」というお話です。
Excelの、あまりにも自由すぎる入力形式を制限するためにドロップダウン・リストは効果的
たとえば「氏名(フリガナ)、性別、年齢、住所、電話番号」を収集する製品(サービス)申込書テンプレートを、Excelで作るとします。
セルに各項目見出しを列挙しただけのシートですませてしまっても、「申込書」としての役割ははたせますが、顧客台帳へ転記することを考えて、せるワザなら、このように作ります。
自由に記入できるセルは、氏名(姓・名)と住所(番地/部屋番号)のみ。それ以外に、姓名のフリガナはPHONETIC関数で漢字姓名から自動取得し、性別、年齢などはドロップダウン・リストから取得するように設計します。
このテンプレートでの書き見本が、こちらです。
Microsoft Excelの、ソフトウエアとしての良い点は、英語・日本語・数字、全角・半角など、どのような書式・形式で入力しても保存できる点です。
しかし、この自由さがアダとなって、他のデータベースへの取り込みなどで「使いものにならない」ことがあります。 企業にとって宝となる顧客情報を無駄にしないため、情報収集前に、目的と使い方を整理し、収集形式を、できる限り厳密に決めておく必要があるのです。
住所(都道府県+市区町村)情報は、ドロップダウン・リストにしておけば、転用ラクラク
性別や年齢をドロップダウン・リストにしておけば、記入者にとっても親切ですが、それ以外にぜひリスト化しておいた方がよいのが、住所(都道府県名/市区町村名)です。
地名に使う漢字にも、常用漢字以外をあてるところは多数あり、自由記入とすると、表記ゆれが生じます。後から申込書を集計して顧客台帳(住所録)を作る場合に、住所データをクリーニングしなおさないといけなくなる可能性がでます。
そこで、ぜひ参照しておいた方がよいExcel資料が、Webに公開されています。
総務省公式サイトの「都道府県コード及び市区町村コード」(Excelファイル)です。
建物名や部屋番号までを含む住所は「個人情報」なので、慎重に取り扱う必要があるものの、”都道府県名”、”市区町村”データであれば、件数を数えるだけで、「販促重点エリア検討」のようなマーケティング情報に応用できますよ。
表記ゆれがない”市区町村”データであれば、VLOOKUP関数を使って、後から郵便番号を付加することもできますし、GIS(地理情報)システムへの取り込みも容易です。 ぜひ総務省公開のExcelシートを「マスター表」とするドロップダウン・リストを作って、住所データ(都道府県名/市区町村名)自由記入はさけるようにしてください。
都道府県名と市区町村名を両方ドロップダウン化するカギはINDIRECT関数
しかし、都道府県名の選択肢は47件であるのに対して、市区町村名の選択肢は2,000件弱あり、Excel仕様上、1つのドロップダウン・リストにはできません(仮にできたとしても、2000件の選択肢から「わが町」を選ぶ作業はしたくないですよね)。
そこで、まずはじめに「都道府県名」を選択すると、その都道府県内の「市区町村名」だけがドロップダウン・リストで選択できるようにしておけばよいのです。
そのカギが、INDRECT関数です。
具体的に作り方をみてみましょう。
まず、標準的なドロップダウン・リストは、［データ］→［入力規則］であらわれるダイアログボックス［設定］タブで、［入力値の種類(A)］を「リスト」とし、［元の値(S)］に選択肢をカンマ付きで直接入力します(もしくは、別セルに作成された選択肢リストの範囲を指定します)。
都道府県名を同じ要領でリスト化してもよいのですが、［入力規則］操作に入る前に、都道府県✖️市区町村名のデータテーブルを整理しておきましょう。
総務省「都道府県コード及び市区町村コード」(上図)を、47列に都道府県名✖️市区町村名が並んだデータテーブル(下図)へ。
※今回はご参考までに政令指定都市のみを抽出しています。
セルA1:AU1まで(もしくは、北海道から沖縄県までの都道府県名が入った1行47列のセル範囲)を「都道府県名」と名付けてください。
その上で、［数式］→［選択範囲から作成］で表示されるダイアログボックス［名前の作成］で、［名前の作成先:］→［上端］のみにチェックを入れ、OKしてください。
これで、都道府県名を検索キーとする、市区町村検索テーブルが出来上がります。
最後に、都道府県名セル、市区町村名セルの入力規則を指定します。
都道府県名セルは、先に作成したデータテーブルの名前「都道府県」をそのまま参照します。
市区町村名セルでは、都道府県名セルに指定された都道府県名でデータテーブルの参照領域を切りかえて、該当する行政区の市区町村名だけを表示させます。
INDIRECT関数が、都道府県名を元に、参照領域をきりかえる役割をします。
このように［データの入力規則］のダイアログボックスには、参照する［元の値(S)］に［セルの名前］や［関数］も入力できることをおぼえておくと、ほかにも、条件切替ドロップダウン・リストが簡単に作成できますよ。
まとめ;ドロップダウン・リスト活用で、キレイなリストにまとめられるエントリーシートを作りましょう
自由記入だけの申込書を、あとから集計するのは大変ですよね。
お客さんにもいろいろな方がいるので、字をまちがえたり、省略したり、受け手が「よくわからない」書き方をされることがよくあります。
実際にデータを取ったあとで「さあ、困った〜」となる前に、申込書をExcelで作成する時点で、「自由記入」できるセルをできるだけへらしておきましょう。
セルの名前づけやINDIRECT関数など、条件によって参照領域をきりかえる方法を使うと、かなり複雑なドロップダウン・リストも、簡単に作ることができますよ!
