利用時間の求め方

質問

Excelで利用時間の求め方関する質問です。

時間 氏名
10:00:10 A ログイン
10:00:20 B ログイン
10:00:15 C ログイン
10:01:10 A ログアウト
10:02:00 C ログアウト
10:02:02 A ログイン
10:02:03 B ログアウト
10:03:00 A ログアウト

ログイン、ログアウトをしたデータを一日何回ログインをして何時間アクセスしていたか求めたいのですが、関数やマクロを使ってうまくできないでしょうか？

教えてください！！
お願いします！

回答No.1

tom04 · ベストアンサー

こんばんは！
オートフィルタ等色々やり方はあるかと思いますが・・・

一例です。
Sheet1のデータをSheet2に表示するようにしてみました。

↓の画像のようにSheet1・Sheet2に作業用の列を設けます。
Sheet1の作業列D2セルに
=IF(OR(COUNTBLANK(A2:C2),B2<>Sheet2!$B$1),"",ROW())
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてSheet2の作業列D1セルに
=IF(COUNT(Sheet1!D:D)<ROW(Sheet1!A1),"",ROUND(INDEX(Sheet1!A:A,SMALL(Sheet1!D:D,ROW(Sheet1!A1))),5))
としてこれもオートフィルでずぃ～～～！っと下へコピー！

Sheet2のE2セルに
=IF(D2="","",D2-D1)
という数式を入れ、E1・E2セルを範囲指定　→　E2セルのフィルハンドルで2行ずつ下へコピー！

最後に
B2セルに
=IF(B1="","",COUNT(E:E))

B3セル（セルの表示形式はユーザー定義から　[h]:mm:ss　）に
=IF(B1="","",SUM(E:E))

とすれば画像のような表示になります。

※　注意点　※
・Sheet1の時刻は昇順になっている前提です。
・最後が「ログイン」の場合は「ログアウト」していないので、回数・合計時間に反映されません。

参考になりますかね？m(_ _)m

補足

回答ありがとうございます！
個人別と書き忘れてしまいました！
誰がその日何時間アクセスしていたか知りたいのですヽ(´o｀；

回答No.10

KURUMITO

時間の入力で日付までも入力されているということは日付が変わっても同じ列にデータが入力されているということで１日だけのデータが有るシートということではないということでしょう。
次のようにしてはどうでしょう。
お示しのような元のデータはシート１に有り、A２セルから下方に日付の入った時間が、B２セルから下方に氏名が、C２セルから下方にログインログアウトがそれぞれ入力されているとします。
D列に作業列を作って対応することにして、お求めの表は例えばシート２に表示させることにします。
シート２ではA１セルには検索期間と文字列を入力しB１セルには月初めの日付を例えば２０１２/１２/１のように入力します。C１セルには　から　と入力、D１セルには月の終わりの例えば２０１２/１２/３１と入力します。
このB１とD１の日付は月の初めや終わりでなくとも数カ月の間になってもよいのですが、式が複雑になりますのでその期間においては初めにその期間に関係する方についてはすべての方のログインが有ってその後にログアウトで終わっていることが必要条件となります。勿論その期間において何回もログインとログアウトが有っても構いません。
そこでシート１に戻ってシート１のD２セルには次の式を入力し下方にドラッグコピーします。

=IF(OR(A2<Sheet2!B$1,A2>Sheet2!D$1,B2="",C2=""),"",IF(C2="ログイン",IF(COUNTIF(D$1:D1,1000)=0,1000,IF(COUNTIF(INDEX(B$1:B1,MATCH(1000,D$1:D1,0)):B2,B2)=1,ROUNDDOWN(MAX(D$1:D1),-3)+1000,INDEX(INDEX(D$1:D1,MATCH(1000,D$1:D1,0)):D1,MATCH(B2,INDEX(B$1:B1,MATCH(1000,D$1:D1,0)):B1,0))+COUNTIF(INDEX(B$1:B1,MATCH(1000,D$1:D1,0)):B1,B2)*10)),IF(C2="ログアウト",A2-INDEX(INDEX(A$1:A1,MATCH(1000,D$1:D1,0)):A1,MATCH(INDEX(INDEX(D$1:D1,MATCH(1000,D$1:D1,0)):D1,MATCH(B2,INDEX(B$1:B1,MATCH(1000,D$1:D1,0)):B1,0))+(COUNTIF(INDEX(B$1:B1,MATCH(1000,D$1:D1,0)):B1,B2)-1)*10,INDEX(D$1:D1,MATCH(1000,D$1:D1,0)):D1,0)),"")))

この作業列でのデータをもとにお求めの表をシート２に表示させます。
シート２のA２セルには氏名、B２セルにはログイン回数、C２セルにはアクセス時間と項目名を入力します。
A３セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)<=INT(MAX(Sheet1!D:D)/1000),INDEX(Sheet1!B:B,MATCH(ROW(A1)*1000,Sheet1!D:D,0)),IF(ROW(A1)=INT(MAX(Sheet1!D:D)/1000)+1,ROW(A1)-1&"名集計",""))

