第五講公式與函數(shù)一

1、第三節(jié)公式與函數(shù)一、公式的應(yīng)用（一）公式的概念及其公式是指由“=”、運(yùn)算體和運(yùn)算符在單體是指能夠運(yùn)算的數(shù)據(jù)或者數(shù)據(jù)所在單行特定運(yùn)算的符號(hào)。中按特定順序連接而成的運(yùn)算表達(dá)式。運(yùn)算的地址名稱、函數(shù)等；運(yùn)算符是使 Exce1 自動(dòng)執(zhí)Exce1 中,運(yùn)算符主要有四種類型:算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和運(yùn)算符。1算術(shù)運(yùn)算符2比較運(yùn)算符使用比較運(yùn)算符比較兩個(gè)值時(shí),結(jié)果為邏輯值 TRUE 或 FALSE運(yùn)算符含義示例(等號(hào))等于A1B1(大于號(hào))大于A1B1(小于號(hào))小于A1(大于等于號(hào))大于或等于A1B1(小于等于號(hào))小于或等于A1B1(不等號(hào))不等于A1B1運(yùn)算符含義示例(加號(hào))加法112(減號(hào))

2、減法110*(星號(hào))乘法1*11/(正斜杠)除法1/11(脫字號(hào))乘方2416%(百分號(hào))百分比1%0.013文本運(yùn)算符又叫文本連接符,使用符號(hào)“&”（and）連接一個(gè)或多個(gè)文本字符串以產(chǎn)生一串新的文本字符串。加入的文本字符必須使用英文狀態(tài)下的【”】。4運(yùn)算符運(yùn)算一般涉及到單,即公式中輸入單地址時(shí),表示該單的數(shù)據(jù)參與運(yùn)算。當(dāng)?shù)膯沃械臄?shù)據(jù)發(fā)生變化時(shí),公式自動(dòng)重新計(jì)算并重新計(jì)算結(jié)果。Exce1 中,公式總是以等號(hào)=開始,以運(yùn)算體結(jié)束,相鄰的兩個(gè)運(yùn)算體之間必須使用能夠正確表達(dá)二者運(yùn)算關(guān)系的運(yùn)算符進(jìn)行連接。即公式的完整表達(dá)式按以下方式依次:等號(hào)=、第一個(gè)運(yùn)算體、第一個(gè)運(yùn)算符、第二個(gè)運(yùn)算體,以下類推,直

3、至最后一個(gè)運(yùn)算體。（二）公式的創(chuàng)建與修改1公式的創(chuàng)建Excel 中,創(chuàng)建公式的方式包括手動(dòng)輸入和移動(dòng)點(diǎn)擊輸入。（1）手動(dòng)輸入步驟如下:選定需要輸入公式的單,在其對(duì)應(yīng)的編輯欄中手動(dòng)輸入,輸入的內(nèi)容會(huì)在單運(yùn)算符含義示例：(冒號(hào))區(qū)域運(yùn)算符,兩個(gè)之間的區(qū)域B3:C7,表示B3 單到 C7 單之間所有的單,包括 B3 和 C7，(逗號(hào))聯(lián)合運(yùn)算符,多個(gè)合并為一個(gè)B3:C7,D3:E7,表示B3 單到 C7單之間的單和 D3 單到 E7單之間的單(空格)交集運(yùn)算符,取兩個(gè)有單B3:C7 C3:D7,表示B3:C7 區(qū)域和C3:D7 共有區(qū)域 C3:C7 區(qū)域和編輯欄中同步顯示。輸入第一個(gè)運(yùn)算體、第一個(gè)運(yùn)

