Ⅶ　文字列検索

関数名

関　　　　　数　　　　　の　　　　　機　　　　　能
SEARCH 検索文字列が他の文字列の左端から数えて最初に現れる文字数を返す。
半角と全角を区別しない。英字の大文字と小文字は区別しない。
SEARCHB 検索文字列が他の文字列の左端から数えて最初に現れるバイト数を返す。
全角→2 、半角→ 1 バイトで計算。英字の大文字と小文字は区別しない。
FIND 検索文字列が他の文字列の左端から数えて最初に現れる文字数を返す。
半角と全角を区別する。英字の大文字と小文字は区別する。
FINDB 検索文字列が他の文字列の左端から数えて最初に現れるバイト数を返す。
全角→2 、半角→ 1 バイトで計算。英字の大文字と小文字は区別する。
MATCH 指定範囲の中で検索値と一致する値の存在する相対的な位置を求める。
INDEX 任意の範囲から、指定した行と列に交わる位置にある値を求める。
VLOOKUP 指定範囲の左端列を検索し、検索値と一致した行の指定列セルの値を求める。
HLOOKUP 指定範囲の先頭行を検索し、検索値と一致した列の指定行セルの値を求める。
DGET データベースの指定されたフィールド列を検索し、条件を満たすレコードから1つの値を抽出。

 

Ⅶ－1 SEARCH関数、SEARCHB関数

機　能 SEARCH関数　検索する文字列が他の文字列の左端から数えて最初に現れる文字数を返す。半角と全角を区別しない。
SEARCHB関数　検索する文字列が他の文字列の左端から数えて最初に現れるバイト数を返す。全角→2 、半角→ 1 バイトで計算。
書　式 SEARCH(検索文字列,対象,開始位置)
SEARCHB(検索文字列,対象,開始位置)
説　明 文字列を検索するとき、英字の大文字と小文字は区別されない。（英字の大文字と小文字を区別するときは、FIND 関数を使用）
検索文字列   検索する文字列を指定する。半角の「?」 または半角の「*」 をワイルドカード文字として使用することができる。
ワイルドカード文字の「?」は任意の 1 文字を表し、「*」は任意の文字列を表す。ワイルドカード文字ではなく、通常の文字として「?」「*」を検索する場合は、その文字の前に、"中村区~*" のように半角のチルダ (~) を付ける。
対象   検索文字列 を含む文字列を指定する。
開始位置   検索を開始する位置を指定する。対象の先頭文字（左端）から検索を開始するときは 1 を指定し、3 文字目から開始するときは 3 を指定する。開始位置 を省略すると、1 を指定したと見なされ、対象 の先頭文字から検索が始まる。
SEARCH関数が返す文字番号は、開始位置 からの文字数ではなく、常に 対象 の先頭 (左端) からの文字数を表す。

［実例1]：検索文字列が対象文字列中の左端から何文字目にあるかを英数字の大文字・小文字を区別しないで調べる。


[セルB2の式]
  =SEARCH("市",A2)
[式の意味]
  「市」の文字が対象文字列(セルA2)の左端から何文字目にあるか文字数単位で調べる。
　　"市"　　検索文字列
　　A2　　　対象
　　　　　　開始位置を省略
[セルB3の式]
  =SEARCHB("市",A3)
[式の意味]
  「市」の文字が対象文字列(セルA3)の左端から何文字目にあるかバイト数単位で調べる。
　　"市"　　検索文字列
　　A3　　　対象
　　　　　　開始位置を省略

[セルB4の式]
  =MID(A4,SEARCH("市",A4,1)+1,SEARCH("区",A4,1)-SEARCH("市",A4,1))
[式の意味]
  「市」と「区」の文字が対象文字列(セルA3)の左端から何文字目にあるか文字数単位で調べ、区名を取り出す。
