防止单元格号在Excel中的公式中递增

I have a formula in Excel that needs to be run on several rows of a column based on the numbers in that row divided by one constant. When I copy that formula and apply it to every cell in the range, all of the cell numbers increment with the row, including the constant. So:

我在Excel中有一个公式，它需要在列的几行上运行，基于行中的数字除以一个常数。当我复制这个公式并把它应用到范围内的每个单元格时，所有的单元格数都随着行增加，包括常数。所以:

B1=127
C4='=IF(B4<>"",B4/B1,"")'

If I copy cell C4 and paste it down column C, the formula becomes

如果我复制cell C4，然后把它粘贴到C列，公式就变成了。

=IF(B5<>"",B5/B2,"")
=IF(B6<>"",B6/B3,"")
etc.

when what I need it to be is

当我需要它的时候

=IF(B5<>"",B5/B1,"")
=IF(B6<>"",B6/B1,"")
etc.

Is there a simple way to prevent the expression from incrementing?

是否有一种简单的方法可以阻止表达式递增?

3 个解决方案

#1

116

There is something called 'locked reference' in excel which you can use for this, and you use $ symbols to lock a range. For your example, you would use:

在excel中有一种叫做“锁定引用”的东西，你可以使用它，你可以使用$ symbols来锁定一个范围。对于您的示例，您将使用:

=IF(B4<>"",B4/B$1,"")

This locks the 1 in B1 so that when you copy it to rows below, 1 will remain the same.

它锁定B1中的1，这样当你将它复制到下面的行时，1将保持不变。

If you use $B$1, the range will not change when you copy it down a row or across a column.

如果您使用$B$1，当您将其复制到行或横列时，范围将不会改变。

#2

In Excel 2013 and resent versions, you can use F2 and F4 to speed things up when you want to toggle the lock.

在Excel 2013和最近的版本中，你可以使用F2和F4来在你想切换锁的时候加快速度。

About the keys:

关于钥匙:

F2 - With a cell selected, it places the cell in formula edit mode.
选定单元格后，将单元格置于公式编辑模式。
F4 - Toggles the cell reference lock (the $ signs).

F4 -切换单元格引用锁($符号)。
Example scenario with 'A4'.

用A4的示例场景。
- Pressing F4 will convert 'A4' into '$A$4'
- 按F4键，“A4”会变成“$A$4”
- Pressing F4 again converts '$A$4' into 'A$4'
- 再次按F4将$A$4转换为$4
- Pressing F4 again converts 'A$4' into '$A4'
- 再次按F4键将“A$4”转换为“$A4”
- Pressing F4 again converts '$A4' back to the original 'A4'
- 再次按下F4，将“$A4”转换回原来的“A4”

How To:

如何:

In Excel, select a cell with a formula and hit F2 to enter formula edit mode. You can also perform these next steps directly in the Formula bar. (Issue with F2 ? Double check that 'F Lock' is on)

在Excel中，选择一个带有公式的单元格，按F2进入公式编辑模式。您还可以在公式栏中直接执行这些步骤。(与F2问题?再次检查“F锁”是否打开)
- If the formula has one cell reference;
  - Hit F4 as needed and the single cell reference will toggle.
  - 按下F4按一下，单个单元格引用就会切换。
- 如果公式有一个单元格引用;按下F4按一下，单个单元格引用就会切换。
- If the forumla has more than one cell reference, hitting F4 (without highlighting anything) will toggle the last cell reference in the formula.
- 如果forumla有多个单元格引用，那么按F4(没有高亮显示任何内容)将切换公式中的最后一个单元格引用。
- If the formula has more than one cell reference and you want to change them all;
  - You can use your mouse to highlight the entire formula or you can use the following keyboard shortcuts;
  - 你可以用鼠标来突出整个公式，或者你可以使用下面的快捷键;
  - Hit End key (If needed. Cursor is at end by default)
  - 点击结束键(如果需要的话。默认情况下光标在末尾)
  - Hit Ctrl + Shift + Home keys to highlight the entire formula
  - 按Ctrl + Shift + Home键来突出显示整个公式
  - Hit F4 as needed
  - 根据需要达到F4
