1、第8章 公式、函數(hnsh)與統計 作為一個電子表格系統，除了(ch le)進行一般的表格處理外，最主要的還是它的數據計算能力。本章的內容主要是通過對單元格引用方法及常用函數的基本應用方法的討論，從而掌握比較復雜的公式和一般常用函數的使用方法。第1頁/共33頁第一頁，共34頁。本章(bn zhn)學習內容 8.1 公式(gngsh)、函數與統計基礎 8.2 輸入函數 8.3 含有條件的函數 8.4 在函數和公式(gngsh)中使用定義名稱 第2頁/共33頁第二頁，共34頁。8.1.1 相對引用(ynyng)和絕對引用(ynyng) 相對單元格引用：在公式中，基于包含公式的單元格與被引用的單元

2、格之間的相對位置的單元格地址。如果復制公式，相對引用將自動調整。相對引用采用的形式為“A1”。 絕對單元格引用：公式中單元格的精確地址，與包含公式的單元格的位置無關。絕對引用采用的形式為“$A$1”。 相對引用、絕對引用和混合引用間切換 選中包含公式的單元格。 在編輯欄中，選擇(xunz)要更改的引用。 按 F4 進行切換。 分類引用說 明相對引用C3相對列和相對行絕對引用$A$1絕對列和絕對行混合引用C$1相對列和絕對行$A3絕對列和相對行第3頁/共33頁第三頁，共34頁。8.1.2 三維引用(ynyng) 所謂三維引用，是指跨工作表的單元格或單元格區域的引用。 地址形式：工作表名！單元格地

3、址，當前工作表的單元格地址可以(ky)省略“工作表名！”。 例：=A1+Sheet2!A1+Sheet3!B1 說明：此公式表示對當前工作表的A1單元格、sheet2工作表的A1單元格和sheet3工作表的B1單元格中的數據求和。第4頁/共33頁第四頁，共34頁。8.1.3 求和(qi h)按鈕的使用 Excel提供了一些預定好的的公式，稱為函數，利用函數可以簡化操作，還能實現許多普通運算所難以完成的運算。 函數的語法：函數名（參數）例：“SUM(A1:C5)” 函數有多個參數時，參數之間以逗號分隔。有些函數可能無參數，但括號不能省略。 在前面章節中我們已經使用過“常用工具欄”的“自動求和”按

4、鈕進行求和。在Office XP以后的版本(bnbn)中增加了求平均值、計數、最大值和最小值等的自動計算功能，使計算操作更加方便快捷。第5頁/共33頁第五頁，共34頁。8.2 插入(ch r)函數 前面我們介紹了使用自動求和按鈕及使用鼠標點擊求和按鈕右側的箭頭，完成求和、求平均值、最大值等操作。下面介紹使用“插入函數”對話框在公式(gngsh)及單元格中輸入函數的方法。第6頁/共33頁第六頁，共34頁。 8.2.1 插入(ch r)函數對話框 使用“插入函數”對話框可根據需要在工作表中插入的函數和參數(cnsh)，使用下列方法之一可以打開“插入函數”對話框。點擊編輯工具欄上的插入函數按鈕點擊自

5、動求和按鈕右側的箭頭，在列表中選擇“其它函數”選擇插入菜單的函數命令第7頁/共33頁第七頁，共34頁。8.2.2 函數(hnsh)對話框 搜索函數：輸入一條簡短的說明來描述想做什么（如：“日期”）。 選擇類別：從下拉列表中，執行下列操作之一： 選擇“常用函數”。最近插入的函數將按字母順序顯示在“選擇函數”框中。 選擇函數類別。類別中的函數將按字母順序顯示在“選擇函數”框中。 選擇“全部”。所有函數將按字母順序顯示在“選擇函數”框中。選擇函數：執行下列一項操作： 單擊函數名稱，可立即在“選擇函數”框下面看到函數語法(yf)和簡短說明。 雙擊函數名稱可在“函數參數”向導中顯示函數及其參數，該向導可

