Excel函數(shù)的高級應用.ppt

1、Excel函數(shù)的高級應用,第一章 高級應用基礎,工作簿和工作表的區(qū)別 工作表的重命名 分數(shù)及文本輸入，不同單元格輸入同一內(nèi)容 單元格的三種引用方式 文本和數(shù)據(jù)合并方法 數(shù)據(jù)篩選 數(shù)據(jù)有效性 打印區(qū)域設置,第二章 數(shù)據(jù)的鏈接及應用,1、跨工作表引用數(shù)據(jù) 數(shù)據(jù)表名！單元格名，也可用復制粘貼，然后單元格旁邊小圖標，選擇“鏈接單元格” 2、在實例中的應用,第一節(jié) 工作薄及工作表相關,工作薄是由工作表構(gòu)成，工作表是由行和列構(gòu)成。 工作表的插入方法 工作表重命名、復制、移動 回到章節(jié),第二節(jié) 一些數(shù)據(jù)的輸入,1、分數(shù)的錄入 0+空格+分數(shù) 如：0 1/5 2、數(shù)字文本錄入 +數(shù)字，如34022219990

2、9091112 3、不同的單元格輸入同一內(nèi)容 用CTRL鍵選擇不同的單元格，然后在編輯框錄入數(shù)據(jù)，錄入完成按CTRL+對號 回到章節(jié),第三節(jié) 單元格的三種引用方式,1、相對引用 如單元格A1 2、絕對引用 如單元格$A$1，讀著絕對的A列，絕對的1行 3、混合引用 如單元格A$1讀著相對的A列，絕對的1行 回到章節(jié),第四節(jié) 文本和數(shù)據(jù)合并方法,1、兩列文本的合并 如A1列數(shù)據(jù)為“張”，B1列數(shù)據(jù)為”三”，兩列數(shù)據(jù)在C1列，在C1列輸入：=A1空格&空格 B1 2、數(shù)據(jù)合并 如A1列數(shù)據(jù)為：2,B1數(shù)據(jù)為4,要把B1列數(shù)據(jù)加入到A1列，復制B1列，點A1列，點編輯-選擇性粘貼，選擇相關選項。 回

3、到章節(jié),第五節(jié) 數(shù)據(jù)篩選,1、自動篩選 2、高級篩選 二個條件以上的篩選，有“和”與“或”兩種情況。 回到章節(jié),第六節(jié) 數(shù)據(jù)有效性,1、點“數(shù)據(jù)”“有效性” 點“設置”，“輸入信息”，“出錯警告”等，進行相應的設定 回到章節(jié),第七節(jié) 打印區(qū)域設置,點“文件”“頁面設置”“工作表”“頂端標題行”，然后選擇要在每一頁中要出現(xiàn)的標題行，如1和2兩行。 回到章節(jié),第二章 常用函數(shù)及應用,1、ABS函數(shù) 2、AND函數(shù) 3、AVERAGE函數(shù) 4、COLUMN 函數(shù) 5、CONCATENATE函數(shù) 6、COUNTIF函數(shù) 7、DATE函數(shù) 8、函數(shù)名稱：DATEDIF 9、DAY函數(shù) 10、DCOUNT

4、函數(shù) 11、FREQUENCY函數(shù) 12、IF函數(shù) 13、INT函數(shù) 14、LEFT函數(shù) 15、LEN函數(shù),16、MATCH函數(shù) 17、MAX函數(shù) 18、MID函數(shù) 19、MIN函數(shù) 20、MOD函數(shù) 21、MONTH函數(shù) 22、NOW函數(shù) 23、RANK函數(shù) 24、RIGHT函數(shù) 25、SUMIF函數(shù) 26、VLOOKUP函數(shù),一 ABS函數(shù),函數(shù)名稱：ABS 主要功能：求出相應數(shù)字的絕對值。 使用格式：ABS(number) 參數(shù)說明：number代表需要求絕對值的數(shù)值或引用的單元格。 應用舉例：如果在B2單元格中輸入公式：=ABS(A2)，則在A2單元格中無論輸入正數(shù)（如100）還是負數(shù)

