下载

1下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 Excel使用技巧及高级应用

Excel使用技巧及高级应用.ppt

Excel使用技巧及高级应用

xeed
2012-02-07 0人阅读 举报 0 0 暂无简介

简介:本文档为《Excel使用技巧及高级应用ppt》,可适用于IT/计算机领域

Excel的使用技巧及高级应用Excel的使用技巧及高级应用一、Excel的使用技巧一、Excel的使用技巧如何显示多行文本一般情况下在单元格中输入的文本是不换行的这样看起来会很别扭。如果要在同一单元格中显示多行文本可以这样做:选中要换行的单元格单击“格式”菜单选择“单元格”命令然后选择“对齐”选项卡选中“文本控制”标题下的“自动换行”复选框。一、Excel的使用技巧一、Excel的使用技巧如何显示多行文本如果单元格中还有段落就要在里面输入硬回车这时Enter键的功能不是在单元格内换行而是在单元格间移动。这时我们可以在编辑栏(或正编辑的单元格)中要换行的地方单击鼠标按ALTENTER键来换行。换行后的效果如下图在编辑区文字是换行的。一、Excel的使用技巧一、Excel的使用技巧怎样同时改变多行行高或多列列宽?以改变行高为例先选中要改变行高的列按下Shift键再单击行标题头可以选定连续的多行(如果要选中多个不连续行可以按下Ctrl键)。选中多列后拖动任意一个被选中的行标题间的分界线到适当高度释放鼠标所有被选中的行高都改变了。一、Excel的使用技巧一、Excel的使用技巧如何改变回车键的功能?一般情况下我们在一个单元格中输入资料后按下回车键后活动单元格会转入下一行的同列单元格中。如果要转到右边的单元格能不能实现呢?打开“工具”菜单选择“选项”命令单击“编辑”选项卡多后从“方向”下拉列表框中选择“向右”单击“确定”。一、Excel的使用技巧一、Excel的使用技巧巧用转置功能粘贴数据我们手头有这样一个数据表行表示学生姓名列表示学生的各科成绩。如果我们想得到一个以行表示各科成绩以列表示学生姓名的数据表应该怎样做呢?其实很简单EXCEL为我们提供了转置功能。先选中要进行转换的数据区A:D按下CtrlC将其复制再将光标定位在另一个工作表的A单元格中然后选择“编辑”菜单中的“选择性粘贴”命令。在“选择性粘贴”对话框中选中“转置”复选框单击“确定”。一、Excel的使用技巧一、Excel的使用技巧快速输入技巧在Excel的工作表中如果在多个单元个中输入同一个公式多次填写某一个字符或某一个有规律数字一个一个输入是很麻烦的掌握一些技巧是可以快速输入的。选定要在多个单元格中输入同一个公式的这一区域在某一单元格中输入公式后安组合键CtrlEnter那么所选区域那的所有单元格中就都输入了同一公式。)、用组合键CtrlEnter在多个单元格中输入同一个公式REPT工作表函数的语法为:REPT(text,numbertimers)。其中text为需要重复显示的字符串不能多能多于个字符numbertimers为指定文本进行重复的次数为正数否则将截尾取整数。例如要在某一单元格中重复显示“■”次则输入=REPT("■",)回车即可。)、用REPT工作表函数在某一单元格中多次填写某一字符一、Excel的使用技巧一、Excel的使用技巧快速输入技巧在Excel工作表中如果需要在一行或一列单元格中填入按顺序排列的项目序号用Excel的自动填充功能可以帮助您快速输入这些数据序列而且还不容易出错。其方法是:在第一单元格那输入数据在下一个单元格那输入第二个数据选定这两个单元格将光标指向单元格右下方的填充柄沿着要填充的方向拖动填充柄拖过的单元格中会自动的按Excel内部规定的序列进行填充。如下图在B中输入“第名”然后拖动控制柄往下拉松开鼠标时所拉过的区域就自动填充了“第名、第名”。在拖动控制柄的过程中我们也可以在鼠标的右边小说明中看到所填充的内容。)、用填充柄自动填充工作表一、Excel的使用技巧一、Excel的使用技巧快速输入技巧如果使用鼠标右键拖动单元格填充柄也可以实现自动填充。其方法是:在某单元格内输入数据按住鼠标右键沿着要填充的方向拖动填充柄此时就会弹出包含下列各项的快捷菜单(如下图):复制单元格、以序列方式填充、以格式填充、以值填充、以天数填充、以工作日填充、以年填充、等差序列、等比序列。。。。此时您可以根据需要进行选择填充方式。显然这种方法更有灵活性。)、用填充柄自动填充工作表一、Excel的使用技巧一、Excel的使用技巧快速移动活动单元格经常要在数据区域的最开始和最末尾处、最左端和最右端处来回折腾吗?其实用不着多次单击滚动条也不用多次滚动鼠标的滚轮的。只要将鼠标指向当前单元格的下边框当鼠标指针变为四个箭头的十字形时双击就可以使活动单元格迅速定位于当前数据区域中当前列的最后一行如图所示。双击单元格的上边框可以到达最上一行而双击左边框和右边框则可以分别迅速到达最左端和最右端。快吧?也可按Ctrl 上下左右箭头可快速移动到数据清单的顶端、底部、最左边及最右边。一、Excel的使用技巧一、Excel的使用技巧多个工作表的同时输入和设置我们有时要把一个Excel文件中的多个工作表输入同样数据或设置成同样的格式分别对一张张工作表去输入和设置感觉很繁琐。如果用下面的方法就可以一次将多个工作表中的数据和格式同时完成:把鼠标移到工作表的名称处(如果没有给每张表取名的话Excel自动设置的名称就是Sheet、Sheet、Sheet等等)然后点右键在弹出的菜单中选择“选择全部工作表”的菜单项这时再进行输入和格式设置就是针对全部工作表了。一、Excel的使用技巧一、Excel的使用技巧防止误删工作表及数据在包含多个工作表的工作薄中为了防止误修改我们常常采取将行(列)隐藏或者设置编辑区域的方法但是如果要防止整个工作表的误修改怎么办呢单击“格式→工作表→隐藏”将当前的工作表隐藏这样操作者连表格都看不到误操作就无从谈起了。要重新显示该表格只须单击“格式→工作表→取消隐藏”。要注意的是:如果设置了工作表保护则不能进行隐藏操作。为了防止误修改数据可以采取数据锁定保护的方法选定不需要锁定保护的区域单击“格式→单元格→保护”将“√”去掉确定后再选择“工具→保护→保护工作表”这时可以选择保护密码确定后就可以保护锁定区域的数据了。快速隐藏:在英文状态下按“Ctrl”或“Ctrl”组合键就可以快速隐藏光标所在的行或列ShiftCtrl或取消隐藏。一、Excel的使用技巧一、Excel的使用技巧$符号的应用在工作表的单元引用地址有种:A(相对列和相对行)$A(绝对列和相对行)A$(相对列和绝对行)$A$(绝对列和绝对行)在复制或者拖拽时要保持引用单元的地址不变就要采用绝对列或绝对行。一、Excel的使用技巧一、Excel的使用技巧条件格式的应用在学生成绩表中如果想让小于分的额以“红色”显示我们可以这样设置:)选中各科成绩单元执行“格式→条件格式”命令打开“条件格式”对话框。单击第二个方框右侧的下拉按钮选中“小于”选项在后面的方框中输入数值“”。单击“格式”按钮打开“单元格格式”对话框将“字体”的“颜色”设置为“红色”。)设置完成后按下“确定”按钮。一、Excel的使用技巧一、Excel的使用技巧建立分类下拉列表填充项我们常常要将数据输入到表格中为了保持数据的一致性利用“数据有效性”功能建了一个分类下拉列表填充项。)在Sheet中将企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中建立一个企业名称数据库。)选中A列(“工业企业”名称所在列)在“名称”栏内输入“工业企业”字符后按“回车”键进行确认。仿照上面的操作将B、C……列分别命名为“商业企业”、“个体企业”……)切换到Sheet中选中需要输入“企业类别”的列(如C列)执行“数据→有效性”命令打开“数据有效性”对话框。在“设置”标签中单击“允许”右侧的下拉按钮选中“序列”选项在下面的“来源”方框中输入“工业企业”“商业企业”“个体企业”……序列(各元素之间用英文逗号隔开)确定退出。再选中需要输入企业名称的列(如D列)再打开“数据有效性”对话框选中“序列”选项后在“来源”方框中输入公式:=INDIRECT(C)确定退出。)选中C列任意单元格(如C)单击右侧下拉按钮选择相应的“企业类别”填入单元格中。然后选中该单元格对应的D列单元格(如D)单击下拉按钮即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。一、Excel的使用技巧一、Excel的使用技巧Excel中“摄影”功能这个功能比较另类似乎和计算、统计、排序等等“正统”功能格格不入因为它的作用竟然是“抓屏”!而且还不是像“PrintScreen”按钮那样“一把乱抓”而是允许让你通过鼠标进行选择“指哪抓哪”。要找到这个功能还不太容易我们点击Excel“工具”菜单的“自定义”选项。在“命令”卡片的“类别”中点“工具”再在命令栏中找到“摄影”按钮并将它拖到工具栏的任意位置。如果我们想要对表格中的某一部分“照相”只须先选择它们(图)然后按下“摄影”按钮这时该选定区域就被“拍”了下来。然后将鼠标移动到需要显示“照片”的地方(当然也可以是另一张表格)再次按下“摄影”按钮这样刚才被“拍摄”的“照片”就立即粘贴过来了。当然和“照片”一同出现的还有“图片”工具栏。很显然Excel是将它作为一幅图片来对待了我们可随意将它进行旋转、缩放处理。不过这可不是一般的照片!你可以试着改动原来被我们“拍摄”下来的表格数据看看刷新一下数据结果“照片”中的数据竟然也被同步更新了!二、Excel图表制作二、Excel图表制作(一)柱形图EXCEL提供了类多种基本的图表包括柱形图、饼图、条形图、面积图、折线图、气泡图以及三维图。图表可以用来表现数据间的某种相对关系在常规状态下我们一般运用柱形图比较数据间的多少关系用折线图反映数据间的趋势关系用饼图表现数据间的比例分配关系。运用Excel的图表制作可以生成多种类型的图表下面以柱形图、折线图、饼图三种类型为例分别介绍其制作方法。选取要生成图表的数据区,单击“插入”工具栏中的“图表”显示图表向导第一步骤图表类型对话框选择图表类型为柱形图单击“下一步”。.进入图表向导第二步骤图表数据源根据需要选择系列产生在行或列单击“下一步”。.进入图表向导第三步骤图表选项此时有一组选项标签用来确定生成的图表中需要显示的信息(如图表标题、轴标题、网格线等可根据个人生成图表的需要选择)。.通常直接单击“下一步”进入图表向导第四步骤图表位置在默认状态下程序会将生成的图表嵌入当前工作表单中。如果希望图表与表格工作区分开,选择新工作表项输入新表单的名称(本例使用默认状态)。.单击“完成”按钮就生成了比较数据的柱形图。二、Excel图表制作二、Excel图表制作(二)折线图在最终生成的柱形图中用鼠标右键单击图区域显示命令列表从中选择图表类型选项显示图表类型对话框从对话框中选择折线图单击“确定”按钮后就生成了反映数据趋势的折线图。(三)饼图同折线图的生成步骤基本一样只要从“图表类型”对话框中选择“饼图”单出“确定”按钮就能生成反映数据比例分配的饼图。说明:在图表的制作过程中、制作完成后均有很多种修饰项目可根据自己的爱好和需要按照提示选择满意的背景、色彩、子图表、字体等修饰图表。三、函数的应用三、函数的应用Excel的强大不仅在于它易于编排表格和计算而在于它有完善的函数功能可以进行很多数据处理。Excel函数的选择在用函数处理数据时常常不知道使用什么函数比较合适。Excel的“搜索函数”功能可以帮你缩小范围挑选出合适的函数。执行“插入→函数”命令打开“插入函数”对话框在“搜索函数”下面的方框中输入要求(如“计数”)然后单击“转到”按钮系统即刻将与“计数”有关的函数挑选出来并显示在“选择函数”下面的列表框中。再结合查看相关的帮助文件即可快速确定所需要的函数。三、函数的应用三、函数的应用常用函数的应用)if函数可以使用函数IF对数值和公式进行条件检测。语法IF(logicaltest,valueiftrue,valueiffalse)Logicaltest表示计算结果为TRUE或FALSE的任意值或表达式。例如A=就是一个逻辑表达式如果单元格A中的值等于表达式即为TRUE否则为FALSE。本参数可使用任何比较运算符。例:=IF(C<,"不及格","及格")=IF(CB>,“A”,IF(C>,“B”,IF(C>,“C”,IF(C>,“D”,“F”))))if函数最多可套层。三、函数的应用三、函数的应用常用函数的应用)rank函数返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值(如果列表已排过序则数字的排位就是它当前的位置)。语法RANK(number,ref,order)Number为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字指明排位的方式。如果order为(零)或省略MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。如果order不为零MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。例:=RANK(C,C$:C$)三、函数的应用三、函数的应用常用函数的应用)large函数返回数据集里第k个最大值。语法LARGE(array,k)Array数据集例:=LARGE(C$:C$,)类似还有small()函数。)round函数(四舍五入)返回某个数字按指定位数取整后的数字。语法ROUND(number,numdigits)Number需要进行四舍五入的数字。Numdigits指定的位数按此位数进行四舍五入。例:=ROUND(,)将四舍五入到一个小数位()=ROUND(,)将四舍五入到两小数位()=ROUND(,)将四舍五入到小数点左侧一位()三、函数的应用三、函数的应用常用函数的应用)VLOOKUP函数Excel表格和Word中的表格最大的不同就是Excel是将填入表格中的所有内容(包括静态文本)都纳入了数据库的范畴之内。我们可以利用VLOOKUP的函数查询功能对目标数据进行精确定位找到所需要的数据。比如在如图所示的表格中从A到F的单元格中输入了多名同学的各科成绩。而在A到A的单元格中我们则建立了一个“函数查询”区域。我们的设想是当我们在“输入学生姓名”右边的单元格也就是C格中输入任何一个同学的名字后其下方的单元格中就会自动显示出该学生的各科成绩。具体实现的方法如下:三、函数的应用三、函数的应用常用函数的应用将光标定位到C单元格中然后单击“插入”之“函数”选项。在弹出的窗口中选择“VLOOKUP”函数点“确定”。在随即弹出的“函数参数”窗口中我们设置Lookupvalue”(指需要在数据表首列中搜索的值)为“C”(即搜索我们在C单元格中填入的人名)“Tablearray”(指数据搜索的范围)为“A∶B”(即在所有学生的“语文”成绩中搜索)“Colvindexnum”(指要搜索的数值在表格中的序列号)为“”(即数值在第列)“Rangelookup”(指是否需要精确匹配)为“FALSE”(表明不是。如果是就为“TURE”)。设定完毕按“确定”。三、函数的应用三、函数的应用常用函数的应用此时回到表格单击C单元格我们看到“fx”区域中显示的命令行为“=VLOOKUP(CA∶BFALSE)”。复制该命令行在C、C、C、C单元格中分别输入:“=VLOOKUP(CA∶CFALSE)”“=VLOOKUP(CA∶DFALSE)”“=VLOOKUP(CA∶EFALSE)”“=VLOOKUP(CA∶FFALSE)”。接下来我们就来检验“VLOOKUP”函数的功效。试着在“C”单元格中输入某个学生名比如“赵耀”回车之下我们会发现其下方每一科目的单元格中就自动显示出该生的入学成绩了。类似的函数还有OffsetLookup等。三、函数的应用三、函数的应用常用函数的应用)文本类函数文本类函数主要是处理文本类的单元。主要有:=left():返回左起指定字符数=right():返回右起指定字符数=mid():返回指定字符位置起的指定字符数=len():返回字符串长度=lower():全部小写=upper():全部大写=proper():首字母大写=trim():清除字符串前后的空格=value():将文本形式的数字转化为数值=text():将数值转化为文本=substitute():文本替换函数三、函数的应用三、函数的应用常用函数的应用)文本类函数例:有时候我们可能对某个单元格中字符的数量感兴趣需要计算单元格中的总字数。要解决这个问题除了利用到“SUBSTITUTE”函数的虚拟计算外还要动用“TRIM”函数来删除空格。比如现在A单元格中输入有“howmanywords”字样那么我们就可以用如下的表达式来帮忙:=LEN(SUBSTITUTE(TRIM(A),"",""))该式的含义是先用“TRIM”函数删除其前后可能的空格并且利用“SUBSTITUTE”函数将字符间的空格替换掉创建一个无空格的新字符串然后用长度函数就得出单元格中字符的数量了。三、函数的应用三、函数的应用、在Excel中自定义函数Excel函数虽然丰富但并不能满足我们的所有需要。我们可以自定义一个函数来完成一些特定的运算。例:下面我们就来自定义一个计算梯形面积的函数:)执行“工具→宏→VisualBasic编辑器”菜单命令(或按“AltF”快捷键)打开VisualBasic编辑窗口。)在窗口中执行“插入→模块”菜单命令插入一个新的模块模块。)在右边的“代码窗口”中输入以下代码:FunctionV(a,b,h)V=h*(ab)EndFunction关闭窗口自定义函数完成。以后可以像使用内置函数一样使用自定义函数。提示:用上面方法自定义的函数通常只能在相应的工作簿中使用。三、函数的应用三、函数的应用、在Excel中自定义函数三、函数的应用三、函数的应用、在Excel中自定义函数例:将阿拉伯数字的金额转化为大写金额:Functionvtoc(num)s=Str(Int(num*))Ifnum=Thenl=Elsel=Len(s)dx="零壹贰叁肆伍陆柒捌玖"dw="佰拾万仟佰拾元角分"wz=""Form=ToLen(dw)lwz=wz"/"Mid(dw,m,)NextmFori=Tolzz=Mid(s,i,)sz=Val(zz)wz=wzMid(dx,sz,)Mid(dw,Len(dw)li,)Nextivtoc=wzEndFunction三、函数的应用三、函数的应用、在Excel中自定义函数例:去除几个最大和最小值后的平均值:Functionpj(x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x)ma=:ma=:ma=mi=:mi=:mi=:s=Dimx()x()=x:x()=x:x()=x:x()=x:x()=x:x()=x:x()=x:x()=xx()=x:x()=x:x()=x:x()=x:x()=x:x()=x:x()=xx()=xFori=ToIfx(i)>maThenma=x(i):m=iNextix(m)=x():x()=maFori=ToIfx(i)<miThenmi=x(i):n=iNextix(n)=x():x()=miFori=ToIfx(i)>maThenma=x(i):m=iNextix(m)=x():x()=ma三、函数的应用三、函数的应用、在Excel中自定义函数Fori=ToIfx(i)<miThenmi=x(i):n=iNextix(n)=x():x()=miFori=ToIfx(i)>maThenma=x(i):m=iNextix(m)=x():x()=maFori=ToIfx(i)<miThenmi=x(i):n=iNextix(n)=x():x()=miFori=Tos=sx(i)Nextis=sIfs=Thenpj=""Elsepj=sEndFunction四、在Excel中编程四、在Excel中编程MicrosoftExcel是一款功能非常强大的电子表格软件。它可以轻松地完成数据的各类数学运算并用各种二维或三维图形形象地表示出来从而大大简化了数据的处理工作。但若仅利用Excel的常用功能来处理较复杂的数据可能仍需进行大量的人工操作。但Excel的强大远远超过人们的想象宏的引入使其具有了无限的扩展性因而可以很好地解决复杂数据的处理问题。VBA是从流行的VisualBasic编程语言中派生出来的一种面向应用程序的语言全称为VisualBasicApplication它适用于各种Windows应用程序可以解决各应用程序的宏语言不统一的问题。除此之外使用VBA语言还有如下优点:、VBA是一种通用程序语言通过它不仅可以共享Microsoft相关的各种软件(如Excel、Word、Access)……而且随着其它的一些软件(如大名鼎鼎的AutoCAD)等对VBA的支持这些软件也已进入到了VBA的控制范围、可以将用VBA编写的程序复制到VisualBasic中调试并运行从而实现用VisualBasic来控制有关的应用程序、VBA提供的大量内部函数大大简化了用户的操作。四、在Excel中编程四、在Excel中编程A、通过VisualBasic编辑器编写:可以通过点击“工具→宏→VisualBasic编辑器”打开的编辑环境点击“插入→模块”系统将打开一个代码窗口就可以在这个窗口编写程序了。有了编程环境后我们来开发VBA之旅的第一个程序。在窗口中输入以下代码:SubMyFirstVBAProgram()DimstrNameAsStringDimstrHelloAsStringstrName=InputBox("请输入你的名字:")strHello="你好"strName"!"MsgBoxstrHelloEndSub运行这段代码。运行结果会显示一个对话框输入一些内容后会显示相应的问候语。、如何编写VBA程序?四、在Excel中编程四、在Excel中编程SubMyFirstVBAProgram()DimstrNameAsStringDimstrHelloAsStringstrName=InputBox("请输入你的名字:")strHello="你好"strName"!"MsgBoxstrHelloEndSub、如何编写VBA程序?我们简单看一下这段代码的组成代码第行表示这是一个新的过程名称为“MyFirstVBAProgram”第、行定义了个变量其类型为字符串类型第行调用InputBox这个内置函数并将返回值赋给strName这个变量第行将几个字符串组合成一个新的字符串第行调用MsgBox这个函数显示一个对话框第行表示过程结束。VBA程序由不同的模块组成在模块内部可以定义不同的变量、过程或函数由此组成一个完整的程序。四、在Excel中编程四、在Excel中编程B、通过录制宏来获得代码:绝大多数的Excel操作都可以录制为宏而录制的宏可以作为开发的基础来使用这点也是ExcelVBA开发的一个特点。打开文本文件的代码录制过程为:()选择工具-宏-录制新宏()在录制新宏对话框中选择将宏保存在当前工作薄单击确定开始()进行所需要的操作()停止录制()然后就可以在VBA的代码窗口中找到新录制的宏。、如何编写VBA程序?四、在Excel中编程四、在Excel中编程VBA究竟能做什么更确切地讲它是一种自动化语言,它可以使常用的程序自动化,可以创建自定义的解决方案。此外如果你愿意,还可以将EXCEL用做开发平台实现应用程序。可以利用VBA来实现完成重复性的操作或者EXCEL基本操作所不能完成的功能或者通过VBA所编写的程序自动运行你想完成的工作。下面主要通过一些工作中的实例说明VBA的一些应用。、VBA程序的应用谢谢各位!谢谢各位!

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/38

Excel使用技巧及高级应用

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利