Excel如何按固定区间批量自动求和？适配3万行大数据场景

Absolutely! You’ve got several efficient ways to skip the tedious manual formula typing and automate this 24-row grouping sum—perfect for your 30,000-row dataset. Let’s walk through the most practical methods:

Method 1: Use INDEX (Stable, Non-Volatile Formula) #

This is my go-to for large datasets because it’s non-volatile (won’t slow down Excel with constant unnecessary recalculations).

1. Pick a blank column (e.g., Column D) to hold your sums. In cell D2, enter this formula:

   =SUM(INDEX($C:$C, (ROW()-2)*24+2):INDEX($C:$C, (ROW()-2)*24+25))
   

2. Drag the fill handle (the small square at the bottom-right of D2) down as far as needed to cover all your data groups.

How it works: #

• ROW()-2 calculates the offset from your starting row (since we start at D2, the first group uses 0 as the multiplier).
• (ROW()-2)*24+2 gives the starting row of each 24-row block (C2, C26, C50, etc.).
• (ROW()-2)*24+25 gives the ending row (C25, C49, C73, etc.).

Method 2: Use OFFSET (Simpler, Volatile Formula) #

If you prefer a shorter formula, OFFSET works too—just note it’s volatile (recalculates whenever any cell in Excel changes, which might slow things down slightly for 30k rows).

1. In cell D2, enter:

   =SUM(OFFSET($C$2, (ROW()-2)*24, 0, 24, 1))
   

2. Drag the fill handle down to apply the formula to all groups.

How it works: #

• OFFSET($C$2, (ROW()-2)*24, 0, 24, 1) creates a 24-row tall, 1-column wide range starting at $C$2 offset by (ROW()-2)*24 rows.

Method 3: Power Query (Best for Long-Term Maintenance) #

If you need to refresh or update the dataset later, Power Query is ideal—it handles large data smoothly and lets you re-run the sum with one click.

1. Select your entire data range (including the header row). Go to the Data tab and click From Table/Range (check "My table has headers" if prompted).
2. In the Power Query Editor:
   • Go to Add Column > Index Column > From 0 (this adds a column starting at 0).
   • Add a custom column: Click Add Column > Custom Column, name it Group, and enter this formula:

     = Number.IntegerDivide([Index], 24)
     

   • Select the Group column and your Wind_MWh column. Go to Transform > Group By:
     • Set "Group by" to Group, "New column name" to something like Total_Wind_MWh, "Operation" to Sum, and "Column" to Wind_MWh.
3. Click Close & Load—your grouped sums will appear in a new worksheet. To update later, just right-click the table and select Refresh.

Bonus Tip: #

If your last group has fewer than 24 rows, all these methods will automatically sum whatever rows are left—no extra adjustments needed.

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