第01講_Excel公式和函數(shù)使用_1

1、辦公軟件的高級(jí)應(yīng)用之辦公軟件的高級(jí)應(yīng)用之Excel Excel公式和函數(shù)使用公式和函數(shù)使用 公式和函數(shù)是Excel的靈魂。Excel表格與其他表格最本質(zhì)的區(qū)別就在于，用戶可以在Excel表格中創(chuàng)建計(jì)算公式和使用Excel中的函數(shù)來(lái)解決實(shí)際工作中的各種問(wèn)題。實(shí)驗(yàn)內(nèi)容實(shí)驗(yàn)內(nèi)容l 快速?gòu)?fù)制公式快速?gòu)?fù)制公式公式的基礎(chǔ)知識(shí)公式的基礎(chǔ)知識(shí)l 顯示、隱藏和保護(hù)公式顯示、隱藏和保護(hù)公式l 數(shù)組公式及其應(yīng)用數(shù)組公式及其應(yīng)用l 數(shù)據(jù)的邏輯判斷與處理數(shù)據(jù)的邏輯判斷與處理常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 處理日期數(shù)據(jù)處理日期數(shù)據(jù)l 處理文本數(shù)據(jù)處理文本數(shù)據(jù)l 數(shù)據(jù)計(jì)數(shù)與求和匯總表數(shù)據(jù)計(jì)數(shù)與求和匯總表l 數(shù)據(jù)查找與匯

2、總數(shù)據(jù)查找與匯總l 公式中名稱的使用公式中名稱的使用操作小技巧操作小技巧公式的基礎(chǔ)知識(shí)公式的基礎(chǔ)知識(shí)l 快速?gòu)?fù)制公式快速?gòu)?fù)制公式 復(fù)制公式的基本方法是，復(fù)制公式的基本方法是，在一個(gè)單元格中輸入公式以后，將鼠標(biāo)指針對(duì)準(zhǔn)該單元格右下角的黑色小方塊，按住左鍵不放，然后向下、向右、向上或者向左拖曳鼠標(biāo)，從而完成在其他單元格中輸入相應(yīng)計(jì)算公式的工作。 還可以利用一些小技巧來(lái)實(shí)現(xiàn)對(duì)公式的快速?gòu)?fù)制，例如雙擊法、快速填充法、快速?gòu)?fù)制法雙擊法、快速填充法、快速?gòu)?fù)制法等。1. 雙擊法雙擊法 在某個(gè)單元格輸入完公式以后，選取該單元格，在某個(gè)單元格輸入完公式以后，選取該單元格，將鼠標(biāo)指針對(duì)準(zhǔn)該單元格右下角的黑色小方塊

3、，將鼠標(biāo)指針對(duì)準(zhǔn)該單元格右下角的黑色小方塊，雙擊左鍵，迅速得到復(fù)制的公式。雙擊左鍵，迅速得到復(fù)制的公式。 特征：只能快速向下復(fù)制公式，無(wú)法向上、向左或向右快速?gòu)?fù)制公式，且不適用于數(shù)據(jù)區(qū)域中間有空行的場(chǎng)合。2. 快速填充法快速填充法 假如要在包含很多行和很多列的單元格區(qū)域中輸入假如要在包含很多行和很多列的單元格區(qū)域中輸入相同的公式，可以先選取包括第一個(gè)單元格在內(nèi)的相同的公式，可以先選取包括第一個(gè)單元格在內(nèi)的要輸入公式的全部單元格區(qū)域，再輸入計(jì)算公式，要輸入公式的全部單元格區(qū)域，再輸入計(jì)算公式，按按F2鍵，使公式處于編輯狀態(tài)，最后再按鍵，使公式處于編輯狀態(tài)，最后再按Ctrl+Enter組合鍵，即可

4、得到所有的計(jì)算公式。組合鍵，即可得到所有的計(jì)算公式。3. 原樣復(fù)制完整公式或公式的一部分原樣復(fù)制完整公式或公式的一部分l 一般情況下，將某個(gè)單元格中的公式復(fù)制到其他單元格或單元格一般情況下，將某個(gè)單元格中的公式復(fù)制到其他單元格或單元格區(qū)域時(shí)，所得到的公式中引用的單元格往往會(huì)發(fā)生變化，除非原區(qū)域時(shí)，所得到的公式中引用的單元格往往會(huì)發(fā)生變化，除非原來(lái)的公式中引用單元格采用的是絕對(duì)引用方式。若要把公式本身來(lái)的公式中引用單元格采用的是絕對(duì)引用方式。若要把公式本身完整地從一個(gè)單元格復(fù)制到另一個(gè)單元格，但不改變公式中單元完整地從一個(gè)單元格復(fù)制到另一個(gè)單元格，但不改變公式中單元格地引用位置，最基本的方法是將

