Excel(エクセル)基本講座：表引き関数(検索・行列の関数・VLOOKUP関数)

• 他のシートやセル範囲に入力された対照表などからデータを読み込んだり、配置したりするのに使用できます。
  エクセルで最も必要とされる関数でありながら理解しがたい関数でもあります。
• 検索範囲が左端列ならVLOOKUP関数、行頭ならHLOOKUP関数、任意の列/行ならINDEX関数といった具合に使い分ける必要があります。
• 文字を検索する関数(FIND関数,SEARCH関数)についてはExcel(エクセル)基本講座：文字列の関数(1)で説明しています。

列方向の検索(VLOOKUP関数)	行方向の検索(HLOOKUP関数)
昇順データの検索(LOOKUP関数)	何番目に位置するかを検索(MATCH関数)
行位置と列位置を指定して検索(INDEX関数)	セル,セル範囲名を計算式で参照(INDIRECT関数)
指定された行/列数だけシフトする(OFFSET関数)	セルの参照を文字列で返す(ADDRESS関数)
VLOOKUP関数とMATCH関数の複合	値のリストから指定した値を取出す(CHOOSE関数)
リンクの設定(HYPERLINK関数)	行番号・列番号を返す(ROW関数・COLUMN関数)
セルのデータ型を調べる(TYPE関数)

範囲の左端の列で、検索値を検索し、値の見つかった行の、列位置のセルの内容を表示する。　　　topへ
　ブイルックアップ
=VLOOKUP(検索値,範囲,列位置,検索の型)
　　　検索の型：「TRUE」もしくは[省略]（「1」と入力してもOK)
　　　　　　　　検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。
　　　　　　　　(注)検索範囲の値は昇順に並んでおく必要があります。
　　　検索の型：「FALSE」(「0」と入力してもOK)
　　　　　　　　検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。
　　　　　　　　文字列の検索に適します。

[A]　検索値が文字列の場合　(検索の型：FALSE)
　　　　(注)検索値が文字列の場合で昇順に並んでいる時は検索の型を[TRUE]でOKです。
　　　　　　ただし、昇順の並べ替えで基準は[ふりがな]を使用してはいけません。日本語の場合には要注意です。

• 商品一覧表（H3:J6)があります。
• 見積書の品番を入力すると商品名と単価が自動的に入力されるようにC4:D6セルに数式を入れなさい。

• 	B	C	D	E	F	G	H	I	J
  2		見積書					商品一覧表
  3	品番	商品名	単価	数量	小計		品番	商品名	単価
  4	B01	ラジオ	5,000	1	5,000		A01	テレビ	50,000
  5	C01	ビデオデッキ	20,000	2	40,000		B01	ラジオ	5,000
  6	A01	テレビ	50,000	3	150,000		C01	ビデオデッキ	20,000
  7				合計	195,000

【解答例1】

• C4セルの場合
  • =VLOOKUP(B4,$H$4:$J$6,2,FALSE)　または　=VLOOKUP(B4,$H$4:$J$6,2,0)となります。
    • 検索値：品番の入力されているB4セルになります。
    • 範囲：商品一覧のデータ部分H4:J6を絶対参照で指定します。
      （範囲を絶対参照にするのはC4セルの数式をC5,C6セルにコピーする時値が変化しないようにするためです。）
    • 列位置：範囲の2列目ですので「2」とします。
    • 検索の型：文字の検索や固有品番などの検索では[FALSE] にします。[FALSE]のかわりに[0]でもOK。
• ”B4セルの『B01』を範囲『H4:J6』の左端の列から探し、完全に一致した値が有ったら2列目の値を返す”という意味になります。

	B	C	D	E	F	G	H	I	J
2		見積書					商品一覧表
3	品番	商品名	単価	数量	小計		品番	商品名	単価
4	B01	=VLOOKUP(B4,$H$4:$J$6,2,FALSE)	=VLOOKUP(B4,$H$4:$J$6,3,FALSE)	1	5,000		A01	テレビ	50,000
5	C01	=VLOOKUP(B5,$H$4:$J$6,2,FALSE)	=VLOOKUP(B5,$H$4:$J$6,3,FALSE)	2	40,000		B01	ラジオ	5,000
6	A01	=VLOOKUP(B6,$H$4:$J$6,2,FALSE)	=VLOOKUP(B6,$H$4:$J$6,3,FALSE)	3	150,000		C01	ビデオデッキ	20,000
7				合計	195,000

