2017年最全的excel函數大全3—查找和引用函數（下）

上次給大家分享了《2017年最全的excel函數大全（3）——查找和引用函數（上）》，這次分享給大家查找和引用函數（下）。

INDIRECT 函數

描述

返回由文本字元串指定的引用。此函數立即對引用進行計算，並顯示其內容。如果需要更改公式中對單元格的引用，而不更改公式本身，請使用函數 INDIRECT。

用法

INDIRECT(ref_text, [a1])

INDIRECT 函數語法具有以下參數：

Ø Ref_text 必需。對單元格的引用，此單元格包含 A1 樣式的引用、R1C1 樣式的引用、定義為引用的名稱或對作為文本字元串的單元格的引用。如果 ref_text 不是合法的單元格引用，則 INDIRECT 返回 錯誤值。

ü 如果 ref_text 是對另一個工作簿的引用（外部引用），則被引用的工作簿必須已打開。如果源工作簿沒有打開，則 INDIRECT 返回錯誤值 #REF!。

ü 注意 Excel Web App 中不支持外部引用。

ü 如果 ref_text 引用的單元格區域超出 1,048,576 這一行限制或 16,384 (XFD) 這一列限制，則 INDIRECT 返回錯誤 #REF!。

Ø A1 可選。一個邏輯值，用於指定包含在單元格 ref_text 中的引用的類型。

ü 如果 a1 為 TRUE 或省略，ref_text 被解釋為 A1-樣式的引用。

ü 如果 a1 為 FALSE，則將 ref_text 解釋為 R1C1 樣式的引用。

案例

LOOKUP 函數

描述

當您需要查詢一行或一列並查找另一行或列中的相同位置的值時，會使用其中一個查找和引用函數 LOOKUP。

例如，假設你知道某個汽車部件的部件號，但是不知道價格。 如果在單元格 H1 中輸入汽車部件號，可在單元格 H2 中使用 LOOKUP 函數返回價格。

可使用 LOOKUP 函數搜索一行或一列。 在上面的示例中，我們在 D 列中搜索價格。

可使用 VLOOKUP 搜索一行或一列，或搜索多行和多列（如表）。

LOOKUP 有兩種使用方式：向量形式和數組形式

向量形式

可使用 LOOKUP 的這種形式在一行或一列中搜索值。 如果要指定包含要匹配的值的區域，請使用這種形式。 例如，如果要在 A 列中向下搜索值到第 6 行。

LOOKUP 的向量形式在單行區域或單列區域（稱為「向量」）中查找值，然後返回第二個單行區域或單列區域中相同位置的值。

用法

LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP 函數向量形式語法具有以下參數：

Ø lookup_value 必需。 LOOKUP 在第一個向量中搜索的值。 Lookup_value 可以是數字、文本、邏輯值、名稱或對值的引用。

Ø lookup_vector 必需。 只包含一行或一列的區域。 lookup_vector 中的值可以是文本、數字或邏輯值。

重要: lookup_vector 中的值必須按升序排列：..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；否則，LOOKUP 可能無法返回正確的值。 文本不區分大小寫。

Ø result_vector 可選。只包含一行或一列的區域。result_vector 參數必須與 lookup_vector 參數大小相同。其大小必須相同。

其他

如果 LOOKUP 函數找不到 lookup_value，則該函數會與 lookup_vector 中小於或等於 lookup_value 的最大值進行匹配。

如果 lookup_value 小於 lookup_vector 中的最小值，則 LOOKUP 會返回 #N/A 錯誤值。

案例

案例1

數組形式

強烈建議使用 VLOOKUP 或 HLOOKUP，不要使用數組形式。

數組是要搜索的行和列（如表）中的值的集合。 例如，如果要在 A 列和 B 列中向下搜索值到第 6 行。 LOOKUP 將返回最接近的匹配項。 要使用數組形式，必須對數據排序。

LOOKUP 的數組形式在數組的第一行或第一列中查找指定的值，並返回數組最後一行或最後一列中同一位置的值。當要匹配的值位於數組的第一行或第一列中時，請使用 LOOKUP 的這種形式。

用法

LOOKUP(lookup_value, array)

LOOKUP 函數數組形式語法具有以下參數：

Ø lookup_value 必需。 LOOKUP 在數組中搜索的值。 lookup_value 參數可以是數字、文本、邏輯值、名稱或對值的引用。

ü 如果 LOOKUP 找不到 lookup_value 的值，它會使用數組中小於或等於 lookup_value 的最大值。

ü 如果 lookup_value 的值小於第一行或第一列中的最小值（取決於數組維度），LOOKUP 會返回 #N/A 錯誤值。

Ø array 必需。 包含要與 lookup_value 進行比較的文本、數字或邏輯值的單元格區域。

LOOKUP 的數組形式與 HLOOKUP 和 VLOOKUP 函數非常相似。 區別在於：HLOOKUP 在第一行中搜索 lookup_value 的值，VLOOKUP 在第一列中搜索，而 LOOKUP 根據數組維度進行搜索。

