Excel2003で年次有給休暇(個人)の管理をするための数式を教えて

Excel2003で年次有給休暇(個人)の管理をするための数式を教えてください。

今年度から勤務時間の改正により１日が7時間45分となりました。
有給休暇は１日単位で使用すると7時間45分で計算され、
時間休で使用すると１時間60分で計算されます。
なので、１日取得すると、例えば有給休暇全体が40日0時間であれば39日0時間。
この状態から１時間の時間休を取得すると38日6時間45分となります。
そして、38日0時間45分から１時間の時間休で37日7時間30分となります。
つまり、１日を7時間45分とし、
日単位では7時間45分、時間単位では60分で計算される仕組みです。

今までは、D列に取得日数、E列に取得時間、F列に残り総日数、G列に取得累計日数として、
成数が日数、少数部分が時間として表示させていました。
勤務時間が8時間なので、8進法を使っていました。

分かりにくいのですが、参考までに以前まで使っていた数式を載せます。

F4セル：前年度からの繰り越し休暇日数(例えば20と入力)

B8・C8：申請年月日・曜日(入力欄)

D8・E8：取得日数・時間(入力欄)

F8：有給休暇の残り日数
=IF(B8="","",IF(20*8+F4*8-(D8*8)-E8<64,DEC2OCT(20*8+F4*8-(D8*8)-E8),IF(20*8+F4*8-(D8*8)-E8<128,DEC2OCT(20*8+F4*8-(D8*8)-E8)-20,IF(20*8+F4*8-(D8*8)-E8<192,DEC2OCT(20*8+F4*8-(D8*8)-E8)-40,IF(20*8+F4*8-(D8*8)-E8<256,DEC2OCT(20*8+F4*8-(D8*8)-E8)-60,IF(20*8+F4*8-(D8*8)-E8<320,DEC2OCT(20*8+F4*8-(D8*8)-E8)-80,DEC2OCT(20*8+F4*8-(D8*8)-E8)-100)))))/10)

G8:その年度の有給休暇取得日数累計
=IF(B8="","",IF(B8="","",IF(D8*8+E8<64,DEC2OCT(D8*8+E8),IF(D8*8+E8<128,DEC2OCT(D8*8+E8)-20,IF(D8*8+E8<192,DEC2OCT(D8*8+E8)-40,IF(D8*8+E8<256,DEC2OCT(D8*8+E8)-60,IF(D8*8+E8<320,DEC2OCT(D8*8+E8)-80,DEC2OCT(D8*8+E8)-100))))))/10)

今までは、残り10日と3時間であれば10.3と表示させていました。
今回からは、これを一つのセルに「10日３時間0分」と表示させたいと考えています。

検索サイトで、いくつか数式の紹介があったのですが、
どうも上手に計算・表示させることができなくて困っています。

よろしくお願いします。

No.1 回答者： keithin 回答日時： 2010/04/10 22:17

時休部分のセルのデータの入れ方がちょっと不明ですが。

F4に開始日数（たとえば20)
D8以下に日単位の休暇取得数（たとえば１や２や３）
E8以下に時休みの取得数（たとえば　1:00　とか　6:00　とか，書式設定はh:mm）
として

F8以下に残数
=IF(D8+E8,$F$4-SUM($D$8:D8)-ROUNDUP(SUM($E$8:E8)/"7:45",0)+MOD("7:45"-SUM($E$8:E8),"7:45"),"")
G8以下に取得済数
=IF(D8+E8,SUM($D$8:D8)+INT(SUM($E$8:E8)/"7:45")+MOD(SUM($E$8:E8),"7:45"),"")

ＦＧ列はセルの表示形式を　d日h時間m分　と設定しておく。




＃Ver2
D8以下に，
　日休を取得した場合は１，２，３を記入する
　時間休を取得した場合は1:00や4:45を記入する
　7:45以上の時間数を記入してはいけない
　日休と時間休の併用（１日と4:00など）は認めない
E列は使わない

F8:
=IF(D8,$F$4-SUMIF($D$8:D8,">=1")-ROUNDUP(SUMIF($D$8:D8,"<1")/"7:45",0)+MOD("7:45"-SUMIF($D$8:D8,"<1"),"7:45"),"")
G8:
=IF(D8,SUMIF($D$8:D8,">=1")+INT(SUMIF($D$8:D8,"<1")/"7:45")+MOD(SUMIF($D$8:D8,"<1"),"7:45"),"")

この回答への補足

すみません、もう１つ質問です。
教えていただいた数式についての質問なのですが、

