Excel公式需求：每3行偏移一次对连续12行数据求和

Hey there, let's figure out how to get that rolling 12-row sum with a 3-row shift in Excel. I've got two reliable methods for you—one that's straightforward, and another that's better for large datasets.

Method 1: Using the OFFSET Function #

The OFFSET function is tailor-made for dynamic range shifts like this. Let's say you want your first sum result in cell E4 (adjust this to your preferred starting cell). Use this formula:

=SUM(OFFSET($C$4, (ROW()-ROW($E$4))*3, 0, 12, 1))

Here's a breakdown of what each part does:

• $C$4: This is your fixed starting anchor (the first row of your initial sum range, C4:C15)
• (ROW()-ROW($E$4))*3: When you drag the formula down, this calculates how many rows to offset. For the first cell (E4), it's 0 offset (starts at C4). For E5, it adds 3 rows (starts at C7), and so on.
• 0: We don't shift columns—we stay in column C.
• 12: Specifies we want to sum 12 consecutive rows.
• 1: Tells Excel we're working with a single column (column C).

Once you enter the formula in E4, just drag it down as far as you need, and it'll automatically shift the sum range by 3 rows each time.

Method 2: Using INDEX (Non-Volatile Alternative) #

If you're working with a large dataset, OFFSET can slow down your workbook because it's a volatile function (it recalculates every time any cell changes). The INDEX function is non-volatile and more efficient here. Use this formula in your starting cell (again, we'll use E4 as an example):

=SUM(INDEX($C:$C, 4 + (ROW()-ROW($E$4))*3):INDEX($C:$C, 4 + (ROW()-ROW($E$4))*3 + 11))

Let's break this down:

• The first INDEX call: INDEX($C:$C, 4 + (ROW()-ROW($E$4))*3) returns the starting cell of each sum range. For E4, that's C4; for E5, that's C7, etc.
• The second INDEX call: INDEX($C:$C, 4 + (ROW()-ROW($E$4))*3 + 11) calculates the end cell. Since we need 12 rows, we add 11 to the starting row (4 + 11 = 15 for the first range, 7 + 11 = 18 for the next, and so on).
• The SUM function then adds up all values between these two calculated cells.

Quick Pro Tip #

• Don't forget to use absolute references ($) for your anchor points (like $C$4 or $E$4) so they don't shift unexpectedly when you drag the formula down.
• If you start your results in a different cell (say, F2), just replace $E$4 in the formulas with $F$2 to adjust the offset calculation correctly.

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