Excel公式的使用.ppt

第6章 公式和函數(shù)的使用,6.1 使用公式 6.2 課堂練習(xí)計(jì)算月考成績(jī)總分 6.3 公式中的引用設(shè)置 6.4 公式中的錯(cuò)誤和審核 6.5 使用函數(shù) 6.6 課堂練習(xí)計(jì)算水、電費(fèi)合計(jì)金額,6.1 使用公式,公式是對(duì)工作表中數(shù)據(jù)進(jìn)行計(jì)算的表達(dá)式，函數(shù)是Excel預(yù)先定義好的用來(lái)執(zhí)行某些計(jì)算、分析功能的封裝好的表達(dá)式，即用戶(hù)只需按要求為函數(shù)指定參數(shù)，就能獲得預(yù)期結(jié)果，而不必知道其內(nèi)部是如何實(shí)現(xiàn)的。 利用公式可對(duì)同一工作表的各單元格，同一工作簿中不同工作表的單元格，甚至其他工作簿的工作表中單元格的數(shù)值進(jìn)行加、減、乘、除、乘方等各種運(yùn)算。 公式必須以等號(hào)“=”開(kāi)頭，后面跟表達(dá)式。表達(dá)式由運(yùn)算符和參與運(yùn)算的操作數(shù)組成。運(yùn)算符可以是算術(shù)運(yùn)算符、比較操作符、文本連接符和引用操作符；操作數(shù)可以是常量、單元格地址和函數(shù)等。,6.1.1 公式中的運(yùn)算符,運(yùn)算符是用來(lái)對(duì)公式中的元素進(jìn)行運(yùn)算而規(guī)定的特殊符號(hào)。Excel 2007包含4種類(lèi)型的運(yùn)算符：算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符。,1算術(shù)運(yùn)算符,算術(shù)運(yùn)算符有6個(gè)，如下表所示，其作用是完成基本的數(shù)學(xué)運(yùn)算，并產(chǎn)生數(shù)字結(jié)果。,2比較運(yùn)算符,比較運(yùn)算符有6個(gè)，如下表所示。它們的作用是可以比較兩個(gè)值，結(jié)果為一個(gè)邏輯值，不是“TRUE（真）”，就是“FALSE（假）”。,3文本運(yùn)算符,文本運(yùn)算符只有1個(gè)，如下表所示，使用文本連接符（）可加入或連接一個(gè)或更多字符串以產(chǎn)生一長(zhǎng)文本。例如：“2008年”“北京奧運(yùn)會(huì)”就產(chǎn)生“2008年北京奧運(yùn)會(huì)”。,4引用運(yùn)算符,引用運(yùn)算符有3個(gè)，如下表所示，可以將單元格區(qū)域進(jìn)行合并計(jì)算。,6.1.2 公式中的運(yùn)算順序,通常情況下，Excel根據(jù)公式中運(yùn)算符的特定順序從左到右計(jì)算公式。如果公式中同時(shí)用到了多個(gè)運(yùn)算符，Excel將按一定的順序（優(yōu)先級(jí)由高到低）進(jìn)行運(yùn)算，如下表所示。另外，相同優(yōu)先級(jí)的運(yùn)算符，將從左到右進(jìn)行計(jì)算。,若要更改求值的順序，可以將公式中要先計(jì)算的部分用括號(hào)括起來(lái)。,6.1.3 創(chuàng)建和編輯公式,1創(chuàng)建公式,要?jiǎng)?chuàng)建公式，可以直接在單元格中輸入，也可以在編輯欄中輸入。,直接在單元格中輸入,對(duì)于簡(jiǎn)單的公式，我們可以直接在單元格中輸入：首先單擊需輸入公式的單元格，接著輸入=（等號(hào)），然后輸入公式內(nèi)容，最后單擊編輯欄上的“輸入”按鈕 ，或按【Enter】鍵結(jié)束。,在編輯欄中輸入,單擊要輸入公式的單元格，然后單擊編輯欄，在編輯欄中輸入等號(hào)“=”，輸入操作數(shù)和運(yùn)算符，輸入完畢，按下【Enter】鍵或單擊編輯欄上的“輸入”按鈕。,2編輯公式,要修改公式，可單擊含有公式的單元格，然后在編輯欄中進(jìn)行修改，修改完畢按【Enter】鍵即可。要?jiǎng)h除公式，可單擊含有公式的單元格，然后按【Delete】鍵，如下圖所示。,6.1.4 移動(dòng)和復(fù)制公式,1移動(dòng)公式,要移動(dòng)公式，最簡(jiǎn)單的方法就是：選中包含公式的單元格，將鼠標(biāo)指針移到單元格的邊框線上，當(dāng)鼠標(biāo)指針變成十字箭頭形狀 時(shí)，按住鼠標(biāo)左鍵不放，將其拖到目標(biāo)單元格后釋放鼠標(biāo)即可。,2復(fù)制公式,使用“填充柄”,在Excel中，當(dāng)我們想將某個(gè)單元格中的公式復(fù)制到同列（行）中相鄰的單元格時(shí)，可以通過(guò)拖動(dòng)“填充柄”來(lái)快速完成。方法是：按住鼠標(biāo)左鍵向下（也可以是上、左或右，據(jù)實(shí)際情況而定）拖動(dòng)要復(fù)制的公式的單元格右下角的填充柄，到目標(biāo)位置后釋放鼠標(biāo)即可。,利用“選擇性粘貼”,復(fù)制含有公式的單元格（此單元格包含格式），然后選擇目標(biāo)單元格，單擊“粘貼”按鈕下方的三角按鈕，在展開(kāi)的列表中選擇“選擇性粘貼”項(xiàng) ，在打開(kāi)的對(duì)話(huà)框中選中“公式”單選鈕，然后單擊“確定”即可。,6.2 課堂練習(xí)計(jì)算學(xué)生成績(jī)總分,下面通過(guò)制作如下圖所示的第一次月考成績(jī)表，來(lái)熟悉一下公式的輸入及使用填充柄復(fù)制公式的方法。操作步驟如下：,1 打開(kāi)文件“第一次月考成績(jī)”，然后單擊I2單元格，輸入等號(hào)“=”，然后輸入C2+D2+E2+F2+G2+H2，單擊編輯欄上的“輸入”按鈕，得到第一個(gè)學(xué)生的成績(jī)總分。,2 按住鼠標(biāo)左鍵向下拖動(dòng)I2單元格右下角的填充柄，至I13單元格后釋放鼠標(biāo)，得到其他學(xué)生的成績(jī)總分。最后將工作簿另存。,6.3 公式中的引用設(shè)置,引用的作用在于標(biāo)識(shí)工作表中的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的位置。通過(guò)單元格引用，可以在一個(gè)公式中使用工作表不同部分的數(shù)據(jù)，或者在多個(gè)公式中使用同一個(gè)單元格的數(shù)值，還可以引用同一個(gè)工作簿中不同工作表中的單元格，甚至其他工作簿中的數(shù)據(jù)。當(dāng)公式中引用的單元格數(shù)值發(fā)生變化時(shí)，公式會(huì)自動(dòng)更新單元格的內(nèi)容。,6.3.1 引用單元格或單元格區(qū)域,公式大都使用單元格地址引用一個(gè)或多個(gè)單元格。 要在公式中引用單元格，只需單擊要在其中輸入公式的單元格，接著輸入等號(hào)（=），然后直接輸入該單元格地址或單擊該單元格，最后按【Enter】鍵即可。,若要引用單元格區(qū)域，則輸入?yún)^(qū)域左上角單元格的地址、冒號(hào)（：）和區(qū)域右下角單元格地址，例如：A2:E2表示單元格A2到單元格E2的區(qū)域。 下表列出了在公式中使用地址引用單元格或單元格區(qū)域示例。,6.3.2 相對(duì)引用、絕對(duì)引用和混合引用,1相對(duì)引用,相對(duì)引用指的是單元格的相對(duì)地址，其引用形式為直接用列標(biāo)和行號(hào)表示單元格，例如A1。如果公式所在單元格的位置改變，引用也隨之改變。默認(rèn)情況下，公式使用相對(duì)引用。,在復(fù)制公式時(shí)，Excel將自動(dòng)調(diào)整復(fù)制公式的引用，以便引用相對(duì)于當(dāng)前公式位置的其他單元格。,2絕對(duì)引用,絕對(duì)引用指的是單元格的精確地址，與包含公式的單元格位置無(wú)關(guān)，其引用形式為在列標(biāo)和行號(hào)的前面都加上“$”號(hào)。例如，已知快樂(lè)書(shū)店三個(gè)分店同種書(shū)籍一天的銷(xiāo)售量，現(xiàn)在要計(jì)算各分店的銷(xiāo)售總額，操作步驟如下圖所示。,含有絕對(duì)引用的公式,3混合引用,引用中既包含絕對(duì)引用又包含相對(duì)引用的稱(chēng)為混合引用，如A$1或$A1等，用于表示列變行不變或列不變行變的引用。 如果公式所在單元格的位置改變，則相對(duì)引用改變，而絕對(duì)引用不變。下圖所示單元格D2中的公式為：=$B2*$C2，當(dāng)將其復(fù)制到D3單元格中時(shí)，公式變?yōu)椋?$B3*$C3。,6.3.3 引用不同工作表中的單元格或單元格區(qū)域,在同一工作簿中，不同工作表中的單元格可以相互引用，它的表示方法為：“工作表名稱(chēng)!單元格地址”。如： Sheet3!E6,6.3.4 不同工作簿間單元格的引用,除了可以在同一工作表、同一工作簿的不同工作表中引用單元格外，還可以在當(dāng)前工作表中引用不同工作簿中的單元格，它的表示方法為： 工作簿名稱(chēng).xlsx工作表名稱(chēng)！單元格地址,6.4 公式中的錯(cuò)誤和審核,6.4.1 公式中返回的錯(cuò)誤值,（1）錯(cuò)誤值： 含義：輸入到單元格中的數(shù)據(jù)太長(zhǎng)或單元格公式所產(chǎn)生的結(jié)果太大，使結(jié)果在單元格中顯示不下，或是對(duì)日期和時(shí)間格式的單元格做減法，出現(xiàn)了負(fù)值。 解決辦法：增加列的寬度，使結(jié)果能夠完全顯示。如果是由日期或時(shí)間相減產(chǎn)生了負(fù)值引起的，可以改變單元格的格式，比如改為文本格式。,（3）錯(cuò)誤值：VALUE! 含義：輸入引用文本項(xiàng)的數(shù)學(xué)公式。如果使用了不正確的參數(shù)或運(yùn)算符，或者當(dāng)執(zhí)行自動(dòng)更正公式功能時(shí)不能更正公式，都將產(chǎn)生錯(cuò)誤信息VALUE!。 解決辦法：應(yīng)確認(rèn)公式或函數(shù)所需的運(yùn)算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。例如，單元格C4中有一個(gè)數(shù)字或邏輯值，而單元格D4包含文本，則在計(jì)算公式=C4D4時(shí)，系統(tǒng)不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類(lèi)型，因而返回錯(cuò)誤值VALUE!。,（2）錯(cuò)誤值：DIV/0! 含義：試圖除以0。這個(gè)錯(cuò)誤的產(chǎn)生通常有下面幾種情況：除數(shù)為0、在公式中除數(shù)使用了空單元格或是包含零值單元格的單元格引用。 解決辦法：修改單元格引用，或者在用作除數(shù)的單元格中輸入不為零的值。,（4）錯(cuò)誤值：REF! 含義：刪除了被公式引用的單元格范圍。 解決辦法：恢復(fù)被引用的單元格范圍，或是重新設(shè)定引用范圍。 （5）錯(cuò)誤值：N/A 含義：無(wú)信息可用于所要執(zhí)行的計(jì)算。在建立模型時(shí)，用戶(hù)可以在單元格中輸入#N/A，以表明正在等待數(shù)據(jù)。任何引用含有#N/A值的單元格都將返回#N/A。 解決辦法：在等待數(shù)據(jù)的單元格內(nèi)填上數(shù)據(jù)。 （6）錯(cuò)誤值：NUM! 含義：提供了無(wú)效的參數(shù)給工作表函數(shù)，或是公式的結(jié)果太大或太小而無(wú)法在工作表中表示。 解決辦法：確認(rèn)函數(shù)中使用的參數(shù)類(lèi)型正確。如果是公式結(jié)果太大或太小，就要修改公式。,（7）錯(cuò)誤值：NAME? 含義：在公式中使用了Excel不能識(shí)別的文本。例如，可能是輸錯(cuò)了名稱(chēng)，或是輸入了一個(gè)已刪除的名稱(chēng)。另外，如果沒(méi)有將文字串括在雙引號(hào)中，也會(huì)產(chǎn)生此錯(cuò)誤值。 解決辦法：如果是使用了不存在的名稱(chēng)而產(chǎn)生這類(lèi)錯(cuò)誤，應(yīng)確認(rèn)使用的名稱(chēng)確實(shí)存在；如果是函數(shù)名拼寫(xiě)錯(cuò)誤，改正過(guò)來(lái)就可以了；將文字串括在雙引號(hào)中；確認(rèn)公式中使用的所有區(qū)域引用都使用了冒號(hào)（:），如SUM（C1:C10）。,（8）錯(cuò)誤值：NULL！ 含義：在公式中的兩個(gè)范圍之間插入一個(gè)空格以表示交叉點(diǎn)，但這兩個(gè)范圍沒(méi)有公共單元格。例如，輸入“=SUM(A1:A10 C1:C10)”，就會(huì)產(chǎn)生這種情況。 解決辦法：改正區(qū)域運(yùn)算符使之正確；更改引用使之相交。例如，在兩個(gè)區(qū)域之間用逗號(hào)隔開(kāi)“=SUM(A1:A10，C1:C10)”。,6.4.2 公式審核,Excel 2007提供了公式審核功能，通過(guò)它可以將任意單元格中的數(shù)據(jù)來(lái)源和計(jì)算結(jié)果顯示出來(lái)，使用戶(hù)明白計(jì)算的方式。,1查找公式中引用的單元格,要查找公式中引用的單元格，只需單擊含有公式的單元格，即可在編輯欄中顯示引用的單元格。,2追蹤為公式提供數(shù)據(jù)的單元格,要顯示公式中引用的單元格，可單擊含有公式的單元格，然后單擊“公式”選項(xiàng)卡上“公式審核”組中的“追蹤引用單元格”按鈕 ，此時(shí)，公式所引用的單元格就會(huì)有追蹤箭頭指向公式所在的單元格，在代表數(shù)據(jù)流向的箭頭上，每一個(gè)引用的單元格上都會(huì)出現(xiàn)一個(gè)藍(lán)色的圓點(diǎn)。,3追蹤導(dǎo)致公式錯(cuò)誤的單元格,Excel會(huì)自動(dòng)對(duì)輸入的公式進(jìn)行檢查，當(dāng)發(fā)生錯(cuò)誤時(shí)，單元格的左上角會(huì)出現(xiàn)一個(gè)綠色的小三角 ，單擊該單元格，會(huì)在該單元格左側(cè)出現(xiàn) ，按鈕。單擊 按鈕，會(huì)彈出快捷菜單，提供解決此錯(cuò)誤的途徑。,要追蹤導(dǎo)致公式錯(cuò)誤的單元格，可單擊“公式審核”組中的“錯(cuò)誤檢查”按鈕 右側(cè)的三角按鈕 ，在展開(kāi)的列表中單擊“追蹤錯(cuò)誤”項(xiàng)，即可標(biāo)識(shí)出產(chǎn)生錯(cuò)誤的單元格 。,4追蹤產(chǎn)生循環(huán)引用的單元格,當(dāng)某個(gè)公式直接或間接引用了該公式所在的單元格時(shí)，稱(chēng)作循環(huán)引用。當(dāng)打開(kāi)的工作簿中含有循環(huán)引用時(shí)，Excel會(huì)顯示警告提示框。,當(dāng)按【Enter】鍵時(shí)，會(huì)彈出如下左圖所示的對(duì)話(huà)框。如果確定要進(jìn)行循環(huán)引用，則單擊“確定”按鈕，結(jié)果如下右圖所示，此時(shí)“追蹤從屬單元格”列表中的“循環(huán)引用”項(xiàng)變?yōu)榭捎?，并顯示出循環(huán)引用的單元格。,單元格A5中的值依賴(lài)于單元格A2，而A2單元格中的值又依賴(lài)于A5，它們之間形成了間接的循環(huán)引用,6.5 使用函數(shù),函數(shù)是預(yù)先定義好的表達(dá)式，它必須包含在公式中。每個(gè)函數(shù)都由函數(shù)名和變量組成，其中函數(shù)名表示將執(zhí)行的操作，變量表示函數(shù)將作用的值的單元格地址，通常是一個(gè)單元格區(qū)域，也可以是更為復(fù)雜的內(nèi)容。在公式中合理地使用函數(shù)，可以完成如求和、邏輯判斷、財(cái)務(wù)分析等眾多數(shù)據(jù)處理功能。,6.5.1 函數(shù)的分類(lèi),財(cái)務(wù)函數(shù)：可以進(jìn)行一般的財(cái)務(wù)計(jì)算。例如，確定貸款的支 付額、投資的未來(lái)值或凈現(xiàn)值，以及債券或息票的價(jià)值。 時(shí)間和日期函數(shù)：可以在公式中分析和處理日期值和時(shí)間值。 數(shù)學(xué)和三角函數(shù)：可以處理簡(jiǎn)單和復(fù)雜的數(shù)學(xué)計(jì)算。 統(tǒng)計(jì)函數(shù)：用于對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析。 查找和引用函數(shù)：在工作表中查找特定的數(shù)值或引用的單元格。,邏輯函數(shù)：可以進(jìn)行真假值判斷，或者進(jìn)行復(fù)合檢驗(yàn)。 信息函數(shù)：用于確定存儲(chǔ)在單元格中的數(shù)據(jù)的類(lèi)型。 工程函數(shù)：用于工程分析。 多維數(shù)據(jù)集函數(shù)：主要用于返回多維數(shù)據(jù)集的重要性能指標(biāo)、屬性、層次結(jié)構(gòu)中的成員或組等。,數(shù)據(jù)庫(kù)函數(shù)：分析工作表中的數(shù)值是否符合特定條件。 文本函數(shù)：可以在公式中處理文字串。,6.5.2 函數(shù)的使用方法,使用函數(shù)時(shí)，應(yīng)首先確認(rèn)已在單元格中輸入了“=”號(hào)，即已進(jìn)入公式編輯狀態(tài)。接下來(lái)可輸入函數(shù)名稱(chēng)，再緊跟著一對(duì)括號(hào)，括號(hào)內(nèi)為一個(gè)或多個(gè)參數(shù)，參數(shù)之間要用逗號(hào)來(lái)分隔。,1手工輸入函數(shù),如果用戶(hù)能記住函數(shù)的名稱(chēng)、參數(shù)，可直接在單元格中輸入函數(shù)。方法如下：?jiǎn)螕粢斎牒瘮?shù)的單元格，然后依次輸入等號(hào)、函數(shù)名、左括號(hào)、具體參數(shù)和右括號(hào)，最后單擊編輯欄中的“輸入”按鈕或按【Enter】鍵，此時(shí)在輸入函數(shù)的單元格中將顯示公式的運(yùn)算結(jié)果。,2使用函數(shù)向?qū)?如果不能確定函數(shù)的拼寫(xiě)或參數(shù)，可以使用函數(shù)向?qū)Р迦牒瘮?shù)。,下面使用“插入函數(shù)”來(lái)來(lái)統(tǒng)計(jì)“第一次月考成績(jī)（成績(jī)統(tǒng)計(jì)）”工作表中“高等數(shù)學(xué)”成績(jī)大于85的個(gè)數(shù)、各科目成績(jī)大于等于90的個(gè)數(shù)，操作步驟如下：,選擇單元格后單擊”插入函數(shù)”按鈕,選擇函數(shù)后單擊“確定”按鈕,輸入第二個(gè)函數(shù)參數(shù),選擇第一個(gè)函數(shù)參數(shù),輸入第二個(gè)函數(shù)參數(shù),在“公式”選項(xiàng)卡的“函數(shù)庫(kù)”中，系統(tǒng)分類(lèi)列出了各種函數(shù)。其中，單擊各分類(lèi)函數(shù)按鈕可打開(kāi)函數(shù)列表，將光標(biāo)指向某個(gè)函數(shù)并稍等片刻，可查看函數(shù)幫助，如下圖所示。要使用某個(gè)函數(shù)，只要單擊選擇該函數(shù)就可以了。,6.5.3 獲取函數(shù)幫助,要獲取某個(gè)函數(shù)的幫助，可先打開(kāi)“插入函數(shù)”對(duì)話(huà)框，然后選擇函數(shù)，最后單擊對(duì)話(huà)框左