第12講章便利的常用函數(shù)

1、第12講章：便利的常用函數(shù)【範例檔案：請搭配教學平臺中的教學範例檔案 chap121r.xls】本次講章您將學習到： l 函數(shù)的觀念l 常用數(shù)值運算函數(shù)l 常用邏輯函數(shù)l 常用日期函數(shù)l 常用資料查表函數(shù)l 其他函數(shù)l 複合函數(shù)應用函數(shù)的觀念在EXCEL中提供了許多功能強大又方便使用的現(xiàn)成運算功能的函數(shù)。何謂函數(shù)？函數(shù)就是由EXCEL的工程師為了能完成各種不同目的的特定計算或處理而事先設計好的公式，使用者爾後當需要使用該函數(shù)時，只要給定該函數(shù)所需的參數(shù)，則透過該函數(shù)固定程序的計算或處理後即可得到預期的結果。使用者無須深入記憶各種函數(shù)中公式的設計內容，只需瞭解各種函數(shù)的名稱、各種函數(shù)的功用、及各

2、種函數(shù)所需給定的參數(shù)等，即可方便的使用該函數(shù)。函數(shù)在使用上不僅無須設計複雜的公式即可輕鬆的得到結果，並且只要給定函數(shù)所需的參數(shù)，函數(shù)還可重複的使用，更增便利。事實上可以將函數(shù)視為是一種事先設計好的公式，所以函數(shù)可以應用搭配在一般的儲存格公式設計中。公式的設計如應用了函數(shù)，不僅可以使公式的設計更為簡易，同時更能達到強大運算的效果，真是快又有效率。當按下一般工具列中的函數(shù)精靈工具鈕時，會出現(xiàn)插入函數(shù)的對話框。在EXCEL中函數(shù)可分為下列幾類：財務、日期與時間、數(shù)學與三角函數(shù)、統(tǒng)計、檢視與參照、資料庫、文字、邏輯、與資訊等類別。特別在函數(shù)精靈中的函數(shù)類別除了上述的類別選項外，另外還有還可以選擇全部已

3、列出所有的函數(shù)清單的選項，與最近用過函數(shù)以列出最近用過的十個函數(shù)之選項。函數(shù)應用的類型函數(shù)的應用不僅可以單獨使用，並且還可以併聯(lián)應用與複合應用。所謂函數(shù)的併聯(lián)應用就是將某函數(shù)應用的結果與其他函數(shù)應用的結果加以結合運算。例如：=ROUND(G5,2)+SUM(A4:G4)。此公式中將G5儲存格的內容值進行四捨五入後再加上A4到G4儲存格的值。所謂函數(shù)的複合應用就是將某函數(shù)應用後的結果在當作其他函數(shù)的引數(shù)，簡單的說也就是函數(shù)中還有函數(shù)。例如：=ROUND(SUM(A4:G4),2)。此公式中就是將A4到G4儲存格的值做加總，加總後的結果再進行四捨五入。常用數(shù)值運算函數(shù)SUM函數(shù)l 函數(shù)功能：將各引

4、數(shù)的數(shù)值予以加總，以計算出引數(shù)總和。l 函數(shù)表示式：SUM（引數(shù)1，引數(shù)2，引數(shù)30）。引數(shù)的內容值若為邏輯值或文字則將略過不計。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇數(shù)值函數(shù)(I)練習的工作表，並在儲存格F5內輸入=SUM(C5:E5)或=SUM(C5,D5,E5)，並請拖曳完成F5:F8的儲存格計算。v 總分的計算公式=平時成績+期中考+期末考。v 補充說明：在函數(shù)中的引數(shù)可以利用單一儲存格，也可以利用儲存格範圍的表示方式來應用。SUMPRODUCT函數(shù)l 函數(shù)功能：傳回多個陣列或範圍中的各相對元素乘積之總和。l 函數(shù)表示式：SUMPRODUCT（