5、（如-100），B2中均顯示出正數(shù)（如100）。 特別提醒：如果number參數(shù)不是數(shù)值，而是一些字符（如A等），則B2中返回錯誤值“#VALUE！”。 回到首頁,二 AND函數(shù),函數(shù)名稱：AND 主要功能：返回邏輯值：如果所有參數(shù)值均為邏輯“真（TRUE）”，則返回邏輯“真（TRUE）”，反之返回邏輯“假（FALSE）”。 使用格式：AND(logical1,logical2, .) 參數(shù)說明：Logical1,Logical2,Logical3：表示待測試的條件值或表達式，最多這30個。 應用舉例：在C5單元格輸入公式：=AND(A5=60,B5=60)，確認。如果C5中返回TRUE，說明

6、A5和B5中的數(shù)值均大于等于60，如果返回FALSE，說明A5和B5中的數(shù)值至少有一個小于60。 特別提醒：如果指定的邏輯條件參數(shù)中包含非邏輯值時，則函數(shù)返回錯誤值“#VALUE!”或“#NAME”。 回到首頁,三 AVERAGE函數(shù),函數(shù)名稱：AVERAGE 主要功能：求出所有參數(shù)的算術平均值。 使用格式：AVERAGE(number1,number2,) 參數(shù)說明：number1,number2,：需要求平均值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。 應用舉例：在B8單元格中輸入公式：=AVERAGE(B7:D7,F7:H7,7,8)，確認后，即可求出B7至D7區(qū)域、F7至H7區(qū)域中

7、的數(shù)值和7、8的平均值。 特別提醒：如果引用區(qū)域中包含“0”值單元格，則計算在內(nèi)；如果引用區(qū)域中包含空白或字符單元格，則不計算在內(nèi)。 回到首頁,四 COLUMN 函數(shù),函數(shù)名稱：COLUMN 主要功能：顯示所引用單元格的列標號值。 使用格式：COLUMN(reference) 參數(shù)說明：reference為引用的單元格。 應用舉例：在C11單元格中輸入公式：=COLUMN(B11)，確認后顯示為2（即B列）。 回到首頁,五 CONCATENATE函數(shù),函數(shù)名稱：CONCATENATE 主要功能：將多個字符文本或單元格中的數(shù)據(jù)連接在一起，顯示在一個單元格中。 使用格式：CONCATENATE(T

8、ext1，Text) 參數(shù)說明：Text1、Text2為需要連接的字符文本或引用的單元格。 應用舉例：在C14單元格中輸入公式：=CONCATENATE(A14,“”,B14,“.com”)，確認后，即可將A14單元格中字符、B14單元格中的字符和.com連接成一個整體，顯示在C14單元格中。 特別提醒：如果參數(shù)不是引用的單元格，且為文本格式的，請給參數(shù)加上英文狀態(tài)下的雙引號，如果將上述公式改為：=A14&“”&B14&“.com”，也能達到相同的目的。 回到首頁,六 COUNTIF函數(shù),函數(shù)名稱：COUNTIF 主要功能：統(tǒng)計某個單元格區(qū)域中符合指定條件的單元格數(shù)目。 使用格式：COUNTI

9、F(Range,Criteria) 參數(shù)說明：Range代表要統(tǒng)計的單元格區(qū)域；Criteria表示指定的條件表達式。 應用舉例：在C17單元格中輸入公式：=COUNTIF(B1:B13,“=80”)，確認后，即可統(tǒng)計出B1至B13單元格區(qū)域中，數(shù)值大于等于80的單元格數(shù)目。 特別提醒：允許引用的單元格區(qū)域中有空白單元格出現(xiàn)。 回到首頁,七 DATE函數(shù),函數(shù)名稱：DATE 主要功能：給出指定數(shù)值的日期。 使用格式：DATE(year,month,day) 參數(shù)說明：year為指定的年份數(shù)值（小于9999）；month為指定的月份數(shù)值（可以大于12）；day為指定的天數(shù)。 應用舉例：在C20單

10、元格中輸入公式：=DATE(2003,13,35)，確認后，顯示出2004-2-4。 特別提醒：由于上述公式中，月份為13，多了一個月，順延至2004年1月；天數(shù)為35，比2004年1月的實際天數(shù)又多了4天，故又順延至2004年2月4日。回到首頁,八 DATEDIF,主要功能：計算返回兩個日期參數(shù)的差值。使用格式：=DATEDIF(date1,date2,“y”)、=DATEDIF(date1,date2,“m”)、=DATEDIF(date1,date2,“d”)參數(shù)說明：date1代表前面一個日期，date2代表后面一個日期；y（m、d）要求返回兩個日期相差的年（月、天）數(shù)。應用舉例：在C

