[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数ファイルの集計について(複数シート対象)』(abcd)
みなさま
お世話になります。
表題の件について、ご協力いただけると幸いです。
やりたいこととしては、
同一フォルダにある複数のエクセルファイルのI列の合計値を算出したいです。その際シート1だけではなく、シート3、シート7の合計値も算出したいです(シート1は各シート1の値を合計、シート3は各シート3の値を合計という感じです)。
ファイルのイメージとしては、
ファイルa(シート1)
A B C ... I 2017 男 正社員 100 2017 女 正社員 200 2017 男 契約 200
ファイルb(シート1)
A B C ... I 2016 男 正社員 50 2016 女 正社員 250 2016 男 契約 300
合計ファイルイメージ(シート1)
A B C ... I 2016 男 正社員 150 2016 女 正社員 450 2016 男 契約 500
これと同じフォーマットでi列の値だけが違うシート3、シート7についても同じように集計したいと考えております。
ここまでだと、よくある集計マクロだと思うのですが、
今回対象のデータでは、集計元のファイル(ファイルaやファイルb)の行数が違う場合があります(下記、ファイルc)。
ファイルCはファイルa、bの行に何行か増えているデータで、a、bにある行はすべてCにも存在しております。
ファイルcのような場合は、
Cにだけある行も合計ファイルに追加して計上したいと考えております。
ちなみにID列を設けることは難しいですが、
f、g、h列の計3列の値で各行は判別可能です。
ファイルc
A B C ... I 2016 男 正社員 100 2016 女 正社員 100 2016 女 アルバイト 200 2016 男 契約 100
a、b、c、集計イメージ
ファイルc
A B C ... I 2016 男 正社員 250 2016 女 正社員 550 2016 女 アルバイト 200 2016 男 契約 600
非常にわかりにくい説明になってしまいましたが、
よろしくお願いいたします。
< 使用 Excel:Excel2013、使用 OS:Windows7 >
Dim FSO As Object, f As Object, wb As Workbook, ctr As Variant, p As Variant, c As Range, r As Range, i As Long Set FSO = CreateObject("Scripting.FileSystemObject") With Application.FileDialog(msoFileDialogFolderPicker) .Title = "対象のフォルダを選択してください" If Not .Show Then Exit Sub p = .SelectedItems(1) End With For i = 1 To 3 Sheets(i).Cells.Clear Sheets(i).Name = "シート" & WorksheetFunction.Choose(i, 1, 3, 7) & "集計" Next i For Each f In FSO.GetFolder(p).Files If MsgBox(f.Name & "を集計しますか?", 36) = 6 Then Set wb = Workbooks.Open(Filename:=f, ReadOnly:=True) For Each ctr In Array(1, 3, 7) For Each c In wb.Sheets(ctr).Range("B:B").SpecialCells(xlCellTypeConstants) Set r = ThisWorkbook.Sheets("シート" & ctr & "集計").Range("B" & Rows.Count).End(xlUp).Offset(1) r.Value = c.Value r.Offset(, 1).Value = c.Offset(, 1).Value r.Offset(, 7).Value = c.Offset(, 7).Value Next c Next ctr wb.Close False End If Next f Set FSO = Nothing For i = 1 To 3 Set r = Intersect(Sheets(i).Range("I:I"), Sheets(i).UsedRange) If Not r Is Nothing Then For Each c In Intersect(Sheets(i).Range("I:I"), Sheets(i).UsedRange) If Not c.Value = Empty Then c.Offset(, 1).FormulaR1C1 = "=SUMIFS(C[-1],C[-8],RC[-8],C[-7],RC[-7])" c.Offset(, 1).Value = c.Offset(, 1).Value End If Next c End If Sheets(i).Range("I:I").Delete Shift:=xlToLeft Sheets(i).Range("$B$1:$I$" & Rows.Count).RemoveDuplicates Columns:=Array(2, 3, 8), Header:=xlNo Next i End Sub (mm) 2017/05/08(月) 16:41
さっそくのご返信ありがとうございます。
実は実際のシート名はシート1、3,7ではなく、別の名前になっております(ただ、左からの順番は1,3,7番目のファイルです)。
Sheets(i).Name = "シート" & WorksheetFunction.Choose(i, 1, 3, 7) & "集計" の部分をいじればよいのでしょうか? また、ほかにも変更すべき箇所があれば、ご教示していただけないでしょうか?
当方、初心者で低レベルの質問をしてしまい、申し訳ございません。
以上、よろしくお願います。
(abcd) 2017/05/08(月) 17:07
ご返信ありがとうございます。
当方、初心者でして、これからの応用のために、
お手数ですが、各セクションでどのような処理が行われているか教えていただけないでしょうか?
よろしくお願いします。
(abcd) 2017/05/09(火) 09:04
結果としては、うまくいっておりません。
実際のデータは、上記のデータと違って、
AからK列までデータが入力されており、集計したい値はI列、
データに空白がない列はJ列です。
この当たりが原因でしょうか?(上記コードではB列を参照している??)
あと、勘違いしておりまして、集計したいシートは左から
1、3、9の3列でした。
申し訳ございません。
ちなみに集計データでは、
AからK列のすべてを表示させ、I列が各行の合計値になるというイメージです。
正しく、伝えておらず、申し訳ございませrんでした。
以上、よろしくお願いします。
(abcd) 2017/05/09(火) 15:18
(mm) 2017/05/09(火) 17:43
勉強したいと思います。
ありがとうございました。
(abcd) 2017/05/09(火) 17:50
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.