EXCEL实用技巧---EXCEL常用函数简介

1、EXCEL实用技巧,EXCEL常用函数简介,生技部 2009年11月25日,主要内容,SUM 函数,1,AVERAGE/AVERAGEA/TRIMMEAN函数,2,COUNT/COUNTA 计数函数,3,4,5,函数基礎知識,ROUND/TRUNC 函数,主要内容,IF函数（COUNTIF/SUMIF）,7,8,9,公式中常见的错误信息,10,FREQUNCY 陣列函数,MOD函数在美化表格中的应用,常用函数快捷方式,1、函数基础知识,1)、什么是Excel函数 Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。 2)、函数的结构：函数以等号 (=) 开始，后面紧跟函数名称和

2、左括号，然后以逗号分隔输入参数，最后是右括号。结构如下图所示：,函数的语法为:函数名(参数,参数,）,其中括号是不可少的,1、函数基础知识,引用运算符：冒号、空格、逗号，感叹号 冒号(:)， 是区域运算符 逗号(,)， 是合并运算符 空格, 是交叉运算符,取引用区域的公共部分. 感叹号(!)， 是三维引用运算符,可以引用另一张工作表中的数据. 起其表示形式为:工作表名!单元格引用区域.,1、函数基础知识,AVERAGE算术平均值 COUNT 数值型数据的个数 MAX 最大值 MIN 最小值 LEFT 从左边截取字符 RIGHT 从右边截取字符 MID 从字符串截取字符 INT 整数值 SUM

3、求和 ROUND 按指定位数四舍五入某数字 DATE特定日期的序列数 YEAR特定年份的序列数 TIME 特定时间的序列数 NOW 当前时间的序列数 IF（X,Z1,X2）当X为TRUE时，函数取值Z1，否则取值Z2。,统计 函数,文字 函数,数学、 三角 函数,日期 与时间 函数,逻辑 函数,1）相对引用：将公式复制到新位置时， Excel将相对于新位置修改公式中单元格的引用，I3=F3+G3-H3，如果该公式被复制到I6单元，则公式自动变为F6+G6-H6这就是地址的相对引用，即公式在复制时单元格地址随复制而改变。 2）绝对引用：有时我们在复制公式时，不希望公式中的地址随复制而改变，如：I

4、列每个单元格的公式均为F3+G3-H3，这时就要用到绝对地址引用。绝对引用就是公式在复制时不改变单元格地址。在单元格字母和数字前都加上一个“$”符号就将地址变成绝对地址。如：要实现上述操作应把公式修改为： $F $ 3+ $ G $ 3- $ H $ 3 3）混合引用：在公式地址中即有相对引用又有绝对引用称为混合引用，如：A$1或$A1;当公式不是被复制到一行（列）而是一个区域时常使用混合引用 ，例如：,1、函数基础知识(地址的引用方法),1、函数基础知识(嵌套函数),什么是嵌套函数：就是一个函数的参数本身也是一个函数。 如下图中所示的公式使用了嵌套的 AVERAGE 函数，并将结果与 50

5、相比较。这个公式的含义是：如果单元格F2到F5的平均值大于50，则求F2到F5的和，否则显示数值0。 嵌套函数注意事项： 1、在Excel中，一个公式最多可以嵌套7层函数。 2、嵌套函数是一个难度性较大的知识点，我们要清楚函数的意义及结构，并且要熟悉其使用方法，括号必须成对出现。,2、SUM 函数,行或列求和 H3单元格 =SUM(B3:G3) B11单元格 =SUM(B3:B10),2、SUM 函数,区域求和 H11单元格 =SUM(B3:G10),2、SUM 函数,鼠标拖拉法 选中存放求和结果的单元格，点击工具栏中的“”（自动求和）按钮 用鼠标拖过需要求和的所有单元格，然后回车即可 注：如

6、果需要求和的单元格是不连续的，则可按住Ctrl键分别拖过它们。,3、AVERAGE / AVERAGEA /TRIMMEAN函数,AVERAGE函数 用途：求算数平均数 使用方法：同SUM函数相同，只需把刚才所有“SUM”的部分替换成“AVERAGE” =AVERAGE(B3:G3) =AVERAGE(B3:B10) =AVERAGE(B3:G10),3、AVERAGE/ AVERAGEA /TRIMMEAN函数,AVERAGEA 函数,3、AVERAGE/ AVERAGEA /TRIMMEAN 函数,TRIMMEAN函数 用途：求数据集的内部平均值（即可以按照要求去掉所有数据中最高的和最低的

7、一些数据，然后求平均值） 使用方法：TRIMMEAN（array,percent) 例,需要求内部平均值的所有数据区域,需要去除的数据占全部数据的比例,4、COUNT/COUNTA函数,用途：计数 COUNT统计所选中的单元格区域中数字的个数 COUNTA统计所选中的单元格区域中非空单元格的个数（包括数字、文字等） 例:,5、ROUND/TRUNC函数,功能： ROUND函数对小数按照位数要求四舍五入 （亦可通过设置“单元格格式”实现同样功能） ROUND(number,num_digits) number就是将要进行四舍五入的数字 num_digits则是需要保留小数点后几位 TRUNC函数

