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

INDIRECT 函數(shù)

描述

返回由文本字符串指定的引用。此函數(shù)立即對(duì)引用進(jìn)行計(jì)算，并顯示其內(nèi)容。如果需要更改公式中對(duì)單元格的引用，而不更改公式本身，請(qǐng)使用函數(shù) INDIRECT。???

用法?

INDIRECT(ref_text, [a1])

?INDIRECT 函數(shù)語(yǔ)法具有以下參數(shù)：

?? Ref_text??? 必需。對(duì)單元格的引用，此單元格包含 A1 樣式的引用、R1C1 樣式的引用、定義為引用的名稱或?qū)ψ鳛槲谋咀址膯卧竦囊?。如?ref_text 不是合法的單元格引用，則 INDIRECT 返回 錯(cuò)誤值。

ü? 如果 ref_text 是對(duì)另一個(gè)工作簿的引用（外部引用），則被引用的工作簿必須已打開(kāi)。如果源工作簿沒(méi)有打開(kāi)，則 INDIRECT 返回錯(cuò)誤值 #REF!。

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

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

?? A1??? 可選。一個(gè)邏輯值，用于指定包含在單元格 ref_text 中的引用的類型。

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

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

案例

?

LOOKUP 函數(shù)

描述

當(dāng)您需要查詢一行或一列并查找另一行或列中的相同位置的值時(shí)，會(huì)使用其中一個(gè)查找和引用函數(shù) LOOKUP。

例如，假設(shè)你知道某個(gè)汽車部件的部件號(hào)，但是不知道價(jià)格。 如果在單元格 H1 中輸入汽車部件號(hào)，可在單元格 H2 中使用 LOOKUP 函數(shù)返回價(jià)格。

可使用 LOOKUP 函數(shù)搜索一行或一列。 在上面的示例中，我們?cè)?D 列中搜索價(jià)格。

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

LOOKUP 有兩種使用方式：向量形式和數(shù)組形式

向量形式

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

LOOKUP 的向量形式在單行區(qū)域或單列區(qū)域（稱為“向量”）中查找值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的值。

用法

LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP 函數(shù)向量形式語(yǔ)法具有以下參數(shù)：

?? lookup_value??? 必需。 LOOKUP 在第一個(gè)向量中搜索的值。 Lookup_value 可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊谩?/p>

?? lookup_vector??? 必需。 只包含一行或一列的區(qū)域。 lookup_vector 中的值可以是文本、數(shù)字或邏輯值。

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

?? result_vector??? 可選。只包含一行或一列的區(qū)域。result_vector 參數(shù)必須與 lookup_vector 參數(shù)大小相同。其大小必須相同。

其他

如果 LOOKUP 函數(shù)找不到 lookup_value，則該函數(shù)會(huì)與 lookup_vector 中小于或等于 lookup_value 的最大值進(jìn)行匹配。

如果 lookup_value 小于 lookup_vector 中的最小值，則 LOOKUP 會(huì)返回 #N/A 錯(cuò)誤值。

案例

案例1

數(shù)組形式

強(qiáng)烈建議使用 VLOOKUP 或 HLOOKUP，不要使用數(shù)組形式。

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

LOOKUP 的數(shù)組形式在數(shù)組的第一行或第一列中查找指定的值，并返回?cái)?shù)組最后一行或最后一列中同一位置的值。當(dāng)要匹配的值位于數(shù)組的第一行或第一列中時(shí)，請(qǐng)使用 LOOKUP 的這種形式。

用法

LOOKUP(lookup_value, array)

LOOKUP 函數(shù)數(shù)組形式語(yǔ)法具有以下參數(shù)：

?? lookup_value??? 必需。 LOOKUP 在數(shù)組中搜索的值。 lookup_value 參數(shù)可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊谩?/p>

ü? 如果 LOOKUP 找不到 lookup_value 的值，它會(huì)使用數(shù)組中小于或等于 lookup_value 的最大值。

ü? 如果 lookup_value 的值小于第一行或第一列中的最小值（取決于數(shù)組維度），LOOKUP 會(huì)返回 #N/A 錯(cuò)誤值。

?? array??? 必需。 包含要與 lookup_value 進(jìn)行比較的文本、數(shù)字或邏輯值的單元格區(qū)域。

LOOKUP 的數(shù)組形式與 HLOOKUP 和 VLOOKUP 函數(shù)非常相似。 區(qū)別在于：HLOOKUP 在第一行中搜索 lookup_value 的值，VLOOKUP 在第一列中搜索，而 LOOKUP 根據(jù)數(shù)組維度進(jìn)行搜索。

ü? 如果數(shù)組包含寬度比高度大的區(qū)域（列數(shù)多于行數(shù)）LOOKUP 會(huì)在第一行中搜索 lookup_value 的值。

ü? 如果數(shù)組是正方的或者高度大于寬度（行數(shù)多于列數(shù)），LOOKUP 會(huì)在第一列中進(jìn)行搜索。

ü? 使用 HLOOKUP 和 VLOOKUP 函數(shù)，您可以通過(guò)索引以向下或遍歷的方式搜索，但是 LOOKUP 始終選擇行或列中的最后一個(gè)值。

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

