Excel絕對參考(製作及使用方法)
在 Excel 的公式中引用單元格時,默認引用類型是相對引用。 當公式根據相關列和行複製到其他單元格時,這些引用將發生變化。 如果要保持引用不變,而不管公式複製到哪裡,就需要使用絕對引用。
視頻:絕對參考
什麼是絕對引用
絕對引用是 Excel 中的一種單元格引用。
與將公式複製到其他單元格時根據其相對位置發生變化的相對引用相比,無論公式被複製或移動到何處,絕對引用都將保持不變。
通過在公式中的列和行引用前添加美元符號 ($) 創建絕對引用。 例如,要為單元格 A1 創建絕對引用,您應該將其表示為 $A$1。
當您想要引用將被複製到多個單元格的公式中的固定單元格或區域,但又不想更改引用時,絕對引用很有用。
例如,區域A4:C7 包含產品的價格,你想根據單元格B2 中的稅率獲取每個產品的應納稅額。
如果在公式中使用相對引用,如“=B5*B2”,當您向下拖動自動填充句柄以應用該公式時,會返回一些錯誤的結果。 由於對單元格 B2 的引用將相對於公式中的單元格發生變化。 現在,C6單元格中的公式為“=B6*B3”,C7單元格中的公式為“=B7*B4”
但是,如果您使用公式“=B2*$B$5”對單元格 B2 的絕對引用將確保在使用自動填充句柄向下拖動公式時所有單元格的稅率保持不變,結果是正確的。
使用相對引用 | 使用絕對引用 | |
如何做絕對引用
在Excel中做絕對引用,需要在公式中的列引用和行引用前加上美元符號($)。 創建絕對引用有兩種方式:
手動將美元符號添加到單元格引用
在單元格中鍵入公式時,您可以在要使其成為絕對引用的列和行引用之前手動添加美元符號 ($)。
例如,如果要將單元格 A1 和 B1 中的數字相加並使它們均為絕對值,只需鍵入公式“=$A$1+$B$1”。 這將確保在將公式複製或移動到其他單元格時單元格引用保持不變。
或者,如果您想將單元格中現有公式中的引用更改為絕對引用,您可以選擇該單元格,然後轉到公式欄以添加美元符號 ($)。
使用快捷鍵 F4 將相對引用轉換為絕對引用
- 雙擊含有公式的單元格,進入編輯模式;
- 將光標放在要絕對化的單元格引用上;
- 媒體推薦 F4 鍵盤上的鍵切換引用類型,直到在列和行引用之前添加美元符號;
- 媒體推薦 Enter 鍵退出編輯模式並應用更改。
F4 鍵可以在相對引用、絕對引用和混合引用之間切換引用。
A1 → $A$1 → A$1 → $A1 → A1
如果要使公式中的所有引用都成為絕對引用,請在公式欄中選擇整個公式,然後按 F4 切換引用類型的鍵,直到在列和行引用之前添加美元符號。
A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1
在示例中使用絕對引用
這部分提供了 2 個示例來說明何時以及如何在 Excel 公式中使用絕對引用。
示例 1 計算佔總數的百分比
假設你有一個包含每個水果銷售額的數據區域(A3:B7),而單元格B8包含這些水果的總銷售額,現在你想計算每個水果銷售額佔總數的百分比。
計算總百分比的通用公式:
Percentage = Sale/Amount
使用公式中的相對引用來獲得第一個水果的百分比,如下所示:
=B4/B8
向下拖動自動填充手柄計算其他水果的百分比時,#DIV/0! 將返回錯誤。
因為當您拖動自動填充柄將公式複製到下面的單元格時,相對引用 B8 會根據它們的相對位置自動調整為其他單元格引用(B9、B10、B11)。 並且單元格 B9、B10 和 B11 為空(零),當除數為零時,公式返回錯誤。
在這種情況下,要修復錯誤,您需要在公式中將單元格引用 B8 設置為絕對值 ($B$8),以防止在您將公式移動或複製到任何位置時更改它。 現在公式更新為:
=B4/$B$8
然後向下拖動自動填充手柄以計算其他水果的百分比。
示例2 查找一個值並返回對應的匹配值
假設要查找D4:D5中的姓名列表,根據范圍(A4:B8)中提供的員工姓名和對應的年薪返回其對應的薪水。
查找的通用公式是:
=VLOOKUP(lookup_value, table_range, column_index, logical)
如果您在公式中使用相對引用來查找值並返回相應的匹配值,如下所示:
=VLOOKUP(D4,A4:B8,2,FALSE)
然後向下拖動自動填充手柄查找下面的值,會返回錯誤。
當您向下拖動填充柄將公式複製到下面的單元格時,公式中的引用會自動向下調整一行。 因此,對錶區域 A4:B8 的引用變為 A5:B9。 由於“Lisa:無法在 A5:B9 範圍內找到,因此公式返回錯誤。
為避免錯誤,請在公式中使用絕對引用 $A$4:$B$8 而不是相對引用 A4:B8:
=VLOOKUP(D4,$A$4:$B$8,2,FALSE)
然後向下拖動自動填充手柄以獲得 Lisa 的薪水。
2次點擊批量使用Kutools使單元格引用絕對化
無論您選擇手動鍵入還是使用 F4 快捷方式,您在 Excel 中一次只能更改一個公式。 如果要在 Excel 中使數百個公式中的單元格引用成為絕對值,則 轉換參照 的工具 Excel的Kutools 只需單擊 2 次即可幫助您處理工作。
選擇要使單元格引用成為絕對的公式單元格,單擊 庫工具 > 更多(外匯) > 轉換參照。 然後選擇 絕對 選項並單擊 Ok or 登記. 現在所選公式的所有單元格引用都已轉換為絕對引用。
-
轉換引用功能將更改公式中的所有單元格引用。
-
要使用轉換引用功能,您應該先安裝 Kutools for Excel。 單擊以下載並享受 30 天免費試用。
相對引用和混合引用
除了絕對引用之外,還有另外兩種引用類型:相對引用和混合引用。
相對參考 是 Excel 中的默認引用類型,在行和列引用之前沒有美元符號 ($)。 當具有相對引用的公式被複製或移動到其他單元格時,引用將根據它們的相對位置自動更改。
例如,當您在“=A1+1”等單元格中鍵入公式,然後向下拖動自動填充手柄將此公式填充到下一個單元格時,公式將自動更改為“=A2+1”。
混合參考 由絕對引用和相對引用組成。 換句話說,混合引用在復製或填充公式時使用美元符號 ($) 來固定行或列。
以乘法表為例,行和列列出了從 1 到 9 的數字,您將把這些數字相互相乘。
首先,您可以使用單元格 C3 中的公式“=B2*C3”將單元格 B1 中的 3 乘以第一列中的數字 (1)。 但是,當您向右拖動自動填充手柄以填充其他單元格時,您會注意到除第一個以外的所有結果都不正確。
這是因為當你向右複製公式時,行位置不會改變,但列位置會從B3變為C3,D3等。結果,右邊單元格(D3,E3,等)更改為“=C3*D2”、“=D3*E2”等,而實際上你希望它們是“=B3*D2”、“=B3*E2”等。
在這種情況下,您需要添加一個美元符號 ($) 來鎖定“B3”的列引用。 使用以下公式:
=$B3*C2
現在,當您向右拖動公式時,結果是正確的。
然後,您需要將單元格 C1 中的數字 2 乘以下面各行中的數字。
向下複製公式時,C2單元格的列位置不變,但行位置由C2變為C3、C4等,結果下面單元格的公式變為“=$B4C3”, "=$B5C4" 等會產生不正確的結果。
要解決此問題,請將“C2”更改為“C$2”,以防止在向下拖動自動填充句柄以填充公式時行引用發生變化。
=$B3*C$2
現在您可以向右或向下拖動自動填充手柄以獲得所有結果。
要記住的事情
-
單元格引用摘要
類別 例 總結 絕對參考 1澳元 將公式複製到其他單元格時永遠不會改變 相對參考 A1 將公式複製到其他單元格時,行和列引用都會根據相對位置發生變化 混合參考 $A1/A$1
當公式複製到其他單元格但列引用固定時,行引用發生變化/當公式複製到其他單元格但行引用固定時,列引用發生變化; -
通常,移動公式時,絕對引用永遠不會改變。 但是,當在工作表的頂部或左側添加或刪除行或列時,絕對引用將自動調整。 例如,在公式“=$A$1+1”中,當您在工作表頂部插入一行時,公式將自動更改為“=$A$2+1”。
-
F4 鍵可以在相對引用、絕對引用和混合引用之間切換。
最佳辦公生產力工具
🤖 | 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%,每天為您減少數百次鼠標點擊!