仕事効率3倍も夢じゃない！！INDIRECT関数で作る超便利な可変リストの作り方

《実務で使えるスキル講座　第4弾》

日々のExcel業務をもっと効率的に使いやすくしたいなら、

【テーブルとして書式設定】と【入力規則】をマスターしましょう。

前回の第3弾から読みたい場合は、下のリンクをクリックして下さい

サクッと簡単でも効果大！！　フッター・タイトル設定・ＰＤＦ化を極めろ！！

簡単に設定できる機能ですが、効率はすごくあがるとっても役立つ機能です。

以前にもこの2つの機能に触れましたが、今回は私の経験からこうするとめっちゃ楽になるポイントと組み合わせ方や将来的な使い方までを考えたExcel超実践活用術を紹介しちゃいましょう。

※この解説は、Excel2016を使用しています。

シンプルな一覧表を便利で楽しい表にするにはどうすりゃいいの？

君近壺音

坂田～！！！ちょっと来なさい！！

何なのこの使っかいにくい表は、こんなのチンパンジーの方がまともなもの用意出来るわ！！

すぐに直して再提出しなさい！！18時までには作りなさいよ。

私、残業なんてしませんからね。分かった！！！？

隣の坂田さん

はひぃぃぃ。すっスミマセン！！

大至急用意して使いやすいものをお持ちしますので、時間を下さい。

君近壺音

仕方ないわね。じゃあ特別にあと1時間5分だけ待ってあげるわ！！

それまでに用意出来なかったら、分かってわね。

あなたの席は明日から空気椅子に変更するからそのつもりで！！！

隣の坂田さん

わっかりました！！！

では、至急用意して提出させていただきます！！！

坂田君はどうやら今回、横暴な上司から無茶ぶりな命令を受けてしまったようです。

どうにも今回用意した表がお気に召さないようです。

今回用意したブックは、「マスタシート」と「DataBase」シートの2ページで作られているようです。

《マスタシート》

※マスタシートには、事前に「テーブルとして書式設定」を各表に設定しています。

A2～A5のセル範囲に『商品カテゴリー』という名前をつけています。

《DataBaseシート》

確かにシンプルな表ですが、そんなには悪くはありません。

使い方は、「マスタシート」のデータを基にして「DataBaseシート」に各情報を入力するスタイルのようです。

ただ、このままでは、入力するところが多くてちょっと使いづらいですよね。

では、今回はこの表を「楽しく便利な表」に変えてみましょう！！

イェイ！！！

初めから表を用意するのは大変なので、練習用のファイルを用意しました。

まずは、『テーブルとして書式設定と入力規則.xlsx』をダウンロードしましょう。

準備が出来たところで、今回の目的を整理してみましょう。

1. 『マスタシート』の各データを基に「商品カテゴリー」・「商品名」をリストから選べるようにします。
2. 「商品金額」をマスタシートから自動的に反映出来るようにします。

さて、困り果てた坂田くんは、このピンチを無事に乗り越えられるのでしょうか？？

向かいの長谷川さん

どうしたんですか！？

坂田さんが奥さん以外に怒鳴られるなんて珍しいですね。

何やらかしたんです？また、くだらないオノロケ話でもしてたんですか？

隣の坂田さん

イヤイヤイヤ。

奥さんにそんなに怒鳴られてないからね。

それにどうして長谷川さん。うちの事情に詳しい感じになってるの？

って。今日はそんなこと話してる場合じゃないんだよ。

18時までにちゃんとした資料を提出できないと、君近(きみぢか)部長に俺の席無いぞって脅かされてるんだ。

向かいの長谷川さん

え～！！大変じゃないですか。

君近部長って、最近うちの部に移動になった君近壷音(きみぢか・つぼね)部長ですよね。

あの切れ方は、日本刀も真っ青だって評判ですよ。

今までお世話になりました。元気で過ごして下さいね。

隣の坂田さん

ちょっと待っって！！

なんか。首切られるの前提に話進んでない？

「日本刀も真っ青なだけに首切るの上手いよね。」とかって思ってない？