#ver1の数式を入力した状態で、
F4が２０であれば問題ないのですが、
F4を４０にして、時間休に「1:00」と入力すると、
残りの日時数が８日６時間４５分となってしまいます。
どういうことなのでしょうか。
すみませんが、今一度お願いします。

補足日時：2010/04/11 01:18

この回答へのお礼

早速のご回答ありがとうございました。

今までは日数や時間数はセルが別々だったので、
どちらも整数で入力していました。(１日も１時間も「１」と。)
これを１：００と入力するということですね。
それでも全く問題はありません。

今数式を入れて確認してみました。
有給の残りの部分のみで確認しましたが、
イメージ通りに表示されて感激しています。

他の数式も入力してみます。
本当にありがとうございました。

お礼日時：2010/04/10 23:27

No.2 回答者： MackyNo1 回答日時： 2010/04/10 22:32

以下のような数式で本年度の有休残日数および時間数を表示できます。

=INT(本年度有給休暇数-取得週休休暇日数-取得有休時間数/"7:45")&"日"&TEXT("7:45"-MOD(取得有休時間数,"7:45"),"h時間m分")

取得有休時間数が「２：３０」のような形式で入力されていない場合は、たとえば「２．５」と入力しているなら上記の数式の時間の部分を24で割り算してください。

もし、それ以外の方式で入力している場合は、実際に使用している入力形式を具体的に提示してください。

No.3 回答者： MackyNo1 回答日時： 2010/04/10 22:33

以下のような数式で本年度の有休残日数および時間数を表示できます。

=INT(本年度有給休暇数-取得週休休暇日数-取得有休時間数/"7:45")&"日"&TEXT("7:45"-MOD(取得有休時間数,"7:45"),"h時間m分")

取得有休時間数が「２：３０」のような形式で入力されていない場合は、たとえば「２．５」と入力しているなら上記の数式の時間の部分を24で割り算してください。

もし、それ以外の方式で入力している場合は、実際に使用している入力形式を具体的に提示してください。

この回答へのお礼

早速のご回答ありがとうございました。

当初は時間の入力を「１」、「２」と入力することを考えていました。
ただ、入力形式を「１：００」とするのでも大丈夫です。

Excelでの時間の計算を使うというのがポイントだったんですね。
教えていただいた数式をExcelの表と照らし合わせながら入力してみたら、
イメージ通りの表示になりました。
時間の計算について勉強しながら、
表を作り上げていきたいと思います。

助かりました。本当にありがとうございました。

お礼日時：2010/04/10 23:36

No.4 回答者： tom04 回答日時： 2010/04/10 22:57

こんばんは！

外していたらごめんなさい。
勝手に↓の画像のように表を作ってみました。

実際には実数としての数値が必要だと思いますので、
仮に残り日数が40日の場合　G4セルに
="7:45"*40　としてセルの表示形式をユーザー定義から
[h]:mm　としています。
この数値を○日○時間○分というようにF4セルに表示させています。
F4セルは
=INT(G4/"7:45")&"日"&TEXT(ROUND(MOD(G4,"7:45"),5),"[h]時間mm分")
という数式を入れています。

このF4セルは単なる飾りで実際はG4セルが必要になります。

そしてF8セルに
=IF(B8="","",INT((G4-D8*"7:45"-E8)/"7:45")&"日"&TEXT(MOD(G4-D8*"7:45"-E8,"7:45"),"[h]時間mm分"))
という数式を入れたら画像のような感じで表示されるはずです。

以上、参考になれば良いのですが
的外れならごめんなさいね。m(__)m

この回答へのお礼

ご回答ありがとうございました。
全く的外れではなく、教えていただいた通りに入力すると、
確かに分かりやすい表示になります。
半分あきらめていたのですが、色々な方法があることにびっくりしています。

実際に入力しながら、
通常の数字の扱いと時間の関数を用いた数字の扱いとの違いが分かってきました。
また、表作りを始めて、前年度から繰り越される日数や時間も変動する可能性があることに気づきました。
ここからは時間の計算について、勉強してみます。

使うべき関数や勉強のしどころも分かり、本当に助かりました。
ありがとうございました。

お礼日時：2010/04/11 00:36

No.5 回答者： keithin 回答日時： 2010/04/11 01:37

回答No1です。

＞前年度から繰り越される日数や時間も変動する可能性があることに気づきました。

F4の記述が「18日6時間45分」等だった場合に計算に誤りがありました。失礼しました。

D列に日休を整数で入力として
E列に時間給を時:分で入力として

