常用的excel函數(shù)公式有哪些

 　　常用的excel函數(shù)公式有哪些?在職場(chǎng)辦公中，對(duì)數(shù)據(jù)的統(tǒng)計(jì)分析應(yīng)用最多的還是Excel，如果不掌握一定量的技巧，那在辦公的過(guò)程中，肯定會(huì)求助于別人，一起來(lái)了解下吧!

　　| 常用的excel函數(shù)公式有哪些

　　01、什么是函數(shù)

　　它是由Excel內(nèi)部預(yù)先定義并按照特定的順序、結(jié)構(gòu)來(lái)執(zhí)行計(jì)算、分析等數(shù)據(jù)處理任務(wù)的功能模塊。因此，函數(shù)被人們稱為“特殊公式”，與公式一樣，Excel函數(shù)的最終返回結(jié)果為值。函數(shù)只有唯一的名稱且不區(qū)分大小寫，它決定了函數(shù)的功能和用途。

　　舉個(gè)簡(jiǎn)單的例子——處理表格時(shí)，如何把名字首個(gè)字母全部變成大寫?

　　不懂函數(shù)的你，是不是一個(gè)個(gè)手動(dòng)修改?如果你知道Proper這個(gè)函數(shù)，就不會(huì)一個(gè)個(gè)修改了。輸入函數(shù)公式，3秒搞定!

　　02、什么是公式

　　公式就是由用戶自行設(shè)計(jì)并結(jié)合常量數(shù)據(jù)、單元格引用、運(yùn)算符等元素進(jìn)行數(shù)據(jù)處理和計(jì)算的算式。用戶使用公式是為了有目的地計(jì)算結(jié)果，因此Excel的公式必須(且只能)返回值。

　　公式的結(jié)構(gòu)：=(C2+D2)*5從公式結(jié)構(gòu)來(lái)看，構(gòu)成公式的元素通常包括等號(hào)、常量、引用和運(yùn)算符等元素。其中，=號(hào)是不可或缺的。但在實(shí)際應(yīng)用中，公式還可以使用數(shù)組、Excel函數(shù)或名稱(命名公式)來(lái)進(jìn)行運(yùn)算。

　　通常情況下，Excel按照從左向右的順序進(jìn)行公式運(yùn)算，當(dāng)公式中使用多個(gè)運(yùn)算符時(shí)，Excel將根據(jù)各運(yùn)算符的優(yōu)先級(jí)進(jìn)行運(yùn)算，對(duì)于同一級(jí)次的運(yùn)算符，則按自左而右的順序運(yùn)算。具體的優(yōu)先順序如下表：

　　在使用Excel公式進(jìn)行計(jì)算時(shí)，可能會(huì)因?yàn)槟撤N原因無(wú)法得到正確結(jié)果，而返回一個(gè)錯(cuò)誤值。常見的錯(cuò)誤值及其含義如下表所示。

　　當(dāng)公式的結(jié)果返回錯(cuò)誤值時(shí)，應(yīng)該及時(shí)地查找錯(cuò)誤原因，并修改公式來(lái)解決問(wèn)題。

　　03、函數(shù)與分類

　　Excel函數(shù)通常是由函數(shù)名稱、左括號(hào)、參數(shù)、半角逗號(hào)和右括號(hào)構(gòu)成。

　　函數(shù)公式結(jié)構(gòu)：=IF(A1>0,”正數(shù)”，IF(A1<0,負(fù)數(shù),””))對(duì)于函數(shù)的參數(shù)來(lái)說(shuō)，可以由數(shù)值、日期和文本等元素組成，也可以使用常量、數(shù)組、單元格引用或其他函數(shù)。

　　當(dāng)函數(shù)的參數(shù)也是函數(shù)時(shí)，Excel稱之為函數(shù)的嵌套。函數(shù)一共有11類，分別是數(shù)據(jù)庫(kù)函數(shù)、日期與時(shí)間函數(shù)、工程函數(shù)、財(cái)務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計(jì)函數(shù)、文本函數(shù)以及用戶自定義函數(shù)。

　　04、常用函數(shù)

　　本文內(nèi)容為目錄式的，介紹每個(gè)函數(shù)是做什么的、遇到某個(gè)問(wèn)題可以用哪個(gè)函數(shù)解決等，具體使用方法各位可以自行百度學(xué)習(xí)。

