EXCEL技巧EXCEL常用函數(shù)簡(jiǎn)介課件

1、EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介EXCELEXCEL實(shí)用技巧實(shí)用技巧 EXCELEXCEL常用函數(shù)簡(jiǎn)介常用函數(shù)簡(jiǎn)介2002009 9年年1111月月2525日日EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介主要內(nèi)容主要內(nèi)容 SUMSUM 函數(shù)函數(shù)1 1 AVERAGE/AVERAGEAVERAGE/AVERAGEA/A/TRIMMEANTRIMMEAN函數(shù)函數(shù)2 2COUNT/COUNTACOUNT/COUNTA 計(jì)數(shù)計(jì)數(shù)函數(shù)函數(shù)3 34 45 5 函數(shù)函數(shù)基基礎(chǔ)礎(chǔ)知知識(shí)識(shí)ROUND/TRUNCROUND/TRUNC 函數(shù)函數(shù)EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介主要內(nèi)容主要內(nèi)容IFIF函數(shù)（函

2、數(shù)（COUNTIF/SUMIFCOUNTIF/SUMIF）789公式中常見(jiàn)的錯(cuò)誤信息公式中常見(jiàn)的錯(cuò)誤信息10FREQUNCY FREQUNCY 陣陣列列函數(shù)函數(shù)MODMOD函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用6常用函數(shù)快捷方式常用函數(shù)快捷方式EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介 Excel函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。函數(shù)以等號(hào) (=) 開(kāi)始，后面緊跟函數(shù)名稱和左括號(hào)，然后以逗號(hào)分隔輸入?yún)?shù)，最后是右括號(hào)。結(jié)構(gòu)如下圖所示： EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介函數(shù)的語(yǔ)法為函數(shù)的語(yǔ)法為: :函數(shù)名函數(shù)名( (參數(shù)參數(shù), ,參數(shù)參數(shù),）其中括號(hào)是不其中括號(hào)是

3、不可少的可少的引用運(yùn)算符引用運(yùn)算符：冒號(hào)、空格、逗號(hào)，感嘆號(hào)冒號(hào)、空格、逗號(hào)，感嘆號(hào)冒號(hào)冒號(hào)(:)(:)， 是區(qū)域運(yùn)算符是區(qū)域運(yùn)算符逗號(hào)逗號(hào)(,)(,)， 是合并運(yùn)算符是合并運(yùn)算符空格空格, , 是交叉運(yùn)算符是交叉運(yùn)算符, ,取引用區(qū)域的公共部分取引用區(qū)域的公共部分. .感嘆號(hào)感嘆號(hào)(!)(!)， 是三維引用運(yùn)算符是三維引用運(yùn)算符, ,可以引用另一張工作表中的數(shù)據(jù)可以引用另一張工作表中的數(shù)據(jù). . 起其表示形式為起其表示形式為: :工作表名工作表名! !單元格引用區(qū)域單元格引用區(qū)域. .EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介 AVERAGE算術(shù)平均值 COUNT 數(shù)值型數(shù)據(jù)的個(gè)數(shù) MAX 最大

4、值 MIN 最小值 LEFT 從左邊截取字符 RIGHT 從右邊截取字符 MID 從字符串截取字符 INT 整數(shù)值 SUM 求和 ROUND 按指定位數(shù)四舍五入某數(shù)字 DATE特定日期的序列數(shù) YEAR特定年份的序列數(shù) TIME 特定時(shí)間的序列數(shù) NOW 當(dāng)前時(shí)間的序列數(shù) IF（X,Z1,X2）當(dāng)X為TRUE時(shí)，函數(shù)取值Z1，否則取值Z2。統(tǒng)計(jì)函數(shù)文字函數(shù)數(shù)學(xué)、三角函數(shù)日期與時(shí)間函數(shù)邏輯函數(shù)EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介1）相對(duì)引用：）相對(duì)引用：將公式復(fù)制到新位置時(shí)，將公式復(fù)制到新位置時(shí)， Excel將相對(duì)于新位置修改公式中單元格的引將相對(duì)于新位置修改公式中單元格的引用，用，I3=F3

5、+G3-H3，如果該公式被復(fù)制到，如果該公式被復(fù)制到I6單元，則公式自動(dòng)變?yōu)閱卧?，則公式自動(dòng)變?yōu)镕6+G6-H6這就是這就是地址的相對(duì)引用，即公式在復(fù)制時(shí)單元格地址隨復(fù)制而改變。地址的相對(duì)引用，即公式在復(fù)制時(shí)單元格地址隨復(fù)制而改變。2）絕對(duì)引用：）絕對(duì)引用：有時(shí)我們?cè)趶?fù)制公式時(shí)，不希望公式中的地址隨復(fù)制而改變，如：有時(shí)我們?cè)趶?fù)制公式時(shí)，不希望公式中的地址隨復(fù)制而改變，如：I列列每個(gè)單元格的公式均為每個(gè)單元格的公式均為F3+G3-H3，這時(shí)就要用到絕對(duì)地址引用。絕對(duì)引用就是公式，這時(shí)就要用到絕對(duì)地址引用。絕對(duì)引用就是公式在復(fù)制時(shí)不改變單元格地址。在單元格字母和數(shù)字前都加上一個(gè)在復(fù)制時(shí)不改變單元格

