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

公開日：2020-06-02　最終更新日：2022-10-25

XLOOKUP関数とスピル入門

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

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

スピルとは

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

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

スピルを体験しよう

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

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

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

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

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

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

スピルとは、数式を入れたセルから出力結果がこぼれだし隣接するセルに結果が出力される機能です。
スピルのより詳しい説明は以下を参照してください。

スピルについて

・スピルとは・スピルの数式例・ゴースト・スピル範囲での独特な挙動について・スピルのエラー表示・スピル範囲演算子・暗黙的なインターセクション演算子・従来のスピルしないエクセルとの互換性についての注意点・スピル関連記事

XLOOKUP関数の基本

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

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

より詳しいXLOOKUP関数の使い方については、以下を参照してください。

XLOOKUP関数（範囲を検索し一致する対応項目を返す）

・XLOOKUP関数の書式・従来の関数の代わりとして・XLOOKUP関数をスピルさせる・見つからない場合・一致モードの使い方・検索モードの使い方・XLOOKUP関数をネストして戻り列を可変にする・XLOOKUP関数の戻りセル範囲を別の関数で使う・XLOOKUP関数が縦横に同時にスピルしないことについて・スピルと新関数の練習・XLOOKUP関数のVBA使用例・スピルによって新しく追加された関数

XLOOKUP関数の書式

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

検索値

検索する値を指定します。

検索範囲

検索する範囲または配列を指定します。

戻り範囲

見つかった場合に戻す範囲または配列を指定します。

見つからない場合

省略可能。
検索値が検索範囲で見つからなかった場合に元す値を指定します。

一致モード

省略可能。
どのような状態を一致とするかを指定します。

検索モード

省略可能。
検索の向きを指定します。

VLOOKUP関数の代わり

※なんちゃって個人情報です。

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

VLOOKUP関数

=VLOOKUP(G2,B:E,3,FALSE)
FALSEは0でも良い。

XLOOKUP関数

=XLOOKUP(G2,B:B,D:D)
XLOOKUPでも列指定できます。

VLOOKUP関数では、

・VLOOKUP関数の書式・VLOOKUP関数：近似一致（検索方法=TRUE）の使用例・VLOOKUP関数：完全一致（検索方法=FALSE）の使用例

最後の4番目の引数を省略してしまうと、近似値（検索値未満の最大値）となり意図した結果になりません。
この間違いが極めて多いです。

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

検索列より左の列を返す

VLOOKUPでは検索列より左の列の値を取得する事はできません。

VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)

・VLOOKUP関数・キー列より左側の列を取得したい・MATCH関数・INDEX関数・OFFSET関数・MATCH関数とINDEX関数を使う・MATCH関数とOFFSET関数を使う・キー列より左側の列を取得のまとめ・配列を使いVLOOKUPでキー列より左側の列を取得

検索列より左の列の値も、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関数を横にスピルさせる

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

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

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

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

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

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

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

XLOOKUP関数の縦横スピルについての詳細は以下をお読みください。

XLOOKUP関数（範囲を検索し一致する対応項目を返す）

スピルと新関数の練習

スピルとは：スピル基礎から応用までの問題集 ・・・解答は別ページになっています。

・目次・スピルって何だ編1 ・スピルって何だ編2 ・スピルって何だ編3 ・スピルって何だ編4 ・スピルって何だ編5 ・スピルって何だ編6 ・ハイレツテイスウっておいしいの編1 ・ハイレツテイスウっておいしいの編2 ・イフでウフフ編1 ・イフでウフフ編2 ・イフでウフフ編3 ・かー‼うんと頑張ってみよう編・サムはイフと恋をした編・君はヤマダなのかい編・毎月月末は彼女とデートなんだよ編・SEQUENCE編1 ・SEQUENCE編2 ・FILTER編1 ・FILTER編2 ・SORTBY編1 ・SORTBY編2 ・UNIQUE（そろそろLETも）編・XLOOKUP編1 ・XLOOKUP編2 ・ここまでの総合演習1 ・ここまでの総合演習2 ・ここまでの総合演習3 ・ここまでの総合演習4 ・ここまでの総合演習5 ・ここまでの総合演習6

スピルと新関数の練習（XLOOKUP関数、LET関数、VBAまで）

・スピルとXLOOKUP関数の練習問題・従来の関数+スピルで数式を作る・ XLOOKUP関数に書き換える・LET関数に書き換える・VBAでユーザー定義関数を作成・スピルと新関数の練習のまとめ

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

MAXIFS関数、MINIFS関数（条件付き最大値最小値）

・MAXIFS関数、MINIFS関数の書式・ワイルドカード・MAXIFS関数とMINIFS関数の使用例

IFS関数（複数条件）

IFS関数は、1つ以上の条件が満たされているかどうかをチェックして、最初のTRUE条件に対応する値を返します。複数のネストしたIFステートメントを置き換えることができるため、複数の条件指定ではより簡単に記述できます。IFS関数の書式 =IFS(論理式1,真の場合1,[論理式2,真の場合2]…) 論理式1 必須です。

SWITCH関数（複数値での切替）

SWITCH関数は、1つの値に対して値の一覧を評価し、最初に一致する値に対応する結果を返します。いずれにも一致しない場合は、任意指定の既定値が返されます。SWITCH関数の書式 SWITCH(式,値1,結果1,[既定値または値2,結果2],…[既定値または値126,結果126]) 式必須です。

XLOOKUP関数とスピル入門
スピルについて

FILTER関数（範囲をフィルター処理）

・FILTER関数の書式・FILTER関数使用例のサンプルデータ・FILTER関数の基本・空白セルを0ではなく空白にする場合・複数条件のフィルター・関数を使ってフィルター・横（列）でフィルター・表示する列を選択する・FILTER関数の結果を他の関数で使う・スピルによって新しく追加された関数

SORT関数、SORTBY関数（範囲を並べ替え）

・SORT関数の書式・SORTBY関数の書式・SORT関数、SORTBY関数と、ワークシートの並べ替えの違い・最も単純な並べ替え・複数キーでの並べ替え・列方向（横方向）で並べ替え・並べ替え範囲（配列）以外の基準で並べ替える・列全体を範囲指定する場合・スピルによって新しく追加された関数

UNIQUE関数（一意の値）

・UNIQUE関数の書式・ワークシートでの一意化（ユニーク化）・UNIQUE関数の使用例・UNIQUE関数の応用例・スピルによって新しく追加された関数

RANDARRAY関数（ランダム数値）

・RANDARRAY関数の書式・RANDARRAY関数の使用例・スピルによって新しく追加された関数

SEQUENCE関数（連続数値）

・SEQUENCE関数の書式・SEQUENCE関数の使用例・SEQUENCE関数を使ったエクセル問題・スピルによって新しく追加された関数

XLOOKUP関数（VLOOKUP関数を拡張した新関数）

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
エクセル全般
エクセル入門
XLOOKUP関数とスピル入門

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

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

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