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

質問

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

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
・
・

回答No.3

MackyNo1 · ベストアンサー

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

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

出社時刻
=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

お礼

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

回答No.2

MRT1452

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

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

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

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

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

※あくまで机上論です。

お礼

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

回答No.1

o_chi_chi

下記でどうでしょう

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

お礼

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