第6章公式和函數的使用6.1使用公式6.2課堂練習——計算月考成績總分6.3公式中的引用設置6.4公式中的錯誤和審核6.5使用函數6.6課堂練習——計算水、電費合計金額1整理ppt第6章公式和函數的使用6.1使用公式1整理ppt6.1使用公式

公式是對工作表中數據進行計算的表達式，函數是Excel預先定義好的用來執(zhí)行某些計算、分析功能的封裝好的表達式，即用戶只需按要求為函數指定參數，就能獲得預期結果，而不必知道其內部是如何實現的。利用公式可對同一工作表的各單元格，同一工作簿中不同工作表的單元格，甚至其他工作簿的工作表中單元格的數值進行加、減、乘、除、乘方等各種運算。公式必須以等號“=”開頭，后面跟表達式。表達式由運算符和參與運算的操作數組成。運算符可以是算術運算符、比較操作符、文本連接符和引用操作符；操作數可以是常量、單元格地址和函數等。2整理ppt6.1使用公式公式是對工作表中數據進行計算的表6.1.1公式中的運算符

運算符是用來對公式中的元素進行運算而規(guī)定的特殊符號。Excel2007包含4種類型的運算符：算術運算符、比較運算符、文本運算符和引用運算符。1．算術運算符算術運算符有6個，如下表所示，其作用是完成基本的數學運算，并產生數字結果。3整理ppt6.1.1公式中的運算符運算符是用來對公2．比較運算符

比較運算符有6個，如下表所示。它們的作用是可以比較兩個值，結果為一個邏輯值，不是“TRUE（真）”，就是“FALSE（假）”。4整理ppt2．比較運算符比較運算符有6個，如下表所示。3．文本運算符

文本運算符只有1個，如下表所示，使用文本連接符（＆）可加入或連接一個或更多字符串以產生一長文本。例如：“2008年”＆“北京奧運會”就產生“2008年北京奧運會”。5整理ppt3．文本運算符文本運算符只有1個，如下表所示4．引用運算符

引用運算符有3個，如下表所示，可以將單元格區(qū)域進行合并計算。6整理ppt4．引用運算符引用運算符有3個，如下表所示，可6.1.2公式中的運算順序通常情況下，Excel根據公式中運算符的特定順序從左到右計算公式。如果公式中同時用到了多個運算符，Excel將按一定的順序（優(yōu)先級由高到低）進行運算，如下表所示。另外，相同優(yōu)先級的運算符，將從左到右進行計算。若要更改求值的順序，可以將公式中要先計算的部分用括號括起來。7整理ppt6.1.2公式中的運算順序通常情況下，Exce6.1.3創(chuàng)建和編輯公式

1．創(chuàng)建公式要創(chuàng)建公式，可以直接在單元格中輸入，也可以在編輯欄中輸入。直接在單元格中輸入

對于簡單的公式，我們可以直接在單元格中輸入：首先單擊需輸入公式的單元格，接著輸入=（等號），然后輸入公式內容，最后單擊編輯欄上的“輸入”按鈕，或按【Enter】鍵結束。

8整理ppt6.1.3創(chuàng)建和編輯公式1．創(chuàng)建公式要在編輯欄中輸入單擊要輸入公式的單元格，然后單擊編輯欄，在編輯欄中輸入等號“=”，輸入操作數和運算符，輸入完畢，按下【Enter】鍵或單擊編輯欄上的“輸入”按鈕。

9整理ppt在編輯欄中輸入單擊要輸入公式的單元格，然后單2．編輯公式要修改公式，可單擊含有公式的單元格，然后在編輯欄中進行修改，修改完畢按【Enter】鍵即可。要刪除公式，可單擊含有公式的單元格，然后按【Delete】鍵，如下圖所示。

10整理ppt2．編輯公式要修改公式，可單擊含有公式的單元6.1.4移動和復制公式1．移動公式要移動公式，最簡單的方法就是：選中包含公式的單元格，將鼠標指針移到單元格的邊框線上，當鼠標指針變成十字箭頭形狀時，按住鼠標左鍵不放，將其拖到目標單元格后釋放鼠標即可。11整理ppt6.1.4移動和復制公式1．移動公式2．復制公式使用“填充柄”在Excel中，當我們想將某個單元格中的公式復制到同列（行）中相鄰的單元格時，可以通過拖動“填充柄”來快速完成。方法是：按住鼠標左鍵向下（也可以是上、左或右，據實際情況而定）拖動要復制的公式的單元格右下角的填充柄，到目標位置后釋放鼠標即可。