4、算符、第二個(gè)運(yùn)算體、第二個(gè)運(yùn)算符,依次類推,直到最后一個(gè)運(yùn)算體。確認(rèn)新創(chuàng)建的公式(按 Enter 鍵)注意：手動(dòng)輸入公式時(shí)相匹配。小圓括號(hào),應(yīng)注意其位置是否適當(dāng)以及左括號(hào)是否與右括號(hào)（2）移動(dòng)點(diǎn)擊輸入當(dāng)輸入的公式中含有其他單過移動(dòng)鼠標(biāo)去單擊擬輸入數(shù)值所在單的數(shù)值時(shí),為了避免重復(fù)輸入費(fèi)時(shí)甚至出錯(cuò),還可以通的地址(即單的數(shù)值)來創(chuàng)建公式。移動(dòng)點(diǎn)擊輸入數(shù)值所在單步驟如下:選定需要輸入公式的單運(yùn)算符。的地址后,單將處于數(shù)據(jù)點(diǎn)模式。,在其對(duì)應(yīng)的編輯框中輸入、必須直接輸入的數(shù)值和將鼠標(biāo)移至公式所需數(shù)值所在的單,然后鼠標(biāo)左鍵單擊(也可以通過鍵盤方向鍵移動(dòng)選取)，該數(shù)值所在單四周出現(xiàn)彩色框線,公式所在單及其對(duì)

5、應(yīng)編輯欄中出現(xiàn)該數(shù)值所在單的地址名稱,其名稱的顏色與選取的單框線顏色相同,光標(biāo)自動(dòng)返回公式所在單或其對(duì)應(yīng)編輯欄中該地址名稱的后面,等待輸入其他內(nèi)容。繼續(xù)輸入運(yùn)算符和其他數(shù)值或單確認(rèn)新創(chuàng)建的公式(Enter 或)。移動(dòng)點(diǎn)擊輸入圖解:的地址名稱。注意:在編輯或者移動(dòng)點(diǎn)擊輸入公式時(shí),不能隨便移動(dòng)方向鍵或者單擊公式所在單以外的單,否則單內(nèi)光標(biāo)移動(dòng)之前的位置將自動(dòng)輸入所移至單的地址名稱。2公式的編輯和修改雙擊公式所在的單選中公式所在的單選中公式所在的單（三）公式的運(yùn)算次序直接在單內(nèi)修改內(nèi)容。,按下“F2”建后直接在單內(nèi)更改內(nèi)容。后單擊公式編輯欄,在公式編輯欄中作相應(yīng)更改。1一個(gè)運(yùn)算符或者多個(gè)優(yōu)先級(jí)次相同

6、的運(yùn)算符(如既有加號(hào)又有減號(hào))左到右的順序自動(dòng)運(yùn)算；的公式從2多個(gè)優(yōu)先級(jí)次不同的運(yùn)算符的公式,按照運(yùn)算符優(yōu)先級(jí)次從高到低進(jìn)行智能運(yùn)算,即運(yùn)算符算術(shù)運(yùn)算符邏輯運(yùn)算符可以使用一對(duì)左右小圓括號(hào)（不能使用中括號(hào)）括起來改變運(yùn)算優(yōu)先順序,即先計(jì)算括號(hào)內(nèi),在計(jì)算括號(hào)外。公式中左右小圓括號(hào)的對(duì)數(shù)超過一對(duì)時(shí),按照從內(nèi)向外的順序進(jìn)行計(jì)算。（四）公式運(yùn)算結(jié)果的顯示就緒狀態(tài):單顯示公式自動(dòng)進(jìn)行智能運(yùn)算的結(jié)果編輯欄顯示公式表達(dá)式的完整內(nèi)容編輯狀態(tài):單顯示等號(hào)=及其運(yùn)算體和運(yùn)算符,與所對(duì)應(yīng)編輯欄顯示的內(nèi)容相一致。1查看公式中某步驟的運(yùn)算結(jié)果單中默認(rèn)顯示的運(yùn)算結(jié)果是根據(jù)完整的公式進(jìn)行運(yùn)算的結(jié)果,但可通過下述方法查看公式中