エラー処理　　　　topへ

• 検索値が検索する範囲にないときはエラー#N/Aが表示されます。
  #N/Aが表示されないようにするにはいろいろな方法があります。

• ISNA関数やISERROR関数で処理する

  • VLOOKUP関数がエラーとなるか否かを調べてIF関数で対処します。
  • エラーの表示を無くするには　IF関数とISNA関数を使います。
    C4セルの例：=IF(ISNA(VLOOKUP(B4,$H$4:$J$6,2,FALSE)),"",VLOOKUP(B4,$H$4:$J$6,2,FALSE))
  • ISERROR関数でもOKです。
    =IF(ISERROR(VLOOKUP(B4,$H$4:$J$6,2,FALSE)),"",VLOOKUP(B4,$H$4:$J$6,2,FALSE))

• COUNTIF関数で処理する

  • 検索値が検索範囲にあるか否かをCOUNTIF関数で調べてIF関数で対処します。
  • =IF(COUNTIF($H$4:$H$6,B7)=0,"",VLOOKUP(B7,$H$4:$J$6,2,FALSE))

(Step Up)共通の数式に変更する

• C4セルの数式を　=VLOOKUP($B4,$H$4:$J$6,COLUMN(B1),FALSE)　とするとひとつの式で済みます。
  検索値を列固定の複合参照にし、列位置をCOLUMN関数で与えています。
  (D4セルへフィルハンドルをドラッグしてD4セルへコピーし、そのまま、フィルハンドルをC6:D6までドラッグしてコピーします。)
• (説明)
  C4セルの列位置「2」を、D4セルでは「3」になるように細工をします。
  COLUMN関数を使うと、右方向へコピーして値が1増加するので「2」を返す「COLUMN(B1)」とすれば良さそうです。
  この場合重要なのは「B」=「2」なのでCOLUMN(B100)でも良く、「B1」としたのは見た目だけ？のことです。

検索範囲に名前を定義する場合　　　　topへ

1. セル範囲 H4:J6 に『商品一覧表』と名前を定義します。
   （ポイント：検索列が一番左になるようにセル範囲を選択して、名前を定義します。）
   

   (A) メニューバーからの方法	(B)　名前ボックスを使う方法
   セル範囲 H4:J6 をドラッグして選択します。 メニューバーの【挿入】→【名前】→【定義】を選択します。 【名前の定義】ダイアログの名前に『商品一覧表』と入力し、【OK】ボタンをクリックします。 これで、セル範囲に名前が定義されます。	セル範囲 H4:J6 をドラッグして選択します。 【名前ボックス】に『商品一覧表』と入力し、　【Enter】 キーを押します。 これで、セル範囲に名前が定義されます。

2. C4セルの数式は　=VLOOKUP(B4,商品一覧表,2,FALSE)　　D4セルの数式は　=VLOOKUP(B4,商品一覧表,3,FALSE)　とします。
   • 列位置をCOLUMN関数で置き換え、C4セル=VLOOKUP(B4,商品一覧表,COLUMN(B1),FALSE)とすれば、D4セルへコピーすればOK。
3. C4：D4セルを選択し、オートフィルでC6:D6まで数式をコピーして完成です。

	B	C	D	E	F	G	H	I	J
2		見積書					商品一覧表
3	品番	商品名	単価	数量	小計		品番	商品名	単価
4	B01	=VLOOKUP(B4,商品一覧表,2,FALSE)	=VLOOKUP(B4,商品一覧表,3,FALSE)	1	5,000		A01	テレビ	50,000
5	C01	=VLOOKUP(B5,商品一覧表,2,FALSE)	=VLOOKUP(B5,商品一覧表,3,FALSE)	2	40,000		B01	ラジオ	5,000
6	A01	=VLOOKUP(B6,商品一覧表,2,FALSE)	=VLOOKUP(B6,商品一覧表,3,FALSE)	3	150,000		C01	ビデオデッキ	20,000
7				合計	195,000