5、陣列1，陣列2，陣列30）。陣列個數(shù)最少為2，最多為30。此函數(shù)會計算在每一陣列相同位置的元素的乘積，然後加總這些乘積。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇在儲存格G5內輸入=SUMPRODUCT($C$3:$E$3,C5:E5)，並請拖曳完成G5:G8的儲存格計算。v 學期成績的計算公式：平時成績*30%+期中考*30%+期末考*40%。AVERAGE函數(shù)l 函數(shù)功能：將各引數(shù)的數(shù)值予以加總後在除以引數(shù)的個數(shù)，以計算出引數(shù)的算術平均值。l 函數(shù)表示式：AVERAGE（引數(shù)1，引數(shù)2，引數(shù)30）。引數(shù)可為名稱、陣列、或包含數(shù)值的儲存格參照。l 實例應

6、用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇在儲存格C9內輸入=AVERAGE(C5,C6,C7,C8)或=AVERAGE(C5:C8)，並請拖曳完成C9:G9的儲存格計算。ROUND函數(shù)l 函數(shù)功能：將引數(shù)的內容值依所指定的小數(shù)位數(shù)予以四捨五入。l 函數(shù)表示式：ROUND（引數(shù)，指定的小數(shù)位數(shù)）。其引數(shù)內容可為數(shù)值、儲存格位址。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇在儲存格H5內輸入=ROUND(G5,0)，並請拖曳完成H5:H8的儲存格計算。l 進階應用：在儲存格H9中，由於縱向的函數(shù)是ROUND()，橫向的函數(shù)是AVER

7、AGE()，所以H9儲存格中的公式可以應用到函數(shù)的複合應用方式。也就是同時應用ROUND()與AVERAGE()兩個函數(shù)。至於到底是ROUND()使用在AVERAGE()函數(shù)內或AVERAGE()使用在ROUND()函數(shù)內，則必須視你所需要的公式情況來設計，公式設計與函數(shù)應用的不同，其公式的意義與計算的結果也會不一樣，這就是公式設計的彈性變化與功能強大的地方。根據(jù)本範例表格的意義，儲存格H9的公式可以應用函數(shù)的複合應用，先計算平均值後再進行四捨五入的運算，所以H9儲存格內的公式則為=ROUND(AVERAGE(H5:H8),0)。ROUNDDOWN函數(shù)l 函數(shù)功能：將引數(shù)內容值依所指定的小數(shù)位

8、數(shù)予以無條件捨去。l 函數(shù)表示式：ROUNDDOWN（引數(shù)，指定的小數(shù)位數(shù)）。其引數(shù)內容可為數(shù)值、儲存格位址。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇在儲存格I5內輸入= ROUNDDOWN(G5,0)，並請拖曳完成I5:I8的儲存格計算。l 進階應用：根據(jù)本範例表格的意義，在儲存格I9中公式的設計可以輸入為= ROUNDDOWN(AVERAGE(I5:I8),0)。ROUNDUP函數(shù)l 函數(shù)功能：將引數(shù)內容值依所指定的小數(shù)位數(shù)予以無條件進位。l 函數(shù)表示式：ROUNDUP（引數(shù)，指定的小數(shù)位數(shù)）。其引數(shù)內容可為數(shù)值、儲存格位址。l 實例應用：【 插入圖

9、片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇在儲存格J5內輸入= ROUNDUP(G5,0)，並請拖曳完成J5:J8的儲存格計算。l 進階應用：根據(jù)本範例表格的意義，在儲存格J9中公式的設計可以輸入為= ROUNDUP(AVERAGE(J5:J8),0)。INT函數(shù)l 函數(shù)功能：將引數(shù)內容值予以無條件的捨去小數(shù)，只保留整數(shù)值。l 函數(shù)表示式：INT（引數(shù)）。其引數(shù)內容可為數(shù)值、儲存格位址。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇在儲存格K5內輸入= INT(G5)，並請拖曳完成K5:K8的儲存格計算。l 進階應用：根據(jù)本範例表格的意義，在儲存

