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

1、excelexcel實用技巧實用技巧 excelexcel常用函數(shù)簡介常用函數(shù)簡介生技部生技部2002009 9年年1111月月2525日日主要內(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ù)基基礎(chǔ)礎(chǔ)知知識識round/truncround/trunc 函數(shù)函數(shù)主要內(nèi)容主要內(nèi)容ifif函數(shù)（函數(shù)（countif/sumifcountif/sumif）789公式中常見的錯誤信息公式中常見的錯誤信

2、息10frequncy frequncy 陣陣列列函數(shù)函數(shù)modmod函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用6常用函數(shù)快捷方式常用函數(shù)快捷方式 excel函數(shù)即是預(yù)先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。函數(shù)以等號 (=) 開始，后面緊跟函數(shù)名稱和左括號，然后以逗號分隔輸入?yún)?shù)，最后是右括號。結(jié)構(gòu)如下圖所示： 函數(shù)的語法為函數(shù)的語法為: :函數(shù)名函數(shù)名( (參數(shù)參數(shù), ,參數(shù)參數(shù), ,）其中括號是不其中括號是不可少的可少的引用運算符引用運算符：冒號、空格、逗號，感嘆號冒號、空格、逗號，感嘆號冒號冒號(:)(:)， 是區(qū)域運算符是區(qū)域運算符逗號逗號(,)(,)， 是合并運算符是合并

3、運算符空格空格, , 是交叉運算符是交叉運算符, ,取引用區(qū)域的公共部分取引用區(qū)域的公共部分. .感嘆號感嘆號(!)(!)， 是三維引用運算符是三維引用運算符, ,可以引用另一張工作表中的數(shù)據(jù)可以引用另一張工作表中的數(shù)據(jù). . 起其表示形式為起其表示形式為: :工作表名工作表名! !單元格引用區(qū)域單元格引用區(qū)域. . average算術(shù)平均值 count 數(shù)值型數(shù)據(jù)的個數(shù) max 最大值 min 最小值 left 從左邊截取字符 right 從右邊截取字符 mid 從字符串截取字符 int 整數(shù)值 sum 求和 round 按指定位數(shù)四舍五入某數(shù)字 date特定日期的序列數(shù) year特定年份的

4、序列數(shù) time 特定時間的序列數(shù) now 當前時間的序列數(shù) if（x,z1,x2）當x為true時，函數(shù)取值z1，否則取值z2。統(tǒng)計函數(shù)文字函數(shù)數(shù)學(xué)、三角函數(shù)日期與時間函數(shù)邏輯函數(shù)1）相對引用：）相對引用：將公式復(fù)制到新位置時，將公式復(fù)制到新位置時， excel將相對于新位置修改公式中單元格的引將相對于新位置修改公式中單元格的引用，用，i3=f3+g3-h3，如果該公式被復(fù)制到，如果該公式被復(fù)制到i6單元，則公式自動變?yōu)閱卧?，則公式自動變?yōu)閒6+g6-h6這就是這就是地址的相對引用，即公式在復(fù)制時單元格地址隨復(fù)制而改變。地址的相對引用，即公式在復(fù)制時單元格地址隨復(fù)制而改變。2）絕對引用：）絕

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

6、有相對引用又有絕對引用稱為混合引用，如：在公式地址中即有相對引用又有絕對引用稱為混合引用，如：a$1或或$a1;當公式不是被復(fù)制到一行（列）而是一個區(qū)域時常使用混合引用當公式不是被復(fù)制到一行（列）而是一個區(qū)域時常使用混合引用 ，例如：，例如： 地址的引用方法地址的引用方法) ) ) 就是一個函數(shù)的參數(shù)本身也是一個函數(shù)。就是一個函數(shù)的參數(shù)本身也是一個函數(shù)。 如下圖中所示的公式使用了嵌套的如下圖中所示的公式使用了嵌套的 average average 函數(shù)，并將結(jié)函數(shù)，并將結(jié)果與果與 50 50 相比較。這個公式的含義是：如果單元格相比較。這個公式的含義是：如果單元格f2f2到到f5f5的平均值大

