こんにちは。ヒトツメです。
前回は、VLOOKUPおよびHLOOKUP、そして新関数のXLOOKUPについて、それぞれの構成と間違えやすいポイントについて解説してきました。
今回は、前回の内容を踏まえて、具体的にVLOOKUPなどのLOOKUP系の関数をどのような場合に使うと便利なのかについて考えてみたいと思います。
二つのデータを結合する
種類ごとにデータを保持していることが前提
ずばり、VLOOKUP関数の使いどころの一つ目は、二つのデータを結合する場合です。
例えば、こんな二つのデータがあったとします。
これらのデータから、担当エリアごとの取引金額（請求金額）のデータを作りたいとしたときをイメージして貰えれば、わかりやすいと思います。この時、取引先名やIDを突合キーとして、二つのデータを横にくっつけたデータがあれば、簡単に集計できそうですが、その時にVLOOKUPが活用できます。
初めから、下のデータの右側に、各取引先の担当エリアをつけておく、というのも一つの手ですが、下の取引データがどんどん増えていった場合、データ自体が大きくなることが考えられます。したがって、取引データには、取引先の属性情報まで載せてしまうのは、あまり得策ではないケースが多々あります。
結合ができれば、例えば取引先属性のデータ、取引のデータ、担当者のデータ、といった形で、種類ごとにデータを保持することができ、その時々に応じて、必要なデータを結合しながら作業を行うことができます。
結合キーの重複があるデータをベースにする
さて、実際に式を入れながら結合データを作っていくわけですが、この時、結合キー、ここでは取引先名やIDに重複があるデータをベースに、そのデータの右側に式を入れるようにしなければならない、というのがポイントです。
というのも、重複がない方のデータをベースにしてしまうと、VLOOKUPなどで結合しようとしたときに、より上の方のデータだけを引っ張ってきてしまうことになるため、結局求めているデータは得られないことになってしまうからです。
例えば上のデータをベースにして式を入れていくと、下のデータの2行目は、A株式会社のところで拾うことになりますが、9行目のデータは無視されてしまうことになります。これでは、1月20日の取引は集計されない、ということになってしまいます。
式を入れてみる
こういったところに気を付けながら、実際に式を入れていくと、例えばE2には、次のような式を入れることになります。
=VLOOKUP(A2,取引先データ!A:E,3,0)
同じように、F2セルには
=VLOOKUP(A2,取引先データ!A:E,4,0)
G2セルには
=VLOOKUP(A2,取引先データ!A:E,4,0)
といった形で入れていき、10行目まで式をコピーすると、二つのデータを結合することができます。
相対参照と絶対参照
検索値と参照先の固定
ただ、横に長い式の場合、一つ一つのセルにこれらの式を入れていくのは面倒です。そこで、通常、このような場合に備えてExcelに用意されている、絶対参照という機能を使うのが一般的です。
例えば、上のE2のセルをコピーし、そのままF2列に張り付けると、
=VLOOKUP(B2,取引先データ!B:F,3,0)
となります。
これは、E2にとってのA2は、F2にとってはB2だから、A2のところにB2というセルの住所が入ってしまうという事象です。
これを避けるために、E2列に初めから、
=VLOOKUP($A2,取引先データ!$A:$E,3,0)
と入れておくことで、「$」がついているセルの住所は、ほかのセルにコピーしても動かないというものです。
実際に、この式をF2列にコピーしても、
=VLOOKUP($A2,取引先データ!$A:$E,3,0)
となり、検索値と参照範囲は固定されたままになります。
戻り値の列指定の移動
しかしながら、これでは、いつでも戻り値は「取引データ!$A:$E」の、「3列目」というのは変わりません。横に移動したらその分横に増えてもらわないと、いつまでも同じ戻り値が返ってきてしまいます。
そこで活用するのが、COLUMN関数です。これは、参照するセルの列番号を返します。例えば、COLUMN(A1)なら1を、COLUMN(B1)なら2を返します。普段はあまり使いませんが、これを利用することで、横に動かしながらVLOOKUPの三つ目の引数をカウントアップすることができます。
=VLOOKUP($A2,取引先データ!$A:$E,COLUMN(C2),0)
ずばり、上記のような記載です。COLUMN(C2)は、C列の列番号、つまり3を返しますが、これを相対参照にすることで、横に移動した時、つまりF2にこの式をコピーした時に、
=VLOOKUP($A2,取引先データ!$A:$E,COLUMN(D2),0)
となり、無事F列では取引先データのD列を参照することができるようになるわけです。
あとはE列からG列にこの式をコピーすれば、欲しかったデータが得られることになります。
XLOOKUPで代用
VLOOKUPでの結合はデータと処理が重くなる
ただ、VLOOKUPによるデータの結合には、一つ問題があります。というのも、上の例だと、3列×9行、つまり27個式が入るわけですが、VLOOKUPの関数は、引数も多く、データがそれなりに重くなることが想定されるからです。さらに、データが重くなるだけではなく、VLOOKUP自体、処理がそれなりに重いので、毎回式を再計算させることになると、いちいち27回も思い計算をExcelに強いることになり、「ちょっとデータを書き換える」といったことができなくなってしまいます。
例えばこれが、1,000行あれば、さらにデータは重くなるうえ、Excelの処理自体もかなり重くなってしまい、データを一つ入力するのに、5秒くらいかかってしまったりします。
こういうときのために、Excelは、計算結果を手動でのみ更新するという機能がありますが、結局、再計算の時には同じように時間がかかるわけで、時間のロスはかなり発生してしまいます。
データをSPILLさせる
そこで、XLOOKUPが使える環境にある場合、E2列に次のような式を入れることを強くお勧めします。
=XLOOKUP($A2,取引先データ!$A:$A,取引先データ!$C:$E)
すると、上のように、F2セルとG2セルに、結果が勝手に入ります。
SPILLという機能なのですが、比較的新しいExcelでは、戻り値を複数のセルにまたがって返すことができ、その場合、実際に入れているセルの右側や下側に、勝手に結果を表示させることができます。
上記の式では、参照範囲を複数の列にわたって設定しているため、戻りの値も複数の列にわたって返しており、それが何も入っていないF2セルとG2セルに表示されている、という状況です。
これなら、実際に式を計算する量が、この例だと3分の1に削減できますし、データもそこまで重くならず、処理することが可能となります。
さいごに
以上のように、「検索」という観点ではなく、データを結合するという観点だと、VLOOKUPは非常に強力に作用します。さらにXLOOKUPを活用することで、データを軽くすることもできます。XLOOKUPは新関数で、開く環境によって作用しないということがあるので注意が必要ですが、使いどころを考えると非常に便利に使うことができます。
- VLOOKUPは、検索という観点ではなく、データの結合という観点で見ると非常に強力
- XLOOKUPを使えば、処理などを軽くすることができるが、開く環境によって作用しないので注意が必要
次回はもう一つある、VLOOKUPの使いどころについて投稿してみたいと思います。
コメント