EXCEL实用技巧EXCEL常用函数简介.ppt

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)、函数的结构：函数以等号(=)开始，后面紧跟函数名称和左括号，然后以逗号分隔输入参数，最后是右括号。结构如下图所示：,函数的语法为:函数名(参数,参数,）,其中括号是不可少的,1、函数基础知识,引用运算符：冒号、空格、逗号，感叹号冒号(:)，是区域运算符逗号(,)，是合并运算符空格,是交叉运算符,取引用区域的公共部分.感叹号(!)，是三维引用运算符,可以引用另一张工作表中的数据.起其表示形式为:工作表名!单元格引用区域.,1、函数基础知识,AVERAGE算术平均值COUNT数值型数据的个数MAX最大值MIN最小值LEFT从左边截取字符RIGHT从右边截取字符MID从字符串截取字符INT整数值SUM求和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列每个单元格的公式均为F3+G3-H3，这时就要用到绝对地址引用。绝对引用就是公式在复制时不改变单元格地址。在单元格字母和数字前都加上一个“$”符号就将地址变成绝对地址。如：要实现上述操作应把公式修改为：$F$3+$G$3-$H$33）混合引用：在公式地址中即有相对引用又有绝对引用称为混合引用，如：A$1或$A1;当公式不是被复制到一行（列）而是一个区域时常使用混合引用，例如：,1、函数基础知识(地址的引用方法),1、函数基础知识(嵌套函数),什么是嵌套函数：就是一个函数的参数本身也是一个函数。如下图中所示的公式使用了嵌套的AVERAGE函数，并将结果与50相比较。这个公式的含义是：如果单元格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函数,鼠标拖拉法选中存放求和结果的单元格，点击工具栏中的“”（自动求和）按钮用鼠标拖过需要求和的所有单元格，然后回车即可注：如果需要求和的单元格是不连续的，则可按住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函数用途：求数据集的内部平均值（即可以按照要求去掉所有数据中最高的和最低的一些数据，然后求平均值）使用方法：TRIMMEAN（array,percent)例,需要求内部平均值的所有数据区域,需要去除的数据占全部数据的比例,4、COUNT/COUNTA函数,用途：计数COUNT统计所选中的单元格区域中数字的个数COUNTA统计所选中的单元格区域中非空单元格的个数（包括数字、文字等）例:,5、ROUND/TRUNC函数,功能：ROUND函数对小数按照位数要求四舍五入（亦可通过设置“单元格格式”实现同样功能）ROUND(number,num_digits)number就是将要进行四舍五入的数字num_digits则是需要保留小数点后几位TRUNC函数对小数进行取整（同INT函数功能）TRUNC(number)number就是需要进行取整的数字,5、ROUND/TRUNC函数,使用方法：B2单元格=ROUND(A2,2)代表保留的小數位數C2单元格=ROUND(A2,0)注:如果位数0,则舍入到指定的小数位;如果位数=0,则舍入到整数.如果位数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,良,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表格的右下角有一个常用函数快捷方式的区域（如同所示），可以右键点击该区域，使用六种常