こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

入退室ログから出社時刻、退社時刻を抽出したい

1カ月分の入退室ログの元データから、
各日の一番早い時刻を出社時刻、一番遅い時刻を退社時刻と
みなして個人別の勤務表データを作ろうと思います。

EXCELのマクロで実現可能でしょうか?
どなたか親切な方、プログラミング方法を教えてください<(__)>
元データをsheet1に、ボタンクリックで、別シートに出力できるのが理想です。

<元データ>
操作者,時刻
太郎さん,2011/12/1 8:10
花子さん,2011/12/1 8:40
太郎さん,2011/12/1 8:55
花子さん,2011/12/1 12:05
太郎さん,2011/12/1 15:22
太郎さん,2011/12/1 17:59
花子さん,2011/12/1 18:30
太郎さん,2011/12/1 21:10
花子さん,2011/12/2 8:22
太郎さん,2011/12/2 8:58
太郎さん,2011/12/2 11:00
花子さん,2011/12/2 12:01
太郎さん,2011/12/2 15:39
太郎さん,2011/12/2 17:01
太郎さん,2011/12/2 18:02
花子さん,2011/12/2 19:55
太郎さん,2011/12/3 9:02



<output>
太郎さんの出社時刻,退社時刻
2011/12/1 8:10,2011/12/1 21:10
2011/12/2 8:58,2011/12/2 18:02

投稿日時 - 2011-12-20 15:34:59

QNo.7200295

困ってます

質問者が選んだベストアンサー

このケースならVBAで対応するより関数で表示したほうが簡単かも知れません。

データが1000行目までに入力されており、開始年月日がD1セル、名前がE1セルに入力されているなら、以下の式を入力して下方向にオートフィルしてください。

出社時刻
=MIN(INDEX((($A$2:$A$1000<>$E$1)+(INT($B$2:$B$1000)<>$D$1+ROW(A1)-1))*100000+$B$2:$B$100,))

退社時刻
=MAX(INDEX((($A$2:$A$1000=$E$1)*(INT($B$2:$B$1000)=$D$1+ROW(A1)-1))*$B$2:$B$1000,))

該当データがない場合、空白表示する必要があるなら、セルの書式設定で表示形式をユーザー定義にして以下のように入力して下さい。

[>50000]"";[=0]"";yyyy/m/d h:mm

投稿日時 - 2011-12-20 18:10:35

お礼

ご回答ありがとうございます。
頂いた関数で試してみたのですが、退社時刻はばっちり出たのですが、
なぜか出社時刻は#N/Aと表示されてうまくいきません。
ご教授いただけるとありがたいです。
よろしくお願いします。

投稿日時 - 2011-12-21 10:45:48

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

作り方は色々あると思いますが、可能です。
VBAマクロでという事なので結構力技に派なると思いますが。

ぱっと考えて、
人と日付で二重ループにして、チェックですかね。


ちょっとしたテクとして、メインの処理をする前に、
操作者を第1ソートキー、,時刻を第2ソートキーとして並べ替えをする処理を入れると良いかと。
そうすする事でデータが並んでいるという前提でメインのマクロを組めるので、考えやすくなります。

例えば作業者の現在の行と1つ前の行のデータが違えば、その作業者のデータは終わりというような判断が出来るようになります。
バラバラだとまた最初から舐めていく必要が出てきますが、並べ替えて判定させれば最初からやり直す必要も無いので、
処理時間が短くなることも期待できます。

また時間も並べかえることで同様に日の区切りも判定できますし、
その中で頭と終わりを抜けば、その日の最初と最後となります。

※あくまで机上論です。

投稿日時 - 2011-12-20 17:12:04

お礼

ご回答ありがとうございます。
考え方は理解できました。
私自身、VBAマクロがわかってもいないのに、
それを指定したのが間違いだったと気がつきました。
ありがとうございます。

投稿日時 - 2011-12-21 10:40:51

ANo.1

下記でどうでしょう

outputは勉強と思って自分でアレンジしてください。
----
Sub test1()
Dim strSql As String
Dim cn As Object
Dim rs As Object
Const adOpenForwardOnly = 0

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & ThisWorkbook.FullName & "; ReadOnly=True;"
.Open
End With

strSql = "Select " _
& " 操作者 " _
& " ,format(時刻,'yyyymmdd') as 日付 " _
& " ,min(format(時刻,'hh:nn')) as 出社 " _
& " ,max(format(時刻,'hh:nn')) as 退社 " _
& "From [Sheet1$A1:B18] " _
& "group by 操作者, format(時刻,'yyyymmdd') "

' Debug.Print strSql
rs.Open strSql, cn, adOpenForwardOnly

Worksheets("sheet2").Cells(1, 1).Value = "操作者"
Worksheets("sheet2").Cells(1, 2).Value = "日付"
Worksheets("sheet2").Cells(1, 3).Value = "出社"
Worksheets("sheet2").Cells(1, 4).Value = "退社"
Worksheets("sheet2").Cells(2, 1).CopyFromRecordset rs

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

MsgBox "Sheet2に出力しました"
End Sub

投稿日時 - 2011-12-20 17:03:08

お礼

早速のご回答ありがとうございました。
頂いたコードで試してみたのですが、
[Microsoft][ODBC Driver Manager]パラメータが少なすぎます。2を指定してください。
が出てどうしていいかわからなくなり挫折してしまいました。

投稿日時 - 2011-12-21 10:37:07

あなたにオススメの質問