excel函數(shù)說明必看

1、數(shù)據(jù)庫和列表管理函數(shù) DAVERAGE   返回選定數(shù)據(jù)庫項(xiàng)的平均值DCOUNT   計(jì)算數(shù)據(jù)庫中包含數(shù)字的單元格個(gè)數(shù)DCOUNTA   計(jì)算數(shù)據(jù)庫中非空單元格的個(gè)數(shù)DGET   從數(shù)據(jù)庫中提取滿足指定條件的單個(gè)記錄DPRODUCT   將數(shù)據(jù)庫中滿足條件的記錄的特定字段中的數(shù)值相乘DSUM   對(duì)數(shù)據(jù)庫中滿足條件的記錄的字段列中的數(shù)字求和GETPIVOTDATA   返回存儲(chǔ)于數(shù)據(jù)透視

2、表中的數(shù)據(jù) 日期和時(shí)間函數(shù) DATE   返回特定日期的序列號(hào)DATEVALUE   將文本格式的日期轉(zhuǎn)換為序列號(hào)DAY   將序列號(hào)轉(zhuǎn)換為月份中的日EDATE   返回在開始日期之前或之后指定月數(shù)的日期的序列號(hào)MONTH   將序列號(hào)轉(zhuǎn)換為月NOW   返回當(dāng)前日期和時(shí)間的序列號(hào)TIMEVALUE   將文本格式的時(shí)間轉(zhuǎn)換為序列號(hào)TODAY   返回今天日

3、期的序列號(hào)WEEKDAY   將序列號(hào)轉(zhuǎn)換為星期幾WEEKNUM   將序列號(hào)轉(zhuǎn)換為一年中相應(yīng)的周數(shù)邏輯函數(shù) IF   指定要執(zhí)行的邏輯檢測(cè)數(shù)學(xué)和三角函數(shù) CEILING   將數(shù)字舍入為最接近的整數(shù)，或最接近的有效數(shù)字的倍數(shù)ROUND   將數(shù)字舍入到指定位數(shù)SUBTOTAL   返回?cái)?shù)據(jù)庫列表或數(shù)據(jù)庫中的分類匯總SUMIF   按給定條件將指定單元格求和SUMPRODUCT 

4、0; 返回相對(duì)應(yīng)的數(shù)組部分的乘積和TRUNC   將數(shù)字截尾取整 信息函數(shù) CELL   返回有關(guān)單元格格式、位置或內(nèi)容的信息ISBLANK   如果值為空，則返回 TRUEISNA   如果值為 #N/A 錯(cuò)誤值，則返回 TRUE查找和引用函數(shù) ADDRESS   以文本形式返回對(duì)工作表中某個(gè)單元格的引用CHOOSE   從值的列表中選擇一個(gè)值HLOOKUP   在數(shù)組的首行

5、查找并返回指定單元格的值LOOKUP   在向量或數(shù)組中查找值TRANSPOSE   返回?cái)?shù)組的轉(zhuǎn)置VLOOKUP   在數(shù)組第一列中查找，然后在行之間移動(dòng)以返回單元格的值 統(tǒng)計(jì)函數(shù) CORREL   返回兩個(gè)數(shù)據(jù)集之間的相關(guān)系數(shù)COUNT   計(jì)算參數(shù)列表中數(shù)字的個(gè)數(shù)COUNTA   計(jì)算參數(shù)列表中值的個(gè)數(shù)COUNTBLANK 計(jì)算區(qū)間內(nèi)的空白單元格個(gè)數(shù)COUNTIF 計(jì)算滿足給定標(biāo)準(zhǔn)的區(qū)間內(nèi)的非空單元格的個(gè)數(shù)MO

6、DE   返回?cái)?shù)據(jù)集中出現(xiàn)最多的值PERCENTRANK   返回?cái)?shù)據(jù)集中值的百分比排位文本和數(shù)據(jù)函數(shù) ASC   將字符串內(nèi)的全角（雙字節(jié)）英文字母或片假名更改為半角（單字節(jié)）字符CONCATENATE   將若干文本項(xiàng)合并到一個(gè)文本項(xiàng)中EXACT   檢查兩個(gè)文本值是否完全相同F(xiàn)IND   在一文本值內(nèi)查找另一文本值（區(qū)分大小寫）FIXED   將數(shù)字設(shè)置為具有固定小數(shù)位的文本格式L

