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

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

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

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

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

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

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

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

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

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

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