これによって氏名が自動的に表示されしかも最後の行では集計の数値が表示されます。
B３セルには次の式を入力して下方にドラッグコピーします。

=IF(A3="","",IF(COUNTIF(A3,"*集計")=0,COUNTIF(Sheet1!D:D,">="&1000*ROW(A1))-COUNTIF(Sheet1!D:D,">="&1000*(ROW(A1)+1)),SUM(B$2:B2)))

C3セルには次の式を入力し下方にドラッグコピーします。

=IF(A3="","",IF(COUNTIF(A3,"*集計")=0,MOD(SUMIF(Sheet1!B:B,A3,Sheet1!D:D),10),SUM(C$2:C2)))

最後にC列にはシリアル値が表示されますのでC３セルから下方の選択して右クリックし、「セルの書式設定」から「表示形式」で「時刻」から選択します。

回答No.9

tom04

たびたびごめんなさい。

ログアウトの形態
＞【強制ログアウト】や【タイムアウト】と【通常ログアウト】
を見逃していました。

前回は無視して
数式を

=IF(A2="","",SUMIFS(Sheet1!A:A,Sheet1!B:B,A2,Sheet1!C:C,"*アウト*")-SUMIFS(Sheet1!A:A,Sheet1!B:B,A2,Sheet1!C:C,"ログイン")+IF(ISODD(COUNTIF(Sheet1!B:B,A2)),MAX(IF(Sheet1!B:B=A2,IF(Sheet1!C:C="ログイン",Sheet1!A:A))),0))

としてみてください。m(_ _)m

回答No.8

tom04

No.1・4です。

合計時間がマイナスになってしまっているというコトですので・・・

ログイン形態は1種類のみ、ログアウト携帯が3種類の合計部分で引っかかっているように思われます。
おそらく数式を　ログイン1+ログイン2+ログイン3　のようにしていらっしゃると思いますので、

No.4で回答したC2セルの数式を↓のように変更してみてください。

=IF(A2="","",SUMIFS(Sheet1!A:A,Sheet1!B:B,A2,Sheet1!C:C,"ログアウト*")-SUMIFS(Sheet1!A:A,Sheet1!B:B,A2,Sheet1!C:C,"ログイン")+IF(ISODD(COUNTIF(Sheet1!B:B,A2)),MAX(IF(Sheet1!B:B=A2,IF(Sheet1!C:C="ログイン",Sheet1!A:A))),0))

※　ログアウト1～3をそれぞれ合計するのではなく、
「ログアウトが含まれるもの」の合計をしています。

これでもダメならごめんなさいね。m(_ _)m

回答No.7

keithin

毎回「読んでませんでした」「やってみます」ばっかり。

やってみて上手くいったのか行かなかったのか、どうやったらどうなったのか、何もフィードバックがありませんね。お礼だけ言っとけばいいや、質問する気が無いって事かなと思います。

＞年月日が入っている

実際にやってみれば、計算できることは確認できたはずです。
やってみて何か上手くいかなくて質問しているのですか？他の人からの回答の数式とごっちゃにしてしまい、勝手に間違えるご相談もとても多いです。
それともやっぱり手を動かしもせずに、口だけ動かしているんでしょうか。
回答の数式を見て一体何をどうやってどういう結果が現れたのか、ちゃんと情報を出してください。

たとえば実際のデータがどんだけあるのか知りませんが３万行あるところに、回答の数式を何も考えずにそのままコピーしてみたとか。そういった「具体的な姿」と「どうなった」があれば、もっと手早く問題も解決できます。

補足

ご指摘ありがとうございます。

そうですね。具体的な姿とどうなったをうまく説明できればよいのですが、私の知識の無さなのかうまく表現ができませんでした。
不快に思わせてしまい申し訳なく思います。

keithinさんに教えていただいた、

⇒Ａの時間数は、ご利用のエクセルのバージョンが不明なのでExcel2007以降を使い
　=SUMIFS(A:A,B:B,"A",C:C,"ログアウト")-SUMIFS(A:A,B:B,"A",C:C,"ログイン")
　で求まります。