6、地址。在單元格字母和數(shù)字前都加上一個(gè)“$”符號(hào)就將地址變符號(hào)就將地址變成絕對(duì)地址。如：要實(shí)現(xiàn)上述操作應(yīng)把公式修改為：成絕對(duì)地址。如：要實(shí)現(xiàn)上述操作應(yīng)把公式修改為：$F $ 3+ $ G $ 3- $ H $ 33）混合引用：）混合引用：在公式地址中即有相對(duì)引用又有絕對(duì)引用稱為混合引用，如：在公式地址中即有相對(duì)引用又有絕對(duì)引用稱為混合引用，如：A$1或或$A1;當(dāng)公式不是被復(fù)制到一行（列）而是一個(gè)區(qū)域時(shí)常使用混合引用當(dāng)公式不是被復(fù)制到一行（列）而是一個(gè)區(qū)域時(shí)常使用混合引用 ，例如：，例如： 地址的引用方法地址的引用方法) )EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介) ) 就是一個(gè)函數(shù)的參數(shù)本身也是

7、一個(gè)函數(shù)。就是一個(gè)函數(shù)的參數(shù)本身也是一個(gè)函數(shù)。 如下圖中所示的公式使用了嵌套的如下圖中所示的公式使用了嵌套的 AVERAGE AVERAGE 函數(shù)，并將結(jié)函數(shù)，并將結(jié)果與果與 50 50 相比較。這個(gè)公式的含義是：如果單元格相比較。這個(gè)公式的含義是：如果單元格F2F2到到F5F5的平均值大的平均值大于于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)，我們

8、要清楚函數(shù)的意義及結(jié)構(gòu)，并且要熟悉其使用方法，括號(hào)必須成對(duì)出現(xiàn)。結(jié)構(gòu)，并且要熟悉其使用方法，括號(hào)必須成對(duì)出現(xiàn)。 EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介2 2、SUMSUM 函數(shù)函數(shù) 行或列求和行或列求和 H3單元格單元格 =SUM(B3:G3) B11單元格單元格 =SUM(B3:B10)EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介2 2、SUMSUM 函數(shù)函數(shù) 區(qū)域求和區(qū)域求和 H11單元格單元格 =SUM(B3:G10)EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介2 2、SUMSUM 函數(shù)函數(shù) 鼠標(biāo)拖拉法鼠標(biāo)拖拉法 選中存放求和結(jié)果的單元格，點(diǎn)擊工具欄選中存放求和結(jié)果的單元格，點(diǎn)擊工具欄中的中的“”（

9、自動(dòng)求和）按鈕（自動(dòng)求和）按鈕 用鼠標(biāo)拖過(guò)需要求和的所有單元格，然后用鼠標(biāo)拖過(guò)需要求和的所有單元格，然后回車即可回車即可 注：如果需要求和的單元格是不連續(xù)的，注：如果需要求和的單元格是不連續(xù)的，則可按住則可按住CtrlCtrl鍵分別拖過(guò)它們。鍵分別拖過(guò)它們。EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介3 3、AVERAGEAVERAGE / AVERAGE/ AVERAGEA A / /TRIMMEANTRIMMEAN函數(shù)函數(shù) AVERAGEAVERAGE函數(shù)函數(shù) 用途：求算數(shù)平均數(shù)用途：求算數(shù)平均數(shù) 使用方法：同使用方法：同SUMSUM函數(shù)相同，只需把剛才所有函數(shù)相同，只需把剛才所有“SUM”SU