　　對(duì)于函數(shù)，不用死記硬背，只需要知道應(yīng)該選取什么類別的函數(shù)，以及需要哪些參數(shù)怎么用就行了!比如選取字段，用LEFT/RIGHT/MID函數(shù)......其他細(xì)節(jié)交給萬(wàn)能的百度吧!

　　下面根據(jù)不同的運(yùn)用場(chǎng)景，對(duì)這些常用的必備函數(shù)進(jìn)行分類介紹。

　　1、關(guān)聯(lián)匹配類

　　需要的數(shù)據(jù)不在同一個(gè)Excel表或同一個(gè)Excel表不同sheet中，數(shù)據(jù)太多copy起來(lái)麻煩還容易出錯(cuò)，如何整合呢?下面這些函數(shù)就是用于多表關(guān)聯(lián)或者行列比對(duì)時(shí)的場(chǎng)景，而且表格越復(fù)雜，用起來(lái)越爽!

　　01.VLOOKUP

　　功能：用于查找首列滿足條件的元素。

　　語(yǔ)法：=VLOOKUP (lookup_value,table_array, col_index_num, [range_lookup])

　　*備注：[ ]內(nèi)為可選參數(shù)，其余為必需參數(shù)，下文同理。=VLOOKUP (要查找的項(xiàng)、要查找位置、區(qū)域中包含要返回的值的列號(hào)、返回近似匹配或精確匹配 - 指示為 1/TRUE 或 0/FALSE) 。例：查詢姓名是F5單元格中的員工是什么職務(wù)。

　　02.HLOOKUP

　　功能：在表格的首行或數(shù)值數(shù)組中搜索值，然后返回表格或數(shù)組中指定行的所在列中的值。HLOOKUP中的H代表“行”。

　　語(yǔ)法：=HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup])

　　例：=HLOOKUP("車軸",A1:C4, 2, TRUE) 在首行查找車軸，并返回同列(列A)中第2行的值。

　　LOOKUP和HLOOKUP區(qū)別：當(dāng)比較值位于數(shù)據(jù)表格的首行時(shí)，如果要向下查看指定的行數(shù)，則可使用HLOOKUP。當(dāng)比較值位于所需查找的數(shù)據(jù)的左邊一列時(shí)，則可使用VLOOKUP。

　　03.INDEX

　　功能：返回表格或區(qū)域中的值或值的引用。

　　語(yǔ)法：=INDEX(array,row_num, [column_num])

　　例：= INDEX(B2:D11,3,3)位于區(qū)域A2:B3中第三行和第三列交叉處的數(shù)值。

　　04.MATCH

　　功能：用于返回指定內(nèi)容在指定區(qū)域(某行或者某列)的位置。

　　語(yǔ)法：=MATCH(lookup_value,lookup_array, [match_type])

　　例：=MATCH(41,B2:B5,0) 單元格區(qū)域B2:B5中值41的位置。

　　match_type：

　　1或省略：MATCH 查找小于或等于lookup_value的最大值。

　　0：MATCH查找完全等于lookup_value的第一個(gè)值。

　　-1：MATCH查找大于或等于lookup_value的最小值。

　　05.RANK

　　功能：求某一個(gè)數(shù)值在某一區(qū)域內(nèi)一組數(shù)值中的排名。

　　語(yǔ)法：=RANK(number,ref,[order])

　　例：=RANK(A3,A2:A6,1) A3在上表中A2:A6的排位排名方式：0是降，1是升序，默認(rèn)為0

　　06.ROW

　　功能：返回引用的行號(hào)。

　　語(yǔ)法：= ROW([reference])

　　例：= ROW() 公式所在行的行號(hào)

　　07.COLUMN

　　功能：返回單元格所在的列。

　　語(yǔ)法=COLUMN(reference)

　　例：=COLUMN (D10) 返回4，因?yàn)镈列是第四列。

　　08.OFFSET

　　功能：返回對(duì)單元格或單元格區(qū)域中指定行數(shù)和列數(shù)的區(qū)域的引用。返回的引用可以是單個(gè)單元格或單元格區(qū)域?？梢灾付ㄒ祷氐男袛?shù)和列數(shù)。

　　語(yǔ)法：=OFFSET(reference, rows, cols,[height], [width])

　　例：=OFFSET(D3,3,-2,1,1)顯示單元格 B6中的值，其中3為下方三行，-2為左方兩行，1為行高和列寬。

　　2、清洗處理類

　　數(shù)據(jù)處理之前，需要對(duì)提取的數(shù)據(jù)進(jìn)行初步清洗，如清除字符串空格，合并單元格、替換、截取字符串、查找字符串出現(xiàn)的位置等。