　(上の表はExcelへコピーすると #NAME?　エラーとなります。　セル範囲 H4:J6 に『商品一覧表』と名前を定義するとエラーが解消されます。)

[B]　検索値が数値範囲の場合　(検索の型：TRUE)　　　　　topへ
　　　　(注)検索値が昇順に並んでいる必要があります。
　　　　　　検索値が数値の範囲ではなく、その値に対応する時は[FALSE]とします。

【問題2】成績の評価を付けたいと思います。右の評価一覧表（G4:J6)にしたがって、D4:E6セルに数式を入力しなさい。

• 	B	C	D	E	F	G	H	I	J
  2							評価一覧表
  3	名前	得点	評価A	評価B		得点		評価A	評価B
  4	今田	50	△	良		0	〜	×	不可
  5	河野	20	×	不可		40	〜	△	良
  6	山田	70	○	優		70	〜	○	優

【解答例2-1】　

• 解答例1とほとんど同じですが、数値の範囲での検索になりますので、検索の型を[TRUE]または「1」もしくは[省略]します。
  • 検索の型を省略：=VLOOKUP(C4,$G$4:$J$6,3)
  • 検索の型をTRUE：=VLOOKUP(C4,$G$4:$J$6,3,TRUE)
  • 検索の型を1 　：=VLOOKUP(C4,$G$4:$J$6,3,1)
  • 上の3つは同じ結果になります。
• この場合、得点欄の値以上の意味になります。得点欄（G列)は昇順に並んでいることが必要です。
  

  	B	C	D	E	F	G	H	I	J
  2							評価一覧表
  3	名前	得点	評価A	評価B		得点		評価A	評価B
  4	今田	50	=VLOOKUP(C4,$G$4:$J$6,3)	=VLOOKUP(C4,$G$4:$J$6,4)		0	〜	×	不可
  5	河野	20	=VLOOKUP(C5,$G$4:$J$6,3)	=VLOOKUP(C5,$G$4:$J$6,4)		40	〜	△	良
  6	山田	70	=VLOOKUP(C6,$G$4:$J$6,3)	=VLOOKUP(C6,$G$4:$J$6,4)		70	〜	○	優

