馬玉傑 數據產品之道 1周前
Excel查找與引用函數主要框架如下:
01
—
ROW與COLUMN
ROW(reference) 返回一個引用的行號
ROWS(array) 返回數組或單元格區域中的行數
COLUMN(reference) 返回一個引用的列號
COLIMNS(array) 返回數組或單元格區域中的列數
例一:生成如下圖數列
例二:
02
—
VLOOKUP函數
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
=VLOOKUP ( 查找值 , 查找的區域 , 返回位置(列) , 查找方式 )
<strong>設置這種上下三角的方法:
文件>選項>自定義功能區>開發工具
<strong>精確查找(FALSE):
例一:
絕對引用($E$2:$G$6)快捷鍵:F4
例二:
其中,下拉三角選項的設置方法:數據>數據有效性>設置>系列>(選擇區域)
但是,當C4為空時,就會出現下面這種情況:比較難看
例三:
要求:根據不同的職務底薪和全勤金額填入左表
<strong>模糊查找(TRUE):
例一:
模糊查找,找出比它本身數字小的最大值。
例二:
<strong>VLOOKUP之通配符、向左查找:
IF({1,0},$E$8:$E$16,$D$8:$D$16) 選中,按F9
{"張三林","NED001";"李啟","NED002";"王生軍","NED003";"陳秀峰","NED004";"張梅","NED005";"胡蘋","NED006";"陳青","NED007";"劉思燕","NED008";"高妞兒","NED009"}
即,如果是1,則執行$D$8:$D$16),如果是0,則執行$E$8:$E$16 ,
從而使得兩列前後順序互換。
<strong>VLOOKUP進階:
03
—
HLOOKUP函數
=HLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
= HLOOKUP ( 查找的值 , 查找的區域 , 返回的行號 , 查找方式 )
04
—
LOOKUP函數
向量用法:既能行查詢,又能列查詢,但只能是單行或者單列。
不管序列升序與否,LOOKUP都以為是升序。
9E+307,為EXCEL裡面的最大值。
<strong>數組用法:
05
—
CHOOSE函數
CHOOSE(index_num, value1, [value2], ...) 從參數列表中選擇並返回一個值
1.第一參數的值不能小於1或者大於29
2.第一參數是小數,則在使用前將被截尾取整
例一:
例二:
06
—
MATCH函數
返回的是look_value在lookup_array中的位置,是一個數值。
07
—
INDEX函數
例一:
例二:
為去掉無效值,使用IF函數。
例三:
例四:
08
—
OFFSET函數
=OFFSET ( reference , rows , cols , height , width)
例一:
例二:
備註:
- 使用逗號分隔橫向數組元素,使用分號分隔縱向數組元素。
閱讀更多 薄荷草1977 的文章