今回は「VLOOKUP関数」、「HLOOKUP関数」、「XLOOKUP関数」の使い方について詳しくご紹介させて頂きます。
VLOOKUP関数はよく知られていますので、すでにお使いの方もいらっしゃると思います。
他のデータから指定した値を検索するのに大変便利な関数ですが、「垂直方向の検索」に限定されているので、条件によっては利用することが出来ない場合があります。
そんな時に活躍するのが、「HLOOKUP関数」であり、「XLOOKUP関数」になります。
これら3つの関数を覚えておくと、他のデータから簡単に指定した値を検索出来るようになりますので、ぜひこの機会に覚えてみてください。
HLOOKUP関数
基本的な使い方
構文＝HLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
Googleのヘルプには以下のように書かれています。
水平方向の検索です。範囲の 1 行目で指定したキーを検索し、同じ列内にある指定したセルの値を返します。
以前ご紹介した「VLOOKUP関数」と同様に、「データから指定した値を検索する」関数になります。
VLOOKUP関数との違い
では「VLOOKUP関数」との違いは何でしょうか。
機能的にはほぼ同じ関数なのですが、検索する方向が違います。
- VLOOKUP関数は縦方向の検索
- HLOOKUP関数は横方向の検索
参照するデータが縦方向なのか、横方向なのかで使い分けます。
実際の使い方
まずは「VLOOKUP関数」の使い方について振り返ってみましょう。
D２のセルに「=VLOOKUP(C2,$D$23:$E$27,2,0)」と入力しています。
②のC列に商品名を入力すると自動的に③の表から商品価格を①に入力します。
この時、商品価格の検索は「垂直方向」に行われます。
次に「HLOOKUP関数」についてみてみましょう。
D30のセルに、「=HLOOKUP(C30,$C$51:$G$52,2,0)」と入力しています。
②のC列に商品名を入力すると自動的に③の表から商品価格を①に入力します。
言葉の説明としては、先程の「VLOOKUP関数」と同じですが、③の表での検索の仕方が違います。
「VLOOKUP関数」は垂直方向に商品価格を検索しましたが、「HLOOKUP関数」は「水平方向」に商品価格を検索しています。
XLOOKUP関数
基本的な使い方
構文＝XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
Googleのヘルプには以下のように書かれています。
検索範囲を検索して見つかった一致の位置に基づき、結果範囲内の値を返します。一致するものが見つからない場合は、最も近い値が返されます。
上記の説明では何が出来るのかよく分からないかもしれませんが、簡単に言うと、「VLOOKUP関数」と「HLOOKUP関数」の代わりに使用することが出来る関数になります。
VLOOKUP関数の代わりに使う
先程の「VLOOKUP関数」を使った表を「XLOOKUP関数」で書き換えてみましょう。
当然、結果は同じで、②のC列に商品名を入力すると自動的に③の表から商品価格を①に入力します。
違いは、③の選択範囲です。
「VLOOKUP関数」では、表の中の「何列目（番号）」を使うか指定する必要がありました。
「XLOOKUP関数」では、「検索範囲」、「結果範囲」をそれぞれ個別に指定します。
HLOOKUP関数の代わりに使う
「HLOOKUP関数」の代わりに使う場合もみてみましょう。
こちらも結果は同じになります。
「HLOOKUP関数」の代わりに使用しますので、③で水平方向の範囲を選択しています。
「商品」が検索範囲になり、「価格」が結果範囲になります。
実用的な使い方
今まで「XLOOKUP関数」を「VLOOKUP関数」、「HLOOKUP関数」の代わりに使用する使い方をみてきました。
しかし、「VLOOKUP関数」、「HLOOKUP関数」で出来ることをわざわざ「XLOOKUP関数」を使って計算する意味はありません。
では、どのような場合に、「XLOOKUP関数」を使用するべきでしょうか。
実は、「VLOOKUP関数」、「HLOOKUP関数」共に、「検索キー」にある制限がかけられています。
- VLOOKUP関数：検索キーは、データ範囲の一番左の列であること
- HLOOKUP関数：検索キーは一番上にあり、その下のデータを返すこと
このような制限がかけられているために、使用するデータによっては「VLOOKUP関数」、「HLOOKUP関数」を使用することが出来ない場合があります。
実際の例で確認してみましょう。
①にあるセルD2に、②の「商品名（検索キー）」から自動で③の「商品番号」を入力したい場合を考えてみましょう。
下の表を見てみると、「商品番号」は検索キーの左側にあるために、「VLOOKUP関数」を使用することが出来ません。
このような場合に「XLOOKUP関数」であれば、「検索範囲」と「結果範囲」を個別に指定することが出来るため、問題なく商品番号を自動で入力することが出来るようになります。
計算式は以下になります。
=XLOOKUP(C2,$D$23:$D$27,$C$23:$C$27)
では④に、検索キーから自動で⑤の商品価格を入力したい場合はどうでしょうか。
表を見ると商品価格は検索キーの右側にあることが分かります。
このような場合は、「XLOOKUP関数」を使うまでもなく、「VLOOKUP関数」を使用することで自動的に商品価格を入力することが出来ます。
計算式は以下になります。
=VLOOKUP(C2,$D$23:$E$27,2,0)
「XLOOKUP関数」と「VLOOKUP関数」を使用することにより、正しい表を作成することが出来ました。
「HLOOKUP関数」の場合も確認しておきましょう。
セルD31に、商品名から自動で商品番号を入力する時は、商品番号が検索キーより上にあるので、「XLOOKUP関数」を使用します。
数式は以下になります。
=XLOOKUP(C31,$C$53:$G$53,$C$52:$G$52)
セルE31に、商品名から自動で商品価格を入力する時は、商品価格が検索キーより下にあるので、「HLOOKUP関数」を使用します。
数式は以下になります。
=HLOOKUP(C31,$C$53:$G$54,2,0)
絶対参照とIFERROR関数
すでにご存じの方も多いと思いますが、今回ご紹介したような表を作成する時は、「IFERROR関数」と「絶対参照」を記述するのを忘れないようにしましょう。
絶対参照
セルD31に「XLOOKUP関数」を普通に記述すると以下になります。
=XLOOKUP(C31,C53:G53,C52:G52)
D31には正しい計算結果、「102」が返されますが、D31を下の行に貼り付けると「#N/A」とエラーが表示されてしまいます。
これは参照するデータの位置も変わってしまうために、「データが見つからない」ということで表示されるエラーになります。
なおGoogleスプレッドシートでは「絶対参照」を記述しなくても、下の行に数式をコピーしようとすると、正しいと思われる数式が候補として表示されます。
このような候補が表示された場合は、矢印の先にあるチェックアイコンをクリックしてください。
先程の数式が訂正され、絶対参照を含んだ正しい数式が表示されるようになりました。
=XLOOKUP(C31,$C$53:$G$53,$C$52:$G$52)
またD32以降のセルにも絶対参照を含んだ数式がペーストされ、商品番号が正しく入力されています。
IFERROR関数
先程の表では、「商品名」がすべてのセルに記載されていましたが、実際に利用する時は、日々商品を入力していくと思います。
その際は当然空白のセルが存在することになるのですが、XLOOKUP関数では検索キーが見つからないため「#N/A」エラーが表示されてしまいます。
このままでも、商品名を入力すれば、エラーは消えて商品番号や金額が表示されるのですが、見た目的にも良くありませんし、表が分かりにくいものになってしまいます。
そこで、商品名が未入力でもエラーを表示させないようにする関数が「IFERROR関数」になります。
使い方はとても簡単で、「XLOOKUP関数」の数式を「IFERROR関数」で囲むだけになります。
=IFERROR(XLOOKUP(C31,$C$53:$G$53,$C$52:$G$52))
あとはこの数式を表の最後まで貼り付ければ、先程まで表示されていた「#N/A」エラーは表示されなくなります。
ここでは単純にエラーが表示されないように「IFERROR関数」を使用しましたが、入力が未入力の場合に特定の数字やメッセージを挿入することも可能です。
詳しくは以下の記事でご説明させて頂いていますので、合わせてご参照ください。
まとめ
いかがでしたか。
「VLOOKUP関数」、「HLOOKUP関数」、「XLOOKUP関数」と3つの関数の使い方についてご説明させて頂きました。
最初は難しく感じることがあるかもしれませんが、実際にご自身で表を作成して頂くとすぐに慣れてしまうと思います。
どの関数も実際の業務でも大変良く使われることが多く、とても便利な関数なので、ぜひこの機会にしっかりと見に付けておきましょう。
今回も最後までお読み頂きありがとうございました。
スプレッドシートを使用する際の参考になれば幸いです。
コメント