(解答例2-2）検索範囲に名前を定義して使う方法

• セル範囲 G4:J6 に『評価一覧』と名前を定義すると、
  • D4セルの数式は =VLOOKUP(C4,評価一覧,3)
  • E4セルの数式は =VLOOKUP(C4,評価一覧,4)
  となります。
• D4:E4 セルを選択し、オートフィルでD6:E6セルまで数式をコピーして完成です。
• 名前の定義方法：G4:J6を選択し、挿入→名前→定義　で名前に「評価一覧」と入力する。
• 別解：D4セル=VLOOKUP(C4,評価一覧,COLUMN(C1))　として、E4セルへコピーしてもOKです。

[C]　検索表を使用しない方法　　(数式中に検索範囲のデータを書く方法)　　　　topへ

• (解答例2-3)　シートに検索表を作成しない方法
  • 数式内に対応する値を並べて記述します。
  • D4セルの数式　=VLOOKUP(C4,{0,"×","不可";40,"△","良";70,"○","優"},2)
  • E4セルの数式　=VLOOKUP(C4,{0,"×","不可";40,"△","良";70,"○","優"},3)
  • D4:E4 セルを選択し、オートフィルでD6:E6セルまで数式をコピーして完成です。

[D]　検索値が見つからず #N/Aエラー が返される場合　　　topへ

• シート上で検索値が検索範囲内の値と同じ値が入力されているように見えているのに、エラーとなるときがあります。
  

1. 検索値にスペースが紛れ込んでいないか確認してみます。特に最後にスペースが含まれる場合は気づきにくいです。
   • スペースの削除は【編集】→【置換】を使い、半角スペースと全角スペースを取り除きます。
     検索する文字列：半角または全角スペースを入力します。(画面では見た目の変化はないです)
     置換後の文字列：何も入力しません。
     『半角と全角を区別する』にチェックは入れません。(【オプション】をクリックすると表示されます)
   • 【すべて検索】ボタンをクリックし、【すべて置換】をクリックすると、一度に置換されます。
     確認しながら置換するときは、【次を検索】と【置換】を使います。
     
2. 他のソフトなどで作成したデータで空白文字CHAR(160)が紛れ込んでくることがあります。
   • CHAR(160)はTRIM関数やCLEAN関数では取り除くことができないので、SUBSTITUTE関数で取り除きます。その後、形式を選択して貼り付けの「値」でデータを置き換えます。
     • =SUBSTITUTE(A1,CHAR(160),"")
3. 表示形式は文字列であるが、データは数値と認識されていることがあります。
   この様な場合は、=TYPE(A1)のようにTYPE関数を使ってセルのデータ形式を確認します。
   • 数値データを文字列に変更したいときは、【データ】→【区切り位置】のウィザードで「文字列」を指定します。
   • 文字列の数字を数値に変更したい場合は、どこかのセルに数値の「1」を入力しコピー、変更したいセルを選択して形式を選択して貼り付けで「乗算」にチェックを入れて【OK】とします。

   • セルのデータ型を調べる　　　topへ
     　タイプ
     =TYPE(データタイプ)
     　　　数値は「1」 、テキストは「2」 、論理値は「4」
     　　　エラー値は「16」 、配列は「64」を返します。

     1. C列にD列のようなそれぞれの値や数式を入力します。
     2. C5,C6はそれぞれの結果「FALSE」「#VALUE!」が表示されます。
     3. C7はTYPE関数の戻り値「64」が表示され、E7は戻り値「64」は数値なので、「1」が返されます。
        

        	B	C	D	E	F
        2	データタイプ	入力データ	C列の内容	TYPE関数の戻り値	E列の数式
        3	数値	10	←10	1	←=TYPE(C3)
        4	テキスト	文字です	←文字です	2	←=TYPE(C4)
        5	論理値	FALSE	←=A1="moji"	4	←=TYPE(C5)
        6	エラー	#VALUE!	←=B1/B2	16	←=TYPE(C6)
        7	配列	64	←=TYPE({1,2;3,4})	1	←=TYPE(C7)

[E] 検索条件が2列(複数)ある場合　　　topへ

1. 検索条件を1つのセルにまとめます。(作業列を利用します。)
   • B3セルに=C3&"_"&D3　とC3セルとD3セルの値をつないで1つにします。B８セルまで数式をコピーします。
     なお、区切り文字にはデータに使用されない文字を使います。
     ここでは「_」アンダーバーを使ってみました。
     

     	B	C	D	E	F	G	H
     2		アーチスト	種別	価格		アーチスト	吉村拓郎
     3	池田陽水_LP	池田陽水	LP	2500		種別	CD
     4	池田陽水_CD	池田陽水	CD	3000		価格	2900
     5	池田陽水_DVD	池田陽水	DVD	4000
     6	吉村拓郎_LP	吉村拓郎	LP	2600
     7	吉村拓郎_CD	吉村拓郎	CD	2900
     8	吉村拓郎_DVD	吉村拓郎	DVD	4100

   • H4セルには=VLOOKUP(H2&"_"&H3,$B$3:$E$8,4,FALSE)とします。
2. SUMPRODUCT関数を使う事もできます。この場合B列は不要です。
   • 上の例ではH2セルに=SUMPRODUCT((C3:C8=H2)*(D3:D8=H3),E3:E8)とします。
   • なお、求める値が数値である場合は上のようにSUMPRODUCT関数が使えます。
     しかし、文字列を求める場合は使用できませんので、上記のように作業列を利用しVLOOKUP関数を使います。

範囲の上端の行で、検索値を検索し、値の見つかった列の、行位置のセルの内容を表示する。　　　topへ
　エイチルックアップ
=HLOOKUP(検索値,範囲,行位置 [,検索の型])
　　　検索の型：「TRUE」もしくは　[省略]（「1」と入力してもOK)
　　　　　　　　検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。
　　　　　　　　(注)検索範囲の値は昇順に並んでおく必要があります。
　　　検索の型：「FALSE」(「0」と入力してもOK)
　　　　　　　　検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。
　　　　　　　　文字列の検索に適します。

【問題】

• 右の評価一覧表にしたがって、D4:E6セルの評価を表示する数式を入れなさい。
• なお、H3セルの「0」は0以上40未満　I3セルの「40」は40以上70未満　J3セルの「70」は70以上を意味します。

