Excel でデータをまとめたり、分析したりする際に条件に応じてセルの値を変化させたい時があります。
その際、例えばフィルタを使ってセルを抽出、手入力で値を打ち込むことを行う人がいるかもしれません。
もしくは、IF関数やIFS関数を使って長い条件式を書いている人がいるかもしれません。
最終的な結果はどれも同じですが、効率に大きく差が開きます。
今回は、知っておくと便利なHLOOKUP関数で条件一致によるデータの変化させる方法について、紹介したいと思います。
HLOOKUPとは？
Microsoft Officeの公式サポートページでは以下のように説明されています。
テーブルの上端行または配列内の特定の値を検索し、テーブルまたは配列内の指定した行から同じ列の値を返します。 HLOOKUP 関数は、比較する値がデータ テーブルの上端行にあり、指定した行数分だけ下を参照する場合に使用します。 比較する値が検索データの左側の列にある場合は、VLOOKUP 関数を使用してください。
HLOOKUP 関数の “H” は、横方向 (horizontal) の検索を意味します。
Microsoft Officeサポート〝HLOOKUP〟
Horizontal（水平線）で Lookup（見つける）関数です。
ここで何を見つけるか？というと、ある範囲内で指定した値を横方向に検索して見つけ出すということになります。
表示する値はさらに下方向に下がることになりますが、これは垂直方向で値を見つけ出したと考えるよりも、水平方向で検索値を見つけ出した後に指定した行数下がっただけなので、関数には水平線の〝H〟がついていると考えるのが納得できます。
また、上記説明でもある通り、縦方向に探し出したい場合は、VLOOKUP関数を使用するようにしましょう。
HLOOKUP関数の構文
まず、HLOOKUP関数は以下の構文で表記されます。
HLOOKUP(検索値, 範囲, 行番号, [検索の型])
それぞれに指定するものの意味は以下の通りです。
- 検索値 テーブルの上端行で検索する値
- 範囲 データを検索する範囲
- 行番号 値を返すデータが存在している範囲内の行番号
- 検索の型 完全一致で検索するか、近似値を含めて検索するかを〝FALSE〟〝TRUE〟で指定
検索の型については省略することが可能です。
検索値では、関数の中で値（文字列）を指定することもできますし、セルを参照して指定することも可能です。
例えば、HLOOKUP(1,～,～)とすることや、HLOOKUP(A1,～,～)とすることも可能です。
範囲については、セルの範囲を指定する他に、範囲にあらかじめ名前を付けておいて、その範囲名で指定することも可能です。
例えば、HLOOKUP(～,A1:D10,～)とすることや、HLOOKUP(～,Name1,～)とすることも可能です。
行番号については、1より大きい数値を指定します。
検索の型については、省略することが可能ですが、指定する場合は〝TRUE〟か〝FALSE〟を指定します。
指定しない場合は部分一致の検索〝TRUE〟となっていますので、完全一致で検索させたい場合は、必ず〝FALSE〟を指定してください。
なお、〝TRUE〟の代わりに0以外の数値、〝FALSE〟の代わりに0を利用することも可能です。（理解するためには〝TRUE〟〝FALSE〟の方が良いとは思います）
個人的には、完全一致の方がTRUEになっても良いような気がします。
HLOOKUP関数の使い方・利用シーンは？
HLOOKUP関数をどういった時に使用するかを紹介します。
HLOOKUP関数は、横方向に捜査する関数なので、参照元のデータが横方向に重複せずに並んでいるものを元にして条件分岐させたい場合に有効です。
例えば、以下のようなくだものの購入履歴のデータがあり、くだものに当てはまる金額を入力したいときに、HLOOKUP関数が利用できます。
| 購入者 | 購入品 | 金額 |
|---|---|---|
| Aさん | りんご | ？ |
| Aさん | いちご | ？ |
| Aさん | メロン | ？ |
| Bさん | すいか | ？ |
| Bさん | バナナ | ？ |
この際、別に以下のようなマスタとなる金額データを作成し、金額セルの中に関数を入れることで、自動的に金額を入力することができます。
| りんご | みかん | ぶどう | いちご | メロン | すいか | バナナ |
| 98 | 298 | 1400 | 79 | 1980 | 4980 | 98 |
この際、購入履歴のデータがA1～C6にある（ヘッダが1行目）として、マスタとなる金額データがE1～K2にあると想定します。
金額セル（C2）で関数設定を〝=HLOOKUP(B2, E1:K2, 2, FALSE)〟とすると、C2セルにはりんごの金額〝98〟が入力されます。
この際、〝E1:K2〟部分を$を使って、セルの固定を指定する〝$E$1:$K$2〟とすると、残りのセルにもセルをコピペするだけで、全て自動的にデータを入力させることができます。
HLOOKUP関数の考え方
HLOOKUP関数の考え方はどうなるかについて紹介します。
HLOOKUP関数の動き
それでは、HLOOKUP関数が実行されるとき、どのような動きとなっているのでしょうか？
検索の型がTRUEの場合とFALSEの場合で少し動きが変わってくるので、分けて紹介したいと思います。
検索の元となるテーブルは以下の通りとし、検索するフィールドが昇順に並んでいるものとします。
Excelにてサンプルデータとして作成したい場合は、以下のテーブルからコピーして使用してみてください。
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| りんご | みかん | ぶどう | いちご | メロン | すいか | バナナ |
| 98 | 298 | 1400 | 79 | 1980 | 4980 | 98 |
検索の型がTRUEの場合（指定しない場合）
一致する値が存在した場合
ここでの関数の設定は以下の通りとします。
HLOOKUP(6, A1:G3, 2, TRUE)
まず、検索を行う範囲を設定します。今回はA1:G3の範囲が検索が実行されるデータの範囲です。
A1:G3の範囲の一行目のA1～G1の中で〝6〟という数値を検索します。
すると、F1に一致する値〝6〟が存在することが分かります。
そして、6が存在するF列の2行目はF2となるため、そこに存在する値は〝すいか〟なので、関数の出力値は〝すいか〟となります。
一致する値が存在しなかった場合
では、一致する値が存在しなかった場合はどうなるのでしょうか？
ここでの関数の設定は以下の通りとします。
HLOOKUP(10, A1:G3, 2, TRUE)
先ほどと同じように、検索する範囲はA1:G3です。
A1～G1の中で〝10〟を探しても、見つかりません。
この場合、代替の値として、10よりも小さくて、1行目に存在する一番大きな（最後の）値を見つけることになります。
ここで10よりも小さい中で一番大きな（最後の）値は〝7〟となり、G1に存在しています。
G列の2行目、G2に存在する値は〝バナナ〟になるため、〝バナナ〟が関数の出力値となります。
分かりやすくなるように、テーブル中の一番大きな値で表現しましたが、例えば、G1の値が〝70〟だった場合は、F1の〝6〟が10よりも小さくて、1行目に存在する大きな（最後の）値になるため、関数の出力値は〝すいか〟となります。
検索の型がFALSEの場合
次に、検索の型がFALSEの場合の関数の動きを紹介します。
一致する値が存在した場合
一致する値が存在する場合の関数の動きについては、TRUEの場合と全く同じです。
前述していますので、目次から飛んできた方はこちらをクリック。
一致する値が存在しなかった場合
一致する値が存在しなかった場合の動きは、TRUEの場合と少し異なります。
ここでの関数の設定は以下の通りとします。
HLOOKUP(10, A1:G3, 2, FALSE)
検索する範囲はA1:G3となり、A1～G1の中で〝10〟という値を探しますが、見つかりません。
この場合、HLOOKUP関数では、TRUEの場合のように代替の値を検索することはなく、#N/Aというエラーを出力します。
検索フィールドの順番に注意！
検索の型がTRUEの場合、検索フィールドが昇順になっている必要があります。
検索フィールドが昇順になっていない場合、自分が求める正しい値が求められない可能性があるため、注意しましょう。
ここでは、検索フィールドが昇順になってない場合のHLOOKUP関数の出力値の変化をいくつか紹介します。
先ほど紹介したデータの1行目の値の7と2が入れ替わったものをデータとして使用してみます。
| 1 | 7 | 3 | 4 | 5 | 6 | 2 |
| りんご | みかん | ぶどう | いちご | メロン | すいか | バナナ |
| 98 | 298 | 1400 | 79 | 1980 | 4980 | 98 |
HLOOKUP関数の検索値を〝2〟〝3〟〝7〟〝10〟とそれぞれ変化させた場合の出力値は以下の表の通りとなります。
| 関数 | 出力値 | |
| ① | HLOOKUP(2,A1:G3,2,TRUE) | りんご |
| ② | HLOOKUP(3,A1:G3,2,TRUE) | りんご |
| ③ | HLOOKUP(7,A1:G3,2,TRUE) | バナナ |
| ④ | HLOOKUP(10,A1:G3,2,TRUE) | バナナ |
このように、目で見たデータでは、①の出力結果は〝バナナ〟となってほしいところですが、結果は〝りんご〟。
②の出力結果は〝ぶどう〟となってほしいところですが、結果は〝りんご〟。
③の出力結果は〝みかん〟となってほしいところですが、結果は〝バナナ〟。
④の出力結果は10より小さい数値の最大値は7なので、〝みかん〟となりそうなところですが、結果は〝バナナ〟。
と想定とは全く違う動きをします。
そのため、検索の型をTRUE（指定しないで省略する）場合は、元々のデータが昇順に並んでいることを確認してください。
検索フィールドのデータの重複に注意！
検索の型に寄らず、検索フィールドのデータが重複していると、欲しい結果とならない可能性があるため、検索フィールドのデータの重複には注意してください。
先ほどのデータの3が2になったものを例として使用します。
| 1 | 2 | 2 | 4 | 5 | 6 | 7 |
| りんご | みかん | ぶどう | いちご | メロン | すいか | バナナ |
| 98 | 298 | 1400 | 79 | 1980 | 4980 | 98 |
このように、検索フィールドのデータが重複している場合に、HLOOKUP関数のTRUEとFALSEを変更させた場合の出力値の変化は以下のようになります。
| 関数 | 出力値 | |
| ① | HLOOKUP(2,A1:G3,2,TRUE) | ぶどう |
| ② | HLOOKUP(2,A1:G3,2,FALSE) | みかん |
このように、データが重複しているとき、検索の型がTRUEの場合はよりデータが後ろにあるものが、検索の型がFALSEの場合は、よりデータが前にあるものが結果として出力されます。
そのため、欲しいデータが出力されない可能性があるので、検索フィールドのデータが重複していないか、確認をする必要があります。
まとめ
今回は、条件に一致するものをデータとして出力する方法として、HLOOKUP関数について紹介しました。
条件一致によるデータの出力・集計等は表計算ソフトであるExcelの醍醐味だと言っても過言ではないと思っています。
条件一致・条件分岐系の関数は覚えていくとかなり使える知識なので、是非活用してください！
コメント