を試してみたのですが、【"A"】つまり氏名など個人を特定するコードは3000件ほどあり番号とアルファベットの組み合わせのため式を作るのが困難であきらめてしまいました。
別途コード一覧は別に作成しているのですが…

またそのあと回答いただいた、

⇒＃ユーザー名を計算で出すのは無駄なので
　手順:
　Ｂ列をコピーする
　Ｅ列に貼り付ける
　データタブの「重複の削除」でユーザー一覧を作成する

上記は、ご指摘の通りほかの方から回答いただいた数式と組み合わせて使おうとしました。
なぜなら、元データには重複する行はないので何を指しているか理解できませんでした。

また、回答の数式を何も考えずにそのままコピーはしていません。
回答いただいた式の範囲を自分の持っているデータの部分に置き換えて式を作成しています。

ここで改めて求めたい【具体的な姿】について記載します。

*****************************************************************************
一定期間、誰が何時間ログイン状態を保持していたかを求めたいのです。
期間中、ユーザはログイン、ログアウトを繰り返し行われているので、
【期間はひと月で個人別の総ログイン保持時間を求める】これが具体的な姿です。
*****************************************************************************

回答No.6

tom04

No.1・4です。

＞時間の部分なんですが、実は頭に年月が入っているんでです。
＞2012/10/01のように。

に関しては、おそらくそのままで大丈夫だろうと思います。
としかお答えできません。
実際のデータがこちらでは判らないので、お手元のデータで試してみてください。
もし、ダメならどういう風にダメだったのかが判らないとアドバイスしようがありません。

＞一ヶ月分の総ログイン時間を出すにはこの式でいけますか？
＞それとログアウトには三種類あって
＞"ログアウト1"+"ログアウト2"+"ログアウト3"
＞のようにすれば大丈夫でしょうか。

の部分に関して

ログイン・ログアウトは二つでワンセットと考えるのが普通だと思います。
ログアウトの形態が3種類ある！という部分が理解に苦しむのですが、

とりあえずこれもお考えの方法でやってみてください。
keithinさまの方法だとおそらく大丈夫だろうと思われます。

この程度しかアドバイスできませんが、ごめんなさいね。m(_ _)m

補足

回答ありがとうございます！

写真付きで送っていただいた回答案でほぼデータをまとめることができました！
ログアウトの形態は、【強制ログアウト】や【タイムアウト】と【通常ログアウト】とあったのですが、全て【ログアウト】に置き換えて対応しました。

問題は、ログイン時間がまとめられません。
最初質問した時は、その日何度もログイン、ログアウトをしている人のログインしている総時間を求めたいと書いたのですが、1か月単位でその人がどれだけログインしていたか時間を求めたいのです。

データの時間列の表示は、
【2012/10/10 10：10：10】のようになっているのですが、教えていただいた式を入れてみると【-1214545.254】のようになっていて時間に変換することができないのです。

いろいろ試してみたのですが、どうしても時間に変換してくれないのです。
大体は#########################となってしまいます。

何度もすみません。どうかお力をお貸しください(; - ;)

回答No.5

keithin

さて、寄せられている回答を読んでいないので、ログインとログアウトがきちんと対応しているかどうかについてもスルーして、何も補足がありませんでしたね。

というワケで。
○ログインとログアウトがきちんと対になって含まれている
○ログインしたけどログアウトの記録が無い
○ログインが無く、ログアウトから記録されている
○ログアウトから記録され、ログインで終わっている（＝見た目上は数が合っている点に留意）
といったパターンがあるので、その全部を含めて計算してみます。

＃ユーザー名を計算で出すのは無駄なので
手順:
Ｂ列をコピーする
Ｅ列に貼り付ける
データタブの「重複の削除」でユーザー一覧を作成する

Ｆ２に
=SUMIFS(A:A,B:B,E2,C:C,"ログアウト*")-IF(VLOOKUP(E2,B:C,2,FALSE)="ログイン",0,INDEX(A:A,MATCH(E2,B:B,0)))-SUMIFS(A:A,B:B,E2,C:C,"ログイン")+IF((LOOKUP(E2&0,IF($B$2:$B$9999=E2,$B$2:$B$9999,0),$C$2:$C$9999))="ログイン",LOOKUP(E2&0,IF($B$2:$B$9999=E2,$B$2:$B$9999,0),$A$2:$A$9999))
と記入し、コントロールキーとシフトキーを押しながらＥｎｔｅｒで入力する
以下コピー。

お礼

朝早くに回答ありがとうございます！
本当に助かります(＞人＜;)

手順通り今からやってみます！

補足

