![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
勤務表入力用ファイルが人数分、その集計用ファイルが1ファイルの構成になっています。
(10人の場合は11ファイル、30人の場合は31ファイル)
勤務表入力用ファイルは1つのブック内に1月~12月の12シートが入っています。
各シートに31日分の開始時間と終了時間を入力し、1日の作業時間と31日分の合計作業時間が自動計算されるようになっています。
集計用ファイルの表則は、
縦軸の項目が勤務者の名前が列挙され、横軸の項目はが1月、2月・・・12月、合計となっており、
全員の1年間の合計値が集計されます。
困っているのは、上記ファイル群を1セットとし、各グループに配布するので、
名前や人数は固定されていません。
ただMAX値としては30と想定されているため、
今の私が思いつくのは、
勤務表入力用ファイルは、ファイル名を01.xlsx、02.xlsx、・・・30.xlsx と用意しておき、
集計用ファイルの方には、縦01~30までの各ファイルの各月の合計作業時間を参照するように設定しておく方法です。
ですが、
・勤務表のファイル名を勝手に変更されたら参照できなくなる?
・集計用ファイルの縦軸には氏名を自動的に入れたい。
・万が一30名以上だった場合でも対応できた方が良い
等の課題が考えられるのですが、それらをクリアして汎用性を持たせたいです。
上記の解決法、またはもっとスマートなやり方等、
手法も含めてお知恵をお貸しください。
説明不足の点もあるかもしれません。ご指摘いただければ補足させていただきます。
No.3ベストアンサー
- 回答日時:
遅くなりましてすみません。
勤務表の1月~12月のシート名が何になっているか、また、シート名が変更されている場合でも集計できるように
シート名ではなく、勤務表のセルの内容で判定するようにしました。
勤務表ですので、すべて共通のフォーマットであることだと思いますので、ある程度推測でセル番地を入れてみました。
最初のほうのConstで始まっている行のセル番地を実際のものに変えていただければ、使用できます。
・勤務表の氏名が入力されているセル
・勤務表の合計作業時間が表示されているセル
・勤務表内に月が表示されているセル(数字だけが入っていると仮定しています)
もし、「1月」というように入力されているのであれば、マクロ内のSYUUKEISTの部分で数値だけ取り出した値を
使用するように修正をお願いします。
・集計表は3行目から反映するようにしていまして、
1列目が氏名、2列目以降が1月から12月の合計作業時間としています。
2列目に項目名や、1列目にタイトルなどを入れていただけるように、3行目からにしています。
(実行テスト確認済みです)
Option Explicit
Const SHIMEICELL = "B2" '勤務表の氏名のセル
Const GOUKEICELL = "B32" '勤務表の合計作業時間のセル
Const TSUKICELL = "C2" '勤務表の月のセル
Const SYUUKEIST = 3 '集計表の書き出し開始行
'集計ボタン
Sub ボタン1_Click()
Call sample1
End Sub
'集計処理
Sub sample1()
Dim sDir As String
Dim sFileName As String
Dim sFullName As String
Dim kinmuName As String
Dim kinmuSum As Long
Dim summaryRow As Long
Dim summaryWk As Workbook
Dim kinmuWk As Workbook
Set summaryWk = ThisWorkbook
'フォルダの選択
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
sDir = .SelectedItems(1)
Else
Exit Sub
End If
End With
'選択されたフォルダ内の最初のファイルを取得
sFileName = Dir(sDir & "\" & "*.xls?")
'選択されたフォルダ内にエクセルファイルがない場合
If sFileName = "" Then
MsgBox "勤務表がありません。", vbExclamation, "エラー"
Exit Sub
End If
'画面描画を停止
Application.ScreenUpdating = False
sFullName = sDir & "\" & sFileName
'勤務表を開き、集計表に値を転記
Do
sFullName = sDir & "\" & sFileName
'最初のファイルを開く
Set kinmuWk = Workbooks.Open(Filename:=sFullName, ReadOnly:=True)
'勤務表の最初のシートのA2に入っている氏名を取得
kinmuName = kinmuWk.Worksheets(1).Range(SHIMEICELL)
'集計表の何行目にその人のデータがあるか確認
summaryRow = fnGetRow(summaryWk, kinmuName)
'集計表に勤務表の合計作業時間を反映
Call sbSetSummary(kinmuWk, summaryWk, summaryRow)
'ファイルを閉じる
kinmuWk.Close
'同一フォルダ内の次のファイル名を取得
sFileName = Dir()
Loop Until sFileName = "" 'ファイルがすべて処理済みになったらループ終了
'画面描画を開始
Application.ScreenUpdating = True
MsgBox "集計終了"
End Sub
'集計表内の氏名を検索し、該当行を求める。
Function fnGetRow(summaryWk As Workbook, sName As String) As Long
Dim i As Long
With summaryWk.Worksheets(1)
'集計表の検索開始行
i = SYUUKEIST
Do Until .Cells(i, "A") = sName Or .Cells(i, "A") = ""
i = i + 1
Loop
'集計表に氏名が存在しない場合
If .Cells(i, "A") = "" Then
'最終行の下に氏名をセットし、その行を返す
.Cells(i, "A") = sName
End If
End With
'検索結果の行を返す
fnGetRow = i
End Function
'勤務シートから集計シートへ値を転記
Sub sbSetSummary(kinmuWk As Workbook, summaryWk As Workbook, summaryRow As Long)
Dim i As Long
Dim sKinmuName As String
For i = 1 To 12
'該当月の勤務表のワークシートを取得
sKinmuName = fnGetSheet(kinmuWk, i)
'シート存在時
If sKinmuName <> "" Then
'勤務表に反映
summaryWk.Worksheets(1).Cells(summaryRow, i + 1) = kinmuWk.Worksheets(sKinmuName).Range(GOUKEICELL)
End If
Next
End Sub
'勤務表内のシートから指定月のワークシートを取得
Function fnGetSheet(kinmuWk As Workbook, tsuki As Long) As String
Dim i As Long
Dim tmpSheetName As String
tmpSheetName = ""
With kinmuWk
For i = 1 To .Worksheets.Count
If .Worksheets(i).Range(TSUKICELL) = tsuki Then
tmpSheetName = .Worksheets(i).Name
Exit For
End If
Next
End With
fnGetSheet = tmpSheetName
End Function
No.4
- 回答日時:
No.3のマクロの集計表の画面イメージです。
![「人毎の勤務表を別ブックにてサマリ集計する」の回答画像4](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/e/1006347_5a7b8570f2553/M.jpg)
(No.3の補足の続きです)
長文すみません。
なので私は、下記のような処理を勤務表のファイルの数だけLOOPさせ、下方に列挙していましたがどうなんでしょう?もっと上手な処理ができるでしょうか。
----------------------------------------------------------------------------
Set srcSheet = srcBook.Worksheets("4月")
dstSheet.Cells(i, 4).Value = srcSheet.Cells(5, 4)'4月のシートから氏名を取得
Set srcSheet = srcBook.Worksheets("4月")
dstSheet.Cells(i, 17).Value = srcSheet.Cells(39, 11)'勤務表4月の合計作業時間
Set srcSheet = srcBook.Worksheets("5月")
dstSheet.Cells(i, 19).Value = srcSheet.Cells(39, 11)'勤務表5月の合計作業時間
・
・
・
Set srcSheet = srcBook.Worksheets("3月")
dstSheet.Cells(i, 39).Value = srcSheet.Cells(39, 11)'勤務表3月の合計作業時間
----------------------------------------------------------------------------
↓現在困っている個所です。
ブック、シート保護にて配布するので、シート不備エラーはあり得ない想定ですが、念のためエラー処理をしておきたいのですがそれができません。
万が一、シートが抜けている、またはシート名にスペースが入っているなど、一致しない場合(例えば11月だけ削除されてる、「11 月」になっている)、気づけるようにしたいです。
単に取得できない月は空欄になっていれば良いのですが、何故か私のやり方だと、空欄にならず何らかの値が入ってしまうため、シートの不備に気づけません。
もし可能でしたらよろしくお願いいたします。
No.2
- 回答日時:
INDIRECT関数を使わなくても、該当フォルダ内のエクセルファイルをひとつずつ開いて
氏名の入っているや集計値セルをコピーしていけばいいのではないでしょうか。
集計用ファイルは別フォルダにあるとした場合、フォルダ内のファイル名をDIR関数で取得して
ひとつずつ処理していけばいいと思います。
なるほど。
確かにそうですね。アルゴリズムは理解できましたが、私はまだ自力でソースをかけなくて、色々切り貼り、加工しながら作成しています。(涙)
厚かましいお願いですが、ある座標のセルの値を取得する例を見せては頂けないでしょうか?
フォルダ内のファイル名をひとつひとつ取得、記入するところまではできています。。。
No.1
- 回答日時:
VBAで行うこととなってしまいますが、下記の方法はいかがでしょうか。
《前提》
・勤務表入力用ファイルのあるセルに氏名を入れておく。
・勤務表入力用ファイルは1か所のフォルダ(以下、集計用フォルダ)にまとめておき、ファイル名は変更されてもいい状態とする。
《集計処理》
・集計用フォルダ内のブックを読み、氏名と合計作業時間を取得し、集計用ファイルに転記する。
(集計用ファイルに該当氏名があれば、その人の合計作業時間の欄に転記。氏名がなければ行追加して転記)
※氏名をキーとすると、同姓同名が出てくるかもしれないので、社員番号をもとに集計したほうがいいのではないかと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) VBA ふたつの同じ様式シートのセルをコピーしたい 2 2023/03/08 15:28
- Excel(エクセル) Excel、同じフォルダ内のExcelファイルの特定シートのみを1つのファイルに集約したい 8 2022/09/07 15:12
- Excel(エクセル) 【マクロ】同じフォルダ内にある複数ブックから1つのブック内の1シートにデータを集めたい 6 2022/09/28 18:16
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Visual Basic(VBA) VBAの参照先のファイル名をセルに書いて代入したい 2 2022/04/04 13:42
- システム CSVファイルのマッピング処理の省力化 1 2022/11/24 00:01
- Excel(エクセル) Excel使用前提 同じフォルダ内にあるファイルの集約等をマクロでできますか 4 2022/09/06 19:03
- Visual Basic(VBA) Excelのマクロコードについて教えてください。 1 2022/03/27 13:25
- その他(データベース) Excel VBA 転記について 1 2022/04/20 16:55
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
他部署からもらう データで、 ...
-
エクセルで特定の色の数字だけ...
-
【エクセル】オートフィルタで...
-
VBAで重複する項目を1つにまと...
-
ピボットテーブルでは時間の表...
-
excelで集計の合計を降順に並べ...
-
エクセルのプルダウン リスト...
-
エクセル、リソース不足エラー...
-
アンケートの集計
-
エクセル 日付(年月日)の合...
-
人毎の勤務表を別ブックにてサ...
-
Excel : 二通りの合計が合わな...
-
Excel VBAについて
-
いい機能だけど、毎回めんどく...
-
ピボットテーブルで同じデータ...
-
Excelの集計結果だけをコピー貼...
-
Excelで、空白を表示したい
-
エクセルの主軸と第2軸の0を合...
-
Excelで小数点以下1桁の年数を...
-
Excelで指定した条件と一致する...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルで特定の色の数字だけ...
-
ピボットテーブルでは時間の表...
-
フィルタをしても最下行を常に...
-
VBAで重複する項目を1つにまと...
-
ピボットの集計方法「合計」初...
-
アンケートの集計
-
ピボットを更新すると数式が入...
-
エクセルの小計を自動的に色づ...
-
エクセル、リソース不足エラー...
-
EXCELで増減率を計算したいです
-
EXCEL 重複データの集計の仕方
-
excelで集計の合計を降順に並べ...
-
VLOOKUP関数とCOUNT関数等の組...
-
ピボットテーブルで同じデータ...
-
常にタイトル行と合計行を表示...
-
Excelの集計結果だけをコピー貼...
-
いい機能だけど、毎回めんどく...
-
EXCELで0を除いた平均値...
-
エクセル 日付(年月日)の合...
おすすめ情報
ありがとうございます。
私も似てる仕様で考えておりました。
現在の進行状況は、
集計用ファイルに「勤務表取得」ボタンを設置し、
クリックするとエクスプローラが開き、勤務表の入っているフォルダを選択できるようにしました。
選択するとフォルダ内のエクセルファイルのみファイル名を非表示列に列挙するようにしました。
ファイル名をINDIRECT関数にて、該当ファイルの指定セルを参照するようにしています。
【ここで壁にぶつかっています】
INDIRECT関数は参照先ファイルが開いていないと、#REF!エラーになってしまいます。
これについて解決方法があればご教授いただけると助かります。
ありがとうございます!
完成度が高くて驚きました。
ここまで作り上げていただいたのに大変恐縮なのですが、下記のような仕様でもっと簡略化できませんか?(初めから伝えれば良かったのに申し訳ありません。)
勤務表のシート名は、1月、2月・・・12月の固定(数字は半角)なので単純にシート名からの判定のみで良いと考えています。
※一応、セルは「平成30年1月分」という書式です。
集計表の方は、
氏名、4月、5月・・・2月、3月の順の項目名で固定してあり、それぞれ下方に氏名、各月の合計作業時間が流し込まれるようにしています。
どんな氏名が何件あるかは分からない想定なので、とにかくファイル数だけ取得、貼り付けしていけば良しとしています。(MAX30件を想定してるので、集計表も予め30行確保)
(続く)