Excel講座

データベースと検索関数

本章で用いる実習用ファイル

excelLookup

リスト形式の表で用いられる関数群に「検索関数」がある。名前からして、あるいは実際によくある説明として、(リスト形式の)データベースから特定のデータを検索するための関数である、と理解しがちである。機能としては確かにその通りなのだが、実際の使い方とは少し違う。

データベースから条件に従って特定のデータを引き出すのは抽出(フィルター)機能を用いるべきである。検索関数はこの用途には余り適さない。なぜか?

Excelの検索関数は(今のところ)配列を返さない。一つの値を返すのみである。となると複数のレコードが条件に合致する検索条件での実用的な使用は難しいことになる。

	A	B	C	DE	F	G
1	ID	名前	グループ	血液型	生年月日	身長
2	2203	小片リサ	つばきファクトリー	A	1998/11/5	159
3	1601	山岸理子	つばきファクトリー	B	1998/11/24	155
4	1905	藤井梨央	こぶしファクトリー	B	1999/3/4	150
5	2205	広瀬彩海	こぶしファクトリー	O	1999/8/4	156
6	2003	新沼希空	つばきファクトリー	O	1999/10/20	160.2
7	2206	谷本安美	つばきファクトリー	B	1999/11/16	157.5

18	2405	秋山眞緒	つばきファクトリー	B	2002/7/29	160
19

このデータからIDで検索するのであれば(IDは一意Uniqueな値なので)検索関数が使える。しかし例えば血液型で検索すると値は複数必要になる。抽出(フィルター)機能なら例えば「B型」で抽出すると複数のレコードを引き出せる。しかし検索関数は最初にヒットしたレコードからしか値を引き出せない。これでは「使えない」。

では検索関数はなにに使うのか?まずはそこの理解から始めよう。

検索関数

検索関数の主たる使用目的は表の結合である。表の結合とは何か?

	A	B	C	D	E
1	明細番号	商品番号	商品名	単価	数量
2	1	A-1	越前ガニ	3600	1
3	2	B-1	越前そばセット	3500	1
4	3	B-2	ソースカツ丼	2500	3
5	4	C-1	若狭牛	10000	1
6	5	D-1	梅ワイン	4200	2
7

このような表では「商品番号」と「商品名」は一対一の対応をしていて、「商品番号」が決まれば「商品名」も確定する。「商品番号」と「商品名」双方を逐一入力する必要が無い。「商品番号」と「商品名」を別に入力すると不整合が起こることの方が問題である。ならば「商品番号」が入力されれば「商品名」は自動的に入力されるのが望ましい。さらに商品(番号・名)が決まれば、価格も決まる。というわけで「商品番号」と「商品名」「価格」の対応表を用意する。

	A	B	C	D	E
1	明細番号	商品番号	商品名	単価	数量
2	1	A-1			1
3	2	B-1			1
4	3	B-2			3
5	4	C-1			1
6	5	D-1			2
7
8		商品番号	商品名	単価
9		A-1	越前ガニ	3600
10		A-2	若狭河豚	13000
11		B-1	越前そばセット	3500
12		B-2	ソースカツ丼	2500
13		C-1	若狭牛	10000
14		D-1	梅ワイン	4200
15

こうした対応表があれば、商品番号が決まれば、商品名・単価は自動的に確定する。元の「受注票」は「明細番号・商品番号・数量」の表と「商品番号・商品名・単価」の表を結合させた表とみることができる。このときの「商品番号」から他の情報を引き出すときに用いるのが検索関数である。もちろん「商品番号」は一意Uniqueな値である。

検索関数一覧

INDEX

範囲指定は一次元・二次元どちらも可
MATCH
- デフォルトの検索モードは昇順検索(1)
XMATCH2021
- デフォルトの検索モードは完全一致(0)
- 昇順検索は「-1」

一次元

LOOKUP

検索モードは昇順検索のみ

XLOOKUP2021

LOOKUP関数をパワーアップ(完全一致・昇順・降順・ワイルドカード)
デフォルトの検索モードは完全一致
昇順検索は「-1」

二次元

VLOOKUP

縦方向の検索を行う
検索対象は一番左の列固定
デフォルトの検索モードは昇順検索(TRUE)

HLOOKUP

横方向の検索を行う
検索対象は一番上の行固定
デフォルトの検索モードは昇順検索(TRUE)

検索モード
	完全一致	近似一致(昇順・降順)	見つからない場合
MATCH	0	[1]…昇順 / -1…降順	#N/A
XMATCH2021	[0]	-1…昇順 / 1…降順 / 2…ワイルドカード	#N/A
LOOKUP	×	昇順	#N/A
VLOOKUP/HLOOKUP	FALSE/0	[TRUE/1]…昇順	#N/A
XLOOKUP2021	[0]	-1…昇順 / 1…降順 / 2…ワイルドカード	自由に指定できる

