5Excel操作技巧2.ppt

《5Excel操作技巧2.ppt》由会员分享，可在线阅读，更多相关《5Excel操作技巧2.ppt（47页珍藏版）》请在三一办公上搜索。

1、Excel 操作技巧,Luo ping,Excel 操作技巧,Excel表头斜线设置秘技用VBA实现Excel打印成绩通知单为Excel加装快捷的公式保护器用Excel和VBA轻松实现桌签批量打印Excel轻松提取网上数据,Excel表头斜线设置秘技,技术要点：使用“单元格格式”对话框中的“边框”选项卡设置斜线，使用“对齐”选项卡结合空格键调整文字位置。具体操作步骤如下。1.单击 工作表的的A1单元格，输入斜线表头的内容，比如本例中有两个标题“项目名称”和“日期”，先输入“项目名称”，然后按快捷键 Alt+回车键，这样可以在单元格内向下换行，再按 Alt+回车键一次，输入“日期”。2.按Ctr

2、l+回车键，这样可以在不离开该单元格的情况下选中该单元格。这时的单元格应如图1所示。,图1 选中A1单元格,3.按快捷键Ctrl+1，打开“单元格格式”对话框。(如果在单元格上单击右键，然后从弹出的快捷菜单中选择命令“设置单元格格式”，也能打开该对话框，但使用快捷键会更快一些。)4.在“单元格格式”对话框中，单击“边框”选项卡，然后单击如图2所示的左斜线按钮。,图2 单击左斜线按钮,5.在“单元格格式”对话框中，单击“对齐”选项卡，将文本的水平和垂直对齐方式都设置为“两端对齐”，如图3所示。设置完毕，单击“确定”按钮，可以看到如图4所示的效果。,图3 设置单元格对齐方式,图4 出现斜线,6.观

3、察表头中的文本，显然项目名称的位置不是很合适。双击A1单元格，将插入点定位到“项目名称”左边，并按两次空格键，让“项目名称”的位置向右一些。按Ctrl+回车键离开编辑状态并选中A1单元格，如果发现“项目名称”换行了，可以通过调整该列的宽度将其调整到合适为止，同样可以调整行宽，使表头的大小变得合适。当然，也可以改变表头中文本的大小。最后我们可以得到如图5所示的表头斜线效果。,图5 最终的表头斜线效果,用VBA实现Excel打印成绩通知单,准备工作 为便于理解、记忆，将工作表的名称分别更改为“考试成绩”、“通知单”。在“考试成绩”工作表中是每个学生的考试成绩A1至K64，在“通知单”工作表中建立一

4、个空白表格，作为成绩通知单模板，如图所示。,编写代码 1.进入VB编辑环境，依次点击“工具宏Visual Basic 编辑器”。2.定义触发事件。鼠标双击“工程”窗体中的“通知单”工作表，窗体右侧就出现了对应于该工作表的一些事件。选择响应“激活工作表”动作的事件activate，在窗体右侧的代码区域就出现了worksheet_activate()过程。接下来要做的就是在这个过程中添加代码了。,3.为事件添加代码如下：Private Sub Worksheet_Activate()Dim i As Integer 循环变量 For i=1 To 100 If Sheet1.Cells(i,1)=

