XLOOKUPとVLOOKUPの比較。推移表から任意の列の数字を取り出す事例。

会計ソフトの推移表から、任意の列の数字を取り出す事例として、VLOOKUP関数 と XLOOKUP関数を比較してみました。

※XLOOKUPの事例　by　Leica M10

VLOOKUP関数とXLOOKUP関数

Excelに出した会計ソフトからの推移表で、

たとえば4月の数字を表示したいとします。

売上高、経常利益、現金・預金合計だけを取り出したいというケースです。

この場合 VLOOKUP 関数を使うなら、次のように入力すれば

A2の「売上高」に一致するものを推移表から探して取り出せるのです。

しくみとしてはA2の「売上高」を推移表の A から M の一番左から探して、見つかったら2列目つまり4月の数字を取り出すというもので、完全一致のものを取り出すのであれば、FALSEを入れます。

この場合は、完全に一致したものを取り出したいのでFALSEです。

一方、新関数である XLOOKUP関数を使うともっとシンプルにできます。
XLOOKUP 関数については、お使いのExcel で使えない場合もありますので、こちらの記事を参考にしていただければ。
（Office2019、Microsoft365（旧Office365）なら使えます）

VLOOKUP関数を使ってはいけない。よりシンプルなXLOOKUP関数を使う。 | EX-IT

XLOOKUP関数とスピル。#N/A、#SPILL! エラーの解決方法。 | EX-IT

XLOOKUP 関数なら、次のように入れます。

売上高を推移表の A列から探して見つかったら B 列を表示というものです 。
XLOOKUP 関数の場合は何も設定しなければ、標準設定で完全一致のものを探しますので、これ以外は省略できます。

VLOOKUP 関数よりもシンプルなのです。

これらの事例では4月の数値を取り出しましたが、これが5月、6月、7月……となるとどうすればいいか。
その対応策についてまとめてみました。

 

VLOOKUP関数で推移表から取り出す

VLOOKUP 関数では、4月を取り出す場合、次のように入力しますので、何列目を取り出すかという2の部分を変えれば、5月、6月、7月……と変わっても対応できます 。

1つの方法としては、たとえば適当な場所＝セル C1に、2を入れて、それを数式で読み取るというものです。

4月だったら2、5月だったら3、6月だったら4と変えていけば読み取る場所を変えることができます。

もしプリントアウトするのであれば、この セルC1は範囲外に置いておけば問題ありません。
または別シートにおいてもいいでしょう。

しかしながらこの数字を毎月入力する手間も惜しみたいもの。
さらにはミスの可能性があるので、こういった方法は避けたいものです。

「2020年4月」から、MONTH関数で4という数字を取り出し、その4を2にするためにはどうすればいいかを考えます。
4から2を引けば2、5から2を引けば3、6から2を引けば4となり、2、3、4と数字を変えていくことができるわけです。

VLOOKUP 関数を次のように変えれば、セルB1の年月を変えるだけで、取り出す数値も変わります。

さらに、この場合は4月、5月、6月……翌年3月まで続く3月決算の事例にしていますので、12月まではこの数式でいいのですが、1月、2月、3月は、「2を引いて」という風には計算できません。
たとえば1月であれば11列目を表示するので、1+10、2月であれば12列目なので2+10と計算します。

IF で、「もし3より大きかったら2引いて、そうじゃなかったら10を足す」というように変えて対応しましょう。

決算月によってこの数式が変わるということです。

さらに工夫すればそれすらも自動化できますが。
（私は自動化しています）

 

XLOOKUP関数で推移表から取り出す

一方 XLOOKUP 関数で任意の数字を取り出すときにどうするか。
XLOOKUP 関数の式を見ると、列を指定しているところは「推移表!B:B」 というところです

これを VLOOKUP 関数のように1つずつずらしていくというのは、そうかんたんではありません。
BをC、D、E ……と変えたいわけです。

XLOOKUP関数とOFFSET関数を使う方法

この場合、OFFSET関数を使う方法があります。
OFFSET関数は、=OFFSET（〇、△、□）で、〇を行（下）へ△、列（右）へ□ずらして表示するもの。

=XLOOKUP(A2,推移表!A:A,推移表!B:B)
4月を指定すると、行も列も0、つまりずらさないので、このように表現します。

月だと列（右）へ1つ移動するので、
=OFFSET(XLOOKUP(A2,推移表!A:A,推移表!B:B),0,1)
です。

 

これを「2020年4月」からMONTH関数で取り出すと、4から4を引くと0になるので、次のような数式になります。

さらに、条件によってわけるので、こうしましょう。

※追記：XLOOKUPで該当するものが見つからない場合があるなら、IFERRORでエラー処理が必要です。

 

 

XLOOKUP関数とINDEX関数を使う方法

一方 XLOOKUP 関数は、次のように、4月から3月まで（BからM）と指定することもできます。

この場合は Excel のスピルという機能も使いつつ 、B から M つまり4月から3月までのデータをいっぺんに表示できるのです。

これはこれで便利なのですが、今は4月分だけを出せばいいので、このうち、4月分だけを表示するという数式に変えてみましょう。

ここでは INDEX関数と組み合わせます。
BからMで、一括して読み取ったものの中から1番目、つまり1と指定すれば4月分が表示できるのです。

では、この1というものを「2020年4月」からMONTH関数で取り出した4を使って表現すると、4から3を引いて1になるので、MONTH（B1)-3という指定をすれば取り出せるわけです。

3より大きいかどうかで、場合分けをすると次のようになります。
1月だと11列目なので10を足すのです。

 

OFFSETとINDEXは、どっちもどっちですが、スピル（すべてを一括で表示する）を使うことを考えると、INDEXで使えるようになっておいたほうがいいかなと。
（INDEXのほうがプログラミング的な要素が大きいので私は好きです）

※追記：XLOOKUPで該当するものが見つからない場合があるなら、IFERRORでエラー処理が必要です。

 

XLOOKUP関数は、VLOOKUP関数は、この事例だとVLOOKUP関数のほうがやりやすいかもしれませんが、XLOOKUP関数自体はシンプルで、使いやすいので、こういった事例の時は、OFFSETやINDEXを使ってみていただければ。

 

 

---

■編集後記
昨日は、税理士業中心に。
仕事後、サザビー（MG 1/100)が完成。
圧巻のボリュームでした。
次は、百式。

母からLINEがあり、「娘日記ないんやね」と。
ん？と思って見返してみると、おとといの記事、編集後記以下が入っていませんでした。
多めに書いた編集後記のときに限って……。
追加しました。
（記事は興味ない方多いかと思いますが）

Leica M10 おすすめアクセサリ。持ち運び・電源・撮影

■「1日1新」
MG　サザビー　完成
娘と新しい遊び
生協　ポップコーン

■娘(3歳2ヶ月）日記

散歩に行っている穴場の場所で、ボールを転がしたいと（坂になっています）。
行ってみて転がしてみると思うように転がりません。
傾斜がそれほどなく、すべらないようにもなっていて。
その場でも落ち込み、帰宅してからも「あんまり転がらなかった……」とごろごろしながら言ってました。

明日は丘のようなところに連れて行ってみようかなと。