エクセル入門
XLOOKUP関数とスピル入門

Excelの初心者向け入門解説
最終更新日:2020-08-26

XLOOKUP関数とスピル入門


2019年Office365に追加された革新的な機能としてスピルがあります。
また、少し難しく時々間違った指定をしてしまいがちだったVLOOKUP関数が、
より強力で簡単で便利な新関数としてXLOOKUP関数が登場しました。


スピルの概要とXLOOKUP関数の基本について説明します。

スピルとは

スピル(spill)とは、こぼれる、あふれる、と言うような意味です。
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。

スピルは数式の結果の複数の値が隣接するセルに自動的に出力されます。
複数の値を返す配列数式を該当セル範囲の先頭(左上セル)に入力すると、結果の複数の値が入力したセルからこぼれ出して隣接するセルに出力されます。

スピルを体験しよう

A1セルから下に1,2,3と入れておきます。
B1セルに、
=A1:A3
このようにいれてEnter

エクセル Excel スピル

B1セルにしか数式を入れていないのに、B2とB3セルにも表示されているのが分かります。
これは、
=A1:A3
この数式の結果が、その参照している大きさのまま出力された結果です。

スピルしない以前であれば、先頭のセル値しか出力されませんでしたので、
B1セルに、=A1と入れ、これをドラッグコピーして、B2,B3に入れる必要がありました。
これに比べて、直感的で入力がとても簡単になりました。


今度は、
A2セルから下に1,2,3と入れ、B1セルから右に1,2,3と入れておきます。
B2セルに、
=A2:A4+B1:D1
このようにいれてEnter

エクセル Excel スピル

スピルしない以前であれば、B2セルに=A2+B1と入れ、これをコピーしていたものです。
入れる数式は計算式だけではなく、文字列結合等いろいろな数式を入れることができます。

エクセル Excel スピル

スピルしない以前には1セルだけ参照していた指定を、
複数セルの範囲指定にすることで複数セルの処理が一括でできるようになり、指定した範囲と同じ大きさの範囲に出力されるようになりました。

スピルとは、数式を入れたセルから出力結果がこぼれだし隣接するセルに結果が出力される機能です。
スピルのより詳しい説明は以下を参照してください。
スピルについて
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。

XLOOKUP関数の基本

XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の両方の機能を備え、さらに新しい機能まで追加された新関数です。

VLOOKUP関数やHLOOKUP関数の引数は4個でしたが、XLOOKUP関数の引数は6個あります。
引数が多いので難しく見えてしまいますが、単純な使い方の場合は3個目以降の引数は省略できるので、かえってVLOOKUP関数より簡単に使えます。

より詳しいXLOOKUP関数の使い方については、以下を参照してください。
XLOOKUP関数(範囲を検索し一致する対応項目を返す)
XLOOKUP関数は、範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。VLOOKUP関数とHLOOKUP関数の両方の機能を備え、さらに新しい機能まで追加された新関数です。VLOOKUP関数、HLOOKUP関数の引数は4個でしたが、XLOOKUP関数の引数はなんと6個あります。

XLOOKUP関数の書式

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])

検索値
検索する値を指定します。
検索範囲
検索する範囲または配列を指定します。
戻り範囲
見つかった場合に戻す範囲または配列を指定します。
見つからない場合
省略可能。
検索値が検索範囲で見つからなかった場合に元す値を指定します。
一致モード
省略可能。
どのような状態を一致とするかを指定します。
検索モード
省略可能。
検索の向きを指定します。


VLOOKUP関数の代わり

エクセル Excel XLOOKUP スピル
※なんちゃって個人情報です。

G2セルの名前でB列を検索し、対応する年齢をH2セルに出力します。

VLOOKUP関数
=VLOOKUP(G2,B:E,3,FALSE)
FALSEは0でも良い。
XLOOKUP関数
=XLOOKUP(G2,B:B,D:D)
XLOOKUPでも列指定できます。

