excel2003使用小技巧PPT.ppt

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel 2003使用小技巧,铝业财务中心,1,主要内容,一、录入技巧,二、编辑技巧,三、几种常用函数的应用,四、动态图表的制作及应用,2,一、录入技巧,1、快速输入系统时间、日期、平方米等,用,“,Ctrl+：,”,输入时间；用,“,Ctrl+；,”,输入日期；输入平方米可以利用上标方法，也可以用快捷键,“,Alt+178,”,，输入立方米则用,“,Alt+179,”,；另外，Alt+137,、Alt+177,。,2、快速添加信息,如果要给单元格数据的尾部添加信息，可以选中该单元格然后按F2键。光标就会在数据尾部出现，输入数据后回车即可。,3,3、快速填入相同内容,选中多个单元格后(包括不连续单元格)，输入字符，然后按Ctrl+Enter组合键，即可在选中的每个单元格中填入上述相同的字符。,选中下面的单元格，按Ctrl+,即可将上一单元格的内容快速复制下来。,4,4、分数输入技巧,如果事先将Excel单元格设置为,“,分数,”,格式，即可在其中输入1/3、3/5这样的分数数据。除此以外，可以在,“,常规,”,单元格中按,“,0空格分数,”,的格式输入分数，如输入的,“,0 1/3,”,（注意：0与1之间加空格）即可显示,“,1/3,”,。不过，此法输入的分母不能超过99，否则输入结果显示为0。,5,5、轻松实现多行文本输入,Excel默认单元格输入一行文本，实现多行文本输入的简单方法是：当你需要在单元格中开始一个新行时，只要按 Alt+Enter组合键即可，从而在一个单元格中输入两行乃至多行文本。,6,6、在单元格区域内换行,将某个长行转成段落并在指定区域内换行。例如：A10内容很长，要将其显示在A列至C列之内，步骤为：选定区域A10:C12（先选A10），选择,“,编辑,”,菜单之,“,填充,”,的,“,内容重排,”,，A10内容就会分布在A10:C12区域中。,7,7、巧用字符映射表,当我们需要在Excel输入某些符号，而Excel中又没有时，可以选择,“,开始,”,“,程序,”,“,附件,”,“,系统工具,”,“,字符映射表,”,，单击特殊符号按钮，再通过上一个、下一个按钮调出各类符号，选中后，单击选择按钮，再单击复制按钮，就可以粘贴到了Excel中了。,8,8、在多个工作表内输入相同内容的技巧,如果需要在同一个工作簿的不同工作表中输入相同的内容，这时我们不必逐个进行输入，也不必利用复制、粘贴，最简单的方法：,（1）打开相应工作簿。,（2）在按下Ctrl键的同时用鼠标单击需要输入相同内容的不同工作表标签，为这些工作表建立联系关系。,（3）在其中的任意一个工作表中输入需要输入的内容，此时这些数据就会自动出现在选中的其它工作表相应位置之中。,（）取消联系关系，按Ctrl,再点击相应工作表即可。,9,9、在公式中快速输入不连续的单元格地址,在SUM函数中输入比较长的单元格区域字符串很麻烦，尤其是当区域为许多不连续单元格区域组成时。这时可按住Ctrl键，进行不连续区域的选取。区域选定后选择,“,插入,”,“,名字,”,“,定义,”,，将此区域命名，如AA，然后在公式中使用这个区域名，如,“,SUM（AA）,”,。,10,10、在已有的单元格中批量加入一段固定字符,如果需要在已有单元格中加入一个或多个固定字符，不需要一个个去改，可以用以下方法批量加入：,（1）假设要在A列单元格前面加入,“,0757,”,，先在A列后插入一个新列，为B列；,（2）在B1单元格写入：,=,“,0757,”,&A1后回车；(注：字符型加英文状态下的双引号),（3）选中B1往下拉，当你放开鼠标左键时就全部都改好了。若是在A列单元格后面加,则在B1单元格中写入：=A1&,“,0757,”,后回车。,11,二、编辑技巧,1、在公式内容和显示公式之间切换,公式执行后显示的是计算结果，按Ctrl+键（位于键盘左上角），可使公式在显示公式内容与显示公式结果之间切换，方便公式编辑和计算结果查看。,2、快速选定Excel区域,在Excel中，要想在工作簿中快速选定某块区域，只需单击想选定的区域的左上角单元格，同时按住Shift键不放，再单击想选定的区域的右下角单元格即可。,12,3、高效率计算平均值、求和、最值等,在Excel中计算一些数据的平均值，一般都是通过函数来进行的，但这样的效率很慢，其实只要选中相关的数据，在状态栏上就已经显示和了，试着右击状态栏，会弹出一个菜单，菜单中包括了很多计算项目，例如选择均值，即在状态栏上显示出所选中单元格的平均数。,13,4、,“,选择性粘贴,”,进行简单运算,多个单元格内的数据如果需要与某一个数进行加、减、乘、除，并且不希望增加新的数据单元格，可以使用,“,选择性粘贴,”,效果：如有多个数据区域，希望其中的数据同时乘以17。可以先在一个空白单元格中输入17，将其选中,“,复制,”,，再将要乘以17的单元格全部选中。打开,“,选择性粘贴,”,对话框，选中,“,运算,”,下的,“,乘,”,，单击,“,确定,”,按钮，所菜单元格中的数据均变为乘以17的结果。如果你要对单元格中的数据执行其他运算，只须在对话框的,“,运算,”,下选择某种运算即可。,14,5、利用,“,监视窗口,”,监视公式和运算结果,如果在多个单元格或工作表中设置了公式，要想查看公式和计算结果，就必须反复滚动窗口或切换工作表。采用下面这个技巧可以避免这种麻烦：选中含有公式的单元格，鼠标右键单击工具栏选择,“,监视窗口,”,。单击,“,监视窗口,”,中的,“,添加监视,”,按钮，所选中单元格中的公式及其计算结果就会显示在,“,监视窗口,”,中，观察起来非常方便。,15,6、快速交换相邻两列数据,编辑Excel工作表时，如果需要交换相邻两列的数据，可以借助Shift键快速实现。比如，现在需要交换A、B两列的数据，那么请按下述步骤操作：,选中A列数据：把鼠标指向A列右边缘，当指针变为双十字箭头时，按住Shift键，同时按住 左键向右拖动鼠标。,在拖动过程中，会出现一条虚线，当虚线到达 B列右边缘时，屏幕上会出现,“,C:C,”,的提示。,此时，松开鼠标左键，即可完成A、B两列数据的互换。,16,7、如何把B列与A列不同之处标识出来,（1）如果是要求A、B两列的同一行数据相比较：,假定第一行为表头，单击A2单元格，点,“,格式,”,“,条件格式,”,，将条件设为:,“,单元格数值,”“,不等于,”,=B2，点,“,格式,”,“,字体,”,“,颜色,”,，选中红色，两次,“,确定,”,。,用格式刷将A2单元格的条件格式向下复制。,B列可参照此方法设置。,17,（2）如果是A列与B列整体比较（即相同数据不在同一行）：,假定第一行为表头，单击A2单元格，点,“,格式,”,“,条件格式,”,，将条件设为:,“,公式,”,=COUNTIF($B:$B,$A2)=0，点,“,格式,”,“,字体,”,“,颜色,”,，选中红色，点两次,“,确定,”,。,用格式刷将A2单元格的条件格式向下复制。B列可参照此方法设置。,按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B列有A列无的数据标记为红色字体。,18,8、批量地处理按行排序,假设有大量的数据(数值)，需要将每一行按从大到小排序，如何操作？,由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以，这一问题不能用排序来解决。,19,解决方法如下：,假定你的数据在A至E列，请在F1单元格输入公式：=LARGE($A1:$E1,COLUMN(A1),用填充柄将公式向右向下复制到相应范围。,注：如果要从小到大排序，公式改为:,=SMALL($A1:$E1,COLUMN(A1),20,9、把数据彻底隐藏起来,工作表部分单元格中的内容不想让别人查阅，可以将它隐藏起来。,（1）选中需要隐藏内容的单元格(区域)，执行,“,格式单元格,”,命令，打开,“,单元格格式,”,对话框，在,“,数字,”,标签的,“,分类,”,下面选中,“,自,定义,”,选项，然后在右边,“,类型,”,下面的方框中输入,“,;,”,(三个英文状态下的分号)。,21,（2）再切换到,“,保护,”,标签下，选中其中的,“,隐藏,”,选项，按,“,确定,”,按钮退出。,（3）执行,“,工具保护保护工作表,”,命令，打开,“,保护工作表,”,对话框，设置好密码后，,“,确定,”,返回。,经过这样的设置以后，上述单元格中的内容不再显示出来。如果需要恢复显示，请先,“,撤消工作表保护,”,，然后删除设置单元格格式时在,“,类型,”,框中自定义的,“,;,”,符号即可。,22,10、相对引用、绝对引用以及混合引用的快速替换,对于某单元格公式为,“,=SUM（B4:B8）,”,，选中整个公式，按下,“,F4,”,键，该公式内容变为,“,=SUM($B$4:$B$8),”,绝对引用；第二次按下,“,F4,”,键，公式内容又变为,“,=SUM(B$4:B$8),”,混合引用；第三次按下,“,F4,”,键，公式则变为,“,=SUM($B4:$B8),”,混合引用；第四次按下,“,F4,”,键时，公式变回了初始状态,“,=SUM(B4:B8),”,相对引用。,23,11、对公式计算结果的引用,假设excel表中某个或多个单元格中的公式使用相对引用，我们将公式复制、粘贴到其他地方时，公式的计算结果会发生变化。若要复制、粘贴后的结果随着原单元格的变化而变化，且结果一致，可参照以下几种方法进行：,（1）将相对引用的公式转换为绝对引用，然后复制、粘贴；,（2）直接在编辑栏中复制公式，进行粘贴；,（3）先复制公式单元格，然后进行粘贴链接；,（4）使用excel表中的照相机功能。,24,12、Excel中,“,照相机,”,功能的妙用,这个功能比较另类，似乎和计算、统计、排序等等,“,正统,”,功能格格不入，因为它的作用竟然是,“,抓屏,”,!而且允许让你通过鼠标进行选择，,“,指哪抓哪,”,。,要找到这个功能还不太容易，点击Excel,“,工具,”,“,自定义,”,“,命令,”,“,类别,”,“,工具,”,，再在命令栏中找到,“,照相机,”,按钮，并将它拖到工具栏的任意位置。,25,如果我们要对表格中的某一部分,“,照相,”,，只须先选择它们，然后按下,“,照相机,”,按钮，这时该选定区域就被,“,拍,”,了下来。然后将鼠标移动到需要显示,“,照片,”,的地方(当然，也可以是另一张表格)，点击鼠标，这样刚才被,“,拍摄,”,的,“,照片,”,就立即粘贴过来了。Excel将它作为一幅图片来对待，我们可随意将它进行旋转、缩放处理。当修改原来的数据时，该图片中的数据呈现同步更新。,26,三、几种常用函数的应用,1、隔行相加,（1）奇数行相加,=SUMPRODUCT(MOD(ROW(A1：An),2),A1：An),（2）偶数行相加,=SUMPRODUCT(MOD(ROW(A1：An)+1,2),A1：An),27,2、用函数组合进行多条件的计数统计,例：第一行为表头，A列是,“,姓名,”,，E列是,“,收入总额,”,，F列是,“,社保费,”,，J列是,“,是否优秀员工,”,，现在要统计收入总额高于2500元，买有社保，而且是优秀员工的人数。统计结果存放在本工作表的其他列。公式如下：,=SUM(IF(E2:E4=2500)*(F2:F40)*(J2:J4=,是,),1,0),28,3、从身份证号码中提取年龄、性别,身份证号码比较多，而且身份证号的最后一位字符有可能为X，所以身份证号应为字符型数据。,计算年龄：,=YEAR(NOW()-IF(LEN(C2)=18,MID(C2,7,4)，MID(C2,7,2)+1900),判断性别：,=IF(MOD(MID(C2,17,1),2)=0,“,女,”,“,男,”,),29,4、使用Excel打出工资条,有两种方法：,（1）采用编写公式直接产生,工资条公式应为=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,sheet1!A$1,INDEX(sheet1!$A:$G,INT(ROW()+4)/3),COLUMN(),30,（2）间接法,对于一些员工来说，采用编写公式法显得专业性太强。其实，变通一下，也可以不用公式，直接让Excel按人头打出工资条的，方法简单，适合Excel新手使用。（由于方法简单，但描述起来，内容繁多，在此不作描述，请留意演示）,31,5、if函数、sumif函数以及vlookup函数的应用,(1)if函数,在excel表中，if函数的使用非常普遍，虽然用法简单，但是功能强大，只要懂得灵活使用，excel表中的大多问题都可以用if函数来解决。,如：工资表中的个人所得税，可以用if函数快速计算，,32,假设B3单元格为工资收入总额（忽略税前扣除项目）：,个税,=IF(B3-2000)=0,0,IF(B3-2000)=500,(B3-2000)*0.05,IF(B3-2000)=2000,(B3-2000)*0.1-25,IF(B3-2000)=5000,(B3-2000)*0.15-125,IF(B3-2000)=1800,E2:E50),34,3、vlookup函数,vlookup函数是针对搜索表区域首列满足条件的元素，而hlookup函数则针对搜索表区域首行满足条件的元素，两者用法相似，在此，仅以vlookup函数为例进行简单讲解。,例如：在工资表中寻找职工B的收入总额,=VLOOKUP(B,A2:J7,5,0),上例是最简单的例子。在第四部分内容中，vlookup函数和hlookup函数有详细讲解，敬请留意。,35,四、动态图表的制作及应用,原理：利用查找函数VLOOKUP来根据一个变化的查,询条件，从而来达到，当查询的条件变化,时，数据也跟着变化,所以也就促成了图表,数据源的动态。,36,实例：根据2008年四个季度管理费用数据表，制作一个管理费用的动态图表。假设A4:P8区域为四个季度的管理费用数据表（请留意演示）。,操作步骤：,1、复制B4:P4，选中A11单元格，右键选择项粘贴转置,2、选中B10单元格，菜单栏数据数据有效性序列来源选择A5:A8。,37,3、在B11中输入公式=VLOOKUP($B$10,$4:$8,ROW()-9,0)，然后下拉之B25,4、以A10:B25为数据源创建一个柱型图。,5、调出控件工具箱，画一个组合框出来。,6、右键单击组合框，选择属性。,38,7、找到ListFillrange和LinkedCell这两项分别填入动态图表!A5:A8和动态图表!B10，填写完毕后关闭属性对话框。,8、把组合框移动到图表上方中间位置，然后按住Ctrl键分别点击组合框和图表，右键组合。,9、退出组合框控件的设计模式。这个动态图表就做成了。,39,：静态图表的制作及应用,40,产品结构,41,2008年与2007年利润情况对比走势图,42,2008年与2007年销量和利润对比趋势图,43,：Word邮件合并快速制作询证函,44,THE END,谢谢！,45,