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

      實(shí)際工作中,經(jīng)常需要在原有求和公式前再增加新行,如果增加一行或增加多行后,每行都輸入數(shù)據(jù),excel會智能的更改SUM公式的求和范圍,如果增加多行后,未按序輸入,SUM公式就會保持原來的求和范圍以至得出錯誤的結(jié)果，為避免這種現(xiàn)象,我們可以試用offset函數(shù)配合sum函數(shù)求解,做到以不變應(yīng)萬變。[A6=SUM(A2:OFFSET(A6,-1,0)) ]