7、EFT   返回文本值最左邊的字符LEN   返回文本字符串中的字符個(gè)數(shù)LOWER   將文本轉(zhuǎn)換為小寫形式MID   從文本字符串中的指定位置起返回特定個(gè)數(shù)的字符REPLACE   替換文本內(nèi)的字符RIGHT   返回文本值最右邊的字符SEARCH   在一文本值中查找另一文本值（不區(qū)分大小寫）SUBSTITUTE   在文本字符串中以新文本替換舊文本TE

8、XT   設(shè)置數(shù)字的格式并將數(shù)字轉(zhuǎn)換為文本TRIM   刪除文本中的空格UPPER   將文本轉(zhuǎn)換為大寫形式 A 1 數(shù)據(jù)2 Ceramic Insulators #124-TD45-873 Copper Coils #12-671-67724 Variable Resistors #116010公式 說明（結(jié)果）=MID(A2,1,FIND(" #",A2,1)-1) 在上面第一個(gè)字符串中，抽取從第一個(gè)字符到“#”的文本 (Ceramic Insulators)=MID(A3,1,F

9、IND(" #",A3,1)-1) 在上面第二個(gè)字符串中，抽取從第一個(gè)字符到“#”的文本 (Copper Coils)=MID(A4,1,FIND(" #",A4,1)-1) 在上面第三個(gè)字符串中，抽取從第一個(gè)字符到“#”的文本 (Variable Resistors) A1 數(shù)據(jù)2 Statements3 Profit Margin4 margin公式說明（結(jié)果）=SEARCH("e",A2,6)上面第一個(gè)字符串中的第一個(gè) e 出現(xiàn)的位置，開始于第六個(gè)位置 (7)=SEARCH(A4,A3)margin 在 Profit Margi

10、n 中的位置 (8)=REPLACE(A3,SEARCH(A4,A3),6,"Amount")用 Amount 替換 Margin常用函數(shù)及技巧詳解 1.求平均值.函數(shù)名稱：AVERAGE主要功能：求出所有參數(shù)的算術(shù)平均值。使用格式：AVERAGE(number1,number2,)參數(shù)說明：number1,number2,：需要求平均值的數(shù)值或引用單元格（區(qū)域），參數(shù)不能超過30個(gè)。應(yīng)用舉例：如在B8單元格中輸入公式：=AVERAGE(B7:D7)，即可求出B7至D7區(qū)域值的平均值.也可求出指定區(qū)域的平均值，如：在B8單元格中輸入公式：=AVERAGE(B7:D7,F7:

11、H7,7,8)，確認(rèn)后，即可求出B7至D7區(qū)域、F7至H7區(qū)域中的數(shù)值和7、8的平均值。 特別提醒：如果引用區(qū)域中包含“0”值單元格，則計(jì)算在內(nèi)；如果引用區(qū)域中包含空白或字符單元格，則不計(jì)算在內(nèi)。2.求一組數(shù)據(jù)的和函數(shù)名稱：SUM主要功能：計(jì)算所有參數(shù)數(shù)值的和。使用格式：SUM（Number1,Number2）參數(shù)說明：Number1、Number2代表需要計(jì)算的值，可以是具體的數(shù)值、引用的單元格（區(qū)域）、邏輯值等。應(yīng)用舉例：如在D64單元格中輸入公式：=SUM(D2:D63)，確認(rèn)后即可求出D列中2-63格中的和。特別提醒：如果參數(shù)為數(shù)組或引用，只有其中的數(shù)字將被計(jì)算。數(shù)組或引用中的空白單元