　● SEARCH("市",A4,1)+1
　　　　「市」の文字があるのは左から4文字目なので、4+1で値は 5
　● SEARCH("区",A4,1)+1
　　　　「区」の文字があるのは左から7文字目なので、7+1で値は 8
　● SEARCH("市",A4,1)+1
　　　　「市」の文字があるのは左から4文字目なので、4+1で値は 5
上記の式で得られた値を使用してMID関数に置き換えると、
　　=MID(A4,5,8-5)
　　　　A4　　文字列
　　　　5　　　開始位置
　　　　8-5 文字数(結果は3)
の式となる。つまり、セルA4の文字列の5文字目から3文字分抽出するという意味となる。

[セルB5の式]
   =REPLACE(A5,SEARCHB("C",A5,1),SEARCHB("E",A5,1)-(SEARCHB("C",A5,1)-1),"XYZ")
[式の意味]
  「B」と「E」の文字が対象文字列(セルA5)の左端から何文字目にあるかバイト数単位で調べて、文字列「C,D,E」を「X,Y,Z」に置き換える。なお、文字はすべて半角である。
　● SEARCHB("C",A5,1)
　　　　「C」の文字があるのは左から3バイト目なので、値は 3
　● SEARCHB("E",A5,1)
　　　　「E」の文字があるのは左から5バイト目なので、値は 5
　● SEARCHB("C",A5,1)-1
　　　　「C」の文字があるのは左から3文字目なので、3-1で値は 2
上記の式で得られた値を使用してREPLACE関数に置き換えると、
　　=REPLACE(A5,3,5-2,"XYZ")
　　　　A4　　　文字列
　　　　3　　　　開始位置
　　　　5-2 　バイト数(結果は3)
　　　　"XYZ" 置換文字列
の式となる。つまり、セルA5の文字列の3バイト目から3バイト分"XYZ"に置換するという意味となる。
 

Ⅶ－2 FIND関数、FINDB関数

機　能 FIND関数　検索する文字列が他の文字列の左端から数えて最初に現れる文字数を返す。半角と全角を区別する。
FINDB関数　検索する文字列が他の文字列の左端から数えて最初に現れるバイト数を返す。全角→2 、半角→ 1 バイトで計算。
書　式 FIND(検索文字列,対象,開始位置)
FINDB(検索文字列,対象,開始位置)
説　明 文字列を検索するとき、英字の大文字と小文字は区別する。（英字の大文字と小文字を区別しないときは、SEARCH関数を使用）
検索文字列   検索する文字列を指定する。「?」 「*」などのワイルドカード文字は使用できない。
対象   検索文字列 を含む文字列を指定する。
開始位置   検索を開始する位置を指定する。対象の先頭文字（左端）から検索を開始するときは 1 を指定し、3 文字目から開始するときは 3 を指定する。開始位置 を省略すると、1 を指定したと見なされ、対象 の先頭文字から検索が始まる。
FIND関数が返す文字番号は、開始位置 からの文字数ではなく、常に 対象 の先頭 (左端) からの文字数を表す。

［実例1]：検索文字列が対象文字列中の左端から何文字目にあるかを、英数字の大文字・小文字を区別して調べる。

[セルB2の式]
   =FIND("市"A2)
[式の意味]
  「市」の文字が対象文字列(セルA2)の左端から何文字目にあるか文字数単位で調べる。
　　"市"　　検索文字列
　　  A2　　　 対象
　　　　　　　　開始位置は省略

[セルB5の式]
   =FIND("b"A5)
[式の意味]
  「b」の文字が対象文字列(セルA5)の左端から何バイト目にあるかバイト数単位で調べる。
　　"b"　 　検索文字列
　　  A5　　　 対象
　　　　　　　　開始位置は省略
 

Ⅶ－3 MATCH関数

