1 LOOKUP 函數(shù)
LOOKUP 函數(shù)返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組（用于建立可生成多個結(jié)果或可對在行和列中排列的一組參數(shù)進行運算的單個公式。數(shù)組區(qū)域共用一個公式。數(shù)組常量是用作參數(shù)的一組常量）中的數(shù)值。函數(shù)LOOKUP有兩種
函數(shù) LOOKUP 有兩種語法形式
1.1 向量
函數(shù) LOOKUP 的向量形式是在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值
LOOKUP(lookup_value,lookup_vector,result_vector)
| 參數(shù) | 簡單說明 |
|---|---|
| Lookup_value | 為函數(shù) LOOKUP 在第一個向量中所要查找的數(shù)值。lookup_value 可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用 |
| Lookup_vector | 為只包含一行或一列的區(qū)域。lookup_vector 的數(shù)值可以為文本、數(shù)字或邏輯值。 |
| Result_vector | 只包含一行或一列的區(qū)域，其大小必須與 lookup_vector 相同 |
=LOOKUP(x,A:A,B:B)
# 在 A 列中查找 x，并返回同一行 B 列的值
- 數(shù)組
函數(shù) LOOKUP 的數(shù)組形式是在數(shù)組的第一行或第一列中查找指定數(shù)值，然后返回最后一行或最后一列中相同位置處的數(shù)值。
LOOKUP(lookup_value,array)
LOOKUP_vector 的數(shù)值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。否則，函數(shù) LOOKUP 不能返回正確的結(jié)果。文本不區(qū)分大小寫。
=LOOKUP("C",{"a","b","c","d";1,2,3,4})
# 在數(shù)組的第一行中查找“C”，并返回同一列中最后一行的值 (3)
2 VLOOKUP
VLOOKUP 函數(shù)在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。當比較值位于數(shù)據(jù)表首列時，可以使用函數(shù)VLOOKUP代替函數(shù)HLOOKUP。在VLOOKUP中的V代表垂直。
2.1 語法
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
| 參數(shù) | 簡單說明 | 輸入數(shù)據(jù)類型 |
|---|---|---|
| lookup_value | 要查找的值 | 數(shù)值、引用或文本字符串 |
| table_array | 要查找的區(qū)域 | 數(shù)據(jù)表區(qū)域 |
| col_index_num | 返回數(shù)據(jù)在查找區(qū)域的第幾列數(shù) | 正整數(shù) |
| range_lookup | 模糊匹配/精確匹配 | TRUE/FALSE（或不填） |
2.2 舉例
# 新建excel
# "=CHAR(RAND()*26+65)" 生成隨機大寫字母
# "=CHAR(RAND()*26+97)" 生成隨機小寫字母
# "=RANDBETWEEN(bottom,top)" 生成隨機數(shù)
# vlookup() 函數(shù)按列篩選匹配的數(shù)據(jù)，VLOOKUP(1,A:C,3,TRUE)為在 A ：C列中查找 1，并根據(jù)查到的匹配值返回A:C區(qū)域第3列的對應(yīng)值
3 HLOOKUP
在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當前列中指定行處的數(shù)值。當比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時，請使用函數(shù) HLOOKUP。當比較值位于要查找的數(shù)據(jù)左邊的一列時，請使用函數(shù) VLOOKUP。
HLOOKUP 中的 H 代表“行”。
語法
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
| 參數(shù) | 簡單說明 |
|---|---|
| lookup_value | 為需要在數(shù)據(jù)表第一行中進行查找的數(shù)值,可以為數(shù)值、引用或文本字符串 |
| Table_array | 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤^(qū)域或區(qū)域名稱的引用 |
| row_index_num | 為 table_array 中待返回的匹配值的行序號。row_index_num 為 1 時，返回 table_array 第一行的數(shù)值，row_index_num 為 2 時，返回 table_array 第二行的數(shù)值，以此類推 |
| Range_LOOKUP | 為一邏輯值，指明函數(shù) HLOOKUP 查找時是精確匹配，還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值。也就是說，如果找不到精確匹配值，則返回小于 lookup_value 的最大數(shù)值。如果 range_value 為 FALSE，函數(shù) HLOOKUP 將查找精確匹配值，如果找不到，則返回錯誤值 #N/A! |
3.2 說明
- 如果函數(shù) HLOOKUP 找不到 lookup_value，且 range_lookup 為 TRUE，則使用小于 lookup_value 的最大值。
- 如果函數(shù) HLOOKUP 小于 table_array 第一行中的最小數(shù)值，函數(shù) HLOOKUP 返回錯誤值 #N/A!
HVLOOKUP與VLOOKUP用法相同，其中H代表行，V代表列，返回的值分別為指定的行和列
4 MATCH
返回在指定方式下與指定數(shù)組匹配的數(shù)組中元素的相應(yīng)位置。如果需要找出匹配元素的位置而不是匹配元素本身，則應(yīng)該使用 MATCH 函數(shù)而不是 LOOKUP 函數(shù)。
4.1 語法
MATCH(lookup_value,lookup_array,match_type)
| 參數(shù) | 簡單說明 |
|---|---|
| Lookup_value | 為需要在數(shù)據(jù)表中查找的數(shù)值 |
| Lookup_array | 可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域。lookup_array 應(yīng)為數(shù)組或數(shù)組引用 |
| Match_type | 為數(shù)字 -1、0 或 1。MATCH-type 指明WPS表格如何在 lookup_array 中查找 lookup_value |
4.2 說明
- 函數(shù) MATCH 返回 lookup_array 中目標值的位置，而不是數(shù)值本身。例如，MATCH("b",{"a","b","c"},0) 返回 2，即“b”在數(shù)組 {"a","b","c"} 中的相應(yīng)位置。
- 查找文本值時，函數(shù) MATCH 不區(qū)分大小寫字母。
- 如果函數(shù) MATCH 查找不成功，則返回錯誤值 #N/A。
- 如果 MATCH_type 為 0 且 lookup_value 為文本，lookup_value 可以包含通配符、星號 (*) 和問號 (?)。星號可以匹配任何字符序列。問號可以匹配單個字符
5 INDEX
返回表或區(qū)域中的值或值的引用。函數(shù)INDEX()有兩種形式：
數(shù)組和引用。數(shù)組形式通常返回數(shù)值或數(shù)值數(shù)組。引用形式通常返回引用。
- INDEX(array,Row_num,column_num) 返回數(shù)組中指定單元格或單元格數(shù)組的數(shù)值。
- INDEX(reference,Row_num,column_num,area_num) 返回引用中指定單元格區(qū)域的引用。
5.1 數(shù)組
返回數(shù)據(jù)清單或數(shù)組中的元素值，此元素由行序號和列序號的索引值給定。
INDEX(array,Row_num,column_num)
| 參數(shù) | 簡單說明 |
|---|---|
| Array | 為單元格區(qū)域或數(shù)組常量 |
| Row_num | 數(shù)組中某行的行序號，函數(shù)從該行返回數(shù)值。如果省略 Row_num，則必須有 column_num |
| Column_num | 數(shù)組中某列的列序號，函數(shù)從該列返回數(shù)值。如果省略 column_num，則必須有 Row_num |
說明
- 如果同時使用 Row_num 和 column_num，函數(shù) INDEX 返回 Row_num 和 column_num 交叉處的單元格的數(shù)值。
- 如果將 Row_num 或 column_num 設(shè)置為 0，函數(shù) INDEX 則分別返回整個列或行的數(shù)組數(shù)值。若要使用以數(shù)組形式返回的值，請將 INDEX 函數(shù)以[數(shù)組公式](javascript:viewsampleb())形式輸入，對于行以水平單元格區(qū)域的形式輸入，對于列以垂直單元格區(qū)域的形式輸入。若要輸入數(shù)組公式，請按 Ctrl+Shift+Enter。
-
Row_num 和 column_num 必須指向 array 中的某一單元格。否則，函數(shù) INDEX 返回錯誤值 #REF!。
image.png
5.2 引用
返回指定的行與列交叉處的單元格引用。如果引用由不連續(xù)的選定區(qū)域組成，可以選擇某一連續(xù)區(qū)域。
INDEX(reference,Row_num,column_num,area_num)
| 參數(shù) | 簡單說明 |
|---|---|
| Reference | 對一個或多個單元格區(qū)域的引用，如果為引用輸入一個不連續(xù)的區(qū)域，必須用括號括起來，如果引用中的每個區(qū)域只包含一行或一列，則相應(yīng)的參數(shù) Row_num 或 column_num 分別為可選項。例如，對于單行的引用，可以使用函數(shù) INDEX(reference,,column_num) |
| Row_num | 引用中某行的行序號，函數(shù)從該行返回一個引用 |
| COLUMN_num | 引用中某列的列序號，函數(shù)從該列返回一個引用 |
說明
- 在通過 reference 和 area_num 選擇了特定的區(qū)域后，Row_num 和 column_num 將進一步選擇指定的單元格：Row_num 1 為區(qū)域的首行，column_num 1 為首列，以此類推。函數(shù) INDEX 返回的引用即為 Row_num 和 column_num 的交叉區(qū)域。
- 如果將 Row_num 或 column_num 設(shè)置為 0，函數(shù) INDEX 分別返回對整個列或行的引用。
- Row_num、column_num 和 area_num 必須指向 reference 中的單元格。否則，函數(shù) INDEX 返回錯誤值 #REF!。如果省略 Row_num 和 column_num，函數(shù) INDEX 返回由 area_num 所指定的區(qū)域。
-
函數(shù) INDEX 的結(jié)果為一個引用，且在其他公式中也被解釋為引用。根據(jù)公式的需要，函數(shù) INDEX 的返回值可以作為引用或是數(shù)值。例如，公式 CELL("width",INDEX(A1:B2,1,2)) 等價于公式 CELL("width",B1)。CELL 函數(shù)將函數(shù) INDEX 的返回值作為單元格引用。而在另一方面，公式 2*INDEX(A1:B2,1,2) 將函數(shù) INDEX 的返回值解釋為 B1 單元格中的數(shù)字。
image.png
6 COLUMNS
返回數(shù)組或引用的列數(shù)。
語法
COLUMNS(array)
Array 為需要得到其列數(shù)的數(shù)組或數(shù)組公式，或?qū)卧駞^(qū)域的引用
7 ROWS
返回引用或數(shù)組的行數(shù)。
語法
ROWS(array)
Array 為需要得到其行數(shù)的數(shù)組、數(shù)組公式或?qū)卧駞^(qū)域的引用。
8 ADDRESS
按照給定的行號和列標，建立文本類型的單元格地址。
語法
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
| 參數(shù) | 簡單說明 |
|---|---|
| Row_num | 在單元格引用中使用的行號 |
| Column_num | 在單元格引用中使用的列標 |
| Abs_num | 指定返回的引用類型 |
說明
- 1或省略 絕對引用
- 2 絕對行號，相對列標
- 3 相對行號，絕對列標
- 4 相對引用
# 對A1孔絕對引用
# 直接輸入
=$A$1
# 輸入"＝"，選中引用孔，按下"F4"，可以轉(zhuǎn)換為絕對引用
=$A$1
9 OFFSET
以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。
語法
OFFSET(reference,rows,cols,height,width)
| 參數(shù) | 簡單說明 |
|---|---|
| Reference | 作為偏移量參照系的引用區(qū)域。Reference 必須為對單元格或相連單元格區(qū)域的引用。否則，函數(shù) OFFSET 返回錯誤值 #VALUE! |
| Rows | 相對于偏移量參照系的左上角單元格，上（下）偏移的行數(shù)。如果使用 5 作為參數(shù) ROWS，則說明目標引用區(qū)域的左上角單元格比 reference 低 5 行。行數(shù)可為正數(shù)（代表在起始引用的下方）或負數(shù)（代表在起始引用的上方） |
| Cols | 相對于偏移量參照系的左上角單元格，左（右）偏移的列數(shù)。如果使用 5 作為參數(shù) Cols，則說明目標引用區(qū)域的左上角的單元格比 reference 靠右 5 列。列數(shù)可為正數(shù)（代表在起始引用的右邊）或負數(shù)（代表在起始引用的左邊） |
| Height | 高度，即所要返回的引用區(qū)域的行數(shù)。Height 必須為正數(shù) |
| Width | 寬度，即所要返回的引用區(qū)域的列數(shù)。Width 必須為正數(shù) |
說明
- 如果行數(shù)和列數(shù)偏移量超出工作表邊緣，函數(shù) OFFSET 返回錯誤值 #REF!。
- 如果省略 height 或 width，則假設(shè)其高度或?qū)挾扰c reference 相同。
- 函數(shù) OFFSET 實際上并不移動任何單元格或更改選定區(qū)域，它只是返回一個引用。函數(shù) OFFSET 可用于任何需要將引用作為參數(shù)的函數(shù)。例如，公式 SUM(OFFSET(C2,1,2,3,1)) 將計算比單元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的區(qū)域的總值。