F8:
=IF(D8+E8,INT($F$4)-SUM($D$8:D8)-ROUNDUP(SUM(-MOD($F$4,1),$E$8:E8)/"7:45",0)+MOD("7:45"-SUM(-MOD($F$4,1),$E$8:E8),"7:45"),"")
G8は変更有りません。
=IF(D8,SUMIF($D$8:D8,">=1")+INT(SUMIF($D$8:D8,"<1")/"7:45")+MOD(SUMIF($D$8:D8,"<1"),"7:45"),"")


＞40日だと８日になる

なるほど，それは思わぬ落とし穴でした。

ＦＧ列のセルの表示形式を
G/標準"日"h"時間"m"分"
としてみてください。

併せてF4の記入方法について，前年度からの繰り越し込みで例えば「35日5時間45分」のような場合
=35+"5:45"
のように記入してみてください。


表示形式の「ｄ」を使うと１月３１日を超えると２月になってしまい，８などで表示されます。
他に寄せられている回答は計算結果の値に「年月日　時分秒」形式を採用していないため，この問題を回避できています。

この回答へのお礼

何度も本当にありがとうございます。

なるほど、表示形式の問題だったのですね。

それで、F4を４０にして、
取得時間を１：００とし、
教えていただいた通りに数式を入力し、
表示形式のｄをG/標準にすると、
F8の日数の部分が39.28と表示されます。

表示形式を色々と変えて試してみたのですが、
なかなか上手に表示されません。
今日はこれで作業を中断して、
また明日、他の回答者様の数式とkeithin様の数式を組み合わせて
色々と試しながらやってみようと思います。

それでも、今日の質問で随分前進できました。
お手数をおかけしましたが、本当にありがとうございました。

お礼日時：2010/04/11 03:14

No.6 回答者： keithin 回答日時： 2010/04/11 11:12

なるほど。

列幅をぐぐっと狭めて，日数部分が39だけの表示桁数になるように調整してみてください。
G/標準"日"h"時間"mm"分"
と設定した方が，桁が動かなくてずれなくて良いですね。
なお狭めすぎると######表示になります。


あるいは表示の制約を回避したいときは，やはり他の方から寄せられた回答の方式で
=IF(D8+E8,INT(INT($F$4)-SUM($D$8:D8)-ROUNDUP(SUM(-MOD($F$4,1),$E$8:E8)/"7:45",0))&TEXT(MOD("7:45"-SUM(-MOD($F$4,1),$E$8:E8),"7:45"),"日h時間m分"),"")
などのようにします。

この回答へのお礼

何度もありがとうございました。

keithin様の数式と、
ここでいただいた数々のご助言や数式を参考に、
ほぼ表が完成しました。

自分で数式を変更した点としては、
前年度繰り越しは変更する可能性がありますが、
今年度から新しく付与される日数は２０日なので、
この部分を数式に入れてみました。

また、時間休は分で取得することはないので、
「１：００」という入力ではなく、単に「１」という入力でできるよう、
ご助言を見ながら挑戦してみました。

そして、表示形式を標準にして表示されるようにしました。

その結果、F列(残り有給休暇)は、

=IF(D8+E8,INT((20+$F$4-SUM($D$8:D8)-SUM($E$8:E8)/24))&"日"&TEXT(MOD(20*"7:45"+$F$4*"7:45"-SUM($D$8:D8)*"7:45"-SUM($E$8:E8)/24,"7:45"),"[h]時間mm分"),"")

数式の半分位の意味は分かってはいないのですが、
これで今のところ正しく表示されています。

そして、G列(今年度取得日時数)です。
最初にkeithin様から教えていただいた数式から、
入力形式の変更による補正をして、

=IF(D8+E8,SUM($D$8:D8)+INT(SUM($E$8:E8)/24/"7:45")+MOD(SUM($E$8:E8)/24,"7:45"),"")

としてみました。
この数式で今のところ矛盾なく表示されています。

ただ、この数式では、表示形式をG/標準"日"h"時間"mm"分"にしているため、
日数部分が、０日であれば問題なく、一桁の数字の時は小数表示になります。
そして、列幅を調整することで回避できますが、
そうすると日数が二桁のところで####という表示になってしまいます。

この問題を解決するために、色々と試しているのですが、
１～７時間までは良いのですが、
8時間取得したところで０日０時間15分となったり、
なかなかうまくいきません。

ほぼ完成しているんですけど、、、
もしよろしければ解決策があればお願いしたいのですが…。

お礼日時：2010/04/11 14:12

No.7 ベストアンサー 回答者： tom04 回答日時： 2010/04/11 15:36

No.4です！

またまた・・・横からお邪魔します。

有給の残日数セルは単純に日数の数値のみを入力させたいと思いますので、

もう一度画像をアップしたいと思います。
今回は累計の列も数式を作ってみました。