7、某步驟的運(yùn)算結(jié)果:（1）選中公式所在單,雙擊或按“F2”進(jìn)入編輯狀態(tài)。選中公式中需要查看其運(yùn)算結(jié)果的運(yùn)算體和運(yùn)算符,按F9鍵后,被選中的內(nèi)容將轉(zhuǎn)化為運(yùn)算結(jié)果,該運(yùn)算結(jié)果同時(shí)處于被選中狀態(tài)。按下Esc鍵或者Ctr1+Z組合鍵(或單擊撤消按鈕),運(yùn)算結(jié)果將恢復(fù)為公式表達(dá)式的原來內(nèi)容。注意:在運(yùn)算結(jié)果處于被選中狀態(tài)下,如果按下確認(rèn)鍵或者移動(dòng)光標(biāo)鍵,公式中參與運(yùn)算的運(yùn)算體和運(yùn)算符將不復(fù)存在,而被該結(jié)果所替代。如果移動(dòng)鼠標(biāo)去點(diǎn)擊其他單,公式所在單的地址或名將由編輯狀態(tài)切換成數(shù)據(jù)點(diǎn)狀態(tài),公式所在單稱。2公式默認(rèn)顯示方式的改變里同時(shí)顯示被選中單為了檢查公式整體或者其中某一組成部分的表述是否正確,可以通過下述

8、方法實(shí)現(xiàn)公式表達(dá)式與運(yùn)算結(jié)果之間的便捷切換。選中單選中單選中單,按下“Ctr1+”組合鍵。,點(diǎn)擊公式顯示公式命令(Excel 2013),點(diǎn)擊“工具公式審核公式審核模式” (Excel 2003)。運(yùn)算符優(yōu)先級(jí)次運(yùn)算符優(yōu)先級(jí)次:(冒號(hào))1和(加和減)8(空格)2&(與號(hào))9,(逗號(hào))3(等于)10(負(fù)數(shù))4和(大于和小于)11%(百分比)5(小于等于)12(乘方)6(大于等于)13*和/（乘和除）7(不等于)143將公式運(yùn)算結(jié)果轉(zhuǎn)換為數(shù)值采用粘貼的方法將公式原地后,進(jìn)行選擇性粘貼,只粘貼數(shù)值。二、單單的是指在不同單之間建立,以來自其他單的數(shù)據(jù)。的作用在于標(biāo)識(shí)工作表上的單或單區(qū)域,并指明公式中所

9、使用的數(shù)據(jù)的位置。通過,可以在公式中使用工作表不同部分的數(shù)據(jù),或者在多個(gè)公式中使用同一單元格的數(shù)值,常用的單一工作簿不同工作表的單（一）的類型1相對(duì)如果公式使用的是相對(duì)分為相對(duì)、絕對(duì)和混合三種。此外還可以同、不同工作簿的單、甚至其他應(yīng)用程序中的數(shù)據(jù)。,公式的是源數(shù)據(jù)所在單與源數(shù)據(jù)的單的相對(duì)位置,當(dāng)使用了相對(duì)的公式到別的單式,被粘貼公式中的將自動(dòng)更新,數(shù)據(jù)源將指向與當(dāng)前公式所在單位置相對(duì)應(yīng)的單。在相對(duì)中,所的單是相對(duì)地址的列坐標(biāo)和行坐標(biāo)前面沒有任何標(biāo)示符號(hào)。Excel 默認(rèn)使用的單2絕對(duì)。如果公式使用的是絕對(duì),公式的是源數(shù)據(jù)所在單在工作表中的絕對(duì)位置,當(dāng)使用了絕對(duì)的公式到別的單式,被粘貼公式中的

