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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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