检视参照与资料库函数.pdf

CHAPTER 檢視參照與資料庫函數 10-1 多重條件判斷 IFS() IFS( 邏輯測試1, 成立值1, [ 邏輯測試2, 成立值2], [ 邏輯測試3, 成立值3], …) IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…) 此為 Excel 2016 的新增函數 ，邏輯測試 1 、邏輯測試 2 、… ，為可以產 生 TRUE 或 FALSE 結果的任何條件式，最多可擁有 127 組測試若邏輯 測試 1 條件式成立 ， 即取成立值 1 之運算結果 ； 反之 ， 繼續判斷邏輯測試 2 ， 若 成 立，即 取成立值 2 之運算結果 ； 反之 ， 繼續判斷邏輯測試 3 ， 若成立， 即取成立值 3 之運算結果…… 若所有條件均不成立 ， 其回應值為 #NA! 錯誤 ， 欲避免此一錯誤 ， 應 安排至少一組會成立的結果 此函數之目的 ， 在縮減原單獨使用 IF() 函數時 ， 若為多重條件 ， 得組 合成很長之巢狀 IF() 。

求成績等級 例如，擬依下示條件分別給予成績之等級： 成績 等級 90～89 A 80～89 B 70～79 C 10-2  成績 等級 60～69 D 89～59 F 範例 Ch10.xlsx 『成績等級-IF 』 工作表之 D2 ， 以單一條件 IF() 函數進行判 斷成績等級，其運算式將為： =IF(C2>=90,“A“,IF(C2>=80,“B“,IF(C2>=70,“C“,IF(C2>=60,“D“,“F“)))) 一連串的 IF() ， 不僅數量多 ， 且左/ 右括號要完全配對 ， 有時還不太容易！ 但若改為使用多重條件 IFS() 函數（範例 Ch10.xlsx 『成績等級-IFS 』 工作表），其運算式將為： =IFS(C2>=90,“A“,C2>=80,“B“,C2>=70,“C“,C2>=60,“D“,C2=3000000,3%,IF(B2>=2000000,2%,IF(B2>=1500000,1%,IF(B2>=10 00000,0.8%,IF(B2>=500000,0.5%,IF(B2>300000,0.3%,0%)))))) 運算式很長，想取得正確結果，還真不太容易！ 但若改為使用多重條件 IFS() 函數（範例 Ch10.xlsx 『獎金比例-IFS 』 工作表），其運算式將為： =IFS(B2>=3000000,3%,B2>=2000000,2%,B2>=1500000,1%,B2>=1000000,0. 8%,B2>=500000,0.5%,B2>=300000,0.3%,B2>0,0%) 10-4  運算式會短一點點，但其實還是蠻長的！若能改為下節之 VLOOKUP() 函 數，將會更便捷許多。

10-2 垂直查表 VLOOKUP() VLOOKUP( 查表依據, 表格, 第幾欄,[ 是否不用找到完全相同值]) VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) 在一表格的最左欄中，尋找含查表依據的欄位，並傳回同一列中第幾 欄 所指定之儲存格內容式中，方括號所包圍之內容，表該部份可省略 表格是要在其中進行找尋資料的陣列範圍，且必須按其第一欄之內容 遞增排序 [是否不用找到完全相同值] 為一邏輯值，為 TRUE （或省略）時，如果 找不到完全符合的值 ， 會找出僅次於查表依據的值 當此引數值為 FALSE 時，必須找尋完全符合的值，如果找不到，則傳回錯誤值#N/A 不用找到完全相同值之實例 假定，員工之業績獎金係依其業績高低，給予不同之比例： 業績 獎金比例 0~299,999 0.0% 300000~499,999 0.3% 10-5 業績 獎金比例 500000~999,999 0.5% 1000000~1,499,999 0.8% 1500000~1,999,999 1.0% 2000000~2,999,999 2.0% 3000000~ 3.0% 茲將其對照表安排於範例『FunCh10- 參照.xlsx\VLOOKUP1 』之 A3:B9 ： 安排此一表格時，標題之文字內容並無作用，重點為代表業績及獎金比例 之數字 ，第一個 0 很重要 ， 很多使用者直接於 0 的位置上輸入 300,000 ，將 會使業績未滿 300,000 者 ， 找不到可用之獎金比例 ， 而顯示錯誤值 #N/A 。