いや、空気椅子にされちゃうんだよ。デスクワークなのに下半身鍛えられちゃうよ！！

ツルトロ

それは、それで良いんじゃないかな。

ところで、無駄話してる間に残りは45分を切ってるけど大丈夫なの？

隣の坂田さん

うわぁ！！ツルトロさん？？

ビックリした。どうしたんですか？

ツルトロ

いや。あれだけ大声で騒いでたら嫌でも見に来るよ。

そういう事なら、今回は使いやすい表の作り方を特別にレクチャーしよう。

時間も無いことだし。早速始めようか。

隣の坂田さん

本当ですか？？ありがとうございます。

ツルトロさんがサポートしてくれるんだったら、なんとか間に合いそうです！！よろしくお願いします。

向かいの長谷川さん

え～。手伝ちゃうんですか～。

手伝わない方が面白いと思いますよ～。明日から

隣の坂田さん

こら～！！！人の不幸を面白がるな～！！

うぉ～！！！時間がない～！！！

ツルトロさんお願いします。使いやすい表の作り方の教えて下さい。

ツルトロ

ははは。

じゃあ、時間もないことだし一緒に使いやすい表を早速作ってみよう！！

《商品カテゴリー》をリストから選択出来るようにしよう！！

まずは、商品カテゴリーをリストから選択出来るように改良するようです。

どんな方法があるか一緒に考えてみましょう。

ツルトロ

まずは、「商品カテゴリー」の列を使いやすくしてみよう。

ここまで出来てるんだから下準備は十分だね。

問題点はこのままだと、「カテゴリー」を一つ一つ入力しなくちゃいけないね。

これだと不便だからどうにかしたいけど、どうしたら良いと思う？

隣の坂田さん

なるほど、言われてみれば、確かに使いにくいですね。

だったら、「リスト」から選択するようにすれば、入力ミスもなくなるし入力する手間も減りますかね。

ツルトロ

うん。良いところに気づいたね。

その通り、入力規則の機能を使ってリストを設定すれば便利そうだね。

よし、早速作業に移ろうか。

まずは、『H2～H101』のセルを選択します。

次に「データタブ」→「データの入力規則」→「データの入力規則(V)…」をクリックします。

データの入力規則ダイアログが表示されるので、「入力の値の種類」から「リスト」を選びます。

「元の値(S)：」のテキストボックスをクリックして「セル範囲の名前」を設定しましょう。

今回は、「マスタシート」の「商品カテゴリー」という名前の範囲を指定します。

ここでポイントです。直接入力はせずに【F3キー】を押してみましょう。

F3のキーは、「名前をつけたセル範囲の一覧表」を表示するショートカットキーです。

一覧表が表示されたら、「商品カテゴリー」を選択して「OKボタン」を押します。

元の値(S)：に「=商品カテゴリー」と表示されたら、「OK」ボタンを押しましょう。

ツルトロ

ここまで出来れたら、ベースになるリストは完成。

次に「商品カテゴリー」を元に「商品名のリスト」が選択出来るリストを設定しよう。

どうすれば選択されたカテゴリーによって商品の内容が変わるリストを設定出来ると思う？

こんな感じのイメージだね。

隣の坂田さん

う～ん。

全然わかりません！！

ツルトロ

返事、早！！

ちょい待ち。ちょっとは考えようよ！！！

向かいの長谷川さん

そうですよ。

だから、チンパンジーって言われるんですよ。

そうね～。Excelが勝手に判断してリストの内容を変えてくれると良いんだけど。

隣の坂田さん

ううっ！！

チンパンジーとは言われてないよ。

ツルトロ

まあ、坂田くんがチンパンジーがオラウータンかは微妙なところだけど

確かにExcelが勝手に判断してくれるというのはいい発想だね。

じゃあ、ここで発想を変えて実際にせていしてみよう。

「商品のカテゴリー」を選択したら、「商品名」が自動的に切り変わるリストの設定をしよう。

ツルトロ

まずは、下準備として家電製品の項目範囲に名前をつけてみよう。

まずは、マスタシートに切り替えます。

