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

■■Lesson 5
「さらなる応用 more than one way.」

[ここで学ぶ関数 EDATE() EOMONTH() TIMEVALUE() DATEVALUE() MAX() ]

■ 年・月・日・時・分・秒を単位とする加減算

◆加減算の基本

特定の日付（セルB13 に入っている値とする）の100日後の日付を求めるなら、Lesson 4 までに習った関数を用いて、

=DATE(YEAR(B13),MONTH(B13),DAY(B13)+100)

のような方法が使える。
これはもちろん、年、月、時、分、秒 についても応用可能。
が、日付は一日を「1」とするシリアル値なので、「日」にかぎってはたんに、

=B13+100

とすればよい。
（さきほどの例とは異なり、この場合、時刻の情報が含まれていれば保存される）

また、
時、分、秒の加減算の場合なは、
Lesson 4 で学んだ変換方法を応用して、
たとえば「7時間後」であれば、

=B13+7/24

といった書き方もできるが、

=B13+"7:00”

のように日付文字列を直接、算術記号で結んでもよい。分・秒も含んだ加減算の場合はこちらのほうが単純に記述できる。

時間の場合は、掛け算、割り算(〜時間〜分の〜倍の時間を求めるなど）についてもとくに問題なく同じ要領で行うことができます。このとき、24時間を超えたり、マイナスになったりする場合は、Lesson 1 で学んだ「表示形式」を確認してください。

そして、たとえば B5 に「2019/12/5」のような日付情報が、B6 に「9:35」のような時間情報が入っている場合、「2019/12/5 9:35」のような日付時間情報を得るには、単純に
=B5+B6
のようにすればよい。

◆ 月・年の加減算

月の場合、たとえば、「B13に入っている日付の7ヶ月後の日付を求める」なら

=DATE(YEAR(B13),MONTH(B13)+7,DAY(B13))

でもよいが、「〜カ月後の日付」を求める EDATE() 関数を使うこともできる。
たとえば、上記の例であれば、多くの場合、以下のやり方でも同じ結果を返す。
（時刻に関するデータは切り捨て）

=EDATE(B13,7)

年の場合、たとえば「5年後の日付を求める」なら、

=DATE(YEAR(B13)+5,MONTH(B13),DAY(B13))

=EDATE(B13,12*5)

といった方法が考えられる。

ただし、月末付近の日付を扱う場合、EDATE() の結果は、DATE() の第２引数を加減算した場合の結果と異なる場合がある。
 たとえば、

=EDATE("2014/8/31",1) --- 2014/9/30
=DATE(2014,8+1,31) --- 2014/10/1

「月末の日付であれば返り値も月末に」したいといった場合は EDATE() を使うほうがよい。

応用：
D9 に生年月日を入れると、これから訪れる直近の誕生日を返す関数を作成するとする。
「今日」が今年の誕生日以前か以後かで場合分けして、以下のような方法が使える。

=IF(TODAY()<DATE(YEAR(TODAY()),MONTH(D9),DAY(D9)),DATE(YEAR(TODAY()),MONTH(D9),DAY(D9)),DATE(YEAR(TODAY())+1,MONTH(D9),DAY(D9)))

これは、以下のように段階を分けると見やすくなる。
D10: #今年の誕生日
=DATE(YEAR(TODAY()),MONTH(D9),DAY(D9))
D11: #来年の誕生日 =DATE(YEAR(TODAY())+1,MONTH(D9),DAY(D9))
D12: =IF(TODAY()<D10,D10,D11)

これは、Lesson 4 で紹介したDATEDIF() を使って以下のように書くことも可能。
（「今日時点での年齢」+1 を誕生年に足すことで
「西暦何年の誕生日か」を求めている）

=DATE(YEAR(D9)+DATEDIF(D9,TODAY(),"Y")+1,MONTH(D9),DAY(D9))

さらに、EDATE() を使うと以下のようになる。

=EDATE(D9,(DATEDIF(D9,TODAY(),"Y")+1)*12)

VBA補足：
VBA で、本節と同様の年月日時分秒の加減算を行う場合はDateAdd() 関数を使うことができる。
特定の日付（たとえば 2014年11月3日）の 1日後、1週間後、 1ヶ月後、1年後の日付を求めるには以下のようにする。
DateAdd("d" , 1, "2014/11/3")
DateAdd("ww", 1, "2014/11/3")
DateAdd("m", 1, "2014/11/3")
DateAdd("yyyy", 1, "2014/11/3")
ほかに、時間は "h" 、分は "n" 、秒は "s"

■ 「月末」の扱い:閏年などの判定

特定の日付（セルB2に入力されたシリアル値とする）の月末の日付を返すなら、

=DATE(YEAR(B2),MONTH(B2)+1,0)

といった表現が可能。（「次の月」の第0日 = その月の月末 ）
これは、以下のように EOMONTH() 関数を使っても同じ結果を返す。

=EOMONTH(B2,0)

さらに、該当する月に含まれる日数（その月が大の月か小の月か。2月なら28日か29日か）を求めるなら、

=DAY(DATE(YEAR(B2),MONTH(B2)+1,0))

または、

=DAY(EOMONTH(B2,0))

となる。

EOMONTH() 関数の第2引数には第1引数の「何ヶ月後か」を入力する。
たとえば、「翌々月の10日」なら、

=EOMONTH(B2,1)+10

これはもちろん、以下の関数と同じ結果を返す。

=DATE(YEAR(B2),MONTH(B2)+2,10)

応用：
今年が閏年かどうかを返す関数
=IF(DAY(EOMONTH(DATE(YEAR(TODAY()),2,1),0))=29,"今年は閏年","今年は閏年ではない")
「セルC5に入力した任意の年（西暦）が閏年かどうかを返す」ならもちろん、
=IF(DAY(EOMONTH(DATE(C5,2,1),0))=29,"閏年","閏年ではない")

…だが、ここでは 2月の末日 = 3月0日 であることを利用して、それぞれ以下のようにするほうが簡単に記述できる。

=IF(DAY(DATE(YEAR(TODAY()),3,0))=29,"今年は閏年","今年は閏年でない")
=IF(DAY(DATE(C5,3,0))=29,"閏年","閏年ではない")

また、「セルE1に入力した日付からその1年後の日付までの日数（365日か366日か）」を返す関数は

=EDATE(E1,12)-E1

これは、端数（時間）が含まれないならば

=DATE(YEAR(E1)+1,MONTH(E1),DAY(E1))-E1

でも、同じ。

特定の年が閏年かどうかを判定する方法としては、以下のようなものもある。
https://docs.microsoft.com/ja-jp/office/troubleshoot/excel/determine-a-leap-year

補足メモ：
逆に、特定の日付（B5に格納されたシリアル値とする）からその月の月初の日付を求めるなら、
=B5-DAY(B5)+1
=DATE(YEAR(B5),MONTH(B5),1)
=EOMONTH(B5,-1)+1
といった方法が考えられる。

■発展：条件付きの合計 [ SUMIFS() SUMPRODUCT() ]

B列（B5〜B35）に日付（シリアル値）が入力されており、C列（C5〜C35）にその日付に対応する売上高が入力されているシートで、特定の日付（A3にシリアル値で入力）に対応する月（暦日）の売上の合計を求める数式を作りなさい。

答え：
=SUMIFS(C5:C35,B5:B35,">="&EOMONTH(A3,-1)+1,B5:B35,"<"&EOMONTH(A3,0)+1)
または、
=SUMPRODUCT((B5:B35>=EOMONTH(A3,-1)+1)*(B5:B35<EOMONTH(A3,0)+1),C5:C35)
SUMPRODUCT() を使った場合はさらに、
=SUMPRODUCT((YEAR(B5:B35)=YEAR(A3))*(MONTH(B5:B35)=MONTH(A3)),C5:C35)
のような表現も可能。

補足A：
このような場合、いわゆる作業列を使う方法もある。

以下、オートフィルや絶対参照・相対参照といった表計算ソフトの基本的な概念・操作についてはすでに習得しているものとします。

この場合、たとえばD5に、
=YEAR(B5)
と入力してこれをD35までフィルし、E5に、
=MONTH(B5)
と入力してE35までフィルしたうえで、
=SUMIFS(C5:C35,D5:D35,YEAR(A3),E5:E35,MONTH(A3))
または、
=SUMPRODUCT((D5:D35=YEAR(A3))*(E5:E35=MONTH(A3)),C5:C35)
とすると同様の結果が得られる。

あるいは、
D5に、
=IF(AND(YEAR(B5)=YEAR($A$3),MONTH(B5)=MONTH($A$3)),C5,0)
と入力してD35までフィルすれば、
単純に、
=SUM(D5:D35)
で同様の計算ができる。

補足B：
毎月15日を締め日とし、シリアル値A3 が含まれる月度（16日〜翌月15日）の売上の合計を作る……なら
A4：=IF(DAY(A3)<16,DATE(YEAR(A3),MONTH(A3)-1,15),DATE(YEAR(A3),MONTH(A3),15))
A5：=IF(DAY(A3)<16,DATE(YEAR(A3),MONTH(A3),16),DATE(YEAR(A3),MONTH(A3)+1,16))
として、
=SUMIFS(C5:C35,B5:B35,">"&A4,B5:B35,"<"&A5)
または、
=SUMPRODUCT((B5:B35>A4)*(B5:B35<A5),C5:C35)
とすればよい。

解説：
以上のように「条件付き総和」を求める際、まずは SUMIF() SUMIFS() の使用を検討するのがよい。しかし、場合によっては複数列の積の和を求める SUMPRODUCT() 関数を使ったほうが分かりやすい記述ができる場合がある。SUMPRODUCT() の基本的なかたちは以下のようになる（ B3*C3 + B4*C4 + …… + B12*C12 の結果が返る）。
=SUMPRODUCT(B3:B12,C3:C12)
また、シートの保守性や可読性、検算のやりやすさなどを考慮すると、上記で挙げた例のように、作業列の使用(あるいは、そもそも初めの入力をシリアル値ではなく、年・月・日別々のセルに行うようにするなど）も検討に値する。
Excel の場合、さらに「テーブル」機能と構造化参照を用いることで視認性のよい数式を記述できる。これについては Lesson 7 で考察する。

■ TIMEVALUE() DATEVALUE()

時刻表記や日付表記を明示的に数値（シリアル値）として扱う場合、TIMEVALUE()、DATEVALUE()関数を使う。

日付文字列はあくまでも「文字列」なので、日付として扱うにはシリアル値（数値）に変換する必要がある。
この変換は処理によっては自動的に行われるが、自動化が行われていない場面ではこれらの関数で明示的に変換を行わなくてはならない。
とくに、比較演算を行う場合は必須。
また、これ以外の場面でも、日付文字列は "" で括る、
それでも駄目なら TIMEVALUE()、DATEVALUE()で括ってみる、といった操作を忘れないようにすることが肝要。
要するに、TIMEVALUE() 、DATEVALUE() 各関数は、引数となる文字列が「日付」であることを明示的に示す役割しか果たさない。

=TIMEVALUE("12:34")
=DATEVALUE("2014/7/15")

eg) 8:30 以前の出勤は「8:30」出社だとみなす
（それ以後の出勤なら出勤時刻をそのまま返す）、なら

=IF(D3<TIMEVALUE("8:30"),TIME(8,30,0),D3)

ちなみにこれは、与えられた引数のうちの最大値を返す MAX() 関数を用いて、

=MAX(D3,TIMEVALUE("8:30"))

でも同じ。

eg) 退社時刻（C4に入っているとする）を30分単位でまるめ（切り捨て）る、ただし、その「単位時間まであと5分以内」である場合は逆に切り上げる、なら、

=IF(CEILING(C4,"0:30")-C4>TIMEVALUE("0:05"),
FLOOR(C4,"0:30"),CEILING(C4,"0:30"))

補足メモ：
シリアル値と日付文字列との変換にはまるめの誤差が生じることがあり、境界値近辺では思うような動作にならない場合がある。
例えば上記のように分単位での入力を前提とする場合、比較演算の部分に「余裕をもたせる」といった工夫が必要になることがある
（上記の例の場合 TIMEVALUE("0:05")をTIMEVALUE("0:05:01")にするなど）

また、この誤差を生じさせないで日付文字列を扱う方法として、
TIMEVALUE(TEXT(A1,"hh:mm"))
のような処理が知られている。
MROUND() 関数などで最終的な結果を「まるめる」ことを検討してもよい（いろいろ書きましたが、現実的にはこれが一番簡単、確実…でしょうか）