関数説明|検索、倫理

今回は検索によく使用されるVLOOKUPなどの検索関数とIFなどの倫理関数の主なものの説明を行います。 検索関数は製品番号を入力すると価格が自動的に表示されるようにしたい場合に使用されます。倫理関数、特に IF関数は入力値によって値を変化することができるので合否判定などができます。

共に使いこなせるようになるとExcelでできる作業の幅が大きく広がります。

検索関数

基本的な検索関数を説明します。

縦方向に検索をする関数|VLOOKUP

=VLOOKUP(検索値,検索範囲,参照列番号,検索条件)

検索関数の中で最もよく使われる関数ではないでしょうか。検索値を検索範囲の左端列から一致するものを縦(VERTICAL)に検索し 一致した場合、参照列番号に指定した列数だけ右に移動したセルの値を返します。

検索条件を0にすると一致検索、1にすると近似検索となります。

例

	A	B	C
1	品番	数量	価格
2	2A	4	$390
3	6B	5	$100
4	9C	2	$450
5	1D	7	$23

	A	B	C
1	検索値	関数	結果
2	9C	=VLOOKUP(A2,参照シート!$A$2:$C$5,3,0)	$450
3	66	=VLOOKUP(A3,参照シート!$A$2:$C$5,3,0)	N/A（エラー）
4	9C	=VLOOKUP(A4,参照シート!$A$2:$C$5,2,0)	2

検索条件を1(近似検索)にする場合、検索する列を昇順にしておく必要があります。検索に一致する場合は問題ありませんが、 検索値がない場合最も近い値が返されます。昇順にしてない場合は予期されない結果にある場合があります。

横方向に検索をする関数|HLOOKUP

=HLOOKUP(検索値,検索範囲,参照行番号,検索条件)

VLOOKUPの横に検索するバージョンです。上端の行を左から右へ水平(HORIZONAL)に検索し、 一致した場合参照行番号だけ、下に移動したセルの値を返します。

検索条件を0にすると一致検索、1にすると近似検索となります。 検索条件を1(近似検索)にする場合、検索する行を昇順にしておく必要があります。検索に一致する場合は問題ありませんが、 検索値がない場合最も近い値が返されます。昇順にしてない場合は予期されない結果にある場合があります。

検索方向を自動で判断する関数|LOOKUP

=LOOKUP(検索値,検索範囲,参照範囲) ベクトル方式

=LOOKUP(検索値,検索、参照表) 配列方式

LOOKUP関数は検索方向を自動的に判断します。列数または行数の多い方を検索方向として判断します。同数の場合は縦方向で検索します。 一致した場合、対応するセルを返します。 また、他の表計算ソフトとの互換性のために2種類用意されています。

例

	A	B	C
1	品番	数量	価格
2	2A	4	$390
3	6B	5	$100
4	9C	2	$450
5	1D	7	$23

	A	B	C	D
1	検索値	種類	関数	結果
2	9C	ベクトル	=LOOKUP(A2,参照シート!$A$2:$A$5,参照シート!$C$2:$C$5)	$450
3	9C	配列	=VLOOKUP(A3,参照シート!$A$2:$C$5)	$450

ベクトル方式のLOOKUP関数は検索範囲と参照範囲が別れている場合（一つの表ではない）に使用します。

配列方式は互換性のために存在します。検索方向を気にせず使用できるメリットはありますが、 振る舞いのわかるVLOOKUPやHLOOKUP関数を使用する方が安心のように思えます。

表の行番号と列番号を指定したセルの値を返す関数|INDEX

=INDEX(範囲指定（複数可）,行番号,列番号,複数指定の場合の指定番号)

表や指定した範囲の行番号と列番号を指定して、そのセルの値を返します。セル参照の表バージョンとも言えます。 複数指定した場合は、番号（指定した順番）を指定します。

例

	A	B	C	D	E
1	3	1	1A	OK	CAT
2	2	4	1B	NG	DOG
3	67	5	1C	OK	BIRD
4	9	20	1D	OK	FISH
5	100	7	1E	NG	BUG

=INDEX(A1:C5,2,3)=1B

=INDEX(D1:E5,3,2)=BIRD