家電製品の範囲である『B2～C38』を範囲選択して、「名前ボックス」に「家電製品」と入力しましょう。

名前は、名前ボックスに直接入力すれば大丈夫です。

入力したら、「必ずエンターキー」を押しましょう。

名前が入力されているか確認する場合は、「名前ボックスの右側の▼ボタン」を押すことで確認出来ます。

※範囲に名前を付けるときは、必ず商品カテゴリーの表にある名前をつけましょう。

今回は、「家電製品・食品・衣料品・雑貨・日用品」の4種類ですね。

残りの範囲にもそれぞれ名前を付けましょう。

名前を付けた範囲の確認は、「数式タブの名前の管理」をクリックすることで確認できます。

今回、表示された名前を見てみると気になることがあります。

「テーブルから始まるもの」と、「名前だけのもの」の2種類がありますよね。アイコンも違います。

2種類ある理由は、テーブルに付けた名前とセル範囲に付けた名前の違いなので見分けられるようにしておきましょう。

今回用意したファイルは、前もって各テーブルに名前を付けておきました。

ツルトロ

では、ここで問題！！

どうして、今回テーブルとセル範囲にそれぞれ名前を付けたんでしょうか？

はい。チンパ君分かるかな？

隣の坂田さん

え！！チンパ君って僕のことですか？？

勘弁してくださいよ。へこみますから。

でも、名前を二重で付けたってことは、何か使い分ける必要があるとかじゃないですか？

ツルトロ

ごめん。ごめん。でも今回は、いい回答だね。

そう。実はテーブルに名前を付けるとピボットテーブルを作った時にすごく便利なんだ。

ただ、関数や今回のようなリストを設定したいときは、エラーが表示されたり、F3のキーで呼び出すことが出来ない。

その対策と実際に使っていくことを考えて二重に名前を設定しているんだよ。

でも、今回の場合は更にセル範囲に名前をつける必要があるんだ。

理由と一緒にリストの設定をしてみよう

更にセル範囲に名前をつけましょう。

今回は、B2～B38の範囲に「家電製品製品名」と名前を付けます。

同じ要領で「食品製品名」「衣料品製品名」「雑貨・日用品製品名」と名前をつけます。

ここで一度範囲の名前を整理してみましょう。

ちょっと見づらいですが、こんな感じでセル範囲に名前を付ければ、準備完了です。

いよいよ仕上げです。

「DataBase」シートにテーブルとして書式設定をしましょう。

テーブルとしての書式設定については、面倒だったら、勉強だ！！仕事にめちゃ役立つ「表」の作り方。を参考にしてみて下さい。

デザインはどれでも大丈夫なので、好きなデザインを選びましょう。

テーブルの範囲は、【A1セル】を選んでおけば、自動的に判断してくれますのでそのまま【OK】ボタンを押しましょう。

表を整えたら、商品名である【I2のセル】にカテゴリーによって選択肢が変わるリストを設定しましょう。

リストの設定については、ちょっとしたことで大違い！！3Stepで設定できる「種類によって変化するリスト」の作り方も参考にしてみて下さい。

商品名のリストの「元の値(S)：」に「=indirect(h2&”商品名”)」と入力して【OKボタン】を押しましょう。

※リストに直接入力するときは、【F2キー】を押すことで自由に入力が出来るので覚えておくと便利です。

また、入力が心配な場合は、上の式をコピペしても大丈夫ですよ。

今回のINDIRECT関数の式の意味を抑えておきましょう。

INDIRECT(イン・ダイレクト)関数は、INDIRECT(範囲の名前を参照する)という使い方をします。

つまり、INDIRECT(“家電製品製品名”)とすると、『家電製品製品名という名前の範囲』を選択してね。という意味になります。

今回は、INDIRECT(H2&”製品名”)という設定をしました。

この関数の式を日本語にしてみましょう。

INDIRECT(H2のセルに入力されている「家電製品」&「製品名」)　=　INDIRECT(「家電製品製品名」)という意味になります。

この式ってさっき見てますよね。

つまり、INDIRECT(「家電製品製品名」)　は、　INDIRECT(“家電製品製品名”)と同じなので、

