EXCELハック!プルダウンで選択した結果ごとにデータを入れ替えるためのINDIRECT関数をお勉強

エクセルいじいじするのって楽しいですよね。

今回は、プルダウンで名前を選択して、選択された名前ごとにシートの内容が切り替わる仕組みをEXCELで作る方法を書いていこうと思います。

営業マンの進捗管理とか、そういうのに使えるかもしれませんよ!

 

今回の作業のゴール

あなたは会社の事務職員。

ある日、営業部から社員の進捗管理をエクセルでしたい!と頼み込まれました。

できたら、1シートでプルダウンで社員を選択すると、社員ごとの数値が見られるようにしたい、とのこと。

↑のキャプチャでいうと、「名前を選択」の箇所でプルダウンで営業社員の名前を選択できるようにして、その名前と連動して各種数値が変更されるような仕組みを作ってしまうことが今回の作業のゴールです。

山田さんは目標まで残り5件、これを田中さんに切り替えると、目標まで残り3件、というふうに表示が変わるようにしたい、ということです。

 

INDIRECT関数でシート名を参照することで解決!

 

では具体的にどうするか、の解説です。

流れとしては

STEP1:プルダウンで名前を選択できるようにする

STEP2:各シートに元データを作成

STEP3:INDIRECTを使って参照する

の3ステップで行こうと思います。

 

STEP1:プルダウンで名前を選択できるようにする

まずはプルダウンで名前を選択できるようにします。

データ>データの入力規制 を選択します。

このようなウィンドウが出てくるので、

入力値の種類 を リスト

元の値 に ○○,○○,○○ ※○○は人の名前

というように入力してください。

元の値の箇所にカンマで区切った値がプルダウンリストで表示される名前になります。

今回は3名分入力しましたが、ここにはいくらでも数を追加しても大丈夫です。

また、カンマは半角で入力するよう気をつけて下さい。

こんなかんじになるはずです。

 

STEP2:各シートに元データを作成

山田さん、中村さん、田中さんごとに別々にシートを作って、そこに参照する数値を入力します。

 

STEP3:INDIRECTを使って参照する

では元のシートに戻って、各名前ごとのシートと紐付ける作業に入ります。

↑のキャプチャでいうB4セルには、「山田」シートに入力されている山田さんの入社日が書かれているセルを参照したいです。

この”「山田」シートに入力されている山田さんの入社日が書かれているセル”というのは、”山田!B4”というふうに表されます。

まず”○○!”というのは○○というシートを参照しますよ、という意味を表します。

なので”山田!B4”というのは、「山田」シートのB4セル、という意味になります。

 

しかし、普通に「=A2&”!B4″」と入力しても、「山田!B4」という答えが帰ってきてしまいます。

&は文字列を結合するのに使うため、&を使ってつないだものは文字列としてエクセルには認識されてしまうんです。

そこで必要なのがINDIRECT関数です。

▼INDIRECT関数の説明

指定される文字列への参照を返します。セル参照はすぐに計算され、結果としてセルの内容が表示されます。INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセル参照を変更することができます。

Office サポート

INDIRECT関数を使うことで、文字列の箇所を参照してくれるようになります。

「山田!B4」という単なる文字列ではなく「山田!B4」というセルに入っている値を返してくれるようになります。

ですので、最終的なアウトプットとしては

「=INDIRECT(A2&”!B4″)」と入力すれば、山田さんの入社日がきちんと参照されるようになります。

あとは他の欄も同じようにINDIRECTで参照すれば完了です!

 

EXCELマスターへまた一歩近づくことができました!

おわり