10、格K9中公式的設計可以輸入為= INT(AVERAGE(K5:K8)。MAX函數(shù)l 函數(shù)功能：將引數(shù)中的最大值傳回，若引數(shù)的內容值為邏輯值與文字字串，則本函數(shù)將會省略傳回。l 函數(shù)表示式：MAX（引數(shù)1，引數(shù)2，引數(shù)30）。其引數(shù)內容可為數(shù)值、空白儲存格、邏輯植、文字字串。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇在儲存格C10內輸入=MAX(C5,C6,C7,C8)或=MAX(C5:C8)，並請拖曳完成C10:K10的儲存格計算。v 以平時成績?yōu)槔核奈煌瑢W成績最大值為92。MIN函數(shù)l 函數(shù)功能：將引數(shù)中的最小值傳回，若引數(shù)的內容值為邏輯值與文字字串，

11、則本函數(shù)將會省略傳回。l 函數(shù)表示式：MIN（引數(shù)1，引數(shù)2，引數(shù)30）。其引數(shù)內容可為數(shù)值、空白儲存格、邏輯植、文字字串。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇在儲存格C11內輸入=MIN(C5,C6,C7,C8)或=MIN(C5:C8)，並請拖曳完成C11:K11的儲存格計算。COUNTA函數(shù)l 函數(shù)功能：計算所有引數(shù)裏含有非空白資料的儲存格數(shù)目。l 函數(shù)表示式：COUNTA（引數(shù)1，引數(shù)2，引數(shù)30）。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的數(shù)值函數(shù)(2)練習工作表，並在儲存格H2內輸入=COUN

12、TA(C5:H5)。v 在本範例中，C5到H5儲存格所設定的資料型態(tài)為文字型態(tài)，並且在E5儲存格內故意不輸入資料（保持空白），所以COUNTA函數(shù)計算的儲存格數(shù)會扣除掉該儲存格。COUNT函數(shù)l 函數(shù)功能：計算所有引數(shù)裏含有數(shù)值資料的儲存格數(shù)目。l 函數(shù)表示式：COUNT（引數(shù)1，引數(shù)2，引數(shù)30）。其引數(shù)內容可為數(shù)值、儲存格位址。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的數(shù)值函數(shù)(2)練習工作表，並在儲存格H3內輸入=COUNT(C6:H6)。v 本函數(shù)只計算儲存格內為數(shù)值型態(tài)的儲存格個數(shù)，若儲存格內為文字型態(tài)或空白時，則COUNT函數(shù)計算的儲

