検索・抽出

セル参照
関数 機能
ADDRESS 行番号と列番号を指定して、セルのアドレスを取得できる
INDIRECT 参照文字列で指定した位置の、セルの値を取得できる
OFFSET 基準セルから相対的な位置の、セルの値を取得できる
COLUMN 列番号を取得できる
ROW 行番号を取得できる
COLUMNS 指定範囲の列数を取得できる
ROWS 指定範囲の行数を取得できる
検索・抽出
関数 機能
LOOKUP 検索値に対応したセルの値を取り出せる (検査範囲は1行または1列)
MATCH 検索値に対応するセルの位置番号を取得できる
VLOOKUP 縦に並んだ一覧表から、検索値に対応したセルの値を取り出せる (検査範囲は複数列)
HLOOKUP 横に並んだ一覧表から、検索値に対応したセルの値を取り出せる (検査範囲は複数行)
INDEX 表から、行位置と列位置で指定した位置のセルの値を取り出せる
CHOOSE 値のリストから、特定の位置番号のセルの値を取り出せる

単純なデータを一度処理するだけならば、検索する値がある列または行をキーに並べ替えることでも抽出できます。

特定の条件に一致するセルは、IS関数とIF関数で抽出できます。また条件に一致したセルの数を知りたいだけならば、COUNTIF関数を用います。

ワイルドカード

検索値にはワイルドカードを使用できます。

記号 一致する文字
? (疑問符) 任意の1文字
* (アスタリスク) 任意の数の文字
~ (チルダ) "~?"は疑問符に、アスタリスクとチルダも同様に
検索でのワイルドカード文字の使用 - Microsoft サポート

ADDRESS

ADDRESS(行番号, 列番号, [参照の型], [a1], [シート名])
ADDRESS 関数 - Microsoft サポート

参照の型で参照型を指定できます。

値  
1 または省略 絶対参照
2 複合参照 (行は絶対、列は相対)
3 複合参照 (行は相対、列は絶対)
4 相対参照
式 結果
=ADDRESS(1,2) $B$1
=ADDRESS(1,2,2) B$1
=ADDRESS(1,2,3) $B1
=ADDRESS(1,2,4) B1
=ADDRESS(1,2,,FALSE) R1C2

INDIRECT

文字列で指定した、セルの値を得られます。

INDIRECT(参照文字列, [参照形式])
INDIRECT 関数 - Microsoft サポート

参照文字列にはセル範囲を指定でき、他の関数へ複数のセルを渡せます。

参照形式をFALSEとすると、R1C1参照形式と解釈されます。

評価対象のシート
  A B
1 B1 1
2 C 2
式 結果 理由
=INDIRECT(A1) 1 A1の値である"B1"の位置を参照しているから
=INDIRECT("B2") 2 "B2"の位置を参照しているから
=INDIRECT("A"&B2) C "A"とB2の値である"2"を結合した、"A2"の位置を参照しているから
=SUM(INDIRECT("B1:B2")) 3 "B1:B2"のセル範囲を参照し、合計しているから

OFFSET

OFFSET(基準, 行数, 列数, [高さ], [幅])
OFFSET 関数 - Microsoft サポート

行数と列数で、上下方向と左右方向にシフトする数を指定します。

高さと幅で、行数と列数の範囲を指定できます。

評価対象のシート
  A B C
1 1 4 7
2 2 5 8
3 3 6 9
式 結果
=OFFSET(A1,1,2) 8
=OFFSET(B2,-1,-1) 1
=SUM(OFFSET(A1,0,0,3,1)) 6

ROW

指定のセル、または数式が入力されているセルの行番号を取得できます。

ROW([範囲])
ROW 関数 - Office サポート
  A (式) A (結果)
1 =ROW()*2 2
2 =ROW()*2 4
3 =ROW()*2 6

LOOKUP

検索値に対応したセルの値を取り出せます。必要なのが値ではなく、そのセルがある位置ならばMATCHを用います。

LOOKUP(検査値, 検査範囲, [対応範囲])
LOOKUP 関数 - Office のサポート

検査範囲にある値がコード順の昇順に配置されていなければ、正しい結果を得られません。

検査値が見つからなかった場合、範囲内にある検査値以下の最大値が一致する値とみなされます。検査値以下の値が存在しなければ、#N/Aが返されます。

評価対象のシート
  A B
1 10 A
2 20 B
3 30 C
4 40 D
式 結果 理由
=LOOKUP(30,A1:A4,B1:B4) C 一致するA3の値に、B3が対応するため
=LOOKUP(5,A1:A4,B1:B4) #N/A 一致する値が存在せず、検査値5以下の値も存在しないため
=LOOKUP(15,A1:A4,B1:B4) A 一致する値は存在しないが、検査値15以下ではA1の10が最大であり、それにB1が対応するため
=LOOKUP(50,A1:A4,B1:B4) D 一致する値は存在しないが、検査値50以下ではA4の40が最大であり、それにB4が対応するため

