excel函數(shù)說明必看

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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