エクセル入門
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)

Excelの初心者向け入門解説
公開日:2022-08-27 最終更新日:2022-09-04

縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)


LAMBDA以降の新関数を使う事を前提にしたエクセル問題をツイッターで出題しました。
そのまとめ第一弾は以下になります。
LAMBDA以降の新関数の使用例

・2つの1次元配列から、それをクロス結合した結果を返す ・A列が同じ行のB列の値を連結して、A列の一意な値とともに出力 ・名前定義を使わずにLAMBDA関数で再帰する方法 ・文字列を大文字小文字変換して大小文字の全組み合わせを出力 ・2つのテーブルの片方しかない行を縦に連結出力 ・3連単、3連複のフォーメーション買目を全て列挙する ・FILTER関数の出力行数・列数を指定する ・月が縦で日が横の表を、曜日(7列)で縦に折り返す ・文字列内の括弧()()の中の文字を取り出して列挙 ・表内にあるセル内改行を複数行に展開 ・文字列の中から1文字削除の全パターン出力 ・クロスABC分析 ・月利が毎月変動する月複利の計算 ・レーベンシュタイン距離 ・文字列を数字と数字以外で分割 ・縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS) ・直積(クロス結合、…

本ページは、一連の新関数を意識したエクセル問題としては最後にするつもりで出した問題のまとめです。


出題のツイート

出題ツイートへのリンク

【エクセル問題】※これは覚えてほしいな編
みんな期待のXLOOKUPは縦横同時にスピルさせられません。
そこで問題です。
2枚目のように縦横スピルする数式を作成してください。
※データは見た通り単純な検索です。

エクセル Excel サンプル画像

エクセル Excel サンプル画像


他ツイートで新関数の問題は終了と言ったので特に書きませんでしたが。
これは普通の問題です。
新関数を使っても良いですけど、、、


問題としては普通の問題のつもりで出したので、まずはどんな方法でも良いのでやってみましょう。
まあ定番の方法がありますよね。
とはいえ、最新の関数を使っても良いのですよ。
ここ数日の他の問題の数式から使えそうな関数を探して使ってみるのも良いと思います。


解答ツイート

解答ツイートへのリンク

以下はツイートした内容の転載になります。


INDEX+MATCH

この事例なら、まさにINDEX+MATCHの出番ですね。
XLOOKUPと同じように戻り範囲をそのまま取得します。
=INDEX(B:D,MATCH(F2:F4,A:A,0),SEQUENCE(1,COLUMNS(B:D)))

項目名も検索するようにしておくと列移動が可能になります。
=INDEX(B2:D16,MATCH(F2:F4,A2:A16,0),MATCH(K1:M1,B1:D1,0))

Excel エクセル問題 XLOOKUPは縦横にスピルしない

Excel エクセル問題 XLOOKUPは縦横にスピルしない


後日追記
最初の数式、
=INDEX(B:D,MATCH(F2:F4,A:A,0),SEQUENCE(1,COLUMNS(B:D)))
説明不足で理解してもらえていないかもしれないですね。
というか、あまり読まれてもいないのかwww
XLOOKUPの書き換えなら、
=INDEX(B:D,MATCH(F2,A:A,0),0)
これで良い事になります。
この違いを説明しなかった・・・

Excel エクセル問題 XLOOKUPは縦横にスピルしない


MATCHの検索値を縦の複数行にして、
=INDEX(B:D,MATCH(F2:F4,A:A,0),0)
このように指定した場合は、縦にスピルしません。
これはXLOOKUPが縦横同時スピルしない事と同じです。
そこで前記の数式ではSEQUENCEを使い横配列で取得するようにしています。

Excel エクセル問題 XLOOKUPは縦横にスピルしない

誤:縦にスピルしません。

正:縦にしかスピルしません。


FILTER+MATCH

表から抽出する場合、FILTER関数を思い浮かべる人も多いと思います。
=FILTER(B:D,NOT(ISNA(XMATCH(A:A,F2:F4))))
NOT(ISNA(は、ISNUMBER( でも良いです。
FILTER関数では、元表と同じ行数のTRUE/FALSEの配列が必要になります。
そうなると元表のA列の方からF列を検索することになってしまいます。

Excel エクセル問題 XLOOKUPは縦横にスピルしない


データ件数が多いとかなり重くなりそうです。
そこで前記の数式は行数の範囲を限定した数式にしました。
(すみません、画像とツイートの数式が違ってしまいました。)
また、A列にない名前をF列に入れるとFILTERで抜け落ちるのでその行分が詰まってしまいます。
あまり使い勝手が良くないです。

Excel エクセル問題 XLOOKUPは縦横にスピルしない


CHOOSEROWS+MATCH

最新関数から使えそうなものは…
CHOOSEROWS関数が使えそうです。
抽出する行番号を配列で指定します。
=CHOOSEROWS(B:D,XMATCH(F2:F4,A:A))
データ件数を増やしてランダム順で取得してみましたがCHOOSEROWSはとても速いようです。
時間がかかるのはMATCHの方で、これは仕方ないですね。

Excel エクセル問題 XLOOKUPは縦横にスピルしない


では検索値が無い場合はどうなるでしょうか。
数式全体がエラーになってしまいます。
これは都合が悪いですね。
そこでMATCHをIFERRORでくるみます。
でも、エラーの時に何を入れたら良いのか…
row_numなので数値しか入れられません。
こんな時はとりあえず 0 かなとw

Excel エクセル問題 XLOOKUPは縦横にスピルしない


=CHOOSEROWS(A:D,IFERROR(XMATCH(F2:F4,A:A),0))
これは上手くいきました、検索値が無い行を抜かしてきちんと取得されました。
(分かり易いように名前も取得しました)
しかしFILTER同様に行が詰まるのは、場合によっては都合が悪い場合も出てきそうです。

Excel エクセル問題 XLOOKUPは縦横にスピルしない


検索値が無い行を何かで埋める方法はないものでしょうか…
MATCHのエラー時の 0 を -1 にしてみたらどうなるか…
=CHOOSEROWS(A:D,IFERROR(XMATCH(F2:F4,A:A),-1))
0が出てきました、なんとなく上手く行っちゃいましたね。
でも、この 0 はどこからでてきたのでしょう…

Excel エクセル問題 XLOOKUPは縦横にスピルしない


そこで、行全体指定ではなく範囲を絞って指定してみます。
はい、想像通りに(笑) -1 は最終行でした。
-2,-3と下から上に取得できるようです。
以下のようになっているようです。
1 ~ 最大行数:上からの行位置
-1~ -最大行数:下からの行位置
0 もしくは 最大行数超:その行は無視される

Excel エクセル問題 XLOOKUPは縦横にスピルしない


この仕様は「Officeのサポート」には事例の最後にマイナス値の事が出てきているだけでした。
0 および 最大数超 の場合は見当たりませんでした…
しかし、これは完全に意図して作成された仕様と思えるので、たぶん今後もこの仕様のままでいくだろうと思います。


後日追加

CHOOSEROWSおよびCHOOSECOLSでは、
row_numまたはcol_numの最後の要素に、0 または最大数を超える値を指定した場合は「#VALUE!」になってしまいます。
上記の例のように途中の 0 は無視して詰められますが、最後が 0 の場合はエラーとなります。
先頭の要素では問題ありませんので、なぜ最後だけエラーになるのかは謎仕様です。


無理矢理XLOOKUPを使ってみたら…

最後に、XLOOKUPを無理やり使ってゴニョゴニョしてみました。
=LET(
f,F2:F4,
s,A:A,
r,B:D,
c,LAMBDA(x,y,CHOOSECOLS(TEXTSPLIT(x,","),y)*1),
m,MAKEARRAY(ROWS(f),COLUMNS(r),LAMBDA(x,y,x&","&y)),
MAP(m,LAMBDA(m,XLOOKUP(CHOOSEROWS(f,c(m,1)),s,CHOOSECOLS(r,c(m,2)))))
)

Excel エクセル問題 XLOOKUPは縦横にスピルしない


これは何のためにXLOOKUPを使っているのか意味が分からないですね、失敗です。
素直にINDEX+MATCHを使えよという事例でしたw
何と言ってもINDEX+MATCHの使い勝手が良くて優秀で、それと比べてしまうと…
まあXLOOKUPは少ない引数で簡単に使えるというのが良さですね。


総評

・INDEX+MATCHは融通が利いて万能感がある。
・FILTERは少ない条件で表を絞り込むなら。
・CHOOSEROWSは別途行番号リストを取得して表の絞り込みには。(SQLならIN句での行番号絞り込み)
使い分けできれば良いですが、まずはINDEX+MATCHをしっかり覚えて使えるようになることをお勧めします。


ワークシート関数一覧

・文字列関数 ・日付と時刻の関数 ・情報関数 ・論理関数 ・検索/行列関数 ・数学/三角関数 ・統計関数 ・財務関数 ・エンジニアリング関数 ・データベース関数 ・アドイン/オートメーション ・キューブ ・Microsoft Office のサポート
ワークシート関数の一覧(2010以降)
・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021より後の365で追加されたワークシート関数 ・Microsoft 365 Insider ・Microsoft「Office のサポート」



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

TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)

TEXTBEFORE関数は、テキスト文字列から指定した区切り文字列の前に出現するテキストを返します。区切り文字列の前ではなく後ろを取得する場合はTEXTAFTER関数を使用します。TEXTBEFORE関数の構文 =TEXTBEFORE(text,delimiter,[instance_num],[match_mod…
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)
TEXTAFTER関数は、テキスト文字列から指定した区切り文字列の後ろに出現するテキストを返します。区切り文字列の後ろではなく前を取得する場合はTEXTBEFORE関数を使用します。TEXTAFTER関数の構文 =TEXTAFTER(text,delimiter,[instance_num],[match_mode…
LAMBDA以降の新関数の使用例
・2つの1次元配列から、それをクロス結合した結果を返す ・A列が同じ行のB列の値を連結して、A列の一意な値とともに出力 ・名前定義を使わずにLAMBDA関数で再帰する方法 ・文字列を大文字小文字変換して大小文字の全組み合わせを出力 ・2つのテーブルの片方しかない行を縦に連結出力 ・3連単、3連複のフォーメーション買目を全て列挙する ・FILTER関数の出力行数・列数を指定する ・月が縦で日が横の表を、曜日(7列)で縦に折り返す ・文字列内の括弧()()の中の文字を取り出して列挙 ・表内にあるセル内改行を複数行に展開 ・文字列の中から1文字削除の全パターン出力 ・クロスABC分析 ・月利が毎月変動する月複利の計算 ・レーベンシュタイン距離 ・文字列を数字と数字以外で分割 ・縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS) ・直積(クロス結合、…
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)
直積(クロス結合、交差結合)とピボット解除
LAMBDA以降の新関数の練習問題をツイッターで出題しています。今回は、直積(クロス結合、交差結合)の作成とピボット解除についての2問です。LAMBDA以降の新関数について LAMBDA以降の新関数の使用例・2つの1次元配列から、それをクロス結合した結果を返す・A列が同じ行のB列の値を連結して、A列の一意な値ととも…
複数の文字列を検索して置換するSUBSTITUTE
・SUBSTITUTEのネスト ・TEXTSPLIT+CONCAT(またはTEXTJOIN) ・REDUCE+SUBSTITUTE ・複数の「検索文字列」「置換文字列」の実用例
LAMBDA以降の新関数の問題集
・目次 ・LAMBDA踊るぞ編 ・LAMBDA踊るぞ編2 ・SCAN編 ・MAP編 ・REDUCE編 ・山手線営業の旅編 ・BYROW,BYCOL編 ・ここからはフリー問題だよ編 ・好きな関数を使ってくれ編 ・いろんな関数を使ってね編 ・魔球編 ・豚が木に登れば落ちることもある編 ・たまには100点とってみたいものだ編 ・これは何と言う処理なんだろ編 ・数字は嫌いなのだ編 ・外堀から埋めていくぞ編 ・なぜわざわざそうしたいのか編 ・かっこつけてんじゃないよ編 ・頭の体操だけど新関数の出番はあるか?編 ・定番だけど出してなかったよね編
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)
・LAMBDA踊るぞ編 ・LAMBDA踊るぞ編2 ・秋華賞ガミったぞ記念編 ・MAP編 ・REDUCE編 ・山手線営業の旅編 ・BYROW,BYCOL編
LAMBDA以降の新関数の問題と解説(配列操作関数編)
・ここからはフリー問題だよ編 ・好きな関数を使ってくれ編 ・いろんな関数を使ってね編 ・魔球編 ・豚が木に登れば落ちることもある編 ・たまには100点とってみたいものだ編 ・これは何と言う処理なんだろ編 ・数字は嫌いなのだ編 ・外堀から埋めていくぞ編 ・なぜわざわざそうしたいのか編 ・かっこつけてんじゃないよ編 ・頭の体操だけど新関数の出番はあるか?編 ・定番だけど出してなかったよね編
PY関数(Pythonコードをセル内で実行)
・PY関数の構文 ・PY関数の使い方 ・「Excel Labs」アドイン ・Python サンプルを試す ・pandas で説明 ・散布図を作成する ・Pythonの文法 ・PY関数で使えるライブラリ
GROUPBY関数(縦軸でグループ化して集計)
GROUPBY関数は、行(縦)でグループ化し指定された関数によって値を集計します。行(縦)の軸に沿ったグループ化と、関連する値の集計がサポートされます。複数の行グループレベルに対応しています。総計・小計、並べ替え、フィルター処理もサポートされています。


新着記事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」をお願いいたします。
本文下部へ