• 	B	C	D	E	F	G	H	I	J
  2						評価一覧表
  3	名前	得点	評価A	評価B		得点	0	40	70
  4	今田	50	△	良		評価A	×	△	○
  5	河野	20	×	不可		評価B	不可	良	優
  6	山田	70	○	優

【解答例1】

• VLOOKUP関数と同じです。参照表の行と列が入れ替わっています。今回はTRUEを入れました。(省略または1としてもOKです)

• 	B	C	D	E	F	G	H	I	J
  2						評価一覧表
  3	名前	得点	評価A	評価B		得点	0	40	70
  4	今田	50	=HLOOKUP(C4,$H$3:$J$5,2,TRUE)	=HLOOKUP(C4,$H$3:$J$5,3,TRUE)		評価A	×	△	○
  5	河野	20	=HLOOKUP(C5,$H$3:$J$5,2,TRUE)	=HLOOKUP(C5,$H$3:$J$5,3,TRUE)		評価B	不可	良	優
  6	山田	70	=HLOOKUP(C6,$H$3:$J$5,2,TRUE)	=HLOOKUP(C6,$H$3:$J$5,3,TRUE)

【解答例2】検索範囲に名前を定義して使う方法

• セル範囲 H3:J5 に『評価一覧』と名前を定義すると、
  • D4セルの数式は =HLOOKUP(C4,評価一覧,2)
  • E4セルの数式は =HLOOKUP(C4,評価一覧,3)
  となります。
• D4:E4 セルを選択し、オートフィルでD6:E6セルまで数式をコピーして完成です。
• 別解：D4セルに=HLOOKUP(C4,評価一覧,COLUMN(B1))としてもOKです。

LOOKUP 　　　　　　　　　　　　　topへ
　ルックアップ
=LOOKUP(検査値,検査範囲,対応範囲)　　[ベクトル形式]
　　　対応範囲：1行/1列のみのセル範囲を指定。検査範囲と同じサイズであること。
　　(注)検査範囲の値は昇順に並べておく必要があります。英字の大文字と小文字は区別されません。
　　　　また、日本語文字を並べ替える場合「ふりがなを使用しない」で並べ替えます。
=LOOKUP(検査値,配列)　[配列形式]

【問題】成績の評価を付けたいと思います。右の評価一覧表（G4:J6)にしたがって、D4:E6セルに数式を入力しなさい。

• 	B	C	D	E	F	G	H
  2						評価一覧表
  3	名前	得点	評価A		得点		評価A
  4	今田	50	△		0	〜	×
  5	河野	20	×		40	〜	△
  6	山田	70	○		70	〜	○

【解答例】

• LOOKUP関数の場合、得点欄の値以上の意味になります。得点欄（G列)は昇順に並んでいることが必要です。

• 	B	C	D	E	F	G	H
  2						評価一覧表
  3	名前	得点	評価A		得点		評価A
  4	今田	50	=LOOKUP(C4,$F$4:$F$6,$H$4:$H$6)		0	〜	×
  5	河野	20	=LOOKUP(C5,$F$4:$F$6,$H$4:$H$6)		40	〜	△
  6	山田	70	=LOOKUP(C6,$F$4:$F$6,$H$4:$H$6)		70	〜	○

【別解】配列形式を使うと、D4セルは以下のように書くこともできます。

• =LOOKUP(C4,$F$4:$H$6)
  (範囲の右端の列の値を返す。)
• =LOOKUP(C4,{0,"×";40,"△";70,"○"})
  =LOOKUP(C4,{0,40,70;"×","△","○"})

(参考)　LOOKUP関数のその他の用途（文字列から数値を取り出す方法など)

範囲内で値を検索し、見つかった値が何番目に位置するかを数値で返す。　　　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となります。
• 

範囲の中から、行位置と列位置を指定して値を取り出す　　topへ
　インデックス
=INDEX(範囲,行位置,列位置 [,領域番号])
　　　　領域番号は、離れた場所にある複数の範囲を指定する時に使う。

【問題1】INDEX関数と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

【解答1例】

• INDEX関数での検索範囲はデータの範囲C4:E7で、
• 行位置はMATCH関数で出発地の熊本（C10)の位置をB4:B7の範囲から検索し、
• 列位置はMATCH関数で到着地名古屋（C9)の位置はC3:E3の範囲から検索します。
• MATCH関数の照合の型は「完全に一致」ですので「0」を指定しています。

