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 2 count/countacount/counta 計數(shù)計數(shù)函數(shù)函數(shù) 3 3 4 4 5 5 函數(shù)函數(shù)基礎(chǔ)知識基礎(chǔ)知識 round/truncround/trunc 函數(shù)函數(shù) 主要內(nèi)容主要內(nèi)容 ifif函數(shù)（函數(shù)（countif/sumifcountif/sumif） 7 8 9 公式中

2、常見的錯誤信息公式中常見的錯誤信息 10 frequncy 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ū)域運

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

4、位數(shù)四舍五入某數(shù)字 date特定日期的序列數(shù) year特定年份的序列數(shù) time 特定時間的序列數(shù) now 當(dāng)前時間的序列數(shù) if（x,z1,x2）當(dāng)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)閱卧瑒t公式自動變?yōu)閒6+g6-h6這就是這就是 地址的相對引用，即公式在復(fù)制時單

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

6、把公式修改為： $f $ 3+ $ g $ 3- $ h $ 3 3）混合引用：）混合引用：在公式地址中即有相對引用又有絕對引用稱為混合引用，如：在公式地址中即有相對引用又有絕對引用稱為混合引用，如：a$1或或 $a1;當(dāng)公式不是被復(fù)制到一行（列）而是一個區(qū)域時常使用混合引用當(dāng)公式不是被復(fù)制到一行（列）而是一個區(qū)域時常使用混合引用 ，例如：，例如： 地址的引用方法地址的引用方法) ) ) ) 就是一個函數(shù)的參數(shù)本身也是一個函數(shù)。就是一個函數(shù)的參數(shù)本身也是一個函數(shù)。 如下圖中所示的公式使用了嵌套的如下圖中所示的公式使用了嵌套的 average average 函數(shù)，并將結(jié)函數(shù)，并將結(jié) 果與果與

7、50 50 相比較。這個公式的含義是：如果單元格相比較。這個公式的含義是：如果單元格f2f2到到f5f5的平均值大的平均值大 于于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單

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

9、ageaverage / average/ averagea a / /trimmeantrimmean函數(shù)函數(shù) averageaverage函數(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

10、函數(shù)函數(shù) 3 3、average/ averageaverage/ averagea a / /trimmeantrimmean 函數(shù)函數(shù) 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ù)占 全

11、部數(shù)據(jù)全部數(shù)據(jù) 的比例的比例 4 4、count/countacount/counta函數(shù)函數(shù) 用途：計數(shù)用途：計數(shù) countcount統(tǒng)計所選中的單元格區(qū)域中數(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)同樣

12、功能） round(number,num_digits) number就是將要進行四舍五入的數(shù)字就是將要進行四舍五入的數(shù)字 num_digits則是需要保留小數(shù)點后幾位則是需要保留小數(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單元格單元格

13、 =round(a2,=round(a2, 0 0) ) 注注: 如果位數(shù)如果位數(shù)0,則舍入到指定的小數(shù)位則舍入到指定的小數(shù)位;如果位數(shù)如果位數(shù)=0,則舍入到整數(shù)則舍入到整數(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單元格中輸入單元格中輸入=sumi

14、f=sumif(b2:b4,”20”),(b2:b4,”20”),可以求出在可以求出在b2b2到到b4b4這這 三個單元格中大于三個單元格中大于2020的數(shù)字之和的數(shù)字之和 。( (即即: :加總符合某特定搜尋準(zhǔn)加總符合某特定搜尋準(zhǔn) 則的儲存格則的儲存格 ) * * 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ù) 功能：功能：計算某一個範(fàn)圍內(nèi)的值出現(xiàn)的次數(shù)，並傳回一個

15、垂計算某一個範(fàn)圍內(nèi)的值出現(xiàn)的次數(shù)，並傳回一個垂 直數(shù)值陣列。直數(shù)值陣列。 例如例如, ,用用 frequency frequency 來計算某些範(fàn)圍內(nèi)的考試成績各有幾個人。來計算某些範(fàn)圍內(nèi)的考試成績各有幾個人。 由於由於 frequency frequency 回陣列，因此必須輸入為陣列公式?；仃嚵?，因此必須輸入為陣列公式。 附註附註: : 範(fàn)例的公式必須輸入為一個陣列公式。在複製範(fàn)例到另一個空範(fàn)例的公式必須輸入為一個陣列公式。在複製範(fàn)例到另一個空 白工作表之後，選取範(fàn)圍白工作表之後，選取範(fàn)圍 a13:a16 a13:a16 儲存格開始公式。按一下儲存格開始公式。按一下 f2f2，然後，然後

16、按下按下 ctrl+shift+enterctrl+shift+enter。如果公式不是一個陣列公式，就只有單一結(jié)果。如果公式不是一個陣列公式，就只有單一結(jié)果 1 1。 7 7、 frequency 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è)的下拉菜單中選擇在

17、左側(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ù)，分別是： 平均值（平均值（average） 計