見出し画像

超絶便利-Googleスプレッドシートの XLOOKUPやVLOOKUP関数を（割と）厳密な一致判定に切り替える*魔法* 2

mir

「Googleスプレッドシートは一致がゆるい」

なんかラノベのタイトルみたいですが、実際これで困ってる人も多いんじゃないでしょうか？

具体的にどういうことか？は本編で触れていきますが、今回はこの困ったGoogleスプレッドシートの一致判定をXLOOKU関数で「割と」厳密にする魔法を紹介します。

2024年5月に書いた シリーズ1回目では COUNTIF、COUNTIFS、SUMIF、SUMIFS といった ○○IF(S) 系関数を 割と厳密な一致判定に切り替える魔法を紹介しました。

今回シリーズの2回目なんですが、このタイトルの「2」もいい感じにタイトル伏線回収として使えるとはｗ

前回のnoteは Googleスプレッドシートの新機能「複数選択プルダウンリスト」の使い方や活用例について書きました。



XLOOKUPやVLOOKUPを 割と厳密な一致判定に変える*魔法*2 とは？

すぐ使える簡単ハックネタなんで、今回もいきなり方法（答え）を書いておきましょう。

通常はこうなる
祝！2期作成決定ってことで 2を入れてみる

おわかりいただけただろうか？

XLOOKUP関数で D2セルのキーワードに一致するものを A:A（A列）を上から検索して、最初に一致した行のB列（B:B）の数字 を返してるわけですが、

上のGIF画像の式

=XLOOKUP(D2,A:A,B:B,)

だと、「葬送のふりーれん」 や 「葬送のふりぃれん」、「葬送のﾌﾘｲﾚﾝ」、これら全てが 2行目の「葬送のフリーレン」と一致すると判定され、数字の1を返しています。

さすがに「葬送のフーリレン」は別モノと判定されて、 9を返しているので、XLOOKUPは正しく機能しています。

これは Googleスプレッドシートの

「ひらがな・カタカナ、全角・半角、伸ばし棒と母音」を全て区別しない

緩い一致判定の 困った仕様によって発生しています。

しかし下のGIF画像のようにXLOOKUPの式を

=XLOOKUP(D2,A:A,B:B,,2)

第5引数（一致モード）を 2 とする。

たったこれだけで XLOOKUP関数の 一致判定基準が（割と）厳密になり、

葬送のふりいれん　の検索結果は　3
葬送のふりぃれん　の検索結果は　4
葬送のﾌﾘｲﾚﾝ　　　  の検索結果は　7

と、正しい結果が得られるようになります。

まとめると、こんな感じ ↓

■Googleスプレッドシートの イコールによる一致判定は、ひらがな・カタカナ、全角・半角、伸ばし棒と母音、大文字・小文字を区別せず一致とみなす非常に緩い判定である

■XLOOKUP関数は、この緩い一致基準のせいで困ること（誤判定）がある

■【ここがポイント！】しかし XLOOUP関数の第5引数（一致モード）を 2（ワイルドカード使用）に指定することで、一致判定が割と厳密になる

後で触れますが アルファベットの大文字、小文字は区別できないので「割と」厳密な一致という言い方をしていますが、Googleスプレッドシートの困った仕様 「一致判定の緩さ」が、この簡単なテクニックで一気に解消されます。

ワイルドカードを使った検索を「あいまい検索」と表現することもありますが、「あいまい検索」モードにすることで逆に「厳密な検索」になるという・・・。

もう、わけがわかりませんねｗ

このXLOOKUPで使える  *魔法*2 について、もう少し詳しくみていきましょう！


Googleスプレッドシートと Excelにおける 一致基準

改めて Googleスプレッドシートの一致の緩さ、Excelとの違いに触れておきましょう。

Googleスプレッドシートは一致がゆるい

Googleスプレッドシートの緩い一致判定ですが、イコール一致の判定をまとめると

■ひらがなとカタカナは区別されない
ふりーれん = フリーレン　ヒンメル = ひんめる

■伸ばし棒と母音は区別されない
ふりいれん = ふりーれん　ゼーリエ = ぜえりえ

■ひらがな、カタカナの小さい文字は区別されない
ふりいれん = ふりぃれん　フェルン = ふえるん

■全角文字と半角文字は区別されない（例外あり）
FRIEREN =ＦＲＩＥＲＥＮ　アイゼン ＝ ｱｲｾﾞﾝ　デストロ２４６ = ﾃﾞｽﾄﾛ246