=INDEX((A1:C5,D1:E5),4,2,1)=20

=INDEX((A1:C5,D1:E5),4,2,2)=FISH

検索値の一致したセルの相対位置返す関数|MATCH

=MATCH(検索値,検索範囲,検索条件)

LOOKUP関数の相対位置を返すバージョンと思うと分かりやすいと思います。検索条件は0（一致検索）、 1（近似検索-最大）、-1（近似検索-最小）があります。 相対位置とは検索値が検索範囲の何番目（上または左から）かを意味します。

例

	A	B	C	D	E
1	3	1	1A	OK	CAT
2	2	4	1B	NG	DOG
3	67	5	1C	OK	BIRD
4	9	20	1D	OK	FISH
5	100	7	1E	NG	BUG

=MATCH("DOG",E1:E5,0)=2

検索条件が1（近似検索-最大）や-1（近似検索-最小）の場合、検索値以上または以下の最大、最小値の相対位置を返します。 並びを1 は 昇順、-1 は 降順にしている必要があります。

基準から指定した行数と列数の相対位置の参照を返す関数|OFFSET

=OFFSET(基準,指定行数,指定列数,高さ,幅)

基準はセルまたは範囲から指定した行数、列数だけ移動したセル、範囲の参照を返します。 セルの値が欲しい場合は高さと幅は1,1となり、範囲を変更する必要がある場合はその高さと幅を入力します。 他の関数の引数として使用されます。

例

	A	B	C	D	E
1	3	1	1A	OK	CAT
2	2	4	1B	NG	DOG
3	67	5	1C	OK	BIRD
4	9	20	1D	OK	FISH
5	100	7	1E	NG	BUG

セルの値が必要な場合

=OFFSET(A1,1,2)=1B A1をから1下がり、右へ2移動

=OFFSET(A1,1,2,1,1)=1B

関数の変数として利用する場合

=SUM(OFFSET(B1:C5,0,-1))=218 B1:C5を左へ1移動
=SUM(A1:B5)と同様

=SUM(OFFSET(B1:C5,0,-1,2,2))=10 B1:C5を左へ1移動し、範囲を行（高さ）2、列（幅）2に変更
=SUM(A1:B2)と同様

倫理関数

倫理関数について説明します。

条件によって値を変える|IF

=IF(条件式,真の場合の値,偽の場合の値)

条件判定をして、値を変えます。条件は倫理記号を使用するか、別の倫理関数を利用することができます。 また入れ子にすることもできます。

例

	A	B	C
1	参照先	関数例	結果
2	5	=IF(A2<6,“OK”,“NG”)	OK
3	10	=IF(A3<6,“OK”,“NG”)	NG
4	true	=IF(A4,“OK”,“NG”)	OK
5	false	=IF(A5,“OK”,“NG”)	NG
6	AAA	=IF(A6=“AAA”,“OK”,“NG”)	OK
7	BBB	=IF(A7=“AAA”,“OK”,“NG”)	NG

３つ以上使用すると分かりづらくなり保守性が低下します。入れ子の数には限界があります。

以下の倫理関数を併用するとより複雑な条件を設定できます。

全ての条件を満たす場合のみ真をかえす|AND

=AND(条件式,条件2,・・・）

倫理積を返します。指定された条件を全て満たすと真を返します。条件の数には限界があります。

一つでも条件を満たすと真を返す|OR

=OR(条件1,条件2,・・・）

倫理和を返します。指定された条件の内１つでも満たすと真を返します。条件の数には限界があります。

真を偽に偽を真にする|NOT

=NOT(条件式)

排他を返します。真偽を逆転します。

常に真、偽を返す|TRUE FALSE

=TRUE()

=FALSE()

常に真、偽を返します。引数は不要です。他の表計算ソフトとの互換性のために用意されています。

今回説明した関数類は基本と言ってもいいですが、これらだけでも作業の効率化を計ることができます。 実際、組み合わせるともっと複雑なことができる関数もあります。しかし数式が複雑になりがちで保守性が著しく低下します。

複雑な数式を否定するわけではありません。ただ、最初は基本をしっかりしてからの方が何かと応用が効くように思えます。