Excel使用技巧荟萃.docx

Excel使用技巧荟萃本文供Excel初中级用户参考一、 数据操作二、公式与函数（VLOOKUP函数、SUMPRODUCT函数）三、图表四、数据分析五、宏与VBA（模拟Access报表，双面打印，自动导入表格、多表合并）一、数据操作1. 减少无用击键和点击鼠标次数从左到右：用TAB键，但行尾用ENTER键；从上到下：先选择录入区域，输入、回车.；SHIFT+TAB与TAB相反，SHIFT+ENTER与ENTER相反。2.数字小键盘盲打，大拇指按03.身份证号，帐号等单元格设为文本，或输入前先输入半角单引号4.日期输入，本年度日期只要输入月日，如12-8，当前日期=now()当前日期：CTRL+;当前时间：CTRL+SHIFT+;当前日期时间：CTRL+;空格CTRL+SHIFT+;5.M2,M3,在单元格中选2或3，注意不要选整个单元格，然后设置为上标。或按ALT+178(上标2的ASCII码)、ALT+179(上标3的ASCII码)。分数输入：如三分之一，输入0空格1/36.是ALT+41420，是ALT+41409，也可用右击输入法软键盘数学符号调出。以上使用ASCII码快捷输入，可能与某些输入法冲突，可切换到英文，笔记本电脑要打开NumLock键台式机要用数字小键盘。7.省力录入有规律的数据输入一个单元格后，移动鼠标到单元格右下角变为小十字箭头，可往下拉，如1月份，入字09001等数据。8.批量输入相同数据：先选区域，输入数据、CTRL+ENTER9.快速插入多个单元格：先选一格光标移到右下变为小十字，然后按SHIFT并往右或下拖。方法2：先选几格，光标移到正下变为小十字，然后往右或下拖（相当于剪切粘贴）。快速插入多行（或列）：先选几行（或列）再右击插入快速移动列（或行）：先选几列（或行），光标移到列右（或行下）变为十字箭头，然后SHIFT+鼠标拖动到目标位置即可。行列互换：复制，选择性粘贴/转置。10.单元格格式是CTRL+1键。如入字09001后是入字09093，则可设置单元格格式：自定义类型中输入“入字09”000，后三个0表示缺位用0代替。11.重复一个操作F4键或CTRL+Y，撤销一个操作CTRL+Z12.利用值列表录入。首先建立一个值列表，可以隐藏，数据菜单有效性允许栏设为序列，然后设置来源、提供下拉箭头复选框、忽略空值复选框。如下图13.定位某一数值的单元格，编辑菜单定位或F5（WPS中是CTRL+G），在2007版中是开始查找和替换定位条件。14.选择性粘贴，很重要，主要有粘贴为“值”，“运算”如要某一区数据都乘1000，操作：在某一个输入1000，“复制”，再选那一区数据，“选择性粘贴”“运算”“乘”即可。15.自动打印标题行（或列）：文件/页面设置-工作表选项卡顶端标题行$1:$2照相机命令调出：右击菜单栏/自定义命令选项卡类别：选“工具”，命令：选“照相机” 并拖到工具栏。照相机命令使用：先选“源数据”，按“照相机”按钮，然后鼠标点到目标区域。打印不连续区域：可以使用照相机命令实现。16.精确查找：CTRL+F选项选中单元格匹配复选框。EXCEL默认是模糊查找。CTRL+F选项查找范围中选“值”，可以按最终结果查找。EXCEL默认查找范围是“公式”。17.数据高级模糊查找：用通配符*?和单元格匹配复选框。18.单元格中强制换行：ALT+ENTER批量删除单元格中换行符：查找内容输入：ALT+10（用数字小键盘），单元格匹配复选框为空二、公式与函数1.普通公式=a1+b1 =sum(a1:a10)2.数组公式=average(b1:b10-a1:a10) 意思是(b1-a1)+(b2-a2)+(b10-a10)/10操作步骤：键入=average(用鼠标选择b1:b10区域键入-用鼠标选择a1:a10区域按CTRL+SHFIFT+ENTER组合键另外,将单元格区域转换为常量数组:输入如=D3:E6,选择D3:E6,按F93.命名公式如上图命名a1:a10为Data以后就可这样使用=sum(data)插入菜单名称定义或按Ctrl+F3，输入Total 和 =sum(data)以后就可这样使用=total4.运算符优先级5.单元格应用类型相对引用（默认）a2绝对引用 $a$2 如何设置：选择a2按F4混合引用 $a2 这时a列固定 a$2 这时2行固定6.函数的嵌套不能超过7级7.IF(条件表达式，结果1，结果2) 如果.就.否则8.VLOOKUP(查找值，查找区域，返回结果的列号，查找方式)查找方式为0，是精确查找VLOOKUP(B11,A2:H7,8,0)9.SUMPRODUCT函数（在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。数组参数必须具有相同的维数，否则，返回错误）例子：编写根据部门、人员类别、节假日与否确定某员工销售指标和目标的公式函数分别在两个工作表（规则表、考核明细表）记录考核规则和具体销售情况，其中销售指标根据考核规则表确定。规则表如下：ABCDE1部门类别节假日指标目标2观前店全职是8,000 10,000 3观前店全职否4,000 6,000 4观前店兼职是4,000 5,000 5观前店兼职否2,000 3,000 考核明细表如下：ABCDEFGHIJ1姓名日期销售额部门类别节假日指标目标指标完成目标完成2张三09-4-19704.6 观前店全职否4000600011.2F2单元格为=IF(WEEKDAY(B2)5,是,否)G2单元格为=SUMPRODUCT(规则!D$2:D$20)*(规则!A$2:A$20=D2)*(规则!B$2:B$20=E2)*(规则!C$2:C$20=F2)H2单元格为=SUMPRODUCT(规则!E$2:E$20)* (规则!A$2:A$20=D2)* (规则!B$2:B$20=E2)*(规则!C$2:C$20=F2)公式中4个括号，第一个括号中是取数列，后3个括号中是条件三、图表1.选择正确的图表2.快捷作图表：选择区域，按F11，默认创建在新工作表中3.正常用图表向导按钮：4.如何在图表中增加数据：方法一：在图表上选择绘图区，这时在数据区边界会出现蓝色线，调整这根蓝色线范围就可增加或减少数据方法二：在数据区选择要增加的区域，然后移动鼠标至十字箭头状，拖动到图表中。5.利用图表工具栏中下拉列表和属性按钮，可方便编辑调整图表中各元素参数。6.图表的美化如上图中文字可调小一点（字体中自动缩放复选框为空），中间绘图区背景可删除（点按Delete键），网格线也可删除（点按Delete键）或淡一点（按格式工具栏填充颜色按钮）。数据系列格式边框选无。 以下效果是在数据系列格式按填充效果按钮图片选择图片（填充格式选层叠）而来。（也可先把图片复制到工作表中并选择，按CTRL+C，再选绘图区某个数据柱，按CTRL+V）7. 图表的高级美化步骤：先设置某个数据柱为次坐标轴如下图然后设置某个数据柱图案内部无，选项分类间距中调整数值。另一效果图数据如下:地区计划实际东北区181122西北区156121华南区143143华东区151185西南区101166华中区181127F2输入 =IF(MOD(ROW()+1,3)=0,INDEX(B:B,(ROW()+4)/3),0),然后”拉下去”G2输入=IF(H3-F20,H3-F2,) ,然后”拉下去”H2输入=IF(MOD(ROW(),3)=0,INDEX(C:C,(ROW()+3)/3),0) ,然后”拉下去”I3输入=IF(F2-H30,F2-H3,) ,然后”拉下去”F-I列数据也可直接输入图表向导-柱形图-堆积柱形图-完成在图表上分别选中”增加”和”减少”数据柱,设置:数据标志-“值”复选框。分别选中系列 ”增加”减少”数据柱,设置:图案-内部设为无,边框-自定义-颜色（增加红色，减少绿色）。分别选中系列 ”增加”减少” 数据标志,设置:数字选项卡-自定义-G/通用格式;(这样就不显示0)。选中某一数据柱,设置:选项选项卡-分类间距设为0。分别选中”计划”和”实际”数据柱, 设置:图案选项卡-边框设为”无”。四、数据分析1.排序先选择区域(特别是源表格不规范时)，单击，CTRL+A或CTRL+SHIFT+8（这一步可选）或选择适当区域.数据菜单排序设置主观键字、次关键字、第三关键字如果有超过三个关键字需要排序，可分几轮来排序；越重要的关键字，安排在越靠后的伦次中；某个轮次中，越重要的关键字越优先。Office2007版中，开始排序和筛选自定义排序可一次安排多个关键字排序。特定顺序排序，如总经理、副总经理、经理、组长、员工，可在工具选项自定义序列中输入特定顺序（每项以回车分隔）。然后排序时在排序对话框中按“选项”调用这个特定顺序。在2007版是徽标EXCEL选项常用编辑自定义序列，使用在“开始”项“编辑”“排序和筛选”“自定义排序”，在次序中选自定义序列，调用.按行排序、按汉字笔画顺序（特指微软内定的笔画顺序），都可从排序对话框选项中设置。每隔一行插入一行空行，先添加辅助列输入序号1 2 然后输入1.1 2.1 最后按辅助列排序并删除辅助列。随机排序，先添加辅助列输入=rand()产生0-1的随机数，然后排序。2.分类汇总先分类（即按关键字排序），后汇总。多级分类汇总，先分类（即按关键字优先级排序），然后依次执行分类汇总，优先级别高的先汇总，注意分类汇总对话框中替换当前分类汇总复选框设为空。把分类汇总结果复制出来，用定位（F5），定位条件选可见单元格3.自动筛选数据菜单筛选自动筛选，出现下拉箭头即可单击这个下拉箭头自定义，出现如下图对话框，可设置条件4.高级筛选数据菜单筛选高级筛选，在对话框中设置条件区域和列表区域。在可用作条件区域的区域上方插入至少三个空白行。条件区域必须具有列标签。“与”条件在同一行 ，“或”条件在不同行中。如下图。在表中可用高级筛选提取不重复的记录，如下图。用高级筛选提取两个表中具有相同值的记录，如下图。5.数据透视表数据菜单数据透视表，在2007版中是插入数据透视表将销售人员拖到行区域，数量、销售金额拖到数据区域，右击数据区左上角“数据”/顺序移至列，（在2007版中是“将值移动到列”）。另外可将销售地区拖到行区域或页区域。如下图五、宏与VBA1. 宏是在应用程序中可以自动运行的一连串的功能指令。能够完成大量重复的操作。2.宏安全性设置：工具菜单宏安全性，设为中（以后打开带宏工作簿则会提示是否启用宏），在2007版中是 “Excel 选项”“信任中心”“信任中心设置”“宏设置”类别。3.实例一：如何录制编辑宏工具宏录制宏，（可设置快捷键如CTRL+K），在弹出宏工具栏中选中“相对引用”，然后复制第一个记录到D2-G2单元格，最后光标定位到B7(即第二个源记录上)，最后按“停止录制”。如何编辑宏：按ALT+F11或工具宏宏，“编辑”或VB编辑器可调出VB编辑器，查看宏代码。在上面例子中录制的宏代码首可加Dim k as Long和For K=1 To 4000两行代码，在宏尾加Next一行代码，就可重复刚才操作4000次。如何中断宏的执行：CTRL+Break键。4.使用窗体控件、图像、自定义工具按钮右击菜单栏选择窗体，弹出窗体工具栏，就可添加窗体控件（在2007版中是开发工具插入表单工具），如使用“按钮”到工作表中，则弹出“指定宏”对话框。如使用插入图片，右击图片/指定宏，则弹出“指定宏”对话框。右击菜单栏选择自定义弹出如下对话框，.省略5.使用滚动条右击菜单栏选择窗体，弹出窗体工具栏，（在2007版中是开发工具插入表单工具），使用“滚动条”到工作表中，然后右击滚动条/设置控件格式，如下图，注意不要忘了设置单元格链接。6.VBA，全称Visual Basic for Application，是编程语言的一种。用于Office应用程序进行开发。实例一、巧用Excel函数也能批量打印明信片-模拟Access报表打印Excel中的一个查询函数VLOOKUP，首先建立一个Excel文件，在文件中设计两个表:一个是通讯录，另一个是明信片。因为寄发明信片需要邮编、地址、姓名、职务等，所以先将发送目录的上述数据放到通讯录中。在设置邮政编码时遇到了麻烦，因为Excel中没法进行字间距的设置，打印出来的邮编不能对应到相应的方框中，所以变化的邮政编吗每一位要占一个单元格。然后根据所用明信片的格式输入不变的内容，如祝福语、发信人等信息。再把需要变化的单元格中输入公式(下图圈出的位置)。H1是输入编号的地方。注意H1中的编号最好设置成白色，不然可就打印出来了。H4单元格的公式是“=VLOOKUP(H1,通讯录!A3:G1002,2,FALSE)”，该公式的意思是根据H1单元格的内容，在通讯录的A3到G1002单元格的范围内按照第一列进行垂直查询，把查到相同内容的单元格右侧的第2列（单位名称）单元格的内容显示在H4。同理我们再分别设置好H5、I5、E1、F1单元格的公式。设置结束后，我们只需改变H1中的编号，明信片的其它信息就会根据通讯录上的内容自动显示出来了。然后对好版，一张张漂亮的明信片就打印出来了。如需自动打印全部明信片，可在明信片工作表设置打印区域，然后在打印区域外增加两个命令按钮一个是“下一个”按钮，另一个是“全部打印”按钮。Private Sub 全部打印_Click() 注:代码中J1相当于图片中H1，存储序号用Dim i As IntegerFor i = 1 To Range(J3).Value 遍历明细表序号字段的所有值，Range(J3)应存储明细表序号字段最后一个值Range(J1).Value = i Range(J1)单元格存储明细表序号字段的值ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 打印 从2003版中宏录制ExecuteExcel4Macro PRINT(1,1,2,TRUE,FALSE) 打印 2007版Next iEnd SubPrivate Sub 下一个_Click() 浏览下一个人员信息Range(J1).Value = Range(J1).Value + 1 Range(J1)单元格存储明细表序号字段的值End Sub实例二：为Excel2007添加一个“双面打印”按钮一、展开“开发工具”选项卡要在Excel中实现“双面打印”，就要用到其内嵌的VBA功能，因此，需要先展开“开发工具”选项卡。启动Excel2007，单击左上角徽标“Excel选项”选项，先在左侧选中“个性化设置”选项，然后在右侧“使用Excel时采用的首选项”下面选中“在功能区显示开发工具选项卡”，确定返回。二、生成“个人宏工作簿”为了让“双面打印”按钮对所有工作簿都有效，我们需要将其保存在“个人宏工作簿”中。第一次使用“宏”这个功能时，需要先让软件生成一个隐藏的“个人宏工作簿”。切换到“开发工具”选项卡中，单击其中的“录制宏”按钮，打开“录制新宏”对话框(如图3)。输入一下宏名称(如“smdy”)，按“保存在”右侧的下拉按钮，在随后出现的下拉列表中，选择“个人宏工作簿”选项，确定进入“宏”录制状态。不需要进行任何操作，直接单击“开发工具”选项卡中的“停止录制”按钮，系统会自动生成一个隐藏的“个人宏工作簿”。特别提醒:以后需要在“个人宏工作簿”中编辑宏时，就不需要再进入此步操作了。三、编辑“双面打印”代码单击“开发工具”选项卡上的“Visual Basic”按钮，进入VBA编辑状态(如图)特别提醒:按“Alt+F11”组合键，即可快速进入VBA编辑状态。在左侧“工程资源管理器”中，展开“VBAProject(PERSONAL.XLSB)”选项(这就是“个人宏工作簿”)，双击其中的“模块1”，然后用下述代码替换右侧编辑区中的原有代码:Sub smdy()On Error Resume Nextx = ExecuteExcel4Macro(get.document(50)For i = 1 To Int(x / 2) + 1ExecuteExcel4Macro PRINT(2, & 2 * i - 1 & , & 2 * i - 1 & ,1,2,TRUE,FALSE)Next iMsgBox 请将打印纸反向装入打印机中, vbOKOnly, 打印另一面For j = 1 To Int(x / 2) + 1ExecuteExcel4Macro PRINT(2, & 2 * j & , & 2 * j & ,1,2,TRUE,FALSE)Next jEnd Sub输入完成后，关闭VBA编辑窗口返回到Excel编辑状态。实例三、自动导入当前Excel表格文件夹中的Excel表格(限文件中第一个工作表)在工作簿首页A1输入序号，B1输入名称，并增添一个导入电子表格命令按钮。Private Sub CommandButton1_Click() 导入电子表格按钮的代码，.xlsx文件不可导入Dim Sh As Worksheet, MyName$, n%Application.DisplayAlerts = False 值False,宏运行时 Microsoft Excel 不显示特定的警告和消息Application.ScreenUpdating = False 值False,关闭屏幕更新可加快宏的执行速度,但将看不到宏的执行过程If ThisWorkbook.Sheets.Count 1 ThenIf MsgBox(重新导入报表将删除原来报表，继续吗？ , 52, 警告) = 7 Then Exit SubEnd IfOn Error Resume NextFor Each Sh In Worksheets 删除工作表If Sh.Name ActiveSheet.Name Then 如工作表名为当前活动工作表则不删除Sh.DeleteEnd IfNextn = 1MyName = Dir(ThisWorkbook.Path & *.xls)Range(a2:b65536).ClearContents 清除a、b列内容，除了A1、B1单元格Range(a2:b65536).Hyperlinks.Delete 删除a、b列超链接，除了A1、B1单元格Do While MyName If MyName ThisWorkbook.Name Then Workbooks.Open ThisWorkbook.Path & & MyName ActiveWorkbook.Sheets(1).Copy After:=ThisWorkbook.Sheets(n) 复制工作表 Sheet(1)，并将其放置在工作表 Sheet(n)之后。 n = n + 1 ThisWorkbook.Sheets(n).Name = Left(MyName, InStr(MyName, .) - 1) 用源文件名为新增工作表取名 在当前工作表中为新增的电子表格增加一行，在Range(a & n)单元格存储工作表的序号 Range(a & n) = n - 1 在当前工作表中为新增的电子表格增加一行，在Range(b & n)单元格存储工作表的名称 Me.Hyperlinks.Add Range(b & n), Address:=, SubAddress:= & ThisWorkbook.Sheets(n).Name & !A1, ScreenTip:=ThisWorkbook.Sheets(n).Name, TextToDisplay:=ThisWorkbook.Sheets(n).Name 增加一行后在这一行添加链接 ActiveSheet.Hyperlinks.Add ActiveSheet.Range(p1), Address:=, Su