　　截取字符串：使用MID /LEFT/ RIGHT

　　替換單元格中內(nèi)容：SUBSTITUTE /REPLACE

　　合并單元格：使用CONCATENATE

　　清除字符串空格：使用TRIM/LTRIM/RTRIM

　　查找文本在單元格中的位置：FIND/ SEARCH

　　09.MID

　　功能：從中間截取字符串

　　語(yǔ)法：=MID(text,start_num, num_chars)

　　例：=MID(A2,1,5) 從A2內(nèi)字符串中第1個(gè)字符開始，返回5個(gè)字符。

　　根據(jù)身份證號(hào)碼提取年月。

　　10.LEFT

　　功能：從左截取字符串。

　　語(yǔ)法：=LEFT(text,[num_chars])

　　例：=LEFT(A2,4) 第一個(gè)字符串中的前四個(gè)字符。

　　11.RIGHT

　　功能：從右截取字符串。

　　語(yǔ)法：=RIGHT(text,[num_chars])

　　例：=RIGHT(A2,5)第一個(gè)字符串的最后5個(gè)字符

　　12. SUBSTITUTE

　　功能：在文本字符串中用new_text替換old_text。

　　語(yǔ)法：=SUBSTITUTE(text,old_text, new_text, [instance_num])

　　例：=SUBSTITUTE(A2, "銷售", "成本")將“銷售”替換為“成本”(成本數(shù)據(jù))替換部分電話號(hào)碼。

　　13.REPLACE

　　功能：替換掉單元格的字符串。

　　語(yǔ)法：=REPLACE(old_text,start_num, num_chars, new_text)

　　例：=REPLACE(A2,6,5,"*") 在A2中，從第六個(gè)字符(f)開始使用單個(gè)字符*替換五個(gè)字符。

　　REPLACE和SUBSTITUTE區(qū)別：兩個(gè)函數(shù)很接近，不同在于REPLACE根據(jù)位置實(shí)現(xiàn)替換，需要提供從第幾位開始替換，替換幾位，替換后的新的文本;而SUBSTITUTE根據(jù)文本內(nèi)容替換，需要提供替換的舊文本和新文本，以及替換第幾個(gè)舊文本等。因此REPLACE實(shí)現(xiàn)固定位置的文本替換，SUBSTITUTE實(shí)現(xiàn)固定文本替換。

　　14.CONCATENATE

　　功能：將兩個(gè)或多個(gè)文本字符串聯(lián)接為一個(gè)字符串。

　　語(yǔ)法：=CONCATENATE(text1,[text2], ...)

　　合并單元格中的內(nèi)容，還有另一種合并方式是&，需要合并的內(nèi)容過(guò)多時(shí)，CONCATENATE 效率更快。

　　例：=CONCATENATE(B2, " ", C2) 聯(lián)接三部分內(nèi)容：?jiǎn)卧馚2中的字符串、空格字符以及單元格C2中的值。

　　15.TRIM

　　功能：除了單詞之間的單個(gè)空格之外，移除文本中的所有空格。

　　語(yǔ)法：=TRIM(text)

　　Text為要去掉空格的文本。

　　例：=TRIM("First Quarter Earnings ") 從公式的文本中移除前導(dǎo)空格和尾隨空格。

　　16.LTRIM

　　功能：從字符串左側(cè)刪除空格或其他預(yù)定義字符。

　　語(yǔ)法：=LTRIM (string, [charlist])

　　17.RTRIM

　　功能：從字符串右側(cè)刪除空格或其他預(yù)定義字符。

　　語(yǔ)法：= LTRIM(string, [charlist])

　　18.FIND

　　功能：查找文本位置

　　語(yǔ)法：=FIND(find_text,within_text, [start_num])

　　例：=FIND("M",A2) 單元格A2中第一個(gè)“M”的位置

　　19.SEARCH

　　功能：返回一個(gè)指定字符或文本字符串在字符串中第一次出現(xiàn)的位置,從左到右查找。

　　語(yǔ)法：=SEARCH(find_text,within_text,[start_num])

　　例：=SEARCH("e",A2,6) 單元格A2中的字符串中，從第6個(gè)位置起，第一個(gè)“e”的位置。

　　FIND和SEARCH區(qū)別：這兩個(gè)函數(shù)功能幾乎相同，實(shí)現(xiàn)查找字符所在的位置，區(qū)別在于FIND函數(shù)精確查找，區(qū)分大小寫;SEARCH函數(shù)模糊查找，不區(qū)分大小寫。

