EXCEL備忘録「VLOOKUP関数」「INDIRECT関数」

私の会社では、ある得意先が紹介してくれて直接取引が始まった会社の売上に対して、その紹介元の得意先に手数料を払うことがあります。
売上総金額の何%とかではなく、1商品に対し、また取引先によっても手数料の単価が違います。

以前の事務員さんが作ってくれていた明細書は、取引先の売上から「取引先名」「商品名」「数量」「単価」を拾って入力し、合計額を出すというもの。

しばらくはこれを使っていたのですが、どうも効率が悪く、この明細書を作るのもとても面倒くさく感じていました。

そこで…
100511.gif

1.「送り先名」をプルダウンから選択する。
2.1で選択した送り先に出荷している商品が絞り込まれてプルダウンに表示され、そこから「品名」を選ぶ。
3.商品名を選ぶと自動的に「単価」が入る。
4.自動的に合計を計算。

と、なるべく簡単にすませたかったのです。

てなわけで、自分用の備忘録を兼ねて記事にしておきます。
(書いておかないと次に使いたくなったときにすぐに忘れちゃうんだよね…(^^ゞ)

1と2をするには「リストの作成」と「INDIRECT関数」を使います。
3には「VLOOKUP関数」を使います。

以下手順です。

10051101.gifまず別シートに送り先名と送り先毎の商品一覧を入力します。
1.「送り先名」(A社、B社、C社……)の行(セルA1~E1)を選択し、「数式」タブ-「名前の定義」→「名前欄」にここでは「会社名」と名前を入れました。

10051102.gif同様に送り先別の品名にも名前をつけます。
先ほどと同様に品名の部分だけ範囲選択し、名前をつける方法もありますが、ここでは一括して名前を自動的につけてもらうことにします。
それぞれの名前を「A社」「B社」……とつけたいので、一覧全体(セルA1~E11)を範囲選択します。
「数式」タブ-「選択範囲から作成」-「上端列」にチェック(ここでは最上列の「社名」を名前としてつけたかったので)-「OK」

10051103.gif「名前の管理」で確認してみると「A社」「B社」……「会社名」と名前がついています。

10051104-0.gif「送り先」(列B)にドロップダウンリストを表示できるようにします。
「列B」全体、または入力したい範囲を範囲選択し、「データ」タブ-「データの入力規則」
「設定」タブ-「入力値の種類」の▼ボタンをクリックし「リスト」を選びます。
「元の値」のところに「=会社名」(←先ほどつけた名前)を入力します。
半角で「=」を入れるのも忘れないでください。

10051104.gifこれでドロップダウンリストから「A社」~「E社」が選択できるようになりました。

10051105-0.gif次に、先ほど選んだ「送り先」(列B)によって「品名」(列C)のドロップダウンリストに表示されるものが限定されるようにします。
「列C」の入力したい範囲を範囲選択し、「データ」タブ-「データの入力規則」
「設定」タブ-「入力値の種類」の▼ボタンをクリックし「リスト」を選びます。
「元の値」のところに「=INDIRECT(B4)」と入力します。
半角で「=」を入れるのも忘れないでください。

「INDIRECT関数」は「参照先のセルに記入されているセルの値を返す」関数だそうです。
「=INDIECT(B4)」の「(B4)」はここでは「セルB4を参照する」の意味です。
(「行5」の時には「入力規則」を確認すると自動的に「=INDIRECT(B5)」と表示されています。)

10051105-1.gif最初に設定する場合は↑画面で「OK」をクリックするとこのような画面が出てくると思います。
これは、まだ「列B」に何も入力されていないからですので、「はい」をクリックしておきます。
(「列B」に選択されたデータが入力されている場合はこの画面は出ません。)

10051106.gif以上で、
1.「送り先名」をプルダウンから選択する。
2.1で選択した送り先に出荷している商品が絞り込まれてプルダウンに表示され、そこから「品名」を選ぶ。

ができるようになりました。

10051107.gif続いて 
3.商品名を選ぶと自動的に「単価」が入るようにしてみます。

別シート(ここではさきほどの送り先名と送り先毎の商品一覧を作ったシート)に「品名」と「単価」の一覧表を作っておきます。

10051120.gif①「単価」の欄(ここではセルF4)を選択し、
②数式バーに「=VLOOKUP(C4,」と記入(「C4」のあとに「,(半角カンマ)」も忘れずにつけておいて下さいね)
③別シート(一覧表を作ったシート)をクリックします。

10051121.gif「品名単価一覧表」の範囲を選択します。
すると数式バーに選択された範囲が表示されます。
このままだと「相対参照」になり、行が変わってくると参照する範囲も自動的に変化してきますので、「絶対参照」にしなければなりません。
「絶対参照」にすると、行が変わっても、参照する範囲は固定となります。

10051122.gif範囲を選択した状態でキーボードの「F4」キーを押します。
すると、先ほどの範囲に「$」マークがつきます。
これで絶対参照になりました。
数式バーの範囲の後ろにも「,」を入れておいて下さい。

10051123.gifその後ろに「2」と入力します。
この「2」は参照する一覧表の1列目・2列目の「2」です。
単価が2列目に書かれてありますので「2」となります。
続いてここではその後ろに「,0」または「,FALSE」をつけておきます。
これは「検索方法」ですが、これを入れなくてもうまく動いてくれる場合もありますが、おかしい場合は入れておくと正常に動作します。
最後に「)」をつけて完了。
これで商品名を選ぶと自動的に「単価」が入るようになりました。

10051124.gif先ほど設定した「単価」や「単位」「金額欄」にすべてに文字や数式をコピーして貼り付けたいところですが、そうすると、このように空欄の行まで「kg」や「#N/A(該当なし)」がすべてに表示されてしまいます。
そこで、今度は空欄の行にはこれらの表示が出ないようにします。

10051125.gif「金額欄(セルG4)」を選択して、数式バーに「=IF(D4="","",D4*F4)」と書き換えます。
「セルD4が空欄ならばセルG4も空欄に、(そうでなければ)D4*F4の解を表示する」という数式にしてやるのです。

同様に「単位」のセルE4にも「=IF(D4="","","kg")」、「単価」セルF4も「=IF(D4="","",VLOOKUP(C4,Sheet2!$A$17:$B$27,2))」を付け加えます。

これらをコピーし、必要箇所に貼り付けると完成です。
10051113.gif

関連記事

6 Comments

まる  

キャリアウーマン!!

お久しぶりです

れんさん!!スゴイですねぇe-349
まるはテキスト見て「?・?・?」e-350
キャリアウーマンなんですね
お勉強させていただきますっ。

2010/05/14 (Fri) 13:52 | REPLY |   

れん  

まる様

まるさん、いつもうれしくなってしまうようなコメントをありがとうございます♪

いえいえ、全然すごくないです……e-449
自分でこんなのが作れるといいのに…と思ったものを、検索で探し出してきてそれを参考にしてつくっているだけですもの…。
ただ作っただけだとすぐに忘れてしまうので、ここに記事にすることによって「とりあえず復習」になり、また忘れてしまった時でも「以前記事にしたぞ」と自分の記事から探し出してこれるので書いてるだけなのでお恥ずかしい限りです……。

でも、自分が作りたい表がうまく作れるとすごくうれしくなりますし、EXCELを触るのが楽しくなります♪

2010/05/14 (Fri) 18:31 | EDIT | REPLY |   

miyoco  

初めまして!

ほんといいですねぇ!
こんな難しい表が簡単にできるなんて~

昨年度1年間、情報処理の
科目履修生として高校の定時制に
通ったんですよ。
懐かしい画面を見つけて思わずコメント
させていただきました(^^)

何とか最後まで出席しただけの落ちこぼれ
でしたが(笑)
IF関数やVLOOKUP関数なんて泣かされましたが
懐かし~い(すっかり思い出状態)
何とかしないとこのまま忘れてしまいそうだわ(汗)

2010/05/15 (Sat) 18:43 | EDIT | REPLY |   

れん  

miyoco様

初めまして♪
ご訪問いただき、そしてコメントまで残して下さってありがとうございます\(^O^)/
すごくうれしいです♪

miyocoさんは情報処理の勉強をされてきたのですね!
うらやましいです!
私は独学……と言えば聞こえがいいんですけど、自己流でやってますから……。

以前関数を覚えようと、手本を見ながらやってみたことがあるのですが、それはそれで「わ~♪こうやるとこんなことができるのね~。」と感動モノなんですけど、実際に使う機会がなかなかないので、すぐに忘れてしまうんです。
でも、仕事でEXCELを触っていて、「こんなことができればいいのに」と思って自分で調べて作ってみると、達成感と、こんなときはこの関数…って覚えてる可能性が高くなりました♪

miyocoさん、私にぜひEXCEL教えて下さ~い(#^.^#)

2010/05/16 (Sun) 13:56 | EDIT | REPLY |   

もも  

初めまして

私のやりたいことピッタリのことを教えていただきました。
まずはお礼を
ありがとうございますm(_ _)m

エクセルは自己流で自信がないので
れんさんと同じものを作り、動作確認をしてから
自分のやりたいこと(※原価計算です)を作りましたところ

式に間違いはなくちゃんと動作し連携していますが
ちょっと不具合が生じています。

例:
品名「えおか」
品名「えくせる」
品名「おがわ」

など品名に重複する文字
(この場合「え」、「お」)
があると

「えくせる」も「おがわ」の単価にも
「えおか」の単価が出てきてしまいます。

この場合の対処法がありましたら
お時間のあるときにでも教えていただけたら
と思いコメントさせていただきました。

無理に…ではありませんので
お時間があるときや
気が向いた時にでも教えていただければと思います

ありがとうございました。
またいろいろこちらで勉強しますv-238

2012/05/25 (Fri) 19:54 | REPLY |   

れん  

もも様

もも様、いらっしゃいませ。
この記事で参考になりましたでしょうか。

私はエクセルは全く分かりませんで、自分が使いやすい表計算をなんとか作ってみたくて、上手くできた時には忘れないように(というか、次回作るときに参考にできるように)記事にしている程度です。

ですので、不具合?が出ても、即答で「ここをこうすれば直りますよ」とは言うことができず、もも様のご質問にお答えできるようになるにはしばらく試行錯誤する時間が必要だと思われます。

もうしばらくお時間をいただいてもよろしいでしょうか。
頑張って対処法がわかるように努力してみたいと思います。
それも私自身の勉強にもなりますし!

わかりましたら(わからなくても)またこのコメント欄でご報告させていただきますのでよろしくお願いいたしますm(__)m

ご訪問くださってありがとうございましたm(__)m

2012/05/26 (Sat) 17:56 | EDIT | REPLY |   

Post a comment