データの集計をして、Excelのブックに保存する処理

質問

1500人分のデータ（有給残日数・振休未消化日数・時間外）を所属別に集計して、
内訳と所属の合計値を所属別にブックを作成して保存し、それに全体の平均値を出したシートを追加して、
グラフを作成し、該当所属長にメールで送信するという作業が毎月あり、
かなりの時間をかけてコピー・ペーストしながら手作業でやっております
（なんせ50箇所もあるため、50個のブックに分けるのが大変です。）
この一連の作業を、なんとか自動化できないものでしょうか？

Excelのマクロを今勉強中で、操作しながら記録するのと、少し改変することができる程度で、詳しい知識はもちあわせておりません。
Accessも、クエリはあつかえますが、SQLとかはわかりませんし、こちらも詳しい知識はもちあわせておりません。

データとしては、
本部コード・所属コード・所属名　・社員番号・氏　　名・時間外対象フラグ・有給残・振休日数・時間外
　999900　・　999999　・××支店・777777　・磯野並平・　　　　　　Y 　　・　　10　　・　　　3　　・　　5.5
　・
　・
　・

××支店のブック
　シート１・・・データシート：××支店　所属者のデータ・最終行に合計表示
　シート２・・・全体の平均値：××支店の平均値（時間外の合計÷時間外対象者の数）（有給残の合計÷人数の合計）（未消化日数の数÷人数の合計）
　　　　　　　　　　　　　　　○○支店の平均値　同上　　　【全支店共通】
　シート３・・・グラフ【全支店共通】

といったかんじにしたいのです。
シート２は、グラフを作成するためのものなので、グラフさえできれば、なくてもかまわないです。
Accessを使って、本部コードごとに集計して、Excelにエクスポートをして、その後はマクロでグラフを作成するのがいいのかなと思いますが、本部ごとのデータをそれぞれ分けてエクスポートすることが可能かどうか、マクロでグラフを作成するほうほうがわかりません。
Excelですべてできるものでしょうか？

Access大辞典やExcelのマクロの本を買い、インターネットで調べたりいろいろしておりますが、結局どのようにしたらいいのかという根本的なところがわかりませんので、困っております。
どうかお知恵をお貸しください。
よろしくお願いいたします。

回答No.1

papayuka · ベストアンサー

たぶん全てExcel-VBAで可能だと思います。
但し、相当のスキルを要求される処理で、道のりは険しいと思います。

取り合えずブック分けのサンプルを。
以下の条件で、サンプルブックを用意して試してみてください。

　1）新規ブックの一番左のシートのセル A1からタイトル行を含むデータがある
　2）B列の所属コードでソートされている
　3）上記のブックはマクロ実行前に名前を付けて保存されている。

下記を実行すると上記ブックと同じフォルダ内にB列毎のデータを保存します。
以上、参考まで。

'******************************************************************
Sub BookSeparate()
Dim myList(), wb As Workbook, tws As Worksheet, i As Integer

On Error Resume Next

Set tws = ThisWorkbook.Worksheets(1)
If Not tws.AutoFilterMode Then
　 tws.Range("A1").CurrentRegion.AutoFilter
End If

Call ListCreate(tws, myList, 2)

For i = 0 To UBound(myList)
　Set wb = Workbooks.Add(xlWBATWorksheet)
　wb.Worksheets(1).Name = myList(i)
　tws.Range("A1").CurrentRegion.AutoFilter _
　　　field:=2, Criteria1:=myList(i)
　tws.Range("A1").CurrentRegion.Copy _
　　　Destination:=wb.Worksheets(1).Range("A1")
　wb.SaveAs Filename:=ThisWorkbook.Path & "\" & myList(i) & ".xls"
　wb.Close
Next i

tws.Range("A1").AutoFilter

End Sub

'******************************************************************
Private Sub ListCreate(ws As Worksheet, rList, myCol As Integer)
Dim myLow As Long, cnt As Long

myLow = 2: cnt = 0

Do While ws.Cells(myLow, myCol).Value <> ""
　 If ws.Cells(myLow, myCol).Value <> _
　　　ws.Cells(myLow, myCol).Offset(-1, 0).Value Then
　　　ReDim Preserve rList(cnt)
　　　rList(cnt) = ws.Cells(myLow, myCol).Value
　　　cnt = cnt + 1
　 End If
　 myLow = myLow + 1
Loop

End Sub

回答No.3

noname#52504

いろいろなアプローチが考えられますが、
配布するブックをその都度ゼロから作成するのは大変なので、まずは、

　>>所属名を切り替えるだけでその月の当該所属の配布用ブックになるような雛形<<

