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

1、EXCELEXCEL实用技巧实用技巧 EXCELEXCEL常用函数简介常用函数简介生技部生技部2002009 9年年1111月月2525日日主要内容主要内容 SUMSUM 函数函数1 1 AVERAGE/AVERAGEAVERAGE/AVERAGEA/A/TRIMMEANTRIMMEAN函数函数2 2COUNT/COUNTACOUNT/COUNTA 计数计数函数函数3 34 45 5 函数函数基礎知識基礎知識ROUND/TRUNCROUND/TRUNC 函数函数主要内容主要内容IFIF函数（函数（COUNTIF/SUMIFCOUNTIF/SUMIF）789公式中常见的错误信息公式中常见的错误信

2、息10FREQUNCY FREQUNCY 陣列陣列函数函数MODMOD函数在美化表格中的应用函数在美化表格中的应用6常用函数快捷方式常用函数快捷方式1 1、函数基础知识、函数基础知识 1 1 1 1)、什么是、什么是、什么是、什么是ExcelExcelExcelExcel函数函数函数函数 Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。2 2 2 2)、函数的结构：、函数的结构：、函数的结构：、函数的结构：函数以等号(=)开始，后面紧跟函数名称和左括号，然后以逗号分隔输入参数，最后是右括号。结构如下图所示：函数的语法为函数的语法为:函数名函数名(参数参数,参数参数,）其中括

3、号是不其中括号是不可少的可少的1、函数基础知识引用运算符引用运算符：冒号、空格、逗号，感叹号冒号、空格、逗号，感叹号冒号冒号(:)(:)，是区域运算符是区域运算符逗号逗号(,)(,)，是合并运算符是合并运算符空格空格,是交叉运算符是交叉运算符,取引用区域的公共部分取引用区域的公共部分.感叹号感叹号(!)(!)，是三维引用运算符是三维引用运算符,可以引用另一张工作表中的数据可以引用另一张工作表中的数据.起其表示形式为起其表示形式为:工作表名工作表名!单元格引用区域单元格引用区域.1 1、函数基础知识、函数基础知识 AVERAGE算术平均值 COUNT 数值型数据的个数 MAX 最大值 MIN 最

4、小值 LEFT 从左边截取字符 RIGHT 从右边截取字符 MID 从字符串截取字符 INT 整数值 SUM 求和 ROUND 按指定位数四舍五入某数字 DATE特定日期的序列数 YEAR特定年份的序列数 TIME 特定时间的序列数 NOW 当前时间的序列数 IF（X,Z1,X2）当X为TRUE时，函数取值Z1，否则取值Z2。统计函数文字函数数学、三角函数日期与时间函数逻辑函数1）相对引用：）相对引用：将公式复制到新位置时，将公式复制到新位置时，Excel将相对于新位置修改公式中单元格的引将相对于新位置修改公式中单元格的引用，用，I3=F3+G3-H3，如果该公式被复制到，如果该公式被复制到I

5、6单元，则公式自动变为单元，则公式自动变为F6+G6-H6这就是地这就是地址的相对引用，即公式在复制时单元格地址随复制而改变。址的相对引用，即公式在复制时单元格地址随复制而改变。2）绝对引用：）绝对引用：有时我们在复制公式时，不希望公式中的地址随复制而改变，如：有时我们在复制公式时，不希望公式中的地址随复制而改变，如：I列列每个单元格的公式均为每个单元格的公式均为F3+G3-H3，这时就要用到绝对地址引用。绝对引用就是公式，这时就要用到绝对地址引用。绝对引用就是公式在复制时不改变单元格地址。在单元格字母和数字前都加上一个在复制时不改变单元格地址。在单元格字母和数字前都加上一个“$”符号就将地址

6、变符号就将地址变成绝对地址。如：要实现上述操作应把公式修改为：成绝对地址。如：要实现上述操作应把公式修改为：$F$3+$G$3-$H$33）混合引用：）混合引用：在公式地址中即有相对引用又有绝对引用称为混合引用，如：在公式地址中即有相对引用又有绝对引用称为混合引用，如：A$1或或$A1;当公式不是被复制到一行（列）而是一个区域时常使用混合引用当公式不是被复制到一行（列）而是一个区域时常使用混合引用，例如：，例如：1 1、函数基础知识、函数基础知识(地址的引用方法地址的引用方法)1 1、函数基础知识、函数基础知识(嵌套函数嵌套函数嵌套函数嵌套函数)什么是嵌套函数：什么是嵌套函数：什么是嵌套函数：

7、什么是嵌套函数：就是一个函数的参数本身也是一个函数。就是一个函数的参数本身也是一个函数。如下图中所示的公式使用了嵌套的如下图中所示的公式使用了嵌套的 AVERAGE AVERAGE 函数，并将结函数，并将结果与果与 50 50 相比较。这个公式的含义是：如果单元格相比较。这个公式的含义是：如果单元格F2F2到到F5F5的平均值大的平均值大于于5050，则求，则求F2F2到到F5F5的和，否则显示数值的和，否则显示数值0 0。嵌套函数注意事项：嵌套函数注意事项：嵌套函数注意事项：嵌套函数注意事项：1 1、在、在ExcelExcel中，一个公式最多可以嵌套中，一个公式最多可以嵌套7 7层函数。层函

8、数。2 2、嵌套函数是一个难度性较大的知识点，我们要清楚函数的意义及、嵌套函数是一个难度性较大的知识点，我们要清楚函数的意义及结构，并且要熟悉其使用方法，括号必须成对出现。结构，并且要熟悉其使用方法，括号必须成对出现。2 2、SUMSUM 函数函数行或列求和行或列求和 H3单元格单元格=SUM(B3:G3)B11单元格单元格=SUM(B3:B10)2 2、SUMSUM 函数函数区域求和区域求和 H11单元格单元格=SUM(B3:G10)2 2、SUMSUM 函数函数鼠标拖拉法鼠标拖拉法选中存放求和结果的单元格，点击工具栏选中存放求和结果的单元格，点击工具栏中的中的“”（自动求和）按（自动求和）

9、按钮钮用鼠标拖过需要求和的所有单元格，然后用鼠标拖过需要求和的所有单元格，然后回车即可回车即可注：如果需要求和的单元格是不连续的，注：如果需要求和的单元格是不连续的，则可按住则可按住CtrlCtrl键分别拖过它们。键分别拖过它们。3 3、AVERAGEAVERAGE /AVERAGE/AVERAGEA A /TRIMMEANTRIMMEAN函数函数AVERAGEAVERAGE函数函数用途：求算数平均数用途：求算数平均数使用方法：同使用方法：同SUMSUM函数相同，只需把刚才所有函数相同，只需把刚才所有“SUMSUM”的部分替换成的部分替换成“AVERAGEAVERAGE”=AVERAGE(B3

10、:G3)=AVERAGE(B3:B10)=AVERAGE(B3:G10)3 3、AVERAGE/AVERAGE/AVERAGEAVERAGEA A /TRIMMEANTRIMMEAN函数函数AVERAGEA 函数函数3 3、AVERAGE/AVERAGEAVERAGE/AVERAGEA A /TRIMMEANTRIMMEAN 函数函数TRIMMEANTRIMMEAN函数函数用途：求数据集的内部平均值（即可以按照要用途：求数据集的内部平均值（即可以按照要求去掉所有数据中最高的和最低的一些数据，求去掉所有数据中最高的和最低的一些数据，然后求平均值）然后求平均值）使用方法：使用方法：TRIMMEAN

11、TRIMMEAN（array,percent)array,percent)例例需要求内需要求内部平均值部平均值的所有数的所有数据区域据区域需要去除需要去除的数据占的数据占全部数据全部数据的比例的比例4 4、COUNT/COUNTACOUNT/COUNTA函数函数用途：计数用途：计数COUNTCOUNT统计所选中的单元格区域中数字的个数统计所选中的单元格区域中数字的个数COUNTACOUNTA统计所选中的单元格区域中非空单元统计所选中的单元格区域中非空单元格的个数（包括数字、文字等）格的个数（包括数字、文字等）例例:5 5、ROUND/TRUNCROUND/TRUNC函数函数功能：功能：ROUN