F8セルに
=IF(B8="","",INT((($F$4-SUM($D$8:D8))*"7:45"-SUM($E$8:E8))/"7:45")&"日"&TEXT(ROUND(MOD(($F$4-SUM($D$8:D8))*"7:45"-SUM($E$8:E8),"7:45"),5),"[h]時間mm分"))

累計のG8セルに
=IF(B8="","",INT((SUM($D$8:D8)*"7:45"+SUM($E$8:E8))/"7:45")&"日"&TEXT(MOD(ROUND(SUM($E$8:E8),5),"7:45"),"[h]時間mm分"))

という数式を入れ、F8・G8セルを範囲指定し、G8セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、今回は参考になれば良いのですが・・・
何度も失礼しました。m(__)m

この回答へのお礼

何度も何度もありがとうございました。
皆様のおかげで年休整理ファイルが完成できました。

最後はTEXT関数の使い方が分かっていれば良かったのですね…。
他にも間違っていた箇所あったようで、本当に助かりました。

完成したシートの最終的な数式を書きます。

(1)　G4：前年度からの繰り越し日時数(入力形式=日数＋"時：分")
　ここは表示形式がd日h時間m分になっているので、後でTEXT関数で表示させようと思います。

(2)　B～E列：入力欄
　取得月日、曜日、取得日数、取得時数(入力形式：１日ならD列に「１」、１時間ならE列に「１」)

(4)　Ｄ列：残日時数
　=IF(B8="","",INT(((20+$G$4-SUM($D$8:D8))*"7:45"-SUM($E$8:E8)/24)/"7:45")&"日"&TEXT(ROUND(MOD(($G$4-SUM($D$8:D8))*"7:45"-SUM($E$8:E8)/24,"7:45"),5),"[h]時間mm分"))

(5)　Ｅ列：今年度累計取得日時数
　=IF(B8="","",INT((SUM($D$8:D8)*"7:45"+SUM($E$8:E8)/24)/"7:45")&"日"&TEXT(MOD(ROUND(SUM($E$8:E8)/24,5),"7:45"),"[h]時間mm分"))


たくさんの方々にアドバイスを頂き本当にありがとうございました。

お礼日時：2010/04/11 17:45

No.8 回答者： KURUMITO 回答日時： 2010/04/11 16:11

Ｆ８セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(F$4="",COUNT(D8:E8)=0),"",INT(((F$4-SUM(D$8:D8))*"7:45"-SUM(E$8:E8)*"1:00")/"7:45")&"日"&INT(MOD(((F$4-SUM(D$8:D8))*"7:45"-SUM(E$8:E8)*"1:00"),"7:45")/"1:00"+0.001)&"時間"&ROUND(MOD((MOD(((F$4-SUM(D$8:D8))*"7:45"-SUM(E$8:E8)*"1:00"),"7:45")/"1:00"+0.001),1)*60,0)&"分")

Ｇ8セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(F$4="",COUNT(D8:E8)=0),"",INT(((SUM(D$8:D8))*"7:45"+SUM(E$8:E8)*"1:00")/"7:45")&"日"&INT(MOD(((SUM(D$8:D8))*"7:45"+SUM(E$8:E8)*"1:00"),"7:45")/"1:00"+0.001)&"時間"&ROUND(MOD((MOD(((SUM(D$8:D8))*"7:45"+SUM(E$8:E8)*"1:00"),"7:45")/"1:00"+0.001),1)*60,0)&"分")

No.9 回答者： KURUMITO 回答日時： 2010/04/11 16:15

回答Ｎｏ8です。

Ｆ4セルには例えば20と入力します。Ｆ8セルには1とか2などの数値を入力します。Ｅ8セルには1時間なら1のように数値を入力します。
答えは何日何時間何分のように表示されます。

No.10 回答者： KURUMITO 回答日時： 2010/04/11 16:17

回答Ｎｏ8です。

Ｆ4セルには例えば20と入力します。Ｆ8セルには1日なら1のように数値を入力します。Ｅ8セルには1時間なら1のように数値を入力します。
答えは何日何時間何分のように表示されます。

この回答へのお礼

数式のご紹介をありがとうございました。
早速Ｅｘｃｅｌに入力したところ、イメージした通りの形式で表示されています。

皆様からいくつかの数式を紹介していただき、
違う部分と似ている部分がありますが、
今の自分にはどこに違いが出てくるのかは分かりません。
これから関数や時間の計算について調べた上で、
自分の分かりやすいような数式を見つけていこうと思います。

この質問で随分勉強させていただきました。
ありがとうございました。

お礼日時：2010/04/11 18:04