エクセルで時間を起点として集計

質問

A列に開始時間、B列は～という文字　C列は終了時間、D列は種類名、E列は値（経過時間）が記載されています
1行目はタイトルで、2行目から始まっています。

A B C D E
07:00　～　07:12　バナナ　12
07:20　～　07:40　りんご　20
09:00　～　09:12　りんご　12
09:20　～　09:22　ぶどう　2
09:30　～　09:42　メロン　12
13:00　～　14:12　すいか　72

このDセルの種類名のカウントと、Dセルに対してEセル経過時間の値の合計値を計算する関数というと物を以前教えて頂きました。
下記の物を使わせて頂いております。

＞Sheet1のデータをSheet2に表示するようにしてみました。
＞↓の画像で左がSheet1・右側がSheet2とします。

＞Sheet1に作業用の列を1列設けます（今回はF列にしています）
＞F2セルに
＞=IF(COUNTIF(D$2:D2,D2)=1,ROW(),"")
＞という数式を入れ、これ以上データはない！というくらいまでしっかり下へオートフィルでコピーしておきます。

＞Sheet2のA2セルに
＞=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!D:D,SMALL(Sheet1!F:F,ROW(A1))))

＞B2セルに
＞=IF(A2="","",COUNTIF(Sheet1!D:D,A2))

＞C2セルに
＞=IF(A2="","",SUMIF(Sheet1!D:D,A2,Sheet1!E:E))

＞という数式を入れA2～C2セルを範囲指定　→　C2セルのフィルハンドルで下へオートフィルでコピー！

回答者様のお知恵を借り、とても楽に計算する事ができ、感謝しております。

贅沢な者で、もう少し楽をしたいと欲を出してしまいまして・・・。

この集計で朝の7時～19時までと夜19時～7時までで分けて集計する事は可能でしょうか？

基本的には19時で一度〆るので、19時前と19時後を跨ぐ事はないのですが（18:50　～　19:10）←こんな感じには基本ならないのです。
たまに作業が停止する時は（14:00　～　6:59）という形で、跨いでしまう事があります。
この場合は19時を跨いだ時点で一度朝の時点での経過時間の計算をし、再度19:00～夜の経過時間を再計上という形をしたいのですが可能でしょうか？

長々となりましたが、お知恵をお貸し下さい。

回答No.4

tom04

No.2です。

A・C列が文字列ですかぁ～～？
それはかなり厄介ですね。
もう一つ言えば、A列が19時以降でC列が24時以降の場合です。
これはセルの表示形式と入力方法だけで解決出来ますが、
とにもかくにも、文字列ではなく時刻（時間）としてのシリアル値に直さなければ全く意味のない表になってしまいます。

すでに文字列になっている表がある場合は最初から！という訳にはいかないと思いますので、
次の方法でA・C列をシリアル値にしてください。
（1）どこか使っていないセルに　1　と入力　→　そのセル上で右クリック　→　コピー
（2）A～C列すべてを範囲指定　→　A～C列セル上で右クリック　→　形式を選択して貼り付け　→　演算の「乗算」を選択しOK
これで小数点以下の数値が表示されます。
空白セルに「0」が表示されますので、0が表示されている最初のA～C列セルを範囲指定
→　Shiftキー+Ctrlキーを押しながら　下向き矢印キーを押下　→　これで最終行まで選択されますので、
Deleteキーで0が表示されているセルを削除

（3）A列およびC列の表示形式を変えます。
A列を範囲指定　→　右クリック　→　セルの書式設定　→　表示形式　→　ユーザー定義　→　「G/標準」　となっている欄の「G/標準」を消して
[h]:mm
としてOK！
C列も同様の操作をします。
これで時刻（時間）表示になります。

されにC列が次の日の午前になっているセル
仮に　A列が19時以降でC列が2：00　のようなセルは
2：00　を　26：00　のようにします（質問ではこのようなケースはあまりないようなので・・・）
今後も入力時は24時を超えた場合はこのような入力方法とします。

以上、上記の下準備ができた上での一例です。

今回もSheet1に作用用の列を3列設けます。
↓の画像でF2セルは前回同様
=IF(COUNTIF(D$2:D2,D2)=1,ROW(),"")

G2セルに
=IF(D2="","",IF(A2<="19:00"*1,IF(C2<="19:00"*1,E2,("19:00"*1-A2)/(C2-A2)*E2),""))

H2セルに
=IF(D2="","",IF(A2>"19:00"*1,E2,IF(C2>"19:00"*1,(C2-"19:00")/(C2-A2)*E2,"")))

という数式を入れしっかり下へオートフィルでコピー！

最後にSheet2のA2セルに
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!D:D,SMALL(Sheet1!F:F,ROW(A1))))