10、M”的部分替換成的部分替換成“AVERAGE”AVERAGE” =AVERAGE(B3:G3) =AVERAGE(B3:B10) =AVERAGE(B3:G10)EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介3 3、AVERAGE/ AVERAGE/ AVERAGEAVERAGEA A / /TRIMMEANTRIMMEAN函數(shù)函數(shù)AVERAGEA 函數(shù)函數(shù)EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介3 3、AVERAGE/ AVERAGEAVERAGE/ AVERAGEA A / /TRIMMEANTRIMMEAN 函數(shù)函數(shù) TRIMMEANTRIMMEAN函數(shù)函數(shù) 用途：求數(shù)據(jù)集的內(nèi)部平均值（即可以按照

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

12、數(shù) COUNTACOUNTA統(tǒng)計(jì)所選中的單元格區(qū)域中非空單元統(tǒng)計(jì)所選中的單元格區(qū)域中非空單元格的個(gè)數(shù)（包括數(shù)字、文字等）格的個(gè)數(shù)（包括數(shù)字、文字等） 例例: :EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介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則是需要保留小數(shù)點(diǎn)后幾位則

13、是需要保留小數(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ù)字EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介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ù)位;如果位

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

15、B2B2到到B4B4這這三個(gè)單元格中大于三個(gè)單元格中大于2020的數(shù)字之和的數(shù)字之和 。( (即即: :加加總總符合某特定搜符合某特定搜尋準(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,中中,差差)EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介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ù)值

16、值陣陣列。列。 例如例如, ,用用 FREQUENCY FREQUENCY 來(lái)計(jì)來(lái)計(jì)算某些算某些範(fàn)圍內(nèi)範(fàn)圍內(nèi)的考的考試試成成績(jī)績(jī)各有各有幾個(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

17、+ENTER。如果公式不是一。如果公式不是一個(gè)陣個(gè)陣列公式，就只有列公式，就只有單單一一結(jié)結(jié)果果 1 1。7 7、 FREQUENCY FREQUENCY 陣列陣列函數(shù)函數(shù)EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介9 9、MODMOD函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 功能：設(shè)置間隔式底紋功能：設(shè)置間隔式底紋 例，例，EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介8 8、MODMOD函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 選中需要設(shè)置間隔式底紋的區(qū)域選中需要設(shè)置間隔式底紋的區(qū)域 “格式格式條件格式條件格式”，彈出的，彈出的“條件格式條件格式”對(duì)話對(duì)話框（如圖）框（如圖） 在左側(cè)的下拉菜單中

18、選擇在左側(cè)的下拉菜單中選擇“公式公式”，并在右側(cè)區(qū)，并在右側(cè)區(qū)域輸入域輸入=MOD(ROW()，2),然后點(diǎn)擊然后點(diǎn)擊“格式格式”按按鈕鈕EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介8 8、MODMOD函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 在彈出的在彈出的“單元格格式單元格格式”對(duì)話框中，選擇對(duì)話框中，選擇“圖案圖案”選項(xiàng)卡，然后選擇需要的顏色，選項(xiàng)卡，然后選擇需要的顏色，“確定確定” 點(diǎn)擊點(diǎn)擊“條件格式條件格式”對(duì)話框中的對(duì)話框中的“確定確定”按鈕按鈕EXCEL技巧-EXCEL常用函數(shù)簡(jiǎn)介9 9、常用函數(shù)快捷方式、常用函數(shù)快捷方式在在excel表格的右下角有一個(gè)常用函數(shù)快捷方式的區(qū)域表格的右下角有一個(gè)常用函數(shù)快捷方式的區(qū)域（如同所示），可以右鍵點(diǎn)擊該區(qū)域，使用六種常用函數(shù)（如同所示），可以右鍵點(diǎn)擊該區(qū)域，使用六種常用函數(shù)這六種常用函數(shù)，分別是：這六種常用函數(shù)，分別是： 平均值（平均值（AVERAGE） 計(jì)數(shù)（計(jì)數(shù)（COU