12、格、邏輯值、文本或錯(cuò)誤值將被忽略；如果將上述公式修改為：=SUM(LARGE(D2:D63,1,2,3,4,5)，則可以求出前5名成績(jī)的和。3、求出部分指定數(shù)據(jù)的和函數(shù)名稱：SUMIF主要功能：計(jì)算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。使用格式：SUMIF（Range,Criteria,Sum_Range）參數(shù)說明：Range代表?xiàng)l件判斷的單元格區(qū)域；Criteria為指定條件表達(dá)式；Sum_Range代表需要計(jì)算的數(shù)值所在的單元格區(qū)域。應(yīng)用舉例：如，在D64單元格中輸入公式：=SUMIF(B3:B500,"七1",E3:E500) .即：可求出在B3B500中（為班名）E3

13、E500(某科目)的和，另外輸入： =SUMIF(B3:B500,"七1",E3:E500)/R17，（R17代表人數(shù)欄），確認(rèn)后即可求出“七1”班的某科成績(jī)的平均值（班平均分）。補(bǔ)充：如果把上述公式修改為：=SUMIF(C2:C63,"女",D2:D63)，即可求出“女”生的語文成績(jī)和。特別提醒：其中的“男”和“女”由于是文本型的，需要放在英文狀態(tài)下的雙引號(hào)（"男"、"女"）中。4. 對(duì)一組數(shù)據(jù)進(jìn)行自動(dòng)排名函數(shù)名稱：RANK主要功能：返回某一數(shù)值在一列數(shù)值中的相對(duì)于其他數(shù)值的排位。使用格式：RANK（Number

14、,ref,order）參數(shù)說明：Number代表需要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域；order代表排序方式參數(shù)（如果為“0”或者忽略，則按降序排名，即數(shù)值越大，排名結(jié)果數(shù)值越??；如果為非“0”值，則按升序排名，即數(shù)值越大，排名結(jié)果數(shù)值越大；）。應(yīng)用舉例：如在C2單元格中輸入公式：=RANK(B2,$B$2:$B$31,0)，確認(rèn)后即可得出某位同學(xué)的成績(jī)?cè)贐2-B31中的排名結(jié)果。特別提醒：在上述公式中，我們讓Number參數(shù)采取了相對(duì)引用形式，而讓ref參數(shù)采取了絕對(duì)引用形式（增加了一個(gè)絕對(duì)引用“$”符號(hào)），這樣設(shè)置后，選中C2單元格，將鼠標(biāo)移至該單元格右下角，成細(xì)十字線狀時(shí)（

15、通常稱之為“填充柄”），按住左鍵向下拖拉，即可將上述公式快速?gòu)?fù)制到C列下面的單元格中，完成其他同學(xué)的排名統(tǒng)計(jì)。 5. 求出一組數(shù)中的最大值和最小值函數(shù)名稱：MAX （求最大值）主要功能：求出一組數(shù)中的最大值。使用格式：MAX(number1,number2)參數(shù)說明：number1,number2代表需要求最大值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個(gè)。應(yīng)用舉例：輸入公式：=MAX(E4:E100)，確認(rèn)后即可顯示出E4至E100單元中的最大值。特別提醒：如如果參數(shù)中有文本或邏輯值，則忽略MIN函數(shù) （求最小值）函數(shù)名稱：MIN主要功能：求出一組數(shù)中的最小值。使用格式：MIN(number

16、1,number2)參數(shù)說明：number1,number2代表需要求最小值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個(gè)。應(yīng)用舉例：輸入公式：=MAX(E4:E100)，確認(rèn)后即可顯示出E4至E100單元中的最小值特別提醒：如果參數(shù)中有文本或邏輯值，則忽略 6、根據(jù)給定的條件，返回相對(duì)應(yīng)的指定內(nèi)容函數(shù)名稱：IF主要功能：根據(jù)對(duì)指定條件的邏輯判斷的真假結(jié)果，返回相對(duì)應(yīng)的內(nèi)容。使用格式：=IF(Logical,Value_if_true,Value_if_false)參數(shù)說明：Logical代表邏輯判斷表達(dá)式；Value_if_true表示當(dāng)判斷條件為邏輯“真（TRUE）”時(shí)的顯示內(nèi)容，如果忽略返

