內容
恭喜! 你堅持到了馬拉鬆的最後一天 30 天 30 個 Excel 函數. 這是一段漫長而有趣的旅程,在此期間您學到了許多有關 Excel 函數的有用知識。
馬拉松第30天,我們將致力於功能的研究 間接 (INDIRECT),它返回由文本字符串指定的鏈接。 使用此功能,您可以創建相關的下拉列表。 例如,當從下拉列表中選擇一個國家時,確定哪些選項將出現在城市下拉列表中。
那麼,讓我們仔細看看函數的理論部分 間接 (間接)並探索其應用的實際例子。 如果您有其他信息或示例,請在評論中分享。
功能 30:間接
功能 間接 (INDIRECT) 返回由文本字符串指定的鏈接。
如何使用 INDIRECT 功能?
自功能 間接 (INDIRECT) 返回由文本字符串給出的鏈接,您可以使用它來:
- 創建一個不移位的初始鏈接。
- 創建對靜態命名範圍的引用。
- 使用工作表、行和列信息創建鏈接。
- 創建一個不移位的數字數組。
語法間接(間接)
功能 間接 (INDIRECT) 具有以下語法:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- 參考文本 (link_to_cell) 是鏈接的文本。
- a1 – 如果等於 TRUE (TRUE) 或未指定,則將使用鏈接的樣式 A1; 如果為 FALSE (FALSE),則樣式 R1C1.
陷阱間接(間接)
- 功能 間接 (INDIRECT) 每當 Excel 工作表中的值發生變化時都會重新計算。 如果在許多公式中使用該函數,這會大大減慢您的工作簿。
- 如果功能 間接 (INDIRECT) 創建指向另一個 Excel 工作簿的鏈接,該工作簿必須打開,否則公式將報告錯誤 #REF! (#關聯!)。
- 如果功能 間接 (INDIRECT) 引用了超出行列限制的範圍,公式會報錯 #REF! (#關聯!)。
- 功能 間接 (INDIRECT) 不能引用動態命名範圍。
示例 1:創建非移位初始鏈接
在第一個示例中,C 列和 E 列包含相同的數字,它們的總和使用函數計算 和 (SUM) 也一樣。 但是,公式略有不同。 在單元格 C8 中,公式為:
=SUM(C2:C7)
=СУММ(C2:C7)
在單元格 E8 中,函數 間接 (INDIRECT) 創建到起始單元格 E2 的鏈接:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
如果您在工作表頂部插入一行並添加一月(Jan)的值,則 C 列中的金額不會改變。 公式將改變,對添加一行做出反應:
=SUM(C3:C8)
=СУММ(C3:C8)
然而,函數 間接 (INDIRECT) 將 E2 固定為起始單元格,因此一月自動包含在 E 列總計的計算中。 結束單元格已更改,但起始單元格未受影響。
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
示例 2:鏈接到靜態命名範圍
功能 間接 (INDIRECT) 可以創建對命名範圍的引用。 在此示例中,藍色單元格構成範圍 編號列表. 此外,還根據 B 列中的值創建了一個動態範圍 數字列表動態,取決於此列中的數字數量。
兩個範圍的總和可以通過簡單地將其名稱作為函數的參數來計算 和 (SUM),正如您在單元格 E3 和 E4 中看到的那樣。
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
而不是在函數中鍵入範圍名稱 和 (SUM),您可以參考寫在工作表的單元格之一中的名稱。 例如,如果名稱 編號列表 寫在單元格 D7 中,那麼單元格 E7 中的公式將是這樣的:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
不幸的是功能 間接 (INDIRECT) 無法創建動態範圍引用,因此當您將此公式複製到單元格 E8 中時,您將收到錯誤消息 #REF! (#關聯!)。
示例 3:使用工作表、行和列信息創建鏈接
您可以根據行號和列號輕鬆創建鏈接,以及將值 FALSE (FALSE) 用於第二個函數參數 間接 (間接)。 這就是樣式鏈接的創建方式 R1C1. 在此示例中,我們還向鏈接添加了工作表名稱 - 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
示例 4:創建一個不移位的數字數組
有時您需要在 Excel 公式中使用數字數組。 在下面的示例中,我們希望對 B 列中的 3 個最大數字進行平均。可以將這些數字輸入到公式中,就像在單元格 D4 中所做的那樣:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
如果您需要更大的數組,那麼您不太可能希望在公式中輸入所有數字。 第二個選項是使用函數 行 (行),如在單元格 D5 中輸入的數組公式中所做的那樣:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
第三個選項是使用函數 行 (字符串)連同 間接 (間接),與單元格 D6 中的數組公式一樣:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
所有 3 個公式的結果將相同:
但是,如果在工作表頂部插入行,則第二個公式將返回不正確的結果,因為公式中的引用會隨著行移位而改變。 現在,該公式將返回第三、第四和第五大數字的平均值,而不是三個最大數字的平均值。
使用函數 間接 (INDIRECT),第三個公式保持正確的行引用並繼續顯示正確的結果。