７時間15分を１日と換算する計算について

恐れ入ります。
エクセルで以下の計算をしたいのですが、お知恵をお貸しください。

年次休暇の計算をエクセルでしたいと思います。
まず前提として、７時間15分をもって「１日休」と換算します。

そこで、ある人が１６時間の時間休をとったとします。
セルに「１６」と入力すると自動的に２日と１時間３０分という「回答」をさせたいのです。

どのような計算式になるか教えていただけるとたすかります。

どうぞよろしくお願いします。

No.1 ベストアンサー 回答者： mshr1962 回答日時： 2014/01/09 20:55

A1=16なら

=TEXT(INT(TIME(A1,0,0)/"7:15")+MOD(TIME(A1,0,0),"7:15"*1),"d日とh時間m分")

A1=16:00なら
=TEXT(INT(A1/"7:15")+MOD(A1,"7:15"*1),"d日とh時間m分")

この回答へのお礼

ありがとうございます。今回は最も早くご回答いただいた方をベストアンサーとさせてください。

お礼日時：2014/01/11 22:48

No.8 回答者： MarcoRossiItaly 回答日時： 2014/01/11 15:28

No.6 です。

計算誤差が発生する具体的なケースを前回載せていませんでしたね。

誤差は、たまにしか発生しません。さらにそのうちのごく一部に過ぎませんが、例えば添付図 A 列の時間を入力すると、B 列では誤差が発生していることが分かります。

たとえ A 列には整数しか入力しなかったとしても、小数である「7:15」のシリアル値にそもそも誤差が含まれているため、それを使っている限り、誤差が残る場合があるのですね。


整数化は、一つの有効な誤差対策です。全体の数式は、ちょっと長くなってしまいますが。

15 桁以下の整数は、Excel において、（小数から誤差を引き継いだりしていなければ）誤差を含みません。だから整数だけを使って計算すれば、確実に正しい結果が得られます。

次の D2 セルの数式は、(1) A 列の時間を 100 倍して整数化し、(2)それを分単位に直すとともに小数から引き継いでいる誤差を ROUND 関数によって除去し、(3)「7:15」も分単位に直してから（(60*7+15)分）、ご質問の日数・時間数を算出しています。分単位（の 100 倍）だから、全部整数になっていますね。


誤差対策なし
B2　=int("1:00"*a2/"7:15")&" 日と "&text(mod("1:00"*a2,"7:15"),"h 時間 m 分")

7 桁残し
C2　=int(round("1:00"*a2/"7:15",7))&" 日と "&text(round(mod("1:00"*a2,"7:15"),7),"h 時間 m 分")

整数化
D2　=int(round(60*a2*100,0)/((60*7+15)*100))&" 日と "&int(mod(round(60*a2*100,0),(60*7+15)*100)/(60*100))&" 時間 "&mod(mod(round(60*a2*100,0),(60*7+15)*100),60*100)/100&" 分"

C 列・D 列の一致
E2　=c2=d2


なお C2 の方法では、7 桁くらいがちょうどいい桁数です。

誤差を除去する際、四捨五入により残す桁数が少ないほど確実に除去でき、ある程度の桁数さえあれば、ムダにたくさん残していなくても正確な時間のシリアル値に一致してきます。しかし、あまりにも桁数が少ないと、前回から説明しているものとは別の種類の誤差を生んでしまいます。

添付図のように 1,000.00 以上 10,000.00 未満の時間を入力する場合は、最低限 6 桁は必要ですが、余裕を見て 1 桁増やし、小数点第 7 位まで残すこととしました。上の計算では小数点第 14 位・15 位あたりの数字がずれると考えられるので（入力値と「7:15」という 2 数で 1 回演算する場合はそうなる）、この桁数で確実に誤差を除去できていると考えられます。

もしも入力値を小数点第 3 位までにしたいという場合は、8 桁を残すようにすればいいでしょう。

No.7 回答者： kagakusuki 回答日時： 2014/01/11 09:54

　通常ですと、Excelでシリアル値による時間を扱う際には計算誤差が生じる恐れを考えなくてはなりませんが、本件の場合では、時間休をシリアル値ではなく、時間（hour）単位で扱うため、計算誤差が生じる恐れを考慮しなくても済む分、処理が楽になる面があります。

