解説
検索/行列関数
CS資格・Excelの実務に一部必須
全部は覚えなくて良い
解説
- セル範囲等の中から指定したデータの位置や指定セルの位置を求めたり、セル範囲等の中から指定した位置のデータを求める関数です。
- 位置指定等の引数には他の関数が使われる場合が多いため、文系の人には複雑な数式に見えると思いますが、個々に分解して考えれば理解し易いと思います。
- 実務的にはよく利用される関数ですので、必ずマスターしましょう。
- CS検定では合否のカギを握る関数です。
一覧表（赤文字は特に重要）
バージョンによっては使えないものもあります。
| 関数名 | 解説 | |
| データの検索 | MATCH | セル範囲やデータ配列から指定値を検索し、見つけたデータの場所が範囲先頭から何番目なのかを求めます。 |
| INDEX | セル範囲やデータ配列から、指定された位置の値を求めます。よく、MATCH 関数と共に使われます。 | |
| VLOOKUP | セル範囲やデータ配列の左端列で指定値を検索し、同じ行の指定列数目のセルの値を求めます。 | |
| HLOOKUP | セル範囲やデータの配列の上端行で指定値を検索し、同じ列の指定行数目のセルの値を求めます。 | |
| LOOKUP | セル範囲やデータ配列の先頭行または先頭列を検索し、同じ行の末尾の列 又は 同じ列の末尾の行の値を求めます。 | |
| OFFSET | 指定した行数と列数だけずれた位置にあるセルまたはセル範囲へのオフセット参照を求めます。 | |
| CHOOSE | 引数リストの値の中から特定の値を１個選択します。 | |
| セル番地の検索 | COLUMN | 指定セルの列番号を求めます。 |
| ROW | 指定セルの行番号を求めます。 | |
| COLUMNS | 指定セル範囲の列数を求めます。 | |
| ROWS | 指定セル範囲の行数を求めます。 | |
| AREAS | 指定された範囲に含まれる領域の個数を求めます。 | |
| ADDRESS | ワークシート上のセル番地を文字列で求めます。 | |
| INDIRECT | 文字列で表したセル番地のセルの値を求めます。 | |
| その他 | TRANSPOSE | セル範囲のデータの行と列を入れ替えた結果を表示します。 |
| HYPERLINK | WEBページのようなリンクを設定します。 | |
| RTD | アドインソフトからデータを取得します。 | |
LOOKUP、INDEX、MATCH
解説
- 見出しを元に検索したセルの値を返すのが LOOKUP 関数で、範囲の「上から何番目で左から何番目」のセルの値を返すのが INDEX です。
- MATCH は検索するデータと一致する値のセルが検索範囲の何番目なのかを返し、LOOKUP や INDEX 関数とよくセットで使用されます。
- LOOKUPにはLOOKUPとVLOOKUP、それにHLOOKUPの３種があります。
- LOOKUP は範囲の中から指定された値を探しますが、制約が多く殆ど使用しません。
- よく使われるのは VLOOKUP で、先頭列の中から指定値と一致するセルを探し、最初に見つかったセルから右に「指定された番目」のセルの値を返します。
例
=VLOOKUP(3,B57:J104,6,TRUE)
値が「3」のセルをB57:J104の一番左の列から探し、そのセルから数えて右に6番目のセルの値が答えとして返されます。
=VLOOKUP(M60,E57:J104,4,FALSE)
M40の値と等しいセルをE57:J104の一番左の列から探し、そのセルから数えて右に4番目のセルの値が答えとして返されます。
使用方法
書式 =VLOOKUP(検索値 , 範囲 , 列番号 , 検索方法)- 検索値
- 範囲の左端の列の中から探す値
- 範囲
- 対象範囲
- 列番号
- 対象範囲の中で何列目の値を返してほしいのかを指定します。
- 検索方法
- TRUE は範囲の左端の列が昇順に並んでいる場合に指定すれば、検索値が見つからなかった場合に検索値未満で検索値に一番近い値が使用されます。
FALSE は並び替えされていない場合に指定し、見つからない場合はエラーが出ます。
- なお、HLOOKUP は VLOOKUP の行と列を入れ替えただけですので使用方法は同じです。
VLOOKUP での TRUE
VLOOKUPの４番目の引数に「TRUE」又は「１」を指定した場合、検索範囲の中で検索値と一致する値のセルが無ければ検索値以下の値で検索値に最も近い値のセルが該当になります。
ただし範囲の左端の列は昇順に並べ替えておく必要があります。
なお、検索値が範囲の左端の列の先頭よりも小さい値だった場合はエラーになります。
INDEX
- 指定範囲の「上から何番目で左から何番目」のセルの値を返します。
- VLOOKUP は検索値を範囲の先頭列からしか検索できないので、検索値よりも左にあるセルのデータは扱えないのに対し、INDEX は他の関数と組み合わせることで検索値よりも右にあるセルのデータを使用することが出来ます。
- C5:J11の中で3行目の7列目の値
- =INDEX(C5:J11,3,7)
- MATCHと組み合わせるとこのようになります。
- C5:J11の中で「担当者」の値が「シド」の行の７列目の値。=INDEX(C5:J11,MATCH("シド",H5:H11,FALSE),7)
- もう少し高度な別の書き方
C5:J11の中で「担当者」の値が「シド」の行の受渡場所 - =INDEX(C5:J11,MATCH("シド",H5:H11,FALSE),MATCH("受渡場所",C4:J4,FALSE))
- よく使われる組み合わせ
- 数が最大の発注社
=INDEX(E5:E11,MATCH(MAX(J5:J11),J5:J11,FALSE))