VLOOKUP関数では、
検索値でセル範囲の最初の列を検索し、その範囲の同じ行にある任意のセルから値を返します。Vは縦方向(vertical)の意味です。つまり縦方向の表に対して、検索値を縦に探す場合に使用します。VLOOKUP関数の書式 =VLOOKUP(検索値,範囲,列番号,検索方法) 検索値 表または範囲の左端の列で検索する値を指定します。
最後の4番目の引数を省略してしまうと、近似値(検索値未満の最大値)となり意図した結果になりません。
この間違いが極めて多いです。

XLOOKUP関数は3つの引数で済むので、単純な使い方の場合はVLOOKUP関数より簡単になりました。

検索列より左の列を返す

VLOOKUPでは検索列より左の列の値を取得する事はできません。
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
エクセルの数ある関数の中でも頻繁に使われるVLOOKUP関数は非常に便利な関数ですが、キー列(検索値を探す列)より左側にある列を取得できません。これは仕様で仕方ないのですが時に不便な場合があります。キー列より左側の列を取得したい場合には、VLOOKUP関数ではなく他の関数を使って実現します。

検索列より左の列の値も、XLOOKUP関数では簡単に取得できます。

エクセル Excel XLOOKUP スピル

G2セルの名前でB列を検索して、対応するA列の都道府県を出力します。
VLOOKUP関数は、検索列より左の列を返せなかったので、INDEX+MATCH等で対応する必要がありましたが、XLOOKUP関数は戻り列を指定できるので、検索列より左の列も取得できます。

INDEX関数+MATCH関数
=INDEX(A:A,MATCH(G2,B:B,0))
OFFSET関数+MATCH関数
=OFFSET(A1,MATCH(G2,B:B,0)-1,0)
XLOOKUP関数
=XLOOKUP(G2,B:B,A:A)

XLOOKUP関数を横にスピルさせる

エクセル Excel XLOOKUP スピル
※XLOOKUP関数の戻り(スピル範囲)は書式設定されないので日付はシリアル値で表示されています。

=XLOOKUP(G2,B:B,C:E)

3番目の引数は戻り範囲です。
この戻り範囲に複数列を指定することで、対応する複数列をスピルでいっぺんに出力できます。

XLOOKUP関数を縦にスピルさせる

エクセル Excel XLOOKUP スピル

=XLOOKUP(G2:G4,B:B,C:C)

1番目の引数は検査値です。
この検索値に複数行を指定することで、対応する複数行をスピルでいっぺんに出力できます。

XLOOKUP関数を縦横同時にスピルさせることはできません

XLOOKUP関数の縦横スピルについての詳細は以下をお読みください。
XLOOKUP関数(範囲を検索し一致する対応項目を返す)
XLOOKUP関数は、範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。VLOOKUP関数とHLOOKUP関数の両方の機能を備え、さらに新しい機能まで追加された新関数です。VLOOKUP関数、HLOOKUP関数の引数は4個でしたが、XLOOKUP関数の引数はなんと6個あります。

スピルと新関数の練習

スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)
2019年Office365に追加された革新的な機能としてスピルがあります。スピルとともに強力な新関数が登場しました。その中でも特に使用頻度が高いと思われるXLOOKUP関数の練習をかねて、問題を作成しました。




同じテーマ「エクセル入門」の記事

MAXIFS関数、MINIFS関数(条件付き最大値最小値)
IFS関数(複数条件)
SWITCH関数(複数値での切替)
XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)


新着記事NEW ・・・新着記事一覧を見る

タプル(tuple型、イミュータブル)|Python入門(9月15日)
リスト(list型、配列)|Python入門(9月14日)
文字列操作(str型)|Python入門(9月13日)
greeenはgreenに、greeeeeNをGReeeeNに変換|エクセル雑感(9月13日)
while文とデバッグ(ステップイン)|Python入門(9月12日)
入力規則で○△を入れる数を制限する方法|エクセル雑感(9月12日)
コレクションの要素を削除する場合|エクセル雑感(9月11日)
for文とイテラブルオブジェクト|Python入門(9月10日)
if文とインデントによるブロック|Python入門(9月9日)
ショートカット(Ctrl+Shift+n)抜け番ばどれだ|エクセル雑感(9月8日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