跳到主要内容

如何在Excel中保持公式单元格引用恒定？

添加一名作者 最后修改时间：2024-04-12

在本教程中，我们将演示如何将公式中的单元格引用更改为常量（绝对）引用，以防止在将公式复制到其他地方时它发生更改。

下面的屏幕截图显示了计算项目总成本的公式中两种不同类型的单元格引用：
  • 左侧的公式错误地将折扣率参考向下移动到每行。复制时从 D3 至 D4 和 D5，它引用了 F4 和 F5 而不是固定折扣率 F3，导致计算错误 D4 和 D5.
  • 在屏幕截图的右侧， $ F $ 3 是一个常数（绝对）参考。它将折扣率与单元格联系起来 F3，无论公式复制到工作簿中的哪个位置。因此，将公式复制到列中可以使所有商品的折扣率保持一致。

使用F4键保持公式单元格引用恒定

要在公式中保持恒定的单元格引用，只需按 F4 键在列字母和行号之前添加 $ 符号即可。以下是具体操作方法。

  1. 单击包含公式的单元格。
  2. 在编辑栏中，将光标置于要使其恒定的单元格引用内。这里我选择参考 F3.
  3. 按 F4 键切换引用类型，直到到达绝对引用，这会添加一个 美元符号 ($) 位于列字母和行号之前。这里 F3 将更改为 $ F $ 3。然后按 Enter 确认更改。

现在，无论公式复制到工作簿中的哪个位置，它都会将折扣率保持链接到单元格 F3。

备注：每按一次 F4 通过不同参考状态的关键循环：绝对（1澳元)、混合绝对列和相对行 ($ A1)、混合相对列和绝对行 (1 澳元），然后回到相对的（A1).

单击几下即可使所有单元格引用在范围内绝对化

上述方法有助于对单元格进行一一处理。如果您想一次对多个单元格应用绝对引用，这里强烈推荐 转换参照 的特点 Kutools for Excel。借助此功能，您只需单击几下即可轻松将一个单元格区域或多个单元格区域中的所有单元格引用设置为绝对引用。

后 安装 Kutools for Excel，转到 库工具 标签，选择 更多 > 转换参照 打开 转换公式参考 对话框。那么你需要：

  1. 选择包含要使所有单元格引用保持不变的公式的一个或多个范围。
  2. 选择 绝对 选项，然后单击 OK 开始转换。

然后，所选范围内公式的所有单元格引用都会立即更改为常量引用。

备注：要使用此功能，您应该安装 Kutools for Excel 首先，请 点击下载并立即免费试用 30 天.

演示：使用 Kutools for Excel 保持公式单元格引用不变

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

课程描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (12)
Rated 5 out of 5 · 1 ratings
SM
SM
This comment was minimized by the moderator on the site
Thank you for this post, it was very helpful
Reply
0
0
Rated 5 out of 5
Muresan ALIN
Muresan ALIN
This comment was minimized by the moderator on the site
Not working if you have index match or vlooup in the formula
Reply
0
0
Austin314
Austin314
This comment was minimized by the moderator on the site
That Kutools "Convert Formula References" is perfect, exactly what I need. Unfortunately I can't install that, but it's nice to know someone else needed it and made an extension for it. At least it gives me some closure knowing I'll never find it in base Excel haha. Thanks.
Reply
Report
0
0
crystal
crystal     Austin314
This comment was minimized by the moderator on the site
Hi Austin,Did you get any error prompt when installing Kutools for Excel? 
Reply
Report
0
0
Austin314
Austin314     crystal
This comment was minimized by the moderator on the site
Hi Crystal! Oh, thanks for asking. I was on my Work PC so I knew I would be unable to install anything like this without going through the process of getting it approved with IT, presenting a solid reason to the team as to why we need to pay for additional software, etc. It would be a major headache, and I'm certain I would lose the argument. (Make no mistake, I'm all for it) But, unfortunately I settled for the smaller headache of doing what Kutools' "Convert Formula References" process would do, to a few hundred cells by hand. When I've made it to the big leagues, and I'm the boss, I'll ensure that my whole team is outfitted with Kutools. It's an awesome addition, for sure. 
Reply
Report
0
0
crystal
crystal     Austin314
This comment was minimized by the moderator on the site
Hi Austin314,Thank you very much for your feedback and I look forward to the day when your whole team is outfitted with Kutools.^_^
Reply
Report
0
0
Bharat
Bharat
This comment was minimized by the moderator on the site
Superb.....
Reply
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
Does not work. If I insert a row above the cell, the formula "tracks" the cell to its new location. This is not what your title implies. I need a formula that reads = B1 and if data gets inserted into row B:B then the formula reads = B1 and returns the new value. Absolute referencing does not do this.
Reply
0
0
Martin
Martin     Andrew
This comment was minimized by the moderator on the site
Andrew, that is exactly what I want too..... how can we hold the cell reference Completely constant in these circumstances?
Reply
0
0
Austin314
Austin314     Martin
This comment was minimized by the moderator on the site
Andrew, Martin! .....I came here for something else but I think I've got what you need.  You can also reference cells using the INDIRECT() function. For your case, if you go to cell C1 (or wherever) and set the equation in C1 to be =INDIRECT("B1"), then it will always return whatever is in B1, no matter what happens to B1. So then, if you insert a column of data into column B, your equation would move over to cell D1, and would continue to pull data from the brand new value in B1. The older referenced value is now in C1, with the column of data before. You can also perform mathematic operations on it as you would any other cell reference. Happy Spreadsheets :)
Reply
Report
0
0
Angelo Antonino
Angelo Antonino     Martin
This comment was minimized by the moderator on the site
Use the INDEX() function and for the Array use a range of columns to cover all your data (assuming no column inserts/deletes will happen)
Reply
0
0
Gavin Parker Rose
Gavin Parker Rose
This comment was minimized by the moderator on the site
Financial ratios are one of the most common tools of managerial decision making. A ratio is a comparison of one number to another—mathematically, a simple division problem. Financial ratios involve the comparison of various figures from the financial statements in order to gain information about a company's performance. It is the interpretation, rather than the calculation, that makes financial ratios a useful tool for business managers. Ratios may serve as indicators, clues, or red flags regarding noteworthy relationships between variables used to measure the firm's performance in terms of profitability, asset utilization, liquidity, leverage, or market valuation.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations