[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『出勤簿作成で普通残業を22：00以降は加算されないようにしたい。』（ＳＴ）
初めて質問させて頂きます。 出勤簿を作成していて苦戦しております。 説明が上手くできるか不安ですが、おかしい点はご指摘ください。
まず、勤務時間が2種類あります。
一.8：00～17:00（基本時間8：00・休憩1時間・残業17:00より開始・22：00以降深夜残業・残業時間15分毎にカウント）
一.9：00～18:00 (基本時間8：00・休憩1時間・残業18:00より開始・22：00以降深夜残業・残業時間15分毎にカウント）
勤務時間が2パターンある為、開始時間から終了時間の間で基本時間8時間を超えると22：00までの時間は普通残業の欄に記入され、 22：00以降は深夜残業の欄に記入されるように設定したいのですが、 22:00以降の深夜残業は上手く表示できたのですが、普通残業の欄にも深夜残業の時間ともう一度普通残業の時間がカウントされるため、 ダブりが出ておかしな事になってしまいます。
開始時間8：00～終了時間22：30場合を例にしますと、
基本時間 8：00
普通残業 5：00
深夜残業 0：30
となってもらいたいのですが、 私が作った式でしますと、
基本時間 8：00
普通時間 10：30
深夜残業 0：30
と出てしまいます。
ちなみに残業時間22：00までは、問題なく表示できます。
わたしが作った式は下記の通りです。 =IF(OR(開始時間="",終了時間=""),"",IF((終了時間-開始時間-休憩時間)>$O$6,FLOOR(終了時間-開始時間-休憩時間-$O$6+"0:1","0:15"),"0:00") +IF(終了時間>TIME(22,0,0),FLOOR(終了時間-開始時間-休憩時間-$O$6-(終了時間-$O$7)+"0:1","0:15")))
↓別の表に作っています。↓
$O$6→8：00
$O$7→22：00
『偽の場合』にＩＦの中に+ＩＦをいれてるから5：00+5：30で10：30になるのかな？と思いつつ、どうすればいいのか分かりません。 過去ログも見てみましたが、探せませんでした。 もし同じような質問が過去にありましたら、申し訳ありません。
どなたか、お力をお貸しください。 よろしくお願いします。
←行頭を半角スペースで始めると書き込みの改行位置が反映されます。
こんにちは。 ＞普通残業の欄にも深夜残業の時間ともう一度普通残業の時間がカウントされるため、 ご提示の数式中、
IF((終了時間-開始時間-休憩時間)>$O$6,FLOOR(終了時間-開始時間-休憩時間-$O$6+"0:1","0:15"),"0:00") の部分で、総勤務時間-8時間、つまり普通残業＋深夜残業（=5:00+0:30）を計算しており、さらに
IF(終了時間>TIME(22,0,0),FLOOR(終了時間-開始時間-休憩時間-$O$6-(終了時間-$O$7)+"0:1","0:15")) の部分で、普通残業＋深夜残業-深夜残業=普通残業（=5:00）を計算して、加えているためですね。
なので、普通残業だけの計算は、以下のようになります。
※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [O] [1] 開始時間 終了時間 休憩時間 基本時間 普通残業 深夜残業 _ _ [2] 8:00 22:30 1:00 8:00 5:00 0:30 _ _ [6] _ _ _ _ _ _ _ 8:00 [7] _ _ _ _ _ _ _ 22:00
E2 =IF(OR(開始時間="",終了時間=""),"",IF((終了時間-開始時間-休憩時間)>$O$6, FLOOR(終了時間-開始時間-休憩時間-$O$6-深夜残業+"0:1","0:15"),"0:00")) ※「深夜残業」の部分は、(終了時間-$O$7)で置き換えても同じです。
それと、ご質問の際には、上のように簡単なレイアウト図を記載すると、イメージがつかみやすいです。 （コタ）
ありがとうございます。 早速入力しましたところ、きちんと表示されました。感激です。
また、表にしてみないと読んでくださる方は、分かりづらいのですね。 質問した私もコタ様の表で分かりやすかったです。 今後は簡単な表にして、イメージしてもらえるように質問します。 本当に助かりました。ありがとうございました。
上記で解決されていたと思いましたが、また新たに問題が発生しましたので、ご質問させてください。
22：00以降の残業は正しく表示されたのですが、 22:00以前の普通残業がおかしな数字で表示されます。 どうしてこの数字になるかも分からず。。 8：00～18：00まで残業した際は、普通残業1：00で表示されてほしいのですが。。。 下記のよう5：00と表示されます。
※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [O] [1] 開始時間 終了時間 休憩時間 基本時間 普通残業 深夜残業 _ _ [2] 8:00 22:30 1:00 8:00 5:00 0:30 _ _ [6] 8:00 18:30 1:00 8:00 5:00 0:00 _ 8:00 [7] 8:00 19:30 1:00 8:00 5:00 0:00 _ 22:00
どなたかまたお力をお貸しください。
＞下記のよう5：00と表示されます。 ツール-オプションの計算方法が手動になっているのではないでしょうか。 こちらでは正しく（E6セルは1:30、E7セルは2:30）出力されます。
（コタ）
コタ様
すみません。両方にご回答頂きましてありがとうございます。 もう一度、新規で質問したほうがいいのかと、新しく更新いたしました。 返信頂いた方法で、もう一度試してみます。 ありがとうございます。
コタ様
自動計算になってました。 んー。でも明らかにわたしのがおかしいんですよね．．． コタさんの表では正しく出てるからですねぇ。。。 私が思ったのは、
※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [O] [1] 開始時間 終了時間 休憩時間 基本時間 普通残業 深夜残業 _ _ [2] 8:00 18:00 1:00 8:00 5:00 0:00 _ _ [6] 9:00 19:00 1:00 8:00 4:00 0:00 _ 8:00 [7] 9:00 20:30 1:00 8:00 4:00 0:00 _ 22:00
と上記のように、8：00～開始に入力すると普通残業5：00で、9：00～開始と入力すると、4：00とでるので、 定時の時間から、22：00までの時間が出てるのかなと思いますが、どうでしょうか。。。
＞ 自動計算になってました。 自動でしたか。 そうなると、数式中の参照先（名前）がずれているということですかね。
試しに、以下の数式でやってみてください。
E2 =IF(OR(A2="",B2=""),"",IF((B2-A2-C2)>$O$6,FLOOR(B2-A2-C2-$O$6-F2+"0:1","0:15"),"0:00"))
（コタ）
上記の式を入れますと、#VALUEと表示されます。 （終了時間-$O$7)同じことなのになぜこの表示になってしまうんでしょうか??
（ＳＴ）
＞上記の式を入れますと、#VALUEと表示されます。 これまた予想外ですね。
もう一度最初から確認してみましょう。 新規ブックに以下のレイアウトで作成してみてください。 再現できたら、これまでのブックとどこが違うのかを確認してみてください。
○【Sheet1】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [1] 開始時間 終了時間 休憩時間 基本時間 普通残業 深夜残業 _ _ _ _ _ _ _ _ _ [2] 8:00 22:30 1:00 8:00 5:00 0:30 _ _ _ _ _ _ _ _ _ [3] 8:00 18:30 1:00 8:00 1:30 0:00 _ _ _ _ _ _ _ _ _ [4] 8:00 19:30 1:00 8:00 2:30 0:00 _ _ _ _ _ _ _ _ _ [5] 8:00 18:00 1:00 8:00 1:00 0:00 _ _ _ _ _ _ _ _ _ [6] _ _ _ _ _ _ _ _ _ _ _ _ _ _ 8:00 [7] _ _ _ _ _ _ _ _ _ _ _ _ _ _ 22:00
E2 =IF(OR(A2="",B2=""),"",IF((B2-A2-C2)>$O$6,FLOOR(B2-A2-C2-$O$6-F2+"0:1","0:15"),"0:00")) →E2:E5フィルコピー
（コタ）
コタ様
根気強くお付き合いいただきありがとうございます。
新規ブックを作成し,再現しましたところ、すべて正しい数字が表示されました。
となりますと、私がA列,B列,F列に入れている数式が邪魔なのでしょうか???
[R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [1] 開始時間 終了時間 休憩時間 基本時間 普通残業 深夜残業 入時間 退時間 _ _ _ _ _ _ 開始時刻切 [2] 8:00 22:30 1:00 8:00 5:00 0:30 7:50 22:35 _ _ _ _ _ _ 30分 [3] 8:00 18:30 1:00 8:00 1:30 0:00 7:50 18:40 _ _ _ _ _ _ 終了時刻切 [4] 8:00 19:30 1:00 8:00 2:30 0:00 7:50 19:40 _ _ _ _ _ _ 15分 [5] 8:00 18:00 1:00 8:00 1:00 0:00 7:50 18:01 _ _ _ _ _ _ _ [6] _ _ _ _ _ _ _ _ _ _ _ _ _ _ 8:00 [7] _ _ _ _ _ _ _ _ _ _ _ _ _ _ 22:00
すみません。 表を上手く整列できずに、少しずれてます。 A列とB列にそれぞれタイムカードの入時間と退時間を入力したら、 切り上げするように式を入れてます。
A列、開始時間には A2=IF(OR(A2="",B2=""),"",CEILING(G2,TIME(0,$O$2,0)))
B列、終了時間には B2=IF(OR(A2="",B2=""),"",FLOOR(H2,TIME(0,$O$4,0)))
F列、深夜残業には F2=IF(OR(A2="",B2=""),"",IF(B2>$O$7,B2-$O$7,""))
それぞれ入力しています。 これが原因という事が有力ですよね。。。
（ＳＴ）
なるほど、深夜残業（F列）がないときに、""としているので、普通残業が#VALUE!エラーになったのですね。
F列の数式を、下のように0を返すようにすれば、エラーはでなくなります。 （見た目上ブランクがよければ、表示形式を「h:mm;;」 とかにしてみてください）
あと、勘違いだと思いますが、A2の数式内でA2は参照できませんので、下のようになっているかと思います。
[R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [1] 開始時間 終了時間 休憩時間 基本時間 普通残業 深夜残業 入時間 退時間 _ _ _ _ _ _ 開始時刻 [2] 8:00 22:30 1:00 8:00 5:00 0:30 7:50 22:35 _ _ _ _ _ _ 30 [3] 8:00 18:30 1:00 8:00 1:30 0:00 7:50 18:40 _ _ _ _ _ _ 終了時刻 [4] 8:00 19:30 1:00 8:00 2:30 0:00 7:50 19:40 _ _ _ _ _ _ 15 [5] 8:00 18:00 1:00 8:00 1:00 0:00 7:50 18:01 _ _ _ _ _ _ _ [6] _ _ _ _ _ _ _ _ _ _ _ _ _ _ 8:00 [7] _ _ _ _ _ _ _ _ _ _ _ _ _ _ 22:00
A2 =IF(OR(G2="",H2=""),"",CEILING(G2,TIME(0,$O$2,0))) →A2:A5フィルコピー B2 =IF(OR(G2="",H2=""),"",FLOOR(H2,TIME(0,$O$4,0))) →B2:B5フィルコピー F2 =IF(OR(A2="",B2=""),"",IF(B2>$O$7,B2-$O$7,0)) →F2:F5フィルコピー
ところで、開始時間などの名前で参照していたときに、変な結果が返ってくる問題ですが、おそらく名前定義が 違っていると思われます。
E2セルを選択した状態で、挿入-名前-定義（*）で、どんな数式が定義されているか、確認してみてください。 （*）Excel2003の場合です。
（コタ）
コタ様
本当に丁寧な説明ありがとうございます。 お陰さまで、正しく表示することができました。
もう少し、質問させて下さい。 >見た目上ブランクがよければ、表示形式を「h:mm;;」 とかにしてみてください） ・・・してみたのですが"0"と表示されます。他に方法はありますか？
>ところで、開始時間などの名前で参照していたときに、変な結果が返ってくる問題ですが、おそらく名前定義が 違っていると思われます。
名前で参照せずに（B2-$O$7)と終了時間を入力しいているセルを入力していたのですが。。。それが問題ありだったのでしょうか????
＞（B2-$O$7)と終了時間を入力しいているセルを入力していたのですが なるほど、そういうことでしたか。 これは、私が最初の回答で、 ※「深夜残業」の部分は、(終了時間-$O$7)で置き換えても同じです。 と書いてしまったせいですね。ごめんなさい。
この時点では、終了時間>"22:00" しか頭になかったので、こう書いてしまいましたが、 終了時間<"22:00"の場合には、話が変わってきます。
E2の数式中、終了時間-開始時間-休憩時間-$O$6-深夜残業で、深夜残業を(終了時間-$O$7)に置き換えると、 終了時間-開始時間-休憩時間-$O$6-(終了時間-$O$7) =終了時間-開始時間-休憩時間-$O$6-終了時間+$O$7 ^^^^^^^^ ^^^^^^^^^ ←打ち消しあう =-開始時間-休憩時間-"8:00"+"22:00" =-"8:00"-"1:00"-"8:00"+"22:00" ="5:00" となって、終了時間によらない結果になります。
これを防ぐには、終了時間<"22:00"のときには、(終了時間-$O$7)を0にすればいいので、以下のような 数式になります。
E2 =IF(OR(開始時間="",終了時間=""),"",IF((終了時間-開始時間-休憩時間)>$O$6,
FLOOR(終了時間-開始時間-休憩時間-$O$6-MAX(終了時間-$O$7,0)+"0:1","0:15"),"0:00"))
^^^^^^^^^^^^^^^^^^^^
＞・・・してみたのですが"0"と表示されます 以下のサイトをご参考に、ユーザー定義に、「h:mm;;」(「」は不要) と設定してみてください。 きっとブランクになりますよ。 http://www.eurus.dti.ne.jp/~yoneyama/Excel/user_set.htm
（コタ）
コタ様
またまた、分かりやすくありがとうございます。 打ち消しあったりするのですね。色々と勉強になりました。
また、ユーザー定義で h:mm;;と設定したのですが、やはりサンプルのところにも"0"とでて、セルにも"0"が表示されます。 ちなみにExcel2007を使っていますが、設定方法が違うのですかねぇ。んー。。
(ＳＴ)
例えば F2 =IF(OR(A2="",B2=""),"",IF(B2>$O$7,B2-$O$7,0)) を F2 =IF(OR(A2="",B2=""),"",IF(B2>$O$7,B2-$O$7,"0")) と、数値の「0」ではなく文字列の「"0"」を返しているということはありませんか？ (独覚)
独覚さま ご指摘の通りです。ありがとうございました。 空白になりました。まったくすみません。。。
コタさま 何度しても"0"が表示されるはずです。。。 本当に根気強く教えて頂き、ありがとうございました。 お陰さまで、思い通りの勤務表を作成することができました。 次回質問する際は、もう少し勉強しておきます。 またよろしくお願い致します。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.