設備の稼働日報を作成しているのですが、稼働時間から休憩時間を引いた時間を算出したいです。
工場の休憩時間はタイムテーブルで定められているので、それに該当する時間を稼働時間から引きたいのです。
図の例の赤枠内で言うと、8:30~11:50の稼働時間は3時間20分ですが、そのうち10:00~10:10は休憩時間なので10分を引いて3時間10分とF2に表示させたいのです。
休憩時間は全部で6種類あるので複数の条件を指定しなければならないと思うのですが、その方法が分かりません。
表の列は増えても構わないので、何か良い方法はありますでしょうか?
No.1ベストアンサー
- 回答日時:
こんばんは
後で、記すように0:00時をまたぐ計算をすると複雑になるので、連続している時間帯での計算方法のみを記しておきます。
まずは考え方を・・
基本的には、開始~終了の時間帯と一つの休憩時間帯とが重複する時間を求められれば、それを合計すればよいことはお分かりと思います。
時間帯Aと時間帯Bの関係は
・一方が他方に含まれる
・一部がラップする
・重なる時間はない
のいずれかです。
これを調べるのに、開始時刻の遅い方、終了時刻の早い方を計算して、
開始時刻の遅い方 < 終了時刻の早い方
であれば、重なる時間があると判断できます。
開始時刻の遅い方は、MAX(開始時刻A,開始時刻B)で求められます。--①
終了時刻の早い方は、MIN(終了時刻A,終了時刻B) で求められます。--②
これらから、重複する時間を計算すると、
(①<②)*(②-①)
で求めることが可能です。(重複が無い場合は0になるような式にしてあります)
これを、ご提示の①~⑤の時間帯に対して計算した和を求めれば良いことになります。
(以下に示す実際の式では、MAX、MIN関数を使うとうまく計算できないので、IF文による式にしてあります)
どこかに休憩時間の表があるものと仮定し、添付図ではB10:C15の範囲をその表としてあります。
添付図ではE2セルに
=SUM((IF(C2<C$10:C$15,C2,C$10:C$15)>IF(B2>B$10:B$15,B2,B$10:B$15))*(IF(C2<C$10:C$15,C2,C$10:C$15)-IF(B2>B$10:B$15,B2,B$10:B$15)))
の関数式を入力し、Ctrl+Shift+Enterで確定しています。
(配列数式なので、↑の確定操作は必須です。)
その上で、下方にフィルコピーしてあります。
※ 上記の式は方法の説明なので、最初にお断りしたように連続した時間帯を対象とした計算になっています。
0:00をまたぐような時間帯がある場合は、25:40のような時刻表記を用いて連続時間帯となるようにするか、あるいは、作業列などを用いて0:00前と後の二つの時間帯に分けて計算してから、合算するような必要があるかも知れません。
(一つの計算式にすることも可能ですが、長くなるだけなので省略しました)
※ A~C列に入力が無い場合に空白にするなどの処理も省いてあります。
ご回答ありがとうございます。
ご教示頂いた数式で時間計算を行うことが出来ました!
日付をまたぐ場合も25:40のような表記にすることで問題無く計算出来ました。他の資料にも応用して時間短縮が図れそうです。
感謝いたします。ありがとうございました!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
複数の休憩時間がある場合の休憩時間の算出方法
Excel(エクセル)
-
エクセルで休憩時間を引く時と、引かない時の数式
Excel(エクセル)
-
エクセルで複数の勤務時間ごとの休憩時間の計算がしたい
Excel(エクセル)
-
-
4
Excel タイムカードで、昼休み時間を除く計算
Excel(エクセル)
-
5
excel関数 変動する休憩時間等を計算する
ソフトウェア
-
6
エクセル不変則な休憩時間の実労働時間計算
Excel(エクセル)
-
7
エクセルで勤怠管理表で休憩時間を計算させるには
Excel(エクセル)
-
8
エクセルで休憩時間を計算したいのですが 条件 勤務時間6時間以上8時間未満は休憩45分 8時間以上は
Excel(エクセル)
-
9
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
10
エクセルで休憩時間を除く
その他(Microsoft Office)
-
11
エクセル勤務表で昼休憩を挟んだ時の休憩算出方法
Excel(エクセル)
-
12
[IF文] 「何時〜何時」で該当する時間を表示したい お疲れ様です。お知恵をお借りできればと思います
Excel(エクセル)
-
13
8時間以上だったら1時間マイナスする関数
Excel(エクセル)
-
14
エクセルで休憩時間を引く時と、引かない時の数式
Excel(エクセル)
-
15
【Excel】セル内の時間帯が特定の時間帯に含まれるかの判定
Excel(エクセル)
-
16
エクセル 深夜休憩時間(深夜労働時間)を求めたい
Excel(エクセル)
-
17
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
18
【エクセル】休憩時間をマイナスし、実働時間の計算
Excel(エクセル)
-
19
エクセルで、時間の計算をした場合、マイナス表示を0:00にする方法を教えてください。
Excel(エクセル)
-
20
エクセルで 合計の時間から特定の時間を引く 観察を使うやり方を教えてください(>_<)
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ラブホって、支払う料金は2人合...
-
複数の休憩時間がある場合の休...
-
私は総務課です。 昼休み中に職...
-
フードコートで何も頼まずに休...
-
~時間を超えるの定義
-
工場のライン作業は、お昼休憩...
-
仕事中の飲み物購入について
-
休憩のための移動は労働時間か?
-
「お先に休憩行ってきます」と...
-
お昼休憩中に寝るのがいけない...
-
今度、彼女と初めてラブホに行...
-
昼休憩について
-
Excelの時間計算で経過時間から...
-
休憩中に携帯触ると上司に文句...
-
タバコ休憩はありですか なしで...
-
新人でコンビニバイトをしてる...
-
派遣社員です。休憩時間の規則...
-
エクセルで休憩時間を計算した...
-
7時間労働で休憩がないのは普通...
-
時給で働いている方、お昼休み...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ラブホって、支払う料金は2人合...
-
複数の休憩時間がある場合の休...
-
今度、彼女と初めてラブホに行...
-
フードコートで何も頼まずに休...
-
Excelの時間計算で経過時間から...
-
~時間を超えるの定義
-
エクセルで休憩時間を計算した...
-
仕事中の飲み物購入について
-
休憩中の店員
-
「お先に休憩行ってきます」と...
-
車初心者マークなんですが、下...
-
コンビニバイトで5時間の日に休...
-
お昼休憩中に寝るのがいけない...
-
新人でコンビニバイトをしてる...
-
工場のライン作業は、お昼休憩...
-
休憩中に話しかけてきた客に対...
-
PCにあるdzentime ってなんです...
-
ローソンでバイトしています。 ...
-
派遣社員です。休憩時間の規則...
-
私は総務課です。 昼休み中に職...
おすすめ情報
画像が小さくて見づらいので貼り直しました。