ExcelのVLOOKUPを使って書類作成を効率化する

関数式
流れ
目的と効果
必要なもの
方法
VLOOKUPの考え方
VLOOKUPを理解する利点
【レベルアップ？】
感想
方法(簡易版)

関数式

=VLOOKUP(検索値,範囲,列番号,[検索の型])

下記の「方法(簡易版)」にリンクします。

※場合によって、「絶対参照」の知識が必要です。とくにオートフィルを使うときです。

流れ

データ作成(サンプルデータあります)
テンプレート作成(サンプル画像あります)
データを埋め込みたい各セルに「=VLOOKUP()」を記述
検索値を入力し、挙動を確認する

目的と効果

「ラクしたい！」
「できるだけ」
「作業が一瞬で終わる。」
「わかりやすのが一番！(テンプレートと表の分離)」
「数字入れて、印刷して(PDF化して)、確認したら、はい！終わり！」

しくみを作ったら簡単な入力で、はい！終わり！！
書類完成！
時短成功！！
暇になる！！！
シンプルにめっちゃラク！！！！

必要なもの

データシート
テンプレート
VLOOKUPの式

シートは「別シート」であること。(もちろん同じシートでも使えます。)
同じシートでも構いませんが、保守・運用・管理が複雑になる気がします。
個人的にはわかりすいのが一番！
機器やサービスもできるだけ、可能な限り設定を少なく、デフォルトのままで使いたい派です。それも社内でいろんなことを一人でやっているので、手が回らないというのが一番の理由かもしれません。仕様やスペックも昨今では変化が速いので、作業が割に合わなかったりします。再現性も高いほうがいいので、カスタマイズしたりすると、カスタマイズしたその人しかわからないってことに陥ることが多いです。専門の人がいれば話は違うと思いますが。
話がそれてしまいました。でも考え方って結構重要です。
シートは分担したほうがいいと思います！
世の中分業でしょ？最近は多機能な家電も増えてるけど、ユーザーは別にそれを求めてない。開発者の「あれもできます！これもできます！こんなの付けてみました！」っていう…。このままいくと電子レンジに洗濯機と掃除機が付きそうな勢いです。
でも…それは…もう「家」だ笑！
話がそれてしまいました。でも考え方って結構重要です。
データ表が別のブック(ファイル)にあってもいいですが、開いていないとデータ参照ができないらしい。

方法

1.データ作成(サンプルデータあります)

何はともあれデータ表を作成します。

8列4行のCSVデータ(A列~H列)をExcelファイルにします。
とりあえず3件くらいあれば十分です。

メモ帳で.csvで保存
↓
Excelで開き
↓
名前を付けてExcelブック形式.xlsxで新規保存

番号,会社名,住所,電話番号,担当者名,商品,設置日,営業担当者
1,株式会社トヨマ,カレー県ほにゃらら,123-4545644,田中,洗濯機,2019年4月15日,佐藤
2,株式会社シャーボ,マーボー県ほにゃらら,456-58239,皆川,電子レンジ,2019年4月20日,中村
3,NNT株式会社,チョコ県ほにゃらら,9874-24,井村,パソコン,2019年5月10日,武田

まずはデータありきです。
ここで作るデータ表は「生データ」として作成し、必要があればコピペし、いつでもどこでも使い回しが利くように作成するのが好ましいです。
よって、データ表シートと書類作成シートは別にします。
とにかくデータが一番大事です。
1行目に項目名を入力した後は、編集、追加するときもデータだけを入れるようにしてください。
色を付けたり、テキストボックスで補足したり、コメントなんて入れないように！
とにかくデータだけのシートを作成します。
別のブックに作成することもできますが、
書類作成ブックとデータ表ブックが別だと、データを引っ張ってくるときに両方のブックを開いていないといけません。
とにかくデータ表はシンプルにします。

2.テンプレート作成(サンプル画像あります)

VLOOKUPを実感してほしいだけですので簡単にしました。
新しいシートに2列8行の書類作成シートを作成します。

これからすることは、画像赤枠セルに番号を入れたら、他のセルも一気に全部埋まるようにVULOOKUPをちりばめます！めっちゃラクです！！

サンプルの表はとてもシンプルですが、実際にVLOOKUPを使用するときは、このテンプレートを作り込むことになります。時間もかかるし、ちょっと悩んだりもしますが、まさにプログラミングに近い行為です。というより、広義ではプログラミングと言えます。

3.データを埋め込みたい各セルにVLOOKUPを記述

=VLOOKUP(検索値,範囲,列番号,検索の型)

もっとわかりやすく？

=VLOOKUP(検索する値が入ったセル,データ表指定,埋め込みたい値の列番号,ここは基本FALSE)

もっとわかりやすく？

セルの値をデータ表から検索し「行」を特定して、列番号の値を埋め込む。

もはや式ではなくなりました。
「はい！ここテストに出ます！！絶対暗記してください！！！」

【引数は4つ】
1.検索値→画像赤枠のセルC3
2.リスト範囲→「test!A:H」(「testシートのAからHの列」)
3.範囲の列番号→ここの値が頻繁に変わります
4.FALSEかTRUE→完全一致にしたいので基本FALSEです
※「2.範囲」についてです。列だけで指定しましたが、「A2:H11」でもOKです。

