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

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

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

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

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

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

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

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

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

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

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

11、入“=”,切換到數(shù)據(jù)源所在工作表鼠標單擊數(shù)據(jù)源所在單即可。（四）跨工作簿單跨工作簿單是指其他工作簿中的單,又稱外部,需要按照以下方式進行跨工作簿: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ù)運算的各個參數(shù),這些參數(shù)除中文外都必須使用英文半角字符,函數(shù)只能出現(xiàn)在公式中。（一）函數(shù)的方法1選定要輸入公式的單2函

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

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