10、不會(huì)更新,數(shù)據(jù)源仍然指向原來的單。在絕對(duì)中,所的單地址的列坐標(biāo)和行坐標(biāo)前面分別加入標(biāo)示符號(hào)$。如果要使3混合公式時(shí)數(shù)據(jù)源的位置不發(fā)生改變,應(yīng)當(dāng)使用絕對(duì)。混合另一個(gè)是是指所。單地址的行標(biāo)與列標(biāo)中只有一個(gè)是相對(duì)的,可以發(fā)生變動(dòng),而（二）輸入單1輸入單（1）在公式中可以直接輸入單的地址單。（2）使用鼠標(biāo)或鍵盤的方向鍵選擇單。2在單地址的列標(biāo)和行號(hào)前直接輸入$符號(hào)。3輸入完單（三）跨工作表單跨工作表單地址后,重復(fù)按F4鍵選擇合適的類型。是指同一工作簿里其他工作表中的單,又稱三維,可以按照以下方式進(jìn)行跨表:1工作表名!數(shù)據(jù)源所在單如“工作表 2!D4”表示地址“工作表 2”中 D4 單的數(shù)據(jù)。2在單中輸

11、入“=”,切換到數(shù)據(jù)源所在工作表鼠標(biāo)單擊數(shù)據(jù)源所在單即可。（四）跨工作簿單跨工作簿單是指其他工作簿中的單,又稱外部,需要按照以下方式進(jìn)行跨工作簿:1工作簿名工作表名!數(shù)據(jù)源所在單地址如:工作簿的管理xlsx工資表!$I$28 表示工作簿的原理xlsx工作簿中的工資表工作表中 I28 單的數(shù)據(jù)。2路徑/工作簿名工作表名!數(shù)據(jù)源所在單地址如:E:電腦相關(guān)excel三、函數(shù)的應(yīng)用04:工作簿的管理xlsx工資表!$I$28。函數(shù)的基本格式是:函數(shù)名(參數(shù)序列)。參數(shù)序列是用于限定函數(shù)運(yùn)算的各個(gè)參數(shù),這些參數(shù)除中文外都必須使用英文半角字符,函數(shù)只能出現(xiàn)在公式中。（一）函數(shù)的方法1選定要輸入公式的單2函

12、數(shù)公式函數(shù)函數(shù)（Excel 2013）；函數(shù)（ Excel 2003 ）。直接使用函數(shù)庫(kù)的函數(shù)（Excel 2013）。單擊“fx”?？旖萱I Shift+F3。3設(shè)置函數(shù)需要的數(shù)據(jù)（直接輸入或鼠標(biāo)選擇單）（二）常用函數(shù) 1統(tǒng)計(jì)函數(shù) 常用參數(shù)含義:number數(shù)據(jù)或區(qū)域；range范圍,即條件判斷的單區(qū)域；criteria標(biāo)準(zhǔn),即查找的條件；value值或價(jià)值,在統(tǒng)計(jì)函數(shù)中,僅表示數(shù)字型數(shù)據(jù)。（1）MAXMAX(number1,number2,)用于返回?cái)?shù)值參數(shù)中的最大值,忽略參數(shù)中的邏輯值和文本。MINMIN(number1,number2,)用于返回?cái)?shù)值參數(shù)中的最小值,忽略參數(shù)中的邏輯值和文

13、本。SUMSUM(number1,number2,)用于計(jì)算單（4）AVERAGE區(qū)域中所有數(shù)值的和。AVERAGE(number1,number2,)用于返回參數(shù)的算術(shù)平均值。（5）COUNTCOUNT(value1,value2,)用于計(jì)算包含數(shù)字的單COUNT 函數(shù)的使用方法:以及參數(shù)列表中數(shù)字的個(gè)數(shù)。SUMIFSUMIF(range,criteria,sum_range)用于對(duì)滿足條件的單AVERAGEIF求和。AVERAGEIF(range,criteria,average_range)用于返回某個(gè)區(qū)域內(nèi)滿足給定條件的所有單的算術(shù)平均值。（8）COUNTIFCOUNTIF(range,criteria)用于對(duì)區(qū)域中滿足單個(gè)指定條件的單SUMIF 函數(shù)的使用方法:進(jìn)行計(jì)數(shù)。AB1屬性值傭金2100,0007,0003200,00014,0004300,00021,0005400,00028,000公式結(jié)果SUMIF(A2:A5,160000,B2:B5)屬性值高于 160,000 的傭金之和 (63,0