「家電製品製品名」っていう範囲を選択してね。というのを関数の式にすると

=INDIRECT(H2&”製品名”)となります。

「＆」記号は、「左側の文字と右側の文字」をつなげて一文字として扱ってねという記号です。

つまり、INDIRECT関数を使って『H列のカテゴリー名+製品名』という名前の範囲を選択してね。という設定をしています。

この設定をオートフィルで【I101セル】までコピーしましょう。

試しに【I2のセル】を選ぶと「冷蔵庫～コーヒーメーカー」などが選べますね。

次に【I3のセル】をクリックしてみましょう。

今度は、「チョコレート～食玩」などが選べますね。

さて、これでなんとなく分かってもらえたでしょうか。

【カテゴリーによって、選択出来る内容が変わる楽しくて面白いリスト】の設定は終わりです。

ここまで出来たら実際に色々いじってみて下さい。

『百聞は一見に如かず』です。触って実感してもらった方が面白さや仕組みが感覚的に伝わってきますよ。

※このリストの設定のコツの一つは、INDIRECT(H2&”製品名”)の『H2セル』は必ず相対参照にして下さい。

やり方によっては、絶対参照に自動的にされてしまうので、失敗しないコツを掴んでおきましょう。

参照に関しては、参照設定をマスターすればExcelの価値は上がる。3つの参照方法の重要性と後々のことを考えた結果を見てみて下さいね。

商品金額も自動的に表示されるように設定してみよう。

ツルトロ

ここまでくれば、もう一息。最後に金額の自動表示を設定しよう。

坂田君。どうすれば、金額の自動表示ができるか分かるかな？

隣の坂田さん

今回は、僕にもだいたい想像が出来ました。

まず、VLOOKUP関数を使えば検索は出来ると思います。

次に検索する範囲は、カテゴリー名で判断して更に商品名で探せば、金額が自動で表示できそうに思います。

ツルトロ

今回は、いい感じの答えだね。

じゃあ、言葉だけじゃ伝わりづらいから実際に設定してみよう。

【J2のセル】に「=IFERROR(VLOOKUP([@商品名],INDIRECT([@商品カテゴリー]),2,0),””)」と入力しましょう。

エンターキーを押せば、【J101のセル】まで一気に設定してくれるので、これで完成です。

今回の関数は、3つの関数をネストしているので、分かりやすいように分解してみましょう。

「=IFERROR(VLOOKUP([@商品名],INDIRECT([@商品カテゴリー]),2,0),””)」の式を日本語にして分解

=もし、答え(VLOOKUP関数の結果)がエラーだったら,空白を表示してね。この式のVLOOKUP関数部分を更に分解しましょう

VLOOKUP([@商品名],INDIRECT([@商品カテゴリー]),2,0)

商品名を基に,商品カテゴリーの名前のセル範囲から検索してね。,表の中の2列目の項目で,商品名が完全に一致する値を検索するよ。

という意味です。

今回は、関数のネストが多くて複雑ですが、エラーの表示の対策や金額を条件によってきちんと検索する柔軟性を持たせた設定になっているので、

それぞれの関数の使い方を抑えて理解してみましょう。

どうしても、内容がわからない場合は、今回の式をコピペして設定してみて下さいね。

VLOOKUP関数・IFERROR関数の使い方は

仕事が早く終る！！超おすすめエクセル関数10選+3選　まとめも参考にしてみて下さい。

INDIRECT関数の使い方は、

ちょっとしたことで大違い！！　3stepで設定できる「種類によって変化するリスト」の作り方も参考にしてみて下さい。

ツルトロ

これで無事完成したね。

じゃあ、今回はこのファイルを君近さんに渡してくれば、大丈夫だと思うよ。

残り時間5分。ギリギリだったね。

隣の坂田さん

ありがとうございます。

これだけ出来るようになれば、君近さんにも納得してもらえます。

本当に助かりました。

向かいの長谷川さん

でも、一つ気になるんですけど

これって項目や表に追加したい時ってどうなるんですか？

今は便利かもしれないけど、この先増やしたり、減らしたりのすることってもちろんありますよね。