度々すみません。
もう一つ教えてください！
Tom04さんにも聞いているのですが、時間の部分なんですが、実は頭に年月が入っているんでです。
2012/10/01 00:00:00のようになっています。
一ヶ月分の総ログイン時間を出すにはこの式でいけますか？

回答No.4

tom04

No.1です。
補足を読ませていただいて・・・

結局すべて人のデータを表示したい！というコトですよね？

keithinさんの方法を拝借させていただきます。

↓の画像のように今回もSheet1の作業用の列を設けます。
Sheet1の作業列D2セルに
=IF(COUNTIF(B$2:B2,B2)=1,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピー！

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

B2セルに
=IF(A2="","",COUNTIFS(Sheet1!B:B,A2,Sheet1!C:C,"ログイン"))

C2セルに
=IF(A2="","",SUMIFS(Sheet1!A:A,Sheet1!B:B,A2,Sheet1!C:C,"ログアウト")-SUMIFS(Sheet1!A:A,Sheet1!B:B,A2,Sheet1!C:C,"ログイン")+IF(ISODD(COUNTIF(Sheet1!B:B,A2)),MAX(IF(Sheet1!B:B=A2,IF(Sheet1!C:C="ログイン",Sheet1!A:A))),0))

C2セルだけが配列数式になってしまいますので、Shift+Ctrl+Enterで確定！
この画面からコピー＆ペーストする場合はC2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

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

※　今回はログイン中（ログアウトしていない）場合もログイン回数に含んでみました。
※　C列の合計時間にはログアウトしていない時間は含まれません。

何度も失礼しました。m(_ _)m

この投稿のマルチメディアは削除されているためご覧いただけません。

お礼

回答ありがとうございます!!!
それも画像それもとってもわかりやすいです！
今から早速やって見ます！
わからなかったら、、、また教えてください（≧∇≦）

補足

度々すみません。
もう一つ教えてください！
時間の部分なんですが、実は頭に年月が入っているんでです。
2012/10/01のように。
一ヶ月分の総ログイン時間を出すにはこの式でいけますか？
それとログアウトには三種類あって
"ログアウト1"+"ログアウト2"+"ログアウト3"
のようにすれば大丈夫でしょうか。
教えてください(o^^o)

回答No.3

keithin

ん？
＞誰がその日何時間アクセスしていたか知りたいのですヽ(´o｀；

回答済みですが、見てないのですか？　回答を読んでないのか、それとも気が付かなかったのですね。

再掲：
Ａに限って数字を出してみると。

Ａの回数は簡単に
=COUNTIF(B:B,"A")/2

Ａの時間数は、ご利用のエクセルのバージョンが不明なのでExcel2007以降を使い
=SUMIFS(A:A,B:B,"A",C:C,"ログアウト")-SUMIFS(A:A,B:B,"A",C:C,"ログイン")
で求まります。

Ｂの回数：
=COUNTIF(B:B,"B")/2
Ｃの回数：
=COUNTIF(B:B,"C")/2

この様子では、ご利用のエクセルのバージョンが書いてないですよと指摘しておいたのも、同様に見ていませんね？

補足

お返事ありがとうございます！
見ていませんでした、、、すみません！
エクセルのバージョンは2010になります。
教えていただいた方法で試してみますね！！本当に助かります。

回答No.2

keithin

簡単に、ログインした「全員が」ログアウトしている状態までの記録が得られているとします。

で。
いったい何を求めたいのでしょうか。

＞ログイン、ログアウトをしたデータを一日何回ログインをして何時間アクセスしていたか求めたい

ごくシンプルに、ＡＢＣ全員分のログイン時間数の合計が求めたいなら
=SUMIF(C:C,"ログアウト",A:A)-SUMIF(C:C,"ログイン",A:A)
で出来ます。

勿論全員のログイン回数合計は
=COUNTIF(C:C,"ログイン")
で十分です。

それじゃつまらないので、Ａに限って数字を出してみると。

Ａの回数は簡単に
=COUNTIF(B:B,"A")/2

Ａの時間数は、ご利用のエクセルのバージョンが不明なのでExcel2007以降を使い
=SUMIFS(A:A,B:B,"A",C:C,"ログアウト")-SUMIFS(A:A,B:B,"A",C:C,"ログイン")
で求まります。考え方は先の全員分と一緒ですね。

＃補足
Ａが最終的にログアウトしていないデータしか得られて無い状況では、「今現在」までＡがログインしっぱなしという事になってしまい、時間数は計算できません。

補足

個人別と書き忘れてしまいました！
誰がその日何時間アクセスしていたか知りたいのですヽ(´o｀；