excel常用公式及示例介紹剖析

°成都勘測(cè)設(shè)計(jì)研究院有限公司年）國(guó)電建CHENGDUENGINEERINGCORPORATIONLIMITEDfHCiWlftCHiNAEXCEL應(yīng)用技巧交流（一）—基礎(chǔ)函數(shù)應(yīng)用目錄TOC\o"1-5"\h\z1、 公式相關(guān)概念 12、 絕對(duì)引用和相對(duì)引用 13、 條件函數(shù)（if） 1\o"CurrentDocument"4、 取小數(shù)位數(shù)函數(shù)（round、roundup、rounddown） 25、 求和函數(shù)（sum） 25、 單條件求和函數(shù)（sumif） 36、 多條件求和函數(shù)（sumifs） 37、 求乘積之和函數(shù)（sumproduct） 48、 平均值函數(shù)（average） 49、 單條件求平均值函數(shù)（averageif） 510、多條件求平均值函數(shù)（averageifs） 511、統(tǒng)計(jì)個(gè)數(shù)函數(shù)（count） 511、單條件統(tǒng)計(jì)個(gè)數(shù)函數(shù)（countif） 612、多條件統(tǒng)計(jì)個(gè)數(shù)函數(shù)（countifs） 61、公式相關(guān)概念(1)公式組成：(1)公式組成：=ROUND(K14*M14/10000,2)（2（2）公式的輸入、編輯、刪除輸入公式以“=”號(hào)開(kāi)始，然后函數(shù)名、接著輸入一對(duì)小括號(hào)（）編輯（修改）公式：F2、雙擊公式位置、編輯欄2、 絕對(duì)引用和相對(duì)引用（1） 分類(lèi)分為三種：相對(duì)引用，絕對(duì)引用，混合引用、女口A1相對(duì)引用、如$A$1絕對(duì)引用、如$A1、A$1混合引用 （絕對(duì)行，絕對(duì)列）（2） 操作方法：選擇，按F43、 條件函數(shù)（if）（1） 一般表現(xiàn)形式、最后在小括號(hào)里輸入?yún)?shù)表達(dá)式=IF（"判斷條件","成立","不成立"）IF函數(shù)的各種簡(jiǎn)寫(xiě)方式：完整表達(dá)簡(jiǎn)寫(xiě)=IF(表達(dá)式，0,"FALSE")等同于=IF（表達(dá)式,）=IF（表達(dá)式，成立，0）等同于=IF（表達(dá)式，成立,）=IF（表達(dá)式，0,不成立）等同于=IF（表達(dá)式，，不成立）結(jié)論：如果參數(shù)未寫(xiě)，用逗號(hào)隔開(kāi)則看做0如果第三個(gè)參數(shù)未寫(xiě)，當(dāng)反回結(jié)果時(shí)看做"FALSE"（2）嵌套形式=IF（表達(dá)式,IF（表達(dá)式，，），IF（表達(dá)式，，））IF函數(shù)是最常用的嵌套函數(shù)之一，經(jīng)常與vlookup、sum、average等函數(shù)綜合使用

