表計算で VLOOKUP 関数の第３引数をハードコードしないで MATCH 関数の結果を与える。- 参照先（検索対象）の変更が、参照元に影響を与えないために。

• VLOOKUP 関数による値の参照 - Google スプレッドシートで「日付」から「曜日」を表示.WEEKDAY 関数、「範囲名」を利用して のつづき

1. VLOOKUP 関数の第３引数がハードコードされていると柔軟性に欠ける

a. CHOOSE 関数

例えば、表計算で「日付」から「曜日」を得るには、WEEKDAY 関数を日付に適用し、その結果を元に CHOOSE 関数で場合分けを行う。

=CHOOSE(WEEKDAY(A2),"日","月","火","水","木","金","土","日")

この場合、「曜日」はハードコードされるため、曜日の表示を変えたい場合、式の中身を変更しなければならない。

 

b. VLOOKUP 関数

別の方法として、VLOOKUP 関数を用いてデータを参照することで、日付から曜日を得ることができる。

この場合、「WEEKDAY 関数が返す値」と、「曜日」に対応付けた表を予め作成しておく。

表計算を「簡易データベース」として利用することを考えた場合、「曜日」シートを作成し、当該範囲に対して「曜日」と名前を付けておく。

そして、以下のように VLOOKUP 関数を記述する。

=vlookup(weekday(A2),曜日,2,false)

 

c. 参照先の表に情報を追加する場合

ここで例えば、「曜日」の表記を「英語」に変更したいとする。

そのための方法の一つは、参照先の範囲「曜日」に入力した「曜日名」を、日本語から英語に書き換えること。しかし、この方法では「曜日」の表記に変更したいとき、範囲「曜日」の曜日名を変更しなければならない。

別の方法としては、「曜日」シートに「英語」表記の列を追加し、VLOOKUP 関数の第 3 引数で返す値を変更する。

=vlookup(weekday(A1),曜日,3,false)

VLOOKUP 関数の第 3 引数は、参照先の範囲において、関数が返す値が入力されている「列のインデックス」を指定する。

VLOOKUP - Drive Help によると、

VLOOKUP(search_key, range, index, [is_sorted])

index - The column index of the value to be returned, where the first column in range is numbered 1.

 

d. VLOOKUP 関数の第 3 引数をハードコードすると柔軟性に欠ける

上記のように VLOOKUP 関数の第 3 引数をハードコードしてしまうと、参照先の範囲を変更すると、呼び出し元に影響を与えてしまう。

例えば、参照先の範囲に「日本語の曜日」「英語の省略表記」の列を追加したら、参照元の VLOOKUP 関数の結果が変わってしまう。入力済みの VLOOKUP 関数の第 3 引数を変更しなければならなくなる。

 

2. MATCH 関数で位置を取得する

これを解決するためには、VLOOKUP 関数の第 3 引数に必要な値を MATCH 関数で求めるようにすれば良い。

MATCH 関数は、検索する範囲と、検索キーを与えると、条件にあったセルの相対的な位置を返す。

MATCH(search_key, range, search_type)

 

見出し行となる範囲に名前をつける

今回の場合、範囲「曜日」に「見出しとなる行」を予め作成しておく。注意する点は、見出し行にある値が重複しないようにすること。

ここでは、MATCH 関数で範囲を参照しやすくするために、見出し行の範囲に名前をつけておいた。

見出し行の範囲の名前は、「簡易データベース」を意識して、

[対象範囲の名前]_属性

となるようにした。

具体的には、見出し行を選択した後、

• メニューより > データ > 名前付き範囲…

を選択し、

'曜日'!A1:E1

を「曜日_属性」という名前にする。

 

MATCH 関数を使う

確認のため、「英語省略表記」が何列目にあるのか「曜日」シートで確認してみる。

=match("英語省略形", 曜日_属性,0)

結果は 5 となり、「曜日シート」の５列目が「英語省略形」であることを確認した。

MATCH 関数における第３引数は、検索対象がソート済みではないことを表す。

MATCH - Drive Help によると、

0 indicates exact match, and is required in situations where range is not sorted.

 

3. VLOOKUP 関数と MATCH 関数を組み合わせる

MATCH 関数を使うことにより、VLOOKUP 関数の第3引数を「固定された数値」から「match 関数により得られる値」に変えることができる。

例えば、日付から「英語省略形」を曜日に書き込みたい場合、次のように入力する。

=vlookup(
	weekday(A2), 
	曜日,
	match("英語省略形",曜日_属性, 0),
	false
)