勤務時間表をエクセルで作成しました。
出勤時間 退勤時間 休憩時間
と入力し退勤時間-出勤時間-休憩時間で勤務時間を求めます。それぞれのセルに時間を入力して勤務時間を求めていましたが会社の管理により休憩時間がそれぞれの時間帯に定められています。そこで出勤時間と退勤時間だけの入力で、休憩時間の条件を定義すれば休憩時間は入力することなく求められるかと思いましたがIF関数を使うと複雑で長くなりうまくできませんでした。”この関数に関する引数が多い”とのエラーメッセージが出てしまいます。VLOOKも考えてみましたがもの凄く長い条件になりそうで諦めました。休憩時間は以下の通りになります。
10:00~10:10
12:00~12:40
15:00~15:10
ちなみに私の通常勤務は8:30~16:30となります。
早退や遅刻などはたまになので難しい関数使うよりも
簡単に引くべき休憩時間は分かるんですが…。今後の勉強のためにもいい方法があればと思います。よろしくお願いします。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
すみません、m(_ _)m
#1は、#2の方の指摘通り間違いですね。
12:00~12:40
15:00~15:10
の部分が反対になっています。
AND(退勤時間>=休憩終了時間,休憩開始時間>=出勤時間)*(休憩終了時間-休憩開始時間)
でないといけません。
申し訳ございませんでした。
いえいえ、謝らないで下さい。言われてみるとそうですね。私自身、頭が足りないもので単純な違いも分かりませんでした。複雑で分かりにくかったものをここまで解明な式に表してもらって感謝してます。少々の間違いはあってもこの式を即答してもらえて感動してます。
No.4
- 回答日時:
私が良く使う勤務表の技を紹介します
私の場合、残業計算に使っているのですが、
今回のような休憩にも応用できるので
紹介します
まず、
A列・・出勤時刻
B列・・退勤時間
C列・・休憩時間
とし、
2行目に項目を入力
データは3行目から入力します
D列・・休憩1
E列・・休憩2
F列・・休憩3
用に用意します
D1に10:00と入力
D2に10:10と入力
E1に12:00と入力
E2に12:40と入力
F1に15:00と入力
F2に15:10と入力
これで準備okです
D3に
=IF(AND($A3<D$2,$B3>D$1),MIN($B3,D$2)-MAX($A3,D$1),0)
と入力してください
これは、
A3からB3(出勤時刻から退勤時間)の間に
D1からD2の時間が何分入っているかを計算しています
(実際にA3とB3に値を入れると判ります)
これが、出勤時刻に対する休憩1の時間です。
D3の数式を
D3:F33にコピーします
($を意識して入れておりますのでD3をそのままコピペして構いません)
次に休憩時間の計算ですが、
C3に
=SUM(D3:F3)
と入力し
C33までコピーしてください
あとは
A3からB33まで1ヶ月入力すればOKです。
今後、休憩時刻が変更になった場合は、
D1に10:00と入力
D2に10:10と入力
E1に12:00と入力
E2に12:40と入力
F1に15:00と入力
F2に15:10と入力
ここを変更するだけでOKです。
余談ですが、本来の使い方である残業計算は、
休憩時刻を残業の時刻に変更するだけです。
G列を使用して、D1を複写するだけ。
C列とD列の間に残業の列を追加し、G列を参照するかたちにします。
自力で悩み考えややこしい式で結局完成しなかったものがこんな短い式で集約できてしまうんですね。素晴らしい!の一言です。MINとMAXのこの使い方は知りませんでした。私の場合、残業となるのは勤務時間を8時間を過ぎてからとなります。通常勤務では16:30に退社しますが16:30から10分は休憩時間となるので残業となるのは17:40を過ぎてからとなります。17:40以降勤務するのは月に1回あるかないかペースなんですがね。(^^ゞ教えていただいた関数を使って残業の計算式も入れてみます。いろいろとありがとうございました。
No.3
- 回答日時:
すみません、No.2です。
No.2の D2 に入れる数式を
↓に訂正してください。
---------------------------------------------
=IF(COUNT(B2:C2)<>2,"",(B2<=$J$1)*(C2>=$K$1)*($K$1-$J$1)+(B2<=$J$2)*(C2>=$K$2)*($K$2-$J$2)+(B2<=$J$3)*(C2>=$K$3)*($K$3-$J$3))
---------------------------------------------
(結果は同じですが、休憩時間の変更があった場合、
J1:K3 の時間を変更するだけで済みます。
→ 数式の変更が必要ありません)
No.2
- 回答日時:
こんにちは、横から失礼します。
ちょっと確認ですが・・・
No.1の方の数式って合ってますか?
もしかしたらウチのおバカなEXCELが
おバカな答えを出しているだけかもしれませんが・・・
*
たとえば、9:00 - 15:00 の勤務の場合、
休憩時間は 50分ですから、実働時間は 5:10 ですよね?
これが No.1の方の式だと 5:00 になりませんか?
さらに 9:00 - 12:00 の場合、
休憩時間は 10分だけですから 2:50 のはずなのに
2:10 にまでけずられてしまいます(T_T)
*
No.1の方の数式をそのまま使うとすれば
↓が正しいのではないでしょうか?
--------------------------------
=C2-B2-(AND(C2>=TIMEVALUE("10:10:00"),TIMEVALUE("10:00:00")>=B2)*TIMEVALUE("00:10:00")+AND(C2>=TIMEVALUE("12:40:00"),TIMEVALUE("12:00:00")>=B2)*TIMEVALUE("00:40:00")+AND(C2>=TIMEVALUE("15:10:00"),TIMEVALUE("15:00:00")>=B2)*TIMEVALUE("00:10:00"))
--------------------------------
カンちがいでしたらすみません。
***
***
ついでに、どうでもいいことかもしれませんが・・
休憩時間のトータルを別のセルに出して
(退社時間)-(出社時間)-(休憩時間)
とした方が数式がすっきりすると思います。
*
たとえば規定の休憩時間を
J1:J3 に、それぞれ 10:00、12:00、15:00、
K1:K3 に、それぞれ 10:10、12:40、15:10 と入力しておいて
出社時間が B2、退社時間が C2、
休憩時間が D2、実働時間を E2 とすれば
(D列、E列も表示形式を「時刻」にしておいてください)
D2に↓
--------------------------------
=IF(COUNT(B2:C2)<>2,"",(C2>=$K$1)*(B2<=$J$1)*("0:10")+(C2>=$K$2)*(B2<=$J$2)*("0:40")+(C2>=$K$3)*(B2<=$J$3)*("0:10"))
--------------------------------
以下、必要分オートフィルでコピー
E2に↓
--------------------------------
=IF(COUNT(B2:D2)<>3,"",C2-B2-D2)
--------------------------------
以下、必要分オートフィルでコピー
No.1の方の式では私も正確には休憩時間は求められませんでした(T_T)労働時間も単純な引き算とは答えが違ってしまい何か部分的になおせば使えるかとやってはみましたがだめでした…。
教えていただいた式で
退社時間-出勤時間-休憩時間で求める労働時間と一致し休憩時間も正確に表示されました。PC初心者で関数歴半年のオバサンなもので初歩的なものにつまづいてしまい教えてもらってありがたいです。TIMEVALUEも知らなかったので勉強になりました。(時間表示をセルの書式設定から表示形式で時間の数値を標準に直して式に導入していました。)参考にさせていただきます。
No.1
- 回答日時:
B2が出勤時間で
C2が退勤時間の時
以下の式で求められます。
=C2-B2-(AND(C2>=TIMEVALUE("10:10:00"),TIMEVALUE("10:00:00")>=B2)*TIMEVALUE("00:10:00")+AND(C2>=TIMEVALUE("12:00:00"),TIMEVALUE("12:40:00")>=B2)*TIMEVALUE("00:40:00")+AND(C2>=TIMEVALUE("15:00:00"),TIMEVALUE("15:10:00")>=B2)*TIMEVALUE("00:10:00"))
基本は、
退勤時間-出勤時間で
退勤時間>=各休憩時間>=出勤時間の時、休憩時間を引いています。
すごい!!早い回答をいただき驚きました。実はこの数日試行錯誤しながらも解決せず行きつけのPC教室で先生に聞いたら先生が悩んだまま1時間過ぎ、先生の宿題になってしまいました。IF関数ではないんですね。この式をそのまま使わせていただきます。本当にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- 転職 転職活動中で2社内定があり迷っています。皆様ならAとBどちらの方が良いと思いますか?理由もお願いしま 1 2023/02/06 12:20
- アルバイト・パート バイトの休憩時間に関してです。 大学生で友達とふたりでバイトを始めました。 飲食店でオープニングスタ 3 2022/12/04 22:45
- 会社・職場 仕事の休憩タイミングについて 7 2022/04/07 00:23
- 飲食業・宿泊業・レジャー 休憩・残業時間の自動算出 1 2023/01/07 22:58
- 会社・職場 ブラック企業か判断お願いします。 半休使い週6勤務。 休憩時間は、1時間半。しかし、まともに取れるの 5 2023/03/08 20:20
- 労働相談 新しい職場が労働基準法の労働時間に違反してるのかしてないのか知りたいです。 私自身 14:00~22 3 2023/03/02 23:31
- 就職 ホワイト企業ですか? 3 2023/03/10 15:16
- Excel(エクセル) 月間シフト表から総勤務時間を計算する関数 4 2023/05/20 07:12
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
【Microsoft Office Excel Comp...
-
Excelはなんで先頭の0を消すん...
-
Excelのセルを飛ばして入力する
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excelのオートフィル
-
Excel 2019 のピボットテーブル...
-
スプレッドシート クエリ関数 1...
-
excelの不要な行の削除ができな...
-
Excel初心者です。 詳しい方、...
-
【Excel】セル内の時間帯が特定...
-
Excel初心者です。 詳しい方、...
-
EXACT関数とIF関数の組み合わせ...
-
Excelのグラフ軸について
-
スマートな関数を教えて下さい。
-
Excelで全角を半角にしたいので...
-
【マクロ】エクセルにかいてあ...
-
Excel:一部のフォントでセルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報