EXCEL表格公式培訓(xùn)

1、常用常用EXCELEXCEL表格函數(shù)表格函數(shù)講解人：谷曉宗 電話(huà)固話(huà)xcel基本操作1公式和函數(shù)2函數(shù)的組合應(yīng)用3Excel數(shù)據(jù)處理與分析 Excel基本操作excel文件打開(kāi)、關(guān)閉、保存、重命名等 單元格的合并、拆分、設(shè)置行高、列寬、刪除、清除等單元格的數(shù)據(jù)格式、對(duì)齊方式、字體、邊框、底紋等 數(shù)據(jù)的輸入、修改、復(fù)制等 工作薄的新增、修改、移動(dòng)、復(fù)制等 工作薄的新增、修改、移動(dòng)、復(fù)制等 公式與函數(shù)公式的組成元素常用運(yùn)算符及單元格的引用地址常用計(jì)算公式和函數(shù)常用計(jì)算公式和函數(shù) 公式與函數(shù) 公式由下列幾個(gè)元素組成：公式由下列幾個(gè)元素組成：運(yùn)算符

2、，如“+”，“*”；單元格（或區(qū)域）引用；數(shù)值或文本；工作表函數(shù)，如“sum”，“average”； 例如： =sum(A1*B1,A2*B2,A3*B3)/3 函數(shù)是可以嵌套的函數(shù)是可以嵌套的 例如： =sum(average(A1：C1),average(A2:C2) ExcelExcel公式中常用運(yùn)算符公式中常用運(yùn)算符 公式與函數(shù)類(lèi)型類(lèi)型表示形式表示形式優(yōu)先級(jí)優(yōu)先級(jí)算術(shù)運(yùn)算符算術(shù)運(yùn)算符+（加）、-（減）、*（乘）、/（除）、%（百分比）、（乘方）從高到低分為3個(gè)級(jí)別：百分比和乘方、乘和除、加和減關(guān)系運(yùn)算符關(guān)系運(yùn)算符=（等于）、（大于）、=（大于等于）、=（小于等于）、（不等于）優(yōu)先級(jí)相同

3、文本運(yùn)算符文本運(yùn)算符&（文本的連接）引用運(yùn)算符引用運(yùn)算符：（區(qū)域）、，（聯(lián)合）、空格（交叉）從高到低依次為：區(qū)域、聯(lián)合、交叉： 包括兩個(gè)引用在內(nèi)的所有單元格的引用，如SUM（A1：C3）， 對(duì)多個(gè)引用合并為一個(gè)引用，如SUM（A1，C3）空格 產(chǎn)生同時(shí)隸屬于兩個(gè)引用的單元格區(qū)域的引用，如SUM（A1：C4 B2：D3） 單元格的引用地址單元格的引用地址 公式與函數(shù)用列號(hào)行號(hào)表示，如：B1、A2：C4等，其特點(diǎn)是公式復(fù)制和移動(dòng)時(shí)，該地址會(huì)根據(jù)一動(dòng)的位置自動(dòng)調(diào)節(jié)。在列號(hào)和行號(hào)前都加上美元符號(hào)“$”，如$B$1，其特點(diǎn)是公式復(fù)制和移動(dòng)時(shí)，該地址始終保持不變。相對(duì)引用絕對(duì)引用混合引用在列號(hào)或行

4、號(hào)前加上美元符號(hào)“$”，如$B1和B$1，$B1是列不變，行變化；B$1是行不變，列變化。 常用計(jì)算公式和函數(shù)常用計(jì)算公式和函數(shù) 公式與函數(shù)函數(shù)名稱(chēng)函數(shù)名稱(chēng)功能描述功能描述AVERAGE參數(shù)的平均值SUM求和COUNT計(jì)算參數(shù)列表中值得個(gè)數(shù)IF邏輯函數(shù)，判斷是否滿(mǎn)足某一條件MAX返回一組數(shù)中的最大值MIN返回一組數(shù)中的最小值VLOOKUP在數(shù)組第一列中查找，然后在行之間移動(dòng)以返回單元格的值 公式與函數(shù) AVERAGE函數(shù)的功能。AVERAGE的意思就是求平均值。它的參數(shù)可以是常數(shù)，也可以是表格里包含數(shù)字的區(qū)域。如果區(qū)域里有空白表格，就自動(dòng)忽略。 AVERAGE的語(yǔ)法。AVERAGE（a,b,c

5、,.）或AVERAGE（rang）其中，a,b,c,.中的各個(gè)字母可以是任何數(shù)字或是單元格的地址。rang表示Excel表格中的一塊區(qū)域。 公式與函數(shù)第一種：對(duì)數(shù)字求平均值比如我要計(jì)算1，23，4，567，890這些數(shù)字的平均值。任意單元格中輸入：=average（1，23，4，567，890）回車(chē)此時(shí)，表示計(jì)算1，23，4，567，890的平均值并顯示到該單元格中。（見(jiàn)下圖） 公式與函數(shù)第二種：對(duì)區(qū)域內(nèi)求平均值例如：在A2，E4這一塊區(qū)域中分別輸入上述數(shù)字，中間可以空白，系統(tǒng)自動(dòng)無(wú)視它們，然后在這一區(qū)域外的任意空白單元格內(nèi)輸入=average（A2:E4）回車(chē)此時(shí)，表示計(jì)算左上角為A2右下角

6、為E4的這個(gè)區(qū)域中所有單元格中的值的平均值并顯示到該單元格中。（見(jiàn)下圖） 公式與函數(shù) sum函數(shù)的功能。什么叫sum函數(shù)呢，其實(shí)簡(jiǎn)單的說(shuō)，就是幾組數(shù)據(jù)的求和，就像我們數(shù)學(xué)常用到的加法一個(gè)意思，他們的區(qū)別在于，加法需要自己一個(gè)一個(gè)的去加，但是sum函數(shù)的話(huà)，只要你輸入公式，就可以把你這一組的數(shù)據(jù)全部求和。 SUM的語(yǔ)法。SUM（a,b,c,.）或SUM（rang）其中，a,b,c,.中的各個(gè)字母可以是任何數(shù)字或是單元格的地址。rang表示Excel表格中的一塊區(qū)域。 公式與函數(shù) count函數(shù)的功能。計(jì)算參數(shù)列表中的數(shù)字項(xiàng)的個(gè)數(shù),但只有數(shù)字類(lèi)型的數(shù)據(jù)才被計(jì)數(shù)。 count的語(yǔ)法。COUNT(a,

7、b, c,.)或COUNT(rang)其中，a,b,c,.中的各個(gè)字母可以是單元格的地址。rang表示Excel表格中的一塊區(qū)域。 公式與函數(shù) count函數(shù)的衍生函數(shù)countif。countif函數(shù)是指計(jì)算某個(gè)區(qū)域中給定條件的單元格的數(shù)目 公式格式：=COUNTIF（Range,Criteria) Range 要計(jì)算其中非空單元格數(shù)目的區(qū)域 Criteria 以數(shù)字、表達(dá)式或文本形式定義的條件 公式與函數(shù) MAX/MIN函數(shù)。MAX/MIN函數(shù)的功能基本類(lèi)似，主要是選擇范圍內(nèi)的最大/最小值，其語(yǔ)法、使用方式與SUM等函數(shù)相同。在此不再贅述 COUNT函數(shù)。COUNT函數(shù)的功能為統(tǒng)計(jì)范圍內(nèi)符