5、公式作為文本進(jìn)行復(fù)制。格地引用位置，最基本的方法是將公式作為文本進(jìn)行復(fù)制。 (1)選擇要復(fù)制公式的單元格，然后按F2鍵 (2)按Shift+Home組合鍵 (3)按Ctrl+C組合鍵 (4)按Enter鍵或者ESC鍵 (5)單擊目標(biāo)單元格，按Ctrl+V組合鍵公式的基礎(chǔ)知識(shí)公式的基礎(chǔ)知識(shí)l 顯示、隱藏和保護(hù)公式顯示、隱藏和保護(hù)公式 在輸入完畢所有的公式并檢查無(wú)誤后，很有必要將建立好的公式保護(hù)起來(lái)，以免不小心損壞公式。此外，用戶有時(shí)也可能要查看各單元格中已經(jīng)建立的公式，或者希望將計(jì)算公式隱藏起來(lái)，不隨意讓別人看到。 顯示公式和顯示計(jì)算結(jié)果顯示公式和顯示計(jì)算結(jié)果 隱藏公式隱藏公式 保護(hù)公式保護(hù)公式

6、 步驟：步驟： (1)：選擇要實(shí)施保護(hù)的工作表，按：選擇要實(shí)施保護(hù)的工作表，按【Ctrl+A】選擇整個(gè)表選擇整個(gè)表; (2)：解除工作表全部單元格的鎖定，即單元格格式：解除工作表全部單元格的鎖定，即單元格格式-保護(hù)保護(hù)-取消鎖定取消鎖定 (3)：選定要保護(hù)的含有公式的單元格區(qū)域，可以按：選定要保護(hù)的含有公式的單元格區(qū)域，可以按F5定位定位(4)：選擇所有包含公式的單元格，單元格格式：選擇所有包含公式的單元格，單元格格式-保護(hù)保護(hù)-選擇鎖定選擇鎖定(5)：審閱：審閱-保護(hù)保護(hù)-工作表保護(hù)工作表保護(hù)公式的基礎(chǔ)知識(shí)公式的基礎(chǔ)知識(shí)l 數(shù)組公式及其應(yīng)用數(shù)組公式及其應(yīng)用 數(shù)組公式是指對(duì)數(shù)組進(jìn)行計(jì)算的公式。

7、當(dāng)需要對(duì)兩組或兩組以上的數(shù)據(jù)進(jìn)行計(jì)算并返回一個(gè)或多個(gè)計(jì)算結(jié)果時(shí)，就需要使用數(shù)組公式。 步驟步驟：(1)選定單元格或單元格區(qū)域； (2) 輸入數(shù)組公式 (3) 同時(shí)按Ctrl+Shift+Enter組合鍵 案例01：數(shù)組公式的輸入； 注意事項(xiàng)：注意事項(xiàng)：不能在合并單元格中輸入數(shù)組公式。應(yīng)當(dāng)先取消單元格的合并，然后在擬合并的任何一個(gè)單元格中輸入數(shù)組公式，再重新合并單元格。公式的基礎(chǔ)知識(shí)公式的基礎(chǔ)知識(shí)l 公式中使用名稱公式中使用名稱 默認(rèn)情況下，工作表中的單元格名稱是A1、B1等用列標(biāo)和行號(hào)組成的形式。用戶根據(jù)需要還可以給工作表中某些單元格或單元格區(qū)域定義新的名稱，并用新的名稱代替原來(lái)的單元格或單元

8、格區(qū)域的地址。增強(qiáng)公式的可讀性，使公式易于理解。 使用使用“新建名稱新建名稱”對(duì)話框?qū)υ捒?第一步：選擇要定義名稱的單元格或單元格區(qū)域；第一步：選擇要定義名稱的單元格或單元格區(qū)域； 第二步：公式第二步：公式-定義的名稱定義的名稱-定義名稱，定義名稱， “名稱名稱”輸入名稱；輸入名稱；“范圍范圍”指定該名稱的適用范圍；指定該名稱的適用范圍； “引用位置引用位置”輸入單元格區(qū)域地址；輸入單元格區(qū)域地址； 公式的基礎(chǔ)知識(shí)公式的基礎(chǔ)知識(shí)l 公式中使用名稱公式中使用名稱 默認(rèn)情況下，工作表中的單元格名稱是A1、B1等用列標(biāo)和行號(hào)組成的形式。用戶根據(jù)需要還可以給工作表中某些單元格或單元格區(qū)域定義新的名稱，