機　能 指定した範囲の中で検索値と一致する値の存在する相対的な位置を求める
書　式 MATCH(検査値,検査範囲,照合の型)
説　明 検査値   表の中で必要な項目を検索するために使用する値を指定する。 検査値 には、実際に検索する値ではなく、検査範囲 の中で照合する値を指定する。検査値 には、数値、文字列、論理値、またはこれらの値に対するセル参照を指定できる。 
検査範囲   検査値 を含む隣接したセル範囲を指定する。検査範囲 は、配列または配列に対するセル参照を指定してもかまわない。
照合の型   -1、0、1 の数値のいずれかを指定する。照合の型 には、検査範囲 の中で 検査値 を探す方法を指定する。
1 を指定すると、検査値 以下の最大の値が検索される。このとき 検査範囲 は昇順で並べ替えておく必要がある。
0 を指定すると、検査値 に一致する値のみが検索の対象となる。このとき 検査範囲 を並べ替えておく必要はない。
-1 を指定すると、検査値 以上の最小の値が検索される。このとき 検査範囲 は降順で並べ替えておく必要がある。
照合の型 を省略すると、1 であると見なされる。
関数の実行によって得られる値は、検査範囲が行(縦)方向の場合は検査範囲の先頭行を1として検査値の存在する行番号を、検査範囲が列(横)方向の場合は検査範囲の先頭列を1として検査値の存在する列番号を返す。

［実例1]：検索範囲中での検索文字列の相対的な位置(行番号または列番号)を調べる。



[セルL2の式]
   =MATCH(K2,$D$4:$D$8,0)
[式の意味]
  検索値(K2)が「起点(B列)」の駅名の上から何番目(行番号)にあるかを調べる。
　  K2　　　　　　　検査値
　　  $D$4:$D$8　　検査範囲
　　　0　　　　　　　　照合の型(検査値に一致するもののみが検査対象)

[セルL3の式]
   =MATCH(K3,$E$3:$I$3,0)
[式の意味]
  検索値(K3)が「終点(3行目)」の駅名の左から何番目(列番号)にあるかを調べる。
　  K3　　　　　　　検査値
　　  $E$3:$I$3 　　検査範囲
　　　0　　　　　　　　照合の型(検査値に一致するもののみが検査対象)

[セルL4の式]
   =INDEX($E$4:$I$8,L2,L3)
[式の意味]
  INDEX関数により起点(セルL2)・終点(セルL3)の駅番号から区間料金を求める。
 (範囲の中で行番号(2)と列番号(4)が交わるセルの値(200)を求める。)
　　$E$4:$I$8　　　範囲
　　　L2　　　　　　　 行番号
　　　L3 列番号
　　　　　　　　　　　　領域番号は省略　

［実例2]：駅名を入力して料金を調べる（MATCH関数とINDEX関数の併用）



[セルL7の式]
   =INDEX($E$4:$I$8,MATCH(K7,$D$4:$D$8,0),MATCH(K8,$E$3:$I$3,0))
[式の意味]
 MATCH関数により起点の駅名の行番号、終点の駅名の列番号を求めて、INDEX関数により行番号と列番号が交わるセルの値を求める。
［起点の行番号を求めるMATCH関数］
　　　MATCH(K7,$D$4:$D$8,0)
　　K7　　　　　　　検査値
　　　$D$4:$D$8 　 検査範囲
　　　0　　　　　　　　照合の型(検査値に一致するもののみが検査対象)
　結果は、1となる
。
［終点の列番号を求めるMATCH関数］
　　　MATCH(K8,$E$3:$I$3,0)
　　K8　　　　　　　検査値
　　　$E$3:$I$3  　 検査範囲
　　　0　　　　　　　　照合の型(検査値に一致するもののみが検査対象)
　結果は、5となる。

 ［料金を求めるINDEX関数］
  上記の式で得られた値をINDEX関数に置き換えてみると、
　　　=INDEX($E$4:$I$8,1,7)
  という式となる。
　　$E$4:$I$8 範囲
　　　1 　　　　　　　 行番号
　　　7 列番号
　　　　　　　　　　　 領域番号は省略
　行番号1と列番号7の交わるセルの値は260である。
 

Ⅶ－4 INDEX関数