■全角文字の伸ばし棒と半角文字の伸ばし棒は区別される
フリーレン ≠ ﾌﾘｰﾚﾝ

■アルファベット（全角・半角どちらも）の大文字、小文字は区別されない
FRIEREN = frieren　FRIEREN =ｆｒｉｅｒｅｎ

このようになります。だいぶ緩いですよね。

「フェルンです」 と  「ふえるんです」 が一致って・・・困りますｗ

この一致に関する検証は、「厳密な一致判定に切り替える魔法」シリーズの1回目でじっくりやってます。詳しくはそちらをご覧ください。


Excelは Web版でもイコール一致が厳密

Excelは 無料のWeb版であっても、厳密に一致判定をしてくれます。

イコール一致で判定した場合、ひらがな、カタカナの区別は当然ですが、全角・半角、大きい文字「あいう」、小さい文字「ぁぃぅ」も区別します。

半角アルファベットの大文字・小文字は同一と見なしてしまいますが、これはインストール版Excelも同様で昔からの仕様ですね。

Googleスプレッドシートのように緩い一致による誤判定で困る発生といったことは、ほぼありません。


判定がイコール一致より緩い Excelの XLOOKUP関数、UNIQUE関数

しかし Excelも近年追加された新関数には、一致判定がイコールによる判定よりも緩いものがあり、誤判定で困るといったケースが発生することも。

その代表的な関数の1つが UNIQUE関数で

このように 文字の半角・全角が判別できず 「葬送のフリーレン」と「葬送のﾌﾘｰﾚﾝ」は一致とみなされ、上にある「葬送のフリーレン」だけが残る結果となっています。

また、半角・全角に加え大文字・小文字も同一とされる為、

FRIEREN
frieren
F ｒｉｅｒｅｎ
ｆｒｉｅｒｅｎ 

これらが全て一致と判定され 一番上の FRIEREN だけが残ります。


そしてもう一つ、最強検索関数として大人気の XLOOKUP関数も、実は一致判定が甘い関数なんです。

「葬送のﾌﾘｰﾚﾝ」で検索した場合、検索範囲 A2:A14 のより上にある 「葬送のフリーレン」がヒットして 1を返したり、

frieren
F ｒｉｅｒｅｎ
ｆｒｉｅｒｅｎ 

これらが より上にある FRIEREN と 一致すると判定され 10を返すといった事象が発生しています。


Googleスプレッドシートだと、なぜか 全角カタカナの伸ばし棒と 半角カタカナの伸ばし棒 だけは 区別するという謎仕様でしたが、ExcelのXLOOKUP関数・UNIQUE関数では 伸ばし棒も含めて 全角・半角を区別すせず一致扱いなんですね。

UNIQUE関数同様に Excel の XLOOKUPの 一致判定も 半角・全角と大文字・小文字が区別できない仕様になっているわけです。

もちろん イコールで比較したら 別モノと判定され FALSEを返しますし、

VLOOKUP兄さんは イコールでの一致判定と同じで 半角アルファベットの大文字、小文字は判別できませんが、それ以外はしっかり区別して一致判定をしています。

Excelの UNIQUE関数やXLOOKUP関数等の新関数だけの独特な仕様と言えます。

最強検索関数 XLOOKUPの意外な弱点ですね。

ちなみにExcelの XMATCH関数も同じく  全角文字・半角文字を同一と見なしてしまいます。

なお、GoogleスプレッドシートのXLOOKUP関数は VLOOKUP関数と同じイコール判定のようで、検索結果に差異はありません。（もともとがユルユルなんで気になりませんがｗ）


Googleスプレッドシートの一致判定を割と厳密にする魔法

このように Excelの場合も UNIQUE関数やXLOOKUP関数を使った際、少し緩い判定になることがありますが、やはり Excelに比べ圧倒的に困ることが多いのがGoogleスプレッドシートの緩い一致判定。

これを解消することが、日本でのGoogleスプレッドシートの活用促進になると考え、mirも微力ながら色々検証・発信してきたわけですｗ


XLOOKUP、VLOOKUPを 厳密にする魔法は条件付き書式の挙動にヒントがあった

COUNTIF関数やSUMIFS関数など、〇〇IF、〇〇IFS系関数 であれば、範囲に { } 中カッコをつけて配列化 することで、一致判定を割と厳密にすることが出来ます。

これが「厳密な一致判定に変える魔法」シリーズの1回目で紹介したハックネタ {魔法}です。

