[[20200806121846]]　『総継続時間の計算』（計算式学びたい）　ページの最後に飛ぶ

『総継続時間の計算』（計算式学びたい）

はじめまして。
オンライン会議のデータ処理で困っています。

下記のようなデータがあり、参加者によっては複数機器を使用するため
ログが２つ、３つある人がいます。

参加者別にログインの総継続時間（Eの欄のように）を計算したいのですが、
開始時間と継続時間が被ったり、繋がったりといろいろしていて
うまく計算式が作れません。

どなたかうまく計算できる方法を教えていただけますか？？

※マクロは使えません。。
職場全体で共有するので、計算式でないと全員の理解が難しいです。。。

	A	B	C	D	　　E
	名前	開始	終了	継続時間	　　総継続時間
1	Aさん	14:37	16:10	94 分	
2	Bさん	15:39	16:10	32 分	
3	Bさん	14:42	15:39	57 分	　　89分
4	Cさん	14:35	16:10	96 分	
5	Dさん	14:49	16:10	82 分	
6	Eさん	14:38	15:38	60 分	
7	Eさん	15:41	16:11	30 分	　　90分
8	Fさん	14:37	15:37	60 分	
9	Fさん	14:38	16:11	93 分	　　94分
10	Gさん	14:27	16:11	104 分

＜使用 Excel：Office365、使用 OS：unknown ＞

例えば重複しない参加者リスト作ってSUMIF使ったらだめなんですか？
（モゲラ） 2020/08/06(木) 13:56

返信ありがとうございます！

sumif関数・・・使ったことなく調べてみましたが　どのような使い方がよいのかいまいちわかりません（＞＜）
”条件”をどのように設定したらよいのでしょう？（←的外れなこと言ってったらごめんなさい。。）
（計算式学びたい） 2020/08/06(木) 14:06

=sumif(A:A,"Aさん",D:D)
D列が文字列の場合EとかFに=C-BまたはD列を元に時間の数値列作ってD:Dと入れ替えてください
（モゲラ） 2020/08/06(木) 14:19

 そう簡単でもないのでは？
 Fさんのような重なりをどう処理するのか。こんなパターンに対応しないといけない。
 名前    開始    終了    継続時間
 H       9:00    11:00   2:00
 H       10:00   12:00   2:00
 H       10:30   11:30   1:00
 I       9:00    12:00   3:00
 I       10:00   11:00   1:00
 I       10:30   11:30   1:00

 氏名、開始時間でソートした上で、作業列を許容して、
 地道に場合分けする必要があるかもしれません。

 余談：
 ＞職場全体で共有するので、計算式でないと全員の理解が難しいです。。。
 それって、エンジンの詳細構造を理解しないと自動車乗る資格がない、
 と言っているようなものかもしれない。ケースバイケースでしょう。
 少し違和感がありますね。

（γ） 2020/08/06(木) 14:22

モゲラさん、γさん　コメントありがとうございます！
やっぱり元データを頑張って仕分けか処理しないと難しいのですね・・・

職場、年配の方が多いので、マクロというだけで却下されるのです。。。
残念ですよね。。便利な機能いっぱいあるのに　数式で処理しか認められません。。。

新しい関数もちょっと学びながら地道にデータ処理してみます！
ありがとうございました（＾o＾）
（計算式学びたい） 2020/08/06(木) 14:41

 解決済みかもしれませんが、
 E2に　=IF(A2<>A3,MAX(INDEX(($A$2:$A$11=A2)*($C$2:$C$11),0))-LARGE(INDEX(($A$2:$A$11=A2)*($B$2:$B$11),0),COUNTIF($A$2:$A$11,A2)),"")、
 表示形式　→ユーザー定義 [mm]"分"　ではいかがでしょうか？

（jjj） 2020/08/06(木) 16:09

 そっか、中断している場合があるんですね。
 なので、無視してください。
（jjj　） 2020/08/06(木) 16:18

 ちょっと式が長くなりますが、例示のようにD列が終了時刻ー開始時刻（表示形式　[mm]"分"）として、

 E2　=IF(COUNTIF($A$2:$A$11,A2)=1,"",IF(A2<>A3,MIN(MAX(INDEX(($A$2:$A$11=A2)*($C$2:$C$11),0))-LARGE(INDEX(($A$2:$A$11=A2)*($B$2:$B$11),0),COUNTIF($A$2:$A$11,A2)),SUMIF($A$2:$A$11,A2,$D$2:$D$11)),"")) ではどうでしょう？

（jjj　） 2020/08/06(木) 16:46

 氏名、start,endをキーとして昇順（氏名は固まっていさえすれば可）
 になっていることを前提として、以下の計算式ではどうですか？

        A       B       C       D       E       F       G       H
  1             start   end     期間    start2  end2    期間2   通算期間
  2     Aさん   14:37   16:10   1:33    14:37   16:10   1:33    93分
  3     Hさん   9:00    11:00   2:00    9:00    11:00   2:00    
  4     Hさん   10:00   12:00   2:00    11:00   12:00   1:00    
  5     Hさん   10:30   11:30   1:00    12:00   12:00   0:00    180分
  6     Iさん   9:00    12:00   3:00    9:00    12:00   3:00    
  7     Iさん   10:00   11:00   1:00    12:00   12:00   0:00    
  8     Iさん   10:30   11:30   1:00    12:00   12:00   0:00    180分
  9     Jさん   9:00    11:00   2:00    9:00    11:00   2:00    
 10     Jさん   10:00   12:00   2:00    11:00   12:00   1:00    
 11     Jさん   13:00   15:00   2:00    13:00   15:00   2:00    
 12     Jさん   14:00   16:00   2:00    15:00   16:00   1:00    360分

 D2: =C2-B2	
 E2: =IF($A2<>$A1,B2,MAX(B2,F1))
 F2: =IF($A2<>$A1,C2,MAX(C2,F1))
 G2: =F2-E2
 H2: =IF(A2<>A3,SUMIF($A$2:$A$100,A2,$G$2:$G$100),"") 
      (100は最終行のつもり）
 これらを下にコピー     
 （なお、私でしたら、名前をまとめた表に、SUMIFでG列を集計しますね。）

 なお、jjjさんの 2020/08/06(木) 16:46の式では、
 Jさんは 420分になるように思います。

 余談：
 ところで、
 jjjさんの 2020/08/06(木) 16:46の式などは、
 使用される方は皆さん普通に理解されるんですか？すごいですね。
 私は、マクロより計算式のほうが難しいと感じることがたびたびあります。
（γ） 2020/08/07(金) 10:08

 γさま

 >なお、jjjさんの 2020/08/06(木) 16:46の式では、
 > Jさんは 420分になるように思います。

 ご指摘のとおりですね。中断と重複が混在することを想定していませんでした。
 私も普段はこんなややこしい関数を使うことは滅多にしません。
 メンテナンスを考えれば、作業列を使った方が分かりやすいので。

（jjj） 2020/08/07(金) 11:43

コメント返信：

[ 一覧(最新更新順) ]

[[20200806121846]] 『総継続時間の計算』（計算式学びたい） ページの最後に飛ぶ

[[20200806121846]]　『総継続時間の計算』（計算式学びたい）　ページの最後に飛ぶ