13、存格數(shù)會扣除掉該儲存格。SMALL函數(shù)l 函數(shù)功能：將資料組中第K個的最小值傳回，例如：95、28、85、66，這四個資料組中，66是第2個最小值。l 函數(shù)表示式：SMALL（資料組，第K個最小順位）。其資料組陣列(Array)內容可為數(shù)值或參照範圍。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的數(shù)值函數(shù)(2)練習工作表，並在儲存格I6內輸入=(SUM(C6:H6)-SMALL(C6:H6,1)-SMALL(C6:H6,2)/(COUNT(C6:H6)-2)，並請拖曳完成I6:I11的儲存格計算。v 取4次最高分作業(yè)平均計算的公式：（所有小考成績總和

14、-最低的小考成績-次低的小考成績）/（小考次數(shù)-2）。v 本公式中SMALL(C6:H6,1)為找取最低的小考成績。COUNT(C6:H6)為計算小考的總次數(shù)。LARGE函數(shù)l 函數(shù)功能：將資料組中第K個的最大值傳回，例如：95、28、85、66，這四個資料組中，66是第3個最大值。l 函數(shù)表示式：LARGE（資料組，第K個最大順位）。其資料組陣列(Array)內容可為數(shù)值或參照範圍。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的數(shù)值函數(shù)(2)練習工作表，並在儲存格J6內輸入= (SUM(C6:H6)-LARGE(C6:H6,1)/(COUNT(C6

15、:H6)-1)，並請拖曳完成J6:J11的儲存格計算。v 取5次最低分作業(yè)平均計算的公式：（所有小考成績總和-最高的小考成績）/（小考次數(shù)-1）。v 本公式中LARGE(C6:H6,1)為找取最高的小考成績。COUNT(C6:H6)為計算小考的總次數(shù)。MOD函數(shù)l 函數(shù)功能：計算兩個數(shù)值相除後的餘數(shù)，例如：95除以10餘數(shù)為5。l 函數(shù)表示式：MOD（被除數(shù)，除數(shù)）。其引數(shù)內容可為數(shù)值、儲存格位址。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的數(shù)值函數(shù)(2)練習工作表，並在儲存格K6內輸入= MOD(J6,10) ，並請拖曳完成K6:K11的儲存格計

16、算。常用邏輯函數(shù)IF函數(shù)l 函數(shù)功能：根據(jù)所指定的條件進行邏輯判斷，若為真並根據(jù)判斷結果的真?zhèn)?，分別進行不同的運算或處理。l 函數(shù)表示式：IF（條件判斷式，為TRUE時的運算式，為FALSE時的運算式）。此處的運算式也就是一般儲存格內可以輸入的公式。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的邏輯函數(shù)練習工作表，並在儲存格F5內輸入= IF(E5>=60,"PASS","DOWN") ，並請拖曳完成F5:F9的儲存格計算。v 評語欄判斷的原則：若平均分數(shù)大於等於60分時則顯示PASS，若小於60時則顯示

17、PASS。SUMIF函數(shù)l 函數(shù)功能：將所指定的範圍儲存格根據(jù)所設定的判斷條件逐一進行判斷，若各儲存格內容值符合所設定的條件，則將所對應的儲存格內容加以加總。l 函數(shù)表示式：SUMIF（欲判斷的儲存格範圍，邏輯判斷式，所對應欲加總的儲存格範圍）。此處的對應的欲加總儲存格範圍可與欲判斷的儲存格範圍相同，也可不同，但範圍內的儲存格個數(shù)必須相同。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的邏輯函數(shù)練習工作表，並在儲存格G5內輸入=SUMIF(B5:D5,">=60",$B$3:$D$3)，並請拖曳完成G5:G9的儲存格計算，並將

18、G5:G9的儲存格格式自訂為”0學分”的格式。v 及格學分計算方式為分別判斷國文、數(shù)學、英文(B5:D5)三科是否大於等於60分。若國文大於等於60時則會加總所對應的學分儲存格($B$3)。v 本公式中B5:D5儲存格會依序的與$B$3:$D$3對應。COUNTIF函數(shù)l 函數(shù)功能：計算所指定的儲存格範圍內有符合所設定的判斷條件的儲存格個數(shù)。l 函數(shù)表示式：COUNTIF（欲判斷的儲存格範圍，邏輯判斷式）。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的邏輯函數(shù)練習工作表，並在儲存格H5內輸入=COUNTIF(B5:D5,"<60&qu

19、ot;)，並請拖曳完成H5:H9的儲存格計算，並將H5:H9的儲存格格式自訂為”0科”的格式。v 在H5儲存格中，COUNTIF函數(shù)會依序的判斷B5到D5儲存格的內容，若內容值小於60分時，則會自動加計一顆不及格。AND函數(shù)l 函數(shù)功能：當所有引數(shù)的邏輯值皆為TRUE時傳回TRUE，否則就傳回FALSE。l 函數(shù)表示式：AND（邏輯1，邏輯2，邏輯30）。邏輯1到邏輯30的結果可能為TRUE或FALSE的測試條件式，這些條件式的來源可以是邏輯值、陣列、或儲存格參照。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的邏輯函數(shù)練習工作表，並在儲存格I5內輸入

20、=IF(AND(B5>=60,C5>=60,D5>=60),"全PASS","有被當")，並請拖曳完成I5:I9的儲存格計算。v 結果I的判斷原則：當三科全部及格時才顯示"全PASS"，此種功能必須先利用AND函數(shù)判斷三科是否都及格後，再利用IF函數(shù)來判斷顯示所需的資訊。OR函數(shù)l 函數(shù)功能：當所有任何一個引數(shù)的邏輯值皆為TRUE時傳回TRUE，當全部為FALSE時就傳回FALSE。l 函數(shù)表示式：OR（邏輯1，邏輯2，邏輯30）。邏輯1到邏輯30的結果可能為TRUE或FALSE的測試條件式，這些條件式的來源可以是邏

21、輯值、陣列、或儲存格參照。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的邏輯函數(shù)練習工作表，並在儲存格J5內輸入=IF(OR(B5>=60,C5>=60,D5>=60),"有及格的","全被當")，並請拖曳完成J5:J9的儲存格計算。v 結果II的判斷原則：只要有任何一科以上及格(OR函數(shù))，就顯示"有及格的"。NOT函數(shù)l 函數(shù)功能：將引數(shù)的邏輯值予以反轉，也就是將邏輯值TRUE轉為FALSE，將邏輯值FALSE轉為TRUE。l 函數(shù)表示式：NOT（邏輯1）。邏輯1的結果

22、可能為TRUE或FALSE的測試條件式，這些條件式的來源可以是邏輯值、陣列、或儲存格參照。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的邏輯函數(shù)練習工作表，並在儲存格K5內輸入=IF(NOT(OR(B5>=60,C5>=60,D5>=60),"全被當","有及格的")，並請拖曳完成K5:K9的儲存格計算。v 結果III的判斷原則：只要有任何一科以上及格(OR函數(shù))，就顯示"有及格的"。v NOT函數(shù)就是將邏輯判斷後的結果變成相反的結果。所以雖然OR(B5>=60,C5

23、>=60,D5>=60)的結果為有任何一科以上及格，但再加上NOT函數(shù)時則變成全部不及格的意思。常用日期函數(shù)TODAY函數(shù)l 函數(shù)功能：傳回系統(tǒng)中所設定的今天日期資訊。l 函數(shù)表示式：TODAY（）。本函數(shù)由於資訊的取得是來自系統(tǒng)的資訊，所以不需設定任何的引數(shù)。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的日期函數(shù)練習工作表，並在儲存格F5內輸入=IF(TODAY()>E5,"已到期","未到期")，並請拖曳完成F5:F10的儲存格計算。請在儲存格G5內輸入=IF(TODAY()>E5,

24、TODAY()-E5,"-")，並請拖曳完成G5:G10的儲存格計算，並將G5:G10的儲存格格式自訂為”#0天”的格式。v 到期否欄位是以TODAY函數(shù)每天抓取系統(tǒng)的日期來判斷是否已經到期。v 到期日數(shù)的計算為：若該公式若判斷到已到期則直接將系統(tǒng)日期與到期日期相減，若未到期則顯示"-"。YEAR函數(shù)l 函數(shù)功能：求出日期序列數(shù)中的年份數(shù)，也就是傳回日期資料的年份部份。l 函數(shù)表示式：YEAR（日期序列數(shù)）。函數(shù)中的引數(shù)為日期序列數(shù)，並且將傳回的年份範圍為1900到2000。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇

25、本章範例的日期函數(shù)練習工作表，並在儲存格H5內輸入=YEAR(E5)，並請拖曳完成H5:H10的儲存格計算，並將H5:H10的儲存格格式自訂為”#0年”的格式。請在儲存格J5內輸入=YEAR(E5)-1911，並請拖曳完成J5:J10的儲存格計算，並將J5:J10的儲存格格式自訂為”民國”0”年”的格式。v 在EXCEL中的日期型態(tài)資料是以西元的年份來儲存，所以在到期民國年份欄則必須用西元減去1911，然後再設定為”民國”0”年”的格式。MONTH函數(shù)l 函數(shù)功能：求出日期序列數(shù)中的月份數(shù)，也就是傳回日期資料的月份部份。l 函數(shù)表示式：MONTH（日期序列數(shù)）。函數(shù)中的引數(shù)為日期序列數(shù)，並且將

26、傳回的月份範圍為1到12。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的日期函數(shù)練習工作表，並在儲存格K5內輸入=MONTH(E5)，並請拖曳完成K5:K10的儲存格計算，並將K5:K10的儲存格格式自訂為”#0月”的格式。DAY函數(shù)l 函數(shù)功能：求出日期序列數(shù)中該月的第幾天。l 函數(shù)表示式：DAY（日期序列數(shù)）。函數(shù)中的引數(shù)為日期序列數(shù)，並且將傳回的第幾天範圍為1到31。WEEKDAY函數(shù)l 函數(shù)功能：求出日期序列數(shù)中日期的星期值，也就是傳回星期一到星期日間的值。l 函數(shù)表示式：WEEKDAY（日期序列數(shù)，星期的類型）。函數(shù)中的引數(shù)為日期序列數(shù)，並

27、且將傳回的星期範圍為星期一到星期日。星期的類型為1時：傳回的值為17(分別表週日週六)。星期的類型為2時：傳回的值為17(分別表週一週日)。星期的類型為3時：傳回的值為06(分別表週一週日)。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的日期函數(shù)練習工作表，並在儲存格I5內輸入=WEEKDAY(E5,1)，並請拖曳完成I5:I10的儲存格計算，並將I5:I10的儲存格格式設定為日期格式的”星期二”的格式。v WEEKDAY 函數(shù)傳回的資料為數(shù)值資料，所以必須在搭配日期格式的設定才會顯示星期幾。常用資料查表函數(shù)CHOOSE函數(shù)l 函數(shù)功能：根據(jù)所指定的

28、索引值，將引數(shù)串列儲存格範圍內的所有內容值傳回，或是執(zhí)行動作。l 函數(shù)表示式：CHOOSE（索引值，引數(shù)串列1，引數(shù)串列2，引數(shù)串列29。函數(shù)中的索引值可以是數(shù)值、文字、公式、或儲存格參照，但其值必須為1到29的實數(shù)。函數(shù)中的引數(shù)串列則為儲存格範圍，EX：C1:C5。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的查表函數(shù)練習工作表，並在儲存格D10內輸入=SUM(CHOOSE(C10,$E$4:$E$7,$F$4:$F$7,$G$4:$G$7)，並請拖曳完成D10:D14的儲存格計算。v 累積折舊額的計算法：先利用CHOOSE函數(shù)來查詢不同機組所對應

29、的折舊種類，再依據(jù)所查到的折舊種類取得相對應的各月份折舊金額的儲存格範圍，例如：C10為1則取得$E$4:$E$7、C10為2則取得$F$4:$F$7以此類推，最後利用SUM函數(shù)將所查詢到的儲存格範圍內容值做加總。v CHOOSE函數(shù)會自動採取近似值搜尋法。VLOOKUP函數(shù)l 函數(shù)功能：在一清單的最左邊欄位中搜尋指定的特定值，若找到時，傳回找到的特定值那一列的指定欄所交集的儲存格內容值（也就是縱向查詢但傳回對應的橫向儲存格的值）。特別需注意的是，此表格或清單必須先經過遞增的方式排序。l 函數(shù)表示式：VLOOKUP（縱向搜尋值，被搜尋範圍，傳回搜尋範圍內第幾欄，搜尋方式）。函數(shù)中縱向搜尋值可以

30、是數(shù)值、文字、公式、或儲存格參照。函數(shù)中縱向搜尋值為要在搜尋範圍第一欄中搜尋的值。當搜尋值找到資料後會傳回該列與指定傳回欄所交集的儲存格。函數(shù)中搜尋方式有兩種：(1)FALSE為精確搜尋，表示需找到完全相同的資料才會傳回值，否則會出現(xiàn)找不到資料的#N/A訊息。(2)TRUE為近似搜尋，表示找不到完全相同的資料時，則會傳回與尋找資料較鄰近的資料值。特別需注意的是，此表格或清單必須先經過遞增的方式排序。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的查表函數(shù)練習工作表，並在儲存格F10內輸入=VLOOKUP(E10,$D$4:$G$7,C10+1,FALS

31、E)，並請拖曳完成F10:F14的儲存格計算。v 未提折舊欄位為根據(jù)停機月份的數(shù)字資料(本欄位有設定0”月”的格式)，去尋找不同機組在對應折舊種類（傳回搜尋範圍內第幾欄）中停機月份(縱向搜尋值)所需攤提的折舊。v 因為欲搜尋的停機月份資料在折舊參照表的縱向中，所以用VLOOKUP。v 資料搜尋範圍除了包括三種折舊的折舊金額欄外，還必須包括最左邊用來搜尋比對的月份欄。所以選定的範圍為$D$3:$G$7。v 本範例中，傳回搜尋範圍內第幾欄的引數(shù)公式設為C10+1，即因為搜尋範圍除了包括三種折舊的折舊金額欄外，還增加了最左邊用來搜尋比對的月份欄。v 本範例的搜尋方式是以FALSE精確搜尋方式，所以E

32、14儲存格內容中的搜尋值故意輸入為4.2時，則在折舊參照表中是無法精確的找到對應值，因此在F14儲存格內將會傳回#N/A的錯誤訊息。HLOOKUP函數(shù)l 函數(shù)功能：在一清單的最上一列中搜尋指定的特定值，若找到時，傳回找到的特定值那一欄的指定列所交集的儲存格內容值（也就是橫向查詢但傳回對應的縱向儲存格的值）。特別需注意的是，此表格或清單必須先經過遞增的方式排序。l 函數(shù)表示式：HLOOKUP（橫向搜尋值，被搜尋範圍，傳回搜尋範圍內第幾列，搜尋方式）。函數(shù)中橫向搜尋值可以是數(shù)值、文字、公式、或儲存格參照。函數(shù)中橫向搜尋值為要在搜尋範圍第一列中搜尋的值。當搜尋值找到資料後會傳回該欄與指定傳回列所交集

33、的儲存格。函數(shù)中搜尋方式有兩種：(1)FALSE為精確搜尋，表示需找到完全相同的資料才會傳回值，否則會出現(xiàn)找不到資料的#N/A訊息。(2)TRUE為近似搜尋，表示找不到完全相同的資料時，則會傳回與尋找資料較鄰近的資料值。特別需注意的是，此表格或清單必須先經過遞增的方式排序。l 實例應用：【 插入圖片 】 (插入加總結果，顯示公式，並拖曳範圍)v 請選擇本章範例的查表函數(shù)練習工作表，並在儲存格G10內輸入=HLOOKUP(C10,$D$3:$G$7,4,TRUE)，並請拖曳完成G10:G14的儲存格計算。v 3月份折舊額欄位為特定鎖定在3月份的資料，然後再根據(jù)各輪機組的折舊種類（橫向搜尋值）去尋找3月份（傳回搜尋範圍內第幾列）所應攤提的折舊額。 v 因為欲搜尋的折舊種類資料在折舊參照表的橫向中，所以用HLOOKUP。v 資料搜尋範圍除了包括一月到四月的折舊金額列外，還必須包括最上邊用來搜尋比對的折舊種類列。所以選定的範圍為$D$3:$G$7。v 本範例中，傳回搜尋範圍內第幾列的引數(shù)值社為4，即表示是搜尋範圍中的第四列資料，也就是3月份的資料。v 本範例的搜尋方式是以TRUE近似搜尋方式，所以C14儲存格內容中的搜尋值故意輸入為2.1時，則在折舊參照表中是仍能找到