　しかしながら、7時間15分を1日として扱う必要がある事によって、計算誤差の処理など比べ物にならない程に、面倒な事になっています。

　今仮に、Aさんの前年度繰越の年次休暇がB2セルに、1回目に摂った時間休の値が（時間[hour]単位で）D2セルに、2回目に摂った時間休の値がE2セルに、3回目に摂った時間休の値がF2セルに、それぞれ入力されているものとします。
　同様に、Bさんの前年度繰越の年次休暇がB5セルに、1回目に摂った時間休の値がD5セルに、2回目に摂った時間休の値がE5セルに、3回目に摂った時間休の値がF5セルに、それぞれ入力されていて、
Cさんの前年度繰越の年次休暇がB8セルに、1回目に摂った時間休の値がD8セルに、2回目に摂った時間休の値がE8セルに、3回目に摂った時間休の値がF8セルに、それぞれ入力されているものとします。
　そして、時間単位で各時間休の値が入力されているセルの1つ下のセルに、時間休の値を関数を用いて日時分に換算した値を表示させ、
同じく2つ下のセルには休暇日時の残の値を表示させるものとします。（添付画像を参照の事）

　まず、D3セルとD4セルの書式設定の表示形式を[ユーザー定義]の

[<1]h"時間"mm"分";d"日と"h"時間"mm"分"

にして下さい。
　次に、D3セルに次の関数を入力して下さい。

=IF(ISNUMBER(D2),D2/24+INT(D2/7.25)*(1-7.25/24),"")

　次に、D4セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(D2),ISNUMBER(1/(IF(ISNUMBER(LEFT($B2,FIND("日",$B2)-1)+0),LEFT($B2,FIND("日",$B2)-1)*7.25,0)+(0&REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC($B2)," ",),"と",),"間",),1,IF(ISNUMBER(FIND("日",$B2)),FIND("日",$B2),0),)&IF(AND(ISNUMBER(FIND("時間",$B2)),ISERROR(FIND("分",$B2))),"0分",""))*24>=SUM($C2:D2)))), (IF(ISNUMBER(LEFT($B2,FIND("日",$B2)-1)+0),LEFT($B2,FIND("日",$B2)-1)*7.25,0)+(0&REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC($B2)," ",),"と",),"間",),1,IF(ISNUMBER(FIND("日",$B2)),FIND("日",$B2),0),)&IF(AND(ISNUMBER(FIND("時間",$B2)),ISERROR(FIND("分",$B2))),"0分",""))*24-SUM($C2:D2))/24+INT((IF(ISNUMBER(LEFT($B2,FIND("日",$B2)-1)+0),LEFT($B2,FIND("日",$B2)-1)*7.25,0)+(0&REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC($B2)," ",),"と",),"間",),1,IF(ISNUMBER(FIND("日",$B2)),FIND("日",$B2),0),)&IF(AND(ISNUMBER(FIND("時間",$B2)),ISERROR(FIND("分",$B2))),"0分",""))*24-SUM($C2:D2))/7.25)*(1-7.25/24),"")

　次に、B2～B4のセル範囲内にあるセルを結合して下さい。
　次に、D3～D4の範囲をコピーして、E3～F4の範囲に貼り付けて下さい。（必要があれば、貼り付ける範囲を更に右方向に拡大しても構いません）
　次に、D2～F4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　以上で準備は完了で、後はB列に前年度繰越の年次休暇を入力してから、D列～F列の2行目、5行目、8行目に、摂った時間休の値を時間（hour）単位で入力しますと、その1つ下のセルに入力した時間休の値を日時分形式に変換した値が表示され、そのまた下のセルに休暇の斬日時が表示されます。

　尚、この方法で表示する事の出来る休暇の日数は31日までで、それを超える日数を正しく扱う事は出来ませんので、注意して下さい。

No.6 回答者： MarcoRossiItaly 回答日時： 2014/01/10 01:00

手を打っていないと、計算誤差により、正しい答えを表示しないケースがあります。