12、DROUND函数函数对小数按照位数要求四舍五入对小数按照位数要求四舍五入 （亦可通过设置（亦可通过设置“单元格格式单元格格式”实现同样功能）实现同样功能）ROUND(number,num_digits)number就是将要进行四舍五入的数字就是将要进行四舍五入的数字num_digits则是需要保留小数点后几位则是需要保留小数点后几位TRUNCTRUNC函数函数对小数进行取整对小数进行取整 （同（同INTINT函数功能）函数功能）TRUNC(number)number就是需要进行取整的数字就是需要进行取整的数字5 5、ROUND/TRUNCROUND/TRUNC函数函数使用方法：使用方法：B2B

13、2单元格单元格 =ROUND(A2,=ROUND(A2,2 2)代表保留的小數位數代表保留的小數位數 C2C2单元格单元格 =ROUND(A2,=ROUND(A2,0 0)注注:如果位数如果位数0,则舍入到指定的小数位则舍入到指定的小数位;如果位数如果位数=0,则舍入到整数则舍入到整数.如果位数如果位数2020”),),可以求出在可以求出在B2B2到到B4B4这三个单元格中大于这三个单元格中大于2020的数字有几个的数字有几个6 6、IFIF函数（函数（COUNTIF/SUMIFCOUNTIF/SUMIF）SUMIFSUMIF函数函数功能：统计符合条件要求的单元格中数字之和功能：统计符合条件要

