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

新しく、便利な関数XLOOKUP。
この関数とスピルという機能の組み合わせでさらに便利なこともできます。

※新幹線内にて　by　Leica M10（SUMMILUX 50mm F1.4）

 

XLOOKUP関数とは

 

XLOOKUP（エックスルックアップ）関数は、これまであったVLOOKUP関数（HLOOKUP関数、LOOKUP関数）をより使いやすくした、新しい関数です。

私はいち早く使っていましたが、2019年2月現在、製品版のExcel（Office2019、Offcie365）を最新版にすると使えるようになりました。

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

 

ただ、更新しても使えない場合もあります。
更新のタイミングもあるので。
近いうちに使えるようになるかと思います。

 

Excelに　＝x　と入力して、次のように候補としてXLOOKUPが表示されれば使えるということです。

 

出てこない方は、アカウント（Alt→F→D。ALT、F、Dを1つずつ押す）で、［今すぐ更新］をしてみていただければ。

 

この記事を書いている時点での私のExcelのバージョンは、2001（ビルド12430.20184）です。

 

XLOOOKUPを使っていて、まだ使えないExcelで開くと、数式はこうなり、編集はできませんが、データの表示はできます。

 

XLOOKUP関数の使い方

 

たとえば、こういったデータ（シート「推移表」）があり、

 

売上高のみを別のシートに表示するには、

1　= でセルを指定する
2　コピーして貼り付ける
3　見て入力する

という方法があります。

効率よくミスなくやるには、1の方法でやりたいところですが、このセルの位置がいつもこことは限りませんし、ほかの項目を表示したいときには、また同じことを繰り返さなければいけません。

「売上高の数字を探して、入れといてよ」と伝えたいところです。
この「探して入れといて」が　なんたらLOOKUP関数であり、VLOOKUPはVertical（バーティカル、垂直）にLOOKUP（探す）ということを意味します。

 

入れるのは、

=VLOOKUP(A2,推移表!A:B,2,FALSE)

です。

「セルA2（売上高）をシート「推移表」のA列からB列の範囲の一番左（A列）から探して、その範囲の2列目を表示して。ただし、完全一致するものを」

ということを伝えています。

 

FALSEは、完全一致という意味で、近似一致（だいたい一致）ならTRUEです。

 

VLOOKUP関数は、便利なのですが、難易度は少々高いのがたまに傷でした。

 

XLOOKUP関数ができたことで、その難易度はぐっと探り、より便利になったのです。
上記の事例で、XLOOOKUP関数を使うと、こうなります。

 

「セルA2（売上高）をシート「推移表」のA列を探して、完全一致したらB列を表示して」

という意味です。
VLOOKUPよりもシンプルになりました。

=XLOOKUP(A2,推移表!A:A,推移表!B:B)

今後は、XLOOOKUPを使っていきましょう。

XLOOKUP関数とスピル

 

Excelの新しい機能スピルも、XLOOKUPと同様に、まだ使えない場合があります。

まずは使わない場合で、事例を考えてみましょう。

 

4月から6月のデータをXLOOKUPで表示したいとします。

数式をコピーすれば、5月、6月も表示できるのですが、A2（売上高）とA列（シート「推移表」の科目）は固定したいので、数式を変数できるようにして（F2キー）、F4キー押して次のように＄をつけましょう。
これで数式をコピーしてもA2とA列を固定できます。
（絶対参照というものです）

 

コピーすれば、このように4月から6月（ついでに経常利益も）が表示できます。

 

データを加工するよりも楽ですし、別のソフトからダウンロードしたデータを、今回のシート「推移表」のように貼り付ければ、自動的に数字が入るわけです。

 

そして、XLOOKUP関数の間違いがなければ、表示する数字は100％間違えません。
人が入力すると、間違える可能性があります。

 

スピルの考え方・使い方

こういったときにスピルが使えるのです。

=XLOOKUP(A2,推移表!A:A,推移表!B:B)

で、「B列から探す」としているところを、BからD列に変えます。
=XLOOKUP(A2,推移表!A:A,推移表!B:D)

 

すると、次のように、5月（セルC2）も6月（セルC3）にも数式が入り、数値が表示されるのです。

 

 

スピル（SPILL）とは、こぼれる、あふれるという意味。
1つのセルに入れた数式があふれるように、ほかのセルに入るという機能です。

 

 

 

XLOOKUPでエラーが出た場合の解決方法

 

XLOOKUPでは、エラーが出ることがあります。

 

#N/A

not applicable（該当なし）、not available（利用できない）の意味です。
次の例では、セルA2に「売上」とあり、該当するデータがないので、エラーになっています。

 

「売上高」と「売上」はもちろん、
「売上高」と「売上高　」（スペースが入っている）もダメです。

完全一致とみなしません。
「打ち合わせ」と「打合せ」もダメです。

VLOOKUPのときは、「Excel」と「ＥＸＣＥＬ」、「エクセル」と「ｴｸｾﾙ」はダメだったのですが、XLOOKUPでは一致とみなすようになりました。

便利なようですが、これに頼るのもどうかな……と。

 

 

#NAME？

このエラーは、「関数名、間違ってない？大丈夫？」というものですので、XLOOKUPのスペルを確認しましょう。

 

#VALUE!

 

XLOOKUPは、
=XLOOKUP(A2,推移表!A:A,推移表!B:B)
でも、

=XLOOKUP(A2,推移表!A1:A200,推移表!B1:B200)

でも同じ結果です。
列で指定したほうが（A:Aのように）、ミスの可能性も減りますし、楽なので、私は列指定を使っています。

 

この範囲を指定するとき、探す範囲は、A1からA200で、表示する範囲がB1からB300と、範囲の行数が違っていると、#VALUE!というエラーが出ます。

=XLOOKUP(A2,推移表!A1:A200,推移表!B1:B300)

 

#SPILL!

 

このエラーは、スピルに関するものです。
たとえば、D列まで結果を表示しようとしているのに、セルD2に何か入っていると、エラーがでますが、セルD2をクリア（delete）すれば正しく表示されます。

 

また、スピルの範囲に、何か入力すると、

 

#SPILL!と、エラーが出てきます。

スピル機能ではここに注意しなければいけません。

 

スピルは、自動的に表示されるが故の注意点もあり、実際に使うときには、スピルを使わないほうがいい場面もあります。

 

まずは、XLOOKUPから使ってみましょう。

 

 

 

---

■編集後記

昨日は、新宿の眼鏡市場へ。
寝ていてレンズが外れたので……。
レンズ、はずれやすいのですが、今回はひさしぶりでした。
2.8gの超軽量めがね ZEROGRA（ゼログラ）使用レポート。軽い・レンズ込みで8.4g・たためない | EX-IT

視力も若干落ちていたのでレンズも交換。
合間にキンコーズで大阪でのセミナー資料のプリントアウト。
資料は使わないことが多いのですが、今回は必要なので。
大阪で当日やることもできましたが、プリントアウト苦手なので念のため……。

 

■昨日の1日1新
※詳細は→「1日1新」

ゼログラ（眼鏡）のレンズ交換
新宿西口キンコーズ

 

■昨日の娘日記

最近、フォカッチャがお気に入りで、買ってくると喜びます。
私が好きなフォカッチャは、ル パン・ドゥ ジョエル・ロブションのもの。
ちょっと高め（ハーフで270円）ですが、おいしいです。
新宿のルミネで買っています。