11、23單元格中輸入公式：=DATEDIF(A23,TODAY(),“y”)，確認后返回系統(tǒng)當前日期用TODAY()表示）與A23單元格中日期的差值，并返回相差的年數(shù)。特別提醒：這是Excel中的一個隱藏函數(shù)，在函數(shù)向?qū)е惺钦也坏降?，可以直接輸入使用，對于計算年齡、工齡等非常有效。 回到首頁,九 DAY函數(shù),函數(shù)名稱：DAY主要功能：求出指定日期或引用單元格中的日期的天數(shù)。使用格式：DAY(serial_number)參數(shù)說明：serial_number代表指定的日期或引用的單元格。應用舉例：輸入公式：=DAY(“2003-12-18”)，確認后，顯示出18。特別提醒：如果是給定的日期，請包含在英

12、文雙引號中。 回到首頁,十 DCOUNT函數(shù),函數(shù)名稱：DCOUNT主要功能：返回數(shù)據(jù)庫或列表的列中滿足指定條件并且包含數(shù)字的單元格數(shù)目。使用格式：DCOUNT(database,field,criteria)參數(shù)說明：Database表示需要統(tǒng)計的單元格區(qū)域；Field表示函數(shù)所使用的數(shù)據(jù)列（在第一行必須要有標志項）；Criteria包含條件的單元格區(qū)域。應用舉例：如圖1所示，在F4單元格中輸入公式：=DCOUNT(A1:D11,“語文”,F1:G2)，確認后即可求出“語文”列中，成績大于等于70，而小于80的數(shù)值單元格數(shù)目（相當于分數(shù)段人數(shù)）。特別提醒：如果將上述公式修改為：=DCOUNT

13、(A1:D11,F1:G2)，也可以達到相同目的。 回到首頁,十一 FREQUENCY函數(shù),函數(shù)名稱：FREQUENCY主要功能：以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。使用格式：FREQUENCY(data_array,bins_array)參數(shù)說明：Data_array表示用來計算頻率的一組數(shù)據(jù)或單元格區(qū)域；Bins_array表示為前面數(shù)組進行分隔一列數(shù)值。應用舉例：如圖2所示，同時選中B32至B36單元格區(qū)域，輸入公式：=FREQUENCY(B2:B31,D2:D36)，輸入完成后按下“Ctrl+Shift+Enter”組合鍵進行確認，即可求出B2至B31區(qū)域中，按D2至D36區(qū)域

14、進行分隔的各段數(shù)值的出現(xiàn)頻率數(shù)目（相當于統(tǒng)計各分數(shù)段人數(shù)）。特別提醒：上述輸入的是一個數(shù)組公式，輸入完成后，需要通過按“Ctrl+Shift+Enter”組合鍵進行確認，確認后公式兩端出現(xiàn)一對大括號（），此大括號不能直接輸入。 回到首頁,十二 IF函數(shù),函數(shù)名稱：IF主要功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應的內(nèi)容。使用格式：=IF(Logical,Value_if_true,Value_if_false)參數(shù)說明：Logical代表邏輯判斷表達式；Value_if_true表示當判斷條件為邏輯“真（TRUE）”時的顯示內(nèi)容，如果忽略返回“TRUE”；Value_if_false

15、表示當判斷條件為邏輯“假（FALSE）”時的顯示內(nèi)容，如果忽略返回“FALSE”。應用舉例：在C29單元格中輸入公式：=IF(C26=18,“符合要求”,“不符合要求”)，確信以后，如果C26單元格中的數(shù)值大于或等于18，則C29單元格顯示“符合要求”字樣，反之顯示“不符合要求”字樣。特別提醒：本文中類似“在C29單元格中輸入公式”中指定的單元格，讀者在使用時，并不需要受其約束，此處只是配合本文所附的實例需要而給出的相應單元格，具體請大家參考所附的實例文件。 回到首頁,十三 INT函數(shù),函數(shù)名稱：INT主要功能：將數(shù)值向下取整為最接近的整數(shù)。使用格式：INT(number)參數(shù)說明：numbe