4、取小數(shù)位數(shù)函數(shù)(round、roundup、rounddown)(1)round函數(shù)ROUND(number,num_digits)說(shuō)明：①如果num_digits大于0,則四舍五入到指定的小數(shù)位如果num_digits等于0,則四舍五入到最接近的整數(shù)如果num_digits小于0，則在小數(shù)點(diǎn)左側(cè)進(jìn)行四舍五入(2)roundup函數(shù)需要向上舍入的任意實(shí)數(shù)四舍五入后的數(shù)字的位數(shù)AJ\ROUNDUP(number,num_digits)AJ\說(shuō)明：①如果num_digits大于0，則向上舍入到指定的小數(shù)位如果num_digits等于0，則向上舍入到最接近的整數(shù)如果num_digits小于0，則在小數(shù)點(diǎn)左側(cè)向上進(jìn)行舍入rounddown函數(shù)ROUNDDOWN(number,num_digits)需要向下舍入| 四舍五入后的的任意實(shí)數(shù)|數(shù)字的位數(shù)說(shuō)明：①如果num_digits大于0，則向下舍入到指定的小數(shù)位如果num_digits等于0，則向下舍入到最接近的整數(shù)如果num_digits小于0，則在小數(shù)點(diǎn)左側(cè)向下進(jìn)行舍入(4)int函數(shù)INT(number)需要進(jìn)行向下舍入取整的實(shí)數(shù)5、求和函數(shù)(sum)返回某一單元格區(qū)域中所有數(shù)字之和=SUM(number1,number2,…)說(shuō)明:1、 直接鍵入到參數(shù)表中的數(shù)字、邏輯值及數(shù)字的文本表達(dá)式將被計(jì)算。2、 如果參數(shù)為數(shù)組或引用，只有其中的數(shù)字將被計(jì)算。數(shù)組或引用中的空白單元格、邏輯值、文本或錯(cuò)誤值將被忽略。3、 如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換成數(shù)字的文本，將會(huì)導(dǎo)致錯(cuò)誤。4、如果需要對(duì)單元格中文本型數(shù)字進(jìn)行求和，可以將它轉(zhuǎn)換成數(shù)值。轉(zhuǎn)換方法為：利用 VALUE函數(shù)轉(zhuǎn)換、利用--”轉(zhuǎn)換、利用*1”或“1”方式轉(zhuǎn)換5、單條件求和函數(shù)（sumif）根據(jù)指定條件對(duì)若干單元格求和。說(shuō)明:1、當(dāng)省略第三個(gè)參數(shù)時(shí)，則條件區(qū)域就是實(shí)際求和區(qū)域。2、第三參數(shù)可以簡(jiǎn)寫(xiě)，只需要寫(xiě)出區(qū)域中的第一個(gè)單元格地址即可，函數(shù)會(huì)自動(dòng)擴(kuò)展區(qū)域，直到和第一參數(shù)的高度和寬度匹配。如： =sumif（B2:B10,””2）應(yīng)用：可以對(duì)項(xiàng)目名稱(chēng)統(tǒng)一后，對(duì)應(yīng)統(tǒng)計(jì)各項(xiàng)目的工程量或投資合計(jì)。6、多條件求和函數(shù)（sumifs）該函數(shù)是office2007新增函數(shù)，使用該函數(shù)可快速求多條件單元格和。說(shuō)明:1、sumif和sumifs都是條件求和函數(shù)，有兩點(diǎn)不同：① sumifs第一個(gè)參數(shù)為求和區(qū)域，而 sumif求和區(qū)域?yàn)樽詈笠粋€(gè)參數(shù)，若最后參數(shù)省略，則為第一個(gè)參數(shù)； ②sumif函數(shù)求和區(qū)域可簡(jiǎn)寫(xiě)，而sumifs函數(shù)的求和區(qū)域必須和條件格式大小一致。2、如果參數(shù)中設(shè)置了多個(gè)條件，只有同時(shí)符合所有條件的數(shù)據(jù)才求和。7、 求乘積之和函數(shù)(sumproduct)在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。=SUMPRODUCT(array1,array2,array3,...)說(shuō)明：1、如果只有一個(gè)參數(shù)，則返回該數(shù)組參數(shù)之和，如果有多組參數(shù)，則將所有數(shù)組對(duì)應(yīng)的值相乘再求和。相當(dāng)于多條件求和。2、如果有多個(gè)數(shù)組參數(shù)，各數(shù)組的大小必須相等，如第一個(gè)參數(shù)為 A1：A10，第二個(gè)參數(shù)就不能為B1：B9。3、 在該函數(shù)中，“*”和“+”經(jīng)常使用，它們用于匯總條件的判斷方式。“*”和“+”分別表示“且”與”或者”的含義。4、 該函數(shù)不支持通配符。8、 平均值函數(shù)(average)返回參數(shù)的算術(shù)平均值。=AVERAGE(Number1,Number2…)說(shuō)明：1、計(jì)算是將該區(qū)域中的值匯總，再除以區(qū)域中的數(shù)據(jù)個(gè)數(shù)，但計(jì)算時(shí)將忽略文本和區(qū)域中的邏輯值、文本型數(shù)字。2、 參數(shù)可以是數(shù)值、邏輯值、文本、單元格引用以及數(shù)組。3、 該函數(shù)對(duì)直接輸入到參數(shù)中的邏輯值、文本型數(shù)字可以進(jìn)行計(jì)算，而對(duì)于單元格引用或數(shù)組中的邏輯值和文本型數(shù)字會(huì)忽略，除非利用 “1”之類(lèi)的方式將其轉(zhuǎn)換成值。9、單條件求平均值函數(shù)(averageif)返回符合指定條件的數(shù)據(jù)的算術(shù)平均值。=AVERAGEIF(range,criteria,average_range)說(shuō)明：1、 該函數(shù)有三個(gè)參數(shù)，第三參數(shù)是可選參數(shù)，如果忽略，則實(shí)際計(jì)算平均值區(qū)域?yàn)榈谝粋€(gè)參數(shù)。2、 該函數(shù)第三個(gè)參數(shù)和sumif函數(shù)的第三個(gè)參數(shù)一致，都可以使用簡(jiǎn)寫(xiě)，即只需要標(biāo)明該區(qū)域左上角的一個(gè)單元格，實(shí)際運(yùn)算時(shí)會(huì)自動(dòng)將該單元格擴(kuò)充至與第一個(gè)參數(shù)的高度、寬度一致的區(qū)域。3、該函數(shù)的第一個(gè)參數(shù)引用區(qū)域和第三個(gè)參數(shù)引用區(qū)域可以不在相同的起止行， 也可以不在相同的工作表。4、經(jīng)常使用比較運(yùn)算來(lái)限制求平均的條件。主要包括 >”、>=”、<”、<=”、 <>”、=”。10、多條件求平均值函數(shù)(averageifs)返回滿(mǎn)足多重條件的所有單元格的平均值(算術(shù)平均值) 。說(shuō)明：I、 與AVERAGEIF都是條件求平均值函數(shù)，但兩函數(shù)中的區(qū)域和條件參數(shù)不同。II、 統(tǒng)計(jì)個(gè)數(shù)函數(shù)(count)返回實(shí)際統(tǒng)計(jì)個(gè)數(shù)。=C0UNT(value1,value2,…)說(shuō)明:1、 Valuel,value2,...是包含或引用各種類(lèi)型數(shù)據(jù)的參數(shù)（ 1?30個(gè)），但只有數(shù)字類(lèi)型的數(shù)據(jù)才被計(jì)數(shù)。2、 函數(shù)COUNT在計(jì)數(shù)時(shí)，將把數(shù)值型的數(shù)字計(jì)算進(jìn)去；但是錯(cuò)誤值、空值、邏輯值、日期、文字則被忽11、單條件統(tǒng)計(jì)個(gè)數(shù)函數(shù)（countif）返回符合指定條件下單元格的個(gè)數(shù)。=COUNTIF(range,criteria)待統(tǒng)計(jì)的區(qū) 統(tǒng)計(jì)