Сумиране на едни и същи клетки от различни екселски файлове

1 одобрение 0 неодобрения
попитан 2017 ноември 22 в VBA от GG начинаещ (13 точки)
Трябва да сумирам данните от 200 еднакви екселски файла с различни имена - трите имена на клиенти. Справям се по допотопен начин, като ги преименувам с цифрите от 1 до 50 в 4 различни папки, с цел да намаля дължината на формулата за сумиране в една клетка. След това копирам формулата в останалите клетки. Таблиците във файловете са големи и разположени в 11 листа. Известните ми възможности, които предлага Ексел, изискват предварително отваряне на файловете, което също е проблем.

Да ви се намира формула или идея за по-оптимизиран начин за сумиране на клетки от много файлове?

1 отговор

2 одобрения 0 неодобрения
отговорени 2017 ноември 23 от vbafreak експерт (203 точки)

Здравей,

Какво точно искаш да сумираш?

Тъй като имаш твърде много файлове, най-лесния начин да ги сумираш е чрез макро. Следният код преминава през всички екселски файлове от определена папка и сумира стойностите от клетки А1:Е50, sheet1:

Sub Sum_Workbooks()
    'path to open the Excel files
    Const FOLDER As String = "C:\Users\username\Desktop\Excel\"
    Const cStrWSName As String = "Sheet1"
    Const cStrRangeAddress As String = "A1:E50"

    Dim rngTarget As Range
    Dim wbSource As Workbook

    Dim fileName As String

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set rngTarget = ThisWorkbook.Worksheets(cStrWSName).Range(cStrRangeAddress)

    fileName = Dir(FOLDER, vbDirectory)
    Do While Len(fileName) > 0
    Application.DisplayAlerts = False

        If Right$(fileName, 4) = "xlsx" Or Right$(fileName, 3) = "xls" Then

            Set wbSource = Workbooks.Open(FOLDER & fileName)

            wbSource.Worksheets(cStrWSName).Range(cStrRangeAddress).Copy
            rngTarget.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd

            wbSource.Close
        End If
        fileName = Dir
    Loop

ProgramExit:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub

End Sub

За да работи, пействаш този код във VBA едитора на MS Excel (alt + F11, insert new module) и запаметяваш файла в отделна папка от другите файлове като macro-enabled file (file ->save as -> xlsm). Трябва също така да въведеш пътят към папката с екселските файлове в кода. Успех и не забравяй да отбележиш този отговор, ако ти върши работа! 

4 коментари

коментиран 2017 ноември 24 от GG начинаещ (13 точки)
Здравейте,
За съжаление не можах да се справя. Изпълних точно указанията Ви, но при стартиране на кода се появява следния текст: Subscript out of range
Може ли да Ви Изпратя папка с няколко файла за сумиране, със които правих опити, ако имате време и интерес да погледнете къде допускам грешка.
С Уважение,
GG
коментиран 2017 ноември 25 от vbafreak експерт (203 точки)
Трябва също да смениш името на Sheet1, ако е различен. Ако искаш качи папката в dropbox или google drive и дай линк да видя.
коментиран 2017 ноември 28 от GG начинаещ (13 точки)
Дано не повтарям качването на този линк, но имах проблем с компютъра.
https://www.dropbox.com/sh/cd07gcb7rptp6u1/AADTq0CvR5IXJPfmWcsVt8lHa?dl=0
коментиран 2017 ноември 30 от vbafreak експерт (203 точки)
редактиран 2017 ноември 30 от vbafreak
Не си сложил макрото на правилното място. Трябва да е в папка modules. Макрото се слага само в macro-enabled файла. Също така трябва всички първи шийтове да ги преименуваш на ''Sheet1'', вкл. и sheet-a на макрото. На български, както си го написал "Отчет 1", не мисля, че ще работи. Тук има повече инфо как се инсталира макро: https://bgexcel.info/?qa=312/14-%D0%B3%D0%BE%D1%82%D0%BE%D0%B2%D0%B8-%D0%BC%D0%B0%D0%BA%D1%80%D0%BE%D1%81a-%D0%B7%D0%B0-%D0%B5%D0%BA%D1%81%D0%B5%D0%BB

Колкото до това как да направиш кода да минава през другите sheet-ове, виж тази тема:
https://bgexcel.info/?qa=321/%D0%B7%D0%B0%D0%BC%D1%8F%D0%BD%D0%B0-%D0%BD%D0%B0-%D1%85%D0%B8%D0%BF%D0%B5%D1%80%D0%B2%D1%80%D1%8A%D0%B7%D0%BA%D0%B8-%D0%B2-%D0%BC%D0%BD%D0%BE%D0%B6%D0%B5%D1%81%D1%82%D0%B2%D0%BE-%D1%84%D0%B0%D0%B9%D0%BB%D0%BE%D0%B2%D0%B5

Ако ти е много трудна тази материя, те съветвам да продължиш да работиш с формули. С тази формула можеш да събираш данни от два различни файла в трети:
=SUM('C:\Users\Username\Desktop\[Book1.xlsx]Sheet1'!$C$12:$C$20,'C:\Users\Username\Desktop\[Book2.xlsx]Sheet1'!$A$1:$A$10)

 Виж тази тема за повече информация: https://www.ozgrid.com/forum/forum/help-forums/excel-general/26824-sum-across-multiple-workbooks
Добре дошли в българския форум за Ексел и Майкрософт офис! Тук можете да питате хора със знания и опит, както и да споделите знанията и опита си с другите.
...