17、回“TRUE”；Value_if_false表示當(dāng)判斷條件為邏輯“假（FALSE）”時(shí)的顯示內(nèi)容，如果忽略返回“FALSE”。應(yīng)用舉例：在D26單元格中輸入公式：=IF(C26>=60,"及格","不及格")，確認(rèn)以后，如果C26單元格中的數(shù)值大于或等于60，則D26單元格顯示"及格"字樣，反之顯示“不及格”字樣。也可根據(jù)實(shí)際可多設(shè)幾檔條件和內(nèi)容，如：=IF（A3<60,"不合格",(IF(A3<80,"合格",(IF(A3<100,"優(yōu)秀" ）.即根

18、據(jù)A3中的分?jǐn)?shù)進(jìn)行自動(dòng)歸類.然后拖動(dòng)復(fù)制其它進(jìn)行歸類.特別提醒：1.公式中是文字的引號(hào)要用英文狀態(tài)輸入. 2. 括號(hào)要對(duì)稱（正反一樣多） 7、在表中統(tǒng)計(jì)符合指定條件的人數(shù)（單元格數(shù)目）函數(shù)名稱：COUNTIF主要功能：統(tǒng)計(jì)某個(gè)單元格區(qū)域中符合指定條件的單元格數(shù)目。使用格式：COUNTIF(Range,Criteria)參數(shù)說明：Range代表要統(tǒng)計(jì)的單元格區(qū)域；Criteria表示指定的條件表達(dá)式。應(yīng)用舉例：在C17單元格中輸入公式：=COUNTIF(B1:B13,">=80")，確認(rèn)后，即可統(tǒng)計(jì)出B1至B13單元格區(qū)域中，數(shù)值大于等于80的單元格數(shù)目。特別提醒：允許

19、引用的單元格區(qū)域中有空白單元格出現(xiàn) 8、巧用函數(shù)組合進(jìn)行多條件的計(jì)數(shù)統(tǒng)計(jì)（原創(chuàng)）舉例說明：第一行為表頭，A列是“姓名”，B列是“班級(jí)”，C列是“語文成績(jī)”，現(xiàn)在要統(tǒng)計(jì)“班級(jí)”為“2”，“語文成績(jī)”大于等于100的人數(shù)。統(tǒng)計(jì)結(jié)果存放在本工作表的其他列。公式如下：=SUM(IF(B2:B500="2")*(C2:C500>=100),1,0)輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動(dòng)加上數(shù)組公式符號(hào)" "。另：如果只求某一分?jǐn)?shù)段的人數(shù)，假如B列是學(xué)生的語文成績(jī)，則求從B2:B56這一范圍內(nèi)分?jǐn)?shù)在80-90之間的人數(shù)，則可以用這樣一個(gè)公式

20、:=COUNTIF (B2:B56,">=80")-COUNTIF (B2:B56,">=90") 輸入公式回車即可。 注意：函數(shù)COUNTIF是條件統(tǒng)計(jì)函數(shù)，只能對(duì)一個(gè)條件進(jìn)行限制，如果判斷條件有兩個(gè)以上，則不能用。另外，公式中的各種符號(hào)都是在英文狀態(tài)下輸入的。 還有一個(gè)辦法：求出一個(gè)數(shù)目，然后采用遞減的辦法，=COUNTIF (B2:B56,">=80")-A2 - .)9、核對(duì)兩列數(shù)據(jù)或文本是否一樣舉例說明：比較A列和B列是否相同方法一：在c列輸入：=IF(a1=b1,"一樣","

21、不一樣")即：相同的顯示“一樣”，不相同的顯示“不一樣”.方法二：（把B列與A列不同之處標(biāo)識(shí)出來）（1）、如果是要求A、B兩列的同一行數(shù)據(jù)相比較：假定第一行為表頭，單擊A2單元格，點(diǎn)“格式”->“條件格式”，將條件設(shè)為:“單元格數(shù)值” “不等于” =B2點(diǎn)“格式”->“字體”->“顏色”，選中紅色，點(diǎn)兩次“確定”。用格式刷將A2單元格的條件格式向下復(fù)制。B列可參照此方法設(shè)置。不同的自動(dòng)顯示為紅色.（2）、如果是A列與B列整體比較（即相同數(shù)據(jù)不在同一行）：假定第一行為表頭，單擊A2單元格，點(diǎn)“格式”->“條件格式”，將條件設(shè)為:“公式”=COUNTIF(B:B,

22、A2)=0點(diǎn)“格式”->“字體”->“顏色”，選中紅色，點(diǎn)兩次“確定”。用格式刷將A2單元格的條件格式向下復(fù)制。B列可參照此方法設(shè)置。注：實(shí)際用時(shí)要將被用來比較的復(fù)制到相鄰兩列來，整體設(shè)條件，按第一格設(shè).按以上方法設(shè)置后，AB列均有的數(shù)據(jù)不著色，A列有B列無或者B列有A列無的數(shù)據(jù)標(biāo)記為紅色字體。 提示：如果AB兩列整體區(qū)別時(shí)，先要用鼠標(biāo)點(diǎn)擊成為整體選中再設(shè)置. 10、跨表調(diào)取數(shù)據(jù)方法 (原創(chuàng))用VLOOKUP函數(shù)主要功能：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組中該數(shù)值所在行中指定列處的數(shù)值。這里所說的“數(shù)組”，可以理解為表格中的一個(gè)區(qū)域。數(shù)組的列序號(hào)：數(shù)組的“首

