EXCEL offset配合sum求和

 

To sum cells a2:a5 using sum function. If you insert a row directly above the SUM formula, the new row may not be included in the SUM. It may continue to sum cells A2:A5, and ignore A6. To ensure that new rows are included in the total, you can use  the  OFFSET function.
Select cell A6.
Enter the following formula:
   =SUM(A2:OFFSET(A6,-1,0))
Press the Enter key to complete the entry.
Insert a row above row 6
Type a number in cell A6, and it will be included in the total in cell A7

      实际工作中,经常需要在原有求和公式前再增加新行,如果增加一行或增加多行后,每行都输入数据,excel会智能的更改SUM公式的求和范围,如果增加多行后,未按序输入,SUM公式就会保持原来的求和范围以至得出错误的结果，为避免这种现象,我们可以试用offset函数配合sum函数求解,做到以不变应万变。[A6=SUM(A2:OFFSET(A6,-1,0)) ]