excel鲜为人知的技巧.ppt

目录 n一、绝对引用与相对引用 n二、电话号码区号的输入 n三、间隔日期的计算 n四、单元格的分列 n五、单元格的合并 n六、自动保存 目录 n七、VLOOKUP的应用 n八、IF函数的应用 n九、宏的应用 n十、求平均数与统计 n十一、分类汇总与数据透视 n十二、在每页打印标题 返回目录 目录 n十三、对数画图 n十四、主、次坐标轴 n十五、快速作图 返回目录 一、绝对引用与相对引用 1.问题 当我用求和公式求出总计后，我在单元格c1中填入=b3/b6， 结果如上表所示，这是为什么呢？ 为什么往 下填充时 会不灵呢 ？ 返回目录 一、绝对引用与相对引用 2.原因 仔细观察C4我们会发现其内容为=b3/b6,由于b6为空，默认为0，所 以就出现了那种情况。其实这就是相对引用（引用内容随单元格 做相应改变），如果我们把相对引用变成绝对引用（引用内容不 随单元格做相应改变）就能解决这个问题了。 =b3/b6 返回目录 一、绝对引用与相对引用 3.解决方法 方法一、在c3中输入=b2/12000再往下拉，这是普通的做法，缺 点是效率低，容易出错。 方法二、在c3中输入=b2/$b$5再往下拉，不必输入具体数字， 简单易用，但需用“shift”键配合,虽有进步，但不够完美 。 方法三、在c3中输入=b2/b5，然后将光标移到b与6之间并按F4 键，再往下拉，是目前发现的最简单的方法，推荐使用。 小结：绝对引用在总计数值比较大时特别有用。 =b2/$b$5 将光标移到b与6之 间并按F4键 返回目录 二、电话号码区号的输入 1.问题 有许多编码如同电话号码的区号一样，前面几位都是 一样的，每次都要全部输入不是很烦，但往下填充后 ，又要选定然后修改后几位，速度还是不快，那么有 没有比较快速的输入方法呢？ 返回目录 二、电话号码区号的输入 2.解决方法 其实我们只要把单元格格式中的数字设置为自定 然后在单元格中输入后几位即可。 启示：利用改变单元格格式的方法可以自动在数字前面加0或其他数 字，从而使位数不同的数字变成位数相同的数字。 单元格格式中的数字设置为 自定然后 输入8725639，单元格显示如 图所示 返回目录 三、间隔日期的计算 1.问题 怎样计算2003年1月26日与2003年10月1日之间相隔了 多少天？ 2.解决方法 方法一、利用日历手工计算 方法二、利用excel计算 B1中输入A2-A1,然后 将单元格格式设置为 数值即可 返回目录 四、单元格的分列 1.问题 不小心将姓名与身份证输入在同一列，怎样 才能方便快速地将这一列分为两列呢？ 返回目录 四、单元格的分列 2.解决方法 其实只要用分列就可以解决这个问题了，具体步 骤如下： 数据分列 固定宽度（如有其他分隔符，如“，”等也可选“分隔符号”） （选定宽度）下一步完成 返回目录 五、单元格的合并 1.问题 有时我们需要将一列拆成两列，但有时我 们却需要将两列合成一列，如何将上两 列合并在一起？ 返回目录 五、单元格的合并 2.解决方法 方法一：在单元格C1中输入=A1&B1然后往下拉即可。 方法二：使用函数=CONCATENATE()，具体步骤为： = A1&B1或 =CONCATENATE (A1,B1) 插入函数文本CONCATENATECONCATENATE 返回目录 六、自动保存 1.问题： 有时候由于某些原因，如意外断电等，由于没有及 时保存以至工作成果付诸东流。 2.解决方法： 其实excel也有自动保存功能，但需要加载宏。具体 操作如下： 注：自动保存默认为每10分钟保存一次，可按需要调整 ，如不选择保存前提示，则在修改时须慎重，以免误 改后自动保存造成损失，建议在修改前备份。 工具加载宏自动保存确认 返回目录 七、VLOOKUP的应用 1.问题 如何在大量数据中批量找出需要的数据？如假设表一的数据量非常 大，表二中是许多需要查询的数据。现简化为如何在表一中找出 李四、朱八的工资？，当然如果表二中数据的数量很少，用查询 就能解决，但如果表二中数据的数量很多，查找功能就难以胜任 了。 表一 表二 返回目录 七、VLOOKUP的应用 2.解决方法： 这种情况下，使用vlookup函数查找就非常方便了。具体步骤如下： 注：vlookup函数表达式的含义为（查找条件，查找范围，查找结果 所在列，查找结果准确性 ），如查找结果在行上，查找条件在列 上，可用hlookup函数。 表一 表二 =VLOOKUP(A1, 表一！ A:B,2,FALSE) vlookup查找与引用函数插入确定 返回目录 八、IF函数的应用 1.问题 假设供应商评分在90分及以上为A级，80-89为B级，70- 79为c级，当然我们可以用按评分排序来解决,而此类 带条件的判断正是if函数的用武之地。 返回目录 八、 IF函数的应用 2.解决方法 处理这种判断正是if函数的拿手好戏，只需在C2中输入 =IF(B2=90,“A”,IF(B2=80,“B”,IF(B2=70,“C”)，然后下拉， excel就会将等级自动评好。 其实if函数的判断功能非常强大，限于篇幅不做深入讨论，如有兴 趣或需要，欢迎进一步交流。 =IF(B2=90,“A”, IF(B2=80,“B”, IF(B2=70,“C”) ) 返回目录 九、宏的应用 宏的功能就是把多个步骤简化为一个步骤，类似 于设立快捷键。 其基本步骤为： 当按下ctrl键+快捷键后，excel将重复所录制的过程 。在录制宏的过程中要尽量避免不必要的操作，以提 高宏运行的效率。 工具宏 录制过程后）停止录制 录制新宏 （设立快捷键后）确定 返回目录 十、求平均数与统计 1.问题 如上表，有的成绩是空的（未参加考试），我们 怎么才能又快又准地求出平均成绩，总人数， 及格人数呢？ 返回目录 十、求平均数与统计 2.解决方法 其实我们只需使用AVERAGE、COUNTA、COUNTIF函数就可办到！ 具体步骤如下： 插入函数统计 AVERAGE、 COUNTA、 COUNTIF 确定 =AVERA GE(B2: B6) =COUNT A(A2:A6) =COUNTIF(B 2:B6,“=60 “) 返回目录 十一、分类汇总与数据透视 1.问题 1.如何将可乐、雪碧、橙汁按销售额日期明细汇总显示并分页显示？ 2.如何将可乐、雪碧、橙汁按销售额日期明细汇总显示同时求出占比 ？ 返回目录 十一、分类汇总与数据透视 2.解决方法 1.汇总并分页显示的方法 使用分类汇总分页显示的步骤如下： 分页符 数据分类汇总 设置参数 确定 分类字段：商品名称 汇总方式：求和 选定汇总项：销售额 每组数据分页 返回目录 十一、分类汇总与数据透视 2.汇总并求占比的方法 这些只需用数据透视即可办到。具体步骤如下： 数据数据透视表设置参数完成 将销售额拖入到数据 栏两次，双击其中一 个销售额，汇总方式 选求和，单击选项， 在数据显示方式栏中 选“占同列数据总和的 百份比”即可。 返回目录 十二、在每页打印标题 1.问题 如何在打印时，让每页都显示标题？ 返回目录 十二、在每页打印标题 2.解决方法 普通的做法是在分页符处插入标题栏，但这样当页数很 多时容易出错，效率较低。其实有简单的方法可以一 次解决，步骤如下： 文件 页面设置 确定工作表 顶端标题行 在此选定每页需要打 印的标题 返回目录 十三、对数画图 1.问题 如何使图表中的刻度显示比较均匀，如何使158 、159期显示出来？ 返回目录 十三、对数画图 2.解决方法 点击y轴右键，对 数 返回目录 十四、主、次