ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

状況依存型ドロップダウンリストをExcelで使う(2)Indirect関数を使う

 前回はIF関数を使って状況依存型ドロップダウンリストをつくる方法をご紹介しました。
 ドロップダウンリストというのは、誤入力を防ぐためにあらかじめ決められたリストの項目の中からひとつ選択してセルに値を入力するためのツールです。複数ある項目の中からひとつを選択するわけですから、表示される項目数はできるだけ絞り込まれていた方が選びやすくなります。状況依存依存型ドロップダウンリストは、直前に入力したセルの値に応じてリストとして表示する項目の内容を変化させるというものです。
 そのために、表示させるリストのセットを何種類か用意しておいて、扱いやすくするためにそれぞれのリストのセットに対して名前を定義しておくというのがポイントになります。

 前回の説明の中で、直前に入力するセルの値と、リストのセットの名前とが一致していることに、賢明な読者は気づかれたことと思います。


c0136904_23405270.jpg



 上の図では、県名のところ(セルD5)に「秋田県」と入力されていて、それに基づいて名前「秋田県」が選択されて、その中身がリストとして表示されているわけです。どちらも同じ「秋田県」なのですから、IF関数の式の中にわざわざ「秋田県」という文字列を書き込まなくても、セルD5に入力された値をそのままダイレクトに名前として使うことができれば、状況依存型のドロップダウンリストの作成はもっと簡単なものになるはずです。
 ところが、セルE5のところで「データの入力規則」ダイヤログボックスの「元の値」欄に「=D5」と入れると、Excelはそれが「秋田県」という「名前」ではなく単なる値であると解釈してしまい、リストに表示されるのは「秋田県」という文字列だけになってしまいます。これでは意味がありません。

 関数式の引数として、他のセルに入力されている値を参照するやり方が可能であることは以前もご紹介しました。たとえば、VLOOKUP関数の引数である「列番号」を数値で指定する代わりに他のセルに入力されている数値を参照させる方法があります。こうすると関数式を直さなくても参照先のセルの値を変更すれば列番号も一緒に変わることになります。具体的にいうと、参照先のセルにIF関数を使うと、条件に応じて列番号に用いる数値を使い分けることができるようになり、VLOOKUP関数で参照する列番号もその条件に応じて自動的に変更されるようになるわけです。
 
 ここでは、他のセルに入力されている値をそのまま数式の参照範囲として用いたいわけですから、INDIRECT関数を用います。こうすることで、セルD5に入力されている「秋田県」が単なる値ではなく秋田県という「名前」であると、Excelは認識するようになります。
 INDIRECT関数の使い方はとても簡単で、セルE5のところで「データの入力規則」ダイヤログボックスの「元の値」欄に「=INDIRECT(D5)」と入れるだけです。

 せっかくなので、リストにする表についても整理することにします。下図をご参照ください。


c0136904_23412770.jpg



 ご覧の通り、B列で県名を列挙して、各県にある支店名をその右横に記入して、その県名でセル範囲に名前を定義しています。また、今回はドロップダウンリストを設けるデータベースとは別のワークシートにこの表を設けています。このように、シートを分けておいた方が後のメンテナンスが楽になるだけでなく、データベースの集計やAccess等との連携もやりやすくなります。
 そのうえで、データの入力規則」ダイヤログボックスの「元の値」欄への入力は下図のように行います。


c0136904_23415224.jpg



c0136904_23421753.jpg


 INDIRECT関数を入力するときにセルの番地を入力するわけですが、キーボードから直接入力することで相対参照形式となります。マウスで参照先のセルをクリックしてしまうと絶対参照形式となってしまうので、後でドロップダウンリストをコピー&ペーストするときに支障が生じることになるので注意してください。


(ドロップダウンリストをマウスを使わずに展開するには)
 誤入力を防ぐという意味で有効なドロップダウンリストですが、リストを展開するには、セルがアクティブになったときに右側に表示される「▼」をマウスでクリックしなければなりません。データベースを入力する際に、キーボードからいったん手を離しマウスを操作するというのは作業がいったん中断することになるので、あまりお勧めできません。そこで、キーボードの操作でドロップダウンリストを展開する方法をご紹介しておきます。

「Alt」キー+「↓」

 当該セルがアクティブになっていればAltキー+↓キーでドロップダウンリストが展開されます。これだとキーボードから手を離す必要がないので、入力をリズミカルに行うことができると思います。


