excel中如何保持一个变量不变而另一个变量随行变化
假设我有一个单元格 A1，我希望在计算中保持不变。例如，我想计算这样的值：
=(B1+4)/(A1)
如何做到这一点，以便如果我拖动该单元格在许多行中的单元格之间进行计算，则只有 B1 值发生变化，而 A1 始终引用该单元格，而不是继续到 A2、A3 等？
Lets say I have one cell A1, which I want to keep constant in a calculation. For example, I want to calculate a value like this:
=(B1+4)/(A1)
How do I make it so that if I drag that cell to make a calculation across cells in many rows, only the B1 value changes, while A1 always references that cell, instead of going to A2, A3, etc.?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
使用这种形式：
$告诉 Excel 在将公式粘贴到新单元格时不要调整该地址。由于您是跨行拖动，因此实际上只需要冻结行部分：
键盘快捷键
评论者指出，您可以使用以下键盘快捷键切换当前选定单元格中公式的相对寻址：
Use this form:
The
$tells excel not to adjust that address while pasting the formula into new cells.Since you are dragging across rows, you really only need to freeze the row part:
Keyboard Shortcuts
Commenters helpfully pointed out that you can toggle relative addressing for a formula in the currently selected cells with these keyboard shortcuts:
有两种类型的单元格引用，充分理解它们确实很有价值。
一种是相对引用，这是您只需键入单元格：
A5时得到的内容。当您将公式粘贴或填充到其他单元格时，将调整此参考。另一种是绝对引用，您可以通过在单元格引用中添加美元符号来获得它：
$A$5。粘贴或填充时，此单元格引用不会更改。一个很酷但很少使用的功能是单个单元格引用中的行和列可以是独立的：
$A5和A$5。这对于从单个公式生成乘法表之类的东西非常有用。There are two kinds of cell reference, and it's really valuable to understand them well.
One is relative reference, which is what you get when you just type the cell:
A5. This reference will be adjusted when you paste or fill the formula into other cells.The other is absolute reference, and you get this by adding dollar signs to the cell reference:
$A$5. This cell reference will not change when pasted or filled.A cool but rarely used feature is that row and column within a single cell reference may be independent:
$A5andA$5. This comes in handy for producing things like multiplication tables from a single formula.为了使公式更具可读性，您可以为单元格 A0 指定一个名称，然后在公式中使用该名称。
定义名称的最简单方法是突出显示单元格或区域，然后单击公式栏中的“名称”框。
然后，如果您将 A0 命名为“Rate”，您可以像这样使用该名称：
看，更容易阅读。
如果您想查找费率，请单击 F5，它将出现在“转到”列表中。
To make your formula more readable, you could assign a Name to cell A0, and then use that name in the formula.
The easiest way to define a Name is to highlight the cell or range, then click on the Name box in the formula bar.
Then, if you named A0 "Rate" you can use that name like this:
See, much easier to read.
If you want to find Rate, click F5 and it appears in the GoTo list.
您将其设置为
=(B0+4)/($A$0)您还可以使用
Sheet1!$a$0浏览工作表You put it as
=(B0+4)/($A$0)You can also go across WorkSheets with
Sheet1!$a$0$ 表示保持相同（输入 A4 后按几次 F4 可快速翻阅组合！）
$ means keep same (press a few times F4 after typing A4 to flip through combos quick!)
在行值前面放置一个
$以保持恒定对我来说效果很好。例如Placing a
$in front of the row value to keep constant worked well for me. e.g.是的。只需将 $ 符号放在所需的常数单元前面即可。
如果您只想连续更改数字 6 并保持不变，则类似于 $A6；如果您根本不希望该引用中的任何内容发生更改，则类似于 $A$6。
示例：单元格 A5 包含我的汇率。在 B1 中你输入 (= C1 * $A$1)。当您填充 B1 到 B 时......A5 中的值保持不变，而 C1 中的值连续增加。
我到目前为止还不太擅长老师，但我希望这会有所帮助！！！！眨眼
Yeah. Just put the $ sign in front of your desired constant cell.
Like $A6 if you wish to just change the number 6 serially and keep a constant, or $A$6 if you do not want anything from that reference to change at all.
Example: Cell A5 contains my exchange rate. In B1 you put say ( = C1 * $A$1). when you fill B1 through B....... the value in A5 remains constant and the value in C1 increases serially.
I am by far not be good at teacher, but I hope this helps!!!! Wink wink
对于未来的访问者 - 使用此范围：
($A$1:$A$10)For future visitors - use this for range:
($A$1:$A$10)