年次休暇の取得とのこりの自動計算について

　恐れ入ります。先日年次休暇の計算方法について相談させていただいた者です。
　ご回答をいただき、それをヒントに自分で考えられるかと思ったのですが、難しいので申し訳ありませんが、どうぞよろしくお願いします。

　添付画像のようなレイアウトを考えています。
・あらかじめ去年からの繰り越しを入力しておきます。
　日、半日、時間、分という単位です。
・黄色のセルに、現在の取得年休を入れます。
・自動的に現在の積算年休が表示され、差し引きがわかるようにしたいのです。

　また、次の約束があります。
・１日は７時間４５分です。（先日は７時間１５分で尋ねましたが、職種により違い、今回は７時間４５分をもって１日とする職種の分です）
・半日単位での取得はできますが、半日は時間に換算せず、0.5日という表記にします。
　最終的には「15.5日と３時間15分」といった残数表記にします。
・半日を時分換算しないので、繰り下がりは「日」から「７時間45分」をとってきます。

　レイアウトは多少変わっても構わないので、どうぞよろしくお願いします。

No.5 ベストアンサー 回答者： MarcoRossiItaly 回答日時： 2014/01/12 11:58

「先日の相談」とは、こちらのことですね。

http://oshiete.goo.ne.jp/qa/8422338.html


前回の回答は計算誤差にまで話が及んで、難しくし過ぎましたかね。

今回の質問文の添付図を見ると、日数・時間数が各セルに分かれていて、各セルには整数のみ記入するという意味ですよね？そうであれば、シリアル値を使わず整数のみで（時間の「分」のみで）最後まで計算することができるので、そういうときは前回も言ったとおり、誤差が発生する可能性を排除できます。


今回の条件で少し難しいのは、「0.5 日を時間に切り崩せない」という点ですね。1 日は崩せるが 0.5 日はできないというのは、たぶん休暇の制度上の制約なのでしょう。よくある話です。

午前と午後の勤務時間数は異なるかもしれませんが、0.5 日は、平均すれば 7 時間 45 分÷ 2 ＝ 3 時間 52.5 分。半日なので、24 時間÷ 2 ＝ 12 時間という見方もある。しかし例えば「0.0 日 4 時間 0 分」→「0.5 日 0 時間 7.5 分」と書き換えることはできず、逆に「0.5 日 0 時間 0 分」→「0.0 日 3 時間 52.5 分」あるいは「0.0 日 12 時間 0 分」とも書けないということですね。

だから半日を含む日数を単純に分に変換するのはダメで、日数の小数点以下を切り捨ててから（つまり半日のセルを除外した日数のまま）分に変換すれば、解決するということになりますね。


この回答の添付図には、次式を記入しています。

F3　=60*d3+e3
K3　=$F3*g3+60*i3+j3
Q3　=int((int(u3/f3)*2+h3-m3)/2)
R3　=mod(int(u3/f3)*2+h3-m3,2)
S3　=int(mod(u3,f3)/60)
T3　=mod(mod(u3,f3),60)
U3　=k3-p3

※ K3 セルをコピーして P3 に貼り付け
※「前年繰越」には「本年取得分」が合算されていると解釈

実は 0.5 という小数の場合は 2 進数に変換しても丸め誤差がないので、そのまま足したり引いたりしても大丈夫だったりもしますが、上式ではあえて整数のまま計算するため、2 を掛けたり割ったりしてみました。


質問文の帳票？の形に計算結果を並べたかったら、この回答の添付図のシートから質問文のシートに、VLOOKUP 関数なりで参照することによって転載すればいいだけですね。隙間なくセルの結合もなく、整然と並んだ表を元データにするのが基本です。方向を逆にしてしまう人が多いのですが。

なお表中の「7」と「45」という数値を材料にして時間のシリアル値を作ることも一応は可能なのですが、誤差の要因を生んでまでシリアル値を使う必要もないので、今回は行いませんでした。

この回答へのお礼

大変丁寧にかつ、わかりやすい対応をしていただきありがとうございます。

お礼日時：2014/01/12 12:06

No.4 回答者： bunjii 回答日時： 2014/01/12 10:45

>日、半日、時間、分という単位です。

>半日を時分換算しないので、繰り下がりは「日」から「７時間45分」をとってきます。
論理が矛盾しています。
半日を時分に換算しないのに時間と分の単位で繰越が出る理由を提示してください。

論理の矛盾は抜きにして試算させて頂きました。

B7=B5+C5/2+INT(D5/("7:45"*12))/2
D7=D5/24+E5/1440-"7:45"*INT(D5/("7:45"*12))/2
B9=IF(INT((D7+D9)/"7:45")=1,B2+C2/2-B7-1,B2+C2/2-B7)
D9=IF(D2/24+E2/1440<D7,D2/24+E2/1440+"7:45"-D7,D2/24+E2/1440-D7)