(ドロップダウンリストとIF関数、INDIRECT関数のまとめ)
 前回IF関数を使ってドロップダウンリストを使うやり方をご紹介して際には、ドロップダウンリストは支店名を入力するせるにしか設定していませんでした。今回のINDIRECT関数を使うやり方では県名を入力するセルと支店名を入力するセルの両方にドロップダウンリストを設けています。
 この違いは、ドロップダウンリストの特徴と2つの関数の特徴によるものです。ドロップダウンリストを設けると、そのリストに載っていないデータの入力は一切できなくなります。元々誤入力を防ぐために選択できる項目をあらかじめリストにしておくというのがドロップダウンリストなので当然と言えば当然なのですが、INDIRECT関数を使うには県名に対応した支店名グループの名前が漏れなく定義されていなければなりません。別ないい方をすれば、県名と支店グループとが1対1の対応になっています。
 一方、IF関数を使うと支店名グループに対応していない県名を入力すると支店名セルにはは一律に「本社」というデータが入力対象となります(別ないい方をすれば、多対1の対応となります)が、その代わり県名セルにドロップダウンリストを設定することはできなくなります。また、IF関数を入れ子にする場合(これをネストといいます)、7回までが限界なので、7回以上の条件分岐には使用できませんし、関数式が長く複雑なものになるという懸念もあります。

 以下の記述は、このような考えかたもできるという事例としてご紹介するものです。
 今回の事例では東海北陸地方の各県には支店が配置されているものとしてドロップダウンリストを作成しています。
 応用編として、東海北陸地方以外の都府県(たとえば近畿地方)については本社が管轄するという場合はどのようにしたらよいでしょうか。


c0136904_23425465.jpg




c0136904_23432137.jpg



 上の図では、本社が管轄する県名も加えて名前(ここでは「県名2」としています)を定義します。また、「本社」という名前も定義しています(緑の枠の部分)。さらに、支店グループが存在するところ(青い枠の範囲)には「支店名」という名前を定義しています。
 次にドロップダウンリストを作成するに当たって、B列に作業用セルを設け、セルB2には以下の数式を入力しています。

=IF(ISERROR(VLOOKUP(A2,支店網,2,FALSE))=TRUE,1,0) -数式①

 一方A列の「データの入力規則」ダイヤログボックスの「元の値」には次の式が入力されています。

=県名2 -数式②

 さらに、C列の「データの入力規則」ダイヤログボックスの「元の値」には次の式が入力されています。

=IF(B2=1,本社,INDIRECT(A2)) -数式③


 数式①では、A列に入力されたデータが、セルの範囲「支店網」になければエラーになりるということを利用しています。ISERROR関数は式の結果がエラーであればTRUEを返す、というものです。通常は、このようにIF関数を組み合わせて使います。数式①の意味は、A列に入力されたデータ(県名)が「支店網」にない場合、「1」を表示し、そうでない場合は「0」を表示するというものです。

 次に、C列の「データの入力規則」ダイヤログボックスの「元の値」である数式③は、B列の値が1(すなわち本社管轄の県)ならば、名前「本社」を適用し、そうでない場合(すなわち管轄する支店が存在する場合)は、INDIRECT関数を使って県名=支店グループの名前という参照を行っています。

 このやり方だと、1対1対応と多対1対応を組み合わせて使うことができます。そうではなくて、INDIRECT関数だけ処理しようとすると、京都府、奈良県、大阪府、和歌山県、兵庫県の右横に本社と入力し、それぞれ「本社」「本社2」「本社3」「本社4」「本社5」という名前を定義する必要があります。
 どちらがいいとは一概にはいえないので、こういうやり方もあるのだという考えかたを理解していただいた方がよいと思います。

(サンプルファイルの公開)
 例によって、今回ご紹介して内容のExcelファイルを公開していますので、必要な方は下記のリンクをクリックしてダウンロードしてください。

http://bit.ly/12ptjwj


(お詫び)
 サンプルファイルで、県名を「三重県」とした場合、「支店名」が正しく表示されないという不具合がありました。これは三重県という名前を定義すべきところを他の名前で定義していたために発生したものです。深くお詫び申し上げます。サンプルファイルの方は訂正してありますが、既にダウンロードされている方は、名前の管理から「三重支店」ではなく「三重県」と訂正していただきますよう、お願い申し上げます。
by t_am | 2012-12-14 23:45 | Excel のあの手この手