12整理ppt2．復制公式使用“填充柄”在Excel中，利用“選擇性粘貼”復制含有公式的單元格（此單元格包含格式），然后選擇目標單元格，單擊“粘貼”按鈕下方的三角按鈕，在展開的列表中選擇“選擇性粘貼”項，在打開的對話框中選中“公式”單選鈕，然后單擊“確定”即可。

13整理ppt利用“選擇性粘貼”復制含有公式的單元格（此單6.2課堂練習——計算學生成績總分

下面通過制作如下圖所示的第一次月考成績表，來熟悉一下公式的輸入及使用填充柄復制公式的方法。操作步驟如下：14整理ppt6.2課堂練習——計算學生成績總分下面通過

1打開文件“第一次月考成績”，然后單擊I2單元格，輸入等號“=”，然后輸入C2+D2+E2+F2+G2+H2，單擊編輯欄上的“輸入”按鈕，得到第一個學生的成績總分。

15整理ppt1打開文件“第一次月考成績”，然后單擊I2單2按住鼠標左鍵向下拖動I2單元格右下角的填充柄，至I13單元格后釋放鼠標，得到其他學生的成績總分。最后將工作簿另存。16整理ppt2按住鼠標左鍵向下拖動I2單元格右下角的填充6.3公式中的引用設置引用的作用在于標識工作表中的單元格或單元格區(qū)域，并指明公式中所使用的數據的位置。通過單元格引用，可以在一個公式中使用工作表不同部分的數據，或者在多個公式中使用同一個單元格的數值，還可以引用同一個工作簿中不同工作表中的單元格，甚至其他工作簿中的數據。當公式中引用的單元格數值發(fā)生變化時，公式會自動更新單元格的內容。17整理ppt6.3公式中的引用設置引用的作用在于標識6.3.1引用單元格或單元格區(qū)域公式大都使用單元格地址引用一個或多個單元格。要在公式中引用單元格，只需單擊要在其中輸入公式的單元格，接著輸入等號（=），然后直接輸入該單元格地址或單擊該單元格，最后按【Enter】鍵即可。

18整理ppt6.3.1引用單元格或單元格區(qū)域公式大都若要引用單元格區(qū)域，則輸入區(qū)域左上角單元格的地址、冒號（：）和區(qū)域右下角單元格地址，例如：A2:E2表示單元格A2到單元格E2的區(qū)域。下表列出了在公式中使用地址引用單元格或單元格區(qū)域示例。19整理ppt若要引用單元格區(qū)域，則輸入區(qū)域左上角單元格的地址6.3.2相對引用、絕對引用和混合引用1．相對引用相對引用指的是單元格的相對地址，其引用形式為直接用列標和行號表示單元格，例如A1。如果公式所在單元格的位置改變，引用也隨之改變。默認情況下，公式使用相對引用。在復制公式時，Excel將自動調整復制公式的引用，以便引用相對于當前公式位置的其他單元格。

20整理ppt6.3.2相對引用、絕對引用和混合引用1．相對引用2．絕對引用絕對引用指的是單元格的精確地址，與包含公式的單元格位置無關，其引用形式為在列標和行號的前面都加上“$”號。例如，已知快樂書店三個分店同種書籍一天的銷售量，現在要計算各分店的銷售總額，操作步驟如下圖所示。

含有絕對引用的公式21整理ppt2．絕對引用絕對引用指的是單元格的精確地址，3．混合引用引用中既包含絕對引用又包含相對引用的稱為混合引用，如A$1或$A1等，用于表示列變行不變或列不變行變的引用。如果公式所在單元格的位置改變，則相對引用改變，而絕對引用不變。下圖所示單元格D2中的公式為：=$B2*$C2，當將其復制到D3單元格中時，公式變?yōu)椋?$B3*$C3。22整理ppt3．混合引用引用中既包含絕對引用又包含相對引6.3.3引用不同工作表中的單元格或單元格區(qū)域在同一工作簿中，不同工作表中的單元格可以相互引用，它的表示方法為：“工作表名稱!單元格地址”。如：Sheet3!E6

同一工作簿的兩張工作表Sheet1工作表的B2單元格引用了Sheet2工作表中的C4單元格輸入工作表名稱和單元格地址按Enter鍵23整理ppt6.3.3引用不同工作表中的單元格或單元格區(qū)域6.3.4不同工作簿間單元格的引用除了可以在同一工作表、同一工作簿的不同工作表中引用單元格外，還可以在當前工作表中引用不同工作簿中的單元格，它的表示方法為：[工作簿名稱.xlsx]工作表名稱！單元格地址

