将公式应用于整列时保持单元格引用相同 [英] Keeping cell references the same when applying formula to entire column

问题描述

我这里有这个公式:

=IFERROR(IF(D4<=0,0+(VLOOKUP(A4,'Civil Maintenance'!A5:C46,3,FALSE)),_
C4+(VLOOKUP(A4,'Civil Maintenance'!A5:C46,3,FALSE))),"0")

众所周知，当您向下拖动它以将公式复制到列中的其他单元格时，它会向所有引用添加一个(D4 变为 D5 等)但是我想知道是否有办法阻止这种情况部分?我想保持查找搜索区域相同，以便 A5:C46 在列的所有公式中保持不变，但我仍然希望 D4、A4 ... 匹配它所在的行.

As you all know when you drag it down to copy the formula to other cells in the column, it adds one to all the references (D4 becomes D5 etc.) However i was wondering if there's a way to stop this for certain parts? I want to keep the lookup search area the same so that A5:C46 stays the same in all formulas of the column but i still want the D4, A4 ... to match the row it's in.

手动更改所有单元格引用非常耗时，我希望能节省一点时间，因为有很多行！

Manually changing all the cell references is very time consuming and I was hoping to save a bit of time as there is plenty of rows!

推荐答案

您所追求的是相对/绝对单元格引用.在 Excel 中，$ 符号锁定行或列.

What you are after is called relative/absolute cell referencing. In Excel, a $ sign locks the row or column.

=A1 向下拖动变为 =A2，向右拖动变为 =B1 这是相对单元格引用.

=A1 dragged down becomes =A2 and dragged to the right becomes =B1 This is a relative cell reference.

=$A1 向下拖动变为 =$A2 并向右拖动保持 =$A1，因为该列被 $ 符号锁定.列引用是绝对的，行引用是相对的.

=$A1 dragged down becomes =$A2 and dragged to the right stays =$A1 because the column is locked with the $ sign. The column reference is absolute and the row reference is relative.

=A$1 向下拖动保持 =A$1 因为行号被锁定，向右拖动它变成 =B$1 .列引用是相对的，行引用是绝对的.

=A$1 dragged down stays =A$1 because the row number is locked, dragged to the right it becomes =B$1 . The column reference is relative and the row reference is absolute.

而且，最后=$A$1 可以向任何方向拖动并保持=$A$1，因为行和列都用$ 符号锁定.这是绝对参考.

And, finally =$A$1 can be dragged in any direction and will stay =$A$1, because both row and column are locked with the $ sign. This is an absolute reference.

这样会更清楚吗?

如果没有数据的上下文和公式应该做什么，就很难判断公式中的哪些引用应该为行、列、两者或两者都锁定，但根据上面的信息，您应该能够解决这个问题.

Without the context of your data and what the formula is supposed to do, it's hard to tell which references in your formula should be locked for rows, columns, both, or neither, but with the info above you should be able to work that out.

这篇关于将公式应用于整列时保持单元格引用相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！