EXCEL實(shí)用技巧---EXCEL常用函數(shù)簡(jiǎn)介

1、EXCELEXCEL實(shí)用技巧實(shí)用技巧 EXCELEXCEL常用函數(shù)簡(jiǎn)介常用函數(shù)簡(jiǎn)介MISMISZhang JunZhang Jun主要內(nèi)容主要內(nèi)容 SUMSUM 函數(shù)函數(shù)1 1 AVERAGE/AVERAGEAVERAGE/AVERAGEA/A/TRIMMEANTRIMMEAN函數(shù)函數(shù)2 2COUNT/COUNTACOUNT/COUNTA 計(jì)數(shù)函數(shù)計(jì)數(shù)函數(shù)3 34 45 5 函數(shù)函數(shù)基礎(chǔ)知識(shí)基礎(chǔ)知識(shí)ROUND/TRUNCROUND/TRUNC 函數(shù)函數(shù)主要內(nèi)容主要內(nèi)容IFIF函數(shù)（函數(shù)（COUNTIF/SUMIFCOUNTIF/SUMIF）789公式中常見(jiàn)的錯(cuò)誤信息公式中常見(jiàn)的錯(cuò)誤信息10FR

2、EQUNCY FREQUNCY 陣列陣列函數(shù)函數(shù)MODMOD函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用6日期函數(shù)日期函數(shù) ExcelExcel函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。數(shù)據(jù)任務(wù)的特殊公式。函數(shù)以等號(hào)函數(shù)以等號(hào) (=) (=) 開(kāi)始，后面緊跟函開(kāi)始，后面緊跟函數(shù)名稱和左括號(hào)，然后以逗號(hào)分隔輸入?yún)?shù)，最后是右括數(shù)名稱和左括號(hào)，然后以逗號(hào)分隔輸入?yún)?shù)，最后是右括號(hào)。結(jié)構(gòu)如下圖所示：號(hào)。結(jié)構(gòu)如下圖所示： 函數(shù)的語(yǔ)法為函數(shù)的語(yǔ)法為: :函數(shù)名函數(shù)名( (參數(shù)參數(shù), ,參數(shù)參數(shù),）其中括號(hào)是不其中括號(hào)是不可少的可少的引用運(yùn)算符：冒號(hào)、

3、空格、逗號(hào)，感嘆號(hào)引用運(yùn)算符：冒號(hào)、空格、逗號(hào)，感嘆號(hào)冒號(hào)冒號(hào)(:)(:)， 是區(qū)域運(yùn)算符是區(qū)域運(yùn)算符逗號(hào)逗號(hào)(,)(,)， 是合并運(yùn)算符是合并運(yùn)算符感嘆號(hào)感嘆號(hào)(!)(!)， 是三維引用運(yùn)算符是三維引用運(yùn)算符, ,可以引用另一張工作表中的數(shù)據(jù)可以引用另一張工作表中的數(shù)據(jù). . 起其表示形式為起其表示形式為: :工作表名工作表名! !單元格引用區(qū)域單元格引用區(qū)域. . AVERAGEAVERAGE算術(shù)平均值算術(shù)平均值 COUNT COUNT 數(shù)值型數(shù)據(jù)的個(gè)數(shù)數(shù)值型數(shù)據(jù)的個(gè)數(shù) MAX MAX 最大值最大值 MIN MIN 最小值最小值 LEFT LEFT 從左邊截取字符從左邊截取字符 RIGHT

4、 RIGHT 從右邊截取字符從右邊截取字符 MID MID 從字符串截取字符從字符串截取字符 INT INT 整數(shù)值整數(shù)值 SUMSUM 求和求和 ROUNDROUND 按指定位數(shù)四舍五入某數(shù)字按指定位數(shù)四舍五入某數(shù)字 DATEDATE特定日期的序列數(shù)特定日期的序列數(shù) YEARYEAR特定年份的序列數(shù)特定年份的序列數(shù) TIMETIME 特定時(shí)間的序列數(shù)特定時(shí)間的序列數(shù) NOWNOW 當(dāng)前時(shí)間的序列數(shù)當(dāng)前時(shí)間的序列數(shù) IFIF（X,Z1,X2X,Z1,X2）當(dāng)當(dāng)X X為為TRUETRUE時(shí)，函數(shù)取值時(shí)，函數(shù)取值Z1Z1，否則取值，否則取值Z2Z2。統(tǒng)計(jì)統(tǒng)計(jì)函數(shù)函數(shù)文字文字函數(shù)函數(shù)數(shù)學(xué)、數(shù)學(xué)、三角

5、三角函數(shù)函數(shù)日期日期與時(shí)間與時(shí)間函數(shù)函數(shù)邏輯邏輯函數(shù)函數(shù)1 1）相對(duì)引用相對(duì)引用：將公式復(fù)制到新位置時(shí)，將公式復(fù)制到新位置時(shí)， ExcelExcel將相對(duì)于新位置修改公式中單元格的將相對(duì)于新位置修改公式中單元格的引用，引用，I3=F3+G3-H3I3=F3+G3-H3，如果該公式被復(fù)制到，如果該公式被復(fù)制到I6I6單元，則公式自動(dòng)變?yōu)閱卧?，則公式自動(dòng)變?yōu)镕6+G6-H6F6+G6-H6這就是這就是地址的相對(duì)引用，即公式在復(fù)制時(shí)單元格地址隨復(fù)制而改變。地址的相對(duì)引用，即公式在復(fù)制時(shí)單元格地址隨復(fù)制而改變。2 2）絕對(duì)引用絕對(duì)引用：有時(shí)我們?cè)趶?fù)制公式時(shí)，不希望公式中的地址隨復(fù)制而改變，如：有時(shí)我們