　　20.LEN

　　功能：返回文本字符串中的字符個(gè)數(shù)。

　　語(yǔ)法：=LEN(text)

　　例：=LEN(A1) A1單元格字符串的長(zhǎng)度

　　21. LENB

　　功能：返回文本字符串中用于代表字符的字節(jié)數(shù)。

　　語(yǔ)法：=LENB(text)

　　例：=LEN(A1)A1單元格字符串的字節(jié)數(shù)。

　　3、邏輯運(yùn)算類

　　邏輯，顧名思義，不贅述，直接上函數(shù)。

　　22.IF

　　功能：使用邏輯函數(shù)IF 函數(shù)時(shí)，如果條件為真，該函數(shù)將返回一個(gè)值;如果條件為假，函數(shù)將返回另一個(gè)值。

　　語(yǔ)法：=IF(Logical,Value_if_true,Value_if_false)

　　如果指定條件的計(jì)算結(jié)果為true，IF函數(shù)將返回某個(gè)值;如果該條件的計(jì)算結(jié)果為false，則返回另一個(gè)值。

　　23.COUNTIF

　　功能：用于統(tǒng)計(jì)滿足某個(gè)條件的單元格的數(shù)量;例如，統(tǒng)計(jì)特定城市在客戶列表中出現(xiàn)的次數(shù)。

　　語(yǔ)法：=COUNTIF(單元格1: 單元格2 ,條件)

　　統(tǒng)計(jì)特定店鋪在列表中出現(xiàn)的次數(shù)。

　　24.AND

　　功能：邏輯判斷，相當(dāng)于“并”。

　　語(yǔ)法：全部參數(shù)為True，則返回True，經(jīng)常用于多條件判斷。

　　例：=AND(A2>1,A2<100) 如果A2大于1并且小于100，則顯示TRUE;否則顯示FALSE。

　　25.OR

　　功能：邏輯判斷，相當(dāng)于“或”。

　　語(yǔ)法：只要參數(shù)有一個(gè)True，則返回Ture，經(jīng)常用于多條件判斷。

　　例：=OR(A2>1,A2<100) 如果A2大于1或者小于100，則顯示TRUE;否則顯示FALSE。

　　4、計(jì)算統(tǒng)計(jì)類

　　在利用Excel表格統(tǒng)計(jì)數(shù)據(jù)時(shí)，常常需要使用各種Excel自帶的公式，也是最常使用的一類。(對(duì)于這些，Excel自帶快捷功能)

　　26.MIN

　　功能：找到某區(qū)域中的最小值。

　　語(yǔ)法：=MIN(number1, [number2], ...)

　　例：=MIN(D2:D11) 區(qū)域D2:D11中的最小數(shù)。

　　27.MAX

　　功能：找到某區(qū)域中的最大值。

　　語(yǔ)法：=MAX(number1, [number2], ...)

　　例：=MAX(A2:A6) 區(qū)域A2:A6中的最大值。

　　28.AVERAGE

　　功能：計(jì)算某區(qū)域中的平均值。

　　語(yǔ)法：=AVERAGE(number1, [number2], ...)

　　例：=AVERAGE(D2:D11) 單元格區(qū)域D2到D11中數(shù)字的平均值。

　　29.COUNT

　　功能：計(jì)算含有數(shù)字的單元格的個(gè)數(shù)。

　　語(yǔ)法：=COUNT(value1, [value2], ...)

　　例：=COUNT(A2:A7) 計(jì)算單元格區(qū)域A2到A7中包含數(shù)字的單元格的個(gè)數(shù)。

　　30.COUNTIFS

　　功能：統(tǒng)計(jì)一組給定條件所指定的單元格數(shù)。

　　語(yǔ)法：COUNTIFS(criteria_range1,criteria1, [criteria_range2, criteria2],…)

　　例：=COUNTIFS(A2:A7,"<6",A2:A7,">1")計(jì)算1和6之間(不包括1和6)有幾個(gè)數(shù)包含在單元格A2到A7中。

　　31.SUM

　　功能：計(jì)算單元格區(qū)域中所有數(shù)值的和。

　　語(yǔ)法：=SUM(單元格1:單元格2)

　　例：=SUM(A2:A10) 將單元格A2:10中的值加在一起。

　　32.SUMIF

　　功能：求滿足條件的單元格和。

　　語(yǔ)法：=SUMIF(range,criteria, [sum_range])

　　例：=SUMIF(A2:A7,"水果",C2:C7) “水果”類別下所有食物的銷售額之和。

　　32.SUMIFS

　　功能：對(duì)一組滿足條件指定的單元格求和。

　　語(yǔ)法：=SUMIFS(sum_range,criteria_range1, criteria1, [criteria_range2, criteria2], ...)

　　例：=SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "盧寧") 計(jì)算以“香”開頭并由“盧寧”售出的產(chǎn)品的總量。

　　33.SUMPRODUCT

　　功能：返回相應(yīng)的數(shù)組或區(qū)域乘積的和。

　　語(yǔ)法：=SUMPRODUCT (array1， [array2]， [array3]， ...)

　　例：=SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) 計(jì)算表格1的A1到A100與表格2的B1到B100的乘積和，即A1*B1+A2*B2+A3*B3+…

　　34.STDEV

　　功能：基于樣本估算標(biāo)準(zhǔn)偏差。

　　語(yǔ)法：STDEV(number1,[number2],...)

　　例：=STDEV(D2:D17) 列的標(biāo)準(zhǔn)偏差

　　35.SUBSTOTAL

　　功能：返回列表或數(shù)據(jù)庫(kù)中的分類匯總。

　　語(yǔ)法：=SUBTOTAL(function_num,ref1,[ref2],...)

　　例：=SUBTOTAL(9,A2:A5)使用9作為第一個(gè)參數(shù),算出的單元格A2:A5中分類匯總的值之和。

　　http://36.INT/ROUND

　　功能：ROUND 函數(shù)將數(shù)字四舍五入到指定的位數(shù)。

　　語(yǔ)法：=ROUND(A1, 2)

　　例：=ROUND(2.15, 1)將2.15四舍五入到一個(gè)小數(shù)位

　　功能：INT將數(shù)字向下舍入到最接近的整數(shù)。

　　語(yǔ)法:=INT(8.9) 將 8.9 向下舍入到最接近的整數(shù)。

　　5、時(shí)間序列類

　　專門用于處理時(shí)間格式以及轉(zhuǎn)換。

　　37.TODAY

　　功能：返回當(dāng)前日期的序列號(hào)。

　　語(yǔ)法：=TODAY()

　　li'z=TODAY()+5返回當(dāng)前日期加5天。例如，如果當(dāng)前日期為1/1/2012，此公式會(huì)返回1/6/2012。

　　38.NOW

　　功能：返回當(dāng)前日期和時(shí)間的序列號(hào)。

　　語(yǔ)法：=Now()

　　=NOW()+7 返回7天后的日期和時(shí)間。

　　39.YEAR

　　功能：返回對(duì)應(yīng)于某個(gè)日期的年份。

　　語(yǔ)法：=YEAR(serial_number)

　　=YEAR(A3) 單元格A3中日期的年份

　　40.MONTH

　　功能：返回日期中的月份。

　　語(yǔ)法：=MONTH(serial_number)

　　=MONTH(A2) 單元格A2中日期的月份

　　41.DAY

　　功能：返回以序列數(shù)表示的某日期的天數(shù)。

　　語(yǔ)法：=DAY(serial_number)

　　=DAY(A2) 單元格A2中日期的天數(shù)

　　42.WEEKDAY

　　功能：返回對(duì)應(yīng)于某個(gè)日期的一周中的第幾天。默認(rèn)情況下，天數(shù)是1(星期日)到7(星期六)范圍內(nèi)的整數(shù)。

　　語(yǔ)法：=WEEKDAY(serial_number,[return_type])

　　=WEEKDAY(A2) 1(星期日)到7(星期六)一周中的第幾天

　　=WEEKDAY(A2, 2) 1(星期一)到7(星期日)一周中的第幾天。

　　43.DATEDIF

　　功能：計(jì)算兩個(gè)日期之間相隔的天數(shù)、月數(shù)或年數(shù)。

　　語(yǔ)法：=DATEDIF(start_date,end_date,unit)

　　=DATEDIF(Start_date,End_date,"Y")一段時(shí)期內(nèi)的年數(shù)

　　=DATEDIF(Start_date,End_date,"D")一段時(shí)期內(nèi)的天數(shù)

　　=DATEDIF(Start_date,End_date,"YD")忽略日期中的年份，一段時(shí)期內(nèi)的天數(shù)

　　附：

　　以上就是“常用的excel函數(shù)公式有哪些”的全部?jī)?nèi)容，希望能幫助到大家!

?