6、幫助添加正確的參數。有關該函數的幫助：在“幫助”窗口中為“選擇函數”框中當前所選的函數顯示引用幫助主題。 第8頁/共33頁第八頁，共34頁。8.2.3 常用(chn yn)函數 Excel中提供了多種函數，其中比較常用的9種函數如下(rxi)： 1. 求和函數 SUM （統計類） 返回某一單元格區域中所有數字之和。 語法：SUM(number1,number2, .) Number1, number2, .為 1 到 30 個需要求和的參數。 2. 算術平均值 AVERAGE （統計類） 返回參數的平均值（算術平均值）。 語法：AVERAGE(number1,number2,.) Number

7、1, number2, .為需要計算平均值的 1 到 30 個參數。第9頁/共33頁第九頁，共34頁。8.2.3 常用(chn yn)函數 3. 3. 最大值函數最大值函數 MAX MAX（統計類）（統計類） 返回返回(fnhu)(fnhu)一組值中的最大值。一組值中的最大值。 語法：語法：MAX(number1,number2,.)MAX(number1,number2,.) Number1, number2, .Number1, number2, .是要從中找出最大值的是要從中找出最大值的 1 1 到到 30 30 個數字參數。個數字參數。 4. 4. 最小值函數最小值函數 MIN MIN

8、（統計類）（統計類） 返回返回(fnhu)(fnhu)一組值中的最小值。一組值中的最小值。 語法：語法：MIN(number1,number2,.)MIN(number1,number2,.) Number1, number2,.Number1, number2,.是要從中找出最小值的是要從中找出最小值的 1 1 到到 30 30 個數字參數。個數字參數。第10頁/共33頁第十頁，共34頁。8.2.3 常用(chn yn)函數 5. 5. 計數函數計數函數 COUNT COUNT（統計類）（統計類） 返回包含數字以及包含參數列表中的數字的單元格的個數。利用函數返回包含數字以及包含參數列表中的數

9、字的單元格的個數。利用函數 COUNT COUNT 可以可以計算單元格區域或數字數組中數字字段的輸入項個數。計算單元格區域或數字數組中數字字段的輸入項個數。 語法：語法：COUNT(value1,value2,.)COUNT(value1,value2,.) Value1, value2, .Value1, value2, .為包含或引用各種類型數據的參數（為包含或引用各種類型數據的參數（1 1 到到 30 30個），但只有個），但只有數字類型的數據才被計算。數字類型的數據才被計算。 6. 6. 取整函數取整函數 INT INT（數學與三角函數類）（數學與三角函數類） 將數字向下舍入到最接近的