6、在復(fù)制公式時(shí)，不希望公式中的地址隨復(fù)制而改變，如：I I列列每個(gè)單元格的公式均為每個(gè)單元格的公式均為F3+G3-H3F3+G3-H3，這時(shí)就要用到絕對(duì)地址引用。絕對(duì)引用就是公式在，這時(shí)就要用到絕對(duì)地址引用。絕對(duì)引用就是公式在復(fù)制時(shí)不改變單元格地址。在單元格字母和數(shù)字前都加上一個(gè)復(fù)制時(shí)不改變單元格地址。在單元格字母和數(shù)字前都加上一個(gè)“$”$”符號(hào)就將地址變符號(hào)就將地址變成絕對(duì)地址。如：要實(shí)現(xiàn)上述操作應(yīng)把公式修改為：成絕對(duì)地址。如：要實(shí)現(xiàn)上述操作應(yīng)把公式修改為：$F $ 3+ $ G $ 3- $ H $ 3$F $ 3+ $ G $ 3- $ H $ 33 3）混合引用混合引用：在公式地址中即有

7、相對(duì)引用又有絕對(duì)引用稱為混合引用，如：在公式地址中即有相對(duì)引用又有絕對(duì)引用稱為混合引用，如：A$1A$1或或$A1;$A1;當(dāng)公式不是被復(fù)制到一行（列）而是一個(gè)區(qū)域時(shí)常使用混合引用當(dāng)公式不是被復(fù)制到一行（列）而是一個(gè)區(qū)域時(shí)常使用混合引用 ，例如：，例如： 地址的引用方法地址的引用方法) ) ) 就是一個(gè)函數(shù)的參數(shù)本身也是一個(gè)函數(shù)。就是一個(gè)函數(shù)的參數(shù)本身也是一個(gè)函數(shù)。 如下圖中所示的公式使用了嵌套的如下圖中所示的公式使用了嵌套的 AVERAGE AVERAGE 函數(shù)，并將結(jié)函數(shù)，并將結(jié)果與果與 50 50 相比較。這個(gè)公式的含義是：如果單元格相比較。這個(gè)公式的含義是：如果單元格F2F2到到F5F

