实用办公培训之EXCEL

单击此处编辑母版副标题样式11实用办公技巧培训之——EXCELExcel是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。2Excel2007新增功能新的面向结果的用户界面使您可以轻松地在MicrosoftOfficeExcel中工作。过去，命令和功能常常深藏在复杂的菜单和工具栏中，现在您可以在包含命令和功能逻辑组的、面向任务的选项卡上更轻松地找到它们。新的用户界面利用显示有可用选项的下拉库替代了以前的许多对话框，并且提供了描述性的工具提示或示例预览来帮助您选择正确的选项。为了使您能够在工作表中浏览大量数据，OfficeExcel2007支持每个工作表中最多有100万行和16,000列。内存管理已从MicrosoftOfficeExcel2003中的1GB内存增加到OfficeExcel2007中的2GB。3Excel2007的常用文件扩展工作簿文件的默认扩展名：.xlsx（未启用宏），是基于XML的新的文件格式启用宏的Excel工作簿文件扩展名：.xlsm模板文件扩展名：.xltx默认情况下，可包含三个工作表Excel中的区域基础认识（A2：K2）是多少个单元格呢？我们在文本框区域输入即可获悉。4自定义文本框介绍选中所需编辑文本框，右键选择“设置单元格格式”进行详细设定。或直接在EXCEL功能栏进行概要设定。5奇特的F4键Excel中有一个快捷键的作用极其突出，那就是F4键。作为“重复”键，F4键可以重复前一次操作，在很多情况下起作用，比如在工作表内加入或删除一行，然后移动插入点并按下F4键以加入或删除另一行，根本不需要使用菜单。如何隐藏网格线选择“视图”—勾选“网格线”。如何设置打印区域选中需要打印的区域—页面布局—打印区域—设置打印区域。快速复制上一单元格内容除去常用的复制黏贴，我们可以采用CTRL+’(为英文状态下的单引号).6如何设置对角线在Excel中，有时需要在某个单元格中输入一个对角线，表示该单元格中分数据或者无数据输入，设置方式如下：1.选择设置的单元格，右键单击菜单“单元格”，选择“边框”选项卡。2.在边框预览区域的右下角跟左下角，各有一个对角线按钮，进行选定。3.单击确认。需要注意的是，这种设置对角线的办法只实用于单元格，对区域无效。假如选择一个区域来这样设置，Excel会给每一个单元格都设置对角线，而不能设置全部从区域的对角线。7如何快速给数字加上单位有时我们需要给输入的数值加上单位，少量的我们可以直接输入，而大量的如果一个一个地输入就显得太慢了。1.先将数值输入相应的单元格中2.按住Ctrl键的同时，选取需要加同一单位的单元格3.单击“格式→单元格”命令，打开“单元格格式”对话框4、在“数字”标签中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入单位。5、点击确认即可8如何在单元格内快速换行我们在使用Excel制作表格时经常会遇到需要在一个单元格输入一行或几行文字的情况，如果输入一行后敲回车键就会移到下一单元格，而不是换行。1.输入第一行内容。2.按住ALT+ENTER换行3.光标即可自动转换为下一行9如何冻结窗口有写时候在用做Excel表时，由于行列项目很多，往往信息不能同时显示在屏幕上，给日常的查询和修改输入带来一定困难。用下面的方法可以解决这个问题:单独冻结行或列：首先在表格中单击要冻结行（列）的下边（右边），然后单击“窗口→冻结窗格”命令即可。同时冻结行和列：首先单击所需冻结的行列交点单元格，然后点击“视图→冻结窗格”命令即可。撤销冻结：点击“窗口”菜单，原来“冻结窗口”命令变成“撤消窗口冻结”,单击它即可。10条件格式的运用Excel“条件格式”功能可以根据单元格内容有选择地自动应用格式，它为Excel增色不少的同时，还为我们带来很多方便。1.数据条的应用单元格中颜色条越长，表示单元格中数值越大。112.图标集的应用用图标表示价格的对比趋势；图标范围可在自定义中进行设定。12实用例一：对比功能在我们进行供应商多项价格对比时，我们可以用条件格式进行直观对比。1.选择需对比的数据单元格，然后选择菜单命令“开始—条件格式”，在“条件格式”对话框中选择“自定义规则”。2.选择“使用公式确认要设置格式的单元格”输入“=$E3$D3”.条件格式结合公式的运用133.单击“格式”按钮，打开“单元格格式”对话框，为符合条件的单元格设置格式，例如颜色需求。4.设置完毕单击“确定”按钮，回到原来对话框，再单击“确定”14实用例二：强调突出排名靠前或靠后数值例如我们要给众多报价中，强调显示报价最低的，如何显示?第一种方法：在“项目选取规则”中进行定义。第二种方法：在新建规则中“仅对排名靠前或靠后的数值设定格式”中进行定义。图中公式为实现这一功能的函数15使用例三：让工作表间隔固定行显示阴影当单元格数据行较多，我们为了让显示效果更加醒目，可以让工作表间隔固定行显示阴影。如果我们用手动给单元格加阴影，虽然快捷，但是若增加或减少单元格时，所有的阴影显示会错乱，需要重新手动修改，比较麻烦。我们可以使用公式：“=MOD(ROW(),2)=0”，如果要间隔两行显示阴影则用公式“=MOD(ROW(),3)=0”，其余依次类推。Mod是指函数求余16如何设置筛选1.选择需筛选的项目。2.选择“数据”—“筛选”筛选条件方案1.按单元格内容或图标筛选2.按单元格颜色或数值颜色筛选3.按自定义数值筛选17同时在多个单元格中输入相同内容的技巧我们有时候在制作表格的时候，一张表格内，可能多个单元格的内容一致，我们可以采取技巧，来提高输入效率：常见做法1：在某一个单元格内输入内容，然后按照常规的“CTRL+C”进行复制，然后选中其他表格进行逐个“CTRL+V”粘贴。常见做法2：在某一个单元格内输入内容，然后按照常规的“CTRL+C”进行复制，然后用CTRL选中需填单元格，按CTRL+V”粘贴。推荐方法3：选定需要输入数据的单元格，单元格可以是相邻的，也可以是不相邻的，然后键入相应数据，按“Ctrl+Enter”键即可。优缺点比较：在填写内容错误的情况下，使用方法3可以达到快速修改的目的。18如何设置各文档之间的超链接1、右键选择“超链接”2、选择“本文当中的位置”3、在单元格引用中选择需链接的文档页4、在要显示的文字后填写链接命名5、单击确认。若要修改连接，可右键链接，选择“编辑超链接”19如何设置下拉列表快速输入数据如果某些单元格区域中要输入的数据很有规律，我们希望减少手工录入的工作量，这时就可以设置下拉列表实现选择输入。具体方法:选取需要设置下拉列表的单元格区域，单击“数据→有效性”命令，从“数据有效性”对话框中选择“设置”选项卡，在“允许”下拉列表中选择“序列”，在“来源”框中输入我们设置下拉列表所需的数据序列，如“三仁宝业、寿力、英格索兰”，并确保复选框“提供下拉箭头”被选中，单击“确定”按钮即可。这样在输入数据的时候，就可以单击单元格右侧的下拉箭头选择输入数据，从而加快了输入速度。20如何使单元格数值重新排序一般性排序：在功能区“数据”下选择排序方案我们如果想给多排名的项目标注名次，在修改单元格内容时，保持名次自动排列。可以借助rank命令排序，rank命令是获取某一个单元格在一堆数据中的名次。例如我们现排序C列单元格数值。1、对应需排序的单元格，建立空白备注单元格。2、在空白单元格第一格内输入=rank(C2.C:C)。含义即是C2单元格在C:C中的名次，即C2在C列所有数据中的名次。如果求D2单元格在D列的名次，则公式为=rank(D2,D:D)。3、单击选中备注单元格，然后将鼠标指针移动到单元格的右下角，下拉至需排序的所有单元格并列位置。4、选择所有下拉备注单元格，然后单击“数据”选项卡，再单击升序或者降序图标。5、删掉备注列单元格数据，即可获取C列的升序排序结果。21如何使用EXCEL控件功能EXCEL2007中有两种控件：表单控件和ActiveX控件（控件工具箱）表单控件：设置控件格式和通过指定的宏来使用，只能在工作表中使用。Active控件:通过编写代码、触发事件等在工作表和窗体中进行使用。控件功能启用：1、点击EXCEL左上角的OFFICE按钮。2、选择EXCEL选项3、选择“常用”—勾选“在功能区显示”开发工具“选项卡”4、确认。便可在EXCEL功能栏显示”开发工具“22表单控件的使用鼠标直接在开发工具—插入中选择自己需求的控件进行直接使用，较为简单，在此不做详细介绍。ActiveX控件使用实例：PrivateSubCheckBox1_Click()IfCheckBox1.ValueThenCells(2,D).Value=CheckBox1.CaptionElseCells(2,D).Value=EndIfEndSub23如何锁定指定工作表内容1、全选该工作表（即所有单元格）。2、右键单元格格式→保护，取消锁定前面的√。3、选定你要保护的单元格（可以是单个单元格，也可以是多个单元格），右键单元格格式→保护，在锁定前面打上√。4、选择审阅→保护工作表，输入两次一致的密码确定，保存即可。取消保护方法：选定被保护的单元格，工具→保护→撤消工作表保护。24Excel2007无法在未启用宏的工作簿中保存vb项目使用宏的Excel2007文档在保存的时候提示：25我们如何解决保存问题？在EXCEL2007中，使用了宏的文档不是保存为.xlsx格式的，而是保存为xlsm格式。1、录制宏后，点击左上角的“Office”按钮，再单击弹出菜单中的“另存为”命令。2、我们将“保存类型”改为“启用宏的Excel工作簿”3、再单击“保存”按钮即可。26如果自己的工作表保护密码忘记了，怎么办？Excel的工作表保护是非常脆弱的。下面的VBA代码虽然不能找出工作表保护密码，但可以取消工作表保护。1.按Alt+F11，打开VBA编辑器。2.在“工程”窗口中选择要取消保护的工作表名称，单击菜单“插入→模块”。3.在右侧的代码窗口中输入下列代码：27SubRemove_WorkSheet_Password()DimiAsInteger,jAsInteger,kAsIntegerDimlAsInteger,mAsInteger,nAsIntegerDimi1AsInteger,i2AsInteger,i3AsIntegerDimi4AsInteger,i5AsInteger,i6AsIntegerOnErrorResumeNextIfActiveSheet.ProtectContents=FalseThenMsgBox该工作表没有保护密码！ExitSubEndIfFori=65To66:Forj=65To66:Fork=65To66Forl=65To66:Form=65To66:Fori1=65To66Fori2=65To66:Fori3=65To66:Fori4=65To66Fori5=65To66:Fori6=65To66:Forn=32To126ActiveSheet.UnprotectChr(i)&Chr(j)&Chr(k)&_Chr(l)&Chr(m)&Chr(i1)&Chr(i2)&Chr(i3)&_Chr(i4)&Chr(i5)&Chr(i6)&Chr(n)IfActiveSheet.ProtectContents=FalseThenMsgBox已经解除了工作表保护！ExitSubEndIfNext:Next:Next:Next:Next:NextNext:Next:Next:Next:Next:NextEndSub284.关闭VBA编辑器，回到工作表中，按Alt+F8，打开“宏”对话框，选择“Remove_WorkSheet_Password”，并单击“执行”按钮。稍等片刻工作表保护将被取消。29关于EXCEL常用函数的应用运行部陈文刚培训的常用函数课程非常详细、清晰，大家可在OA下载学习。SRB2010第四季度烟台