MATCH 函數(shù)

描述

使用 MATCH 函數(shù)在 范圍 單元格中搜索特定的項(xiàng)，然后返回該項(xiàng)在此區(qū)域中的相對(duì)位置。例如，如果 A1:A3 區(qū)域中包含值 5、25 和 38，那么公式 =MATCH(25,A1:A3,0) 返回?cái)?shù)字 2，因?yàn)?25 是該區(qū)域中的第二項(xiàng)。

提示: 當(dāng)您需要項(xiàng)目在區(qū)域中的位置而非項(xiàng)目本身時(shí)，使用 MATCH 而不是 LOOKUP 函數(shù)之一。例如，您可以使用 MATCH 函數(shù)提供 INDEX 函數(shù)的 row_num 參數(shù)值。

用法

MATCH(lookup_value, lookup_array, [match_type])

MATCH 函數(shù)語(yǔ)法具有下列參數(shù)：

?? lookup_value??? 必需。要在 lookup_array 中匹配的值。例如，如果要在電話簿中查找某人的電話號(hào)碼，則應(yīng)該將姓名作為查找值，但實(shí)際上需要的是電話號(hào)碼。

lookup_value 參數(shù)可以為值（數(shù)字、文本或邏輯值）或?qū)?shù)字、文本或邏輯值的單元格引用。

?? lookup_array?? ?必需。要搜索的單元格區(qū)域。

?? match_type??? 可選。數(shù)字 -1、0 或 1。match_type 參數(shù)指定 Excel 如何將 lookup_value 與 lookup_array 中的值匹配。此參數(shù)的默認(rèn)值為 1。

下表介紹該函數(shù)如何根據(jù) match_type 參數(shù)的設(shè)置查找值。

ü? MATCH 返回匹配值在 lookup_array 中的位置，而非其值本身。例如，MATCH("b",{"a","b","c"},0)返回 2，即“b”在數(shù)組 {"a","b","c"} 中的相對(duì)位置。

ü? 匹配文本值時(shí)，MATCH 函數(shù)不區(qū)分大小寫(xiě)字母。

ü? 如果 MATCH 函數(shù)查找匹配項(xiàng)不成功，它會(huì)返回錯(cuò)誤值 #N/A。

ü? 如果 match_type 為 0 且 lookup_value 為文本字符串，您可在 lookup_value 參數(shù)中使用通配符 - 問(wèn)號(hào) (?) 和星號(hào) (*) 。問(wèn)號(hào)匹配任意單個(gè)字符；星號(hào)匹配任意一串字符。如果要查找實(shí)際的問(wèn)號(hào)或星號(hào)，請(qǐng)?jiān)谧址版I入波形符 (~)。

案例

?

OFFSET 函數(shù)

描述

返回對(duì)單元格或單元格區(qū)域中指定行數(shù)和列數(shù)的區(qū)域的引用。 返回的引用可以是單個(gè)單元格或單元格區(qū)域。 可以指定要返回的行數(shù)和列數(shù)。

用法

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

OFFSET 函數(shù)語(yǔ)法具有下列參數(shù)：

?? 引用??? 必需。 要以其為偏移量的底數(shù)的引用。 引用必須是對(duì)單元格或相鄰的單元格區(qū)域的引用；否則OFFSET 返回 錯(cuò)誤值 #VALUE!。

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

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

?? 高度??? 可選。 需要返回的引用的行高。 Height 必須為正數(shù)。

?? 寬度??? 可選。 需要返回的引用的列寬。 Width 必須為正數(shù)。

其他

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

ü? 如果省略 height 或 width，則假設(shè)其高度或?qū)挾扰c reference 相同。

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

案例

ROW 函數(shù)

描述

返回引用的行號(hào)。

用法

ROW([reference])

ROW 函數(shù)語(yǔ)法具有下列參數(shù)：

?? Reference??? 可選。 需要得到其行號(hào)的單元格或單元格區(qū)域。

ü? 如果省略 reference，則假定是對(duì)函數(shù) ROW 所在單元格的引用。

ü? 如果 reference 為一個(gè)單元格區(qū)域，并且 ROW 作為垂直數(shù)組輸入，則 ROW 將以垂直數(shù)組的形式返回 reference 的行號(hào)。

ü? Reference 不能引用多個(gè)區(qū)域。

案例

ROWS 函數(shù)

描述

返回引用或數(shù)組的行數(shù)。

用法

ROWS(array)

ROWS 函數(shù)語(yǔ)法具有以下參數(shù)：

?? Array??? 必需。 需要得到其行數(shù)的數(shù)組、數(shù)組公式或?qū)卧駞^(qū)域的引用。

案例

RTD 函數(shù)

描述

從支持 COM 自動(dòng)化的程序中檢索實(shí)時(shí)數(shù)據(jù)

用法

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

RTD 函數(shù)語(yǔ)法具有下列參數(shù)：

?? ProgID??? 必需。 已安裝在本地計(jì)算機(jī)上的已注冊(cè) COM 自動(dòng)化加載項(xiàng) ProgID 的名稱。 將該名稱用引號(hào)括起來(lái)。

