錦子老師

範例1：創建開始部分固定的參照

在第一個範例中，C欄和E欄有相同的數值，使用SUM函數求得的和也是相同的。然而，所使用的公式稍微有點不同。

在儲存格B14中，公式為：「=SUM(B2:B13)」

在儲存格D14中，INDIRECT函數對D142儲存格的參照：「=SUM(INDIRECT(“D2”):D13)」。

如果在表格的第二列，點取「常用 > 插入 > 插入工作表列」指令，插入一列空白列，A2儲存格輸入「十二月」字串，B2儲存格輸入「100」數量，C2欄儲存格輸入「100」的數量，則C15儲存格中的公式無任何改變「=SUM(B3:B14)」，但D15儲存格中的公式發生了變化，根據被插入的列進行了調整，INDIRECT函數鎖定開始儲存格為D2，因此十二月的數量被自動包括在D欄的匯總儲存格D15中「=SUM(INDIRECT(“D2”):D14)」，如下圖所示。

範例2：創建對靜態命名範圍的參照

INDIRECT函數也可以創建對命名範圍的參照。在範例工作表8中，A2:A11範圍命名為「清單」，在B欄中也有一個基於該欄的數值的動態範圍，名稱為「清單總和」，範圍為「=SUM(OFFSET(工作表8!A2:A11,0,0,10,1))」。

通過在SUM函數中使用範圍名稱，每個儲存格都能夠計算總和，正如在D2儲存格公式：「=SUM(清單)」和D3儲存格公式：「=SUM(清單總和)」，如下圖所示。

代替在SUM公式中輸入之名稱，可以直接在工作表儲存格中輸入範圍名稱。例如，使用在儲存格C5中顯示的名稱「清單」，儲存格C5中的公式是：「=SUM(INDIRECT(D7))」。

不巧的是，INDIRECT函數不能夠解決對動態範圍的參照，因此當公式向下複製到儲存格C6中時，結果顯示#REF!錯誤。

示例3：從工作表、欄、列資訊創建參照

在INDIRECT函數中使用FALSE作為第二個參數容易創建基於欄名和列號的參照。

在範例中，創建R1C1樣式的參照，還包括了工作表名稱：「工作表8」。

D2儲存格公式為：「=INDIRECT("’"& A2 &"’!R"& B2 &"C" & C2,FALSE)」。

範例4：創建固定的數值組

在一些公式中，需要一組數值，我們想求A欄中最大的3個數值的平均值。在公式中，可以輸入數值，C1儲存格公式為：「=AVERAGE(LARGE(A2:A11,{1,2,3}))」。

如果需要更多的一組數值，那麼可能不想輸入這些數值。此時，可以使用ROW函數，在C2儲存格中輸入公式：「=AVERAGE(LARGE(A2:A11,ROW(1:3)))」。再按 CTRL + SHIFT + ENTER 鍵使其前後加上{}，變成陣列公式：「{=AVERAGE(LARGE(A2:A11,ROW(1:3)))}」。

除了上述方式外，還可以將ROW函數和INDIRECT函數聯合使用，C3儲存格輸入公式：「=AVERAGE(LARGE(A2:A11,ROW(INDIRECT("1:3"))))」，再按 CTRL + SHIFT + ENTER 鍵使其前後加上{}，變成陣列公式：「{=AVERAGE(LARGE(A2:A11,ROW(INDIRECT("1:3"))))}」。

上述3個公式的結果相同的，如下圖所示。

然而，如果在工作表第一列插入列，第二個公式傳回不正確的結果，因為列數被調整了。現在，顯示了第2、第3、第4個最大數值的平均值，而不是求最大的3個數值的平均值。

使用INDIRECT函數，第3個公式保持正確的行參照，並繼續顯示正確的結果。