Results of 1 - 1 of about 19634 for 20�����������������������... (0.004 sec.)
- [[20180307202324]]
- #score: 2681
- @digest: 13e933af2da2e114b58f79bf172f55e3
- @id: 75761
- @mdate: 2018-03-12T12:55:58Z
- @size: 27479
- @type: text/plain
- #keywords: nebukuro3 (147061), 夜法 (67402), 季休 (62101), 半休 (60744), 法定 (57173), 夏季 (51963), 終業 (35775), 深夜 (31942), 業時 (29859), 日始 (28254), 始業 (27043), 業深 (25808), 間シ (25291), 明け (22442), 間終 (21255), 間残 (21247), 残業 (21219), 金8 (20310), 定休 (19345), （ne (19246), 有休 (18214), 公式 (12317), 2018 (11627), 休日 (11024), 時間 (10911), 夜時 (10887), 休み (9520), 果図 (9447), 夜残 (8860), 定時 (8001), 日20 (7822), d8 (7286)
- 『深夜残業時間計算』（nebukuro3）
勤務表を作成していますがお手あけですご指導願います。 C列:始業時間 D列:終業時間 を入力したら深夜残業を自動表示（整数）したいです。（E列残業時間を入力したら終業時間は変更されます。） 現状のシート:vlookupで勤務表が出来ています。 A B C D E F ------------------------------------------------------------------------- 7 日 曜日 始業時間 終業時間 残業 深夜残業 -------------------------------------------------------------------------- 8 1 月 8:00 20:30 10 2 火 20:30 8:30 5.5 12 3 水 明け 14 4 木 休み 条件: 勤務体系:8:00〜20:30 20:00〜8:30 明け 休み （上記表参照） 半休制度:残業D列に「半休」と入力したら20:30→14:30 8:30→14:30 深夜残業時間帯:22:00〜24:00 1:00〜4:30 休憩時間帯:1:00〜2:00 5:00〜5:30 12:00〜13:00 17:00〜17:30 （半休余残:これだと、8:00〜14:30 20:00〜2:30 の半休の対応が出来ていませんが、始業・終業時間を手動入力とします。） .=IFERROR(IF($C$5="","",VLOOKUP($B8,DB!$G:$J,3,TRUE))+IF(E8="","0",VLOOKUP(E8,DB!$K:$L,2,0)),"") ＜ 使用 Excel:Excel2008(Mac)、使用 OS:Windows10 ＞ ---- 使用 Excel:Excel2008(Mac)ではなく、使用 Excel:Excel2010です。 （nebukuro3） 2018/03/07(水) 21:24 ---- サンプルと説明が簡略すぎて、よく分からないです。 「こんなの簡単だねっ」と言う浅薄なレベルで作った案 F8セル =IF(C8="20:30"*1,IF(D8<>"半休",5.5,3.5),"") <結果図> 行 _A_ __B__ ____C____ ____D____ __E__ ____F____ 7 日 曜日 始業時間 終業時間 残業 深夜残業 8 1 月 8:00 20:30 9 2 火 20:30 8:30 5.5 10 3 水 明け 11 4 木 休み 12 5 金 8:00 半休 13 6 土 20:30 半休 3.5 以上で問題があったら(多分あるでしょうね）、詳細な追加説明をしてください。 （半平太） 2018/03/08(木) 13:14 ---- 本当にご検討頂きありがとうございます。 始めての関数表を作成しています、改善項目がありましたら、ご教授願います。 F8セル =IF(C8="20:30"*1,IF(D8<>"半休",5.5,3.5),"") 説明不足のため、目的の何点かはうまくゆきましたが、問題点があり補足説明させていただきます。5.5 3.5 表示OK 訂正:C列20:30→20:00 D列→E列に残業時間（ここに残業時間（例:2時間なら2）又は半休と入力します。 補足説明 現状の良い点 ・C列に有休・夏季休日を入力するとDセル・Fセルが消去される。 ・E列に残業・半休を入力するとDセルの終業時間が変わる。 （8:00〜20:30勤務のEセル残業を入力すると深夜時間が表示される。） 例:Eセル2入力するとFセルに0.5表示（22:00〜深夜残業のためです。） ・G列法定に11（休み出勤）を入力すると、休み行の業始業時間 終業時間 が8:00 20:30となります。 ・E列残業に11（深夜出勤）を入力すると、明け行の始業時間 終業時間 が20:00 8:30となります。 今回の問題点 ・深夜勤務の場合にCセル始業時間 Dセル終業時間が変わればFセル深夜時間も変わりますが、出来ない。 （深夜時間が固定で、始業時間 終業時間が深夜時間帯で変われば深夜時間はかわりますね） （例:F列セルに半休入力（終業時間が変わる）すると、深夜3.5と表示されたい。Dせる終業時間8:30なら深夜は5.5） 深夜時間規則 22:00〜24:00 1:00〜4:30 休憩時間規則 0:00〜1:00 5:00〜5:30 MAX関数でできるのかな 困った 現状シート説明 : DBシートよりVblookupで作成しています。 別シート（名:DB) G H I J K L M N 日 付 始業時間 始業時間 深夜 法定休日 残業 残業 深夜 ---------------------------------------------------------------------------------------------- 2018/4/1 明け 半休 #### 3.0 2018/4/2 休み 0.5 0:30 2018/4/3 8:00 20:30 1.0 1:00 2018/4/4 20:00 8:30 5.5 1.5 1:30 2018/4/5 明け 2.0 2:00 0.5 2018/4/6 休み 2.5 2:30 1.0 2018/4/7 8:00 20:30 3.0 3:00 1.5 2018/4/8 20:00 8:30 5.5 3.5 3:30 2.0 2018/4/9 明け 4.0 4:00 2.5 2018/4/10 休み 4.5 5:30 2.5 2018/4/11 8:00 20:30 5.0 6:00 2.5 2018/4/12 20:00 8:30 5.5 5.5 6:30 3.0 2018/4/13 明け 6.0 7:00 3.5 2018/4/14 休み 6.5 7:30 4.0 2018/4/15 8:00 20:30 7.0 8:00 4.5 2018/4/16 20:00 8:30 5.5 7.5 8:30 5.0 2018/4/17 明け 8.0 9:00 5.5 から今回のシートを作成しています。 C列:=IF(G8=11,"8:00",IF(E8=11,"20:00",IF($C$5="","",VLOOKUP($B8,DB!$G:$J,2,0)))) D列:=IF(E8=11,"8:30",IF(G8=11,"20:30",IF(C8="有休","",IF(C8="夏季休日","",IFERROR(IF($C$5="","",VLOOKUP($B8,DB!$G:$J,3,TRUE))+IF(E8="","0",VLOOKUP(E8,DB!$L:$M,2,0)),""))))) E列:空白（入力セル）残業・半休を入力する。 F列:今回の問題式=IFERROR(IF($C$5="","",IF(D8>=TIMEVALUE("22:00"),VLOOKUP(E8,DB!$L:$N,3,0),VLOOKUP($B8,DB!$G:$J,4,0))),"") G列:法定休日 空白 （休日出勤した場合入力（例:11） （nebukuro3） 2018/03/08(木) 17:42 ---- ＞ <DB> ＞ 行 _L_ __M__ _N_ 時刻 ＞ 9 3.5 3:30 2 24:00 ＞ 10 4 4:00 2.5 24:30 ←休憩中なので2.5ではなく、2.0では? ＞ 11 4.5 5:30 2.5 25:00 ←同上? ＞ 12 5 6:00 2.5 25:30 ＞ 13 5.5 6:30 3 (1) C8セル =IF(G8=11,"8:00"*1,IF(E8=11,"20:00"*1,IF($C$5="","",VLOOKUP($B8,DB!$G:$J,2,0)))) (2) D8セル =IF(OR($C8={"有休";"夏季休日";"明け";"休み"}),"",MOD(C8+"12:30"-0.25*(E8="半休"),1)) (3) F8セル =IF(D8="","",IF(G8=11,0,IF(E8=11,5.5,(MAX(0,MIN(1,D8+(D8<C8)+N(E8)/24)-"22:00")+MAX(0,MIN("28:30",D8+(D8<C8)+N(E8)/24)-"25:00"))*24))) <結果図> 行 ______A______ ___B___ ____C____ _____D_____ ______ E ______ _F_ _____ G _____ 7 日付 始業時間 終業時間 残・半(休出=11) 法休(休出=11) 8 2018/4/1(日) 4月1日 8:00 20:30 0 11 9 2018/4/2(月) 4月2日 20:00 8:30 11 5.5 10 2018/4/3(火) 4月3日 8:00 20:30 2 0.5 11 2018/4/4(水) 4月4日 20:00 8:30 2 5.5 12 2018/4/5(木) 4月5日 明け 13 2018/4/6(金) 4月6日 休み 14 2018/4/7(土) 4月7日 8:00 14:30 半休 0 15 2018/4/8(日) 4月8日 20:00 2:30 半休 3.5 16 2018/4/9(月) 4月9日 明け 17 2018/4/10(火) 4月10日 休み 18 2018/4/11(水) 4月11日 8:00 20:30 0 19 2018/4/12(木) 4月12日 有休 20 2018/4/13(金) 4月13日 夏季休日 21 2018/4/14(土) 4月14日 休み 半休 22 2018/4/15(日) 4月15日 8:00 20:30 4 2.0 23 2018/4/16(月) 4月16日 20:00 8:30 5.5 24 2018/4/17(火) 4月17日 明け （半平太） 2018/03/08(木) 23:28 ---- 何かすごい式ができましたね F8セル =IF(D8="","",IF(G8=11,0,IF(E8=11,5.5,(MAX(0,MIN(1,D8+(D8<C8)+N(E8)/24)-"22:00")+MAX(0,MIN("28:30",D8+(D8<C8)+N(E8)/24)-"25:00"))*24))) 勉強中のため、内容説明していただけますでしょうか。 （nebukuro3） 2018/03/09(金) 06:52 ---- 2.5→2.0 訂正ご指摘ありがとうございます。 早速、関数式をC8 D8 F8 に張り付けしてみましたが、各セルに数式が表れただけで、良い結果が表れません でした、何か設定がまちがえたか模索中です、検証もう少しお待ちください。 ど素人なので、単純なみすと思います。 質問:A列 B列 に日付データーが有りますが何か今回の式に関連していますか? こちらのデーターではA列:数字 B列:日付を曜日に変換しています。 （nebukuro3） 2018/03/09(金) 21:50 ---- すみません、単純に式に空白をコピーしていました。 B C D E F G 曜日 始業時間 終業時間 残業 深夜 法定 4月1日 休み 4月2日 8:00 20:30 5.5 4月3日 20:00 8:30 5.5 4月4日 明け 4月2日の深夜に5.5が表示されました・? 空白がよいです。 また、残業（例:2）を入力しても、5.5です。 0.5表示が良いです。 また、私の変な単純みすかな 半平太さんすごいですね、私が数日かっかた式を一瞬で書き表せるのはすごい。 （nebukuro3） 2018/03/09(金) 22:22 ---- 何度もすみません。 残業時間入力で終業時間・深夜・法定時間が変わってほしいです。 無理難題を申し上げて申し訳ございません、今の私の知識ではギブアップ状態です。 （nebukuro3） 2018/03/09(金) 22:36 ---- 私の作った式の問題点 木曜（明けの日）出勤に残業した場合、残業（例2時間）で11→13を入力すると変更すると、始業時間が明けとなる 金曜（休み）出勤に残業した場合、残業（例2時間）で11→13を入力すると、始業時間が休みとなる。 半休時 深夜残業が変わらない。（今回の問題点） 曜日 始業時間 終業時間 残業 深夜 法定休日 火 8:00 5:30 8.0 5.0 水 20:00 10:30 2.0 5.5 木 明け 金 8:00 20:30 2.0 0.5 11.0 土 有休 日 夏季休日 水 8:00 14:30 半休 木 20:00 2:30 半休 5.5 （nebukuro3） 2018/03/09(金) 23:06 ---- ＞4月2日の深夜に5.5が表示されました・? 空白がよいです。 当方の結果と合ってないですけど、私の結果図と見比べてください。 ＞残業時間入力で終業時間・深夜・法定時間が変わってほしいです。 ‾‾‾‾‾‾‾‾‾‾‾↑‾‾‾‾‾‾‾‾‾ ‾‾‾↑‾‾‾ この説明は初めからあったですか? 法定時間ってなんですか?(説明ありましたか?） ＞木曜（明けの日）出勤に残業した場合、残業（例2時間）で11→13を入力すると変更すると、 ＞始業時間が明けとなる ここ、全然理解できないです。 明けの日に出勤が有って、残業もやるんですか? こちらは正しい結果か何なのか知りたいんですけど・・ ダメなもの見せられても、説明の補足にならないです。 正しい結果図と、どこの列が手入力データで、どこの列が数式なのか明示してください。 （半平太） 2018/03/09(金) 23:39 ---- ＞当方の結果と合ってないですけど、私の結果図と見比べてください。 ご指摘の通り、結果図と異なります。 (1) C8セル =IF(G8=11,"8:00"*1,IF(E8=11,"20:00"*1,IF($C$5="","",VLOOKUP($B8,DB!$G:$J,2,0)))) (2) D8セル =IF(OR($C8={"有休";"夏季休日";"明け";"休み"}),"",MOD(C8+"12:30"-0.25*(E8="半休"),1)) (3) F8セル =IF(D8="","",IF(G8=11,0,IF(E8=11,5.5,(MAX(0,MIN(1,D8+(D8<C8)+N(E8)/24)-"22:00")+MAX(0,MIN("28:30",D8+(D8<C8)+N(E8)/24)-"25:00"))*24))) をコピペしても、良い結果出ません。半平太さんの大切な公式が展開できない、困った また単純なミスを おかしているか確認中です。（3）式が今回の主題ですもの こちらの結果図 日 曜日 始業時間 終業時間 残業 深夜 法定休日 1 月 休み 2 火 8:00 20:30 2.0 5.5 3 水 20:00 8:30 2.0 5.5 4 木 明け 5 金 8:00 20:30 11.0 6 土 8:00 20:30 4.0 5.5 7 日 20:00 8:30 5.5 8 月 20:00 8:30 11.0 5.5 9 火 休み 10 水 8:00 14:30 半休 5.5 11 木 20:00 2:30 半休 3.5 12 金 明け 13 土 休み 14 日 有休 15 月 夏季休日 16 火 明け （nebukuro3） 2018/03/10(土) 09:32 ---- ＞残業時間入力で終業時間・深夜・法定時間が変わってほしいです。 ‾‾‾‾‾‾‾‾‾‾‾↑‾‾‾‾‾‾‾‾‾ ‾‾‾↑‾‾‾ この説明は初めからあったですか? 法定時間ってなんですか?(説明ありましたか?） 勝手に数式を送ってできる物と思い込んでいました、説明不足で申し訳ございませんでした。 お送りした式では、残業時間入力で修業時間・深夜時間が変わります。今回の主題 深夜時間の変更が正確にでないです。 法定時間の変更は無理と思っています。半平太さんの式をみて欲が出ました。 法定時間:法定休日（休み）に出勤した時間数です。 （勤務表入力例:今回は休み行に法定休日「11」を入力すると勤務時間が表記されます。） C列 =IF(G8=11,"8:00",IF(E8=11,"20:00",IF($C$5="","",VLOOKUP($B8,DB!$G:$J,2,0)))) D列 =IF(E8=11,"8:30",IF(G8=11,"20:30",IF(C8="有休","",IF(C8="夏季休日","",IFERROR(IF($C$5="","",VLOOKUP($B8,DB!$G:$J,3,TRUE))+IF(E8="","0",VLOOKUP(E8,DB!$L:$M,2,0)),""))))) F列=IFERROR(IF($C$5="","",IF(D8>=TIMEVALUE("22:00"),VLOOKUP(E8,DB!$L:$N,3,0),VLOOKUP($B8,DB!$G:$J,4,0))),"") 日 曜日 始業時間 終業時間 残業 深夜 法定休日 1 月 休み 2 火 8:00 22:30 2.0 0.5 3 水 20:00 10:30 2.0 5.5 4 木 明け 5 金 8:00 20:30 4.0 2.0 11.0 6 土 8:00 20:30 7 日 20:00 8:30 5.5 8 月 20:00 8:30 11.0 9 火 休み 10 水 8:00 14:30 半休 11 木 20:00 2:30 半休 5.5 （nebukuro3） 2018/03/10(土) 10:03 ---- ＞こちらの結果図 それが希望図(つまり正しい結果図）なのですか? あと、私の式はこれを組み込んでないですから、D列とE列の数式はもう使えません。 ↓ ＞残業時間入力で終業時間・深夜・法定時間が変わってほしいです。 ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾ ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾ また法定時間とは何か分からないので、「法定時間」については何もしていないです。 （半平太） 2018/03/10(土) 10:14 ---- 色々と何題を申し上げて申し訳ございませでした。 半平太さんの頭脳を感知して欲張りが出てしまいました。 本題以外は無視して下さい。 本題: 何故この公式が洋太平さんの結果図と私の結果図と異なるのか意味不明です。 コピペに間違いがないのですが?ん・・・・・・ F列 =IF(D8="","",IF(G8=11,0,IF(E8=11,5.5,(MAX(0,MIN(1,D8+(D8<C8)+N(E8)/24)-"22:00")+MAX(0,MIN("28:30",D8+(D8<C8)+N(E8)/24)-"25:00"))*24))) 参考追伸:法定時間=法定休日（休みの日に勤務した時間） （nebukuro3） 2018/03/10(土) 11:00 ---- 大切な報告を忘れていました、半平太さんの公式は最高です、この結果を求めていました。 （nebukuro3） 2018/03/10(土) 11:06 ---- こちらで展開出来ないチェック項目をご教授願います。 単純なミスだと思います。 （nebukuro3） 2018/03/10(土) 11:08 ---- 半平太さん 混乱しています。 もう一度公式をお送り願います。 （nebukuro3） 2018/03/10(土) 11:12 ---- F列:深夜 2行 深夜5.5→0.5 6行 5.5→2.0 10行 5.5→空白 こちらの展開図結果 日 曜日 始業時間 終業時間 残業 深夜 法定 休日 1 月 休み 2 火 8:00 20:30 2.0 5.5 3 水 20:00 8:30 2.0 5.5 4 木 明け 5 金 8:00 20:30 11.0 6 土 8:00 20:30 4.0 5.5 7 日 20:00 8:30 5.5 8 月 20:00 8:30 11.0 5.5 9 火 休み 10 水 8:00 14:30 半休 5.5 11 木 20:00 2:30 半休 3.5 12 金 明け 13 土 休み 14 日 有休 15 月 夏季休日 16 火 明け （nebukuro3） 2018/03/10(土) 11:42 ---- ・・で、希望図って掲示頂いたのですか? 私には、何がそれなのか認識できません。 YESなら、アップしたレスの日時を書いてください。 NOなら、進展はありません。 （半平太） 2018/03/10(土) 13:54 ---- 希望図です。 日 曜日 始業時間 終業時間 残業 深夜 法定休日 1 月 休み 2 火 8:00 22:30 2.0 0.5 3 水 20:00 10:30 2.0 5.5 4 木 明け 5 金 8:00 20:30 11.0 6 土 8:00 0:30 4.0 2.0 7 日 20:00 8:30 5.5 8 月 20:00 8:30 11.0 9 火 休み 10 水 8:00 14:30 半休 11 木 20:00 2:30 半休 3.5 12 金 明け 13 土 休み 14 日 有休 15 月 夏季休日 16 火 明け （nebukuro3） 2018/03/10(土) 16:26 ---- 私がF8に張り付ける数式を間違えてる可能性が大きいです。 大変申し訳ありませんが、再度最終数式をお送り下さい。 （nebukuro3） 2018/03/10(土) 16:44 ---- ＞希望図です。 ＞日 曜日 始業時間 終業時間 残業 深夜 法定休日 ＞: : : : : : : ＞8 月 20:00 8:30 11.0 8日(月曜) に「深夜が5.5にならない理由」は何ですか? （半平太） 2018/03/10(土) 17:06 ---- 混乱していますね、5.5が正解です。 エクセル2010使用しています。 半平太さんの結果図 F列が正解です。 何故、半平太さんの所で良い結果が出て、こちらで式コピペしてうまく行かないか模索中です。 =IF(D8="","",IF(G8=11,0,IF(E8=11,5.5,(MAX(0,MIN(1,D8+(D8<C8)+N(E8)/24)-"22:00")+MAX(0,MIN("28:30",D8+(D8<C8)+N(E8)/24)-"25:00"))*24))) 単純なミスと思います。 C列 D列 良い結果が出ました。 半平太さん素人のお使い下さいまして恐縮してます。 （nebukuro3） 2018/03/10(土) 17:23 ---- (1) C8セル =IF(G8=11,"8:00"*1,IF(E8=11,"20:00"*1,IF($C$5="","",VLOOKUP($B8,DB!$G:$J,2,0)))) (2) D8セル =IF(OR($C8={"有休";"夏季休日";"明け";"休み"}),"",IF(E8=11,"8:30"*1,TEXT(MOD(C8+"12:30"-0.25*(E8="半休")+N(E8)/24,1),"[h]:m")*1)) (3) F8セル =IF(D8="","",IF(G8=11,0,IF(E8=11,5.5,ROUND((MAX(0,MIN(1,D8+(D8<C8))-"22:00")+MAX(0,MIN("28:30",D8+(D8<C8))-"25:00"))*24,8)))) ‾↑‾‾ ‾‾‾‾‾‾‾‾‾‾‾‾‾↑‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾ ‾‾‾‾‾‾‾‾‾‾‾‾‾‾↑‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾ ‾↑‾ 1 2 3 4 1. 小数演算誤差対策（下8桁で四捨五入) 2. 22:00〜24:00 の勤務時間を算出 3. 25:00〜28:30 の勤務時間を算出 4. 時間シリアル値を標準値に換算(1:00を単なる1に変換) <結果図> 行 ______B______ ____C____ _____D_____ __E__ __F__ __G__ 7 曜日 始業時間 終業時間 残業 深夜 法定 8 2018/4/1(日) 休み 9 2018/4/2(月) 8:00 22:30 2 0.5 10 2018/4/3(火) 20:00 10:30 2 5.5 11 2018/4/4(水) 明け 12 2018/4/5(木) 8:00 20:30 0 11 13 2018/4/6(金) 8:00 0:30 4 2 14 2018/4/7(土) 20:00 8:30 5.5 15 2018/4/8(日) 20:00 8:30 11 5.5 16 2018/4/9(月) 休み 17 2018/4/10(火) 8:00 14:30 半休 0 18 2018/4/11(水) 20:00 2:30 半休 3.5 19 2018/4/12(木) 明け 20 2018/4/13(金) 休み 21 2018/4/14(土) 有休 22 2018/4/15(日) 夏季休日 23 2018/4/16(月) 明け （半平太） 2018/03/10(土) 17:28 ---- 度々の返信対抗、半平太さんありがとうございます。 残業時間入力により、終業時間変更対応感謝しております。 やっと冷静に現状報告ができる状態なりましたので報告させて頂きます。 1、半平太さんの＜結果図＞が私の要求する結果です。 2.半平太さんの結果と私の結果が異なることが問題です。 3.こちらの詳細入力・調査を報告します。 F8セルに入力 =IF(D8="","",IF(G8=11,0,IF(E8=11,5.5,ROUND((MAX(0,MIN(1,D8+(D8<C8))-"22:00")+MAX(0,MIN("28:30",D8+(D8<C8))-"25:00"))*24,8)))) こちらの結果 日 曜日 始業時間 終業時間 残業 深夜 法定休日 -------------------------------------------------------------------------------- 1 月 休み 2 火 8:00 22:30 2.0 5.5→0.5 3 水 20:00 10:30 2.0 5.5 4 木 明け 5 金 8:00 20:30 11.0 6 土 8:00 0:30 4.0 2.0 7 日 20:00 8:30 5.5 8 月 20:00 8:30 11.0 5.5 9 火 休み 10 水 8:00 14:30 半休 5.5 →空白 11 木 20:00 2:30 半休 3.5 12 金 明け 13 土 休み 14 日 有休 15 月 夏季休日 16 火 明け 17 水 休み 18 木 8:00 20:30 5.5 19 金 20:00 8:30 5.5 20 土 明け 半平太さんの結果図と私の結果結果図との違い 2行 深夜 正解 0.5が5.5 10行 深夜 正解 空白が5.5 4.調査（知識不足ですが色々やってみました）私のエクセルソフトがおかしいのかな・? 問題解決糸口が見つかれば、今回の主題が解決できます。 1: =IF(H10="","",IF(L10=11,0,IF(I10=11,5.5,(MAX(0,MIN(1,I10+(I10<H10)+N(E10)/24)-"22:00"))*24))) 結果 8:00 20:30 深夜 2.0 2:=IF(H18="","",IF(L18=11,0,IF(J18=11,5.5,MAX(0,MIN("28:30",I18+(I18<H18)+N(J18)/24)-"25:00"))*24)) 結果 8:00 20:30 深夜 3.5 3:残業時間変更に伴う深夜残業時間変化 結果 残業0〜3 まで深夜5.5表示 残業3.5〜8.0 で 残業2.0〜5.5 変化 8:00 20:30 5.5 8:00 21:30 1.0 5.5 8:00 22:30 2.0 5.5 8:00 23:30 3.0 5.5 8:00 3.5 2.0 何故0:00に終業時刻が空白なのかな? 8:00 0:30 4.0 2.0 8:00 1:30 5.0 2.5 8:00 2:30 6.0 3.5 8:00 3:30 7.0 4.5 8:00 4:30 8.0 5.5 質問 聞き流してください。 有る時間帯に決められた時間帯が当てはまれば、その時間計を表示することは、エクセル関数でできるのですか? 例:20:00〜2:30 深夜3.5 2:30〜8:30 深夜2.0 始業・終業時間を入力するだけで、深夜表示 当社では有りえない事例ですが、今後の知識として知っていたいです。 半平太さんあなたはもしかして、プロではないですか? 今回のやり取りの対応・内容からして一般マニアとは思えません、どうなんでしょうかね・・・・ （nebukuro3） 2018/03/11(日) 18:00 ---- そちらの実態が分かりませんが、多分、nebukuro3さんの基礎知識不足でしょう。 つまり「文字列の8:00」は「時間シリアル値の8:00」とは異なると言うことです。 同じと思える時もありますので、慣れないと混乱するかも知れません。 数値と同じと思えるケース その文字列に四則演算を施した時 MIN、MAX()の中に直接書いた時 → 逆に言うと、セルに書いたものは数値型と同じではない なので、こう言う数式は危ないので作りません。 ↓ ＞=IF(E8=11,"8:30",IF(G8=11,"20:30",・・・・・・・・・・・ その後、そのセルに四則演算を施す工程が入れば、問題は表面化しませんが、 仮にそのセルが12:00より小さいかどうかチェックが必要になったら、面倒に巻き込まれます。 ・・勿論、一般マニアならそれでも慌てずに対応できるスキルはありますが・・ 通常は、初めからこんな風に書いて面倒を回避します。 ↓ =IF(E8=11,"8:30"*1,IF(G8=11,"20:30"*1,・・・・・・・・・・・ ＞有る時間帯に決められた時間帯が当てはまれば、 ＞その時間計を表示することは、エクセル関数でできるのですか? それは既に実例で示しましたよね? 開始(S)から終了(E) の間に休憩(aからb）とかの時間と重複する時間を求めるには、 Max(0,Min(b,E)-Max(a,S)) とします ただ注意しないといけないのは、2:30は翌日の2:30なので、26:30に換算する必要があるということです。 ↓ 例:20:00〜2:30 (S〜E) つまり、1日足す必要があります。 時間シリアル値的に見るなら「1を加える」ことになります。 幸い、そう言うケースでは24時間を超えない前提では、E<S の関係にあるので、 26:30 は E+(E<S) で求められます。 これは E+1 と同じことになります。 何故ならE<S は成立すると True となりますが、 真偽値に四則演算を施すと、エクセル語では True→1、False→0 としてくれるからです。 この性質は昔から利用されており、SUMPRODUCT関数を応用する時、よく活用されています。 ※Sumifs・Countifsが作られてから、少し出番が減りました。 （半平太） 2018/03/11(日) 20:32 ---- 半平太さん、ご教授ありがとうごいざいます、初心者には大変勉強になります。 たったこれだけで、重大問題発生ですか、「エクセル関数」奥が深いですね。 ・時間シリアル値*1を今後追加記入いたします。 ・Max(0,Min(b,E)-Max(a,S)) 24時間を超えるとE+(E<S) True→1、False→0 勉強します。 =IF(D8="","",IF(G8=11,0,IF(E8=11,5.5,ROUND((MAX(0,MIN(1,D8+(D8<C8))-"22:00")+MAX(0,MIN("28:30",D8+(D8<C8))-"25:00"))*24,8)))) 混乱しないように分けて考えて見ました。猛勉強中です。 例:勤務 8:00〜20:30 ROUND((MAX(0,MIN(1,D8+(D8<C8))-"22:00") 結果2時間 MAX(0,MIN("28:30",D8+(D8<C8))-"25:00")) 結果3.5時間 *24,8)))) 深夜合計:2+3.5=5.5 洋太平さんの公式を私ながらの単純計算してみても5.5時間となります。 どこに、深夜0表示が示される公式があるか、ご教授願います。（同じ質問ですみません。 また、0:00以降はMAX(0,MIN("28:30",D8+(D8<C8))-"25:00"))の計算結果が深夜に現れます。 私なりに納得しています。 F列公式コピペの数式を私が間違えている可能性があります。 大変恐縮ですが、もう一度数式を添付願えませんか、すみません。 追伸:勤務 8:00〜20:30 残業 3.5 で 終業時間 空白 問題 0:00表示方法があれば教えてください。 （nebukuro3） 2018/03/12(月) 09:23 ---- ＞・Max(0,Min(b,E)-Max(a,S)) 24時間を超えるとE+(E<S) ‾‾‾‾‾‾‾‾↑‾‾‾‾‾‾‾‾‾‾‾ 「深夜24時を超えていても、いなくても対応できるように」です。 ＞・時間シリアル値*1を今後追加記入いたします。 ‾‾‾‾‾↑‾‾‾‾‾‾‾‾ 時間シリアル値なら不要です。 「時間シリアル値に見える文字列」は「*1」が必要。(分かっていると思いますが念のため) ＞ ROUND((MAX(0,MIN(1,D8+(D8<C8))-"22:00") 結果2時間 それ、まだ解決しないですか? 当方はそちらの状況が分かっていません。 推測で、C8セルが「時間シリアル値に見える文字列」ではないか、と言っているんですけど? どこか空いているセルに下式を入れて確認してください。 空きセル =IF(TYPE(C8)=2,"文字の","数値の")&TEXT(C8,"[h]:mmですよ") 結果例→ 文字の8:00ですよ 結果例→ 数値の8:00ですよ 文字なら、(D8<C8) が面倒を起こしています。(正しい結果が出ない） 対症療法なら(D8*1<C8*1) で問題は回避できますが、 根本原因を探って、そんなつまんない対応策は使わないで良いようにするのが本筋です。 ＞F列公式コピペの数式を私が間違えている可能性があります。 問題はF列じゃなく、他の列にあります。 DBシートのデータに「時間シリアル値に見える文字列」が在るのが根本原因かも知れません。 こちらでは、そんな真相は分からないです。千里眼じゃないんですから。 （半平太） 2018/03/12(月) 10:12 ---- ご教授ありがとうございます。今 喜びで一杯です。 =IF(G8=11,"8:00"*1,IF(E8=11,"20:00"*1,IF($C$5="","",VLOOKUP($B8,DB!$G:$J,2,0)))) のVLOOKUPで表した時間が「文字」でした。 対症療法を(D8*1<C8*1)行った結果 良い結果が出ました。 基本対応は何か VLOOKUP 元データーの時間に*1追加 で 本筋な対応ですか、基本を教えて下さい。 =IF(H3="8:00"*1,"20:00"*1,IF(H3="20:00"*1,"明け",IF(H3="明け","休み","8:00"*1))) セル書式設定 時刻 0:00 に設定しました。 （nebukuro3） 2018/03/12(月) 15:31 ---- ＞VLOOKUP 元データーの時間に*1追加 で 本筋な対応ですか、 それは中間筋です。 私としては、DBシートのH列を数値に直すのが本筋なんだろうと思いますが、 それが何故、文字型になっているのか(必然なのか、はたまた単純なミスなのか）、 その事情は当方では分からないです。 そちらで判断して、そちらで最終的な対応をしてください。 私の出番はもうないです。 （半平太） 2018/03/12(月) 18:06 ---- 基本指導ありがとうございます。 最終質問 勤務8:00〜20:30 で残業3.5 を行った場合 終業時間が E列空白になりました。 0:00又は24:00 表示は可能ですか? （nebukuro3） 2018/03/12(月) 19:37 ---- こちらでは0:00と出ていますけども? <結果図> 行 ______B______ ____C____ _____D_____ __E__ __F__ __G__ 7 曜日 始業時間 終業時間 残業 深夜 法定 8 2018/4/1(日) 8:00 0:00 3.5 2 D列のセルの書式(表示形式）を「0を空白に見えるように設定している」、なんてことは無いですか? 少なくとも当方の数式のセイではありません。 （半平太） 2018/03/12(月) 20:02 ---- お礼 弊社の勤務表記入（エクセル）は手書きレベルで、エクセル関数機能はほとんど使用していない 状態でした、誰に頼んでもエクセル関数知識がなく、私が腰を上げたしだいです。 しかしながら、初めて見るとエクセル関数の奥深さがあり、挫折する毎日でしたが エクセルの学校を知り、初めて質問を上げた次第です。 今回の数式は本当に完ぺきで、私がこの公式に到達するには何か月 いや何年もの月日が必要 だったと思います。 無知な素人に親切に対応頂き、本当にありがとうございました。 （nebukuro3） 2018/03/12(月) 21:55... - http://www.excel.studio-kazu.jp/wiki/kazuwiki/201803/20180307202324.txt - [detail] - similar
PREV
NEXT
Powered by Hyper Estraier 1.4.13, with 97028 documents and 607994 words.
訪問者：