[]内は初期値

縦方向の検索:VLOOKUP(検索値,検索範囲,列番号,[一致モード])

VLOOKUPは検索範囲の一番左の列のなかから検索値と一致する値を上から探し、その列番号目の値を返す関数である。

一致モードが0(FALSE)…完全一致: 検索値と完全に一致するデータのみを検索
一致モードが1(TRUE)…(初期値)近似一致(昇順検索):検索値(数値)以下の値を検索。上回った時点で検索終了
※このとき検索範囲の1列目は昇順に並んでいなければならない。

VLOOKUP(B3,商品マスター,2,FALSE):3行目の「商品番号」から「商品名」を検索する

	A	B	C	D	E
1	明細番号	商品番号	商品名	単価	数量
2	1	A-1			1
3	2	B-1	=VLOOKUP(B3,商品マスター,2,FALSE)		1
4	3	B-2			3
5	4	C-1			1
6	5	D-1			2
7
8		商品番号	商品名	単価
9		A-1	越前ガニ	3600
10		A-2	若狭河豚	13000
11		B-1	越前そばセット	3500
12		B-2	ソースカツ丼	2500
13		C-1	若狭牛	10000
14		D-1	梅ワイン	4200
15		1	2	3

商品マスター: B9:D14

セルB3の値(商品番号:B-1)を範囲商品マスターの一番左の列(商品番号列)から検索(上から3番目、11行目でヒットする)し、その行の左から2番目(C列)の値(「越前そばセット」)を返す。

一致モードを完全一致ではなく、近似一致を用いるメリットは以下の対応表(同じ商品を複数個購入すると購入数に応じて値引きする)を用意したときに現れる。

VLOOKUP(E4,値引率マスター,2,TRUE):4行目の「数量」から「割引率」を検索する

	A	B	C	D	E	F
1	明細番号	商品番号	商品名	単価	数量	値引率
2	1	A-1			1
3	2	B-1			1
4	3	B-2			3
5	4	C-1			1
6	5	D-1			2

16		数量	値引率
17		0	0%
18		2	1%
19		5	5%
20		10	10%
21		1	2

値引率マスター: B17:C20

セルE4の値(数量:3)を範囲値引率マスターの一番左の列(数量列)から検索(1番上:17行目、2番目:18行目でヒット、3番目:19行目で終了→18行目が最終検索値)し、その行(18行)の左から2番目(C列)の値(「1%」)を返す。

課題:会場(形式)

クラス番号から会場を導く式をセルD2に立てよ。

	A	B	C	D
1	学生番号	名前	クラス番号	会場
2	M090001	高橋	10
3	M090002	新垣	60
4	M090003	亀井	30
5	M090004	道重	40
6	M090005	田中	50
7	M090006	久住	60
8	M090007	銭	10
9	M090008	李	20
10	M090009	光井	30
11
12	クラス番号	会場
13	10	京都第一
14	20	京都第二
15	30	滋賀第一
16	40	滋賀第二
17	50	大阪
18	60	東京
19

課題:コース(形式)

点数からコースを導く式をセルD2に立てよ。

	A	B	C	D
1	学生番号	名前	点数	コース
2	C200001	山岸	13
3	C200002	小片	80
4	C200003	新沼	64
5	C200004	谷本	43
6	C200005	岸本	79
7	C200006	浅倉	60
8	C200007	小野	55
9	C200008	小野田	48
10	C200009	秋山	61
11
12	点数	コース
13	0	基礎定着
14	60	基本
15	70	応用
16	80	実践
17

横方向の検索:HLOOKUP(検索値,検索範囲,行番号,[一致モード])

HLOOKUPは検索範囲の一番上の行のなかから検索値と一致する値を左から探し、その行番号目の値を返す関数である。

一致モードが0(FALSE)…完全一致: 検索値と完全に一致するデータのみを検索
一致モードが1(TRUE)…(初期値)近似一致:検索値(数値)以下の値を検索。上回った時点で検索終了
※このとき検索範囲の1行目は昇順に並んでいなければならない。

VLOOKUP関数の横バージョンである。検索範囲(対応表)の作りが行/列入れ替わっているときに用いる。あまり使用されない。

縦横両方向の検索:XLOOKUP(検索値,検索範囲,戻り配列,[見つからない場合の処理],[一致モード])2021

XLOOKUPは検索範囲(VLOOKUP関数における検索範囲の一番左の列と同じ)のなかから検索値と一致する値を探し、戻り配列の中から対応する値を返す関数である。

一致モードが-1…近似一致:検索値(数値)と一致、または次に小さい項目(VLOOKUPの1・TRUE相当)
一致モードが0…(初期値)完全一致: 検索値と完全に一致するデータのみを検索
一致モードが1…近似一致:検索値(数値)と一致、または次に大きい項目

