sxt66329的部落格

最近，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 => 就是以下圖表

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=>就是以下圖表。

故公式寫成  =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=>就是以下圖表。

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=>就是以下圖表。

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公式進行套用或是修改等，就會產生錯誤值，這是許多朋友想了解新函數是否套用舊版時之情形。

三、結論

不管新蕭版本之函數，可以讓使用者正確使用，極端值不易出錯，就是不錯的函數，否則，使用時狀況不斷，查錯時間長就事倍功半了，希望你可以找到好用函數。