LOOKUPには既定のベクトル形式以外に、配列形式もあります。

LOOKUP(検査値, 配列)

この関数は配列の範囲が縦長か横長かによって、下表のように挙動が異なります。

配列の大きさ 検索方向 検索範囲 戻り値  
縦長 縦 表の左端列 表の右端列 VLOOKUPのような動作
横長 横 表の上端行 表の下端行 HLOOKUPのような動作

たとえば=LOOKUP(A1,B2:C5)とすると範囲が縦長となるため、B2:B5の範囲をA1の値で検索し、対応する値がC2:C5から返されます。

MATCH

検索値に対応するセルの位置番号を得られます。

MATCH(検査値, 検査範囲, [照合の型])
MATCH 関数 - Office のサポート
照合の型
値 検索対象 検査範囲の並び順
1
(または省略)		 検査値以下の最大の値 昇順であることが必須。1～9、A～Z、あ～ん、FALSE～TRUEの順
0 検査値と等しい最初の値 任意
-1 検査値以上の最小の値 降順であることが必須。9～1、Z～A、ん～あ、TRUE～FALSEの順
評価対象のシート
  A
1 5
2 20
3 40
4 30
式 結果 理由
=MATCH(10,A1:A4) 1 A1の値が10以下の最大の値であるから
=MATCH(10,A1:A4,0) #N/A 10と等しい値がないから
=MATCH(10,A1:A4,-1) #N/A 降順に並べられていないから
=MATCH(20,A1:A4,0) 2 A2の値が10と一致するから

条件に一致する最初のセルの位置を得る

配列数式でセルに順に式を適用することで、それに一致した最初のセルの位置を得られます。

目的 式
空ではない最初のセルの位置を得る {=MATCH(TRUE,NOT(ISBLANK(A1:A4)),0)}
数値である最初のセルの位置を得る {=MATCH(TRUE,ISNUMBER(A1:A4),0)}

VLOOKUP

左端の列から値を検索し、対応するセルの値を取得できます。

VLOOKUP(検索値, 範囲, 列番号, 検索の型)
VLOOKUP 関数 - Office のサポート

検索値は、範囲の左端にある必要があります。

取得する列は列番号で指定し、それは範囲の左端の列を起点とした1から始まる番号です。

第4引数の検索の型を省略すると「近似一致 (approximate match)」となり、その場合は左端の列を、コード順の昇順に並べ替えておく必要があります。FALSEを指定すると「完全一致」となり、並べ替えの必要はありません。

一致するセルの値を取り出すには、検索の型をFALSEとします。これをTRUEとするのは、数値の大きさに合わせて結果を得るような場合です。

一致する値が見つからないときは、#N/Aが返されます。

評価対象のシート
  A B
1 10 A
2 20 B
3 30 C
4 40 D
式 結果 理由
=VLOOKUP(20,A1:B4,2) B A2の値が20に一致するから
=VLOOKUP(25,A1:B4,2) B  
=VLOOKUP(25,A1:B4,2,FALSE) #N/A 一致する値がないから

INDEX

2種類の用法があります。INDEX 関数 - Microsoft サポート

セル参照形式 (Reference form)

行番号と列番号で指定された位置にある、セルの参照を得られます。

INDEX(参照, 行番号, [列番号], [領域番号])

を基点として、行番号と列番号だけオフセットした位置が参照されます。

参照を名前付き範囲とすることで、その範囲内の指定位置を参照できます。

評価対象のシート
  A B
1 1 4
2 2 5
3 3 6
式 結果 理由
=INDEX(A1:A3,2) 2 3行1列の範囲の、2行目の値
=INDEX(A1:B1,2) 4 1行2列の範囲の、2列目の値
=INDEX(A1:B3,2) #REF! 3行2列の範囲の、列が未指定のため範囲外
=INDEX(A1:B3,2,2) 5 3行2列の範囲の、2行2列目の値
=INDEX((A1:B1,A3:B3),1,1) 1 領域番号1の、1行2列の範囲の、1行1列目の値
=INDEX((A1:B1,A3:B3),1,1,2) 3 領域番号2の、1行2列の範囲の、1行1列目の値

配列形式 (Array form)

行番号と列番号で指定されたテーブルまたは配列の、要素の値を得られます。

INDEX(配列, 行番号, [列番号])

配列は配列定数を指定します。

式 結果
=INDEX({1,2,3},2) 2
=INDEX({1,2,3;4,5,6},1) 1
=INDEX({1,2,3;4,5,6},2) 4
=INDEX({1,2,3;4,5,6},2,2) 5

参考