9、并用新的名稱代替原來(lái)的單元格或單元格區(qū)域的地址。 使用使用“名稱管理器名稱管理器”對(duì)話框?qū)υ捒?公式公式-定義的名稱定義的名稱-名稱管理器名稱管理器-新建名稱；新建名稱； 使用使用“以選定區(qū)域創(chuàng)建名稱以選定區(qū)域創(chuàng)建名稱”對(duì)話框批量定義名稱對(duì)話框批量定義名稱練習(xí)練習(xí)01：表中是某公司某月份的銷售數(shù)據(jù)。要求：對(duì)有關(guān)單元格：表中是某公司某月份的銷售數(shù)據(jù)。要求：對(duì)有關(guān)單元格區(qū)域定義名稱，即數(shù)據(jù)區(qū)域的每列從第區(qū)域定義名稱，即數(shù)據(jù)區(qū)域的每列從第2行開(kāi)始的數(shù)據(jù)列，定義行開(kāi)始的數(shù)據(jù)列，定義為第為第1行的標(biāo)題名稱。行的標(biāo)題名稱。 常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 數(shù)據(jù)的邏輯判斷與處理數(shù)據(jù)的邏輯判斷與處理 一般

10、使用一般使用IF函數(shù)、函數(shù)、AND函數(shù)、函數(shù)、OR函數(shù)。函數(shù)。 IF函數(shù) IF(條件式，條件值為真時(shí)的回應(yīng)，條件值為假時(shí)的回應(yīng)) AND函數(shù) AND(條件表達(dá)式1，條件表達(dá)式2，) OR函數(shù) OR(條件表達(dá)式1，條件表達(dá)式2，) 常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 數(shù)據(jù)的邏輯判斷與處理數(shù)據(jù)的邏輯判斷與處理Excel還提供了一些信息函數(shù)，利用這些函數(shù)可以獲得指定單元格的相關(guān)信息。這些函數(shù)很少單獨(dú)使用，一般是與IF函數(shù)一起使用，用于構(gòu)造一些比較復(fù)雜的條件。 ISERROR函數(shù)：檢驗(yàn)單元格數(shù)據(jù)是否為任意錯(cuò)誤值，如果是錯(cuò)誤值，其返回結(jié)果是TRUE;否則是FALSE。 格式：ISERROR(單元格引用或

11、數(shù)據(jù)) IFERROR函數(shù)：用于對(duì)單元格錯(cuò)誤值進(jìn)行處理。 格式：IFERROR(要檢查是否有錯(cuò)誤的值或表達(dá)式，有錯(cuò)誤時(shí)的返回值) 例1：=IFERROR(A3/B3,”)常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 處理日期數(shù)據(jù)處理日期數(shù)據(jù)常用的日期函數(shù)有TODAY函數(shù)、DATE函數(shù)、YEAR函數(shù)、MONTH函數(shù)、DAY函數(shù)、EOMONTH函數(shù)等。 TODAY函數(shù)：返回計(jì)算機(jī)系統(tǒng)當(dāng)前日期的序列號(hào)。 DATE函數(shù)：用于將三個(gè)數(shù)字組成一個(gè)日期序列號(hào)。 格式：DATE(年,月,日) 例2：=DATE(2010,2,23)-2010-2-23 案例02：某公司記錄的應(yīng)收帳款的有關(guān)數(shù)據(jù)。假定應(yīng)收帳款到期日按照銷售

12、日期間隔信用期限對(duì)應(yīng)的月份數(shù)的那天的前一天計(jì)算，要求計(jì)算該公司各筆應(yīng)收帳款的到期日。常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 處理日期數(shù)據(jù)處理日期數(shù)據(jù)常用的日期函數(shù)有TODAY函數(shù)、DATE函數(shù)、YEAR函數(shù)、MONTH函數(shù)、DAY函數(shù)、EOMONTH函數(shù)等。DATE函數(shù)：用于將三個(gè)數(shù)字組成一個(gè)日期序列號(hào)。 格式：DATE(年,月,日) 特殊用法特殊用法l若參數(shù)day為0，該函數(shù)返回指定月份上個(gè)月的最后一天l 若參數(shù)day大于31，該函數(shù)會(huì)將超過(guò)部分的天數(shù)算到下一個(gè)月，如”=DATE(2010,1,42)”-2010-2-11 l 若參數(shù)day小于0，該函數(shù)將會(huì)往前推算日期l若參數(shù)month為0，該