ü 如果數組包含寬度比高度大的區域（列數多於行數）LOOKUP 會在第一行中搜索 lookup_value 的值。

ü 如果數組是正方的或者高度大於寬度（行數多於列數），LOOKUP 會在第一列中進行搜索。

ü 使用 HLOOKUP 和 VLOOKUP 函數，您可以通過索引以向下或遍歷的方式搜索，但是 LOOKUP 始終選擇行或列中的最後一個值。

重要: 數組中的值必須按升序排列：..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；否則，LOOKUP 可能無法返回正確的值。 文本不區分大小寫。

MATCH 函數

描述

使用 MATCH 函數在 範圍 單元格中搜索特定的項，然後返回該項在此區域中的相對位置。例如，如果 A1:A3 區域中包含值 5、25 和 38，那麼公式 =MATCH(25,A1:A3,0) 返回數字 2，因為 25 是該區域中的第二項。

提示: 當您需要項目在區域中的位置而非項目本身時，使用 MATCH 而不是 LOOKUP 函數之一。例如，您可以使用 MATCH 函數提供 INDEX 函數的 row_num 參數值。

用法

MATCH(lookup_value, lookup_array, [match_type])

MATCH 函數語法具有下列參數：

Ø lookup_value 必需。要在 lookup_array 中匹配的值。例如，如果要在電話簿中查找某人的電話號碼，則應該將姓名作為查找值，但實際上需要的是電話號碼。

lookup_value 參數可以為值（數字、文本或邏輯值）或對數字、文本或邏輯值的單元格引用。

Ø lookup_array 必需。要搜索的單元格區域。

Ø match_type 可選。數字 -1、0 或 1。match_type 參數指定 Excel 如何將 lookup_value 與 lookup_array 中的值匹配。此參數的默認值為 1。

下表介紹該函數如何根據 match_type 參數的設置查找值。

ü MATCH 返回匹配值在 lookup_array 中的位置，而非其值本身。例如，MATCH(b,{a,b,c},0)返回 2，即「b」在數組 {a,b,c} 中的相對位置。

ü 匹配文本值時，MATCH 函數不區分大小寫字母。

ü 如果 MATCH 函數查找匹配項不成功，它會返回錯誤值 #N/A。

ü 如果 match_type 為 0 且 lookup_value 為文本字元串，您可在 lookup_value 參數中使用通配符 - 問號 (?) 和星號 (*) 。問號匹配任意單個字元；星號匹配任意一串字元。如果要查找實際的問號或星號，請在字元前鍵入波形符 (~)。

案例

OFFSET 函數

描述

返回對單元格或單元格區域中指定行數和列數的區域的引用。 返回的引用可以是單個單元格或單元格區域。 可以指定要返回的行數和列數。

用法

OFFSET(reference, rows, cols, [height], [width])

OFFSET 函數語法具有下列參數：

Ø 引用 必需。 要以其為偏移量的底數的引用。 引用必須是對單元格或相鄰的單元格區域的引用；否則OFFSET 返回 錯誤值 #VALUE!。

Ø Rows 必需。 需要左上角單元格引用的向上或向下行數。 使用 5 作為 rows 參數，可指定引用中的左上角單元格為引用下方的 5 行。 Rows 可為正數（這意味著在起始引用的下方）或負數（這意味著在起始引用的上方）。

Ø Cols 必需。 需要結果的左上角單元格引用的從左到右的列數。 使用 5 作為 cols 參數，可指定引用中的左上角單元格為引用右方的 5 列。 Cols 可為正數（這意味著在起始引用的右側）或負數（這意味著在起始引用的左側）。

Ø 高度 可選。 需要返回的引用的行高。 Height 必須為正數。

Ø 寬度 可選。 需要返回的引用的列寬。 Width 必須為正數。

其他

ü 如果 rows 和 cols 的偏移使引用超出了工作表邊緣，則 OFFSET 返回， 錯誤值 #REF!。

ü 如果省略 height 或 width，則假設其高度或寬度與 reference 相同。

ü OFFSET 實際上並不移動任何單元格或更改選定區域；它只是返回一個引用。 OFFSET 可以與任何期待引用參數的函數一起使用。 例如，公式 SUM(OFFSET(C2,1,2,3,1)) 可計算 3 行 1 列區域（即單元格 C2 下方的 1 行和右側的 2 列的 3 行 1 列區域）的總值。

案例

ROW 函數

描述

返回引用的行號。

用法

ROW([reference])

ROW 函數語法具有下列參數：

Ø Reference 可選。 需要得到其行號的單元格或單元格區域。

ü 如果省略 reference，則假定是對函數 ROW 所在單元格的引用。

ü 如果 reference 為一個單元格區域，並且 ROW 作為垂直數組輸入，則 ROW 將以垂直數組的形式返回 reference 的行號。

ü Reference 不能引用多個區域。

案例

ROWS 函數

描述

返回引用或數組的行數。

用法

ROWS(array)

ROWS 函數語法具有以下參數：

Ø Array 必需。 需要得到其行數的數組、數組公式或對單元格區域的引用。

案例

RTD 函數

描述

從支持 COM 自動化的程序中檢索實時數據

