Excel(エクセル)基本講座：MATCH関数の使い方

範囲内で値を検索し、見つかった値が何番目に位置するかを数値で返す。　　　topへ
　マッチ
=MATCH(検索値,範囲 [,照合の型])
　　　照合の型：「0」完全に一致する値を検索する
　　　　　　　　「1」一致するものがない場合、検索値以下の最大の値を検索する。（データは昇順に並んでいることが必要）
　　　　　　　　「-1」一致するものがない場合、検索値よりも大きい最小の値を検索する。（データは降順に並んでいることが必要）

[A]　MATCH関数で完全に一致する値を検索する

【問題】

• 到着地の名古屋(C9セル)は運賃表の左から何番目ですか?E9セルに数式を入れなさい。
• 出発地の熊本(C10セル)は運賃表の上から何番目ですか?E10セルに数式を入れなさい。

• 	B	C	D	E	F
  2	運賃表
  3		東京	名古屋	大阪
  4	鹿児島	50,000	40,000	35,000
  5	宮崎	45,000	35,000	30,000
  6	熊本	40,000	30,000	25,000
  7	福岡	30,000	20,000	15,000
  8
  9	到着地	名古屋	は左から	2	番目です。
  10	出発地	熊本	は上から	3	番目です。
  11

【解答例】

• 到着地（C9セル)は範囲（C3:E3）からMATCH関数の完全一致で求めます。
• 出発地（C10セル)は範囲（B4:B7)からMATCH関数の完全一致で求めます。

(別解)　VLOOKUP関数やINDEX関数とMATCH関数を組み合わせて求める方法

• VLOOKUP関数やINDEX関数と組み合わせて運賃を求めることができます。(下の方に説明があります)
• (注)VLOOKUP関数、INDEX関数のどちらを使っても同じ結果が得られますが、VLOOKUP関数とINDEX関数とで中に使うMATCH関数の範囲が異なります。

• 	B	C	D	E	F
  2	運賃表
  3		東京	名古屋	大阪
  4	鹿児島	50,000	40,000	35,000
  5	宮崎	45,000	35,000	30,000
  6	熊本	40,000	30,000	25,000
  7	福岡	30,000	20,000	15,000
  8
  9	到着地	名古屋	は左から	=MATCH(C9,C3:E3,0)	番目です。
  10	出発地	熊本	は上から	=MATCH(C10,B4:B7,0)	番目です。
  11	運賃は	=VLOOKUP(C10,B4:E7,MATCH(C9,B3:E3,0),FALSE)
  12	運賃は	=INDEX(C4:E7,MATCH(C10,B4:B7,0),MATCH(C9,C3：E3,0)）

MATCH関数で検索値以下の最大値を検索する。

• MATCH関数の照合の型に「1」を使います。
• 検索範囲（B3:E3)の20〜80の中で検索値（B8)の45以下で、最も近い値は（C3)の40であり、
• （C3)の40は検索範囲（B3:E3)の左から2番目にあるので、答えは2となります。
• 

MATCH関数で検索値よりも大きい最小の値を検索する。

• MATCH関数の照合の型に「-1」を使います。
• 検索範囲（B3:E3)の20〜80の中で検索値（B8)の85以上で、最も近い値は（B3)の100であり、
• （B3)の100は検索範囲（B3:E3)の左から1番目にあるので、答えは1となります。
• 

VLOOKUP とMATCH の複合　　　topへ
　　　縦横の検索表から、一致する値を取り出すのに、VLOOKUP関数とMATCH関数を組み合わせて使う方法です。

【問題】VLOOKUP関数とMATCH関数を使って運賃を求めなさい。

• 	B	C	D	E
  2	運賃表
  3		東京	名古屋	大阪
  4	鹿児島	50,000	40,000	35,000
  5	宮崎	45,000	35,000	30,000
  6	熊本	40,000	30,000	25,000
  7	福岡	30,000	20,000	15,000
  8
  9	到着地	名古屋
  10	出発地	熊本
  11	運賃は	30,000

【解答例】

• 出発地熊本（C10セル)の行位置をVLOOKUP関数で求めます。
  VLOOKUP関数は左端の列を検索するので、範囲は『B7:E7』となります。
  =VLOOKUP(C10,B4:E7,○○,FALSE)
• 到着地の名古屋（C9セル)が何列目になるのかMATCH関数で求めます。
  (注)VLOOKUP関数は検索列(一番左の列）を含む範囲になるので、MATCH関数の範囲は『B3:E3』とします。
  MATCH(C9,B3:E3,0）　
• この両方の式を合わせて完成です。
  =VLOOKUP(C10,B4:E7,MATCH(C9,B3:E3,0),FALSE)

• 	B	C	D	E
  2	運賃表
  3		東京	名古屋	大阪
  4	鹿児島	50,000	40,000	35,000
  5	宮崎	45,000	35,000	30,000
  6	熊本	40,000	30,000	25,000
  7	福岡	30,000	20,000	15,000
  8
  9	到着地	名古屋
  10	出発地	熊本
  11	運賃は	=VLOOKUP(C10,B4:E7,MATCH(C9,B3:E3,0),FALSE)

VLOOKUP と MATCH および INDIRECT の複合　　　　topへ
　参照する表が２種類ある場合、切り替えて使いたい時にINDIRECT関数が使えます。
　インダイレクト
=INDIRECT(参照文字列,参照形式)
　　　　　参照文字列で入力したセル、セル範囲名を計算式で参照できるようにする。
　　　　　参照形式：TRUE　または省略した場合、A1形式でのセル参照。
　　　　　　　　　　 　FALSE　R1C1形式でのセル参照。

【料金計算の例】

• 料金表が【大人】と【子供】の２種類あります。計算時にこれらの表を切り替えて使う方法です。

1. 大人の料金表のE4:H7の範囲に【大人】と名前をつけます。
2. 大人の料金表の到着地行E3:H3に【大人到着地】と名前をつけます。
3. 子供の料金表のE11:H14の範囲に【子供】と名前をつけます。
4. 子供の料金表のタイトル行E10:H10に【子供到着地】と名前をつけます。
5. C5に数式　=VLOOKUP(C2,INDIRECT(B4),MATCH(C3,INDIRECT(B4&"到着地"),0),FALSE)*C4　を、入力します。

【説明】

• INDIRECT(B4&"到着地")：B4セルの文字列が『子供』であれば『子供到着地』を参照します。
• MATCH(C3,INDIRECT(B4&"到着地"),0),FALSE)：でC3セルの到着地『名古屋』の列位置を返します。
• VLOOKUP(C2,INDIRECT(B4)・・・：B4セルの文字列『子供』ならば、セル範囲『子供』からC2セルの出発地『宮崎』を検索し、　
• MATCH関数で求めた列位置の一人分の料金を出します。そして、人数を掛けて料金を計算しています。

(ステップアップ)入力規則を使って入力値を制限する

• 入力ミスを防ぐ＆マウス操作のみにするため、C2,C3,B4セルには入力規則をつけます。
• 【データ】→【入力規則】を選択し、
  • C2には【リスト】で【E4:E７】
  • C3には【リスト】で【F3:H3】
  • B4には【リスト】で【大人，子供】　
  とすれば、リストからの選択となり、入力しなくても良いです。
• 更に、C4にも【リスト】で【1，2，3，4，5，6】としておけば、マウス操作のみで料金が表示されます。

PageViewCounter

Since2006/2/27