どうも、Excel紳士のKa`s(かーず)です。言ってみたかっただけです。すみません！
ある日、賃料の算出をExcelでやってたのですが、どうも金額が合わなくて「おかしい…」ってなりましてね。なにが原因なのかシートを舐め回すように見ていたら、どうやら対象としてる期間がおかしいことに気がつきました。
期間を求める時に使った関数は、「DATEDIF関数」だったのですが、コレ使うのやめたほうがいいです。求める期間に「うるう年」が混じってると、日数を算出した時に誤った数値が出されるっていうバグがありました。
DATEDIF関数のバグを避けるために有効だった方法もありますのでバグとセットで紹介していきます。
DATEDIF関数で期間内の日数を求めるのは”危険”
会社で賃料の日割り計算などにDATEDIF関数を使って日数を求めるのは、誤計算になるので絶対にやめたほうがいいです。
今回の話の中心である「DATEDIF関数」ってなんぞ？ という人のために簡単に関数の説明すると
DATEDIF関数とは期間を求める関数で、年数や日割り計算するときにどのくらいの日数があるのか調べたりするのに使います。
DATEDIF関数が危険だなっていうのは、求める期間内に「うるう年」が含まれてる場合です。
通常の年とうるう年が含まれてる場合で比較した時の状況がこんな感じです。
*「”YD"=年未満の数値で日数を表示するときに使います。」
上から説明しますと、2017年と2018年はうるう年ではないため1月1日～3月1日の日数を計算すると「59」で問題ありません。
真ん中の2016年はうるう年であるため、2月29日が存在しています。ですので、1月1日～3月1日の期間を計算したときに「60」と表示されている必要があり、年内の期間算出であれば問題なく利用できることがわかりました。
しかし、一番下の期間を算出した場合、2月29日の存在がなかったことのよう「59」と表示されています。本来であれば、2月29日があるので「60」と表示するのが正解なのです。
Microsoftも認めてるけど、修正する気ゼロ
Microsoftのフォーラムで確認すると特定の条件で関数の結果が不正になるとして、回避策を提示してくれています。
フォーラムはコチラ→Microsoft-help(DATEDIF関数)
回避策の答えで表示されている関数ですが、長すぎて扱いづらいとしか言いようがないのです。
わたしの場合だと、日数計算をするため”YD”を使うのですが・・・フォーラムでの回避策で提示されているのがコチラ
=IF(AND(INFO("release")="12.0",MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1)),
DATEDIF(A1,B1,"YD")-(DATEDIF(DATE(2011,1,2),DATE(2012,1,1),"YD")-364),
DATEDIF(A1,B1,"YD"))
「原理はわかるけど、こんなに複雑なら使わないですよ」ってのが率直なわたしの意見です。
フォーラムを確認する限り、DATEDIF関数は公式関数のようで非公式関数みたいな扱いってことがわかりました。しかも、Excel2010が出る前からこの現象が発生してるようです。
いままで修正してないってことは、修正する気がないってことですよね。
2017/8/25追記
修正する気がないって書いていますが、DATEDIF関数は「Lotus 1-2-3」の古いデータをサポートするためだけに用意されている関数でした。
Lotus 1-2-3は、Excelが世の中で使われるようになる前に使われた計算ソフトウェアです。
DATEDIF関数の代わりに使える、ユーザー定義関数の存在
世の中には優秀な人がいて、そんな優秀な人がDATEDIF関数に代わる「ユーザー定義関数」を作成していました。
ユーザー定義関数は、ExcelのVBAと呼ばれる機能でプログラムを追加することができます。また、VBAは利用者自身が簡易なプログラムを書き込むことができる機能でExcelのオプションから「リボンのユーザー設定」で「開発」にチェックすると表示されるようになります。
代わりになるコードを作成しているサイト→ktdatedif
ユーザー定義関数を設定する
ユーザー定義関数の追加方法の手順は以下を参考にしてください。
「開発タブ」を開き、「VBA」を選択する。
「挿入タブ」＞「標準モジュール」
コード配布サイト→ktdatedifのコード
入力画面にコードを貼り付けるだけ
通常画面に戻って、「ktdatedif」と入力すると新たに追加して関数が利用できるようになっています。
今回追加した「ktDATEDIF関数」の詳しい使い方については、リンク先で確認してください。基本的な使い方は、DATEDIFと同様ですが初日加算ができたりと似ているようで異なります。
おわりに
公式だからバグはないと思っていたので、今回の件で１つ学んだことがあります。
「完璧なモノはない」 必ずしも正確に設計されているとは限らないってことをよく理解できました。仕事上でExcelを使う場面は、今や当たり前で簡単な関数からマイナーなものまで活用した結果がバグとの出会いでした。
うるう年というイレギュラーな年度を回避するのも一苦労ですね。