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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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