エクセルを覚えようと思っても、どんなシーンで使える関数なのかをイメージできなければインプットは困難です。
エクセルには現在で480種以上の関数があると言われており、その関数をひとつずつ覚えていくのは困難と言えるでしょう。
さらに、どの関数にはどの効果があるのかは、実際に使ってみなければ理解することが難しいです。
そんなときは、エクセルの関数の種類と意味をひとつずつ学び、どんなシーンで活用できるのかをイメージすることが大切です。
この記事では【HLOOKUP関数】の使い方や書式、応用方法について解説します。
HLOOKUP関数とは、テーブルデータの範囲を選択し、そのなかに検索したい文字列や数値が書かれたセルがあるかどうかを関数を用いて指定したセルに返すといった意味を持ちます。
文字だけで見るととても複雑に感じてしまう関数ですが、具体的にどのようにして使うのか、応用方法にはどのような関数と組み合わせるのか、HLOOKUP関数と類似するVLOOKUP関数との違いについて詳しく説明しています。
この記事を読むだけでHLOOKUP関数の基礎から応用までをインプットでき、今まで以上にエクセルを使いこなし作業効率が上がるようになりますから、ぜひこの機会にマスターしましょう。
目次
HLOOKUP関数とは？
HLOOKUPは2つの英単語が合わさっており、Hには水平や横方向を表す【HORIZON】が、LOOKUPには【探す】の意味を持っています。
HLOOKUP関数とは、比較する数値がテーブルデータの上の行に位置しており、指定した行数を参照する際に使用する関数です。
比較する数値が検索データの左側の列にある場合には、VLOOKUP関数を使用します。
HLOOKUP関数の書式を解説
HLOOKUP関数で使用する書式は以下の通りです。
| =HLOOKUP(検索する値,範囲,行番号,[検索の型(FALSE(完全一致)または(TRUE)一番近いデータ)] |
それぞれの引数について解説します。
検索の値
入力必須項目です。
テーブルデータの上の行で検索するべき値を指定するために使用します。
検索値には【値】【参照】【文字列】を使用します。
範囲
入力必須項目です。
テーブルデータ内の情報を検索する際に使用します。
セル範囲、またはセル範囲名を使用します。
行番号
入力必須項目です。
第2引数で範囲指定した行の上から数えた行番号を特定するために使います。
検索の型
この引数は任意入力です。
検索値に一致するデータがないときにどのように対応するかを決める引数を意味します。
FALSE、または0は完全一致を表しており、TRUE、または1は一番近いデータを表しています。
後述しますが、範囲指定した検索対象のセルのなかに、間違いなく検索値がある場合は入力しなくても関数の効果は発揮されます。
HLOOKUP関数の基本の使い方
このようなテーブルデータがあるとします。
現在上部の商品コードにはカーネーションの商品コードが入力されていますが、商品名は未入力です。
未記入部分にHLOOKUP関数を用いてカーネーションを表示させてみましょう。
その際の関数は以下のようになります。
| =HLOOKUP(B2,$B$4:$F$6,2,FALSE) |
それぞれの引数の意味を解説します。
| B2, | 商品コードを指定しています。この商品コードに一致するものを返すよう指定しています。 |
| $B$4:$F$6, | B4からF6までの範囲を指定し、そのなかから一致するものを検索します。 |
| 2, | B4からF6までの範囲指定した対象の中で、商品名は2行目に表示されているので【2】を記入します。 |
| FALSE | B2の数値と完全一致するものを返したいのでFALSEを使用します。 |
入力し終えたら【Enter】を押します。
B2で指定した対象物がHLOOKUP関数によってカーネーションであることを導き出しました。
関数の挿入を用いてHLOOKUP関数を使う方法
エクセルには手入力をせずとも関数を検索して使う方法もあります。これを関数の挿入と呼び、この部分をクリックすることで関数を検索することができます。
ではこの関数の挿入を用いてHLOOKUP関数を使用してみましょう。
関数を入力すべき窓の横にこのような記号があるのでクリックします。
クリックすると【関数の挿入】ダイアログボックスが開きます。
上記に【関数の検索】窓があるので、【HLOOK】と途中まで入力します。
すると【関数名】に【HLOOKUP】と表示されました。
検索結果に表示されたHLOOKUPをクリックし、【OK】を押します。
【関数の引数】と呼ばれるダイアログボックスが開きました。
ここではHLOOKUP関数の引数をそれぞれ
- 検索値
- 範囲
- 行番号
- 検索方法
でわかれており、マウスでクリック、または簡単な手入力でHLOOKUP関数を使うことができます。
さっそくテーブルデータをもとに入力していきましょう。
まずは検索値を選択しましょう。
検索値は商品コード+商品名が書かれたA2のセルを指定しました。
つづいて範囲を選択します。
B4からF7の商品の詳細が書かれたセルを範囲指定します。この方法は、手入力で関数を入力するときと全く同じ要領で問題ありません。
他のデータも同じように検索し返したい場合は【F4】キーを押して、範囲のズレをなくすための【絶対参照】を行いましょう。
※今回はカーネーションのみを検索したいので【F4】キーは省いています。
つぎに行番号を指定します。
B4からF7を範囲指定したなかで、価格が表示されている行は上から4番目になります。
そのため、行番号の指定では【4】を入力しています。
最後に検索方法です。
検索方法については入力は任意ですが、エラーがないよう表示させたいので【完全一致】の意味を持つ【FALSE】を入力しています。
このような表示になったら【OK】を押しましょう。
A2で指定したセルの条件と完全に一致する価格は【500】であることが、関数の挿入を用いたHLOOKUP関数で求めることができました。
今回は完全一致のFASLEを入力していますが、最初に指定した検索値と完全に一致している数値が指定した範囲のなかに間違いなくある場合は入力しなくても数値が返ります。
実際に試してみましょう。
完全一致の意味を持つ【FALSE】を削除し、【OK】を押してみます。
問題なく【500】という価格が返りました。
テーブルデータ内に完全一致があることが把握できている場合は省略可能ですが、完全一致しているものがあるかどうかを知りたい場合には、【FALSE】または【0】を入力して検索しましょう。
また、完全一致ではなく、一部一致を検索したい場合は【TRUE】または【1】を入力するようにしましょう。
エクセルを使いはじめたばかりの方や、それぞれの関数の引数の意味をまだ覚えられていない方、どんな引数を使えばいいのかしっかり覚えておきたい方には、手入力ではなく、関数の挿入を使って入力することで、関数の意味と引数への理解を深めることができますよ。
HLOOKUP関数とVLOOKUP関数の違いとは？
HLOOKUP関数と類似した関数としてVLOOKUP関数があります。
HLOOKUP関数は検索を【横】に行うもの、VLOOKUP関数は検索を【縦】に行うものといった違いがあります。
また、第3引数がそれぞれ【行】または【列】といった違いもあります。
参照したいテーブルデータによって、HLOOKUP関数が適しているのか、またはVLOOKUP関数が適しているのかを考慮して使うことにより、さらに効率よく作業を進めることができます。
HLOOKUP関数の応用
ここではHLOOKUP関数を使った応用方法について解説します。
- 複数条件で検索する方法
- 別シートを検索する方法
の2種類を解説しますので、ぜひこの機会にあわせて覚えておきましょう。
複数条件で検索する方法
HLOOKUP関数では1つの条件を指定して検索します。
しかし、テーブルデータ自体を複数条件で検索できるよう加工することで、より多くの検索対象から探し出すことができます。
B2セルの商品コード、C2セルの商品名をもとにD2セルに価格を表示させたいとします。
この場合、あらかじめ【商品コード+商品名】の列を作成して商品コードと商品名を合わせて表示させるように設定します。
この場合B4セルに記入する書式は以下の通りです。
| =B5&B6 |
データを複合して表示させたい場合は【&】で結んで表示させます。
B4に【1001－001カスミソウ】と表示されたら、そのセルの右下角にマウスをあて、オートフィルでF4セルまで自動で関数を入力させます。
4行目全てに商品コード+商品名が表示されました。
つぎに、上部の商品コード横に、同じように【商品コード+商品名】のセルを作成します。
そのセル内に、先ほどと同じ要領で、B2の商品コードとC2の商品名を複合して表示させる関数を入力します。
この場合の書式は以下の通りです。
| =B2&C2 |
【Enter】を押すと、
B2セルとC2セルを指定したことによって、複合した名称が表示されました。
最後に、当初求めようとしていたD2セルの【単価】を求めていきます。
D2セル内に以下の書式で関数を入力します。
| =HLOOKUP(A2,$B$4:$F$7,4,FALSE) |
それぞれの引数を解説します。
| A2, | B2セルとC2セルで指定した商品コード+商品名のセルを指定しています。 |
| $B$4:$F$7, | B4からF7までの範囲の中からA2に書かれた対象物を検索します。 |
| 4, | 範囲指定したセルの4行目に価格が表示されているため、【4】を記入します。 |
| FALSE | A2に対して完全一致するものを検索したいのでFALSEを入力します。 |
入力し終えたら【Enter】を押します。
B2セルとC2セルを合わせた数値【A2】に完全一致する対象物は、B4からF7のセルのなかにあり、その数値は【500】ということがHLOOKUP関数で求められました。
別シートを検索する方法
D2の価格部分に、別シートから参照した数値を表示させる方法もあります。
別シートには、別途商品リストがテーブルデータとして作成されています。
この別シートの商品リストを使って、カーネーションの価格を求めます。
この場合のD2セルに入力する関数は以下の通りです。
| =HLOOKUP(B2,商品リスト!$H$2:$M$4,3,FALSE) |
【Enter】を押して確認してみましょう。
商品リストのテーブルデータから、B2の商品コードと一致する商品の価格を表示することができました。
HLOOKUP関数におけるエラーの理由と解決方法
HLOOKUP関数における主なエラーと、その解決方法について解説します。
#N/Aエラー
検索対象が未指定の場合、または検索範囲が小さい順から並んでいない場合、さらに、FALSEを用いた際に検索範囲の一行目にない検索値を指定している可能性があります。
ほかにも、検索値に記載された数値と完全に一致しないものが検索され、見つからない場合もこのエラーが表示されます。
関数の書式を今一度確認、または検索対象となるセルの選択数値、参照範囲、行の数値を確認してみましょう。
#REF!エラー
検索範囲の行数よりも検索結果の行数が多い場合に起きるエラーです。
検索範囲の行数を今一度確認してみましょう。
######エラー
エクセルにおいて主に発声しやすいエラーです。
検索値の数値がセルの範囲を超えている可能性が考えられます。
セルの幅を広げることで解決します。
まとめ
エクセルをマスターしよう！と奮闘しても、480種以上もの関数をインプットするのは困難です。
エクセルの関数を覚える場合には、どんなシーンで活躍する関数なのかをイメージすることですんなり覚えられるようになります。
まずは、関数の種類をしっかりと把握し、どんな作業に約たつのかを考えながら覚えるよう心掛けることがエクセルをマスターするポイントです。
エクセルをしっかり身につけ、ぜひ今後のオフィスワークに役立ててください。
