2008年11月21日金曜日

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

1. lookup 関数を使って、データを参照したい

Google スプレッドシートで、lookup 関数を使い、データを参照したい。

例えば、

  • 日付
  • 曜日

を記入する列があるとする。このとき、日付を元にして、曜日を自動的に入力したい。

 

2. 検索対象である「曜日」シートを作成

最初に、081121-005新しくシートを追加し、「曜日」と名前をつける。

二つの列を使い、

  • A列に「数値」
  • B列に「曜日」

を記入する。

A列の「数値」は、「曜日」を検索するためのキーとして利用する。この例の場合、「数値」は後述する weekday 関数を用いて、日付から返される値と対応させる必要がある。

weekday 関数は、デフォルトでは日付が日曜日の場合 1 を返す。月曜日は 2, 火曜日は 3 … というようなに値を返す。

 

3. 参照元のデータを入力する「データ」シートを作成

081121-006

「データ」シートを新しく追加し、

  • 日付
  • 曜日

の二つの列に記入する。

「日付」には具体的な日付を入力する。日付の連続データを作成する方法は、以下を参照。

次に、「曜日」列の B2 セルに、以下の関数を入力する。

=VLOOKUP(weekday(A2,1),曜日!$A$1:$B$7,2,false)

 

他のシートを参照するための記号

上記の関数では他のシートを参照している。他のシートのセルを参照するには、以下のように `!’ を記述する。

シート名!セルの番地

 

WEEKDAY 関数

日付から曜日を表す数値を得るには、

weekday 関数

を使う。上記のように第2引数に `1’ を指定すると、「日曜日」からはじまる。

 

VLOOKUP 関数

特定の値に対応したデータを参照するには

vlookup 関数。

各引数の意味は、以下の通り。

  1. 検索するためのキー
  2. 検索する対象のデータ
  3. 取得するデータ列の位置。(先頭の列を 1 と数える。)
  4. flase を指定して、完全に一致するもののみ返す。詳細は以下を参照。

Google スプレッドシートの関数リスト - Google ドライブ ヘルプ によると、

[並べ替え順序](オプション、デフォルトは TRUE)では、配列内の先頭列を並べ替えるかどうかを指定します。TRUE の場合は、検索条件にもっとも近いものを返します。FALSE の場合は、完全に一致するもののみを返します。

 

4. 曜日に色付けをして見やすくする

土曜日を青色、日曜日を赤色にしたい。そのためには、

  1. 曜日の表示されている B 列を選択。
  2. メニューより、「表示形式 > 条件に応じて色を変更」を選択。
  3. 「日曜日、土曜日」の文字列の色を変えるには、以下のように設定。

081122-002

ただし、このままでは最初の行の「曜日」(B1セル) が赤色に変化してしまう。そこで、メニューより「表示形式 > スタイルのクリア」を選択する。

 

5. 曜日を検索する「範囲」に名前を付け、計算式を読みやすくする

追記 (2010.8.9) : 範囲の参照をするとき、範囲に名前を付けておくのがよい。

範囲に名前を付ける
  1. 曜日と対応する数値を記述した範囲 A1:B7 を選択しておく。
  2. メニューの 「編集 > 名前付けされた範囲 > 新しい範囲を定義...」 を選択。
  3. ニックネームに適当に 「曜日」 と名前を付けた。

img08-10-2010[1]

計算式で範囲名を利用

これにより、vlookup 関数を使った計算式を以下のように書ける。

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

2コメント:

齋藤俊樹 さんのコメント...

=CHOOSE(WEEKDAY(A1),"日","月","火","水","木","金","土","日")&"曜日"
で良いかと

jutememo さんのコメント...

ご指摘の通りですが、式の中に曜日の名称をハードコードしたくなかったので、上記のようにしました。
よほどないと思いますが、曜日名の表現を後で変更したくなることもあるかなと思いまして。