发帖
雷达卡
来源: http://www.myonlinetraininghub.c ... -data-into-quarters
I had a question from Somnath this week asking what formula he could use to summarise his monthly data into quarters without having to edit the formula for each quarter.
Let’s imagine Somnath is selling ‘things’ and his data looks like this:
And in rows 16:19 he wants to summarise it into quarters by entering a formula in cell B16 and then copying it to the remainder of the table without so much as an F2, or double click to edit any formulas.
Summarise Monthly Data into QuartersWe can achieve this with a dynamic range using the OFFSET function.
If you haven’t come across OFFSET before I recommend you read up on it here with my treasure map example before continuing on.
Ok, let’s take a closer look at the formula in B16:
=SUM(OFFSET(B$2,3*ROWS(B$2:B2)-3,,3)) [只要把这个公式拷贝到excel中即可，相应修改B2]Remember, the OFFSET function returns a reference to a range that is a given number of rows and columns from a reference. The syntax is:
=OFFSET(reference,rows,I’ve crossed out the cols and width arguments as we don’t need them since our range is only 1 column wide.
In my formula the reference argument is the first cell in the table; B2.
The rows argument tells Excel the number of rows, up or down, that we need to move away from the reference to get to the first cell in our range.
Now remember we don’t want to edit this formula for each quarter so we’ve made it calculate the rows argument dynamically incorporating the ROWS function and the strategic use of absolute and relative references.
Now, the ROWS function simply returns the number of rows in a range. Since there is one row in the range B2:B2 our formula evaluates like this:
=SUM(OFFSET(B$2,3*1-3,,3)) which becomes:=SUM(OFFSET(B$2,0,,3))In English the above OFFSET formula reads: starting in cell B2 move zero rows down (i.e. start in cell B2) and make the range 3 cells high, which is:
=SUM(B$2:B4)Let’s look at Quarter 2’s formula in cell B17:
=SUM(OFFSET(B$2,3*ROWS(B$2:B3)-3,,3))Notice how the ROWS formula reference is now B2:B3.
So, the ROWS formula evaluates to 2 since there’s 2 rows in the range B2:B3:
=SUM(OFFSET(B$2,3*2-3,,3)) which becomes:=SUM(OFFSET(B$2,3,,3))In English the above OFFSET formula reads: starting in cell B2 move 3 rows down, that’s B5 and make the range 3 cells high. Which is:
=SUM(B5:B7)Horizontal DataGot your months going across columns instead of down rows like the table below?
No problem, you can use this formula which uses the COLUMNS function instead of ROWS:
=SUM(OFFSET($L2,,3*COLUMNS($L$2:L2)-3,1,3))The COLUMNS function returns the number of columns in a range.
summarise_mths_into_qtrs.xlsx
(27.3 KB)
京公网安备 11010802022788号