兩張樣表工作表這樣比較兩年的總收入情況就方便了選擇單元格后輸入等號單擊工作表標簽后單擊單元格按Enter鍵24整理ppt6.3.4不同工作簿間單元格的引用除了可以6.4公式中的錯誤和審核6.4.1公式中返回的錯誤值（1）錯誤值：＃＃＃＃含義：輸入到單元格中的數據太長或單元格公式所產生的結果太大，使結果在單元格中顯示不下，或是對日期和時間格式的單元格做減法，出現了負值。解決辦法：增加列的寬度，使結果能夠完全顯示。如果是由日期或時間相減產生了負值引起的，可以改變單元格的格式，比如改為文本格式。25整理ppt6.4公式中的錯誤和審核6.4.1公式中返回的錯誤（3）錯誤值：＃VALUE!含義：輸入引用文本項的數學公式。如果使用了不正確的參數或運算符，或者當執(zhí)行自動更正公式功能時不能更正公式，都將產生錯誤信息＃VALUE!。解決辦法：應確認公式或函數所需的運算符或參數正確，并且公式引用的單元格中包含有效的數值。例如，單元格C4中有一個數字或邏輯值，而單元格D4包含文本，則在計算公式=C4＋D4時，系統(tǒng)不能將文本轉換為正確的數據類型，因而返回錯誤值＃VALUE!。（2）錯誤值：＃DIV/0!含義：試圖除以0。這個錯誤的產生通常有下面幾種情況：除數為0、在公式中除數使用了空單元格或是包含零值單元格的單元格引用。解決辦法：修改單元格引用，或者在用作除數的單元格中輸入不為零的值。26整理ppt（3）錯誤值：＃VALUE!（2）錯誤值（4）錯誤值：＃REF!含義：刪除了被公式引用的單元格范圍。解決辦法：恢復被引用的單元格范圍，或是重新設定引用范圍。（5）錯誤值：＃N/A含義：無信息可用于所要執(zhí)行的計算。在建立模型時，用戶可以在單元格中輸入#N/A，以表明正在等待數據。任何引用含有#N/A值的單元格都將返回#N/A。解決辦法：在等待數據的單元格內填上數據。（6）錯誤值：＃NUM!含義：提供了無效的參數給工作表函數，或是公式的結果太大或太小而無法在工作表中表示。解決辦法：確認函數中使用的參數類型正確。如果是公式結果太大或太小，就要修改公式。27整理ppt（4）錯誤值：＃REF!27整理ppt（7）錯誤值：＃NAME?含義：在公式中使用了Excel不能識別的文本。例如，可能是輸錯了名稱，或是輸入了一個已刪除的名稱。另外，如果沒有將文字串括在雙引號中，也會產生此錯誤值。解決辦法：如果是使用了不存在的名稱而產生這類錯誤，應確認使用的名稱確實存在；如果是函數名拼寫錯誤，改正過來就可以了；將文字串括在雙引號中；確認公式中使用的所有區(qū)域引用都使用了冒號（:），如SUM（C1:C10）。（8）錯誤值：＃NULL！含義：在公式中的兩個范圍之間插入一個空格以表示交叉點，但這兩個范圍沒有公共單元格。例如，輸入“=SUM(A1:A10C1:C10)”，就會產生這種情況。解決辦法：改正區(qū)域運算符使之正確；更改引用使之相交。例如，在兩個區(qū)域之間用逗號隔開“=SUM(A1:A10，C1:C10)”。28整理ppt（7）錯誤值：＃NAME?（8）錯誤值：＃NULL！6.4.2公式審核Excel2007提供了公式審核功能，通過它可以將任意單元格中的數據來源和計算結果顯示出來，使用戶明白計算的方式。1．查找公式中引用的單元格要查找公式中引用的單元格，只需單擊含有公式的單元格，即可在編輯欄中顯示引用的單元格。29整理ppt6.4.2公式審核Excel2007提2．追蹤為公式提供數據的單元格要顯示公式中引用的單元格，可單擊含有公式的單元格，然后單擊“公式”選項卡上“公式審核”組中的“追蹤引用單元格”按鈕，此時，公式所引用的單元格就會有追蹤箭頭指向公式所在的單元格，在代表數據流向的箭頭上，每一個引用的單元格上都會出現一個藍色的圓點。30整理ppt2．追蹤為公式提供數據的單元格要顯示公式中引3．追蹤導致公式錯誤的單元格Excel會自動對輸入的公式進行檢查，當發(fā)生錯誤時，單元格的左上角會出現一個綠色的小三角，單擊該單元格，會在該單元格左側出現，按鈕。單擊按鈕，會彈出快捷菜單，提供解決此錯誤的途徑。31整理ppt3．追蹤導致公式錯誤的單元格Excel會自動要追蹤導致公式錯誤的單元格，可單擊“公式審核”組中的“錯誤檢查”按鈕右側的三角按鈕，在展開的列表中單擊“追蹤錯誤”項，即可標識出產生錯誤的單元格。32整理ppt要追蹤導致公式錯誤的單元格，可單擊“公式審核”組4．追蹤產生循環(huán)引用的單元格當某個公式直接或間接引用了該公式所在的單元格時，稱作循環(huán)引用。當打開的工作簿中含有循環(huán)引用時，Excel會顯示警告提示框。