16、r表示需要取整的數(shù)值或包含數(shù)值的引用單元格。應用舉例：輸入公式：=INT(18.89)，確認后顯示出18。特別提醒：在取整時，不進行四舍五入；如果輸入的公式為=INT(-18.89)，則返回結(jié)果為-19。 回到首頁,十四 LEFT函數(shù),函數(shù)名稱：LEFT主要功能：從一個文本字符串的第一個字符開始，截取指定數(shù)目的字符。使用格式：LEFT(text,num_chars)參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。應用舉例：假定A38單元格中保存了“我喜歡天極網(wǎng)”的字符串，我們在C38單元格中輸入公式：=LEFT(A38,3)，確認后即顯示出“我喜歡”的字符。特別提

17、醒：此函數(shù)名的英文意思為“左”，即從左邊截取，Excel很多函數(shù)都取其英文的意思。 回到首頁,十五 LEN函數(shù),函數(shù)名稱：LEN主要功能：統(tǒng)計文本字符串中字符數(shù)目。使用格式：LEN(text)參數(shù)說明：text表示要統(tǒng)計的文本字符串。應用舉例：假定A41單元格中保存了“我今年28歲”的字符串，我們在C40單元格中輸入公式：=LEN(A40)，確認后即顯示出統(tǒng)計結(jié)果“6”。特別提醒：LEN要統(tǒng)計時，無論中全角字符，還是半角字符，每個字符均計為“1”；與之相對應的一個函數(shù)LENB，在統(tǒng)計時半角字符計為“1”，全角字符計為“2”。 回到首頁,十六 MATCH函數(shù),函數(shù)名稱：MATCH主要功能：返回在

18、指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應位置。使用格式：MATCH(lookup_value,lookup_array,match_type)參數(shù)說明：Lookup_value代表需要在數(shù)據(jù)表中查找的數(shù)值；Lookup_array表示可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域；Match_type表示查找方式的值（-1、0或1）。如果match_type為-1，查找大于或等于 lookup_value的最小數(shù)值，Lookup_array 必須按降序排列；如果match_type為1，查找小于或等于 lookup_value 的最大數(shù)值，Lookup_array 必須按升序排列；如果match_t

19、ype為0，查找等于lookup_value 的第一個數(shù)值，Lookup_array 可以按任何順序排列；如果省略match_type，則默認為1。應用舉例：如圖4所示，在F2單元格中輸入公式：=MATCH(E2,B1:B11,0)，確認后則返回查找的結(jié)果“9”。特別提醒：Lookup_array只能為一列或一行 回到首頁,十七 MAX函數(shù),函數(shù)名稱：MAX主要功能：求出一組數(shù)中的最大值。使用格式：MAX(number1,number2)參數(shù)說明：number1,number2代表需要求最大值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。應用舉例：輸入公式：=MAX(E44:J44,7,8,9

20、,10)，確認后即可顯示出E44至J44單元和區(qū)域和數(shù)值7，8，9，10中的最大值。特別提醒：如果參數(shù)中有文本或邏輯值，則忽略。 回到首頁,十八 MID函數(shù),函數(shù)名稱：MID主要功能：從一個文本字符串的指定位置開始，截取指定數(shù)目的字符。使用格式：MID(text,start_num,num_chars)參數(shù)說明：text代表一個文本字符串；start_num表示指定的起始位置；num_chars表示要截取的數(shù)目。應用舉例：假定A47單元格中保存了“我喜歡天極網(wǎng)”的字符串，我們在C47單元格中輸入公式：=MID(A47,4,3)，確認后即顯示出“天極網(wǎng)”的字符。特別提醒：公式中各參數(shù)間，要用英文

21、狀態(tài)下的逗號“,”隔開。 回到首頁,十九 MIN函數(shù),函數(shù)名稱：MIN主要功能：求出一組數(shù)中的最小值。使用格式：MIN(number1,number2)參數(shù)說明：number1,number2代表需要求最小值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。應用舉例：輸入公式：=MIN(E44:J44,7,8,9,10)，確認后即可顯示出E44至J44單元和區(qū)域和數(shù)值7，8，9，10中的最小值。特別提醒：如果參數(shù)中有文本或邏輯值，則忽略。 回到首頁,二十 MOD函數(shù),函數(shù)名稱：MOD主要功能：求出兩數(shù)相除的余數(shù)。使用格式：MOD(number,divisor)參數(shù)說明：number代表被除數(shù)；di