10、整數。將數字向下舍入到最接近的整數。 語法：語法： INT(number) INT(number) NumberNumber需要進行向下舍入取整的實數。此函數只有一個需要進行向下舍入取整的實數。此函數只有一個(y (y )參數。請注意負參數。請注意負數取整后的結果。數取整后的結果。第11頁/共33頁第十一頁，共34頁。8.2.3 常用(chn yn)函數 7. 7. 當前日期函數當前日期函數 TODAY TODAY（日期與時間類）（日期與時間類）返回當前日期返回當前日期語法語法(yf)(yf)：TODAY( )TODAY( )Excel Excel 可將日期存儲為可用于計算的序列號。默認情況下

11、，可將日期存儲為可用于計算的序列號。默認情況下，1900 1900 年年 1 1 月月 1 1 日的序列號是日的序列號是 1 1 而而 2008 2008 年年 1 1 月月 1 1 日的日的序列號是序列號是 39448 39448，這是因為它距，這是因為它距 1900 1900 年年 1 1 月月 1 1 日有日有 39448 39448 天。天。此函數沒有參數，但函數中的括號不能省略。此函數沒有參數，但函數中的括號不能省略。第12頁/共33頁第十二頁，共34頁。8.2.3 常用(chn yn)函數 8. 8. 年份函數年份函數YEARYEAR（日期與時間類）（日期與時間類） 返回某日期對應

12、的年份。返回值為返回某日期對應的年份。返回值為 1900 1900 到到 9999 9999 之間的整數。之間的整數。 語法：語法：YEAR(serial_number)YEAR(serial_number) Serial_numberSerial_number為一個日期值，其中包含要查找年份的日期。為一個日期值，其中包含要查找年份的日期。 9. 9. 四舍五入四舍五入ROUNDROUND（數學與三角函數類）（數學與三角函數類） 返回某個數字按指定位數進行四舍五入后的數字。返回某個數字按指定位數進行四舍五入后的數字。 語法：語法：ROUND(number,num_digits)ROUND(nu

13、mber,num_digits) NumberNumber需要進行四舍五入的數字。需要進行四舍五入的數字。 Num_digitsNum_digits指定的位數，按此位數進行四舍五入。如參數指定的位數，按此位數進行四舍五入。如參數(cnsh)(cnsh)為負值取小數點左側的位數。為負值取小數點左側的位數。第13頁/共33頁第十三頁，共34頁。8.2.3 常用(chn yn)函數 10. 10. 字符串函數字符串函數midmid（字符串類）（字符串類） 從字符串中返回從字符串中返回(fnhu)(fnhu)指定數目的字符。指定數目的字符。 語法：語法：mid(string, start, lengt

14、h) mid(string, start, length) 11. 11. 字符串函數字符串函數lenlen（字符串類）（字符串類） 返回返回(fnhu)(fnhu)一個包含字符串中的字符數或存儲某個變一個包含字符串中的字符數或存儲某個變量所需的名義字節數的整數。量所需的名義字節數的整數。 語法：語法：len(string|varname)len(string|varname)第14頁/共33頁第十四頁，共34頁。8.2.4 函數(hnsh)的嵌套 在引用函數時，函數的參數又引用了函數，稱為“函數的嵌套”。被嵌套的函數，必須返回與當前參數使用的數值類型相同的數值。如果嵌套函數返回的數值類型不正

15、確(zhngqu)，Excel將顯示“VALUE！”錯誤值。第15頁/共33頁第十五頁，共34頁。8.3 含有(hn yu)條件的函數 這里主要介紹IF函數(hnsh)、IF函數(hnsh)的嵌套、 COUNTIF、 SUMIF等含有條件函數(hnsh)的應用，同學們應熟練掌握IF函數(hnsh)、IF函數(hnsh)的嵌套的應用。第16頁/共33頁第十六頁，共34頁。8.3.1 IF函數(hnsh) IF函數屬于邏輯函數的一種，應用十分廣泛。主要用于執行真假值判斷后，根據邏輯測試的真假值返回不同的結果，因此(ync)IF函數也稱之為條件函數。 語法: IF(logical_test,valu

16、e_if_true,value_if_false) Logical_test表示計算結果為 TRUE 或 FALSE 的邏輯表達式。例如，A10=100 就是一個邏輯表達式，如果單元格 A10 中的值等于 100，表達式的值即為 TRUE，否則為 FALSE。本參數可使用任何比較運算符。 Value_if_true: logical_test 為 TRUE 時返回的值。 Value_if_false: logical_test 為 FALSE 時返回的值。 使用if函數的嵌套可以構造復雜的檢測條件，函數 IF 最多可以嵌套七層。第17頁/共33頁第十七頁，共34頁。例：成績(chngj)判定

17、任務分析任務分析 本任務可使用上面講到的本任務可使用上面講到的IFIF函數完成。函數完成。 根據所定規則，我們在綜合評定中寫公式（以單元格根據所定規則，我們在綜合評定中寫公式（以單元格H3H3為例）：為例）： =IF(G3=60,=IF(G3=60,合格合格,不合格不合格) 語法語法(yf)(yf)解釋為，如果單元格解釋為，如果單元格G3G3的值大于的值大于6060，則執行第二個參數即，則執行第二個參數即在單元格在單元格H3H3中顯示中顯示“合格合格”，否則執行第三個參數即在單元格，否則執行第三個參數即在單元格H3H3中顯示中顯示“不合格不合格”。第18頁/共33頁第十八頁，共34頁。例：實施

18、(shsh)步驟 插入函數 直接(zhji)輸入打開插入函數對話框邏輯表達式表達式的值為“假”時的返回值表達式的值為“真”時的返回值第19頁/共33頁第十九頁，共34頁。8.3.2 IF函數(hnsh)的嵌套 在我們實際工作中，經常遇到需返回兩種以上值得情況(qngkung)，例如我們把上面的任務做一個小小的修改，總評成績的結果不再是“合格”與“不合格”，而是“優”、“良”、“及格”、“不及格”四種結果。第20頁/共33頁第二十頁，共34頁。例：IF嵌套 完成總評成績的計算（IF嵌套） 要求：60分以下為不及格；6080為及格；8090為良；90100為優。 任務分析： 此任務可使用IF函數的

19、嵌套方法來完成，可先判斷該學生成績是否小于60分，如小于則“不及格”，否則(fuz)繼續判斷該學生的成績是否小于80分，如小于則為“及格”，否則(fuz)繼續判斷該學生的成績是否小于90分，如小于則為“良”，否則(fuz)就不需要再繼續判斷了，該學生的成績為“優”。判斷流程如圖所示。60良8032” 或 “蘋果”。 計算第一列中蘋果所在單元格的個數 計算第二列中值大于 55 的單元格個數第24頁/共33頁第二十四頁，共34頁。8.3.3.2 SUMIF函數(hnsh) 根據指定條件對若干單元格求和。 語法：SUMIF (range, criteria, sum _ range) range 為

20、用于條件判斷的單元格區域。 criteria 為確定哪些單元格將被求和的條件，其形式可以為數字、表達式或文本(wnbn)。 sum_ range 是需要求和的實際單元格。 示例:分別計算下表中張三和李四的銷售額的總計第25頁/共33頁第二十五頁，共34頁。8.4 在公式和函數中使用定義(dngy)名稱 除了可以使用行號、列標表示單元格或單元格區域外，Excel允許給單元格或單元格區域命名。使用名稱引用單元格區域時，既可簡化輸入(shr)，又無需記住區域的起始和終止位置。第26頁/共33頁第二十六頁，共34頁。8.4.1 定義(dngy)名稱 用以下兩種方法可以定義名稱。1. 利用編輯欄的名稱框

21、定義名稱首先選定要命名的單元格區域，然后(rnhu)單擊編輯欄左端的名稱框，輸入名稱，按回車鍵。2. 利用“插入”菜單定義名稱首先選定要命名的單元格區域，選擇【插入】【名稱】【定義】命令，彈出“定義名稱”對話框，在“當前工作簿中的名稱”文本框中輸入名稱，此時在“引用位置”文本框中顯示單元格區域，單擊“添加”“確定”按鈕。第27頁/共33頁第二十七頁，共34頁。8.4.2 使用(shyng)名稱名稱定義后，從名稱框的下拉列表中可以查看到已定義的名稱。從中選定某一名稱，它所表示的區域也同時被選中。由于名稱與它所表示的區域之間具有確定的關系，因此可以在公式和函數中使用名稱。當在新建的公式或函數中引用了已被命名的單元格區域，系統會自動用定義的名稱替代該單元格區域的引用。以求張三(zhn sn)的銷售額為例，張三(zhn sn)銷售額的計算公式便可以寫成“=SUMIF(姓名,張三(zhn sn),銷售額)”。這樣使得公式更直觀，更易于理解。第28頁/共33頁第二十八頁，共34頁。8.4.3 刪除(shnch)名稱 選擇【插入】【名稱】【定義】命令，出現“定義名稱”對話框，選中需要刪除(shnch)的名稱，單擊“刪除(shnch)”“確