機　能 任意の範囲から、指定した行と列に交わる位置にある値を求める。
書　式 INDEX(範囲, 行番号, 列番号, 領域番号)この形式では、範囲内にある、指定したセルまたはセル範囲の参照が返される。
説　明 INDEX関数 では、まず範囲と領域番号を使って特定の範囲が選択され、次に 行番号と列番号 から特定のセルが選択される。
行番号の 1 は選択された範囲の先頭行 (上端行) を示し、列番号の 1 は選択された範囲の先頭列 (左端列) を示す。INDEX関数が返すセル参照（セルの値）は、行番号と列番号が交差する点にあるセルの参照（セルの値）となる。
範囲   1 つまたは複数のセルの参照を指定する。
範囲 として複数選択された領域を指定する場合は、範囲をかっこ () で囲み、複数選択を構成するそれぞれの領域を半角のカンマ (,) で区切る。範囲で指定した各領域が 1 行または 1 列である場合、行番号 または 列番号 はそれぞれ省略することができる。たとえば、範囲が1 行のみである場合は、INDEX(範囲,, 列番号) と指定できる。 
行番号   範囲のセル参照を返すセルの行位置を数値で指定する。(指定した範囲の先頭行を1とした相対的な番号を指定する。)
列番号   範囲のセル参照を返すセルの列位置を数値で指定する。(指定した範囲の左端列を1とした相対的な番号を指定する。)
行番号と列番号を省略すると、領域番号で指定したセル範囲内の領域が返される。
領域番号　範囲に複数の領域を指定した場合、その中の 1 つの領域を数値で指定する。指定した領域の中から 行番号と列番号が交差する点にあるセルの参照が返される。最初に選択または入力された領域の 領域番号 が 1 となり、以下、2 番目の領域は 2、3番目の領域は 3 と続く。領域番号を省略すると、1であると見なされる。

［実例2]：指定した範囲の中で、指定した行番号・列番号の交わるセルの値を求める







[セルL4の式]
   =INDEX($E$4:$I$8,K2,K3,1)
[式の意味]
　指定された範囲の行番号と列番号の交わるセルの値を求める。
　　　$E$4:$I$8　　範囲
　　　K2 　　　　　　行番号(範囲の上端行からの行数)
　　　K3 列番号(範囲の左端列からの列数)
　　　1　　　　　　　 領域番号
　　行番号=3 と 列番号=5 の交わるセルの値は 230 である。
　　 
[セルL2の式]
   =IF(K2="","",VLOOKUP(K2,$B$4:$D$8,2,0))
[式の意味]
　セルK2が空白ならば、空白を返し、そうでなければ起点の駅番号から、VLOOKUP関数により起点の駅名を求める。
　　VLOOKUP関数の意味
　　　K2　　　　　　　検査値
　　　$B$4:$D$8　　範囲
　　　2　　　　　　　　列番号(検索列・・・範囲の左端列からの列数)
　　　0　　　　　　　　検索の型(検索値 と完全に一致する値だけ検索)

[セルL3の式]
   =IF(K3="","",HLOOKUP(K3,$E$2:$I$3,2,0))
[式の意味]
　セルK3が空白ならば、空白を返し、そうでなければ終点の駅番号から、HLOOKUP関数により終点の駅名を求める。
　　HLOOKUP関数の意味
　　　K3　　　　　　　検査値
　　　$E$2:$I$3 　　範囲
　　　2　　　　　　　　行番号(検索行・・・範囲の上端行からの行数)
　　　0　　　　　　　　検索の型(検索値 と完全に一致する値だけ検索)　


Ⅶ－5 VLOOKUP関数