ツルトロ

さすが長谷川さん。

良い質問をするね。それじゃあ最後のまとめとして今後の話をしていこう。

先のことまで考えよう！！実際の使い方を必ず確認するのが成功の鍵。

今回作ったファイルはこれからの使い方も考えて作っています。

Excelで何か作るときは、作ったあとのこともきちんと想定しておくことが大切です。

使う人が使いやすいのか？分かりやすくなっているか？簡単に扱えるか？

ここまで考えられれば、自然と良いものが出来上がります。

パソコンで何か作るときは、必ず意識しましょう。

では実際に新しい情報を入力してみましょう。

102行目に名前～携帯の電話番号を入力しました。

テーブルの良いところは、最終行や最終列に文字を入力すだけで自動的にテーブルとして設定してくれることです。

わざわざ表の範囲をもう一度設定しなくちゃ。なんてことをしなくても大丈夫です。

使う人は、便利ですよね。

次は、「商品カテゴリー」・「商品名」はどうなるか確認してみましょう。

商品カテゴリーの列をクリックするときちんとリストが表示されて選べますよね。

もちろん。リストを手作業で設定し直したわけじゃないんですよ。これも自動です。

商品カテゴリーを設定したら、商品名もチェックしましょう。

これも、リストの選択が自動で出来るようになっています。

では、金額はどうでしょうか？

金額は、商品名を選択したら自動で表示されます。

ほら便利で楽しいでしょ。

更に使いやすくするには、シートの保護や《名前～携帯》の列に入力規則でIMEの自動切り替えの機能などを設定してあげると、より使いやすいですね。

IMEの設定方法は

実務でExcelを使いこなすための4つの大切なこと　本当に必要なスキルが知りたいならコレを読め！！を参考にしてみて下さい。

隣の坂田さん

よし。使い方もメリットも把握できた。

これで大丈夫です。いろいろとありがとうございました。

君近部長には、これで怒られなくて済みそうです。

さっそく、行ってきます。

ツルトロ

はいよ～。いってらっしゃ～い。

向かいの長谷川さん

坂田さん。大丈夫でしょうか？

君近部長は、納得してくれますかね。

そういえば、ツルトロさんって君近部長とお知り合いなんですか？

ツルトロ

うん。昔、一緒に仕事してたことがあるんだけど

なかなかの曲者だから、一筋縄ではいかないんだろうけど、大丈夫だとおも思うよ。

・

・

・

・

・

(タイムリミットまであと5分！！)

君近壺音

坂田～！！

何だこのファイルは！！

隣の坂田さん

ひぃ～～～～。すいません。

何か問題があったでしょうか？

君近壺音

おまえ。ツルトロさんにお世話になったろ！！

出来はなかなか良かったが、昔私が教えてもらった方法と発想がそっくりだ！！

正直に言ってみろ！！！

隣の坂田さん

はい！！申し訳ありません。

その通りです。次回からは自分で出来るように努力しますので勘弁していただけないでしょうか。

・

・

・

・

君近壺音

ふん！！まあいい。

言い訳せずに素直にツルトロさんに手伝ってもらったことを認めたことは褒めてやる。

ただし、今後はあの人に手伝ってもらわなくても、自分でしっかり設定できるように努力はするように！！

隣の坂田さん

はい！！

分かりました！！ご期待にお応えできるように一層努力します！！

君近壺音

そうだ。言い忘れていた。

坂田！！今度からお前の名前は、《チンパ》だ！！

分かったな。チンパ！！

隣の坂田さん

ひぇぇぇ～～～～。

勘弁して下さいよ。呼び捨てでご勘弁を～～～～。

最後まで読んでいただきありがとうございます。

テーブルとして書式設定と入力規則-完成版-ダウンロードして、上手くできた人も出来なかった人も触ってみてくっださいね。

もっと分かりやすく教えろ～。とか、もっとこんなことが知りたいということがあったらコメント下さい。

「いいね。」も押してもらえると嬉しいです。

それでは、また会いましょう！！グッバーイ！！！

前回の記事を読む　　　次回の記事を読む