表計算ソフトで時間計算、日数計算を行うための7つのレッスン　第7回

■■Lesson 7
「祝日を扱う」

[ VLOOKUP() OFFSET() MATCH() OR() NOT() SUMPRODUCT() COUNTIF() INDEX() INDIRECT() ]

■祝日表の作成

祝日に関する情報は、 Excel 自体のなかに利用可能なデータとして含まれてはいない。
そのため、たとえばシート中に以下のような表を自前で用意して、その情報を利用する。

祝日名　　　　　　　　　　2014 　　　　2015
元日 　　　　1月1日 　　2014/01/01  2015/01/01　　……
振替休日 
成人の日    第2月曜日  2014/01/13  2015/01/12
建国記念の日 2月11日   2014/02/11  2015/02/11
　……

補足メモ：
「2014年1月の第2月曜日」のように「y 年 m 月」の
「第 n」「a 曜日」（月〜日が0〜6に対応）の日付を求めるには、以下のような関数を用いる。
=DATE(y,m,n*7-WEEKDAY(DATE(y,m,0)-a,3))
上の例の場合（「2014」が入っているセルは D3 であるとする）、2014年の成人の日（1月の第2月曜日）は以下の数式で自動計算可能。
=DATE(D3,1,14-WEEKDAY(DATE(D3,1,0),3))

■祝日の日付の色を変える

この表を使い、「シリアル値の入っているセルが日曜日あるいは祝日であれば赤くする」ために条件付き書式を利用ことを考える。これを、あえて（検索の基本として紹介されることの多い） VLOOKUP() を使って実現しようとすると、OFFSET() 、MATCH() の組み合せでたとえば以下のような数式になる。

=OR(WEEKDAY(Q4)=1,NOT(ISERROR(VLOOKUP(Q4,
OFFSET($C$4,0,MATCH(YEAR(Q4),$D$3:$N$3,0),24,1),1,0))))

解説：
この場合、Q4 に日付データが入っており、D4 が上の表の「2014/01/01」があるセル。D3〜N3 に「年」が西暦で入力されている。祝日の日付データの範囲はD4〜N27。
=OFFSET($C$4,0,MATCH(YEAR(Q4),$D$3:$N$3,0),24,1)
で、Q4に対応する年の祝日データのセル範囲（たとえば2014年の場合、D4:D27 ）が返る。

……これでもよいが、そんなに苦労せずとも、この場合はSUMPRODUCT()を使って、

=OR(WEEKDAY(Q4)=1,SUMPRODUCT(($D$4:$N$27=Q4)*1))

でも同じ結果を返す。つまりここでは、上記の表のデータ部分全体を検索の対象とし、ひとつでも該当する値があればTRUEが返されることになる。

これは、さらに別解として、

=OR(WEEKDAY(Q4)=1,COUNTIF($D$4:$N$27,Q4))

という表現も可能。COUNTIF() 関数は複数列、複数行にわたる範囲を指定できるためである。

補足:
このとき、後述の「テーブル」を用いた構造化参照を使うなら、条件付き書式では、
=OR(WEEKDAY(Q4)=1,COUNTIF(INDIRECT("祝日TBL[#データ]"),Q4))
のように、現状（Excel 2013で確認）、一旦INDIRECTで評価する必要がある。

■ カレンダーに祝日の名称を表示する

上記の表をもとに、「日付（Q4に入力されたシリアル値）からその日が祝日かどうかを判定し、祝日であればその名称（「勤労感謝の日」など。表のなかではB列に格納されている）を表示する」なら、さきほどの MATCH() と OFFSET()の組み合せを応用し、INDEX() あるいはINDIRECT() を使った以下のような数式が使える。

以下の2つの数式はいずれも同じ値を返す。LOOKUP() 関数を使えばもっと単純に記述できそうな気もするが、この関数はなぜか「完全一致」かそうでないか、に対応していないので扱いにくい。代わりに
=INDEX( [返り値のリスト] ,MATCH( [検索する値] , [検索する範囲] ,0))
のように INDEX() と MATCH() の組み合せを用いるのが一般的。…だったのだが、新たに XLOOKUP() 関数が登場し、今後はこちらが主流になってくるのかもしれない。ただ、本記事中ではこの関数はとりあげない。なぜなら、まだ新しすぎるからである。

INDEXを使った例：
=IFERROR(INDEX($B$4:$B$27,MATCH(Q4,OFFSET($C$4,0,MATCH(YEAR(Q4),$D$3:$N$3,0),24,1),0)),"")

INDIRECTを使った例：
=IFERROR(INDIRECT("B"&MATCH(Q4,OFFSET($C$4,0,MATCH(YEAR(Q4),$D$3:$N$3,0),24,1),0)+3),"")

これら、いずれの場合も、表が「2014年」から始まり、年ごとに順番に並ぶことが確定している場合は、
MATCH(YEAR(Q4),$D$3:$N$3,0)
の部分は、
YEAR(Q4)-2013
のようにハードコーディングすることも可能。

さらに、Excel 2007 以降であれば、祝日のデータ全体をテーブルにして「祝日TBL」というテーブル名を付ける（挿入 → テーブル）と、構造化参照を用いて以下のような数式が使えるようになる。
（選択範囲の1行目が「見出し」であるとみなされ、
オブジェクトとして列ごとにテーブル名[見出し名]で参照できる。上記のようなOFFSET()を使ったコーディングが不要になり、より直感的な記述が可能。）

=IFERROR(INDEX(祝日TBL[祝日名],MATCH(Q4,INDIRECT("祝日TBL["&YEAR(Q4)&"]"),0)),"")