8、5的平均值大的平均值大于于5050，則求，則求F2F2到到F5F5的和，否則顯示數(shù)值的和，否則顯示數(shù)值0 0。 1 1、在、在ExcelExcel中，一個(gè)公式最多可以嵌套中，一個(gè)公式最多可以嵌套7 7層函數(shù)。層函數(shù)。 2 2、嵌套函數(shù)是一個(gè)難度性較大的知識(shí)點(diǎn)，我們要清楚函數(shù)的意義及、嵌套函數(shù)是一個(gè)難度性較大的知識(shí)點(diǎn)，我們要清楚函數(shù)的意義及結(jié)構(gòu)，并且要熟悉其使用方法，括號(hào)必須成對(duì)出現(xiàn)。結(jié)構(gòu)，并且要熟悉其使用方法，括號(hào)必須成對(duì)出現(xiàn)。 2 2、SUMSUM 函數(shù)函數(shù) 行或列求和行或列求和 H3單元格單元格 =SUM(B3:G3) B11單元格單元格 =SUM(B3:B10)2 2、SUMSUM 函數(shù)

9、函數(shù) 區(qū)域求和區(qū)域求和 H11單元格單元格 =SUM(B3:G10)2 2、SUMSUM 函數(shù)函數(shù) 鼠標(biāo)拖拉法鼠標(biāo)拖拉法 選中存放求和結(jié)果的單元格，點(diǎn)擊工具欄選中存放求和結(jié)果的單元格，點(diǎn)擊工具欄中的中的“”（自動(dòng)求和）按鈕（自動(dòng)求和）按鈕 用鼠標(biāo)拖過(guò)需要求和的所有單元格，然后用鼠標(biāo)拖過(guò)需要求和的所有單元格，然后回車即可回車即可 注：如果需要求和的單元格是不連續(xù)的，注：如果需要求和的單元格是不連續(xù)的，則可按住則可按住CtrlCtrl鍵分別拖過(guò)它們。鍵分別拖過(guò)它們。3 3、AVERAGEAVERAGE / AVERAGE/ AVERAGEA A / /TRIMMEANTRIMMEAN函數(shù)函數(shù) AV

10、ERAGEAVERAGE函數(shù)函數(shù) 用途：求算數(shù)平均數(shù)用途：求算數(shù)平均數(shù) 使用方法：同使用方法：同SUMSUM函數(shù)相同，只需把剛才所有函數(shù)相同，只需把剛才所有“SUMSUM”的部分替換成的部分替換成“AVERAGEAVERAGE” =AVERAGE(B3:G3) =AVERAGE(B3:B10) =AVERAGE(B3:G10)3 3、AVERAGE/ AVERAGE/ AVERAGEAVERAGEA A / /TRIMMEANTRIMMEAN函數(shù)函數(shù)AVERAGEA 函數(shù)函數(shù)3 3、AVERAGE/ AVERAGEAVERAGE/ AVERAGEA A / /TRIMMEANTRIMMEAN

11、函數(shù)函數(shù) TRIMMEANTRIMMEAN函數(shù)函數(shù) 用途：求數(shù)據(jù)集的內(nèi)部平均值（即可以按照要用途：求數(shù)據(jù)集的內(nèi)部平均值（即可以按照要求去掉所有數(shù)據(jù)中最高的和最低的一些數(shù)據(jù)，求去掉所有數(shù)據(jù)中最高的和最低的一些數(shù)據(jù)，然后求平均值）然后求平均值） 使用方法：使用方法：TRIMMEANTRIMMEAN（array,percentarray,percent) ) 例例需要求內(nèi)需要求內(nèi)部平均值部平均值的所有數(shù)的所有數(shù)據(jù)區(qū)域據(jù)區(qū)域需要去除需要去除的數(shù)據(jù)占的數(shù)據(jù)占全部數(shù)據(jù)全部數(shù)據(jù)的比例的比例4 4、COUNT/COUNTACOUNT/COUNTA函數(shù)函數(shù) 用途：計(jì)數(shù)用途：計(jì)數(shù) COUNTCOUNT統(tǒng)計(jì)所選中的

