[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『総継続時間の計算』(計算式学びたい)
はじめまして。
オンライン会議のデータ処理で困っています。
下記のようなデータがあり、参加者によっては複数機器を使用するため
ログが2つ、3つある人がいます。
参加者別にログインの総継続時間(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(木) 14:06
そう簡単でもないのでは? 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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.