excel常用公式大全.ppt

EXCEL公式和函数 公式的使用和公式的调整常用函数工作表的格式化 公式 Excel表格中不仅可以输入数据 也可以使用公式对工作表中的数据进行各种数值计算和统计分析等Excel的公式是由运算符号 和参与计算的运算数组成的表达式 公式中的运算符 算术运算符 字符运算符 连接符比较运算符 逻辑运算符 andornot以函数形式出现优先级顺序 算术运算符 字符运算符 比较运算符 逻辑函数符 使用括号可确定运算顺序 公式中的数据类型 输入公式要注意公式中的运算对象可以包括 数值和字符 单元格地址 区域 函数等 如 A2 B2 C2不要随意包含空格公式中的字符要用半角引号括起来公式中运算符两边的数据类型要相同如 ab 25出错 VALUE 公式的输入 公式的基本特性 公式的输入是以 开始公式计算结果显示在单元格中公式本身显示在编辑栏中 公式示例 1 2 6 数值计算 A1 B2 引用单元格地址 SUM A1 C4 公式计算 IF and A1 A2 B1 B2 100 A1 A2 100 B1 B2 函数计算 ABC XYZ 字符计算 结果 ABCXYZ 公式中常用单元格的引用来代替单元格的具体数据 当公式中被引用单元格的数据变化时 公式的计算结果会随之变化 公式的复制和移动 公式的复制 使用填充柄菜单 编辑 复制 粘贴 复制公式时 公式会作相对调整 相对引用和绝对引用 相对引用是针对当前单元格的引用 是随当前单元格变化而变化的绝对引用是针对起始单元格的引用 是不随当前单元格的变化而变化的 公式的调整 相对地址引用 在公式复制时将自动调整调整原则是 新行地址 原来行地址 行偏移量新列地址 原来列地址 列偏移量例如 将D2单元中的公式 A2 B2 C2复制到D4后 D4中的公式 A4 B4 C4 公式的调整 绝对地址引用在公式复制时不变 例如 C3单元的公式 B 2 B 3 B 4复制到D5中 D5单元的公式 B 2 B 3 B 4混合地址在公式复制时绝对地址不变 相对地址按规则调整 公式的调整 公式移动相对和绝对地址都不调整C3单元格的公式 B2 B3 B4移动到D5中 D3单元格的公式 B2 B3 B4被公式引用的单元格移动 公式跟踪单元格 值不变 选择性粘贴 菜单 编辑 复制 菜单 编辑 选择性粘贴 公式 数值 格式的粘贴运算 转置的使用 Sumif函数 根据指定条件对若干单元格求和 SUMIF range criteria sum range Range为用于条件判断的单元格区域 Criteria为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为32 32 32 或 apples Sum range为需要求和的实际单元格 只有当Range中的相应单元格满足条件时 才对sum range中的单元格求和 如果省略sum range 则直接对Range中的单元格求和 例如 假设A1 A4单元格中的内容分别为四种产品的属性值 100 000 200 000 300 000 400 000 B1 B4的内容为每个属性值所对应的销售佣金 7 000 14 000 21 000 28 000 那么就可以运行公式 SUMIF A1 A4 160 000 B1 B4 最后的求和结果将是 63 000 函数 COUNT用途 它可以统计数组或单元格区域中含有数字的单元格个数 语法 COUNT value1 value2 参数 value1 value2 是包含或引用各种类型数据的参数 1 30个 其中只有数字类型的数据才能被统计 实例 如果A1 90 A2 人数 A3 A4 54 A5 36 则公式 COUNT A1 A5 返回3 COUNTA用途 返回参数组中非空值的数目 语法 COUNTA value1 value2 说明 参数可以是任何类型 它们包括空格但不包括空白单元格 如果不需要统计逻辑值 文字或错误值 则应该使用COUNT函数 实例 如果A1 6 28 A2 3 74 A3 我们 其余单元格为空 则公式 COUNTA A1 A7 的计算结果等于3 COUNTBLANK用途 计算某个单元格区域中空白单元格的数目 语法 COUNTBLANK range 参数 Range为需要计算其中空白单元格数目的区域 实例 如果A1 88 A2 55 A3 A4 72 A5 则公式 COUNTBLANK A1 A5 返回2 COUNTIF用途 计算区域中满足给定条件的单元格的个数 语法 COUNTIF range criteria 参数 Range为需要计算其中满足条件的单元格数目的单元格区域 Criteria为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 If函数 1 IF函数的语法结构IF函数的语法结构 IF 条件 结果1 结果2 2 IF函数的功能对满足条件的数据进行处理 条件满足则输出结果1 不满足则输出结果2 可以省略结果1或结果2 但不能同时省略 3 条件表达式把两个表达式用关系运算符 主要有 b1 c1 50 1 2 函数式中 条件表达式是a1 b1 50b1 c1 50 4 执行过程下面以IF a1 b1 50b1 c1 50 1 1 函数式为例来说明IF函数的执行过程 先计算条件表达式a1 b1 50b1 c1 50 如果表达式成立 值为TRUE 并在函数所在单元格中显示 1 如果表达式不成立 值为FALSE 并在函数所在单元格中显示 2 IF E2 85 优 IF E2 75 良 IF E2 60 及格 不及格 函数从左向右执行 首先计算E2 85 如果该表达式成立 则显示 优 如果不成立就继续计算E2 75 如果该表达式成立 则显示 良 否则继续计算E2 60 如果该表达式成立 则显示 及格 否则显示 不及格 数据排序 使用工具栏上的排序按钮进行排序单击数据清单需要排序的列中任一单元格 单击 常用工具栏 上的升序或降序按钮 使用 数据 菜单中的 排序 命令进行排序1 排序单击数据清单任一单元格 单击 数据 排序 选择主要关键字 次要关键字 选择升序还是降序 确定 数据筛选 任选一个单元格数据 筛选 自动筛选 分类汇总 分类汇总 就是把某一个关键字段的相同数据汇总在一起 使用分类汇总命令之前 首先要对关键字段进行排序 使关键字段相同的数据记录可以连续访问 操作步骤 先对分类字段进行排序 打开 数据 下拉菜单 再单击 分类汇总 命令 在 分类汇总 对话框中选择分类的字段 与排序字段相同 汇总的方式 汇总的项 统计的数值字段 清除分类汇总 编辑 里的 撤消分类汇总 命令 若已按了1 2或3分级显示 则需多次按 撤消显示分级层次 至到出现 撤消分类汇总 为止 方法二 在 分类汇总 对话框中按 全部删除 按钮 多工作表或工作簿的操作 同一工作簿中的某一个公式中引用不同工作表中的数据 要引用的单元格地址前加表名字和感叹号 如 sheet3 E3 sheet3 F3一个公式中引用不同工作簿中的数据 要在引用的单元格地址前加工作簿的名字 并用方括号括起来如 cjb xls sheet3 E3 cjb xls sheet3 F3 工作表的格式化 单元格格式的设置 选定单元格或区域选择菜单 格式 单元格 设置数字格式 两位小数 千位分隔字体 对齐方式 边框底纹等 EXCEL表格要加边框线 打印的时候才有边线合并及居中选择菜单 格式 条件格式 设置带条件的格式 如 小于60分的成绩显示红色粗体 背景为兰色使用 格式刷 或 选择性粘贴 复制格式 工作表的格式化 调整行高和列宽鼠标拖拽操作菜单操作 格式 行 列 隐藏行或列 格式 行 列 隐藏 工作表的格式化 工作表的整体显示工作表的背景设置菜单 格式 工作表 背景 工作表的整体设置菜单 工具