用法

RTD(ProgID, server, topic1, [topic2], ...)

RTD 函數語法具有下列參數：

Ø ProgID 必需。 已安裝在本地計算機上的已註冊 COM 自動化載入項 ProgID 的名稱。 將該名稱用引號括起來。

Ø server 必需。應運行載入項的伺服器的名稱。如果沒有伺服器，則在本地運行程序，將此參數保留為空。否則，輸入引號 將伺服器名稱括起來。在 Visual Basic for Applications (VBA) 中使用 RTD 時，伺服器需要雙引號或 VBA Nullstring 屬性，即使在本地運行伺服器也不例外。

Ø Topic1, topic2, ... Topic1 是必需的，後續主題是可選的。 1 到 253 個參數，這些參數放在一起代表一個唯一的實時數據。

其他

ü 必須在本地計算機上創建和註冊 RTD COM 自動化載入項。 如果未安裝實時數據伺服器，則在嘗試使用 RTD 函數時，單元格中將出現錯誤消息。

ü 如果伺服器繼續更新結果，那麼與其他函數不同，RTD 公式將在 Microsoft Excel 處於自動計算模式時進行更改。

案例

註釋

必須在本地計算機上創建和註冊 RTD COM 自動化載入項。 如果未安裝實時數據伺服器，則在試圖使用 RTD 函數時 將在單元格中出現一則錯誤消息 #NAME?。

TRANSPOSE 函數

描述

TRANSPOSE 函數可返迴轉置單元格區域，即將行單元格區域轉置成列單元格區域，反之亦然。TRANSPOSE 函數必須在與源單元格範圍具有相同行數和列數的單元格區域中作為數組公式分別輸入。使用 TRANSPOSE 可以轉置數組或工作表上單元格區域的垂直和水平方向。

用法

TRANSPOSE(array)

TRANSPOSE 函數語法具有以下參數：

Ø array 必需。需要進行轉置的數組或工作表上的單元格區域。所謂數組的轉置就是，將數組的第一行作為新數組的第一列，數組的第二行作為新數組的第二列，以此類推。如果不確定如何輸入數組公式，請參閱輸入數組公式。

案例

有時，你需要切換或旋轉單元格。可通過複製、粘貼和使用「轉置」選項來執行此操作。但這樣做會創建重複的數據。如果不希望產生重複數據，可選擇鍵入公式，而不是用 TRANSPOSE 函數。

例如，在下圖中，公式 =TRANSPOSE(A1:B4) 會選取單元格 A1 到 B4，並將它們水平排列。

上方為原始單元格，下方的單元格使用 TRANSPOSE 函數

步驟 1：選擇空白單元格

首先選擇一些空白單元格。但請確保選擇的單元格數量與原始單元格數量相同，但方向不同。例如，此處有 8 個垂直排列的單元格：

因此，我們要選擇 8 個水平排列的單元格，如下所示：

轉置的新單元格將位於此處。

步驟 2：鍵入 =TRANSPOSE(

使這些空單元格保持選中狀態，鍵入：=TRANSPOSE(

Excel 的外觀將如下所示：

請注意，即使已開始輸入公式，8 個單元格仍處於選中狀態。

步驟 3：鍵入原始單元格的範圍。

現在，鍵入想要轉置的單元格範圍。在此示例中，我們要轉置單元格 A1 到 B4。所以此示例的公式是：= TRANSPOSE(A1:B4) -- ，但此時還不能按 Enter！停止鍵入，轉到下一步。

Excel 的外觀將如下所示：

步驟 4：最後，按 Ctrl+Shift+Enter

現在按 Ctrl+Shift+Enter。為什麼？因為 TRANSPOSE 函數僅適用於數組公式，而這就是數組公式的結束方式。簡而言之，數組公式就是一個應用於多個單元格的公式。因為在步驟 1 中選擇了多個單元格，因此該公式將應用於多個單元格。按 Ctrl+Shift+Enter 后，結果如下：

提示

ü 無需手動鍵入範圍。鍵入 =TRANSPOSE( 后，可使用滑鼠選擇範圍。只需單擊並從範圍的開始處拖到結尾處。但請記住：完成操作后，請按 Ctrl+Shift+Enter，而不只是 Enter。

ü 還需轉換文本和單元格格式？嘗試複製、粘貼和使用「轉置」選項。但請注意，此操作會創建重複內容。因此，如果原始單元格發生更改，副本不會更新。

VLOOKUP 函數

描述

如果需要在表格或區域中按行查找內容，可使用 VLOOKUP，它是一個查找和引用函數。=VLOOKUP（要查找的值、要在其中查找值的區域、區域中包含返回值的列號、精確匹配或近似匹配 – 指定為 0/FALSE 或 1/TRUE）。

用法

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

案例

案例 1

案例 2

案例 3

案例 4

案例 5

以上是所有excel的查找和引用函數（下）說明語法以及使用案例。這次分享中存在哪些疑問或者哪些不足，可以在下面進行評論。如果覺得不錯，可以分享給你的朋友，讓大家一起掌握這些excel的查找和引用函數。