VLOOKUPを使って次の表を完成させてみよう！
の詳細解説ページです。
研修用資料のダウンロードがまだの方は
以下リンクより入手してください。
実践
それでは実践です。
なお構成上、例題１が最も難しいです。
何事も飛び上がる時が一番体力を使います。
逆にここを越えれば簡単です！なんとか頑張ってください！
実際にC4セルに『クリニック７』の
『A製品の実績』を表示してみましょう。
C4セルを選択したら関数の挿入
→VLOOKUPを選択
→OKを押しましょう。
そうすると
- 検索値
- 範囲
- 列番号
- 検索方法
という入力窓が立ち上がります。
必要な数字を持ってこれます！。
検索値の指定
この数字を探せ！
という条件をここで入力します。
ここでは施設コードを使用します。コードがある場合は
コードの使用を第一に考えるのがオススメです。
(企業では顧客や施設に特定の番号を振っていることがほとんどです。)施設名やほかの条件でも問題ありませんが、
- 同一の施設名の存在
- ファイルごとに名前にスペースが入る
- 全角半角などの違い
関数は正確に動かなくなります。
そういった事態を避けるために
基本的に唯一の情報でありブレが少ない
顧客コード＆施設コードを
検索値に設定することがオススメです。
次に検索値の入力方法ですが
主に２つあります。
- 施設コードをすべて選択する方法
(今回だとA４からA9まで選択します) - A列すべてを選択する方法
(Aの場所をクリックします。画像の方法です)
どちらでも構いません。
ただ作成する表のサイズが大きい場合は
A列すべて選択するほうが楽です。
好みの方を使ってください。
これで
『記載してある対応する数字をさがすよ！』
という命令が入力されました。
今回はC4のセルを選択しているので、
検索値範囲のA4の数字『3322』を探して！
という条件になっています。
3322と記載されていれば成功です。
範囲の指定
次に検索範囲を選択します。
範囲の入力窓をクリックしたら
A製品のタブを選択→
施設コードを一番左にして
シートを隅から隅まで選択
(正確には欲しいデータがある範囲をすべて選択します。)
ポイント
ここで重要なポイントは
施設コードを一番左にするというルールです。
正確には被検索値を一番左に設定します。
今回はA列に被検索値の施設コードが含まれていますが
これが仮にB列に含まれていた場合はB列が一番左です。
VLOOKUPのルールは実は少ないのですが
被検索値を一番左に設定する！
これはその数少ないルールの一つなので覚えましょう！
列番号の指定
次は列番号を決めます。
ここでいう列番号は
被検索値を１としたときに、
欲しい数字はいくつ隣にあるのか
ということをを示します。
画像を見てもらうとわかりやすいですが
今回の被検索値の列は『施設コード』です。
これを１としてB列が２C列が３と続きます。
表が大きい場合は20とか50とか入力する場合もあります。
なお、こういう場合は数えるのがしんどいので
画像と同じようにExcel側に数字を振ってしまいます。
今回欲しいのは今期実績ですので３と入力します。
これで検索値3322を探して、
それを１列目とした時の
３列目の数字に記載されている数字(1980)を表示する！
という命令になります。
４を入れれば先期実績になりますし、
２を入れれば施設名が表示されます。
試してみてください。
検索方法の指定
最後に検索方法です。
検索方法にはFALSEとTRUEという
二つの検索方法がありますが、
基本的にはFALSEでOKです。
FALSEは完全一致という検索方法で
検索値(例題シートの3322)と被検索値(今回は当然3322)が
完全に一致している場合に検索結果を表示します。
仮に3322がA製品シートにない場合はエラーになります。
TRUEを選ぶと
仮に3322という値がない場合、
最も近い値の数字を探して表示します。
3321という被検索値があれば、
それを１列目とした３列目を表示してくれます。
(つまりこんな似てる数字ならあったよ！とやってくれます)
検索値に余裕を持たせて検索する方法です。
基本的には何も考えずFALSEまたはfalseと入力すればOKです。
絶対参照化も忘れずに：最後のひと手間
ここまで入力出来たらOKを押さ…ないでください！
押しても正確に関数は動きますが、
作った数式をより利用しVLOOKUPを使いこなすために
ここでもうひと手間加えます。
Excelには数式をコピーすると
セルの変更によりズレた分を勝手に補正してくれる
ありがたい機能が存在しています。
基本的にはありがたいシステムなのですが、
VLOOKUPでは邪魔になることが多いです。
この機能のおかげで、作った数式をこのまま使うと、
セルの移動に伴って
検索値や範囲の指定がズレていきます。
これが起こると、結果データが指定範囲に含まれなくなり、見つからずエラーになってしまいます。
これを防ぐため絶対参照と呼ばれる方法を使います。
よく$マークを２つ使うので
$$固定と私は読んでいます笑（ドルドル固定）
絶対参照はExcelの
勝手に数字を補正するシステムを消すための命令です。
ポイント
検索値を選択し数値をグレー反転させ
F4キーを１度だけ押してください
(大体キーボードの一番上にあります)
反応しない場合はFnキーをおしてから、または押しながら
F4キーを押してください。
数値に＄マークが二つ挿入されるはずです。
これでExcelのお節介を止めることが可能です。
同列のセルに数式をコピー
OKを押したらExcelのバージョンによっては
勝手に下の同列の数値まで入力されているかもしれません。
もしされていなければC4セルを選択したときにあらわれる
右下の小さな■をダブルクリック
してください。
それぞれの検索値に対応する数字一気に表示されます。
他の列に数式をコピー
同様にB製品＆C製品も数式を組んでいきます。
同じように０から数式を組んでもOKですが、
CSV出力した元データのデータの並びが同じならば
作った数式を利用する方法が時短になります。
①先にデータの形をそろえる整理用シートをつくる
②元データからVLOOKUPで整理用シートにデータを引っ張る。
③さらに集計用シートに整理用シートからデータを引っ張る。というVLOOKUPを二段活用するという力業もあります。
ポイント
まずはC４をコピーしてC5に数式を張り付けます。
次にB製品のタブ名をコピーします。
(※今回のタブ名はB製品)
次はC5を選択してください。
選択したら上部に
ｆｘの＝VLOOKUP（～、～A製品～、３、FALSE)と
数式が書かれている場所がありますよね？
と書かれているA製品を選択し削除
削除した場所にコピーしたタブ名B製品を貼り付けます。
A製品からB製品に変わり、
742という数字が表れたら成功です。
C列も同様に行いましょう！
書き換えがうまくいかないときは…
このように一度作った数式を書き換えることで
さらに時短をすることが可能になります。
ただ何らかの理由によって書き換えが
うまくいかないときがあります。
そんなときの対応方法は２つあります。
VLOOKUPの数式は前述のとおり
(検索値、範囲、列番号、検索方法)
の順番で並んでいます。
数式の書き換えがうまくいかないときは、
『、』を点を目印にして
エラーが起こっていると思われる部分を
すべて書き換えてしまうことで対応が可能です。
今回、もしB製品への書き換えがうまくいかなかった方は
『範囲』の部分(、A製品～、)の『、と、』の間を
すべて消します。
そのままB製品のタブを押し
範囲を指定することで必要な数字が入力されます。
これでもだめな時は最後の手段です！
０から数式を組みなおしましょう！
組み直せばオールオッケー
まとめと次回
VLOOKUPの
基本的な使い方、関数の組み方、注意点を紹介しました。
最後に一つだけ、心に刻んで欲しいことがあるのですが、
『VLOOKUPが変な挙動をする場合90％は人のミスです』
半角全角の違いやスペースの有無、指定範囲のミスです。
特に『〇〇』と『〇〇 』というスペースの有無は
起こりがちですが、何度見直しても気づけないミスです。
この辺を心に刻んでもらえるといつか
ミスがどこにあるんや！
訳変わらん！Excel壊れてるんか！
と思ったときに役に立つはずです！
例題２からは軽めです。
元記事に戻って例題２に進んでください
