EXCEL2007实战技巧 公式与函数 ppt课件

Excel实战技巧精粹 公式与函数 申明 本教程主要针对Excel中 高级用户 内容主要涉及EXCEL公式与函数的基本使用方法及技巧 本教程对应使用office2007版本李宗尧2013年6月 Content 目录 1 公式和函数基础 1 1认识公式1 2公式中的运算符1 3公式中的常量1 4单元格引用1 5函数1 6名称 1 1认识公式 1 1 1公式的概念1 1 2公式的组成要素 表1 1公式的组成要素 1 1认识公式 1 1 3公式的输入 编辑与删除 3种方法进入单元格编辑状态选中公式所在单元格 并按下键 双击公式所在单元格 选中公式所在单元格 单击列标上方的编辑栏 使用键与键可清除单元格中的内容 1 1认识公式 1 1 4公式的复制与填充 5种方法实现公式的复制与填充方法一 拖拽填充 方法二 双击填充柄 方法三 快捷键填充 选中需要填充的区域 按 方法四 选择性粘贴 方法五 多单元格同时输入 示例 1 2公式中的运算符 1 2 1认识运算符 表1 2公式中的运算符 1 2公式中的运算符 1 2 2数据比较的原则 注意 数字与数值是两个不同的概念 示例 表1 3EXCEL中的数据类型 1 2公式中的运算符 1 2 3运算符的优先顺序 表1 4运算符的优先级优先级次 1 3公式中的常量 1 3 1常量参数 公式中的5种常量数值常量 如 5 8 3 日期常量 如 DATEDIF 2012 1 1 NOW m 文本常量 如 国际 复合 逻辑值常量 如 VLOOKUP 张三丰 A B 2 FALSE 错误值常量 如 COUNTIF A A DIV 0 注意 逻辑值与文本型数字在运算中会被自动转换为数值 1 4单元格引用 1 4 1A1引用样式和R1C1引用样式 A1引用样式在默认情况下 Excel使用A1引用样式 即使用字母A XFD表示列标 使用数字1 1048576表示行号 R1C1引用样式单击 Office 按钮 Excel选项 公式 选项卡 勾选 R1C1引用样式 如下图所示 1 4单元格引用 1 4 2相对引用 绝对引用和混合引用 相对引用从属单元格与引用单元格的相对位置不变 A1绝对引用公式所引用的单元格绝对位置不变 A 1混合引用所引用单元格的行或列方向之一的绝对位置不变 而另一方向位置发生变化 示例 1 5函数 1 5 1函数的概念1 5 2函数的结构 注意 当使用函数作为另一个函数的参数时 成为函数的嵌套 1 5函数 1 5 3可选参数与必需参数 表1 5常用函数省略可选参数情况 1 5函数 1 5 4常用函数的分类 根据函数的功能和应用领域 内置函数可分为11个类别 根据来源的不同 Excel函数可分为内4类 内置函数 扩展函数 自定义函数 宏表函数 1 5函数 1 5 5函数的输入和编辑 1 5函数 1 5 6使用公式的常见问题 表1 6常见错误值及其含义 1 6名称 1 6 1名称的概念1 6 2为什么要使用名称 增强公式的可读性 方便公式的统一修改 代替需要重复使用的公式 以简化公式 代替单元格区域存储常量数据 解决数据有效性和条件格式中无法使用常量数组 交叉引用 跨表引用等问题 1 6名称 1 6 3名称命名的限制 不能以数字开头 不能以R C r c作为名称名 不能使用除下划线 点号 和反斜线以外的其它符号 字符不能超过255个 字母不区分大小写 1 6名称 1 6 4定义名称的方法 方法一 使用名称管理器定义名称 方法一 使用名称框快速创建名称 1 6名称 1 6 5定义名称的对象 示例 文本处理 1合并与比较文本 2大小写 全半角转换 3字符与编码转换 4提取与查找字符 5替换与清理字符 1合并与比较文本 2 1 1区分文本型数字与数值 文本值默认左对齐数值默认右对齐逻辑值与错误值默认居中日期与时间默认右对齐 数据默认对齐方式 文本形式数字与数值导致查询出错 示例 1合并与比较文本 2 1 2合并文本 使用文本合并运算符 使用CONCATENATE函数使用PHONETIC函数 示例 1合并与比较文本 2 1 3文本值的大小比较 示例 使用比较运算符 使用EXACT函数 注意 用比较运算符对比文本时具有以下特性区分全角与半角字符 区分文本型数字与数值 文本始终大于数值 不区分字母大小写 2大小写 半全角转换 2 2 1大小字母转换 LOWER函数 将所有字母转换为小写字母 UPPER函数 将所有字母转换为大写字母 PROPER函数 将所有单词转换为首字母大写的格式 示例 2大小写 半全角转换 2 2 2全角半角字符转换 ASC WIDECHAR 全角字符 又称双音节字符 占用2个标准字符位置 半角字符 又称单音节字符 占用1个标准字符位置 所有的汉字都是全角字符 示例 3字符与编码转换 2 3 1字符与编码转换函数 CHAR CODE ASC 字符集B2312字符集BIG5字符集GB18030字符集Unicode字符集A 65 a 97 CHAR 64 COLUMN A1 CHAR 96 COLUMN A1 4提取与查找字符 2 4 1常用提取字符函数 LEFT函数 从左端提取制定数量字符 RIGHT函数 从右端提取制定数量字符 MID函数 从中间提取制定数量字符 示例 提取身份证信息 分离中英文对照 5替换与清理字符 2 5 1常用替换字符函数 SUBSTITUTE函数 将目标文本中的制定字符串替换为新的字符串 SUBSTITUDE text old text new text instance num REPLACE函数 根据起始位置和文本字符数来替换为指定的新字符串 REPLACE old text start num num chars new text 示例 1 如何将英文句子首字母转换为大写 2 如何清除字符串中间的空格 数学计算 3 1取余函数3 2数值取舍函数3 3四舍五入函数3 4随机函数 3 1取余函数 使用MOD函数判断奇偶性语法 MOD number divisor 返回两数相除的余数 例 MOD 69 7 返回结果为7 示例 3 2数值取舍函数 常用取舍函数汇总 3 3四舍五入示例 取舍函数应用示例 3 4随机函数 RAND函数与RANDBETWEEN函数RAND函数 产生0到1之间的随机数 RANDBETWEEN函数 依据给定上限和下限产生随机整数 抽取试题 安排座位 生成密码 生成模拟测试数据 随机函数用途 4 日期和时间计算 4 1年月日函数4 2认识DATEDIF函数4 3星期相关函数4 4时间的计算 4 1年月日函数 日期数据是一种数值的特殊表现形式 数值1代表1900 1 1 日期是一个整数值 时间则是小数部分 数据0 5对应时间序数12 00 00 常用日期函数如下 4 1年月日函数 4 1 1使用TODAY和NOW快速生成当前日期和时间 使用和组合键快速输入当前日期和时间 与使用函数生成的日期与时间有何异同 4 1 2使用DATE函数生成指定日期 DATE year month day 示例 4 1 3使用YEAR MONTH和DAY提取年份 月份和日期 4 2认识DATEDIF函数 功能描述 计算两个日期之间的天数 月数和年数语法 DATEDIF start date end date unit Unit参数含义如下 示例 4 3星期相关函数 WEEKDAY函数 返回指定日期的星期值WEEKNUM函数 返回指定日期属于全年的第几周 语法 WEEKDAY serial number return type return type说明 数字1 1至7对应星期天到数星期六 1可省略 数字2 1至7代表星期一到星期天 中国人的习惯 数字3 0至6代表星期一到星期天 示例 4 4时间的计算 TIME函数 用于按指定数字生成具体时间HOUR函数 用于提取时间系列值中的小时数MINUTE函数 用于提取时间系列值中的分钟数SECOND函数 用于提取时间系列值中的秒数 5 查找与引用 5 1基本的查找函数5 2常用的定位函数5 3行号和列号函数5 4特殊的查找函数5 5认识OFFSET函数 5 1基本的查找函数 VLOOKUP函数 搜索查找范围中的首列中满足条件的数据VLOOKUP lookup value table array col index num rang lookup HLOOKUP函数 搜索查找范围中的首行中满足条件的数据HLOOKUP lookup value table array row index num rang lookup 示例 注意 rang lookup参数为0或FALSE代表精确查找 rang lookup参数1或TRUE代表模糊匹配查找 查找值与查找范围的数据类型相匹配 5 2常用的定位函数 MATCH函数 确定查找值在查找范围中的位置序号MATCH lookup value lookup array match type 示例 判断数据是否被重复录入 5 3行号和列号函数 ROW函数 返回单元格引用的行号COLUMN函数 返回单元格引用的列号 示例 1 如何隔行设置背景颜色 2 与VLOOKUP函数共同设置数据查询 5 4特殊的查找函数 LOOKUP函数 在查找范围中查找用户指定的查找值LOOKUP lookup value lookup vector result vector 示例 5 5认识OFFSET函数 OFFSET函数 以指定的引用为参照 最终通过给定的偏移量得到一个新的单元格引用或单元格区域引用OFFSET reference rows cols height width 如何设置动态下拉菜单 示例 6 统计与求和 6 1基本的计数函数6 2条件统计类函数6 3条件求和类函数6 4极值应用函数6 5排名应用 6 1基本的计数函数 COUNT 对数值进行统计 忽略错误值 逻辑值及文本值COUNTA 统计非空单元格的个数COUNTBLANK 统计数据表中的空单元格个数 示例 6 2条件统计类函数 COUNTIF 按指定条件 一个 对区域内数值个数进行统计Countif range criteria COUNTIFS 按指定条件 多个 对区域内数值个数进行统计Countifs criteria range1 criteria1 示例 6 3条件求和类函数 SUMIF 按指定条件 一个 对区域内数值进行求和sumif range criteria sum range SUMIFS 按指定条件 一个 对区域内数值进行求和sumifs Countifs criteria range1 criteria1 示例