データベースと検索関数
- 本章で用いる実習用ファイル
リスト形式の表で用いられる関数群に「検索関数」がある。名前からして、あるいは実際によくある説明として、(リスト形式の)データベースから特定のデータを検索するための関数である、と理解しがちである。機能としては確かにその通りなのだが、実際の使い方とは少し違う。
データベースから条件に従って特定のデータを引き出すのは抽出(フィルター)機能を用いるべきである。検索関数はこの用途には余り適さない。なぜか?
Excelの検索関数は(今のところ)配列を返さない。一つの値を返すのみである。となると複数のレコードが条件に合致する検索条件での実用的な使用は難しいことになる。
A | B | C | D | E | 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 | F | |
---|---|---|---|---|---|---|
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 | 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 | |||
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 F 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 G 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 | E | |
---|---|---|---|---|---|
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 | E | |
---|---|---|---|---|---|
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 F 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 G 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(形式)
課題:会場(形式), 課題:コース(形式)を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 F 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 F 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 G 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(形式)
課題:会場(形式), 課題:コース(形式)をINDEX関数とMATCH関数を使うように式を書き換えよ。
課題:成績評価(形式)
成績評価基準表を元に成績表の成績欄を出力する式をセルD2に立て、オートフィルで残りのセルを埋めよ。
成績表 A B C D E 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 H 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 E F G H 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