查找與引用函數

• vlookup(lookup_value,table_array,col_index_num,range_lookup) - 搜索表區域首例滿足條件的元素,確定待檢索單元格在區域中的行序號,再進一步返回選定單元格的值。默認情況下，表是以升序排序的 <–> 垂直方向查找

• offset(reference，rows，columns,height，width) - 以指定的引用為參照系，通過給定偏移量返回新的引用

步驟:

先選中 與要查詢的單元格/單元格區域 具有相同區域大小的 空單元格區域

? 在此例子中,因為要查詢的區域 C5~E9 有5行3列 , 所以我們需要先在空白單元格里也選擇 5行3列 的單元格(H5~J9)

在 公式 欄中 輸入 =OFFSET(A5,2,2,5,3)

同時按下 Ctrl+Shift+Enter , 使得公示欄中的公式變為={OFFSET(A5,2,2,5,3)}樣式 , 即查詢成功

? 注意, 千萬不要直接回車 !!!

注意事項:

offset(基點 , 行偏移 , 列偏移 , 找多少行數據 , 找多少列數據)

reference必須為引用(包括函數產生的三維引用) , 不能為常量或數組

rows , columns 為必須項 , 如要省略須使用","逗號進行占位 , 缺省值為0(即不偏移) ;

heigh , width 為可選項 , 可省略 , 缺省視為與基點相同

rows-行偏移

• 向下偏移 為 正數
• 向上偏移 為 負數

columns-列偏移

• 向右偏移 為 正數
• 向左偏移 為 負數

公式寫完后 , 按下 Ctrl+Shift+Enter

• match(lookup_value,lookup_array,match_type) - 返回符合特定值特定順序的項在數組中的相對位置

注意事項:

lookup_value為必須項 , 該值是需要在 lookup_array 中查找的值

• lookup_value 可以為值(數字、文本或邏輯值) ，也可以為數字、文本或邏輯值的單元格的引用

lookup_array為必須項 , 表示要搜索的單元格區域

match_type為可選項 , 指定查找方式 (-1、0、1）, 默認值為1

• 1 或 省略 :

  • 表示match函數會查找＜或＝lookup_value的最大值

  • lookup_array中的值必須按照升序排列

    ? 例如： … -2, -1, 0, 1, 2, … , A-Z, FALSE, TRUE。

• 0

  • 表示match函數會查找＝lookup_value的第一個值
  • lookup_array中的值可以按照任何順序排列

• -1

  • 表示match函數會查找＞或＝lookup_value的最小值
  • lookup_array中的值必須按照降序排列

• index(array,row_num,column_num) - 在給定的單元格區域中,返回特定行列交叉處單元格的值或引用

  index(reference,row_num,column_num,area_num)

• indirect(ref_text,[a1]) - 返回文本字符串所指定的引用

其中,

? ref_text: 為必須項,表示對指定單元格的引用,如果ref_text不是合法的單元格引用,則返回錯誤值

? al: 為可選項, 用來指定包含在單元格ref_text中的引用類型

主要應用

生成二級下拉菜單(樣圖如下:)

方法一

? 第一步: 選擇范圍(B2~B6) -> 開始 -> 公式 -> 名稱管理器-> 新建 ;

? 第二步: 在 名稱 欄 輸入 序列1-> 確定 ,

? 不要關閉名稱管理器的頁面,繼續點擊 新建 -> 在 名稱 欄 輸入 序列2 -> 引用位置 欄 點擊 右邊的小標識 -> 選擇范圍(C2~C6) -> 再次點擊 右邊的標識 -> 確定

? 第三步: 選中 E3 單元格 -> 數據 -> 數據驗證 -> 數據驗證

? -> 在 驗證條件 允許 欄 選擇 序列

? -> 在 來源 欄內 輸入 序列1,序列2-> 確定

? 第四步: 選中 F3 單元格 -> 數據-> 數據驗證 -> 數據驗證

? -> 在 驗證條件 允許 欄 選擇 序列

? -> 在 來源 欄內 輸入 =INDIRECT($E$3) -> 確定

方法二(推薦使用)

? 第一步: 選中 H5 單元格 -> 數據 -> 數據驗證 -> 數據驗證

? -> 在 驗證條件 允許 欄 選擇 序列

? -> 在 來源 欄內 點擊 右邊的小標識 -> 選擇 A2~A5 , 再次點擊 右邊的小標識 -> 確定 , 這樣 一級下拉菜單(省) 就創建好了

? 第二步: 選中 C2~F14 單元格 -> 開始 -> 查找和選擇 -> 定位條件 -> 常量 -> 確定

? 這樣做的目的是剔除 C2~F14 中空白單元格

? 第三步: 公式 -> 根據所選內容創建-> 首行 -> 確定

? 第四步: 選中 I5 單元格 -> 數據 -> 數據驗證 -> 數據驗證

? -> 在 驗證條件 允許 欄 選擇 序列

? -> 在 來源 欄內 輸入 =INDIRECT($H$5) -> 確定

? 以上四步完成后,便可實現 二級下拉菜單(市) 與 一級下拉菜單(省) 的關聯

多表合并

• row([reference]) - 返回一個引用的行號

• column([reference]) - 返回一個引用的列號

• hlookup(lookup_value,table_array,row_index_num,range_lookup) - 搜索數組區域首行滿足條件的元素,確定待檢索單元格在區域中的序列號,再進一步返回選定單元格的值 <–> 水平方向查找

• choose(index_num,value1,value2, … ) - 根據給定的索引值,從參數串中選出相應值或操作

• find(find_text,within_text,start_num) - 返回一個字符串在另一個字符串中出現的起始位置(區分大小寫)

總結

以上是生活随笔為你收集整理的Excel-查找和引用函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。