機　能 指定した範囲の左端列を検索し、検索値と一致した行の指定列セルの値を求める
書　式 VLOOKUP(検索値,範囲,列番号,検索の型)
説　明 検索値   範囲の左端の列で検索する値を指定する。検索値 には、値、セル参照、または文字列を指定する。
検索値が範囲の左端の列の最小値より小さい場合、エラー値 #N/A が返される。
範囲   目的のデータが含まれるテーブルを指定する。セル範囲の参照、または List、Database のような名前を指定する。範囲 の左端の列のデータは、文字列、数値、論理値のいずれでもかまわない。英字の大文字と小文字は区別されない。
範囲の左端列には検索値を含む列を指定する。
列番号   範囲内で目的のデータが入力されている列を、左端からの列数で指定する。列番号 に 1 を指定すると、範囲 の左端の列の値が返され、列番号に 2 を指定すると、範囲の左から 2 列目の値が返される。
検索の型　検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、論理値で指定する。
検索の型に TRUE を指定し、検索値が見つからない場合は、検索値未満で最も大きい値が使用される。TRUE を指定した場合、範囲の左端の列のデータは、昇順に並べ替えておく必要がある。そうしないと、正しく計算が行われない。
検索の型 にFALSE を指定すると、検索値 と完全に一致する値だけが検索され、見つからない場合は エラー値 #N/A が返される。FALSE を指定した場合は、範囲のデータを並べ替えておく必要はない。

［実例1]：コード入力結果により値を求める。

[セルB10の式]
   =VLOOKUP(A10,$A$3:$C$7,2,0))
[式の意味]
　セルA10に入力された値により、上表から区名を求める。
　　　A10　　　　　　検査値
　　　$A$3:$C$7　 範囲
　　　2　　　　　　　　列番号(検索列・・・範囲の左端列からの列数)
　　　FALSE　　　　検索の型(検索値 と完全に一致する値だけ検索・・・　0 としてもよい)

:検査値(2)に対応するデータを範囲($A$3:$C$7)の左端列(A列)で検索し、一致するデータがあれば指定された列番号(2)の値(東区)を返す。
　セルC10の「件数」を求めるには、列番号を 3 にすればよい。(上表内で「件数」欄は左端から3列目にある。)

［実例2]：点数から成績ランクを求める

　右図が成績表、左図が成績結果による評価を求めるためのランク表である。
ランク表の「点数範囲」は、ランクの内容を分かり易くするために設定したもので、関数式の中では使用しない。



[セルG3の式]
   =VLOOKUP(F3,$B$3:$C$7,2,))
[式の意味]
　セルF3に入力された値により、ランク表から成績評価を求める。
　　　F3   　　　　　　検査値
　　　$B$3:$C$7 　 範囲
　　　2　　　　　　　　列番号(検索列・・・範囲の左端列からの列数)
　　　TRUE　　　　　検索の型(検索値が見つからない場合は、検索値未満で最も大きい値を検索)

　ランク表には、検索値(95)と一致する値が見つからないが、検索の型をTRUEに指定してあるので、検索値を上回らない近似値(80)を検索結果とし、列番号(2)で指定した列の値(E)を返す。
　なお、検索の型をTRUEに指定したときは、範囲の左端列(検索データ欄・・・「最低点数」)は昇順にソートされていなければならない。
 

Ⅶ－6 HLOOKUP関数

機　能 指定した範囲の先頭行を検索し、検索値と一致した列の指定行セルの値を求める
書　式 HLOOKUP(検索値,範囲,行番号,検索の型)
説　明 検索値   範囲 の上端行で検索する値を指定する。検索値 には、値、セル参照、または文字列を指定する。検索値が範囲の左端の列の最小値より小さい場合、エラー値 #N/A が返される。
範囲   目的のデータが含まれるテーブルを指定する。セル範囲の参照、または List、Database のような名前を指定する。
範囲の上端行のデータは、文字列、数値、論理値のいずれでもかまわない。英字の大文字と小文字は区別されない。
行番号   範囲内で目的のデータが入力されている行を、上端からの行数で指定する。行番号 に 1 を指定すると、範囲の上端行の値が返され、行番号 に 2 を指定すると、範囲 の上から 2 行目の値が返される。
検索の型　検索値と完全に一致する値だけを検索するか、その近似値を含めて検索するかを、論理値で指定する。
検索の型に TRUE を指定し、検索値が見つからない場合は、検索値未満で最も大きい値が使用される。TRUE を指定した場合、範囲の上端行のデータは、昇順に並べ替えておく必要がある。そうしないと、正しく計算が行われない。
検索の型 にFALSE を指定すると、検索値 と完全に一致する値だけが検索され、見つからない場合は エラー値 #N/A が返される。FALSE を指定した場合は、範囲のデータを並べ替えておく必要はない。