を作ることを考えた方が簡単かと思います。

●元データについて
　雛形のブックが常に最新のデータを参照するよう、
　最新のデータが一定の場所に一定のファイル名で存在する必要があります。

●シート１について
　Excelのデータベースクエリを使うのが簡便かと思います。

　下記のページの説明が判りやすいですね。

　■外部データの取り込み（パラメータークエリの利用）:条件によるデータの抽出
　http://www11.plala.or.jp/koma_Excel/contents6/mame6041/mame604101.html

　このページではExcelからExcelへの取り込みを例にしていますが、
　元データがテキストファイルやmdbの場合でもいけます。

●シート２について
　>全体の平均値：××支店の平均値
　>【全支店共通】
　とありますが、当該所属についての集計なのでしょうか？
　それとも1500人全体についての集計なのでしょうか？

　前者であれば、シート１で取り込んだデータに応じて
　数式等で値が表示されるようにしておけば済みます。

　後者であれば、別途集計する必要があります。
　元データがExcelのブックであれば、
　元データ側のシートに結果が表示されるようにしておいて、
　配布用ブックに貼り付ければよいかと。
　ただし、外部に送信するので、
　元データを参照している部分は値として確定する必要があります。

●シート３について
　シート２の値を参照するようにしておけば、
　シート２の値が変わればグラフも勝手に変わりますね。

結局、一度雛形を用意しておけば、
手動で操作する(自動化にはマクロが必要になる)部分は…

１．(必要ならば)全体の集計データを配布用のブックに貼り付ける　×１回
２．所属名を切り替える(書き換える)　×５０回
３．(必要ならば)保存する　×５０回
４．メールで送信する　×５０回

の４点ということになります。

１は毎月１回のことなので、手動でもたいした手間ではありませんが、
あえてマクロでやるなら「ブック間の転記」ということになります。

２，３は是非自動化したいところですね。
「特定のセルを順に書き換えて保存」というだけの動作ですから、
質問すれば回答がつくと思います。

４は、個人情報に関することですから、自信がなければ手動でやった方が無難だと思いますが、
マクロでやるなら、SendMailメソッドとか、あるいは下記のようなシステムを使うとか…。

　■CDOでメール送信
　http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_800_080.html

以上、ご参考まで。長乱文陳謝。

回答No.2

imogasi

あえて言います。
こんな盛りだくさんの仕事の全部を、回答者に丸投げでやってくださいというのは無理です。普通はシステム担当部署か、業者にやってもらう事項のはず。
もっと自分で勉強して、課題を小さく分けて、１つずつ解決していかないと。
エクセルでもアクセスでも、マスター的な原資料はできるだけ分けないほうが良いと思う。５０部署にブックを分けるなんて、私の考えでは、絶対しません。
分けなければならない理由は何でしょうか。
印刷ぐらいしか考え付かないが。各部にその電子データを配っているのでしょうか。
まず
（１）部ごとにデータを行の固まりにする方法。＝＝＞ソート
（２）行の塊を取り出す方法＝＞例えば別ブックの１シートに
書き出す。
（３）１つのブックのシートをメイルで送る方法（下記では、アクチブ・シートしか送らないようだ）
（４）部ごと送信を連続して、終わりまで行う方法。
を調べてください。
しかし（３）はファイルーメイルの送信、あたりをやれば、良いがマクロの記録で記録しませんね。
するとエクセルの範囲外の機能の利用になるので、勉強が難しい。
ただWEBで例が載っていると思うので調べてください。
例えば
http://officetanaka.net/excel/vba/tips/tips45.htm
ーー
これらの課題はVBAでも超上級だと思うので、しばらくは調べまくらないと、すぐは無理でしょう。

補足

すみません。質問の仕方が悪かったですね。
すべて回答者さまにやってくださいとお願いしているのではなくて、
自動化するに当たって、どのような手順で、どのようなソフトを使うのがよいか、ご指導いただきたかったのです。
私はアクセスで集計して、各部ごとにテーブル作成クエリか何かでエクセルにエクスポートしたものを作成し、それに全体の平均値のシートをマクロで付け加えるという方法までしか考え付かなかったのですが、それもできるかどうかは不明なので、ほかにいい方法があるか？
エクセルで全部できるのかどうか？

エクセルで集計まではわかりますが、行の塊を取り出すという方法は、わかりません。

各部にその資料をメールで送信しなければいけないため、ブックは分けなければなりません。
メールで送るのは手作業でもかまわないです。それまでの作業が少し楽になればと思いまして・・・。
メールを送るところまで自動化できるとは、すごいですね。