?? server??? 必需。應(yīng)運(yùn)行加載項(xiàng)的服務(wù)器的名稱。如果沒(méi)有服務(wù)器，則在本地運(yùn)行程序，將此參數(shù)保留為空。否則，輸入引號(hào) ("") 將服務(wù)器名稱括起來(lái)。在 Visual Basic for Applications (VBA) 中使用 RTD 時(shí)，服務(wù)器需要雙引號(hào)或 VBA Nullstring 屬性，即使在本地運(yùn)行服務(wù)器也不例外。

?? Topic1, topic2, ...??? Topic1 是必需的，后續(xù)主題是可選的。 1 到 253 個(gè)參數(shù)，這些參數(shù)放在一起代表一個(gè)唯一的實(shí)時(shí)數(shù)據(jù)。

其他

ü? 必須在本地計(jì)算機(jī)上創(chuàng)建和注冊(cè) RTD COM 自動(dòng)化加載項(xiàng)。 如果未安裝實(shí)時(shí)數(shù)據(jù)服務(wù)器，則在嘗試使用 RTD 函數(shù)時(shí)，單元格中將出現(xiàn)錯(cuò)誤消息。

ü? 如果服務(wù)器繼續(xù)更新結(jié)果，那么與其他函數(shù)不同，RTD 公式將在 Microsoft Excel 處于自動(dòng)計(jì)算模式時(shí)進(jìn)行更改。

案例

注釋

必須在本地計(jì)算機(jī)上創(chuàng)建和注冊(cè) RTD COM 自動(dòng)化加載項(xiàng)。 如果未安裝實(shí)時(shí)數(shù)據(jù)服務(wù)器，則在試圖使用 RTD 函數(shù)時(shí) 將在單元格中出現(xiàn)一則錯(cuò)誤消息 #NAME?。

TRANSPOSE 函數(shù)

描述

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

用法

TRANSPOSE(array)

TRANSPOSE 函數(shù)語(yǔ)法具有以下參數(shù)：

?? array??? 必需。需要進(jìn)行轉(zhuǎn)置的數(shù)組或工作表上的單元格區(qū)域。所謂數(shù)組的轉(zhuǎn)置就是，將數(shù)組的第一行作為新數(shù)組的第一列，數(shù)組的第二行作為新數(shù)組的第二列，以此類推。如果不確定如何輸入數(shù)組公式，請(qǐng)參閱輸入數(shù)組公式。

案例

有時(shí)，你需要切換或旋轉(zhuǎn)單元格?？赏ㄟ^(guò)復(fù)制、粘貼和使用“轉(zhuǎn)置”選項(xiàng)來(lái)執(zhí)行此操作。但這樣做會(huì)創(chuàng)建重復(fù)的數(shù)據(jù)。如果不希望產(chǎn)生重復(fù)數(shù)據(jù)，可選擇鍵入公式，而不是用 TRANSPOSE 函數(shù)。

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

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

步驟 1：選擇空白單元格

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

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

轉(zhuǎn)置的新單元格將位于此處。

步驟 2：鍵入 =TRANSPOSE(

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

Excel 的外觀將如下所示：

請(qǐng)注意，即使已開(kāi)始輸入公式，8 個(gè)單元格仍處于選中狀態(tài)。

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

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

Excel 的外觀將如下所示：

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

現(xiàn)在按 Ctrl+Shift+Enter。為什么？因?yàn)?TRANSPOSE 函數(shù)僅適用于數(shù)組公式，而這就是數(shù)組公式的結(jié)束方式。簡(jiǎn)而言之，數(shù)組公式就是一個(gè)應(yīng)用于多個(gè)單元格的公式。因?yàn)樵诓襟E 1 中選擇了多個(gè)單元格，因此該公式將應(yīng)用于多個(gè)單元格。按 Ctrl+Shift+Enter 后，結(jié)果如下：

提示

ü? 無(wú)需手動(dòng)鍵入范圍。鍵入 =TRANSPOSE( 后，可使用鼠標(biāo)選擇范圍。只需單擊并從范圍的開(kāi)始處拖到結(jié)尾處。但請(qǐng)記?。和瓿刹僮骱?，請(qǐng)按 Ctrl+Shift+Enter，而不只是 Enter。

ü? 還需轉(zhuǎn)換文本和單元格格式？嘗試復(fù)制、粘貼和使用“轉(zhuǎn)置”選項(xiàng)。但請(qǐng)注意，此操作會(huì)創(chuàng)建重復(fù)內(nèi)容。因此，如果原始單元格發(fā)生更改，副本不會(huì)更新。

VLOOKUP 函數(shù)

描述

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

用法

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

案例

案例 1

案例 2

案例 3

案例 4

案例 5

以上是所有excel的查找和引用函數(shù)（下）說(shuō)明語(yǔ)法以及使用案例。這次分享中存在哪些疑問(wèn)或者哪些不足，可以在下面進(jìn)行評(píng)論。如果覺(jué)得不錯(cuò)，可以分享給你的朋友，讓大家一起掌握這些excel的查找和引用函數(shù)。