23、列”，就是這個(gè)區(qū)域的第一縱列，此列右邊依次為第2列、3列。假定某數(shù)組區(qū)域?yàn)锽2:E10，那么，B2:B10為第1列、C2:C10為第2列。語法： VLOOKUP（查找值，區(qū)域，列序號(hào)，邏輯值）“查找值”：為需要在數(shù)組第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字符串。“區(qū)域”：數(shù)組所在的區(qū)域，如“B2:E10”，也可以使用對(duì)區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫或數(shù)據(jù)清單?！傲行蛱?hào)”：即希望區(qū)域（數(shù)組）中待返回的匹配值的列序號(hào)，為1時(shí)，返回第一列中的數(shù)值，為2時(shí)，返回第二列中的數(shù)值，以此類推；若列序號(hào)小于1，函數(shù)VLOOKUP 返回錯(cuò)誤值 #VALUE!；如果大于區(qū)域的列數(shù)，函數(shù)VLOOKUP返回錯(cuò)誤

24、值 #REF!?！斑壿嬛怠保簽門RUE或FALSE。它指明函數(shù) VLOOKUP 返回時(shí)是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于“查找值”的最大數(shù)值；如果“邏輯值”為FALSE，函數(shù) VLOOKUP 將返回精確匹配值。如果找不到，則返回錯(cuò)誤值 #N/A。如果“查找值”為文本時(shí)，“邏輯值”一般應(yīng)為 FALSE 。另外：·如果“查找值”小于“區(qū)域”第一列中的最小數(shù)值，函數(shù) VLOOKUP 返回錯(cuò)誤值 #N/A。·如果函數(shù) VLOOKUP 找不到“查找值” 且“邏輯值”為 FALSE，函數(shù) VLOOKUP 返回錯(cuò)

25、誤值 #N/A。下面用示意圖來說明VLOOKUP函數(shù)的使用方法。如：“=VLOOKUP(A10,模擬1!$C$3:$K$500,3,0)”“A10”表示要查找的對(duì)象（在表2中）.“模擬1!”表示被引用表的名稱 “$C$3:$K$500”表示被引用表的要引用的區(qū)域，“C3”表示要引用的第一格，“K500”表示要引用的最后一格.“$”表示絕對(duì)引用，是鎖定的意思，沒有為不鎖定，復(fù)制時(shí)要變化?！?”表示要引用區(qū)域的第3列數(shù)據(jù)，(如例中的從C開始為1).“0”表示準(zhǔn)確查找，如果是“1”表示近似查找. 11.自動(dòng)給出當(dāng)時(shí)日期函數(shù)名稱：TODAY主要功能：給出系統(tǒng)日期。使用格式：TODAY()參數(shù)說明：該函

