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函数在美化表格中的应用 6 常用函数快捷方式 1 1、函数基础知识、函数基础知识 1 1) )、什么是、什么是ExcelExcel函数函数 Excel函数即是预先定义，执行计算、分析等处理 数据任务的特殊公式。 2 2) )、函数的结构：、函数的结构： 函数以等号 (=) 开始，后面紧跟函数 名称和左括号，然后以逗号分隔输入参数，最后是右括号 。结构如下图所示： 函数的语法为:函数名(参数,参数,） 其中括号是不 可少的 1 1、函数基础知识、函数基础知识 引用运算符：冒号、空格、逗号，感叹号 冒号(:)， 是区域运算符 逗号(,)， 是合并运算符 空格, 是交叉运算符,取引用区域的公共部分. 感叹号(!)， 是三维引用运算符,可以引用另一张工作表中的数据. 起其表示形式为:工作表名!单元格引用区域. 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 $ 3 3）混合引用：在公式地址中即有相对引用又有绝对引用称为混合引用，如：A$1或 $A1;当公式不是被复制到一行（列）而是一个区域时常使用混合引用 ，例如： 1 1、函数基础知识、函数基础知识( (地址的引用方法) 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表格的右下角有一个常用函数快捷方式的区域（ 如同所示），可以右键点击该区域，使用六种常用函数 这六种常用函数，分别是： 平均值（AVERAGE） 计数（COUNTA） 计数值（COUNT） 最大值（MAX） 最小值（MIN） 求和（SUM） 10 、公式中常见的错误信息 出错信息可能的原因 DIV/0!公