• 	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	運賃は	=INDEX(C4:E7,MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0))

【問題2】大人運賃表と子供運賃表を使って運賃を求めなさい

• 	B	C	D	E	F	G	H	I	J
  2	運賃表 （A:大人)					運賃表 （B:子供)
  3		東京	名古屋	大阪			東京	名古屋	大阪
  4	鹿児島	50,000	40,000	35,000		鹿児島	25,000	20,000	17,500
  5	宮崎	45,000	35,000	30,000		宮崎	22,500	17,500	15,000
  6	熊本	40,000	30,000	25,000		熊本	20,000	15,000	12,500
  7	福岡	30,000	20,000	15,000		福岡	15,000	10,000	7,500
  8
  9	到着地	名古屋
  10	出発地	熊本
  11	大人/子供	子供
  12	運賃は	15,000

【解答2例】

• =INDEX(範囲,行位置,列位置 ,領域番号)の形式を使います。
• 範囲に(C4:E7,H4:J7)　と大人運賃表と子供運賃表の2つの領域を指定しています。
• 領域番号の指定を　MATCH(C11,{"大人","子供"},0)　として、大人なら「1」、子供なら「2」が返るようにしています。

• 	B	C	D	E	F	G	H	I	J
  2	運賃表 （A:大人)					運賃表 （B:子供)
  3		東京	名古屋	大阪			東京	名古屋	大阪
  4	鹿児島	50,000	40,000	35,000		鹿児島	25,000	20,000	17,500
  5	宮崎	45,000	35,000	30,000		宮崎	22,500	17,500	15,000
  6	熊本	40,000	30,000	25,000		熊本	20,000	15,000	12,500
  7	福岡	30,000	20,000	15,000		福岡	15,000	10,000	7,500
  8
  9	到着地	名古屋
  10	出発地	熊本
  11	大人/子供	子供
  12	運賃は	=INDEX((C4:E7,H4:J7),MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0),MATCH(C11,{"大人","子供"},0))

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】としておけば、マウス操作のみで料金が表示されます。

