excel鲜为人知的技巧.pptVIP

目录,一、绝对引用与相对引用二、电话号码区号的输入三、间隔日期的计算四、单元格的分列五、单元格的合并六、自动保存,目录,七、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.解决方法其实我们只要把单元格格式中的数字设置为自定然后在单元格中输入后几位即可。启示：利用改变单元格格式的方法可以自动在数字前面加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),插入,函数,文本,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轴右键，对数,十