B2セルに
=IF($A2="","",SUMIF(Sheet1!$D:$D,$A2,Sheet1!G:G))
という数式を入れ隣のC2セルまでコピー！

最後にA2～C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　一応按分はできていると思います。m（_ _）m

回答No.3

MackyNo1

現在の計算式を利用するなら、以下のような数式で朝の部と夜の部の値データを別々に計算するのが簡単です(下方向にオートフィル）。

E2セル
=IF(A2="","",(MIN(19/24,C2+(C2<A2))-MIN(A2,19/24))*60*24)

F2セル
=IF(A2="","",(MAX(19/24,C2+(C2<A2))-MAX(A2,19/24))*60*24)

集計セルはI2セルに以下の式を入力し、右方向に1つ、下方向に適当数オートフィルします。

=IF($H2="","",SUMIF($D:$D,$H2,E:E))

回答No.2

tom04

こんばんは！
おそらくその書き方は前回、当方が投稿した方法だと思います。

最後の3行の
＞たまに作業が停止する時は（14:00　～　6:59）という形で、跨いでしまう事があります。
＞この場合は19時を跨いだ時点で一度朝の時点での経過時間の計算をし、
＞再度19:00～夜の経過時間を再計上という形をしたいのですが可能でしょうか？

この部分がよく判りません。
仮に　14：00～6：59　のという行がある場合、一旦19：00で「朝の部」を締めて、
19：00より後の部分が新たに「夜の部」となるのだと思います。

ただその振り分け分（按分）をどのような割合で行うのか？
というのが判断できませんので、
とりあえずこの場合は両方の部に加えるようにしてみました。

↓の画像のように今回は3列の作業列を設けます。
ちょうど19：00の時はどちらに入れるのか判らないので、とりあえず19：00までと
19：00を超えてに分けてみました。

上側がSheet1で下側がSheet2とします。
Sheet1のD列の作業列は前回同様
=IF(COUNTIF(D$2:D2,D2)=1,ROW(),"")
（重複なしに表示するため）
G2セルに
=IF(D2="","",IF(A2<="19:00"*1,1,IF(C2<"19:00"*1,1,"")))
H2セルに
=IF(D2="","",IF(A2>"19:00"*1,1,IF(C2>"19:00"*1,1,"")))
という数式を入れずぃ～～～！っと下へコピー！

Sheet2のA2セルに
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!D:D,SMALL(Sheet1!F:F,ROW(A1))))

B2セルに
=IF($A2="","",SUMPRODUCT((Sheet1!$D$1:$D$1000=$A2)*(Sheet1!G$1:G$1000=1),Sheet1!$E$1:$E$1000))
という数式を入れ隣のC2セルまでオートフィルでコピー！
最後にA2～C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピー！

これで画像のような感じになります。

※　Excel2007以降のバージョンをお使いであれば
Sheet2のB2セルは
=IF($A2="","",SUMIFS(Sheet1!$E:$E,Sheet1!$D:$D,$A2,Sheet1!G:G,1))
という数式が使えます。

※　画像を見てお判りだと思いますが、Sheet1の8行目のデータは
「朝の部」・「夜の部」の両方に加算されています。m（_ _）m

お礼

前回に引き続き、tom04さんありがとうございます。

そして自分の説明不足で申し訳ありません。

＞ただその振り分け分（按分）をどのような割合で行うのか？

値というのは経過時間を指していますので、19時までの「朝の部」で値で一度〆て、その後残りの値を「夜の部」に持ち越したいという物です。

18:00～20:00の場合、値は120となります。18:00～19:00で60の値、19:00～20:00で60の値を分けるような形です。

=IF(D2="","",IF(A2<="19:00"*1,1,IF(C2<"19:00"*1,1,"")))
=IF(D2="","",IF(A2>"19:00"*1,1,IF(C2>"19:00"*1,1,"")))

この数式をコピーしたのですが、
全て下の数式で「１」が経ってしまい、上の数式は全て空欄になってしまいます。
これはAC列の時間が文字列で入力しているのがいけないのでしょうか？

回答No.1

okage

タイトル行でオートフィルタをONにし、

(1)C列のフィルタオプションで【「19：00」より小さい】
でソートをかけ、計算。
(2)A列のフィルタオプションで【「19：00」より大きい】
でソートをかけ、計算。

では、どうでしょうか？
スマートじゃないですかね…？？

お礼

okageさん、ありがとうございます。
実際に試してみると、抽出条件に「19:00」より小さいでと入力すると、「0.791666666666667」と出てしまい集計が不可能でした。
これはAC列の時間が文字列で入力しているのが原因なのでしょうか？