Aに特定の値が入っていた場合、BとCを計算していく

質問

画像のようなシフト表において、その日ごと・店ごとの勤務時間の合計を算出したい場合の数式が書けず困っております。

黄色に塗っているセルを算出したいのですが、今現在は

=IF(B3="店A",C4-B4,0)+IF(D3="店A",E4-D4,0)+IF(B5="店A",C6-B6,0)+IF(D5="店A",E6-D6,0)+IF(B7="店A",C8-B8,0)+IF(D7="店A",E8-D8,0)+IF(B9="店A",C10-B10,0)+IF(D9="店A",E10-D10,0)+IF(B11="店A",C12-B12,0)+IF(D11="店A",E12-D12,0)

というようにIF関数でAM・PMの1ブロックごとに計算し、それを合計していくという手法でやろうとしております。
一応図のように正しい値が出てくるのですが、人数が多い為作業量が多くなってしまい、また途中で新しい行を挿入などすると破綻してしまうと思った為、何か良い数式があればと思い質問させて頂きました。

どなたかご教示頂けますと幸いです。

回答No.3

SI299792 · ベストアンサー

画像の通りとして、
店A 店B 以外もあるかもしれません。

B14 店A
B15 店B
に変更して下さい。（データと同じ表記）
表示形式、ユーザー定義 ｢@"勤務時間合計"｣にすれば、画像と同じ表示になります。

D14 =SUMIF(B$3:D$11,B14,C$4)-SUMIF(B$3:D$11,B14,B$4)
下へコピペ。
B4～E15 を右へコピペ。

お礼

皆さん沢山ご回答いただきありがとうございました。
全て試させて頂きましたが、今回は一番理想に近かったこちらのご回答をBAとさせて頂きました。
非常に使い勝手がよく大変助かりました。

ご教示頂きました全ての皆様、ありがとうございました。

回答No.5

chayamati

回答№２，4です
添付図は４～１５まで１２名該当者の行を用意しましたが、７～１３行を非表示にしています。
１７行目を集計行は集計行です。
氏名列と時分列に入力規則を適用する予定です。
また、１７行目のN17に=SUM(N4:N16)と勤務時間合計を求めますが
表示書式を[h]:mとして日付への繰り上げを回避します。
背景が赤色のセルには式です
店の該当者数とＡＭ、ＰＭの時間帯を補足願います
これが無いと先へ進めません。宜しくお願い致します。

回答No.4

chayamati

回答№2の
　F2=IF(A2="","",IF(B2<C2,C2-B2,0)+IF(D2<E2,E2-D2))　
　G2=IF(A2="","",F2*$I$1*24)
これを以下の行にコピペします
A2の指名欄が空白以外のみ計算しますね
一店当たり該当者は最大何名ですか
=SUM(F2:F14)と日への繰上りを止めるため表示書式を[h]:m
式が単純になりますね。

次は、添付画の《データの入力規則》で氏名、時刻
の処理に入りますが、よろしいでしょうか

回答No.2

chayamati

＞人数が多い為作業量が多くなってしまい、
　また途中で新しい行を挿入などすると破綻してしまう
★そうですね、これがエクセルの最大の欠点ですね。
・運用時だけですが背景色に色を付ける、時刻に入力書式を使う
さて、本題に入ります。
添付のように、店ごとにSheetを分けるのは如何ですか

回答No.1

kkkkkm

表の体裁を変更してよろければ添付図のようにして
店Aの合計は
=SUMPRODUCT(D3:D12-C3:C12)
店Bの合計は
=SUMPRODUCT(F3:F12-E3:E12)
で集計できると思います。