HYT MachineWorks

やったこととか思いついたことをメモしておくブログです。

ExcelのVLOOKUPを使ったかんたんなデータ集計入門

2つのシートに以上にまたがる情報をコピペ無しで集計したい

みんな、大好きエクセル。気合でコピペで切り貼りしたりしてませんか?気合を入れて集計して、終わる瞬間に元データ更新したから作り直してとか言われてイライラしませんか?そんなあなたに送る関数の使い方メモです。

 誰もが口をそろえるVLOOKUP

集計するという話になったらすぐ話題にのぼるのが、VLOOKUPでしょう。ざっくりいうと、探したい値を別の表から探して、その横にある値を取ってこれる関数です。この関数には前提条件があって。必ず探したい値が入った列が一番左端にある条件があります。例えば、下の表が、範囲がF6:F15にある場合に、F列に"た"が含まれる値のG列の値をとってきたいとします。すると式は以下のようになります

  F G
6 1000
7 1200
8 1300
9 1400
10 500
11 200
12 214
13 201
14 213
15 34221

=VLOOKUP("た",F6:G15,2,FALSE)

検索結果は、1400になります

 式の解説をすると、

=VLOOKUP("検索値", ”検索範囲” , 行番号 , 検索方式)

1.検索値は、自分が探したい値になります。上の例で行くと"た"が相当します、必ずしも直接書く必要はなくセルを参照(A1とか)でもOKです。

2.検索範囲は、自分がデータを取ってきたい表の範囲になります。必ず、探したい値が含まれる列を第一列目になるように範囲指定をしてください。ここでは、F6:G15がそこに相当します。ですが、一点注意があって上の書き方で書いた場合、検索値をセル参照にしてオートフィルで反映させると、あろうことか範囲も動いてしまいます。

なので、検索範囲は決まっている場合がほとんどでしょうから、絶対参照にかえてしまいましょう。替え方は、上の例で行くと関数入力欄のF6にカーソルを合わせてファンクションキー(数字の上にF4とかあるやつ)のF4を押しましょう。それで切り替わります。

ファンクションキーを押す前
f:id:hytmachineworks:20170711144449j:plain

ファンクションキーを押した後

f:id:hytmachineworks:20170711144453j:plain

といった感じで切り替わります。ついでに、G15の側も変更してしまいましょう。

ちなみに、F4による切り替えは連続して押すことでどんどんと切り替わり、初めをA1とすると

A1 → $A$1 → A$1 → $A1 → A1

と変化します。ドルマークが付いている行もしくは列が固定されます。

3.行番号ですが、これは、検索範囲の列数以下の値をしていしてください。上の例ですと、2行ですから最大で2になります。1を指定してもいいですが、検索値をそのまま返すことになります。

4.検索方法は、初めはとりあえずFALSEと書くと覚えていてください。ちょっと乱暴かもしれませんが

5.エラーについて。ここまで説明して最後にエラーについて。範囲指定では触れませんでしたが、一致する値が無いとエラーを返します。それを回避するよくある手法は、IFERROR関数との組み合わせでこの関数は

=IFERROR(何らかの関数,"エラーのときの値")

という感じで、何らかの関数の部分がエラーを出すと、その後ろのエラーのときの値を返します。以上をまとめて、エラーのとき空欄にする時は

=IFERROR(VLOOKUP("た",$F$6:$G$15,2,FALSE),"")

と書けばいいことになります。検索値をセル参照にすれば2つの表をかんたんに1つにできるようになりますね。

本当は、FINDとかMATCHとかINDIRECTとかの話もしたかったけど長くなったのでまた今度