- 如果公式不止一个单元格，你想要改变它们;您可以使用鼠标高亮显示整个公式，也可以使用以下键盘快捷键;点击结束键(如果需要的话。按下Ctrl + Shift + Home键来突出显示整个公式按下F4
- If the formula has more than one cell reference and you only want to edit specific ones;
  - Highlight the specific values with your mouse or keyboard ( Shift and arrow keys) and then hit F4 as needed.
  - 用鼠标或键盘(Shift和箭头键)突出显示特定的值，然后按下F4。
- 如果公式有多个单元格引用，而您只想编辑特定的单元格;用鼠标或键盘(Shift和箭头键)突出显示特定的值，然后按下F4。

Notes:

注:

These notes are based on my observations while I was looking into this for one of my own projects.
这些笔记是基于我的观察，当时我正在研究我自己的一个项目。
It only works on one cell formula at a time.
它一次只能作用于一个细胞配方。
Hitting F4 without selecting anything will update the locking on the last cell reference in the formula.
按F4而不选择任何内容将更新公式中最后一个单元格引用的锁定。
Hitting F4 when you have mixed locking in the formula will convert everything to the same thing. Example two different cell references like '$A4' and 'A$4' will both become 'A4'. This is nice because it can prevent a lot of second guessing and cleanup.
当公式中有混合锁时按F4会将所有东西转换成相同的东西。例如，两个不同的单元格引用，如“$A4”和“$4”都将变成“A4”。这很好，因为它可以防止很多猜测和清理。
Ctrl+A does not work in the formula editor but you can hit the End key and then Ctrl + Shift + Home to highlight the entire formula. Hitting Home and then Ctrl + Shift + End.
在公式编辑器中，Ctrl+A不起作用，但是你可以点击结束键，然后按Ctrl+ Shift + Home来突出显示整个公式。然后按Ctrl + Shift +结束。
OS and Hardware manufactures have many different keyboard bindings for the Function (F Lock) keys so F2 and F4 may do different things. As an example, some users may have to hold down you 'F Lock' key on some laptops.
操作系统和硬件制造商有许多不同的功能键绑定，所以F2和F4可以做不同的事情。例如，一些用户可能不得不在一些笔记本电脑上按住“F锁定”键。
'DrStrangepork' commented about F4 actually closes Excel which can be true but it depends on what you last selected. Excel changes the behavior of F4 depending on the current state of Excel. If you have the cell selected and are in formula edit mode (F2), F4 will toggle cell reference locking as Alexandre had originally suggested. While playing with this, I've had F4 do at least 5 different things. I view F4 in Excel as an all purpose function key that behaves something like this; "As an Excel user, given my last action, automate or repeat logical next step for me".
“DrStrangepork”评论说F4实际上关闭了Excel，这是真的，但这取决于你最后选择了什么。Excel根据Excel的当前状态改变F4的行为。如果选择了单元格并处于公式编辑模式(F2)， F4将按Alexandre最初建议的那样切换单元格引用锁定。在玩这个游戏的时候，我让F4做了至少5件不同的事情。我将Excel中的F4视为一个万能功能键，它的行为如下;“作为一个Excel用户，如果我做了最后一个动作，请为我自动或重复逻辑的下一步”。

#3

Highlight "B1" and press F4. This will lock the cell.

突出“B1”并按F4。这将锁定单元格。

Now you can drag it around and it will not change. The principle is simple. It adds a dollar sign before both coordinates. A dollar sign in front of a coordinate will lock it when you copy the formula around. You can have partially locked coordinates and fully locked coordinates.

现在你可以拖动它，它不会改变。原理很简单。它在两个坐标之前都加了一个美元符号。当你复制公式时，坐标前面的一个美元符号将会锁定它。你可以有部分上锁的坐标和完全锁定的坐标。

#1

116