【XLOOKUP】データを探す、めちゃくちゃ有能な関数！

Excelはときどきバージョンアップがあって、その大抵は「あー、まあ、そんな細かい使い勝手を変えてくれなくても別にいいんだけど…」的な瑣末なことだったりするのですが、ときどき新しい関数を作ってくれて、それが大当たりだったりします。

んで、近年ワタシを最も「うおおおおおおお、これは使えるぜ！」と興奮させた関数がこれ。
XLOOKUP関数です。

XLOOKUP関数｜指定した範囲からデータを探してくれる

複数シートにデータがまたがっているとき、シート名とセルを指定してやれば足し算とかできるよ。
ただし、どのシートも同じようなデータの並びになっていることが大前提だよ。
といいました。

しかし、世の中にはお行儀のいいデータばかりでもありません。
1月はこうなのに。

2月はこうで。

3月はこう。

というやんちゃなデータも世の中には数多く存在するでしょう。
つか、ほとんどがやんちゃなデータなのではなかろうか。

そんなときに便利なのがXLOOKUP関数です。

わかりやすくするために、合計を出すシートには月別データを並べられるようにしました。

ちなみにこれ、1行目の「支店名」「売上高」はセルの結合をしていません。罫線の色でごまかしてあるだけ。
なんなら、C1とD1にも「売上高」と入力してあり、文字の色を変えて邪魔にならないようにしています。
こういう細工をしてでもセルは結合してくれるな…！派なのです。結合しちゃうと、後からもっとデータをアレコレしたいとかグラフを作りたいとかってときに不便なのですよ。

さて、ではXLOOKUP関数を使って、東京本社の1月の売上高の値を、1月のシートから持ってきた数式を見てみましょう。

XLOOKUP関数は、次のように記載できます。

この場合、探したいデータは「東京本社」なのでA3セルを指定しています。
A3ではなく”東京本社”と文字列で入力しても式は成立するけど、汎用性がないのでそれはダメ〜。

んで、探したい範囲は1月のシートにあるので’1月’!でシート名を指定。
範囲は次の赤枠内、「東京本社」とかが入っているA2:A6です。

「返す」というのは、計算結果として表示させたいデータを出すときの言い方で、この場合は売上高を返したいので同じく1月シートのB列の範囲を指定します。

「東京支社っていうのをここから探して、ここから返してね」という作りの関数ということです。
数式を書くときも割とわかりやすいです。

では、東京支社の2月と3月のデータも引っ張ってこられるように、今入力した数式をちょいと弄ってみましょう。

まず、探したいデータについて。
本社や支店の名前が入っているのはA列と決まっているので、A列は絶対参照に・行は相対参照にします。
それから探す範囲と返す範囲は2月も3月も同じ範囲なので、セルの範囲は絶対参照にします。

で、これをC3とD3にもずるずるっとコピペすればいいかというと。

あれ、1月のシートを参照したままですね。

置換

C3とD3の数式はそれぞれ2月と3月から持ってきたいから、しゃーない手打ちでぽちぽち修正するか…。
というのはこれくらいのボリュームなら可能ですが、やめておきましょう。手打ちはミスの元です。

そこで修正方法のひとつとして、置換を挙げます。
まず2月の値に修正したいC3セルを選択して、「ホーム」タブから編集の「検索と選択」→「置換」をぽちっとします。

すると、何をどういう風に置換したいのか訊かれるので、1月を2月に置き換えればいいんだなふんふんと指定し「すべて置換」をクリックすると、思わぬ箇所も置換されて「わあ！」となると思います。
「わあ！」となるのを防ぐためには

• 複数セルを選択しておく（ひとつのセルだけ選択だと、表やシート全体が置換されがち）
• 置換前・後を誤解が生じないようにガチガチに指定する
• 一気に置換するのではなく、ひとつひとつ検索して確認してから置換する

てな気遣いが必要です。
面倒くさいですが、Excelで置換は結構使うので慣れてください。

と、置換を使って数式を修正するのも一興ですが、ワタシはこの場合なら別の方法で数式を書きます。
が、それを語り出すと長くなるのでまたの機会に。

1月から3月の数式を正しく修正したのがこちら。

大丈夫かなー、数字合ってるかなー。
と、シートを行ったり来たりするのは面倒です。
そんな場合は、「表示」タブから「新規ウィンドウ」をぽちってやってください。

同じファイルが別のウィンドウで開いて、比較がしやすくなります。
アプリ版だと並べて表示させて同時にスクロールさせることもできるので、目視でザッと確認するときには便利です。

XLOOKUP関数がない時代｜VLOOKUPとHLOOKUP

XLOOKUP関数は近年追加された関数といいました。
ではそれがない時代はどうしていたかといいますと、XLOOKUP的な動きをする関数があるので、それを使用していました。

XLOOKUPは縦方向にも横方向にも検索できますが、なんとXLOOKUPが存在しない時代は縦と横で関数が異なったんですね。
日頃英語と縁がない生活をしていると「えーと、バーチカルだからVで、ホライズンだからHで…」と、一瞬考えてしまいます。そしてボーッとしていてVとHを間違えることもしばしば。

そんで、データの範囲と返す箇所の指定の仕方もちょいと違う。
XLOOKUPの場合は「検索はここ、返すのはここから」と指定できましたが、VLOOKUP, HLOOKUPの場合は「検索や返す場所が含まれる範囲はここ、返すのは何列目（何行目）」という指定の仕方になります。

さっき見たこの例だと、赤も青も含めた「A2:B6」をデータがある範囲として指定し、返すのはその範囲の2列目ということで「2」と指定します。

これの何が面倒くさいって、データがある範囲の端っこを検索する箇所にしておかないと、数式が動かないんですよ。
これが例えばA列に売上高、B列に支店名が入っているとして、同じように支店名から検索したいと思ってもVLOOKUPのデータを探す箇所は「指定した範囲の一番左っ側」というルールがあるのでそのままではVLOOKUPが使えない。
そういうときはどうしていたかというと、左の方に列を増やして、データを探す用の場所を作成し、それを含んで範囲指定するというひと手間がかかっていました。

…なんかよくわかんないって？
いいですよ、VLOOKUPやHLOOKUPを使う機会は、今はないもん。

そして厄介なのが列番号や行番号の指定。
データが少なくてすぐに「2列目」とわかるような場合はいいけど、数十列とかあるといちいちカウントしてられないし、列が増減すると数も変わってくるしで、列番号や行番号を探す関数もかませるのがセオリーでした。
これはこれで使うので、また別の機会に。

VLOOKUP, HLOOKUPで更に面倒くさいのは、検索方法も指定してやらにゃいかんということ。
近似値検索をするのか、完全一致のみを検索するのかを指定しないと、思うようなデータが返ってきません。

…なんかよくわかんないって？
いいですよ、VLOOKUPやHLOOKUPを使う機会は、今はないもん。

面倒な割には頻出のVLOOKUPやHLOOKUPがXLOOKUP関数に置き換えられるようになって、マジで楽になりました！
Microsoftもたまにはやるじゃん！

おわりに

• データを探したいときはXLOOKUP関数
• VLOOKUPやHLOOKUPは使えなくても構わんよ

XLOOKUP関数はかなり使うので、縦方向でも横方向でも自由自在に操れるようになってください。