項(xiàng)目四使用公式與函數(shù).ppt

1、,Excel 2010電子表格制作案例教程,公式與函數(shù)基礎(chǔ)知識(shí)及公式的使用方法,公式中的錯(cuò)誤與審核,項(xiàng)目四 使用公式與函數(shù),函數(shù)的使用方法,任務(wù)一,任務(wù)二,任務(wù)三,單元格引用,數(shù)組公式的使用方法,任務(wù)四,任務(wù)五,任務(wù)一 公式與函數(shù)基礎(chǔ)知識(shí)及公式的使用方法,一、認(rèn)識(shí)Excel中的公式和函數(shù),二、公式中的運(yùn)算符及優(yōu)先級(jí),三、公式的創(chuàng)建、移動(dòng)、復(fù)制與修改,一、認(rèn)識(shí)Excel中的公式和函數(shù),公式是對(duì)工作表中的數(shù)據(jù)進(jìn)行計(jì)算的表達(dá)式。利用公式可對(duì)同一工作表的各單元格、同一工作簿中不同工作表的單元格，以及不同工作簿的工作表中單元格的數(shù)值進(jìn)行加、減、乘、除、乘方等各種運(yùn)算。 要輸入公式必須先輸入“=”，然后再

2、在其后輸入表達(dá)式，否則Excel會(huì)將輸入的內(nèi)容作為文本型數(shù)據(jù)處理。表達(dá)式由運(yùn)算符和參與運(yùn)算的操作數(shù)組成。運(yùn)算符可以是算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符；操作數(shù)可以是常量、單元格引用和函數(shù)等。,函數(shù)是預(yù)先定義好的表達(dá)式，它必須包含在公式中。每個(gè)函數(shù)都由函數(shù)名和參數(shù)組成，其中函數(shù)名表示將執(zhí)行的操作（如求平均值函數(shù)AVERAGE），參數(shù)表示函數(shù)將作用的值的單元格地址，通常是一個(gè)單元格區(qū)域（如A2：B7單元格區(qū)域），也可以是更為復(fù)雜的內(nèi)容。在公式中合理地使用函數(shù)，可以完成諸如求和、邏輯判斷和財(cái)務(wù)分析等眾多數(shù)據(jù)處理功能。,二、公式中的運(yùn)算符及優(yōu)先級(jí),1公式中的運(yùn)算符,運(yùn)算符是用來對(duì)公式中的元

3、素進(jìn)行運(yùn)算而規(guī)定的特殊符號(hào)。Excel包含4種類型的運(yùn)算符：算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符。,算術(shù)運(yùn)算符 算術(shù)運(yùn)算符有6個(gè)，其作用是完成基本的數(shù)學(xué)運(yùn)算，并產(chǎn)生數(shù)字結(jié)果。,比較運(yùn)算符 比較運(yùn)算符有6個(gè)，它們的作用是比較兩個(gè)值，并得出一個(gè)邏輯值，即“TRUE（真）”或“FALSE（假）”。,文本運(yùn)算符 使用文本運(yùn)算符“”（與號(hào)）可將兩個(gè)或多個(gè)文本值串起來產(chǎn)生一個(gè)連續(xù)的文本值。例如：輸入“祝你”“快樂、開心！”會(huì)生成“祝你快樂、開心！”。,引用運(yùn)算符 引用運(yùn)算符有3個(gè)，它們的作用是將單元格區(qū)域進(jìn)行合并計(jì)算。,三、公式的創(chuàng)建、移動(dòng)、復(fù)制與修改,1創(chuàng)建公式 要?jiǎng)?chuàng)建公式，可以直接在單元格中