前回のnoteの時点では、XLOOKUPやVLOOKUP の判定を厳密にするいい方法が見つからなかったんですが、実はそれに繋がるヒントがありました。

それが、条件付き書式の判定を厳密にする方法です。

条件付き書式は「完全一致する」を条件にすると逆にゆるーい判定に

条件付き書式で用意された条件を使う場合、一致判定する時は通常は「完全一致するテキスト」を選ぶかと思いますが、これは ひらがな・カタカナや 半角・全角を区別しない緩い一致判定となってしまいます。

完全一致といいつつ不完全（ゆるゆる）

しかし「次を含むテキスト」を選択した場合は、なぜか ひらがな・カタカナ、半角・全角が区別される 厳密な判定となります。

ここでもう一歩踏みこんで

「LOOKUP系も、もしやこのロジックなのでは？」

と考えられれば良かったんですが、見落としていました～。反省。

一応  COUNTIFやSUMIFS関数で ワイルドカード使用は試したんですが、

COUNTIF や SUMIFS の緩い一致判定は ワイルドカードと組み合わせるとバグる

なぜかバグって 0を返す

COUNTIFやSUMIFSといった〇〇IF、IFS系関数では、ワイルド―カードでは厳密な一致ではなく バグが発生する謎仕様だったんで、そこで検証を終えてしまいました。


XOOKUP関数のワイルドカードモードで 厳密一致を検証する

Excelから輸入されて、Googleスプレッドシートでは 2022年9月から使えるようになった XLOOKUP関数。

こちらを紹介した 当時の紹介 note ↓ でも、

XLOOKUP では 「厳密な一致」での検索は出来ません。

と書いていますが、今回紹介のハックネタ  第5引数（一致モード）2指定 の魔法 を使うことで、「ひらがな、カタカナ、全角、半角、伸ばし棒と母音」を区別して一致判定（検索）できるようになります。

ポイントは、第5引数の一致モードを 2で ワイルドカードが利用できるモードにしているのに ワイルドカードを使用しないって点ですね。


数字の場合、数値としての1と半角文字列の1は 一致モード省略の通常の XLOOKUPでも区別します。

ただ、全角文字列の１と半角文字列の1は区別できません。

これを 一致モード2とすることで、全角文字列の１と半角文字列の1 も区別できるようになっているのがわかりますね。

ただし、この 魔法を使っても 半角アルファベットの大文字・小文字を区別した一致判定は出来ません。

一致モードを2とすることで、全角の 大文字・小文字 は区別して判定できるようになりますが、Excelと同じく半角アルファベットに関しては 大文字・小文字を区別せず一致となります。

COUNTIFやSUMIFSで使った 配列化の {魔法}と同じレベルの厳密化（一致判定）ってことですね。

その為、「割と」厳密な一致 と表現しています。


XLOOKUPの第6引数（検索モード）を -1 指定した場合はどうでしょうか？

検索モードを -1指定すると 範囲のお尻（縦方向の場合は下）から検索をします。

一致モード指定なしの通常のXLOOKUP利用の場合は、「葬送のふりィﾚん」という色々混在した文字が「葬送のフリーレン」と一致すると判定され、8が返ってるセルが多いです。

一方、一致モード 2指定とした方は 問題なく検索できていますね。

検索モード -1（逆方向からの検索）でも 一致判定の厳密化は 効果があるようです。


XMATCH関数でも 魔法による 厳密判定が使える

XLOOKUPと同じタイミングで追加された XMATCH関数も、第3引数が一致モードになっており、2でワイルドカード利用を指定できます。

ここで 一致モードを2で指定することで、XLOOKUPと同じように XMTACH関数でも 割と 厳密な一致判定に切り替えることが出来ます。

*魔法*2が効きましたね！


VLOOKUP、HLOOKUP、MATCH でも *魔法*は使えるのか？

では一致モード 2指定が出来ない、VLOOKUP関数やHLOOKUP関数、そしてMATCH関数 はどうでしょうか？

VLOOKUPやHLOOKUP、MATCH関数は 一致モードによるワイルカード利用指定はできませんが、第1引数内で * を使えば自動的にワイルドカードとして扱われ「含む」検索ができます。

VLOOKUPの場合

=VLOOKUP("*"&A2&"*",A:B,2,FALSE)

HLOOKUPの場合

=HLOOKUP("*"&B1&"*",1:2,2,FALSE)

MATCHの場合

=MATCH("*"&A2&"*",A:A,0)