12、單元格區(qū)域中數(shù)字的個(gè)數(shù)統(tǒng)計(jì)所選中的單元格區(qū)域中數(shù)字的個(gè)數(shù) COUNTACOUNTA統(tǒng)計(jì)所選中的單元格區(qū)域中非空單元統(tǒng)計(jì)所選中的單元格區(qū)域中非空單元格的個(gè)數(shù)（包括數(shù)字、文字等）格的個(gè)數(shù)（包括數(shù)字、文字等） 例例: :5 5、ROUND/TRUNCROUND/TRUNC函數(shù)函數(shù) 功能：功能： ROUNDROUND函數(shù)函數(shù)對(duì)小數(shù)按照位數(shù)要求四舍五入對(duì)小數(shù)按照位數(shù)要求四舍五入 （亦可通過(guò)設(shè)置（亦可通過(guò)設(shè)置“單元格格式單元格格式”實(shí)現(xiàn)同樣功能）實(shí)現(xiàn)同樣功能） ROUND(number,num_digits) number就是將要進(jìn)行四舍五入的數(shù)字就是將要進(jìn)行四舍五入的數(shù)字 num_digits則是需要保

13、留小數(shù)點(diǎn)后幾位則是需要保留小數(shù)點(diǎn)后幾位 TRUNCTRUNC函數(shù)函數(shù)對(duì)小數(shù)進(jìn)行取整對(duì)小數(shù)進(jìn)行取整 （同（同INTINT函數(shù)功能）函數(shù)功能） TRUNC(number) number就是需要進(jìn)行取整的數(shù)字就是需要進(jìn)行取整的數(shù)字5 5、ROUND/TRUNCROUND/TRUNC函數(shù)函數(shù) 使用方法：使用方法： B2B2單元格單元格 =ROUND(A2,=ROUND(A2, 2 2) ) 代表保留的小代表保留的小數(shù)數(shù)位位數(shù)數(shù) C2C2單元格單元格 =ROUND(A2,=ROUND(A2, 0 0) ) 注注: 如果位數(shù)如果位數(shù)0,則舍入到指定的小數(shù)位則舍入到指定的小數(shù)位;如果位數(shù)如果位數(shù)=0,則舍入

