VBA从多工作簿导入数据：适配Excel2007+的场景化最优方案问询

Alright, let’s tackle this with two common real-world weekly update scenarios that fit your constraints—no Power Query required, works seamlessly in Excel 2007+, and zero manual formula adjustments after updating your data. Here’s how to structure it:

This is typical when you get separate files/sheets for each week’s data. The key is standardization and dynamic cross-sheet referencing:

• Lock in a strict sheet structure rule: Create a hidden "Template" worksheet with your fixed header row (e.g., A1:E1) and ensure every new weekly sheet is a copy of this template. No merged cells, no shifting columns—keep the layout identical across all weeks.
• Use dynamic named ranges + indirect referencing for aggregation:
  1. First, define a helper cell (say, Settings!A1) to track the total number of weekly sheets you’ve added (e.g., enter 24 for Week24). You only need to update this number once per week, no formula changes needed.
  2. For summary formulas, use SUMPRODUCT with INDIRECT to pull data across all matching sheets. For example, to sum sales from Column C where Column A matches "Product X":

     =SUMPRODUCT(SUMIF(INDIRECT("Week"&ROW(INDIRECT("1:"&Settings!A1))&"!A:A"),"Product X",INDIRECT("Week"&ROW(INDIRECT("1:"&Settings!A1))&"!C:C")))
     

  3. If you want to avoid the helper cell, you can use a formula to count valid weekly sheets (note: this requires enabling macros temporarily for GET.WORKBOOK, but no ongoing VBA):

     =SUMPRODUCT(SUMIF(INDIRECT(LEFT(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))-1)&"Week"&ROW(INDIRECT("1:"&SUMPRODUCT(--(LEFT(GET.WORKBOOK(1),5)="Week"))))&"!A:A"),"Product X",INDIRECT(LEFT(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))-1)&"Week"&ROW(INDIRECT("1:"&SUMPRODUCT(--(LEFT(GET.WORKBOOK(1),5)="Week"))))&"!C:C")))
     

• Bonus: Dynamic pivot table: Create a pivot table using a named range that aggregates all weekly sheets. Refresh the pivot table after adding a new week’s sheet, and it’ll automatically include the new data.

This is simpler when you paste new rows at the bottom of an existing dataset. The focus is a dynamic dataset that grows automatically:

• Define a dynamic named range for your raw data:
  1. Go to the Formulas tab → Name Manager → New.
  2. Name it DynamicRawData, and set the "Refers to" field to this formula (adjust RawData!$A$1 to your header cell):

     =OFFSET(RawData!$A$1,0,0,MAX(ROW(RawData!$A:$A)*(RawData!$A:$A<>""))-ROW(RawData!$A$1)+1,COUNTA(RawData!$1:$1))
     

     This formula accounts for empty rows in your data (unlike COUNTA alone) and automatically expands as you add new rows.
• Build all summary tools around this named range:
  • For formulas: Use SUMIFS, COUNTIFS, etc., referencing the named range’s columns. For example:

    =SUMIFS(INDEX(DynamicRawData,,3),INDEX(DynamicRawData,,1),">="&Settings!B1,INDEX(DynamicRawData,,1),"<="&Settings!B2)
    

    (Here, INDEX(DynamicRawData,,3) refers to the 3rd column in your raw data.)
  • For pivot tables: Set the pivot table’s data source to DynamicRawData. After appending new data, just right-click the pivot table → Refresh—no need to redefine the source range.

Critical Best Practices #

• Never alter the header structure: If you add a new column later, add it to the end of all sheets/ranges to avoid breaking existing formulas.
• Test for edge cases: Make sure your dynamic ranges handle empty cells, blank rows, and new columns correctly before relying on them weekly.
• Document your setup: Leave a hidden "Instructions" sheet noting the named ranges, helper cells, and sheet rules for future reference.

内容的提问来源于stack exchange，提问作者user3673417