Using relative positions in Excel formulas

问题:

How do I create a formula that isn't made invalid when I delete a row.

For example in cell F12 I have the formula: =F11+D12-E12

This basically says take the value from the cell above then add the value of the cell 2 to the left and subtract the value in the cell directly to the left.

However, because I'm using actual cell addresses, as soon as I delete a row, all the rows below become invalid.

How do i express the formula by relative position (ie = "1 above" + "2 to left" - "1 to left")

Thanks.

回答1:

You can use either

• =OFFSET(F12,-1,0)+OFFSET(F12,0,-2)-OFFSET(F12,0,-1), or
• =INDIRECT("F11",true)+INDIRECT("D12",true)-INDIRECT("E12",true)
• =INDIRECT("R11C6",false)+INDIRECT("R12C4",false)-INDIRECT("R12C5",false)
• =INDIRECT("R[-1]",false)+INDIRECT("C[-2]",false)-INDIRECT("C[-1]",false)

Both functions also allow to specify ranges, just use whatever has your personal preference (see Excel Help)…

回答2:

• You can switch e.g. to the R1C1 reference style (excel options)
• Use OFFSET function (e.g. =OFFSET(F12;-1;0) for above)

回答3:

Reading between the lines... is your actual problem creating a running total from additions and deductions columns?

You can sum each column starting with an absolute reference and ending with a relative reference

Assuming row 11 is your first, put =SUM(D$11:D12)-SUM(E$11:E12) in F12.

As you copy this cell down the absolute reference remains the same where the relative reference automatically updates extending the range. You can even copy the formula up one line and it will work on the first line too.

Deleting and inserting lines doesn't break the formula, though any extra cells would be included in the SUM().