［実例1]：コード入力による費目の表示と、該当費目欄への金額の自動振分。

　上表は、B欄に費目コードを入力するとC欄に費目名称を表示し、E欄に金額を入力すると該当費目欄に入力金額を自動振分して表示するものである。

[セルC4の式]
   =HLOOKUP(B4,$D$1:$K$2,2,0))
[式の意味]
　セルC4に入力された値により、費目欄から費目名称を求める。
　　　B4  　　　　　　検査値
　　　$D$1:$K$2 　 範囲
　　　2　　　　　　　　行番号(検索行・・・範囲の上端行からの行数)
　　　FALSE 　　　　検索の型(検索値 と完全に一致する値だけ検索・・・　0 としてもよい)

:検査値(103)に対応するデータを範囲($D$1:$K$2)の上端行(1行目)で検索し、一致するデータがあれば指定された行番号(2)の値(光熱費)を返す。

[セルI4の式]
   =IF($B4=I$1,$E4,"")
[式の意味]
　IF関数により費目を振り分ける。セルC4に入力された費目コード(103)がセルI1のコード(103)と等しければセルE5の金額(5,000)を表示する。そうでなければ空白とする。
　　$B4=I$1　　　論理式
　　$E4　　　　　　真の場合
　　　""　　　　　　　偽の場合
 

Ⅶ－7 DGET関数

機　能 データベースの指定されたフィールド列を検索し、条件を満たすレコード（行）から1つの値を抽出(抽出データは1件のみ)
書　式 DGET(データベース範囲,フィールド,条件範囲)
説　明 データベース範囲は、リストまたはデータベースを構成するセル範囲を指定する。データベース範囲は、行 (レコード) と列 (フィールド)にデータを関連付けたリストである。リストの先頭の行には、各列のラベル（項目名）がある必要がある。
フィールドは、 関数の中で計算する列を指定する。フィールド には、列項目名（半角のダブル クォーテーション (") で囲む）を指定する方法と、リストでの列の位置を示す番号、例えば、先頭の列を示す場合は 1 、2 番目の列を示す場合は 2 を指定する方法がある。 
条件範囲は、指定した検索条件が設定されているセル範囲を指定する。列ラベルと検索条件を指定するセルが少なくとも 1 つずつ含まれている場合は、条件範囲 に任意のセル範囲を指定することができる。


　検索条件式の指定方法
●同じ行の列と列の関係は、AND条件(～かつ～)となる。
●同じ列の行と行の関係は、OR条件(～または～)となる。
●項目名が「年齢」で、20歳以上40歳未満のように、ある項目のデータ範囲を求めるには、左図の項目名1、項目名2の項目名をそれぞれ「年齢」とし、項目名1の下に「>=20」を、項目名2の下に「<40」を入力する。
データベース関数は、計算内容が異なるのみで、書式はどれも同じである。
［実例1]：品名が「B」で、かつ、単価が1,500円を超える商品の金額を抽出する。

  [セルF7の式]
   =DGET($A$2:$D$7,"金額",$F$3:$G$4)
 または
   =DGET($A$2:$D$7,4,$F$3:$G$4)
[式の意味]
　検索条件範囲で指定した、品名がBで単価が1,500円以上に該当する金額を左図のデータベース範囲から抽出する。
　フィールド指定方法は、フィールド名又はデータベース範囲の左端列からの列番号のどちらでもよい。
　　　$A$2:$D$7  　　　データベース範囲
　　　"金額" 　 　　　　フールド
　　　$F$3:$G$4　　　　条件範囲

Home