4、輸入，也可以在編輯欄中輸入，輸入方法與輸入普通數(shù)據(jù)相似。,單擊要輸入公式的單元格，然后輸入等號(hào)“=”，接著輸入操作數(shù)和運(yùn)算符，按【Enter】鍵得到計(jì)算結(jié)果。,也可在輸入等號(hào)后單擊要引用的單元格，然后輸入運(yùn)算符，再單擊要引用的單元格（引用的單元格周圍會(huì)出現(xiàn)不同顏色的邊框線，它與單元格地址的顏色一致，便于用戶查看）。,2移動(dòng)與復(fù)制公式 移動(dòng)和復(fù)制公式的操作與移動(dòng)、復(fù)制單元格內(nèi)容的操作方法是一樣的。所不同的是，移動(dòng)公式時(shí)，公式內(nèi)的單元格引用不會(huì)更改，而復(fù)制公式時(shí)，單元格引用會(huì)根據(jù)所用引用類型而變化，即系統(tǒng)會(huì)自動(dòng)改變公式中引用的單元格地址。,將鼠標(biāo)指針移到要復(fù)制公式的單元格右下角的填充柄處，按住鼠標(biāo)

5、左鍵不放進(jìn)行拖動(dòng)。,3修改或刪除公式 要修改公式，可單擊含有公式的單元格，然后在編輯欄中進(jìn)行修改，或雙擊單元格后直接在單元格中進(jìn)行修改，修改完畢按【Enter】鍵確認(rèn)。 刪除公式是指將單元格中應(yīng)用的公式刪除，而保留公式的運(yùn)算結(jié)果。,2運(yùn)算符的優(yōu)先級(jí) 通常情況下，如果公式中只用了一種運(yùn)算符，Excel會(huì)根據(jù)運(yùn)算符的特定順序從左到右計(jì)算公式。如果公式中同時(shí)用到了多個(gè)運(yùn)算符，Excel將按一定優(yōu)先級(jí)由高到低進(jìn)行運(yùn)算。另外，相同優(yōu)先級(jí)的運(yùn)算符，將從左到右進(jìn)行計(jì)算。,任務(wù)二 公式中的錯(cuò)誤與審核,一、公式錯(cuò)誤代碼,二、“公式審核”組,三、查找與公式相關(guān)的單元格,四、追蹤導(dǎo)致公式錯(cuò)誤的單元格,一、公式錯(cuò)誤代

6、碼,“#”：輸入到單元格中的數(shù)值或公式計(jì)算結(jié)果太長(zhǎng)，單元格容納不下。增加單元格寬度可以解決這個(gè)問題。另外，日期運(yùn)算結(jié)果為負(fù)值也會(huì)出現(xiàn)這種情況，此時(shí)可以改變單元格的格式，比如改為文本格式。 “#DIV/0!”：除數(shù)引用了零值單元格或空單元格。 “#N/A”：公式中沒有可用數(shù)值，或缺少函數(shù)參數(shù)。 “#NAME?”：公式中引用了無法識(shí)別的名稱，或刪除了公式正在使用的名稱。例如，函數(shù)的名稱拼寫錯(cuò)誤，使用了沒有被定義的區(qū)域或單元格名稱，引用文本時(shí)沒有加引號(hào)等。,在使用公式時(shí)，經(jīng)常會(huì)遇到公式的返回值為一段代碼的情況，如“#”、“#VALUE”等。了解這些代碼的含義，用戶就可以知道在公式使用過程中出現(xiàn)了什么

7、樣的錯(cuò)誤。下面是一些常見的錯(cuò)誤代碼產(chǎn)生的原因。,“#NULL!”：使用了不正確的區(qū)域運(yùn)算符或引用的單元格區(qū)域的交集為空。例如，輸入公式“=A1:B4 C1:D4”，因?yàn)檫@兩個(gè)單元格區(qū)域交集為空，所以回車后返回值為“#NULL!”。 “#NUM!”：公式產(chǎn)生的結(jié)果數(shù)字太大或太小，Excel無法表示出來，例如，輸入公式“=10309”，由于運(yùn)算結(jié)果太大，公式返回錯(cuò)誤；或在需要數(shù)字參數(shù)的函數(shù)中使用了無法接受的參數(shù)，例如，在輸入開平方的公式(SQRT)時(shí)，引用了負(fù)值的單元格或直接使用了負(fù)值。 “#RFF”：公式引用的單元格被刪除，并且系統(tǒng)無法自動(dòng)調(diào)整，或鏈接的數(shù)據(jù)不可用。 “#VALUE”：當(dāng)公式需要

