[[20170508113150]]　『複数ファイルの集計について(複数シート対象）』（abcd）　ページの最後に飛ぶ

『複数ファイルの集計について(複数シート対象）』（abcd）

みなさま

お世話になります。
表題の件について、ご協力いただけると幸いです。

やりたいこととしては、
同一フォルダにある複数のエクセルファイルのI列の合計値を算出したいです。その際シート１だけではなく、シート３、シート７の合計値も算出したいです(シート１は各シート１の値を合計、シート３は各シート３の値を合計という感じです）。

ファイルのイメージとしては、
ファイルa(シート１）

 A    B     C        ...　I
2017　男　　正社員　     100
2017　女　　正社員　     200
2017　男　　契約　   　  200

ファイルb(シート１）

 A    B     C        ...　I
2016　男　　正社員　     50
2016　女　　正社員　     250
2016　男　　契約　   　  300

合計ファイルイメージ(シート1）

 A    B     C        ...　I
2016　男　　正社員　     150
2016　女　　正社員　     450
2016　男　　契約　   　  500

これと同じフォーマットでi列の値だけが違うシート３、シート７についても同じように集計したいと考えております。

ここまでだと、よくある集計マクロだと思うのですが、
今回対象のデータでは、集計元のファイル（ファイルaやファイルb）の行数が違う場合があります（下記、ファイルc）。
ファイルCはファイルa、ｂの行に何行か増えているデータで、a、ｂにある行はすべてCにも存在しております。

ファイルcのような場合は、
Cにだけある行も合計ファイルに追加して計上したいと考えております。
ちなみにID列を設けることは難しいですが、
f、g、h列の計３列の値で各行は判別可能です。

ファイル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 ＞

Sub main()

    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

mm様

さっそくのご返信ありがとうございます。
実は実際のシート名はシート１、３，７ではなく、別の名前になっております（ただ、左からの順番は1,3,7番目のファイルです）。

 Sheets(i).Name = "シート" & WorksheetFunction.Choose(i, 1, 3, 7) & "集計"
の部分をいじればよいのでしょうか？
また、ほかにも変更すべき箇所があれば、ご教示していただけないでしょうか？

当方、初心者で低レベルの質問をしてしまい、申し訳ございません。

以上、よろしくお願います。
（abcd） 2017/05/08(月) 17:07

新規Ｅｘｃｅｌファイルを作成し、マクロを標準モジュールに貼り付けて、実行してください。
（mm） 2017/05/08(月) 17:22

mm様

ご返信ありがとうございます。
当方、初心者でして、これからの応用のために、
お手数ですが、各セクションでどのような処理が行われているか教えていただけないでしょうか？

よろしくお願いします。

（abcd） 2017/05/09(火) 09:04

ところで、実行結果はいかがでしたか？
（mm） 2017/05/09(火) 09:33

肝心なところを返答しておりませんでした。申し訳ございません。

結果としては、うまくいっておりません。

実際のデータは、上記のデータと違って、
AからK列までデータが入力されており、集計したい値はI列、
データに空白がない列はJ列です。
この当たりが原因でしょうか？（上記コードではB列を参照している？？）
あと、勘違いしておりまして、集計したいシートは左から
１、３、９の３列でした。
申し訳ございません。

ちなみに集計データでは、
AからK列のすべてを表示させ、I列が各行の合計値になるというイメージです。

正しく、伝えておらず、申し訳ございませrんでした。

以上、よろしくお願いします。
（abcd） 2017/05/09(火) 15:18

当初お示しになったような前提でテストデータを試作していただき、実行していただきたいですね。
初心者の方には敷居が高いかと思いますので考え方のみ記します。
例えばファイルaのシート１が10行、ファイルbのシート１が15行、ファイルcのシート１が6行
であれば集約ファイルのシート１は31行になります。
集約ファイルのシート１を性別と職種（Ｂ列とＣ列）の組合わせ毎に、金額（Ｉ列）をＳＵＭＩＦＳ関数で集計します。
集計したら、ＳＵＭＩＦＳで算出された金額を、同じセルに値貼付け直します。（後工程で計算結果が変動しないように）
その後、性別と職種と金額で重複削除を実施します。
これをシート３とシート７についても同様に処理します。

（mm） 2017/05/09(火) 17:43

ご回答ありがとうございます。

勉強したいと思います。
ありがとうございました。

（abcd） 2017/05/09(火) 17:50

コメント返信：

[ 一覧(最新更新順) ]

[[20170508113150]] 『複数ファイルの集計について(複数シート対象）』（abcd） ページの最後に飛ぶ

[[20170508113150]]　『複数ファイルの集計について(複数シート対象）』（abcd）　ページの最後に飛ぶ