OFFSET　　　　　　topへ
　オフセット
=OFFSET(基準,行数,列数 [,高さ,幅]）
　 　　　基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセル
　　　　　またはセル範囲の参照 (オフセット参照) を返します。

【問題】OFFSET関数と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

【解答例】

• 基準のセルを「B3」として、出発地を「B4:B7」と一致する行数、到着地を「C3:E3」と一致する列数を求めます。

• 	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	運賃は	=OFFSET(B3,MATCH(C10,B4:B7),MATCH(C9,C3:E3,0))

ADDRESS　　　　　　topへ
　アドレス
=ADDRESS(行番号,列番号 [,参照の型,参照形式,シート名])
　 　　　行番号と列番号で指定したセルの参照を文字列で返します。
　　　　　行番号： セル参照に使用する行番号を指定します。
　　　　　列番号： セル参照に使用する列番号を指定します。
　　　　　参照の型： セル参照の種類を指定します。
　　　　　　参照の型 結果として返される参照形式
　　　　　　　1 または省略 ：絶対参照
　　　　　　　2　　　　　　：行は絶対参照、列は相対参照
　　　　　　　3　　　　　　：行は相対参照、列は絶対参照
　　　　　　　4　　　　　　：相対参照

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

• 	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

【解答例】

• 行番号、列番号を求めるためにMATCH関数の検索範囲をそれぞれB1，A3からとしています。
• ADDRESS関数ではセル参照を返しますので、INDIRECT関数でセルの値を返すようにします。

• 	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	運賃は	=INDIRECT(ADDRESS(MATCH(C10,B1:B7,0),MATCH(C9,A3:E3,0)))

値のリストから指定した値を取出す　　　　　　topへ
　チューズ
=CHOOSE(インデックス,値1,値2,値3・・・)
インデックスに指定した番号に基づいて、最大 29 個の値の中から 1 つの値だけを選択できます。

• インデックスに指定した番号に基づいて、最大 29 個の値の中から 1 つの値だけを選択できます。
  インデックスが 1 の場合は値1 が返され、2 の場合は値2 が返されます。
• インデックスが 1 より小さいか、引数リストの値の個数よりも多い場合、エラー値 #VALUE! が返されます。
• インデックスに小数点以下の値が含まれていても、整数部分だけが計算に使われます。
• 値1,値2,... インデックスに基づいて選択される値または動作を 1 〜 29 個まで指定します。
  これらの引数には、数値、セル参照、名前、数式、関数、または文字列を指定できます。

【例】

• B2の値によって、「1です」「2です」・・・「4です」と表示します。
  C2セル　=CHOOSE(B2,"1です","2です","3です","4です")　としてC5までコピーします。
  

  	B	C
  2	2	2です
  3	0	#VALUE!
  4	5	#VALUE!
  5	3.4	3です

• 上記エラーを表示しないようにするには
  • =IF(OR(B2<1,B2>4),"",CHOOSE(B2,"1です","2です","3です","4です"))
  • =IF(ISERROR(CHOOSE(B2,"1です","2です","3です","4です")),"",CHOOSE(B2,"1です","2です","3です","4です"))
  といった式が考えられます。

ファイルや別シートのセルへリンクを張る　　　　　　topへ
　ハイパーリンク
=HYPERLINK(リンク先,別名)
　　　リンク先:文字列を二重引用符で囲んで指定するか、またはリンクが設定されているセルを文字列として指定する

【例】

• リンク先は　"[ファイル名]シート名!セル"　のように記述します。
• 同一ファイル(ブック)の場合には　"#Sheet2!A2"　のようにシート名の前に「#」を入れます。
• Webアドレスの場合には「=HYPERLINK("http://www.eurus.dti.ne.jp/~yoneyama/","よねさんのWordとExcelの小部屋")」とします。
  

ハイパーリンクの挿入を使う方法

【操作手順】同一ファイルの別シートへのリンク方法を例に説明します。

1. メニューバーの【挿入】→【ハイパーリンク】を選択します。
2. リンク先：このドキュメント内
   ドキュメントの場所：Sheet2　を選択
   セル参照を入力してください：B2　と入力
   表示文字列：シート2のB2へリンク　としました。(何でも良い)
   
3. 数式バーには表示文字列に入力した文字が表示されます。
   

ハイパーリンクの削除

• リンク情報を削除し、表示文字列は残します。
  1. 空白セルを選択し、[編集]→[コピー]を選択します。
  2. ハイパーリンクが設定してあるセルを選択します。
  3. [編集]→[形式を選択して貼り付け]で[加算]にチェックを入れて、OKとします。
• 「空白セル」以外では「1」と入力したセルをコピーしたら[乗算】、「0」を入力したセルなら[加算]でもOKです。
• 【関連情報】
  • 入力したアドレスに青い下線が付くのを解除したい　(ハイパーリンクの解除)
  • ほかのブック（ファイル）とのリンク先を探すには？

行番号を返す　　　　　　topへ
　ロウ
=ROW(範囲)　　引数として指定された範囲の行番号を返します。
　ロウズ
=ROWS(配列)　セル範囲または配列の行数を返します。

列番号を返す　　　　　　topへ
　コラム
=COLUMN(範囲)　　引数として指定された範囲の列番号を返します。
　コラムズ
=COLUMNS(配列)　セル範囲または配列の列数を返します。

• 範囲を省略すると、ROW・COLUMN関数が入力されているセルの行番号・列番号を返します。

• 【例1】
  

• 【例2】ROW関数を使って連番を作成すると、削除しても連番が維持できます。
  • A2には「=1000+ROW()-1」とし、A11までコピーします。
  • C2〜C11は「1001,1002・・・1010」と数値を入力しています。
  • 6行目を削除すると、A列は連番が維持されています。C列は1005が欠けています。

  削除前	削除後

• 【例3】
  • COLUMN関数をVLOOKUP関数の列位置に使うことができます。
    数式1つをコピーすることで表が作成できます。
  • C2に「=VLOOKUP($B2,$F$2:$H$5,COLUMN(B1))」と入力し、D2までフィルハンドルをドラッグしてコピー。
    更に、(C2:D2が選択状態なので)フィルハンドルをD6までドラッグしてコピーすると完成。
    

PageViewCounter

Since2006/2/27