検索値はとにかくデータ表の中でユニークな番号であること。例えば商品コードや、識別番号みたいな重複のない番号のことです。
リスト範囲はID番号の列も含んで範囲を指定します。
データシートを分けておけば、上記のことを考慮せず、ベストプラクティスになりますね。

ようは、この式「=VLOOKUP(C3,test!A:H,2,FALSE)」の列番号の数字を変えながら各セルに埋め込んでいきます。
ちなみにこの式はC3が1なら「株式会社トヨマ」そのものを示します。

検索値で「行」が特定される。
引数の3番目で「列」が特定される

よって、見えない十字線が出来上がり、データ表の中で唯一の「セル」が特定される。

ちなみの式の中でさらっと「test!」と書きましたが、どうしたらいいかわからないときはGoogleで検索します。

【やりたいこと】
「別のシートを参照するには？」
↓
【キーワード】
vlookup 別シート　テーブル　参照
↓
【参考結果】
https://excel-microsoft.info/%E9%96%A2%E6%95%B0/289

4.検索値を入力し、挙動を確認する

C3の値を1、2、3と入れながら挙動を確認する。
これ、わかる？可能性が拡がりまくるよね？って、VLOOKUPはもうずっと前からあるか…苦笑。
伝票や帳票出力、依頼書や詳細データページなどに使えます。
Webアプリ(データベース利用)も実はイメージは似たようなものなのです。
また別の機会に、プログラミングの内容で書こうと思います。

データ表に値がなくてエラーが出る場合は、その手間を回避するためにデータ表に「-」とか入れて、
一旦エラーを回避してもいいと思います。その間に検索して調べるみたいな。

調べてみました。

【やりたいこと】
「参照先が空白、エラーの場合に任意の文字を入れたい！」
↓
【キーワード】
エクセル vlookup　空白　任意の文字
↓
【参考結果】
http://www.asahi-net.or.jp/~ef2o-inue/shiki/sub03_030_03.html

VLOOKUPの考え方

データ表の中から行と列を指定し、セルのデータを引っ張ってきて表示する。
行と列の見えない十字線でセルを特定することができる。

VLOOKUPはいろんなことに使えます。
商品一覧表だったり、パスワード一覧表、案件管理表、行ってみたい旅行先リスト、大好きなアイスクリーム屋さんリストだったり、リストや表にできるものすべてが対象になります。

VLOOKUPを理解する利点

これができるとすごくいいです。
何が良いかというとExcelで簡単にWebアプリのしくみが学べるということです。
もちろんそんなこと意識して使う必要はないですが、VLOOKUPはざっくり言うとデータベースアプリと同じような動きをします。

【レベルアップ？】

「IF関数を使ってVLOOKUPの参照先を変える。」

【Google検索】
「エクセルで条件分岐したい！」
↓
【キーワード】
エクセル　if
↓
【参考結果】
https://udemy.benesse.co.jp/office-enhance/microsoft-office/excel-if.html

下記のコードは3つに分岐してます。
個人的に3つが限界かなと思います。
書くほうも、使うほうも。

=IF(条件,VLOOKUP(検索値,範囲,列番号,FALSE),IF(条件式,VLOOKUP(検索値,範囲,列番号,FALSE)))

※かっこ()の数に注意しましょう。Excelのおせっかいで勝手に()が入ることもあるので。

IFやVLOOKUPを使って自由自在に埋め込んだら…ヒマでヒマでしょうがない。

感想

Excelってプログラミングだよな…。
PythonやRubyなどにも最初から組み込まれている標準メソッド、標準関数ってのがある。
できれば自分で書くより、それを使ったほうがいいし、バグもない。
だからExcelのマクロとかより、関数使ったほうがいい。

方法(簡易版)

次のデータをメモ帳か何かでcsvファイルにして、Excelで開いてExcelファイルで保存し直す。(testというファイル名)

番号,会社名,住所,電話番号,担当者名,商品,設置日,営業担当者
1,株式会社トヨマ,カレー県ほにゃらら,123-4545644,田中,洗濯機,2019年4月15日,佐藤
2,株式会社シャーボ,マーボー県ほにゃらら,456-58239,皆川,電子レンジ,2019年4月20日,中村
3,NNT株式会社,チョコ県ほにゃらら,9874-24,井村,パソコン,2019年5月10日,武田

新規シートに次の画像の表を作る。(赤い色は不要。余白が欲しいためB3セルからC10まで作成。)

C4セルにVLOOKUPのコードを記述する。
おそらく「#N/A」というエラーが表示されるので、先にC3に1という値を入れておいてもいいです。もちろんあとでもいいです。

=VLOOKUP(C3,test!A:H,2,FALSE)

残りのセルにも、引数の3番目を変えながらVLOOKUPのコードを記述する。

C5→=VLOOKUP(C3,test!A:H,3,FALSE)
C6→=VLOOKUP(C3,test!A:H,4,FALSE)
…
C10→=VLOOKUP(C3,test!A:H,8,FALSE)

オートフィルを使う場合は、C3を絶対参照($マーク)にしてからオートフィルをする。そして引数3番目を修正していく。
(でもセルがつながっていないとオートフィルは使えないので、順次コピペします。)
ちなみに絶対参照を使った式は下記です。

#あまり使いません
=VLOOKUP($C$3,test!A:H,2,FALSE)

C列に値が表示されればVLOOKUPは完了です。