VLOOKUP, HLOOKUPを包括し、それに取って代わりうる新関数(Excelのバージョンによっては使用できない)。両関数の欠陥の多くを修正している。

縦横でいちいち関数を分けているのが、無駄っぽい
近似一致の時、対応表が昇順で並んでいないと使えない
値を返すフィールドを指定する第3引数がただの数字で、目視で数えて入力することになる(数えさせることもできるが、別の関数が必要)
対応表の検索対象列より左のフィールドの値は返せない
一致する値がなかったとき、エラー(#N/A)になる

XLOOKUP(B3,商品番号,商品名):3行目の「商品番号」から「商品名」を検索する

	A	B	C	D	E
1	明細番号	商品番号	商品名	単価	数量
2	1	A-1			1
3	2	B-1	=XLOOKUP(B3,商品番号,商品名)		1
4	3	B-2			3
5	4	C-1			1
6	5	D-1			2
7
8		商品番号	商品名	単価
9		A-1	越前ガニ	3600
10		A-2	若狭河豚	13000
11		B-1	越前そばセット	3500
12		B-2	ソースカツ丼	2500
13		C-1	若狭牛	10000
14		D-1	梅ワイン	4200
15

商品番号: B9:B14
商品名: C9:C14
単価: D9:D14

セルB3の値(商品番号:B-1)を範囲商品番号から検索(上から3番目、11行目でヒットする)し、それに対応する商品名の値(「越前そばセット」)を返す。

XLOOKUP(E4,数量,値引率,"",-1):4行目の「数量」から「値引率」を検索する

「値引率」も同様に求められる。

	A	B	C	D	E	F
1	明細番号	商品番号	商品名	単価	数量	値引率
2	1	A-1			1
3	2	B-1			1
4	3	B-2			3
5	4	C-1			1
6	5	D-1			2

16		数量	値引率
17		0	0%
18		2	1%
19		5	5%
20		10	10%
21

数量: B17:B20
値引率: C9:C14

課題:XLOOKUP(形式)

課題:会場( xlsx 形式), 課題:コース( xlsx 形式)をXLOOKUP関数を使うように式を書き換えよ。

要素番号から配列の値を返す:INDEX(配列,行番号,[列番号])
配列の値からそれを含む要素番号を返す:MATCH(検索値,配列,[一致モード])

VLOOKUP,HLOOKUP,XLOOKUPはその名の通り検索LOOKUPするための関数である。同等の機能はより汎用的な機能を持った関数を組み合わせることでも実現できる。

INDEX関数, MATCH関数は配列を扱うための基本的な関数である。

INDEXは要素番号(行番号と列番号)を指定するとそれに対応する配列の値を返す関数である
MATCHは検索値が配列の何番目に含まれるか、要素番号を返す関数である
- 一致モードが0…検索値と完全に一致するデータのみを検索
- 一致モードが1…(初期値)検索値(数値)以下の値を検索。上回った時点で検索終了
  ※このとき配列は昇順に並んでいなければならない。
- 一致モードが-1…検索値(数値)以上の値を検索。下回った時点で検索終了
  ※このとき配列は降順に並んでいなければならない。

配列:つばき
1	2	3	4	5	6	7	8	9
山岸	小片	新沼	谷本	岸本	浅倉	小野	小野田	秋山

INDEX(つばき,6)→浅倉
MATCH("浅倉",つばき,0)→6

INDEX関数、MATCH関数は単独でできることは(重要だが)単純である。しかしこれを組み合わせるとVLOOKUP,HLOOKUP,XLOOKUPすべてを包括し、さらにそれ以上のことができる最強の検索式を作ることができる。

二次元配列バージョン(VLOOKUP相当) INDEX(商品マスター,MATCH($B3,商品番号,0),MATCH(C$1,$B$8:$D$8,0))

	A	B	C	D	E
1	明細番号	商品番号	商品名	単価	数量
2	1	A-1			1
3	2	B-1	=INDEX(商品マスター,MATCH($B3,商品番号,0),MATCH(C$1,$B$8:$D$8,0))		1
4	3	B-2			3
5	4	C-1			1
6	5	D-1			2
7
8		商品番号	商品名	単価
9		A-1	越前ガニ	3600
10		A-2	若狭河豚	13000
11		B-1	越前そばセット	3500
12		B-2	ソースカツ丼	2500
13		C-1	若狭牛	10000
14		D-1	梅ワイン	4200
15

MATCH("B-1",商品番号,0)→3

商品番号
1	A-1
2	A-2
3	B-1
4	B-2
5	C-1
6	D-1

MATCH("商品名",商品マスターフィールド名,0)→2

商品マスターフィールド名
1	2	3
商品番号	商品名	単価

INDEX(商品マスターデータ, 3, 2 )

商品マスターデータ
	1	2	3
1	A-1	越前ガニ	3600
2	A-2	若狭河豚	13000
3	B-1	越前そばセット	3500
4	B-2	ソースカツ丼	2500
5	C-1	若狭牛	10000
6	D-1	梅ワイン	4200

※行方向、列方向をクロスさせた検索が可能である。

一次元配列バージョン(XLOOKUP相当) INDEX(商品名,MATCH($B3,商品番号,0))

	A	B	C	D	E
1	明細番号	商品番号	商品名	単価	数量
2	1	A-1			1
3	2	B-1	=INDEX(商品名,MATCH(B3,商品番号,0)		1
4	3	B-2			3
5	4	C-1			1
6	5	D-1			2
7
8		商品番号	商品名	単価
9		A-1	越前ガニ	3600
10		A-2	若狭河豚	13000
11		B-1	越前そばセット	3500
12		B-2	ソースカツ丼	2500
13		C-1	若狭牛	10000
14		D-1	梅ワイン	4200
15

商品番号: B9:B14
商品名: C9:C14

MATCHの昇順検索モード:4行目の「数量」から「値引率」を検索する INDEX(値引率マスター,MATCH($E4,数量),MATCH(F$1,$B$16:$C$16, 0))二次元 INDEX(値引率,MATCH($E4,数量))一次元

「値引率」も同様に求められる。検索値が数値だとMATCH関数は「検索値以下」の値を検索する。

	A	B	C	D	E	F
1	明細番号	商品番号	商品名	単価	数量	値引率
2	1	A-1			1
3	2	B-1			1
4	3	B-2			3
5	4	C-1			1
6	5	D-1			2

16		数量	値引率
17		0	0%
18		2	1%
19		5	5%
20		10	10%
21

値引率マスター: B16:C20
値引率: B16:B20
数量: B16:B20

課題:INDEX_MATCH(形式)

課題:会場( xlsx 形式), 課題:コース( xlsx 形式)をINDEX関数とMATCH関数を使うように式を書き換えよ。

課題:成績評価(形式)

成績評価基準表を元に成績表の成績欄を出力する式をセルD2に立て、オートフィルで残りのセルを埋めよ。

成績表
	A	B	C	D
1	学生番号	名前	点数	成績
2	C200001	山岸	13
3	C200002	小片	0
4	C200003	新沼	41
5	C200004	谷本	23
6	C200005	岸本	79
7	C200006	浅倉	60
8	C200007	小野	55
9	C200008	小野田	48
10	C200009	秋山	61
11

成績評価基準
評価	点数
5	80点以上
4	60点以上
3	40点以上
2	20点以上
1	それ未満

課題:総当たりリーグ戦結果(形式)

6チーム総当たりリーグ戦の対戦結果表から、自チームと相手チームを指定したら、その試合の結果を表示するようにセルC10に式を立てよ。

	A	B	C	D	E	F	G
1	チーム	汎神	虚人	DaTA	薬留止	中台	島根
2	虚人	9-6	-	13-2	10-3	1-0	0-3
3	DaTA	3-1	2-13	-	9-6	1-0	0-2
4	汎神	-	6-9	1-3	5-3	1-0	2-1
5	薬留止	3-5	3-10	6-9	-	10-3	11-2
6	島根	1-2	3-0	2-0	2-11	0-1	-
7	中台	0-1	0-1	0-1	3-10	-	1-0
8
9	自チーム	相手チーム	結果
10	虚人	汎神	9-6
11

課題:十二支(形式)

十二支は年を12で割った余りから判定できる。

十二支と余りの対応表から、各人の十二支を判定する式を立てよ。

	A	B	C	D	F	G
1	ID	名前	生年月日	十二支	十二支	余り
2	2203	小片リサ	1998/11/5		子	4
3	1601	山岸理子	1998/11/24		丑	5
4	1905	藤井梨央	1999/3/4		寅	6
5	2205	広瀬彩海	1999/8/4		卯	7
6	2003	新沼希空	1999/10/20		辰	8
7	2206	谷本安美	1999/11/16		巳	9
8	1602	野村みな美	2000/2/10		午	10
9	1201	小川麗奈	2000/3/27		未	11
10	1704	岸本ゆめの	2000/4/1		申	0
11	1102	浜浦彩乃	2000/4/26		酉	1
12	1103	田口夏実	2000/7/21		戌	2
13	2207	浅倉樹々	2000/9/3		亥	3
14	2401	小野瑞歩	2000/9/28
15	1706	和田桜子	2001/3/8
16	2208	井上玲音	2001/7/17
17	2209	小野田紗栞	2001/12/17
18	2405	秋山眞緒	2002/7/29
19