別シートの合計時間の列が無い表から集計可能ですか？

質問

お世話になっています。

勉強時間をフリーソフトで記録しています。
フリーソフトからエクセルに吐き出されたシートには、開始時刻と終了時刻が記載されているだけで、終了時刻から開始時刻を差し引いた作業時間を表示する列がありません。
このシートを参照して、別の自分で作るシートに科目ごとの勉強時間の合計を集計したいのですが、いかんせん、作業時間を表示する列がないので、どのような関数をつかえばいいか思いつきません。
sumifとかで集計できないでしょうか？
やはり、参照元に作業時間列がないと無理でしょうか？

宜しくお願い致します。

回答No.3

kagakusuki · ベストアンサー

今仮に、

>フリーソフトからエクセルに吐き出されたシート

とはSheet1であるものとし、Sheet3のA列とB列を作業列として使用して、Sheet2に科目ごとの勉強時間の合計を集計するものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))=1,ROW(),""))

　次に、Sheet3のB2セルに次の関数を入力して下さい。

=IF(COUNT(INDEX(Sheet1!$B:$E,ROW(),))=4,IF(ISERROR(1/(INDEX(Sheet1!$B:$B,ROW())=TEXT(INDEX(Sheet1!$B:$B,ROW()),"yyyy/m/d")/(INDEX(Sheet1!$B:$B,ROW())>=1))/(INDEX(Sheet1!$C:$C,ROW())=TEXT(INDEX(Sheet1!$C:$C,ROW()),"h:m:s")+0)/(INDEX(Sheet1!$D:$D,ROW())=TEXT(INDEX(Sheet1!$D:$D,ROW()),"yyyy/m/d")/(INDEX(Sheet1!$D:$D,ROW())>=1))/(INDEX(Sheet1!$E:$E,ROW())=TEXT(INDEX(Sheet1!$E:$E,ROW()),"h:m:s")+0)),"",IFERROR(TEXT(SUM(INDEX(Sheet1!$D:$E,ROW(),))-SUM(INDEX(Sheet1!$B:$C,ROW(),)),"[h]:m:s")+0,"")),"")

　次に、Sheet3のA2～B2セルをコピーして、Sheet3のA列～B列の3行目以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ROWS(A$1:A2)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL(Sheet3!$A:$A,ROWS(A$1:A2))))。

　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF($A2="","",TEXT(SUMIF(Sheet1!$A:$A,$A2,Sheet3!$B:$B),"[h]:m:s")+0)

　次に、Sheet2のA2～B2セルをコピーして、Sheet2のA列～B列の3行目以下に貼り付けて下さい。

お礼

高度な技術をレクチャーしていただいてありがとうございます。
教えてもらったことを再現したかったのですが、できませんでした。
また、仕組みも理解しておりません。
時間が立ってしまいましたので、一旦締め切らせていただきたいと思います。
せっかく高度な技術を教えてもらっていますので、再度質問しなおしますので、宜しければまた教えてもらえると嬉しいです。
ありがとうございます！

補足

高度な技術をレクチャー頂いて、ありがとうございます。
恐縮です。
現在検証しております。
仕組みを理解できておらず、せっかく教えてもらっている技術です。
週末までに理解して、お礼させていただきたく思います。
返事が遅れて申し訳ありません。

回答No.4

keithin

シート２のＡ列に科目一覧を並べます
ごくシンプルに
Ｂ２：
=SUMIF(Sheet1!A:A,A2,Sheet1!D:D)+SUMIF(Sheet1!A:A,A2,Sheet1!E:E)-SUMIF(Sheet1!A:A,A2,Sheet1!B:B)-SUMIF(Sheet1!A:A,A2,Sheet1!C:C)
時刻の書式、若しくは
[h]:mm
の設定を付けておく
以下コピー
でいいです。

お礼

私の理解が乏しく、再現できませんでした。
再現してお礼をするのが筋ですが、時間が立ってしまいましたので、先に締め切らせていただきます。
ありがとうございます！

補足

ご指導いただきまして、ありがとうございます。
現在、再現できておりません。
週末までに、再現してお礼させていただきたく思います。
ありがとうございます。

回答No.2

msMike

「エクセルに吐き出されたシート」に「作業時間を表示する列がありません」と仰るのが全く理解できません。
シート上にそのための列を挿入するなり、空き列を使うなりして「作業時間を表示」するための式を入力すれば好いだけのことでしょう？それが出来ない事情でもあるのですか？
「参照元に作業時間列がないと無理」なんてことはアリエナ～イ。

お礼

失礼しました。
エクスポートしたシートを参照元とし、反映先シートに関数を設定しておけば、一回一回、作業時間を表示するための式を入力する手間を省くため　の質問でした。
言葉足らずで申し訳ありません。

回答No.1

bunjii

>やはり、参照元に作業時間列がないと無理でしょうか？
フリーソフトから吐き出されたファイルはCSV形式でしょうか？
時間の計算は終了日時から開始日時を差し引けば求められますので元データのシートで予め計算されると良いでしょう。
F2=(D2+E2)-(B2+C2)

>sumifとかで集計できないでしょうか？
SUMIF関数を使えば予め計算された時間から目的の集計ができます。
SUMIF関数の数式は貼付画像が判読できないので具体的に提示できません。

お礼

画像が見える状態になっていませんでした。
申し訳ありません。
エクスポートしたシートで計算する場合は、ご助言の数式を応用することになりますね。
ありがとうございます。