8、數(shù)字或邏輯值時(shí)，卻輸入了文本；為需要單個(gè)值（而不是區(qū)域）的運(yùn)算符或函數(shù)提供了區(qū)域引用；輸入數(shù)組公式后，沒有按【CtrlShiftEnter】組合鍵確認(rèn)。,在使用公式的過程中，有時(shí)可能會(huì)因人為疏忽，或是表達(dá)式的設(shè)置錯(cuò)誤，導(dǎo)致計(jì)算結(jié)果發(fā)生錯(cuò)誤。使用Excel提供的審核功能可以方便地檢查公式、分析數(shù)據(jù)流向和來源、糾正錯(cuò)誤、把握公式和值的關(guān)聯(lián)關(guān)系等。在“公式”選項(xiàng)卡上的“公式審核”組中可以看到一些按鈕。,二、“公式審核”組,查找公式中引用的單元格,查找某單元格被哪些公式所引用,三、查找與公式相關(guān)的單元格,四、追蹤導(dǎo)致公式錯(cuò)誤的單元格,當(dāng)單元格中的公式出現(xiàn)錯(cuò)誤時(shí)，使用審核工具可以方便地查出錯(cuò)誤是由哪些單

9、元格引起的。,任務(wù)三 函數(shù)的使用方法,一、函數(shù)的分類,二、函數(shù)的使用方法,一、函數(shù)的分類,二、函數(shù)的使用方法,使用函數(shù)時(shí)，應(yīng)首先確認(rèn)已在單元格中輸入了“=”號(hào)，即已進(jìn)入公式編輯狀態(tài)。接下來可輸入函數(shù)名稱，再緊跟著一對(duì)括號(hào)，括號(hào)內(nèi)為一個(gè)或多個(gè)參數(shù)，參數(shù)之間要用逗號(hào)來分隔。用戶可以在單元格中手工輸入函數(shù)，也可以使用函數(shù)向?qū)л斎牒瘮?shù)。,手工輸入一般用于參數(shù)比較單一、簡(jiǎn)單的函數(shù)，即用戶能記住函數(shù)的名稱、參數(shù)等，此時(shí)可直接在單元格中輸入函數(shù)。,1手工直接輸入函數(shù),2使用向?qū)л斎牒瘮?shù) 如果不能確定函數(shù)的拼寫或參數(shù)，可以使用函數(shù)向?qū)л斎牒瘮?shù)。,單擊單元格后選擇函數(shù)類型,在工作表中選擇要計(jì)算的單元格區(qū)域,任務(wù)

10、四 單元格引用,一、相同或不同工作簿、工作表中的引用,二、相對(duì)引用、絕對(duì)引用和混合引用,一、相同或不同工作簿、工作表中的引用,引用不同工作表間的單元格 在同一工作簿中，不同工作表中的單元格可以相互引用，它的表示方法為：“工作表名稱!單元格或單元格區(qū)域地址”。如： Sheet2!F8:F16,引用不同工作簿中的單元格 在當(dāng)前工作表中引用不同工作簿中的單元格的表示方法為： 工作簿名稱.xlsx工作表名稱！單元格（或單元格區(qū)域）地址,二、相對(duì)引用、絕對(duì)引用和混合引用,相對(duì)引用是指引用單元格的相對(duì)地址，其引用形式為直接用列標(biāo)和行號(hào)表示單元格，例如B5，或用引用運(yùn)算符表示單元格區(qū)域，如B5：D15。該方

