跳到主要內容

如何在Excel中保持公式單元格引用恆定？

在本教程中，我們將示範如何將公式中的儲存格引用變更為常數（絕對）引用，以防止在將公式複製到其他地方時它發生變更。

下面的螢幕截圖顯示了計算項目總成本的公式中兩種不同類型的儲存格參考：
  • 左側的公式錯誤地將折扣率參考向下移動到每行。複製時從 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)、混合相對列和絕對行 (A $ 1），然後回到相對的（A1).

點擊幾下即可使所有儲存格參考在範圍內絕對化

上述方法有助於對單元格進行一一處理。如果您想一次對多個儲存格應用程式絕對引用，這裡強烈推薦 轉換參照 的特點 Excel的Kutools。借助此功能，您只需單擊幾下即可輕鬆將一個單元格區域或多個單元格區域中的所有單元格引用設定為絕對引用。

後 安裝 Kutools for Excel，轉到 庫工具 標籤，選擇 更多 > 轉換參照 打開 轉換公式參考 對話框。那你需要：

  1. 選擇包含要使所有儲存格參考保持不變的公式的一個或多個範圍。
  2. 選擇 絕對 選項，然後單擊 OK 開始轉換。

然後，所選範圍內公式的所有儲存格參考都會立即變更為常數參考。

備註：要使用此功能，您應該安裝 Excel的Kutools 首先，請 點擊下載並立即免費試用 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選項卡為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