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 则舍入到整数 如果位数 0 则在小数点左侧进入舍入 D2单元格 TRUNC A2 或 INT A2 对小数进行取整 6 IF函数 COUNTIF SUMIF 例 在B12单元格中输入 IF B11 60 合格 不合格 逻辑判断式 当逻辑判断式为真时 返回的值 当逻辑判断式为假时 返回的值 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 良 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表格的右下角有一个常用函数快捷方式的区域 如同所示 可以右键点