7、的平均值大于于5050，則求，則求f2f2到到f5f5的和，否則顯示數(shù)值的和，否則顯示數(shù)值0 0。 1 1、在、在excelexcel中，一個公式最多可以嵌套中，一個公式最多可以嵌套7 7層函數(shù)。層函數(shù)。 2 2、嵌套函數(shù)是一個難度性較大的知識點，我們要清楚函數(shù)的意義及、嵌套函數(shù)是一個難度性較大的知識點，我們要清楚函數(shù)的意義及結(jié)構(gòu)，并且要熟悉其使用方法，括號必須成對出現(xiàn)。結(jié)構(gòu)，并且要熟悉其使用方法，括號必須成對出現(xiàn)。 2 2、sumsum 函數(shù)函數(shù) 行或列求和行或列求和 h3單元格單元格 =sum(b3:g3) b11單元格單元格 =sum(b3:b10)2 2、sumsum 函數(shù)函數(shù) 區(qū)域求

8、和區(qū)域求和 h11單元格單元格 =sum(b3:g10)2 2、sumsum 函數(shù)函數(shù) 鼠標拖拉法鼠標拖拉法 選中存放求和結(jié)果的單元格，點擊工具欄選中存放求和結(jié)果的單元格，點擊工具欄中的中的“”（自動求和）按（自動求和）按鈕鈕 用鼠標拖過需要求和的所有單元格，然后用鼠標拖過需要求和的所有單元格，然后回車即可回車即可 注：如果需要求和的單元格是不連續(xù)的，注：如果需要求和的單元格是不連續(xù)的，則可按住則可按住ctrlctrl鍵分別拖過它們。鍵分別拖過它們。3 3、averageaverage / average/ averagea a / /trimmeantrimmean函數(shù)函數(shù) averagea

9、verage函數(shù)函數(shù) 用途：求算數(shù)平均數(shù)用途：求算數(shù)平均數(shù) 使用方法：同使用方法：同sumsum函數(shù)相同，只需把剛才所有函數(shù)相同，只需把剛才所有“sum”sum”的部分替換成的部分替換成“average”average” =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 函數(shù)函數(shù)

10、 trimmeantrimmean函數(shù)函數(shù) 用途：求數(shù)據(jù)集的內(nèi)部平均值（即可以按照要用途：求數(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ù)的比例的比例4 4、count/countacount/counta函數(shù)函數(shù) 用途：計數(shù)用途：計數(shù) countcount統(tǒng)計所選中的單元格區(qū)域

11、中數(shù)字的個數(shù)統(tǒng)計所選中的單元格區(qū)域中數(shù)字的個數(shù) countacounta統(tǒng)計所選中的單元格區(qū)域中非空單元統(tǒng)計所選中的單元格區(qū)域中非空單元格的個數(shù)（包括數(shù)字、文字等）格的個數(shù)（包括數(shù)字、文字等） 例例: :5 5、round/truncround/trunc函數(shù)函數(shù) 功能：功能： roundround函數(shù)函數(shù)對小數(shù)按照位數(shù)要求四舍五入對小數(shù)按照位數(shù)要求四舍五入 （亦可通過設(shè)置（亦可通過設(shè)置“單元格格式單元格格式”實現(xiàn)同樣功能）實現(xiàn)同樣功能） round(number,num_digits) number就是將要進行四舍五入的數(shù)字就是將要進行四舍五入的數(shù)字 num_digits則是需要保留小數(shù)點后