8、合條件的單元格數(shù)，其語(yǔ)法、使用方式與SUM等函數(shù)相同。 公式與函數(shù) IF函數(shù)的功能。對(duì)滿(mǎn)足條件的數(shù)據(jù)進(jìn)行處理，條件滿(mǎn)足則輸出結(jié)果1，不滿(mǎn)足則輸出結(jié)果2?？梢允÷越Y(jié)果1或結(jié)果2，但不能同時(shí)省略。 IF的語(yǔ)法。IF(logical_test,value_if_true,value_if_false)其中，logical_test表示判斷條件，value_if_true表示若符合條件時(shí)返回的值，value_if_false表示不符合條件時(shí)返回的值。 公式與函數(shù) IF函數(shù)的基本用法。普通公式的單條件表達(dá)假定經(jīng)理級(jí)別員工年齡超過(guò)35歲為“超齡”，年齡低于35歲為“未超齡”，則可編訂函數(shù)：=IF(D835

9、,超齡,未超齡)如圖 公式與函數(shù) IF函數(shù)的基本用法。普通公式的雙條件表達(dá)假定經(jīng)理級(jí)別員工年齡超過(guò)35歲為“超齡”，年齡低于35歲為“未超齡”；而主任級(jí)別員工年齡超過(guò)30歲為“超齡”，低于30歲為“未超齡”則可編訂函數(shù)：=IF(B3=經(jīng)理,IF(D335,超齡,未超齡),IF(D330,超齡,未超齡)如圖 公式與函數(shù) IF函數(shù)的基本用法。普通公式的多條件表達(dá)假定經(jīng)理級(jí)別員工，大專(zhuān)年齡超過(guò)35歲為“超齡”，大專(zhuān)年齡低于35歲為“未超齡”；而本科年齡超過(guò)40歲為“超齡”，低于40歲為“未超齡”主任級(jí)別員工，大專(zhuān)年齡超過(guò)25歲為“超齡”，低于25歲為“未超齡”；而本科年齡超過(guò)30歲為“超齡”，低于3

10、0歲為“未超齡”則可編訂函數(shù)：？=IF(B8=經(jīng)理,IF(C8=本科,IF(D840,超齡,未超齡),IF(D835,超齡,未超齡),IF(C8=本科,IF(D835,超齡,未超齡),IF(D830,超齡,未超齡) 公式與函數(shù) IF函數(shù)的特點(diǎn)。IF函數(shù)最大的特點(diǎn)在于他的可嵌套性和可混合性，在EXCEL2003中 函數(shù) IF 可以嵌套七層，在EXCEL2007中可以嵌套64層，用 value_if_false 及 value_if_true 參數(shù)可以構(gòu)造復(fù)雜的檢測(cè)條件。 公式與函數(shù) VLOOKUP函數(shù)的功能。首列查找函數(shù)，即以某個(gè)要查找的條件，在區(qū)域的首列里面查找，若有匹配的，則返回該數(shù)據(jù)對(duì)應(yīng)的第幾列數(shù)據(jù)。 IVLOOKUP的語(yǔ)法。VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)。表述就是VLOOKUP(查找值，查找范圍，查找列數(shù)，精確匹配或者近似匹配）在此，在我們的工作中，幾乎都使用精確匹配，該項(xiàng)的參數(shù)一定要選擇為false。否則返回值會(huì)出乎你的意料。 公式與函數(shù) VLOOKUP函數(shù)的基本用法。對(duì)目標(biāo)條件的篩選，如在某一個(gè)數(shù)據(jù)繁瑣的表格中找出某一項(xiàng)內(nèi)容。如查找王五等人的學(xué)歷如圖 公式與函數(shù) VLOOKUP函數(shù)的常見(jiàn)用法。VLOOKUP函數(shù)一般情況下不會(huì)單獨(dú)使用，會(huì)用于在多個(gè)表格之間進(jìn)行數(shù)據(jù)