補足：構造化参照
Excel 2007以降では、セル範囲をテーブルとして名前を設定すれば、上記のような構造化参照を扱える。
さきほどの例からエラー処理と見出し名取得の仕組みを取り除き、単純化すると、
=INDEX(祝日TBL[祝日名],MATCH(Q4,祝日TBL[2014],0))
のようになる。
今回扱った祝日データの例のように、表現形にはこだわらず、ただもとデータを蓄積・集計・検索したいという場合はきわめて有効な機能である。

■ NETWORKDAYS.INTL() で祝日を扱う

Lesson 6 で紹介した NETWORKDAYS.INTL() などで、祝日も休日に含めるなら、

=NETWORKDAYS.INTL("2015/1/1","2015/1/31","0010010",$D$4:$N$27)

のようにデータの含まれる範囲を引数として指定するだけである。
この例で、祝日データの範囲を表す $D$4:$N$27 の部分は、前項で説明したテーブルを使えば 祝日TBL[#データ] となり、直観的に分かりやすい記述となる。

マインドマップ：おもな検索関連関数の引数と返り値
□ VLOOKUP(検索値, 範囲, 列番号 [, 検索の型])
該当するセルの値が返る「検索の型」を FALSE にすると完全一致（省略すると「TRUE」）
□ MATCH(検索値, 検索範囲 [, 照合の型])
相対的な位置が数値で返る「照合の型」を 0 にすると完全一致（省略すると「1」）
□ OFFSET(基準, 行数, 列数 [, 高さ, 幅])
該当するセル範囲が返る
□ INDEX(範囲, 行番号 [, 列番号, 領域番号])
該当するセルの値が返る。「範囲」が1列だけの場合「列番号」は省略できる
□ INDIRECT(参照文字列 [, 参照形式])
該当するセルの値が返る。「A1」形式の参照の場合、「参照形式」は省略できる。

発展：シート名の参照 [ INDIRECT() CELL() RIGHT() LEN() FIND() ]
INDIRECT() 関数は、ブック内の他のシート上にある数値を呼び出すときに有効である。たとえば B5 にシート名が文字列として記述してあるとする（ここでは「シート1」とする）と、
=INDIRECT(B5&"!C21")
で「シート1!C21」（「シート1」のセル C21 ）の値を呼び出すことができる。ちなみに、現在の（アクティブな）シート名を関数を使って取り出すには、
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
のようにすればよい。

練習問題：
西暦 A20 年 A21 月 の第 A22 A23 曜日の日付を求める関数を作成しなさい。
（A20〜A22 にはおのおの対応する自然数が、A23 には「月」「火」…「日」のように曜日を表す漢字1文字が入っているとする。）
解答例：
=DATE(A20,A21,A22*7-WEEKDAY(DATE(A20,A21,0)-INDEX({0,1,2,3,4,5,6},MATCH(A23,{"月","火","水","木","金","土","日"},0)),3))

このように、特定の入力値を別の値に変換する場合は、
INDEX と MATCH の組み合せの各引数に { } で行列を直接記入することで実現できる。
また、この場合、MATCH() 関数のデフォルトの挙動
（第2引数の値に応じて 1,2,3…… の整数を返す）を利用して、

=DATE(A20,A21,A22*7-WEEKDAY(DATE(A20,A21,0)-MATCH(A23,{"月","火","水","木","金","土","日"},0)+1,3))

でもよい。

■発展：休日出勤の時間を合計する

たとえば勤怠管理などで、「土曜日」「日曜日」「祝日」のみの出勤時間を合計したい、といった場面を考える。
まず、「日曜日」のみを合計したい場合は、(1)
=TEXT(A3,"aaa")
のように、各日付（シリアル値、A3:A33）に対応する曜日を「日」〜「土」の文字列として保存したセルを用意し、その横（C列）に保存した労働時間を(2)
=SUMIF(B3:B33,"日",C3:C33)
のように SUMIF() 関数を使って合計すればよい。
題意の目的を達するためには、まず、(1)については、
=IF(SUMPRODUCT((祝日=A3)*1),"祝", TEXT(A3,"aaa"))
あるいは
=IF(COUNTIF(祝日,A3),”祝”,TEXT(A3,”aaa”))
のように、祝日は「祝」と表示するようにし、
（ここで、祝日を表すシリアル値の入っているセル範囲にあらかじめ「祝日」という名前をつけているとする）

本文の例のように、表全体を「テーブル」にしている場合は、「祝日」の部分は
祝日TBL[#データ] 
のようになる。

(2)については、
=SUM(SUMIF(B3:B33,{"土","日","祝”},C3:C33))
あるいは（別解）
=SUMPRODUCT((B3:B33="土")+(B3:B33="日")+(B3:B33="祝"),C3:C33)
のような改良を加えればよい。

また、B2 に「曜日」、C2に「労働時間」と入力すると、A2:C33 全体をデータベースとみなし、DSUM() などのデータベース関数が使えるようになる。
たとえば、
　　　E
15　曜日
16　土
17　日
18　祝
と入力すると、
=DSUM(A2:C33,"労働時間",E15:E18)
で、さきほどと同じ結果が得られる。

補足:
関数名が D から始まる「データベース関数」は前述の「テーブル」機能が実装される以前からあったもので、まったく別のものと考えたほうがよい。また、セル範囲に付ける「名前」も「テーブル名」とは別物である。
「モダン」なExcelでは、データベース関数を使わなくともテーブル機能を駆使したほうが分かりやすいシステムを作れる場面も多いが、どちらを採用するかは個人の好みにも左右されるだろう。