26、數(shù)不需要參數(shù)。應(yīng)用舉例：輸入公式：=TODAY()，確認(rèn)后即刻顯示出系統(tǒng)日期和時(shí)間。如果系統(tǒng)日期和時(shí)間發(fā)生了改變，只要按一下F9功能鍵，即可讓其隨之改變。特別提醒：顯示出來的日期格式，可以通過單元格格式進(jìn)行重新設(shè)置12.其它一些常用的但又特殊的方法（一）如何在EXCEL中設(shè)置單元格編輯權(quán)限(保護(hù)部分單元格)1、先選定所有單元格,點(diǎn)"格式"->"單元格"->"保護(hù)",取消"鎖定"前面的""。2、再選定你要保護(hù)的單元格,點(diǎn)"格式"->"單元格"

27、;->"保護(hù)",在"鎖定"前面打上""。3、點(diǎn)"工具"->"保護(hù)"->"保護(hù)工作表",輸入兩次密碼,點(diǎn)兩次"確定"即可。 （二）在EXCEL中建立下拉列表按鈕選定你要設(shè)置下拉列表的單元格，點(diǎn)“數(shù)據(jù)”->“有效性”->“設(shè)置”，在“允許”下面選擇“序列”，在“來源”框中輸入你的下拉列表內(nèi)容，各項(xiàng)之間用半角逗號(hào)隔開，如：A,B,C,D選中“提供下拉前頭”，點(diǎn)“確定”。 （三）自動(dòng)從學(xué)生的身份證號(hào)“提取”出生日期和性別。第一步：轉(zhuǎn)換

28、身份證號(hào)碼格式選中該列中的所有身份證號(hào)后，右擊鼠標(biāo)，選擇“設(shè)置單元格格式”。在彈出對(duì)話框中“數(shù)字”標(biāo)簽內(nèi)的“分類”設(shè)為“文本”，然后點(diǎn)擊確定。第二步：“提取出”出生日期 將光標(biāo)指針放到“出生日期”列的單元格內(nèi)，這里以C2單元格為例。然后輸入“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”（注意：外側(cè)的雙引號(hào)不用輸入，函數(shù)式中的引號(hào)和逗號(hào)等符號(hào)應(yīng)在英文狀態(tài)下輸入）?；剀嚭螅銜?huì)發(fā)現(xiàn)在C2單元格內(nèi)已經(jīng)出現(xiàn)了該學(xué)生的出生日期。然后，選中該單元格后

29、拖動(dòng)填充柄，其它單元格內(nèi)就會(huì)出現(xiàn)相應(yīng)的出生日期。還可以提取出“性別”：判斷性別“男女” 選中“性別”列的單元格，如D2。輸入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")”（注意如上）后回車，該生“是男還是女”已經(jīng)乖乖地判斷出來了。拖動(dòng)填充柄讓其他學(xué)生的性別也自動(dòng)輸入 （四）解釋：?jiǎn)卧竦南鄬?duì)引用、絕對(duì)引用和混合引用相對(duì)引用、絕對(duì)引用和混合引用是指在公式中使用單元格或單元格區(qū)域的地址時(shí)，當(dāng)將公式向旁邊復(fù)制時(shí)，地址是如何變化的。具體情況舉例說明：1、相對(duì)引用，復(fù)制公式時(shí)地址跟著發(fā)生變化，如C1單元格有公式：=A1+B1當(dāng)將公式復(fù)制到C2單元格時(shí)變?yōu)椋?A2+B2當(dāng)將公式復(fù)制到D1單元格時(shí)變?yōu)椋?B1+C1 2、絕對(duì)引用，復(fù)制公式時(shí)地址不會(huì)跟著發(fā)生變化，如C1單元格有公式：=$A$1+$B$1當(dāng)將公式復(fù)制到C2單元格時(shí)仍為：=$A$1+$B$1當(dāng)將公式復(fù)制到D1單元格時(shí)仍為：=$A$1+$B$1 3、混合引用，復(fù)制公式時(shí)地址的部分內(nèi)容跟著發(fā)生變化，如C1單元格有公式：=$A1+B$1當(dāng)將公式復(fù)制到C2單元格時(shí)變?yōu)椋?$A2+B$1當(dāng)將公式復(fù)制到D1單元格時(shí)變?yōu)椋?$A1+C$1 規(guī)