14、求的单元格中数字之和例，例，*在在B5B5单元格中输入单元格中输入=sumif=sumif(B2:B4,(B2:B4,”2020”),),可以求出在可以求出在B2B2到到B4B4这这三个单元格中大于三个单元格中大于2020的数字之和的数字之和 。(即即:加總符合某特定搜尋準加總符合某特定搜尋準則的儲存格則的儲存格)*IFIF函数可以嵌套使用函数可以嵌套使用,最多为最多为7 7层层,如：如：=IF(C2=90,IF(C2=90,“优优 ,IF(C2=75,IF(C2=75,良良,IF(C2=60,IF(C2=60,中中,差差)FREQUENCYFREQUENCY函数函数功能：功能：計算某一個範圍

15、內的值出現的次數，並傳回一個垂計算某一個範圍內的值出現的次數，並傳回一個垂 直數值陣列。直數值陣列。例如例如,用用 FREQUENCY FREQUENCY 來計算某些範圍內的考試成績各有幾個人。來計算某些範圍內的考試成績各有幾個人。由於由於 FREQUENCY FREQUENCY 回陣列，因此必須輸入為陣列公式。回陣列，因此必須輸入為陣列公式。附註附註:範例的公式必須輸入為一個陣列公式。在複製範例到另一個範例的公式必須輸入為一個陣列公式。在複製範例到另一個空空白工作表之後，選取範圍白工作表之後，選取範圍 A13:A16 A13:A16 儲存格開始公式。按一下儲存格開始公式。按一下 F2F2，然

16、後，然後按下按下 CTRL+SHIFT+ENTERCTRL+SHIFT+ENTER。如果公式不是一個陣列公式，就只有單一結果。如果公式不是一個陣列公式，就只有單一結果 1 1。7 7、FREQUENCY FREQUENCY 陣列陣列函数函数9 9、MODMOD函数在美化表格中的应用函数在美化表格中的应用功能：设置间隔式底纹功能：设置间隔式底纹例，例，8 8、MODMOD函数在美化表格中的应用函数在美化表格中的应用选中需要设置间隔式底纹的区域选中需要设置间隔式底纹的区域“格式格式条件格式条件格式”，弹出的，弹出的“条件格式条件格式”对话对话框（如图）框（如图）在左侧的下拉菜单中选择在左侧的下拉菜

17、单中选择“公式公式”，并在右侧区，并在右侧区域输入域输入=MOD(ROW()，2),然后点击然后点击“格式格式”按按钮钮8 8、MODMOD函数在美化表格中的应用函数在美化表格中的应用在弹出的在弹出的“单元格格式单元格格式”对话框中，选择对话框中，选择“图案图案”选项卡，然后选择需要的颜色，选项卡，然后选择需要的颜色，“确定确定”点击点击“条件格式条件格式”对话框中的对话框中的“确定确定”按钮按钮9 9、常用函数快捷方式常用函数快捷方式在在excel表格的右下角有一个常用函数快捷方式的区域表格的右下角有一个常用函数快捷方式的区域（如同所示），可以右键点击该区域，使用六种常用函数（如同所示），可

18、以右键点击该区域，使用六种常用函数这六种常用函数，分别是：这六种常用函数，分别是：平均值（平均值（AVERAGE）计数（计数（COUNTA）计数值（计数值（COUNT）最大值（最大值（MAX）最小值（最小值（MIN）求和（求和（SUM）10、公式中常见的错误信息公式中常见的错误信息出错信息可能的原因DIV/0!公式被零除N/A没有可用的数值NAME?Excel不能识别公式中使用的名字NULL!指定的两个区域不相交NUM!数字有问题REF!公式引用了无效的单元格VALUE!参数或操作数的类型有错小小 结结1、要熟记每个常用函数的表达方式及意义，对于函数的多层嵌套，要分清嵌套的层次及顺序。2、巩固每个常用函数的意义及使用方法。