【エクセル】月間個人別集計表の作成方法ほか

質問

ここ数日いろいろ検索していたのですが、ビビッっとくるものがなく、申し訳ありませんが、どなたか御教授ください。
１　やりたいこと
　　超過勤務の計算を個人別に集計したい。
　　データの元ネタはＣＳＶで毎月出力されるため、
　　これをエクセルに貼り付け、別シートに集計表を作成したい。
２　具体的には
●レコード数（５００件程度／月）
　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　 Ｆ　　　 Ｉ
１　名前　社員ID　部署ID　担当名　 日付　 超勤125　超勤150
２ 氏名１　123　　1234　 販売 　2008/7/3　240 　　100
３ 氏名１　123　　1234　 販売 　2008/7/4　100 　　 0
４ 氏名１　123　　1234　 販売 　2008/7/5　150 　　 0
５ 氏名１　123　　1234　 販売 　2008/7/6　 30 　　 0
６ 氏名２　456　　7890　 流通 　2008/7/1　240 　　190
７ 氏名２　456　　7890　 流通 　2008/7/10 240 　　110
８ 氏名２　456　　7890　 流通 　2008/7/11 150 　　 90
９ 氏名３　987　　7890　 流通 　2008/7/3　130 　　100
※超勤125、超勤150のデータは分単位である。

●作りたい集計表（別シートに作成することを想定）
名前　　社員ID　部署ID　担当名　 超勤125　超勤150
氏名１　　123　　1234　 販売　　9(520)　　2(100)　　
氏名２　　456　　7890　 流通　　8(480)　　5(300)
氏名３　　987　　7890　 流通　　2(130)　　2(100)
※分単位を時間単位（３０分以上切上げ）で集計したい。
※超勤125、超勤150のカッコ内データは便宜的に表示したが、
本来不要な表示である。

３　ソフトのバージョン
　　WinXp Exel2002

４　その他
(1)ピボットテーブル
ピボットテーブルをにわか仕込みで勉強しましたが、
支給割合(125,150)ごとにうまく集計できませんでした。
氏名の隣にIDや担当名を表示することもできなかったです。
(2)毎月、同じ工程を踏む（ＣＳＶ→エクセル貼り付け→集計表作成）ので、マクロを使用したいと考えています。が、初心者です（泣）。申し訳ありませんが、どなたか助けてください。
(3)部署ID別、担当別一覧表も併せて作成したいです。

回答No.3

noname#204879 · ベストアンサー

A　　　 B　　　 C　　　 D　　　 E　　　　F
1　名前　　社員ID　部署ID　担当名　超勤125　超勤150
2　氏名１　　 123　　1234　販売　　　　　9　　　　2
3　氏名２　　 456　　7890　流通　　　　 11　　　　7
4　氏名３　　 987　　7890　流通　　　　　2　　　　2

B2: =VLOOKUP($A2,Sheet1!$A$2:$D$600,COLUMN(),FALSE)
此れを右２列にドラッグ＆ペースト

E2: =ROUND(SUMPRODUCT((Sheet1!$A$2:$A$600=$A2)*(Sheet1!F$2:F$600))/60,0)
F2: =ROUND(SUMPRODUCT((Sheet1!$A$2:$A$600=$A2)*(Sheet1!I$2:I$600))/60,0)

範囲 B2:F2 を下方にズズーッとドラッグ＆ペースト

お礼

早速のご対応に大変感謝しています。
ありがとうございました。
この数日の悩みが一気に晴れた感じです。
もっと勉強します。お手数おかけしました。

担当別一覧表や部署ID別一覧についても
この応用でやってみたいと思います。

回答No.4

n-jun

一例として：
Sub test()
Dim Dic As Object
Dim i As Long, j As Long
Dim k As Long
Dim v, vv, key

Set Dic = CreateObject("Scripting.Dictionary")

With Worksheets("Sheet1")
v = .Range(.[A2], .Cells(Rows.Count, 1).End(xlUp).Resize(, 7))
End With

ReDim vv(1 To 6, 1 To UBound(v, 1))

For k = 1 To UBound(v, 1)
If Not Dic.Exists(v(k, 2)) Then
i = i + 1
vv(1, i) = v(k, 1): vv(2, i) = v(k, 2): vv(3, i) = v(k, 3)
vv(4, i) = v(k, 4): vv(5, i) = v(k, 6): vv(6, i) = v(k, 7)
Dic(v(k, 2)) = Array(v(k, 2), i)
Else
j = Dic(v(k, 2))(1)
vv(1, j) = v(k, 1): vv(2, j) = v(k, 2): vv(3, j) = v(k, 3)
vv(4, j) = v(k, 4)
vv(5, j) = vv(5, j) + v(k, 6)
vv(6, j) = vv(6, j) + v(k, 7)
End If
Next

With Excel.Application
For k = 1 To i
vv(5, k) = .Ceiling(TimeSerial(0, vv(5, k), 0), _
TimeValue("0:30:0")) * 24
vv(6, k) = .Ceiling(TimeSerial(0, vv(6, k), 0), _
TimeValue("0:30:0")) * 24
Next
End With

ReDim Preserve vv(1 To 6, 1 To i)

With Worksheets("Sheet2")
.Cells.ClearContents
.Range("A1").Resize(, 6).Value = _
Array("名前", "社員ID", "部署ID", "担当名", "超勤125", "超勤150")
.Range("A2").Resize(i, 6).Value = Application.Transpose(vv)
End With

Set Dic = Nothing
Erase v, vv
End Sub

回答No.2

Horishita

先般このＯＫWaveで教えてもらった数式ですが、使えないでしょうか？。

=SUMPRODUCT(($E$3:$E$500>=DATE(2008,7,4))*($E$3:$E$500<=DATE(2008,7,9))*(($A$3:$A$500=$B1)*($F$3:$F$500)))
超勤150の集計は$G$3～に訂正する。

=sumproduct((日付欄を期間始まり）*（日付欄を期間終わり））指定します。更に続けて*（氏名１氏を指定($B1)）＊（集計したい欄を指定））
で如何でしょう。
集計値の換算（時間単位）は自分で追加してください。
IDや担当名の表示はコピペで持って来て下さい。
CSV-Fileをexcelに貼り付けた時に表示形式の指定、修正が必要かもしれませんね。

お礼

早速のご回答ありがとうございます。
恥ずかしながら今回初めて「SUMPRODUCT」関数の意味がわかったので、
今後は多用していきたいと思います。
お手数おかけしました。

回答No.1

n-jun

ＣＳＶファイルの区切りはどうなっていますか？
※CSV形式テキストファイルとはどのようなファイルでしょうか。
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_110_020.html
を参照願います。

それともＥｘｃｅｌに貼り付けた状態からの実行でしょうか？

補足

早速のレスありがとうございます。
情報が不十分で申し訳ありません。

扱うＣＳＶファイルは、文字列項目はカンマでは区切られているものの、両端がダブルクォーテーションやシングルクォーテーションで囲われていないものです。
改行コードはCR+LFが使用されている(模様です）。
はっきりしなくて申し訳ありません。

私自身があまり詳しくないため、今回の補足で情報が不足するようでしたら、Ｅｘｃｅｌに貼り付けた状態からの実行で構いません。
いろいろお手数おかけします。