11、式下如果公式所在單元格的位置改變，引用也隨之改變。默認(rèn)情況下，公式使用相對(duì)引用，如前面講解的復(fù)制公式就是如此。 引用單元格區(qū)域時(shí)，應(yīng)先輸入單元格區(qū)域起始位置的單元格地址，然后輸入引用運(yùn)算符，再輸入單元格區(qū)域結(jié)束位置的單元格地址。,絕對(duì)引用是指引用單元格的精確地址，與包含公式的單元格位置無關(guān)，其引用形式為在列標(biāo)和行號(hào)的前面都加上“$”符號(hào)。例如，若在公式中引用$B$5單元格，則不論將公式復(fù)制或移動(dòng)到什么位置，引用的單元格地址的行和列都不會(huì)改變。,引用中既包含絕對(duì)引用又包含相對(duì)引用的稱為混合引用，如A$1或$A1等，用于表示列變行不變或列不變行變的引用。 如果公式所在單元格的位置改變，則相對(duì)引用改

12、變，而絕對(duì)引用不變。,任務(wù)五 數(shù)組公式的使用方法,一、數(shù)組公式的建立方法,二、數(shù)組公式應(yīng)用,三、數(shù)組公式使用規(guī)則,一、數(shù)組公式的建立方法,數(shù)組公式是指可以同時(shí)對(duì)一組或兩組以上的數(shù)據(jù)進(jìn)行計(jì)算的公式，計(jì)算的結(jié)果可能是一個(gè)，也可能是多個(gè)。 在數(shù)組公式中使用的數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個(gè)區(qū)域數(shù)組，也可以是常量數(shù)組。,區(qū)域數(shù)組是一個(gè)矩形的單元格區(qū)域，如$A$1:$D$5；常量數(shù)組是一組給定的常量，如1,2,3或1,2,3;1,2,3。 數(shù)組公式中的參數(shù)必須為矩形區(qū)域，如無法引用1,2,3;1,2。,要輸入數(shù)組公式，首先必須選擇用來存放結(jié)果的單元格區(qū)域（也可以是一個(gè)單元格），然后輸入公式，最后按【

13、CtrlShiftEnter】組合鍵鎖定數(shù)組公式，Excel將在公式兩邊自動(dòng)加上花括號(hào)“”。,用數(shù)組公式計(jì)算兩個(gè)數(shù)據(jù)區(qū)域的乘積 當(dāng)需要計(jì)算兩個(gè)相同矩形區(qū)域?qū)?yīng)單元格的數(shù)據(jù)之積時(shí)，可以用數(shù)組公式一次性計(jì)算出所有的乘積值，并保存在另一個(gè)大小相同的矩形區(qū)域中。,二、數(shù)組公式應(yīng)用,用數(shù)組公式計(jì)算多列數(shù)據(jù)之和 當(dāng)需要把多個(gè)對(duì)應(yīng)列或行的數(shù)據(jù)相加，并得出對(duì)應(yīng)的和值所組成的一列或一行數(shù)據(jù)時(shí)，可以用一個(gè)數(shù)組公式完成。,用數(shù)組公式同時(shí)對(duì)多個(gè)數(shù)據(jù)區(qū)域進(jìn)行相同的計(jì)算 在公式中用數(shù)組作為參數(shù)時(shí)，所有的數(shù)組必須是同維的，即有相同的行和列。如果數(shù)組參數(shù)或數(shù)組區(qū)域的維數(shù)不匹配，Excel會(huì)自動(dòng)擴(kuò)展該參數(shù)。,輸入數(shù)組公式時(shí)，首先選擇用來保存計(jì)算結(jié)果的單元格區(qū)域，如果計(jì)算公式將產(chǎn)生多個(gè)計(jì)算結(jié)果，必須選擇一個(gè)與計(jì)算結(jié)果所需大小和形狀都相同的單元格區(qū)域。 數(shù)組公式輸入完成后，按下【Ctrl+Shift+Enter】組合鍵，這時(shí)在公式編輯欄中可以看見公式的兩邊自動(dòng)加上了花括號(hào)，表示該公式是一個(gè)數(shù)組公式。 在數(shù)組公式所涉及的區(qū)域中，不能編輯、清除或移動(dòng)單個(gè)單元格，也不能插入或刪除其中任何一個(gè)