excel鲜为人知的技巧.ppt

目录,一、绝对引用与相对引用二、电话号码区号的输入三、间隔日期的计算四、单元格的分列五、单元格的合并六、自动保存,目录,七、VLOOKUP的应用八、IF函数的应用九、宏的应用十、求平均数与统计十一、分类汇总与数据透视十二、在每页打印标题,目录,十三、对数画图十四、主、次坐标轴十五、快速作图,一、绝对引用与相对引用,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.解决方法其实我们只要把单元格格式中的数字设置为自定义0571-0000000，然后在单元格中输入后几位即可。启示：利用改变单元格格式的方法可以自动在数字前面加0或其他数字，从而使位数不同的数字变成位数相同的数字。,单元格格式中的数字设置为自定义0571-0000000，然后输入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),插入,函数,文本,CONCATENATE,六、自动保存,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,确定,=AVERAGE(B2:B6),=COUNTA(A2:A6),=COUNTIF(B2:B6,=60),十一、分类汇总与数据透视,1.问题1.如何将可乐、雪碧、橙汁按销售额日期明细汇总显示并分页显示？2.如何将可乐、雪碧、橙汁按销售额日期明细汇总显示同时求出占比？,十一、分类汇总与数据透视,2.解决方法1.汇总并分页显示的方法使用分类汇总分页显示的步骤如下：,分页符,数据,分类汇总,设置参数,确定,分类字段：商品名称汇总方式：求和选定汇总项：销售额每组数据分页,十一、分类汇总与数据透视,2.汇总并求占比的方法这些只需用数据透视即可办到。具体步骤如下：,数据,数据透视表,设置参数,完成,将销售额拖入到数据栏两次，双击其中一个销售额，汇总方式选求和，单击选项，在数据显示方式栏中选“占同列数据总和的百份比”即可。,十二、在每页打印标题,1.问题如何在打印时，让每页都显示标题？,十二、在每页打印标题,2.解决方法普通的做法是在分页符处插入标题栏，但这样当页数很多时容易出错，效率较低。其实有简单的方法可以一次解决，步骤如下：,文件,页面设置,确定,工作表,顶端标题行,在此选定每页需要打印的标题,十三、对数画图,1.问题如何使图表中的刻度显示比较均匀，如何使158、159期显示出来？,十三、对数画图,2.解决方法,点击y轴右键，对数,十四、主、次坐标轴,1.问题怎么样使下面的图形更清楚？,十四、主、次坐标轴,2.解决方法,选中下面的线条，右键，选择数据系列格式，再选坐标轴，然后选次坐标轴,选中线条,数据系列格式,坐标轴,次坐标轴,右键,十五、快速作图,1.问题 如何快速为上表作图？,十五、快速作图,2.解决方法,选中a1:c5按f11键,