[EXCEL] 查表函数
最近,OFFICE 2016刚改版更新,微软大力广告此次改版中,EXCEL之新增几个函数,尤其是IFS、SWITCH,我在日前也有介绍新增函数消息http://sxt66329.pixnet.net/blog/post/446143253-excel-2013%E5%8F%8A2016%E6%96%B0%E5%A2%9E%E5%87%BD%E6%95%B8%E4%B8%80%E9%A9%8D%E8%A1%A8。
本文,就用IFS、SWTICH与其他查表函数进行比较是否好用?我用以下图表,使用IF、VLOOKUP、LOOKUP、CHOOSE & INT、CHOOSE & MATCH、MATCH & INDEX、IFS及SWITCH等函数进行比较。
一、查询值在区间内
IF函数, IF (项目为 true,则,然后进行操作,否则进行其他操作)
所以,我先比较"<1000",若是就是D等级,不是再用IF判断"<2000","<3000",都不是就是A等级了,公式则写成 =IF(C7<A3,C2,IF(C7<A4,C3,IF(C7<A5,C4,C5)))
VLOOKUP函数,=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
lookup_value
=> 就是查询值 2500
table_array => 就是以下图表
下限值 | 上限值 | 等级 |
999 | D | |
1000 | 1999 | C |
2000 | 2999 | B |
3000 | A |
col_index_num => 就是找到的值欲回传的栏位,依本范例就是等级栏位,即为" B "。
range_lookup => 就是寻找lookup_value是用以指定要 VLOOKUP 寻找完全符合或大约符合值的逻辑值,FALSE 是搜寻精确值,TRUE 是搜寻最接近值。本例为TRUE,因为,精确比对是找不到2500的值。
公式则写成 =VLOOKUP($C$7,A2:C5,3,TRUE)
LOOKUP函数, LOOKUP(lookup_value, lookup_vector, [result_vector])
用等级查表,即模糊比对查表时,用LOOKUP是不错的好方法,其中
lookup_value=>就是查询值 2500
lookup_vector=>就是以下图表。
下限值 | 上限值 | 等级 |
999 | D | |
1000 | 1999 | C |
2000 | 2999 | B |
3000 | A |
故公式写成 =LOOKUP(C7,A2:C5)
CHOOSE及INT函数
CHOOSE(index_num, value1, [value2], ...)
index_num =>就是查询值,但该值需为1,2,3...,以利回传公式之后
value1, [value2], ...
但是,本范例查询值为2500,且每个级距是很简单来转换值为1,2,3....,故用INT及除数公式来转换,公式则写成 =CHOOSE((INT(C7/1000)+1),C2,C3,C4,C5)
CHOOSE及MATCH函数
MATCH(lookup_value, lookup_array, [match_type])
MATCH 会搜寻储存格范围中的指定项目,并传回该项目于该范围中的相对位置 ,此方法让CHOOSE查询值,不藉用INT等公式,MATCH回传就是value1, [value2], ...所需要第几笔的值1,2,3...
lookup_value =>就是查询值 2500
lookup_array=>就是以下图表。
下限值 | 上限值 | 等级 |
999 | D | |
1000 | 1999 | C |
2000 | 2999 | B |
3000 | A |
match_type分为以下说明:
1或省略 MATCH 会寻找小于或等于 lookup_value 的最大值。lookup_array 引数内的值必须以递增次序排列
0 MATCH 会寻找完全等于 lookup_value 的第一个值。lookup_array 引数内的值可以依任意次序排列
-1 MATCH 会寻找大于或等于 lookup_value 的最小值。lookup_array 引数内的值必须以递减次序排列
以本范例来说,我用1,用MATCH函数查询2500是第几笔,因此公式为 =CHOOSE((MATCH(C7,A2:A5,1)),C2,C3,C4,C5)
MATCH及INDEX函数
INDEX(array, row_num, [column_num])
INDEX函数会传回表格或范围内的某个值或值,其中,
array=>就是以下图表。
下限值 | 上限值 | 等级 |
999 | D | |
1000 | 1999 | C |
2000 | 2999 | B |
3000 | A |
row_num=>透过MATCH函数查询
column_num=>等级栏位
因此,MATCH及INDEX函数公式为 =INDEX(A2:C5,(MATCH(C7,A2:A5,1)),3)
IFS函数
此函数为2016版2016.03更新以后才会有, IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)
logical_test1 =>条件1之判断式
value_if_true1 =>符合条件1之判断式所需回传之值
其他依此类推,若找不到,回传 N/A! 值,因此,公式为 =IFS(C7>B4,C5,C7>B3,C4,C7>B2,B2,C7<A3,C2)
SWITCH函数
此函数为2016版2016.03更新以后才会有,Switch(expr-1, value-1 ,R_value-1[, expr-2, value-2 ,R_value-2] … [, expr-n, value-n ] )
expr-1=> 就是查询值,
若查询值为value-1之值,传回R_value-1值;不等value-1再查value-2,若都没有,回传 N/A! 值,故公式为 =SWITCH(INT(C7/1000)*1000,A5,C5,A4,C4,A3,C3,C2)
注:Switch函数之说明,官方未写明 R_value-1,个人用OFFICE ONLINE版本测试,确认有 R_value-1之存在。
因此,上述我用下图片再统一说明
二、查询值在区间两侧
当然,公式亦考虑极端值情形,我用以下图片进行测试
1.极小值之公式呈现结果
发现VLOOKUP、LOOKUP、CHOOSE&INT、CHOOSE&MATCH、MATCH&INDEX是找不到,出现错误。故公式仍需调整,即代表运用此函数时,要注意极端值。
2.极大值之公式呈现结果
发现CHOOSE&INT是找不到,出现错误。故公式仍需调整,即代表运用此函数时,要注意极端值。此外,IFS、SWITCH函数若在2016以下版本使用,其结果仍是有效,公式为=_xlfn.IFS(C30>B27,C28,C30>B26,C27,C30>B25,C26,C30<A26,C25),若是,你想COPY公式进行套用或是修改等,就会产生错误值,这是许多朋友想了解新函数是否套用旧版时之情形。
三、结论
不管新萧版本之函数,可以让使用者正确使用,极端值不易出错,就是不错的函数,否则,使用时状况不断,查错时间长就事倍功半了,希望你可以找到好用函数。