VLOOKUP、HLOOKUP、MATCH いずれの場合も * をつけて ワイルドカードを利用した検索にすることで、ひらがな・カタカナ、全角・半角、伸ばし棒と母音を区別する 割と厳密な一致に変更できました。

ちなみに式は 前後に *をつけていますが、前だけ、後ろだけ（どちらか片方）でも大丈夫です。

ただし、* は 0文字以上の任意の文字列を表すワイルドカードなので

キーワードを「含む」（両方に * をつけた場合）で 検索してしまい、「葬送のフリーレン」で検索しても 「葬送のフリーレン 第2期制作決定」がヒットしてしまいます。

こういった部分的な一致を考慮する必要がなければ使えますが、実務で使うにはおススメできません。

ワイルドカードモードに切り替えても、ワイルド―カードを使用せずに検索ができる XLOOKUP関数、XMATCH関数を使うのが良さそうです。


XLOOKUPを一致モード2で使った時のパフォーマンス

XLOOKUPのは 一致モードを2にすることで、割と厳密な一致判定に切り替わるということは、通常モードと検索ロジックが違う と思われます。

そうすると、一致モード2にしたら 検索速度が遅くなるんでは？という点が気になりますよね。

10万行2列のデータで試しに検証してみましたが、一致モード省略と一致モード2指定の結果表示速度は どちらも同程度で差異を感じませんでした。

これなら普段使いでも「 第5引数は 2」で覚えてしまっていいかもしれません。


Excelでも使える XLOOKUPの魔法

実は 今回の Googleスプレッドシートの XLOOKUP を割と厳密な一致判定にするハックネタ、別に mirが独自に発見したとかではなく、もともとは Excel界隈で発見されたネタなんです。


Excel のXLOOKUPは 第5引数（一致モード） 2で 全角・半角を区別できる

先ほど書いた通り Excelの場合は、なぜか新関数の UNIQUEや XLOOKUP では全角・半角を区別せず一致と判定してしまいます。

これをXLOOKUPの場合は、第5引数（一致モード）を 2（ワイルドカード利用）とすることで

=XLOOKUP(A2:A14,A2:A14,B2:B14,"",2)

なんということでしょう～

半角と全角を区別して一致判定できるようになり、VLOOKUP関数と同じ結果になりました。

大文字・小文字の区別はできませんが、VLOOKUPやイコールによる一致判定と同じレベルに厳密になっています。

Googleスプレッドシートほどのインパクトはありませんが、この発見が元になっています。


元ネタは X（旧Twitter）の Excel関連ポストから 

このネタはもしかしたら紹介しているサイトがあるのかもしれませんが、mirは Xの Excelガチ勢の方々のポストで知りました。

玉石混交の Xですが、やっぱ旬で貴重な情報がありますよね。
特にExcel勢の人たちは 層が厚いし熱量が高いなと。


で、この仕組みをGoogleスプレッドシートに転用したらさらに便利じゃんってのを教えてもらって、noteにまとめております。

情報を参考にさせていただきました皆様、
ありがとうございました。<(_ _)>　


Googleスプレッドシートの 緩い一致判定を 割と厳密にする魔法 まとめ

まとめです。

Googleスプレッドシートの困った仕様である

「ひらがな・カタカナ、全角・半角、伸ばし棒と母音」を全て区別しない一致判定

これを回避するためには

COUTIFやCOUNTIFS、SUMIF,SUMIFSなどの 〇〇IF関数、〇〇IFS関数 の場合は、範囲を {　} で括って配列化する {魔法}を使う


LOOKUP系やMATCHの場合は、XLOOKUP、XMATCHを利用して 一致モードを 2 で指定して ワイルドカード利用モードにする *魔法*2 を使う

シリーズの2回目である タイトルの2は、実は 今回の魔法、一致モードの指定方法の 2 も意味してたんですね～。（ついでに フリーレンの2期決定の 2もかかってたりしますｗ）

どちらも頭の片隅に入れておけば簡単に使えるハックネタですね。

一致判定に こんな抜け道があるなら最初からExcelと同じ判定にしてくれればいいのに・・・って思いますが、出来ないものは仕方ないんで、とりあえず現状は この回避方法（魔法）を使って乗り切りましょう！

次回のnoteのネタは未定です。

Excelネタですが 新関数 GROUPBY、PIVOTBY が最強すぎて取り上げたかったんですが、急に使えなくなってしまったんで・・・。

いつ復活するんだ～！！


いいなと思ったら応援しよう！

mir
チップ大歓迎です。やる気がアップしますｗ