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

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

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

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

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

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

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

7、一個函數(shù)。就是一個函數(shù)的參數(shù)本身也是一個函數(shù)。 如下圖中所示的公式使用了嵌套的如下圖中所示的公式使用了嵌套的 AVERAGE AVERAGE 函數(shù)，并將結函數(shù)，并將結果與果與 50 50 相比較。這個公式的含義是：如果單元格相比較。這個公式的含義是：如果單元格F2F2到到F5F5的平均值大的平均值大于于5050，則求，則求F2F2到到F5F5的和，否則顯示數(shù)值的和，否則顯示數(shù)值0 0。 1 1、在、在ExcelExcel中，一個公式最多可以嵌套中，一個公式最多可以嵌套7 7層函數(shù)。層函數(shù)。 2 2、嵌套函數(shù)是一個難度性較大的知識點，我們要清楚函數(shù)的意義及、嵌套函數(shù)是一個難度性較大的知識點，我們

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

9、自動求和）按鈕（自動求和）按鈕 用鼠標拖過需要求和的所有單元格，然后用鼠標拖過需要求和的所有單元格，然后回車即可回車即可 注：如果需要求和的單元格是不連續(xù)的，注：如果需要求和的單元格是不連續(xù)的，則可按住則可按住CtrlCtrl鍵分別拖過它們。鍵分別拖過它們。EXCEL技巧-EXCEL常用函數(shù)簡介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ù)簡介3 3、AVERAGE/ AVERAGE/ AVERAGEAVERAGEA A / /TRIMMEANTRIMMEAN函數(shù)函數(shù)AVERAGEA 函數(shù)函數(shù)EXCEL技巧-EXCEL常用函數(shù)簡介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ù)簡介4 4、COUNT/COUNTACOUNT/COUNTA函數(shù)函數(shù) 用途：計數(shù)用途：計數(shù) COUNTCOUNT統(tǒng)計所選中的單元格區(qū)域中數(shù)字的個數(shù)統(tǒng)計所選中的單元格區(qū)域中數(shù)字的個

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

13、是需要保留小數(shù)點后幾位 TRUNCTRUNC函數(shù)函數(shù)對小數(shù)進行取整對小數(shù)進行取整 （同（同INTINT函數(shù)功能）函數(shù)功能） TRUNC(number) number就是需要進行取整的數(shù)字就是需要進行取整的數(shù)字EXCEL技巧-EXCEL常用函數(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ù)位;如果位

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

15、B2B2到到B4B4這這三個單元格中大于三個單元格中大于2020的數(shù)字之和的數(shù)字之和 。( (即即: :加加總總符合某特定搜符合某特定搜尋準尋準則則的的儲儲存格存格 )* * 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ù)簡介FREQUENCYFREQUENCY函數(shù)函數(shù) 功能：功能：計計算某一算某一個範圍內(nèi)個範圍內(nèi)的值出的值出現(xiàn)現(xiàn)的次的次數(shù)數(shù)，並傳並傳回一回一個個垂垂 直直數(shù)數(shù)值

16、值陣陣列。列。 例如例如, ,用用 FREQUENCY FREQUENCY 來計來計算某些算某些範圍內(nèi)範圍內(nèi)的考的考試試成成績績各有各有幾個幾個人。人。 由於由於 FREQUENCY FREQUENCY 回回陣陣列，因此必列，因此必須輸須輸入入為陣為陣列公式。列公式。 附附註註: : 範範例的公式必例的公式必須輸須輸入入為為一一個陣個陣列公式。在列公式。在複製範複製範例到另一例到另一個個空空白工作表之後，白工作表之後，選選取取範圍範圍 A13:A16 A13:A16 儲儲存格存格開開始公式。按一下始公式。按一下 F2F2，然後，然後按下按下 CTRL+SHIFT+ENTERCTRL+SHIFT

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

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