D7とD9はシリアル値で表示の書式は h時m分 としました。
D9の減算で前年繰越の端数時間より本年換算が大きい場合は1日（7:45）を前年繰越の端数に加算しています。
B9では減算の繰り下げを INT((D7+D9)/"7:45"=1 の論理値で判断しています。

この回答へのお礼

ありがとうございます。おっしゃるとおり「矛盾」です。
これは全く違う次元の話になりますが、以前は１日＝８時間、１日＝半日×２、半日＝４時間という計算方法で矛盾はありませんでした。しかし、１日＝７時間４５分（あるいは１５分）といったようになり、事務処理の約束事として「半日は時間換算しない」という約束になったという理由があります。我が社の変な内規です。ご理解くださいますようお願いします。

お礼日時：2014/01/12 11:42

No.3 回答者： keithin 回答日時： 2014/01/12 03:13

そんな間抜けな見落としをすると思われているのでしょうか。

ちょっと残念です。

＞左の「日」から１日もってくる必要があるかと思うのですが．．．。

アタマで考えてるあいだに一度でも手を動かして回答者からそれぞれ寄せられたアドバイスを実際のエクセルで動かしてみれば、「なぜか不思議、正しい答えが出てくる」のか、それとも「やっぱりこういう状況で間違った（意図と違う、あるいは左から持ってくるのを忘れてマイナスになっちゃうみたいな）答えになる」のか、すぐに判るはずですが？


丁寧に回答しておくと「繰り下がりを組み込んで計算する必要がある」のは、実際に値を持って行かれちゃう日の方（Ｂ９）です。
Ｄ９に回答したのはそういう差し引きは余所で勝手にやってもらう事にして、あくまで「残り時間（剰余）」だけを計算できる関数の利用法です。



で。
実は回答は速攻で投稿するため、わざと手抜きをしています。
左から繰り下がりを持ってこなきゃならない「可能性がある」唯一のシチュエーションは、本年度実績の合計が前年繰越分を「超えてしまった」場合です。

もっともその場合、ふつーに考えて「残りは－４時間１５分」なんて計算はしないで、「残りはゼロ。不足（超過？）分は別途計上し欠勤として今年度中に処理する」などのようになると思いますが、ご相談でも考慮されていませんので組み込んでません。
各回答者から寄せられたそれぞれのやり口で、このシチュエーションがどのように処理されるか確認してみて下さい。





＃蛇足
＞お気を悪くされませんように。

別に気を悪くとかは全然してなくて、随分と失礼なことをしますねと指摘しているだけです。そういう風に思われる事を御自分がしてるんだと、指摘されないと気が付かないからそういう事ができるんでしょ？
指摘されて気分悪くされたらごめんなさい。

この回答へのお礼

ていねいな解説ありがとうございます。感謝いたします。

お礼日時：2014/01/12 11:37

No.2 回答者： tom04 回答日時： 2014/01/12 00:00

こんばんは！

すでに回答は出ていますが・・・

判り易い方法としては↓の画像のように別セルに「時間換算」したデータを利用する方法だと思います。

画像ではG2セル（セルの表示形式やユーザー定義から　[h]:mm　としています）に
=(B2+C2*0.5)*"7:45"+D2/24+E2/60/24

同様にG5セルに
=(B5+C5*0.5)*"7:45"+D5/24+E5/60/24
という数式を入れています。

これで前年繰越分と本年度分が時間換算されますので
B7セル（セルの表示形式は「標準」）に
=FLOOR(G5/"7:45","0.5")

D7セル（表示形式はユーザー定義から [h];mm)に
=G5-B7*"7:45"

B9セル（表示形式は「標準」）に
=FLOOR((G2-G5)/"7:45",0.5)

D9セル（表示形式は　[h]:mm)に
=MOD(G2-G5,"7:45")

という数式を入れています。m(_ _)m

No.1 回答者： keithin 回答日時： 2014/01/11 23:28

ん？

内容は無関係に早いモノ勝ち(?)ということで。というのは冗談ですが。


Ｂ９には
=INT(((B2-B5)*"7:45"+(D2-D5)*"1:0"+(E2-E5)*"0:1")/"7:45")+(C2-C5)*0.5
Ｄ９には
=TEXT(MOD((D2-D5)*"1:0"+(E2-E5)*"0:1","7:45"),"h時間mm分")
でいいです。

この回答へのお礼

さっそくのご回答ありがとうございます。
前回の質問についてはどうかお気を悪くされませんように。

さて、D9のセルについてなのですが、D2からD5を引く場合、D2の値が小さければ、左の「日」から１日もってくる必要があるかと思うのですが．．．。

お礼日時：2014/01/11 23:47