それでも大体合っていれば気にしないという方針もあるのかもしれませんが、特に MOD 関数がマイナスの時間が発生させる場合は、数式全体がエラーになってしまうので、イマイチです。

次式は、ROUND により誤差対策をしています。ですから A 列をいろんな時間に書き換えても、正しく表示すると思います。

B1　=int(round("1:00"*a1/"7:15",7))&" 日と "&text(round(mod("1:00"*a1,"7:15"),7),"h 時間 m 分")

No.5 回答者： keithin 回答日時： 2014/01/10 00:36

＞たとえはＡさんは20日、Ｂさんは15日3時間、Ｃさんは６日２時間15分

無理にやって出来ない事はありませんが、素直に
　２０日×7.25時間＝１４５時間残り
　１５日×7.25＋3時間＝１１１時間４５分残り
といった具合に「何時間残してるのか」に数字を戻してから今年度の「１時間単位」の消費を計算していくような手順をお勧めします。
またその際は、分単位で残してた時間の取り扱いを具体的にどうする事にしてるのかとかも、あなたの会社のやり方をしっかり条件提示してもらう必要があります。



＞さらに、もうひとつ甘えさせてください。

お断りします。
こういう具合にダラダラとついでについでに違う事も聞いちゃえと、終わらないご相談があんまり多くて迷惑しています。
また元のご相談がすっかり無かったことになって、新しい方が解決すると嬉しくなっちゃうのも「元のご相談に回答してくださった皆さん」に大層失礼と思いませんか。

このご相談は「７時間１５分を１日で数える計算方法について」のご質問でした。
それが解決したらこのご相談は一回「解決」の操作で閉じてから、新しいご相談は別のご質問として新たに投稿してください。
そうしてくれたら、追加ご質問の「具体的な計算式の立て方」とかについても、改めて回答できるかもしれません。（もちろん他の皆さんからも、きっとまた良いやり方が幾つも寄せられると思いますけど。）

この回答へのお礼

ご助言ありがとうございます。改めて具体的な質問をさせていただきます。

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

No.4 回答者： keithin 回答日時： 2014/01/09 21:26

Ａ１には「１６」と「１時簡単位」で記入します。

＞７時間15分をもって「１日休」と換算

７時間１５分を１時間単位で表すと７．２５時間です。
これだけの時間数ごとに「１日」なので、計算は

=INT(A1/7.25) & "日" & TEXT(MOD(A1,7.25)/24,"h時間mm分")

といった具合にすることもできます。



＃まぁ個人的には
=TEXT(A1*"1:0"/"7:15","d日")&TEXT(MOD(A1*"1:0","7:15"),"h時間mm分")
とかしますけど。


＃余談ついでにＡ１に「１６時間１５分」のつもりで「１６．１５」とか記入したい場合
=TEXT(TEXT(A1*100,"0!:00")/"7:15","d日")&TEXT(MOD(TEXT(A1*100,"0!:00"),"7:15"),"h時間mm分")
とかで。

この回答へのお礼

みなさん、大変迅速な御回答ありがとうございます。
回答者４様のところでまとめてお礼させてください。

さらに、もうひとつ甘えさせてください。
人によって前年度繰越の年次休暇が違うとします。

たとえはＡさんは20日、Ｂさんは15日3時間、Ｃさんは６日２時間15分

この繰越年次休暇から消費した「○日△時間◇分」を引いた残りをエクセル上で実現したいのです。
一度にお尋ねせず、すみません。

お礼日時：2014/01/09 23:13

No.3 回答者： chayamati 回答日時： 2014/01/09 21:22

24時間は1日ですね

A1に7:15
A2に１６を入れ
B2に日数
C2に残り時間を求めます

B2=INT(A2/24/$A$1)
C2=MOD(A2/24,$A$1)

No.2 回答者： bunjii 回答日時： 2014/01/09 21:02

A列に任意の時間数を h:mm の書式で入力することを条件にして、B列に日数、C列に端数時間を算出することで良ければ次の式で良いでしょう。

B2=INT(A2/"7:15")
C2=MOD(A2,"7:15")

結果を添付画像で確認してください。