EXCEL offset配合sum求和

由 ✎﹏๓₯㎕ζั͡❦﹏﹏♛ 创建, 最后一次修改 2015-11-04
.中国教程网zhangditony翻译,转载请保留此信息 .
 

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)) ]

20070715_e4877cfcc524beb0845bnfad5a1cfxw8[1].gif

以上内容是否对您有帮助:
硅心科技
二维码
建议反馈
二维码