5、“”Then 如果“考试成绩”工作表的考号中没有内容，则退出循环 Exit For End If sbegin=(i-1)*5+1 send=i*5 dbegin=i*5+1 dend=(i+1)*5 确定复制、粘贴“通知单”格式区域的起止行，5为复制区域的行数,Sheet2.Range(Cells(sbegin,1),Cells(send,11).Copy _ Destination:=Sheet2.Range(Cells(dbegin,1),Cells(dend,11)将(Cells(sbegin,1),Cells(send,11)区域中的格式拷贝到(Cells(dbegin,1),Cel

6、ls(dend,11)Cells(sbegin+3,1).Value=Sheet1.Cells(i+1,1).Value Cells(sbegin+3,2).Value=Sheet1.Cells(i+1,2).Value Cells(sbegin+3,3).Value=Sheet1.Cells(i+1,3).Value Cells(sbegin+3,4).Value=Sheet1.Cells(i+1,4).Value Cells(sbegin+3,5).Value=Sheet1.Cells(i+1,5).Value Cells(sbegin+3,6).Value=Sheet1.Cells(i+

7、1,6).Value Cells(sbegin+3,7).Value=Sheet1.Cells(i+1,7).Value Cells(sbegin+3,8).Value=Sheet1.Cells(i+1,8).Value,Cells(sbegin+3,9).Value=Sheet1.Cells(i+1,9).Value Cells(sbegin+3,10).Value=Sheet1.Cells(i+1,10).Value Cells(sbegin+3,11).Value=Sheet1.Cells(i+1,11).Value 将“考试成绩”工作表的数据填入“通知单”相应位置 Next i End

8、 Sub 检验成果 进入工作簿，按下“通知单”工作表，即可得到所有学生的成绩通知单了（如图2）。,图2,为Excel加装快捷的公式保护器,在Excel中出于保密需要，通常不希望使用者查看和修改公式。你可以利用Excel锁定、隐藏和保护工作表的功能，把公式隐藏和锁定起来。但如果按照常规方法设置会比较麻烦，而利用Excel的宏，在工具栏上添加一个“保护公式”的按钮，需要隐藏和锁定公式时，只要轻轻一按即可完成保护。制作宏 为了使此宏对所有工作簿有效，需要将其保存到“个人宏工作簿”中。,第一步:执行“工具宏录制新宏”命令，打开“录制新宏”对话框，见图1所示。,第二步:单击右侧“保存在”下拉按钮，在随后

9、出现的下拉列表中，选择“个人宏工作簿”选项，确定进入录制状态。第三步:单击“停止录制”工具栏上的“停止录制”按钮，退出录制状态。注意:前面的操作是为了在Excel中添加“个人宏工作簿”。第四步:按下AltF11组合键进入VBA编辑状态(见图2)，在左侧依次展开“VBAProject(PERSONAL.XLS)”、“模块”选项，双击下面的“模块1”。,第五步:用下面的代码替换右侧编辑区域中的代码:Sub 保护公式()ActiveSheet.Unprotect(123456)解除对工作表的保护Cells.Select 选中整个表格Selection.Locked=False 解除锁定Selecti

10、on.SpecialCells(xlCellTypeFormulas,23).Select 选中包含公式的单元格Selection.Locked=True 添加锁定Selection.FormulaHidden=True 添加隐藏ActiveSheet.Protect(123456)保护工作表，并设置密码ActiveSheet.EnableSelection=xlUnlockedCells 让锁定单元格不能选中,以下代码用于保存文件If Left(ActiveWorkbook.Name,4)=Book Then y=InputBox(请输入保存文件名称:)z=InputBox(请输入文件保存路

11、径:)ChDir z ActiveWorkbook.SaveAs Filename:=z&y&.xls“Else ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path&ActiveWorkbook.Name End If End Sub 输入完成后，关闭VBA编辑窗口返回。,小提示上述代码中，“保护公式”为宏名称，在实际输入时可以更换为其他名称。代码中，英文单引号及其后面字符是对代码的解释，在实际输入时可以不输入。代码中的密码(“123456”)请根据实际需要修改(需要保持前后的一致)。添加按钮在工具栏上添加一个宏按钮，方便随时调用。,第一步:

12、执行“工具自定义”命令，打开“自定义”对话框，见图3所示。,图3,第二步:切换到“命令”标签下，在“类别”下面选中“宏”选项，然后将右侧“命令”下面的“自定义按钮”选项拖到工具栏上合适位置上。第三步:右击刚才添加的按钮，在随后弹出的快捷菜单中,选择“总是只用文字”选项，并将“命名”后面的字符修改为“保护公式”(见图4)。,图4,第四步:再次右击上述按钮，在随后弹出的快捷菜单中，选择“指定宏”选项，打开“指定宏”对话框(见图5)，选中刚才制作的宏“保护公式”，确定返回，并关闭“自定义”对话框。,以后，工作表中的公式编辑完成后，单击一下工具栏上的“保护公式”按钮，包含公式的单元格全部被锁定，不能进

13、行选中、编辑和查看等操作，安全可靠。,用Excel和VBA轻松实现桌签批量打印,用Excel来制作桌面标签（以下简称桌签）确实是一个比较新颖的方法，不过要是同时制作很多桌签也还是比较费时的。下面介绍一种用VBA轻松实现桌签批量打印的方法。一、准备工作启动Excel2003(其他单元格也可以)，执行“工具自定义”命令，打开“自定义”对话框。在“命令”标签中，选中“类别”下面的“工具”选项，然后在“命令”下面找到“照相机”选项，并将它拖到工具栏合适位置上。,在Sheet1工作表中(最好将文档取名保存一下)，仿照图1的样式，在B列相应的单元格中输入需要打印桌签的名称，并在A1中输入一个名称。,图1,

14、在C1单元格(也可以是其他单元格)中输入公式:=COUNTA(B:B)，用于统计所要打印的桌签数目。选中A1单元格，设置字符居中对齐，并设置一种适合用作桌签的字体(如魏碑体)。二、制作桌签选中A1单元格，单击一下工具栏上的“照相机”按钮，再切换到“Sheet2”工作表中，单击一下鼠标，即可得到一张A1单元格的照片(图2)。,图2,注:这种照片不同于普通图片，它与A1单元格中的数据建立了链接，随A1单元格中的字符改变而改变。选中“照片”，执行“格式图片”命令，打开“设置图片格式”对话框，在“颜色与线条”标签中，将“线条”设置为“无线条颜色”，点击“确定”返回。根据桌签底座的尺寸，将“照片”调整至

15、合适大小，并将它定位到页面合适位置上。将上述“照片”复制一份，在“设置图片格式”对话框的“大小”标签中，将它“旋转180”，并将它定位到页面合适位置上(效果参见图2)。,三、编制宏程序,按下“Alt+F11”组合键，打开“Visual Basic编辑器”(图3)，在右侧的“工程资源管理器”区域中，选中“VBAProject(桌签.xls)”选项，执行“插入模块”命令，插入一个模块(模块1)。,双击“模块1”，展开右侧的代码编辑区，将下述代码输入其中:Sub 桌签()建立一个名称为“桌签”的宏。For i=1 To Sheet1.Cells(1,3)设立一个循环:开始值为1，结束值为Sheet1

16、工作表C3单元格内的值(即桌签数目)。Sheet1.Select 选中Sheet1工作表。Cells(1,1).Value=Cells(i,2).Value 依次将B列的桌签字符调入A1单元格中。,Columns(A:A).Select 选中A列。Selection.Columns.AutoFit 将A列设置为“最合适的列宽”，这样让字符不同的桌签自动适应“照片”的大小。Sheet2.Select 选中Sheet2工作表。ActiveWindow.SelectedSheets.PrintOut执行一下打印操作，打印出一张桌签。Next 进入下一个循环。Sheet1.Select 桌签全部打印完

17、成后，选中Sheet1工作表。,Range(B1).Select 选中B1单元格，等待下一次修改字符。ActiveWorkbook.Save 保存当前工作簿文档。Application.Quit 退出Excel。End Sub 宏的结束符号。注:上述代码中英文单引号及其后面的字符是对代码的注释，可以不输入。宏的结束符号上面的4行代码不输入不影响桌签的打印。输入完成后，关闭“Visual Basic编辑器”窗口。,四、添加按钮,切换到Sheet1工作表中，执行“视图工具栏窗体”命令，展开“窗体”工具栏，单击工具栏上的“按钮”按钮，在工作表中拖拉出一个按钮，此时系统弹出“指定宏”对话框(图4)，选

18、中刚才编制的“桌签”宏，确定返回。,2.将按钮上的文字修改为“打印桌签”，再调整好大小，并定位在工作表合适位置上(参见图1)。以后需要打印桌签时，只要将相应的字符输入到B列下面的单元格中，然后按下“打印桌签”按钮，就一切OK了。,Excel轻松提取网上数据,搞网上信息采集工作，最头疼的便是从网页上一次次很枯燥地进行数据表格的复制，而且在复制过来之后还要进行很多修改，不但麻烦而且也很浪费时间，工作效率大打折扣。这时我们不妨用功能强大的Excel来试着解决一下问题。,对于比较规范的表格数据，我们完全可以在表格页面上点击右键，选择导出到Microsoft Office Excel（图1），此时系统会

19、自动打开Office Excel，进行数据加载。这个过程仅需要几秒钟就把数据加载进来（图2）。如果你认为数据比较适合你的编辑要求，那么就可以直接保存了。否则，也可以做一下适当的修改，因为在表格处理方面，Excel要大大优于word。,图1,在网上表格或数据采集这一点上，Excel往往是较为智能化的，它在进行数据采集与加载时，只加载表格固定区域内的数据，而不是把整个网页都加载进来。这一点我试过很多次，都是很听话的，请看图片2的效果。,图2,当然，在网页中也有一些不太规范的数据与表格，这样的数据，Excel处理起来，稍稍有一点难度，不过只要熟悉Excel的操作功能的话，还是可以轻松搞定的。先看一下这个页面（图3），,图3这种不规范的页面数据，如果让Excel处理起来就会出现这样的结果（图4），看着感觉很乱，所有的东西都错位了，一般的人会感到速手无策的。主要原因就是多出了文件数据的开头与结尾。,图4,不过，只要我们删除文件上下两头的不规范的区域，剩下的这些数据不就变得好处理了吗？这时，我们再执行一下菜单：数据分列下一步，这样就可以把不规范的数据变得规范了。这是处理之后的结果（图5）,对于那些本来就比较规范的表格，就简单得多了，只要执行导出加载略修保存就可以了。,