12、幾位則是需要保留小數(shù)點后幾位 trunctrunc函數(shù)函數(shù)對小數(shù)進行取整對小數(shù)進行取整 （同（同intint函數(shù)功能）函數(shù)功能） trunc(number) number就是需要進行取整的數(shù)字就是需要進行取整的數(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,則舍入到整數(shù)則舍

13、入到整數(shù).如果位數(shù)如果位數(shù)20”),(b2:b4,”20”),可以求出在可以求出在b2b2到到b4b4這三個單元格中大于這三個單元格中大于2020的數(shù)字有幾個的數(shù)字有幾個6 6、ifif函數(shù)（函數(shù)（countif/sumifcountif/sumif） sumifsumif函數(shù)函數(shù) 功能：統(tǒng)計符合條件要求的單元格中數(shù)字之和功能：統(tǒng)計符合條件要求的單元格中數(shù)字之和 例，例，* * 在在b5b5單元格中輸入單元格中輸入=sumif=sumif(b2:b4,”20”),(b2:b4,”20”),可以求出在可以求出在b2b2到到b4b4這這三個單元格中大于三個單元格中大于2020的數(shù)字之和的數(shù)字之和

14、。( (即即: :加加總總符合某特定搜符合某特定搜尋準尋準則則的的儲儲存格存格 )* * 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ù) 功能：功能：計計算某一算某一個範圍內(nèi)個範圍內(nèi)的值出的值出現(xiàn)現(xiàn)的次的次數(shù)數(shù)，並傳並傳回一回一個個垂垂 直直數(shù)數(shù)值值陣陣列。列。 例如例如, ,用用 frequency frequency 來計來計算某些算某些範圍內(nèi)範圍內(nèi)的考的考試試成成

15、績績各有各有幾個幾個人。人。 由於由於 frequency frequency 回回陣陣列，因此必列，因此必須輸須輸入入為陣為陣列公式。列公式。 附附註註: : 範範例的公式必例的公式必須輸須輸入入為為一一個陣個陣列公式。在列公式。在複製範複製範例到另一例到另一個個空空白工作表之後，白工作表之後，選選取取範圍範圍 a13:a16 a13:a16 儲儲存格存格開開始公式。按一下始公式。按一下 f2f2，然後，然後按下按下 ctrl+shift+enterctrl+shift+enter。如果公式不是一。如果公式不是一個陣個陣列公式，就只有列公式，就只有單單一一結(jié)結(jié)果果 1 1。7 7、 freq

16、uency frequency 陣列陣列函數(shù)函數(shù)9 9、modmod函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 功能：設(shè)置間隔式底紋功能：設(shè)置間隔式底紋 例，例，8 8、modmod函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 選中需要設(shè)置間隔式底紋的區(qū)域選中需要設(shè)置間隔式底紋的區(qū)域 “格式格式條件格式條件格式”，彈出的，彈出的“條件格式條件格式”對話對話框（如圖）框（如圖） 在左側(cè)的下拉菜單中選擇在左側(cè)的下拉菜單中選擇“公式公式”，并在右側(cè)區(qū)，并在右側(cè)區(qū)域輸入域輸入=mod(row()，2),然后點擊然后點擊“格式格式”按按鈕鈕8 8、modmod函數(shù)在美化表格中的應(yīng)用函數(shù)在美化表格中的應(yīng)用 在彈出的在彈出的“單元格格式單元格格式”對話框中，選擇對話框中，選擇“圖案圖案”選項卡，然后選擇需要的顏色，選項卡，然后選擇需要的顏色，“確定確定” 點擊點擊“條件格式條件格式”對話框中的對話框中的“確定確定”按鈕按鈕9 9、常用函數(shù)快捷方式常用函數(shù)快捷方式在在excel表格的右下角有一個常用函數(shù)快捷方式的區(qū)域表格的右下角有一個常用函數(shù)快捷方式的區(qū)域（如同所示），可以右鍵點擊該區(qū)域，使用六種常用函數(shù)（如同所示），可以右鍵點擊該區(qū)域，使用六種常用函數(shù)這六種常用函數(shù)，分別是：這六種常用函數(shù)，分別是： 平均值（平均值（aver