14、到整數(shù)則舍入到整數(shù).如果位數(shù)如果位數(shù)2020”),),可以求出在可以求出在B2B2到到B4B4這三個(gè)單元格中大于這三個(gè)單元格中大于2020的數(shù)字有幾個(gè)的數(shù)字有幾個(gè)6 6、IFIF函數(shù)（函數(shù)（COUNTIF/SUMIFCOUNTIF/SUMIF） SUMIFSUMIF函數(shù)函數(shù) 功能：統(tǒng)計(jì)符合條件要求的單元格中數(shù)字之和功能：統(tǒng)計(jì)符合條件要求的單元格中數(shù)字之和 例，例，* * 在在B5B5單元格中輸入單元格中輸入=sumif(B2:B4,=sumif(B2:B4,”2020”),),可以求出在可以求出在B2B2到到B4B4這三這三個(gè)單元格中大于個(gè)單元格中大于2020的數(shù)字之和的數(shù)字之和 。( (即即

15、: :加加總總符合某特定搜符合某特定搜尋準(zhǔn)則尋準(zhǔn)則的的儲(chǔ)儲(chǔ)存格存格 )* * IFIF函數(shù)可以嵌套使用函數(shù)可以嵌套使用, ,最多為最多為7 7層層, ,如：如：= =IF(C2=90,IF(C2=90,“優(yōu)優(yōu) ,IF(C2=75,IF(C2=75,良良, IF(C2=60, IF(C2=60,中中,差差)FREQUENCYFREQUENCY函數(shù)函數(shù) 功能：功能：計(jì)計(jì)算某一算某一個(gè)範(fàn)圍內(nèi)個(gè)範(fàn)圍內(nèi)的值出的值出現(xiàn)現(xiàn)的次的次數(shù)數(shù)，並傳並傳回一回一個(gè)個(gè)垂垂 直直數(shù)數(shù)值值陣陣列。列。 例如例如, ,用用 FREQUENCY FREQUENCY 來(lái)計(jì)來(lái)計(jì)算某些算某些範(fàn)圍內(nèi)範(fàn)圍內(nèi)的考的考試試成成績(jī)績(jī)各有各有幾

16、個(gè)幾個(gè)人。人。 由於由於 FREQUENCY FREQUENCY 回回陣陣列，因此必列，因此必須輸須輸入入為陣為陣列公式。列公式。 附附註註: : 範(fàn)範(fàn)例的公式必例的公式必須輸須輸入入為為一一個(gè)陣個(gè)陣列公式。在列公式。在複製範(fàn)複製範(fàn)例到另一例到另一個(gè)個(gè)空空白工作表之後，白工作表之後，選選取取範(fàn)圍範(fàn)圍 A13:A16 A13:A16 儲(chǔ)儲(chǔ)存格存格開(kāi)開(kāi)始公式。按一下始公式。按一下 F2F2，然後，然後按下按下 CTRL+SHIFT+ENTERCTRL+SHIFT+ENTER。如果公式不是一。如果公式不是一個(gè)陣個(gè)陣列公式，就只有列公式，就只有單單一一結(jié)結(jié)果果 1 1。7 7、 FREQUENCY F

17、REQUENCY 陣列陣列函數(shù)函數(shù)8 8、MODMOD函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 選中需要設(shè)置間隔式底紋的區(qū)域選中需要設(shè)置間隔式底紋的區(qū)域 “格式格式條件格式條件格式”，彈出的，彈出的“條件格式條件格式”對(duì)話對(duì)話框（如圖）框（如圖） 在左側(cè)的下拉菜單中選擇在左側(cè)的下拉菜單中選擇“公式公式”，并在右側(cè)區(qū)，并在右側(cè)區(qū)域輸入域輸入=MOD(ROW()，2),然后點(diǎn)擊然后點(diǎn)擊“格式格式”按按鈕鈕8 8、MODMOD函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 功能：設(shè)置間隔式底紋功能：設(shè)置間隔式底紋 例，例，8 8、MODMOD函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 在彈出的在

18、彈出的“單元格格式單元格格式”對(duì)話框中，選擇對(duì)話框中，選擇“圖案圖案”選項(xiàng)卡，然后選擇需要的顏色，選項(xiàng)卡，然后選擇需要的顏色，“確定確定” 點(diǎn)擊點(diǎn)擊“條件格式條件格式”對(duì)話框中的對(duì)話框中的“確定確定”按鈕按鈕9 9、日期函數(shù)、日期函數(shù)函函 數(shù)數(shù)功功 能能應(yīng)用舉例應(yīng)用舉例結(jié)結(jié) 果果DATE(year,month,day)生成日期生成日期=DATE(99,7,25)1999-7-25DAY(date)取日期的天數(shù)取日期的天數(shù)=DAY(date(99,7,25)25MONTH(date)取日期的月份取日期的月份=MONTH(date(99,7,25)7NOW()取系統(tǒng)的日期和取系統(tǒng)的日期和時(shí)間時(shí)間=NOW()取系統(tǒng)當(dāng)前取系統(tǒng)當(dāng)前時(shí)間時(shí)間TIME(hour,minute,second)返回代表指定時(shí)返回代表指定時(shí)間的序列數(shù)間的序列數(shù)=TIME(16,48,10)4:48:10 PMTODAY()求系統(tǒng)日期求系統(tǒng)日期=TODAY()取系統(tǒng)當(dāng)前取系統(tǒng)當(dāng)前日期日期YEAR(date)取日期的年份取日期的年份=YEAR(date(99,7,25)99DATEDIF計(jì)算間隔時(shí)間計(jì)算間隔時(shí)間9 9、日期函數(shù)、日期