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 inrangeis 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 によると、
0indicates exact match, and is required in situations whererangeis not sorted.
3. VLOOKUP 関数と MATCH 関数を組み合わせる
MATCH 関数を使うことにより、VLOOKUP 関数の第3引数を「固定された数値」から「match 関数により得られる値」に変えることができる。
例えば、日付から「英語省略形」を曜日に書き込みたい場合、次のように入力する。
=vlookup(
weekday(A2),
曜日,
match("英語省略形",曜日_属性, 0),
false
)
0コメント:
コメントを投稿