8、对小数进行取整 （同INT函数功能） TRUNC(number) number就是需要进行取整的数字,5、ROUND/TRUNC函数,使用方法： B2单元格 =ROUND(A2, 2) 代表保留的小數位數 C2单元格 =ROUND(A2, 0) 注: 如果位数0,则舍入到指定的小数位;如果位数=0,则舍入到整数.如果位数0,则在小数点左侧进入舍入. D2单元格 = TRUNC(A2) 或 =INT(A2) (对小数进行取整),6、IF函数（COUNTIF/SUMIF）,例， 在B12单元格中输入： =IF(B11=60,”合格”,”不合格”),逻辑判断式,当逻辑判断式为真时，返回的值,当逻辑判

9、断式为假时，返回的值,6、IF函数（COUNTIF/SUMIF）,COUNTIF函数 功能：统计符合条件要求的单元格的数量 例， 在B5单元格中输入=countif(B2:B4,”20”),可以求出在B2到B4这三个单元格中大于20的数字有几个,6、IF函数（COUNTIF/SUMIF）,SUMIF函数 功能：统计符合条件要求的单元格中数字之和 例， * 在B5单元格中输入=sumif(B2:B4,”20”),可以求出在B2到B4这三个单元格中大于20的数字之和 。(即:加總符合某特定搜尋準則的儲存格 ) * IF函数可以嵌套使用,最多为7层,如：=IF(C2=90,“优 ,IF(C2=75,

10、良, IF(C2=60,中,差),FREQUENCY函数 功能：計算某一個範圍內的值出現的次數，並傳回一個垂 直數值陣列。 例如,用 FREQUENCY 來計算某些範圍內的考試成績各有幾個人。 由於 FREQUENCY 回陣列，因此必須輸入為陣列公式。 附註:範例的公式必須輸入為一個陣列公式。在複製範例到另一個空 白工作表之後，選取範圍 A13:A16 儲存格開始公式。按一下 F2，然後 按下 CTRL+SHIFT+ENTER。如果公式不是一個陣列公式，就只有單一結果 1。,7、 FREQUENCY 陣列函数,9、MOD函数在美化表格中的应用,功能：设置间隔式底纹 例，,如何实现？,8、MOD函数在美化表格中的应用,选中需要设置间隔式底纹的区域 “格式条件格式”，弹出的“条件格式”对话框（如图） 在左侧的下拉菜单中选择“公式”，并在右侧区域输入=MOD(ROW()，2),然后点击“格式”按钮,8、MOD函数在美化表格中的应用,在弹出的“单元格格式”对话框中，选择“图案”选项卡，然后选择需要的颜色，“确定” 点击“条件格式”对话框中的“确定”按钮,9、常用函数快捷方式,在excel表格的右下角有一个常用函数快捷方式的区域（如同所示），可以右键点击