Excel2007高级使用技巧.ppt

Excel2007提升培训班 Contents 一 数据录入和格式化工作表 1 Excel2007的数据录入技巧 2 自定义数字格式 3 Excel2007条件格式设置 4 数据有效性及案例 5 EXCEL多工作表链接 冻结与并排 6 EXCEL工作表的数据保护功能 1 1 数据录入的相关技巧 数据录入技巧灵活使用TAB ALT ENTER键录入分数 零 空格 分数录入文本格式数字 邮编 电话等 单引号 录入内容输入当前日期 时间 Ctrl 和Ctrl Shift 录入特殊字符 对号 平方 不连续区域的填充 Ctrl Enter填充柄录入 序列填充 插入批注 数据录入技巧引入动态数据 导入WEB资料导入外部数据大型工作表中单元格 区域 选择使用智能标记进行输入使用工作表组加快多工作表创建和数据输入速度数据朗读 7 高效的Excel快捷键 使用键盘快捷方式定位到所需位置 1 2 自动限定数据格式 案例分析 1 在数字后面自动添加单位 如 元 千克等 将文本字符括在双引号 内或在单个字符前面添加一个反斜杠 2 输入身份证的前几位 51102519 3 输入条形码前的某些重复数字 999999999 4 定义小数点对齐 分数对齐 5 自定义各种特殊日期格式mmmmd yyyy dddd December30 2008 Tuesday yyyy 年 m 月 d 日 aaaa2008年12月30日星期四 选择性粘贴格式刷 选择性粘贴公式数值格式批注转置粘贴链接格式刷 1 3 Excel2007条件格式设置 1 EXCEL表格的排版技巧 2 EXCEL条件格式 1 3 1 单元格格式设置 文字的对齐与旋转合并单元格使用工具栏上的快捷按钮格式 单元格 对齐 合并单元格自动换行格式 单元格 对齐 自动换行强制换行ALT ENTER文字旋转格式 单元格 对齐 文字方向设定边框和底纹使用工具栏上的 边框 底纹 快捷按钮格式 单元格 边框 图案 1 3 2 条件格式管理器设置 什么是条件格式当单元格的值满足预先设定的条件时 自动将单元格设置成预先设定的格式多条件格式的规则按从上到下的顺序判断条件并执行最多指定三个条件根据公式自动设置格式在条件格式中使用公式和函数实现隔行变色等的特殊格式掌握ROW和MOD函数的用法 mod row 2 案例简析 1 4 数据的有效性 提高数据录入准确性 有效性检查 什么是数据的有效性 数据有效性是一个包含帮助你在工作表中输入资料提示信息的工具 它有如下功能 1 给用户提供一个选择列表 2 限定输入内容的类型或大小 3 自定义设置数据有效性的设置提供下拉式菜单的输入设置输入提示设置出错警告自定义有效性条件 1 5 如何处理大型工作表 在Excel多个工作表间快速切换技巧一 用快捷键快速导航工作表Ctrl PageUp移到上一个工作表Ctrl PageDown移到下一个工作表技巧二 右键菜单准确定位工作表右击导航状态按钮选择所需工作表标签名冻结工作表窗口大型工作表数据的分组 分级显示命名单元格或单元格区域 以便能够快速返回到这些工作表区域组合工作表 以便同时在多张工作表中输入共用数据 并排比较数据两个工作簿将在同一个窗口中打开 当一个工作簿滚动时 Excel将使第二个工作簿自动滚动相应数量的单元格工作表间的数据链接创建引导型页面创建链接按钮创建超级链接及编辑超级链接设置工作表及行号列标的隐藏 同时比较两个工作簿可能很困难 1 5 如何处理大型工作表 1 6 数据保护功能 保护工作表 工作簿 1 为工作簿加密码 2 以隐藏方式保存工作簿 3 为工作表加密码 4 隐藏工作表 并阻止其他人随意取消隐藏 5 设置不同区域不同密码 6 设置只有部分单元格可编辑 7 隐藏敏感公式 设置表格的锁定保护将允许用户更改的单元格区域设置为解除锁定状态隐藏单元格的公式先选中全表 格式 单元格 保护 把选定和隐藏上的两个勾去掉 再选中你要隐藏的单元格 格式 单元格 保护 把选定和隐藏前的两个勾打上 再选工具 保护 保护工作表将工作表进行保护的设置 加密 二 图表及打印输出及其它设置 图表在数据分析中的作用 柱形图 用来显示一段时间内数据的变化或者描述各个项目之间数据比较图表线性图 主要用于以等时间间隔显示数据的变化趋势 强调的是时间性和变动率 而不是变动量饼形图 显示数据系列中的项目和该项目数值总和的比例关系 它只能显示一个系列的数据比例关系组合图 2 1创建分析图表 确定分析目标明确表格数据的取值范围图表向导使用一键成图创建图表 2 2图表的编辑 美化技巧 常规编辑技巧改变图表大小移动图表位置图表内其他对象编辑图表的特殊编辑向图表添加数据删除图表内数据 文字数据修饰线和阴影修饰背景修饰设置图表的序列格式综合案例分析 2 3打印输出及其它设置 页面设置工作表的页眉 页脚分页符和打印选项工作表参数设置设置打印区域自动打印统一表头打印指定页面打印连续页码一次打印多个工作表使某个工作表不能被打印 三 公式与函数的应用及案例 3 1公式的基本用法 双击填充柄复制公式区别公式中各种单元格引用方式绝对引用相对引用混合引用三维引用跨工作表或工作簿的数据计算不同工作表间数据的计算不同工作簿间数据的计算自动求和与自动计算功能的使用 3 2函数应用与常用函数应用举例 函数 是预定义的公式 通过使用一些称为参数的特定值来安特定的顺序和结构执行计算函数的使用方式 函数名称 参数1 参数2 参数3 函数的嵌套特性一个函数可以引用其他函数的结果作为参数Excel最多支持七层嵌套函数逻辑文本类函数应用日期时间函数应用查询引用类函数应用统计计数类函数应用其他常用函数应用财务函数应用 3 2 1逻辑文本类函数 IFAND ORLENMID LEFT RIGHTCONCATENATEEXACT IF函数 判断一个条件是否满足 如果满足返回一个值 如果不满足则返回另一个值 语法 IF logical test value if true value if false 条件为真时 条件为假时 AND OR函数 AND函数的作用 是对多个逻辑值进行交集计算 返回逻辑值语法 AND logical1 logical2 OR函数的作用 是对多个逻辑值进行并集计算 返回逻辑值语法 OR logical1 logical2 MID MIDB LEFT RIGHT函数 MIDB函数作用自文字指定位置开始向右提取指定长度的字符串 操作对象均为文本 MID以字符为单位 MIDB以字节为单位截取语法 MID text start num num bytes LEFT返回文本字符串中的第一个或前几个字符 RIGHT返回文本字符串中的最后一个或后几年字符语法 LEFT text num chars 语法 RIGHT text num chars CONCATENATE函数 CONCATENATE函数的作用是将几个字符串合并为一个文本字符串语法 CONCATENATE text1 text2 也可使用 运算符代替实现文本的合并 EXACT函数 EXACT函数的作用 测试两个字符串是否完全相同 如果它们完全相同 则返回TRUE 否则返回FALSE EXACT函数能区分大小写 但忽略格式上的差异语法 EXACT text1 text2 参数 Text1是待比较的第一个字符串 Text2是待比较的第二个字符串 3 2 2日期与时间函数 DATE 返回代表特定日期的序列号语法 DATE year month day NOW 返回计算机内部时钟的当前日期和时间语法 NOW TODAY 返回当前日期的序列编号语法 TODAY 注意 序列号是MicrosoftExcel日期和时间计算使用的日期 时间代码 MicrosoftExcel可将日期存储为可用于计算的序列号 默认情况下 1900年1月1日的序列号是1 而2008年1月1日的序列号是39448 这是因为它距1900年1月1日有39448天 3 2 3查询和引用类函数 LOOKUPHLOOKUPVLOOKUPINDEXMATCH LOOKUP 使用LOOKUP函数是在单行区域或单列区域中查找数值 然后返回第二个单行区域或单列区域中相同位置的数值LOOKUP lookup value lookup vector result vector 说明 lookup vector为第一个包含一行或一列的区域 result vector为第二个包含一行或一列的区域 其大小必须与参数lookup vector相同 HLOOKUP 实现水平查找 使用HLOOKUP函数可以在表格的首行查找指定的数值 并由此返回指定行处与该数值同一列的数值 当比较值位于数据表首行时 可使用此函数语法 HLOOKUP lookup value table array row index num range lookup VLOOKUP 实现竖直查找 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 当比较值位于数据表首列时 可以使用此函数语法 VLOOKUP lookup value table array col index num range lookup INDEX INDEX函数用于返回表或者区域中的值或值的引用语法 INDEX reference row num column num area num ref 表示对一个或多个单元格区域的引用 如是多个区域需用括号括起来 row column 表示引用中某行 列的序号 area 表示选择引用中的一个区域 对于多区域引用 MATCH MATCH用来返回在指定方式下与指定数值相匹配的数组中元素的相应位置 返回的不是数值本身 语法 MATCH lookup value lookup array match type L value 为需要在数据表中查找的数值L array 表示包含参数L value的连续单元格区域M type 指明如何查找值 可取 1 0或1三个值 1 查找大于或等于Value的最小值 array须降序排列0 查找等于Value的第一个值 array可按任意顺序排列1 查找小于或等于Value的最大值 array须按升序排列 3 2 4统计计数类函数 COUNTCOUNTIFSUMIFFREQUENCYRANK COUNT COUNT函数的作用 返回包含数字以及包含参数列表中的数字的单元格的个数 利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数 语法 COUNT value1 value2 参数 Value1 value2 为包含或引用各种类型数据的参数 1到30个 但只有数字类型的数据才被计算 说明函数COUNT在计数时 将把数字 日期 或以文本代表的数字计算在内 但是错误值或其他无法转换成数字的文字将被忽略 如果参数是一个数组或引用 那么只统计数组或引用中的数字 数组或引用中的空白单元格 逻辑值 文字或错误值都将被忽略 COUNTIF函数 COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数 该条件可以是数值 文本或表达式 在人事 工资和成绩统计中有广泛用途 语法 COUNTIF range criteria SUMIF函数 SUMIF函数可对满足某一条件的单元格区域求和 该条件可以是数值 文本或表达式 语法 SUMIF range criteria sum range 频率分布函数 以一列垂直数组返回某个区域中数据的频率分布 例如 使用该函数可以计算在给定的分数范围内测验分数的个数 由于函数返回一个数组结果 所以必须以数组公式的形式输入公式 语法 FREQUENCY data array bins array RANK函数 RANK函数返回一个数字在数字列表中的排位语法 RANK number ref order order 为0或省略 返回对ref降序排列的列表 3 2 5其它常用函数 INTMODROUNDRANK INT INT函数的作用 将数字向下舍入到最接近的整数 语法 INT number 参数 number是需要进行向下舍入取整的实数 MOD 返回两数相除的余数 结果的正负号与除数相同 语法MOD number divisor Number为被除数 Divisor为除数 注解如果divisor为零 函数MOD返回错误值 DIV 0 函数MOD可以借用函数INT来表示 MOD n d n d INT n d ROUND ROUND函数的作用 按指定位数四舍五入某个数字 语法 ROUND number num digits 参数 Number是需要四舍五入的数字 Num digits为指定的位数 Number按此位数进行处理注意 如果num digits大于0 则四舍五入到指定的小数位 如果num digits等于0 则四舍五入到最接近的整数 如果num digits小于0 则在小数点左侧按指定位数四舍五入 RANK函数 RANK函数返回一个数字在数字列表中的排位语法 RANK number ref order order 为0或省略 返回对ref降序排列的列表 3 2 6财务函数及案例应用 PMT 贷款函数 基于固定利率及等额分期付款方式 返回贷款的每期付款额 语法PMT rate nper pv fv type Rate贷款利率 Nper该项贷款的付款总数 Pv现值 或一系列未来付款的当前值的累积和 也称为本金 Fv为未来值 或在最后一次付款后希望得到的现金余额 如果省略fv 则假设其值为零 也就是一笔贷款的未来值为零 Type数字0或1 用以指定各期的付款时间是在期初还是期末 注解PMT返回的支付款项包括本金和利息 但不包括税款 保留支付或某些与贷款有关的费用 应确认所指定的rate和nper单位的一致性 例如 同样是四年期年利率为12 的贷款 如果按月支付 rate应为12 12 nper应为4 12 如果按年支付 rate应为12 nper为4 四 数据分析