水平照合関数の機能
水平照合関数は、ある値と同じ値を持つセルを左端から走査し、検索したセルと同じ位置で指定した行のセルの値を結果として返す関数です。
やや少し難しそうですが、なんて事は無く単に位置関係での検索をする、という話をしているだけです。あとは、ほぼ同じパターンで覚えることができる垂直照合関数もセットで覚えるのがおすすめです。
照合系関数
定義文を読んでも「ん？」という感じだと思います。基本情報技術者試験の表計算の関数には垂直照合関数、水平照合関数、照合検索関数、照合一致関数と「照合」という名称がつく関数が4つあります。
照合という名称がついた関数をまとめて、私は照合系と勝手に呼んでいますが、私がイメージする照合系関数の特徴は、求めたい値を検査範囲から検索するという、そのままのイメージです。
ある求めたい値Xがあって、その値がセル範囲中のどこかにあるとして、セル範囲の左上から右下の方へダーッと「これは値Xか？Xか？」と見つかるまで探しているのが、私が思っている照合系関数の基本的なイメージです。このことから、照合系関数の特徴としては、求めたい値が引数として必要であり、また検索する対象のセル範囲が引数として必ず含まれています。この点を意識して頂ければ照合系関数の学習は分かりやすくなります。照合系関数は合計関数や剰余関数のように、何かを計算する関数ではなく何かを探す関数なんだということになります。
水平照合関数の書式
水平照合(式,セル範囲,行の位置,検索の指定)
- 式
- 式は検索する値になります。セル参照や計算式（A1/B2とか）も記述可能です。
- セル範囲
- 値を探す範囲になります。
- 行の位置
- 値が結果を返す行の位置の数値になります。
- 検索の指定
- 検索の指定が0の場合：式の値と一致する値を検索する。
検索の指定が1の場合：式の値以下の最大値を検索する。
水平照合関数はとりあえず検索したい値、検索したい範囲、検索したい範囲の中の位置、の3つを覚えてあとは引数4が0と覚えておくといいと思います。指定するセル範囲は1つだけで、この1つで検索と抽出をまとめて行うことになります。
水平照合関数の実行例
| A | B | C | D | E | F | G | |
| 1 | 商品コード | 1001 | 1002 | 1003 | 1004 | 1005 | |
|---|---|---|---|---|---|---|---|
| 2 | 商品 | バナナ | リンゴ | みかん | いちご | メロン | |
| 3 | 単価 | 190 | 130 | 30 | 120 | 250 | |
| 4 | |||||||
| 5 | 商品 | 1003 | |||||
| 6 | 単価 | 30 | |||||
| 7 | 水平照合関数文字列 | 水平照合(B5,B1:F3,3,0) | |||||
| 8 |
つながっているので分かりにくいですが、1行～3行までを1つとする表、5行～7行までを1つとする表がそれぞれあるとします。また、1行と2行と3行は値と文字列のデータがあり、5行と6行は数値、7行は水平照合関数の文字列があるとします。
つぎに、セルB5に商品コードが入力されたら対応する列の単価3行をセルB6に表示したいとします。上の例は、もうすでにセルB5には1003が入力され、セルB6に30が表示されているので分かりにくいのですが、セルB5に1003と入力すると、リターンキーを押した瞬間セルB6が30と変わるような、指定した値から検索した結果を表示したいとします。他の照合系関数でも求めることはできますが、水平照合関数を使用してみます。どのようにするのかというと、セルB5に「水平照合(B5,B1:F3,3,0)」と関数を入力するだけです。
照合検索の関数の引数は、
引数1は B5
引数2は B1:F3
引数3は 3
引数4は 0
となっていますが、少しわかりにくいので段階を経て見てゆきます。
1.式に入力（引数1・B5）
引数1で指定された式に、値が入力されます。例の場合の入力された値は1003です。
2.セル範囲（引数2・B1:F3）
引数1で指定されたD2に、値1003が入力されると、検索のセル範囲から同じ値のセルを検索します。この時のセル範囲の検索の仕方は、一番上の行を上から下へ走査します。
| A | B | C | D | E | F | G | |
| 1 | 商品コード | 1001 | 1002 | 1003 | 1004 | 1005 | |
|---|---|---|---|---|---|---|---|
| 2 | 商品 | バナナ | リンゴ | みかん | いちご | メロン | |
| 3 | 単価 | 190 | 130 | 30 | 120 | 250 | |
| 4 | |||||||
| 5 | 商品 | 1003 | |||||
| 6 | 単価 | 30 | |||||
| 7 | 水平照合関数文字列 | 水平照合(B5,B1:F3,3,0) | |||||
| 8 |
引数2のB1:F3は検索のセル範囲で色は青色を着けてみました。
セル範囲の一番上側を検索するのが決まりです。また一番上側の行内に同じ値が複数あった場合は、最初に出現した列が対象になります。例の場合は、左から3番目、列番号でいうと4列目が目的の列ということになります。
| A | B | C | D | E | F | G | |
| 1 | 商品コード | 1001 | 1002 | 1003 | 1004 | 1005 | |
|---|---|---|---|---|---|---|---|
| 2 | 商品 | バナナ | リンゴ | みかん | いちご | メロン | |
| 3 | 単価 | 190 | 130 | 30 | 120 | 250 | |
| 4 | |||||||
| 5 | 商品 | 1003 | |||||
| 6 | 単価 | 30 | |||||
| 7 | 水平照合関数文字列 | 水平照合(B5,B1:F3,3,0) | |||||
| 8 |
3.行の位置（引数3・3）
2のセル範囲で見つかった列を対象に一番上の行から1、2、3…と数えて引数3で指定された行位置の値を結果として返します。
行の位置
行位置1が行1
行位置2が行2
行位置3が行3
例の場合は3で行番号は3行になります。今回はたまたま3行が行位置3になりましたが、セル範囲の上側から1番目と数える決まりなので、間違えないように注意です。
| A | B | C | D | E | F | G | |
| 1 | 商品コード | 1001 | 1002 | 1003 | 1004 | 1005 | |
|---|---|---|---|---|---|---|---|
| 2 | 商品 | バナナ | リンゴ | みかん | いちご | メロン | |
| 3 | 単価 | 190 | 130 | 30 | 120 | 250 | |
| 4 | |||||||
| 5 | 商品 | 1003 | |||||
| 6 | 単価 | 30 | |||||
| 7 | 水平照合関数文字列 | 水平照合(B5,B1:F3,3,0) | |||||
| 8 |
2と求めた4列と3で求めた3行の交わるところ、セルD3が検索するセルになり、値は30になります。
このように、水平照合関数はセル範囲の上側から水平方向に検索し、見つかったら垂直方向へ引数3の値の数の分を横から縦へと絞り込むように検索してゆくのが水平照合関数になります。
検索の指定が1の場合
先の実行例は、検索の指定が0のときの例で、検索の流れの説明を優先するため、引数4について特に触れてきませんでした。引数4は0と1が指定できると仕様に書いてありますが、違いは何なのでしょうか。仕様では
「検索の指定が1の場合：式の値以下の最大値を検索する。」
と書いてあり、引数4が1のときは、検索する値が同じである必要はなく、最大値を探すことができるようになっています。さきほどの上の例だと引数1の式の値が1003で、値1003をセル範囲の一番上側を検索し、同じ値が4列目にありましたが、もし値1003が無かった場合、引数4が0の場合は検索されずにエラーになりますが、引数4に1を指定した場合、値が一致しない場合でも値1003に近い最大値が検索されることになります。同じ値がないと予想できる場合にはこの方法が有効です。
引数4は厳格に列を検索するか、アバウトに列を検索するかという設定の違いだけで、他の手続きは一緒です。
以上、水平照合関数でした。