13、函數(shù)返回指定年份上年的最后一月，比如”=DATE(2010,0,22)” -2009-12-22 常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 處理日期數(shù)據(jù)處理日期數(shù)據(jù) EOMONTH函數(shù)：獲取指定日期往前或往后幾個(gè)月的特定月份的月底日期。 格式：EOMONTHE(開(kāi)始日期,幾個(gè)月) 例3：計(jì)算2009年10月12日之后3個(gè)月的月末日期？ =EOMONTH(“2009-10-12”,3) 例4：計(jì)算2009年10月12日之前5個(gè)月的月末日期？ =EOMONTH(“2009-10-12”,-5) 例5：計(jì)算2009年2月共有多少天？ =DAY(EOMONTH(“2009-2”,0)案例03：以案例02數(shù)據(jù)

14、資料為例，現(xiàn)在假定各筆應(yīng)收帳款到期日按照銷售日期間隔信用期限對(duì)應(yīng)的幾個(gè)月后那個(gè)月的月底日期，要求計(jì)算該公司各筆應(yīng)收帳款的到期日。常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 處理文本數(shù)據(jù)處理文本數(shù)據(jù) 常用文本函數(shù)有LEN函數(shù)、LEFT函數(shù)、RIGHT函數(shù)、MID函數(shù)、TEXT函數(shù)、FIND函數(shù)。 LEN和LENB函數(shù)：獲取字符串的長(zhǎng)度。LEN用于獲取文本字符串中的字符數(shù)；LENB用于獲取文本字符串中用于代表字符的字節(jié)數(shù)。 例6：=LEN(“100083北京市”) 結(jié)果為9 =LENB(“100083北京市”) 結(jié)果為12 常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 處理文本數(shù)據(jù)處理文本數(shù)據(jù) 常用文本函數(shù)有LEN

15、函數(shù)、LEFT函數(shù)、RIGHT函數(shù)、MID函數(shù)、TEXT函數(shù)、FIND函數(shù)。 LEFT和LEFTB函數(shù)：獲取字符串左邊指定個(gè)數(shù)的字符。LEFT是以字符串的字符數(shù)為單位，而LEFTB是以字符串的字節(jié)數(shù)為單位。格式：LEFT(文本字符串,字符數(shù))例6：=LEFT(“100083北京市”,6) 結(jié)果為”100083” =LEFTB(“北京100083”,6) 結(jié)果為”北京10” 案例案例04：表中是某公司會(huì)計(jì)科目編碼和名稱的數(shù)據(jù)存放。要求：將A列的會(huì)計(jì)科目編碼和名稱拆分成兩列，以便于對(duì)會(huì)計(jì)信息進(jìn)行日常核算和管理。 B2單元格中輸入：?jiǎn)卧裰休斎耄?LEFT(A2,2*LEN(A2)-LENB(A2)

16、C2單元格中輸入：?jiǎn)卧裰休斎耄?RIGHT(A2,LENB(A2)-LEN(A2)常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 數(shù)據(jù)計(jì)數(shù)與求和匯總數(shù)據(jù)計(jì)數(shù)與求和匯總 計(jì)數(shù)統(tǒng)計(jì)使用最多的函數(shù)是COUNTA函數(shù)和COUNTIF函數(shù)。COUNTA函數(shù)的功能是統(tǒng)計(jì)單元格區(qū)域內(nèi)非空單元格的個(gè)數(shù)。COUNTIF函數(shù)的功能是統(tǒng)計(jì)數(shù)據(jù)區(qū)域內(nèi)滿足條件的單元格個(gè)數(shù)，且只能根據(jù)一個(gè)條件進(jìn)行計(jì)數(shù)統(tǒng)計(jì)。多條件計(jì)數(shù)可以用COUNTIFS函數(shù)，適用于幾個(gè)與條件情況下的多條件計(jì)數(shù)。格式：COUNTA(單元格區(qū)域) COUNTIF(單元格區(qū)域，條件值) COUNTIFS(單元格區(qū)域1，條件值1，單元格區(qū)域2，條件值2) 練習(xí)2：某公司的銷售訂單數(shù)據(jù)，其中每一行數(shù)據(jù)是一張訂單記錄。要求：(1)分別編制每種商品的訂單數(shù)量和每個(gè)城市的訂單數(shù)量統(tǒng)計(jì)分析表；(2)編制各城市的每種商品訂單數(shù)量統(tǒng)計(jì)分析表。 常用函數(shù)及其應(yīng)用常用函數(shù)及其應(yīng)用l 數(shù)據(jù)計(jì)數(shù)與求和匯總數(shù)據(jù)計(jì)數(shù)與求和匯總 常 用 的 求 和 匯 總 函 數(shù) 為 S U M 函 數(shù) 、 S U M I F 函 數(shù) 、SUMPRODUCT函數(shù)等。格式：SUMIF(條件判斷的單元格區(qū)域,條件,需要求和的實(shí)際單元格)含義：根據(jù)指定條件對(duì)若干單元格求和。例如： AB1年齡年齡傭金傭金2207,00033014,00044021,000