此外，務必記得要依第一欄之業績內容遞增排序 假定，各員工之基本薪及業績資料為： 於 E 欄，擬依 D 欄之業績計算其業績獎金首先，於 E13 處可使用 =VLOOKUP(D13,$A$3:$B$9,2,TRUE) 10-6  依 D 欄之業績（查表依據），於 A3:B9 （表格）中找出適當（ 第 2 欄 ）之 獎金百分比： 最後一個引數為何要使用 TRUE ？這是因為業績內容很少恰好等於 A3:A9 的間距數字將其安排為 TRUE （或省略）時，於 A3:A9 找不到完 全符合 D 欄之業績值，將找出僅次於查表依據的值如：業績 1,025,000 者，不可能會給予與 1,500,000 同列之 1% 為獎金比例，而是找到僅次於 1,025,000 之 1,000,000 ，而回應與 1,000,000 同列之 0.8% 為其獎金比例 此外，安排業績與其獎金比例之表格原範圍為 A3:B9 ，為了方便向下 抄給其它儲存格，應記得將其安排為 $A$3:$B$9 於判斷查表所取得之獎金比例無誤之後，將其乘上業績： =VLOOKUP(D13,$A$3:$B$9,2,TRUE)*D13 即可算出業績獎金： 10-7 最後 ， 將 C 欄之基本薪加上 E 欄業績獎金 ， 即可獲致 F 欄之總所得： 續前例，假定所得稅率為： 所得 稅率 0~30,000 0.0% 30,001~50,000 3.0% 50,001~80,000 4.5% 80,001~100,000 8.0% 100,001~150,000 10.0% 150,001~200,000 16.0% 200,001~ 20.0% 試依查表取得適當稅率計算所得稅，並計算扣除所得稅後之淨所得： （範例『FunCh10- 參照.xlsx\VLOOKUP- 淨所得』） 10-17 10-4 查表 LOOKUP() 向量型 LOOKUP( 查表依據, 查表向量, 結果向量) LOOKUP(lookup_value,lookup_vector,result_vector) 所使用的兩個向量 ， 均為單列或單欄的陣列 。

本類型之 LOOKUP() 函 數，會 在查表向量中找尋查表依據之內容 ， 然後移到另一個結果向量中的同 一個位置上，傳回該儲存格的內容但應注意：  兩向量之儲存格個數應一致  查表向量之內容應事先遞增排序  如果於查表向量中無法找到查表依據之內容，將取用較小的一個值來 替代  如果查表依據 之內容小於整個查表向量 之所有值，將回應 #N/A 之錯誤值 如果 將成績高低分為下列幾組： 成績 組別 00~59 不及格 60~74 中等 75~84 高分 85~100 特優 將其內容安排於範例『FunCh10- 參照.xlsx\LOOKUP 向量』之 A1:B5 位置如此，A2:A5 即可當查表向量；B2:B5 即可當結果向量 10-18  假定，要將成績內容，於其備註欄上填入適當之組別文字，D9 處之 公式可為： =LOOKUP(C9,$A$2:$A$5,$B$2:$B$5) 由於其結果向量僅能為單列或單欄的陣列故若假定，要使用學號來找出 姓名、成績及備註欄內容就得標定不同之結果向量，如下表中 G10:G12 之內容將分別為： G10 =LOOKUP(G9,A9:A17,B9:B17) G11 =LOOKUP(G9,A9:A17,C9:C17) G12 =LOOKUP(G9,A9:A17,D9:D17) 分別使用三組不同的結果向量，才可找到適當之資料內容： 陣列型 LOOKUP( 查表依據, 陣列) LOOKUP(lookup_value,array) 本類型之 LOOKUP() 函數則會在陣列的第一列（或第一欄），搜尋指 定的查表依據，然後傳回其最後一列（或欄）的同一個位置上之儲存格內 容。

10-19 所以，同上例之要求，要使用此一類型之 LOOKUP() 函數，依成績高 低，於其備註欄上填入適當之組別文字，D9 處之公式將改為：（範例 『FunCh10- 參照.xlsx\LOOKUP 陣列』） =LOOKUP(C9,$A$2:$B$5) 將原分為兩個向量之內容，組合成單一陣列即可： 但由於此類型之 LOOKUP() 函數 ，不管陣列之欄列數多寡，將永遠傳回 最後一列（或欄）的對應內容 故若要於 A9:D17 表中，依學號找出姓名、 成績及備註欄內容就得標定不同之三組陣列，分別讓所要的內容安排於 最後一欄才可如下表中 G10:G12 之內容將分別為： G10 =LOOKUP(G9,A9:B17) G11 =LOOKUP(G9,A9:C17) G12 =LOOKUP(G9,A9:D17) 分別使用三組不同欄數之陣列 ， 每個陣列均讓所要找出之內容安排於最後 一欄，才可找到適當之資料內容： 10-20  10-5 索引 INDEX() 陣列型 INDEX( 陣列,[ 第幾列],[ 第幾欄]) INDEX(array,[row_num],[column_num]) 本類型之 INDEX() 函數，可於陣列中找出指定之欄與列交會處之儲 存格內容。

若該格為空白儲存格，將回應 0 式中，方括號所包圍之內容，表該部份可省略如果陣列只包含單一 的橫列或直欄時 ， 則所對應的[第幾列] 或[第幾欄] 是可省略的 如果省略了 [第幾列] 這個引數 ， 則一定要輸入[第幾欄] 如果省略了[第幾欄] 這個引數， 則一定要輸入[第幾列] 如果陣列含有多列多欄的元素，卻只單獨使用[第 幾列] 或[第幾欄] ，則將以陣列形式傳回陣列中的某一整列或整欄元素 如，於範例『FunCh10- 參照.xlsx\INDEX 陣列』之功課表中，於 B13 要找出星期二第三節之科目，可使用 =INDEX(B2:F9,B12,B11) 本類型之 INDEX() 函數，若省略標示第幾欄（或第幾列）將傳回該 欄 （或該列） 之全部內容 ， 也就是傳回一個陣列如，選取範例 『FunCh10- 參照.xlsx\INDEX 陣列』之 H1:H9 ，鍵入公式： =INDEX(A1:F9,,3) 。