Excel SUM和INDIRECT動態範圍公式

Microsoft Excel有一些很酷的技巧，使用SUM和INDIRECT動態範圍公式只是兩種輕鬆操作數據的方法。

SUM - INDIRECT公式概述

在Excel公式中使用INDIRECT函數可以輕鬆更改公式中使用的單元格引用的範圍，而無需編輯公式本身。

INDIRECT可以和許多接受單元格引用的函數一起使用，例如OFFSET和SUM函數。

在後一種情況下，使用INDIRECT作為SUM函數的參數可以創建SUM函數然後累加的單元格引用的動態範圍。

INDIRECT通過間接通過中間位置引用單元格中的數據來完成此操作。

示例：SUM - INDIRECT用於總計動態值範圍的公式

此示例基於上圖中顯示的數據。

使用以下教程步驟創建的SUM - INDIRECT 公式為：

= SUM（INDIRECT（“D”＆E1＆“：D”＆E2））

在此公式中，嵌套的INDIRECT函數的參數包含對單元格E1和E2的引用。 這些單元格中的數字1和4與INDIRECT的其餘參數結合在一起形成單元格D1和D4。

結果，由SUM函數總計的數字範圍是包含在單元格D1至D4 範圍內的數據，即50。

通過改變位於小區E1和E2中的號碼; 但是，要匯總的範圍可以輕鬆更改。

本例首先使用上面的公式來計算單元格D1：D4中的數據，然後將總計範圍更改為D3：D6，而無需編輯單元格F1中的公式。

03年3月

輸入公式 - 選項

輸入公式的選項包括：

• 將上述公式直接輸入到單元格F1中並按下鍵盤上的Enter鍵
• 使用SUM函數的對話框作為參數輸入INDIRECT函數

Excel中的大多數函數都有一個對話框，它允許您在單獨的行中輸入每個函數的參數，而不必擔心語法 。

在這種情況下，SUM函數的對話框可用於在一定程度上簡化公式。 因為INDIRECT函數嵌套在SUM中，所以仍然必須手動輸入INDIRECT函數及其參數。

以下步驟使用SUM對話框輸入公式。

輸入教程數據

單元 數據 D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. 將以下數據輸入到單元格D1到E2中

開始SUM - INDIRECT公式 - 打開SUM函數對話框

1. 點擊單元格F1 - 這將顯示此示例的結果
2. 點擊功能區菜單的公式選項卡
3. 從功能區中選擇Math＆Trig打開功能下拉列表
4. 點擊列表中的SUM打開函數的對話框

02 03

輸入INDIRECT功能 - 點擊查看大圖

需要輸入INDIRECT公式作為SUM函數的參數。

在嵌套函數的情況下，Excel不允許打開第二個函數的對話框來輸入它的參數。

因此，INDIRECT函數必須在SUM函數對話框的Number1行中手動輸入。

1. 在對話框中，單擊Number1行
2. 輸入以下INDIRECT功能： INDIRECT（“D”＆E1＆“：D”＆E2）
3. 點擊OK完成該功能並關閉對話框
4. 數字50應出現在單元格F1中，因為這是位於單元格D1到D4中的數據的總和
5. 當您單擊單元格F1時，完整公式= SUM（INDIRECT（“D”＆E1＆“：D”＆E2））出現在工作表上方的公式欄中

打破INDIRECT功能

為了使用INDIRECT在列D中創建動態範圍，我們必須將INDIRECT函數參數中的字母D與單元格E1和E2中包含的數字結合起來。

這是通過以下來完成的：

• 和號（ ＆ ）用於將文本數據（在本例中為字母D）與單元格引用（E1和E2） 連接或連接在一起，
• 此外，與單元格引用連接的文本數據必須用雙引號（ “” ）包圍，
• 最後，範圍的結束點用冒號（：）分隔

因此，範圍的起始點由字符“D”＆E1定義 。

第二組字符： “：D”＆E2將冒號與終點相結合。 這是因為冒號是文本字符，因此必須包含在引號內。

中間的第三個連字符用於將兩部分連接成一個參數 ：

“D”＆E1 ＆ “：D”＆E2

03年03月

動態改變SUM函數的範圍

這個公式的全部要點是，可以很容易地改變SUM函數總計的範圍，而不必編輯函數的參數。

通過在公式中包含INDIRECT函數，更改單元格E1和E2中的數字將更改由SUM函數讀取的單元格範圍。

從上圖中可以看出，這也導致公式的答案位於單元格F1中，因為它總計了新的數據范圍。

1. 點擊單元格E1
2. 輸入數字3
3. 按下鍵盤上的Enter鍵
4. 點擊單元格E2
5. 輸入數字6
6. 按下鍵盤上的Enter鍵
7. 單元格F1中的答案應該更改為90 - 這是單元格D3至D6中包含的總數
8. 通過將單元格B1和B2的內容更改為1到6之間的任意數字來進一步測試公式

間接和#REF！ 錯誤值

裁判！ 如果INDIRECT函數的參數為錯誤值，將出現在單元格F1中：

• 不是有效的單元格引用
• 包含對不同工作簿的外部引用，並且該工作簿未打開
• 指的是工作表限制之外的單元格範圍（行1,048,576或列XFD）