Googleスプレッドシートで念願の関数「XLOOKUP」が使える様になりました。
長らくGoogleスプレッドシートで、検索範囲を指定して検索し、一致した位置に基づき、結果範囲内の値を返すには、「VLOOKUP」や「HLOOKUP」の関数を使用していました。
ただ、「VLOOKUP」や「HLOOKUP」 は、仕様の古さから機能的に使いにくいという問題があり、機能アップされた「XLOOKUP」の実装を切望。
既に他の表計算ソフトでは導入済みで、Googleスプレッドシートのユーザーは指をくわえていた状態だったので、ようやく導入されたという感じでしょうか。
そこで本記事では、Googleスプレッドシートに導入された「XLOOKUP」の解説と使い方を紹介したいと思います。
「VLOOKUP」や「HLOOKUP」の使い勝手の悪さに不満を感じていた人も、「XLOOKUP」で確実に解消されると思うので是非参考にしてみてください。
 |
|
「VLOOKUP」や「HLOOKUP」の使い勝手の悪さ
「VLOOKUP」や「HLOOKUP」 は、検索した位置に基づいた値を返してくれる便利な関数ではありますが、設計思想の古さから、使い勝手の悪さが指摘されていました。
「VLOOKUP」「HLOOKUP」の問題点
・検索範囲の位置が、一番端（「VLOOKUP」は最左、「HLOOKUP」は最上）に固定されている為、結果範囲の自由度が低い。
・返す値の位置を列番号・行番号で指定する為、列・行の増減編集をした際に、数式の修正が必要になる。
・「近似値検索」をする際は、あらかじめデータを昇順に並び替える処理が必要。
・「近似値検索」がデフォルトの設定（“TRUE”）になっている為、通常の使用時には、いちいち「完全一致検索」（“FALSE”）を設定する必要がある。
■VLOOKUPの使用例
第一引数に「検索キー」第二引数に「範囲」、第三引数に「値を返す列番号」第四引数に「検索モード」を指定。
=VLOOKUP("第4戦",A2:D24,3,FALSE)この場合、検索される列は、指定した範囲の一番左側「A列」になります。
つまり、「C列」を検索して、「A列」の値を返す。その様な処理はできません。
「XLOOKUP」は「VLOOKUP」や「HLOOKUP」の改善版
「XLOOKUP」は、列範囲を検索し、一致した位置の行範囲の指定した位置の値を返す「VLOOKUP」と、行範囲を検索し、列範囲の値を返す「HLOOKUP」の両方の機能を備えています。
更に、「VLOOKUP」「HLOOKUP」では、端に固定されていた検索範囲の位置を、任意に決めることが可能で、結果の範囲も自由度が高くなっています。
「XLOOKUP」の使い方は、基本的に引数を3つ指定するだけ。
XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
検索範囲と、結果範囲をまとめて指定する「VLOOKUP」や「HLOOKUP」とは違い、それぞれを分けて指定するので、データの並び順を気にする必要がありません。
検索範囲と結果の範囲を指定
「XLOOKUP」では、検索範囲と、結果範囲をそれぞれ分けて設定します。
これにより、「VLOOKUP」「HLOOKUP」の問題として挙げられる、検索位置は“一番左側の列”のような事が無くなり、検索範囲・結果範囲を自由に決められます。
第一引数「検索キー」、第二引数「検索範囲」、第三引数「結果範囲」、第四引数「見つからない場合」を指定。
=XLOOKUP("ベルギーGP",C2:C24,A2:A24,"Not Found")「C列」から「”ベルギーGP”」を検索。一致した位置に基づいて、一番左側の列「A列」から値を返す。
「VLOOKUP」「HLOOKUP」の検索列と、結果列の位置を気にする必要がありません。
一致モード（完全一致・部分一致）を指定
第五引数で「一致モード」を設定する事ができます。
指定した「検索キー」と“完全一致”にするか、“部分一致”にするのか、一致とみなす基準を[0,1,-1,2]で設定できます。
| 一致モード | |
|---|---|
| 0 | 完全一致（デフォルト） |
| 1 | 完全一致または[検索キー]より大きい次の値 |
| -1 | 完全一致または[検索キー]より小さい次の値 |
| 2 | ワイルドカードの文字列に一致する値 |
■一致モードの使用例
=XLOOKUP("メキシコ",C2:C24,A2:A24,"Not Found")第一引数の検索キーに、「”GP”」を抜いた「”メキシコ”」を指定。
すると、完全一致する値が無いので、第四引数の「見つからない場合」の値「”Not Found”」が返されます。
■[検索キー]より大きい値の場合
=XLOOKUP("メキシコ",C2:C24,A2:A24,"Not Found",1)一致させたい値は検索キーより大きいので、第五引数に、「1」を指定。
すると、「”メキシコGP”」が一致し、A列から「”第19戦”」が返されました。
■ワイルドカードを使った検索
検索キーに曖昧な箇所があるのなら、ワイルドカードを使った検索が便利です。
=XLOOKUP("Ba*",D2:D24,C2:C24,"Not Found",2)D列のコース名で検索したいけどスペルが曖昧。その場合はワイルドカード「*」を使って「”Ba*”」
と指定。
第五引数に、「2」を指定。
すると、”Ba”から始まる値「Barcelona」が一致しました。
検索モードを指定
第六引数の「検索モード」は、検索する方法（昇順・降順など）を指定できます。
| 検索モード | |
|---|---|
| 1 | 先頭から末尾に向かって検索（デフォルト） |
| -1 | 末尾から先頭に向かって検索 |
| 2 | バイナリ検索で指定された範囲を検索（最初に範囲を昇順で並べ替える必要有り） |
| -2 | バイナリ検索で指定された範囲を検索（最初に範囲を降順で並べ替える必要有り） |
■先頭から末尾に向かって検索
=XLOOKUP("イタリア*",C2:C24,A2:A24,"Not Found",2,1)検索モードをデフォルトの[1]に指定。
検索範囲「C列」を「”イタリア”」で検索すると、上から下に検索されるので、第2戦目の「”イタリアGP”」が一致しました。
■末尾から先頭に向かって検索
=XLOOKUP("イタリア*",C2:C24,A2:A24,"Not Found",2,-1)次に、検索モードを[-1]に指定。
今度は、下から上に検索されるので、第14戦目の「”イタリアGP”」が一致します。
一致した結果範囲内の全ての値を返す
これまで結果は、指定した一列の値だけを返していました。
「XLOOKUP」は、結果範囲に指定した箇所の値を全て返す事ができます。
=XLOOKUP("第7戦",A2:A24,B2:D24,"Not Found")第三引数の結果範囲に、検索範囲「A列」以外を全て「B2:D24」指定します。
すると、一致した位置に基づく、結果範囲の全ての値が返されました。
まとめ： 「VLOOKUP」「HLOOKUP」から「XLOOKUP」へ
Googleスプレッドシートに新たに実装された「XLOOKUP」の使用方法を解説しました。
「XLOOKUP」は、これまでの 「VLOOKUP」「HLOOKUP」の不満を解消するものとして、アップデートされた関数です。
「VLOOKUP」「HLOOKUP」で出来ることは全て出来るのは勿論、より使いやすく、柔軟性も高いので、敢えて「VLOOKUP」「HLOOKUP」を使い続ける必要性は皆無でしょう。
今すぐ「XLOOKUP」に切り替えることをオススメします。
■「XLOOKUP」の使い方
XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
月額定額制の完全オンライン・プログラミングスクール
「SAMURAI TERAKOYA（侍テラコヤ）」
・月々定額のサブスクで利用でき、入会金も不要、いつでも退会可能
・現役エンジニアの講師とマンツーマン・レッスンで疑問や悩みを解決
・オンラインで学べるので地方在住者でも受講可能
 |
|
 |
|
 |
国内で10年前からサービスを提供しているタスク管理・プロジェクト管理ツール。
利用者が100万人を超え、エンジニアの方以外にも、事務やデザイナー、マーケター、営業など、多様な職種の方にお使いいただいています。 |
 |
 |
|
 |
|
 |
|
 |
|
 |
|