22、visor代表除數(shù)。應用舉例：輸入公式：=MOD(13,4)，確認后顯示出結(jié)果“1”。特別提醒：如果divisor參數(shù)為零，則顯示錯誤值“#DIV/0!”；MOD函數(shù)可以借用函數(shù)INT來表示：上述公式可以修改為：=13-4*INT(13/4)。 回到首頁,二十一 MONTH函數(shù),函數(shù)名稱：MONTH主要功能：求出指定日期或引用單元格中的日期的月份。使用格式：MONTH(serial_number)參數(shù)說明：serial_number代表指定的日期或引用的單元格。應用舉例：輸入公式：=MONTH(“2003-12-18”)，確認后，顯示出11。特別提醒：如果是給定的日期，請包含在英文雙引號中；如

23、果將上述公式修改為：=YEAR(“2003-12-18”)，則返回年份對應的值“2003”。 回到首頁,二十二 NOW函數(shù),函數(shù)名稱：NOW主要功能：給出當前系統(tǒng)日期和時間。使用格式：NOW()參數(shù)說明：該函數(shù)不需要參數(shù)。應用舉例：輸入公式：=NOW()，確認后即刻顯示出當前系統(tǒng)日期和時間。如果系統(tǒng)日期和時間發(fā)生了改變，只要按一下F9功能鍵，即可讓其隨之改變。特別提醒：顯示出來的日期和時間格式，可以通過單元格格式進行重新設置。 回到首頁,二十三 RANK函數(shù),函數(shù)名稱：RANK主要功能：返回某一數(shù)值在一列數(shù)值中的相對于其他數(shù)值的排位。使用格式：RANK（Number,ref,order）參數(shù)說

24、明：Number代表需要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域；order代表排序方式參數(shù)（如果為“0”或者忽略，則按降序排名，即數(shù)值越大，排名結(jié)果數(shù)值越??；如果為非“0”值，則按升序排名，即數(shù)值越大，排名結(jié)果數(shù)值越大；）。應用舉例：如在C2單元格中輸入公式：=RANK(B2,$B$2:$B$31,0)，確認后即可得出丁1同學的語文成績在全班成績中的排名結(jié)果。特別提醒：在上述公式中，我們讓Number參數(shù)采取了相對引用形式，而讓ref參數(shù)采取了絕對引用形式（增加了一個“$”符號），這樣設置后，選中C2單元格，將鼠標移至該單元格右下角，成細十字線狀時（通常稱之為“填充柄”），按住左鍵向下

25、拖拉，即可將上述公式快速復制到C列下面的單元格中，完成其他同學語文成績的排名統(tǒng)計。 回到首頁,二十四 RIGHT函數(shù),函數(shù)名稱：RIGHT主要功能：從一個文本字符串的最后一個字符開始，截取指定數(shù)目的字符。使用格式：RIGHT(text,num_chars)參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。應用舉例：假定A65單元格中保存了“我喜歡天極網(wǎng)”的字符串，我們在C65單元格中輸入公式：=RIGHT(A65,3)，確認后即顯示出“天極網(wǎng)”的字符。特別提醒：Num_chars參數(shù)必須大于或等于0，如果忽略，則默認其為1；如果num_chars參數(shù)大于文本長度，則

26、函數(shù)返回整個文本。,二十五 SUMIF函數(shù),SUMIF函數(shù)函數(shù)名稱：SUMIF主要功能：計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。使用格式：SUMIF（Range,Criteria,Sum_Range）參數(shù)說明：Range代表條件判斷的單元格區(qū)域；Criteria為指定條件表達式；Sum_Range代表需要計算的數(shù)值所在的單元格區(qū)域。應用舉例：在D64單元格中輸入公式：=SUMIF(C2:C63,男,D2:D63)，確認后即可求出“男”生的語文成績和。特別提醒：如果把上述公式修改為：=SUMIF(C2:C63,女,D2:D63)，即可求出“女”生的語文成績和；其中“男”和“女”由于是文本型的，需

27、要放在英文狀態(tài)下的雙引號（男、女）中。,二十六 VLOOKUP函數(shù),函數(shù)名稱：VLOOKUP主要功能：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回數(shù)據(jù)表當前行中指定列處的數(shù)值。使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)參數(shù)說明：Lookup_value代表需要查找的數(shù)值；Table_array代表需要在其中查找數(shù)據(jù)的單元格區(qū)域；Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號（當Col_index_num為2時,返回table_array第2列中的數(shù)值，為3時，返回第3列的值）；Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯誤值#N/A。應用舉例：參見圖7，我們在D65單元格中輸入公式：=VLOOKUP(B65,B2:D63,3,FALSE)，確認后，只要在B65單元格中輸入一個