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

Excelの初心者向け入門解説
公開日:2020-06-02 最終更新日:2022-10-25

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セルだけ参照していた指定を、
複数セルの範囲指定にすることで複数セルの処理が一括でできるようになり、指定した範囲と同じ大きさの範囲に出力されるようになりました。

スピルとは、数式を入れたセルから出力結果がこぼれだし隣接するセルに結果が出力される機能です。
スピルのより詳しい説明は以下を参照してください。
スピルについて
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事

XLOOKUP関数の基本

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

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

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

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関数では、
・VLOOKUP関数の書式 ・VLOOKUP関数:近似一致(検索方法=TRUE)の使用例 ・VLOOKUP関数:完全一致(検索方法=FALSE)の使用例
最後の4番目の引数を省略してしまうと、近似値(検索値未満の最大値)となり意図した結果になりません。
この間違いが極めて多いです。

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

検索列より左の列を返す

VLOOKUPでは検索列より左の列の値を取得する事はできません。
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
・VLOOKUP関数 ・キー列より左側の列を取得したい ・MATCH関数 ・INDEX関数 ・OFFSET関数 ・MATCH関数とINDEX関数を使う ・MATCH関数とOFFSET関数を使う ・キー列より左側の列を取得のまとめ ・配列を使い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関数の書式 ・従来の関数の代わりとして ・XLOOKUP関数をスピルさせる ・見つからない場合 ・一致モードの使い方 ・検索モードの使い方 ・XLOOKUP関数をネストして戻り列を可変にする ・XLOOKUP関数の戻りセル範囲を別の関数で使う ・XLOOKUP関数が縦横に同時にスピルしないことについて ・スピルと新関数の練習 ・XLOOKUP関数のVBA使用例 ・スピルによって新しく追加された関数

スピルと新関数の練習

スピルとは:スピル基礎から応用までの問題集 ・・・ 解答は別ページになっています。
・目次 ・スピルって何だ編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関数(条件付き最大値最小値)
IFS関数(複数条件)
SWITCH関数(複数値での切替)
XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(VLOOKUP関数を拡張した新関数)


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

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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