當按【Enter】鍵時，會彈出如下左圖所示的對話框。如果確定要進行循環(huán)引用，則單擊“確定”按鈕，結果如下右圖所示，此時“追蹤從屬單元格”列表中的“循環(huán)引用”項變?yōu)榭捎茫@示出循環(huán)引用的單元格。單元格A5中的值依賴于單元格A2，而A2單元格中的值又依賴于A5，它們之間形成了間接的循環(huán)引用33整理ppt4．追蹤產生循環(huán)引用的單元格當某個公式直接或6.5使用函數函數是預先定義好的表達式，它必須包含在公式中。每個函數都由函數名和變量組成，其中函數名表示將執(zhí)行的操作，變量表示函數將作用的值的單元格地址，通常是一個單元格區(qū)域，也可以是更為復雜的內容。在公式中合理地使用函數，可以完成如求和、邏輯判斷、財務分析等眾多數據處理功能。34整理ppt6.5使用函數函數是預先定義好的表達式，6.5.1函數的分類財務函數：可以進行一般的財務計算。例如，確定貸款的支付額、投資的未來值或凈現值，以及債券或息票的價值。時間和日期函數：可以在公式中分析和處理日期值和時間值。數學和三角函數：可以處理簡單和復雜的數學計算。統(tǒng)計函數：用于對數據進行統(tǒng)計分析。查找和引用函數：在工作表中查找特定的數值或引用的單元格。35整理ppt6.5.1函數的分類財務函數：可以進行一般的財務計算。例邏輯函數：可以進行真假值判斷，或者進行復合檢驗。信息函數：用于確定存儲在單元格中的數據的類型。工程函數：用于工程分析。多維數據集函數：主要用于返回多維數據集的重要性能指標、屬性、層次結構中的成員或組等。數據庫函數：分析工作表中的數值是否符合特定條件。文本函數：可以在公式中處理文字串。36整理ppt邏輯函數：可以進行真假值判斷，或者進行復合檢驗。數據庫函數：6.5.2函數的使用方法使用函數時，應首先確認已在單元格中輸入了“=”號，即已進入公式編輯狀態(tài)。接下來可輸入函數名稱，再緊跟著一對括號，括號內為一個或多個參數，參數之間要用逗號來分隔。1．手工輸入函數如果用戶能記住函數的名稱、參數，可直接在單元格中輸入函數。方法如下：單擊要輸入函數的單元格，然后依次輸入等號、函數名、左括號、具體參數和右括號，最后單擊編輯欄中的“輸入”按鈕或按【Enter】鍵，此時在輸入函數的單元格中將顯示公式的運算結果。

37整理ppt6.5.2函數的使用方法使用函數時，應首2．使用函數向導如果不能確定函數的拼寫或參數，可以使用函數向導插入函數。

下面使用“插入函數”來來統(tǒng)計“第一次月考成績（成績統(tǒng)計）”工作表中“高等數學”成績大于85的個數、各科目成績大于等于90的個數，操作步驟如下：選擇單元格后單擊”插入函數”按鈕選擇函數后單擊“確定”按鈕

38整理ppt2．使用函數向導如果不能確定函數的拼寫或參數輸入第二個函數參數選擇第一個函數參數

39整理ppt輸入第二個函數參數選擇第一個函數參數39整理ppt

單擊單元格后選擇函數單擊單元格后選擇函數輸入第二個函數參數40整理ppt單擊單元格后選擇