防止单元格数字在Excel中的公式中递增 [英] Prevent cell numbers from incrementing in a formula in Excel
问题描述
B1 = 127
C4 ='= IF(B4',B4 / B1, )'
如果我复制单元格C4并将其粘贴到列C中, p>
= IF(B5,B5 / B2,)
= IF(B6 - ,B6 / B3,)
等
当我需要它(b5 / B1,)
= IF (B6,B6 / B1,)
等
有没有一个简单的方法来防止表达式递增?
在excel中有一些叫做'locked reference'的东西,你可以用于此,您使用 $
符号锁定范围。例如,您将使用:
= IF(B4,B4 / B $ 1,)
这将锁定<$ c中的 1
$ c> B1 ,以便将其复制到下面的行时, 1
将保持不变。
如果您使用 $ B $ 1
,则当您将其复制到一列或一列时,范围不会更改。
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:
B1=127
C4='=IF(B4<>"",B4/B1,"")'
If I copy cell C4 and paste it down column C, the formula becomes
=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?
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:
=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.
If you use $B$1
, the range will not change when you copy it down a row or across a column.
这篇关于防止单元格数字在Excel中的公式中递增的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!