Excel实战技巧教程ppt课件.ppt

PID/DingjpExcel实战技巧教程实战技巧教程1基本功能选择性粘贴技巧快速填充技巧活用条件格式名称的奥妙数据有效性绝技Summary数据分析自动筛选与高级筛选数据透视表使用常用函数技巧“&”的作用绝对引用、混合引用、相对引用的概念VLOOKUP函数IF 函数COUNTIF函数,SUMIF函数VBA基础-入门篇2基本功能篇基本功能篇3粘贴数值图02图03图04基本功能基本功能-选择性粘贴选择性粘贴将含有公式的数据区域进行复制，然后鼠标移动到需要粘贴的单元格，点鼠标右键,选择“选择性粘贴”选项在粘贴选项内选择“数值”，显示的为转换后的没有公式的数据4粘贴格式基本功能基本功能-选择性粘贴选择性粘贴5转置基本功能基本功能-选择性粘贴选择性粘贴6快速填充技巧快速填充技巧 数列或公式的填填充数列或公式的填填充数列或公式的填填充数列或公式的填填充图07图08图091.数列填充（图数列填充（图7-9）A)点击数列表格的右下角，竖直或者水平拉拽至所需部位点击数列表格的右下角，竖直或者水平拉拽至所需部位B)当左侧存在数列，当左侧存在数列，双击双击数列或公式的右下角，自动向下填充直至左侧为空格数列或公式的右下角，自动向下填充直至左侧为空格C)直接拉拽数列或公式，然后再选择填充方式直接拉拽数列或公式，然后再选择填充方式2.公式的填充公式的填充A)点击公式表格的右下角，竖直或者水平拉拽至所需部位点击公式表格的右下角，竖直或者水平拉拽至所需部位B)当左侧存在数列，当左侧存在数列，双击双击公式的右下角，自动向下填充直至左侧为空格公式的右下角，自动向下填充直至左侧为空格C)直接拉拽数列或公式，然后再选择填充方式（图直接拉拽数列或公式，然后再选择填充方式（图10）图107活用条件格式活用条件格式1I.选择所需标识的数据部分选择所需标识的数据部分II.选择选择 格式格式-条件格式条件格式III.根据所需档位进行分档设置根据所需档位进行分档设置附注：最多可进行三种颜色标识附注：最多可进行三种颜色标识 按照数值大小分类按照数值大小分类按照数值大小分类按照数值大小分类图11图12图138活用条件格式活用条件格式2选择数据区域的第一个有效数值单元格选择数据区域的第一个有效数值单元格选择选择 格式格式-条件格式，条件格式，出现图出现图15界面，界面，A.图图15中的位置中的位置1选择选择“公式公式”B.位置位置2手动输入公式手动输入公式“countif（A:A,A2）1”C.点击位置点击位置3进行满足该条件的单元格格式设置进行满足该条件的单元格格式设置(图图16为设置界面为设置界面),设置完毕后点确定设置完毕后点确定D.设置完成一个单元格后点击工具栏上的格式刷设置完成一个单元格后点击工具栏上的格式刷“”,然后将整个工作区域都进行格式复制然后将整个工作区域都进行格式复制E.完成后效果图见图完成后效果图见图17根据所需档位进行分档设置根据所需档位进行分档设置附注：最多可进行三种颜色标识附注：最多可进行三种颜色标识 标记重复的数据标记重复的数据标记重复的数据标记重复的数据图14图17图15图169名称的奥妙名称的奥妙1A.工具栏选择工具栏选择 插入插入-名称名称-定义，出现图定义，出现图18界面界面B.在位置在位置1输入自己定义的名称，比如输入自己定义的名称，比如“CO”；C.在位置在位置2输入需要引用的内容，比如输入需要引用的内容，比如“人民邮电出版社人民邮电出版社”，然后，然后“添加添加”D.在在EXCEL单元格中输入公式单元格中输入公式“=CO”，则显示的结果为，则显示的结果为“人民邮电出版社人民邮电出版社”在名称中使用常量在名称中使用常量在名称中使用常量在名称中使用常量图18图1912在名称中使用函数在名称中使用函数在名称中使用函数在名称中使用函数A.重复上面重复上面A，B步骤，将上面步骤，将上面B步骤的名称定义为步骤的名称定义为“Tax”；B.位置位置2栏位输入栏位输入“=5%”；C.在在EXCEL单元格中可直接将原来公式单元格中可直接将原来公式“=5%*100”使用公式使用公式:”=Tax*100”代替代替10名称的奥妙名称的奥妙2A.选中数据区域选中数据区域,将图将图20的位置的位置1输入自己定义的名称输入自己定义的名称”Myrange”;B.后续在用户编辑任何单元格或者后续在用户编辑任何单元格或者sheet时时,只要在位置只要在位置1栏位下拉按钮选择栏位下拉按钮选择”Myrange”,即可选中即可选中该区域该区域 附注：图标附注：图标,数据透视表的数据源部分可用数据透视表的数据源部分可用”Myrange”来替代常规的区域选择来替代常规的区域选择(图图22)固定区域固定区域固定区域固定区域图20图211图2211数据有效性绝技数据有效性绝技用途用途:限定用户在该单元格的内用只可以进行下拉选择，避免由于用户不同导致数据统计的差异限定用户在该单元格的内用只可以进行下拉选择，避免由于用户不同导致数据统计的差异A.在合适的位置预先输入允许用户选择的源数据在合适的位置预先输入允许用户选择的源数据(图图23)B.选中用户操作单元格选中用户操作单元格,在工具栏上选择在工具栏上选择数据数据-有效性有效性,出现出现(图图24)选项框选项框a.位置位置A选择选择”序列序列”b.位置位置B所有选项的复选框打勾所有选项的复选框打勾c.位置位置C为定义的数据范围为定义的数据范围,完成后点完成后点”确定确定”C.用户操作单元格显示结果如用户操作单元格显示结果如(图图25)在单元格中创建下拉列表在单元格中创建下拉列表在单元格中创建下拉列表在单元格中创建下拉列表图23图24ABC图2512数据分析篇数据分析篇13自动筛选与高级筛选自动筛选与高级筛选1 自动筛选中关于自定义选项的运用自动筛选中关于自定义选项的运用自动筛选中关于自定义选项的运用自动筛选中关于自定义选项的运用 用途用途:筛选特定条件的数据筛选特定条件的数据A)选中含有数据的单元格选中含有数据的单元格,工具栏选择工具栏选择数据数据-筛选筛选-自动筛选自动筛选,数据区表头出现下拉键头数据区表头出现下拉键头,样式见样式见(图图26);B)选择需要进行条件筛选单元格的下拉键头选择需要进行条件筛选单元格的下拉键头,选择选择”自定义自定义”选项选项(图图27)C)设置自定义选项框设置自定义选项框(图图28)内条件内条件,完成后确定即可完成后确定即可图26图27图2814自动筛选与高级筛选自动筛选与高级筛选2 高级筛选高级筛选高级筛选高级筛选用途用途:显示不重复记录显示不重复记录A.选择数据区域选择数据区域,数据数据-筛选筛选-高级筛选高级筛选(图图29),”选择不重复记录选择不重复记录”选项打勾即可选项打勾即可用途用途:多条件复合筛选多条件复合筛选 1.在空白单元格定义筛选条件表格在空白单元格定义筛选条件表格(图图30),定义条件的表头必须与数据区域的表头一致定义条件的表头必须与数据区域的表头一致,条件定义顺条件定义顺序序:从上到下为从上到下为”OR”关系关系,从左到右为从左到右为”AND”关系关系2.选择数据区域选择数据区域,打开图打开图29界面界面,A栏位选择数据区域栏位选择数据区域,B栏位选择条件区域所在的位置栏位选择条件区域所在的位置(图图30),确定即确定即可可 附注附注:条件表格与待分析数据表格必须在一个条件表格与待分析数据表格必须在一个sheet内内,如两张表不在一个如两张表不在一个sheet,请选择请选择”将筛选结果将筛选结果复制到其他位置复制到其他位置”选项，最后选择选项，最后选择“复制到复制到”的目标单元格，其它操作方法同的目标单元格，其它操作方法同1，2图29图30AB15数据透视表使用数据透视表使用 数据透视表数据透视表数据透视表数据透视表用途用途:对不同的条件进行求和、计数等统计操作，数据透视表是交互式报表，可快速合并和比较大量数对不同的条件进行求和、计数等统计操作，数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据A.选中数据区域内任意单元格选中数据区域内任意单元格,数据数据-数据透视表和数据透视图数据透视表和数据透视图-出现数据透视向导一出现数据透视向导一,直接点直接点”下一下一步步”-向导二界面直接点向导二界面直接点”下一步下一步”-向导三界面选择向导三界面选择”布局布局”-出现图出现图31界面界面B.图图31界面上，将右边的字段按钮拖到左边图上，设置结束后，点击界面上，将右边的字段按钮拖到左边图上，设置结束后，点击“完成完成“即可，最终界面间图即可，最终界面间图33附注：双击数据区域的字段，可选择数据计算方式，例：求和、计数、最大值等，见图附注：双击数据区域的字段，可选择数据计算方式，例：求和、计数、最大值等，见图32 图31图32图3316常用函数技巧篇常用函数技巧篇17“&”的作用的作用&-将将将将多多多多个单元格个单元格个单元格个单元格的数据的数据的数据的数据串联串联串联串联起来起来起来起来A)=A1&C3，直接串联两个或者多个数据，直接串联两个或者多个数据B)=A2&“-”&B2，串联中加入，串联中加入“”中间的符号中间的符号图34图35&-引用单元格内的数据引用单元格内的数据引用单元格内的数据引用单元格内的数据A)=indirect(“C”&D8)，取，取C（D8）格内的值）格内的值B)=indirect(“C”&D8+1)，取，取C（D8）+1 格内的值格内的值18绝对引用、混合引用、相对引用的概念绝对引用、混合引用、相对引用的概念 绝对引用绝对引用绝对引用绝对引用在多个公式中使用同一个单元格的数值在多个公式中使用同一个单元格的数值在多个公式中使用同一个单元格的数值在多个公式中使用同一个单元格的数值A)表现形式表现形式:$A$1，字母数字前都加入，字母数字前都加入”$”符号符号图36图37 混合引用混合引用混合引用混合引用 部分单元格的数值随着引用位置的变化而变化部分单元格的数值随着引用位置的变化而变化部分单元格的数值随着引用位置的变化而变化部分单元格的数值随着引用位置的变化而变化A)表现形式表现形式:A$1 或者或者$A1，字母或者数字前加入，字母或者数字前加入”$”符号符号 相对引用相对引用相对引用相对引用 全部单元格的数值随着引用位置的变化而变化全部单元格的数值随着引用位置的变化而变化全部单元格的数值随着引用位置的变化而变化全部单元格的数值随着引用位置的变化而变化A)表现形式表现形式:A1图38附注：按附注：按F4可在不同引用方式间转换可在不同引用方式间转换 19Vlookup 函数函数 VLOOKUP-VLOOKUP-将两组（多组）数列的数据对应起来将两组（多组）数列的数据对应起来将两组（多组）数列的数据对应起来将两组（多组）数列的数据对应起来语法语法语法语法:VLOOKUPVLOOKUP (lookup_value,(lookup_value,table_array,table_array,col_index_num,col_index_num,range_lookuprange_lookup )参数含义:Lookupvalue:为需要在数组第一列中查找的数值,可以为数值、引用或文本字符串Table_array:为需要在其中查找数据的数据表,可以使用对区域或区域名称的引用Col_index_num:为table_array中待返回的匹配值的列序号。为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数VLOOKUP返回错误值值#VALUE!；如果col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!。Range_lookup选择false附注：附注：1.在进行公式拉拽的时候，可将在进行公式拉拽的时候，可将Table_array的的F5:G7改为改为$F$5:$G$7以保证数据区域不变以保证数据区域不变 2.如果存在重复数据如果存在重复数据,系统只会显示第一个数据的结果系统只会显示第一个数据的结果图3920IF 函数函数 IF IF 判断一个条件是否满足判断一个条件是否满足判断一个条件是否满足判断一个条件是否满足,如果满足如果满足如果满足如果满足,返回一个值返回一个值返回一个值返回一个值,如果不满足则返回另一个值如果不满足则返回另一个值如果不满足则返回另一个值如果不满足则返回另一个值语法语法语法语法:IF(logical_test,value_if_true,value_if_false)IF(logical_test,value_if_true,value_if_false)例例1:公式公式 IF(A2B12,Over Budget“,OK)含义为：如果含义为：如果A12大于大于B12，则公式将显示，则公式将显示“Over Budget“，否则公式显示，否则公式显示”OK”图40图41参数含义:Logical_test表示计算结果为TRUE或FALSE的任意值或表达式.Value_if_truelogical_test为TRUE时返回的值。Value_if_falselogical_test为FALSE时返回的值。21Countif 函数函数,Sumif 函数函数 计算区域中满足给定条件的单元格的计算区域中满足给定条件的单元格的计算区域中满足给定条件的单元格的计算区域中满足给定条件的单元格的个数个数个数个数语法语法语法语法:COUNTIFCOUNTIF (range,(range,criteria)criteria)图42Countif函数的运用参数含义:Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本 根据指定条件对若干单元格根据指定条件对若干单元格根据指定条件对若干单元格根据指定条件对若干单元格求和求和求和求和。语法语法语法语法:SUMIF(range,criteria,sum_range)SUMIF(range,criteria,sum_range)参数含义:Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件。Sum_range是需要求和的实际单元格。图43Sumif函数的运用22VBA基础基础-入门篇入门篇23summary目的了解Excel VBA建立所有学员使用VBA的能力Excel VBA入门VBA热身宏的录制与编辑Visual Basic简介解读&简化程序码排疑解难使用Help24Excel VBA入门入门VBA热身宏的录制与编辑排疑解难使用Help解读&简化程序码Visual Basic简介25VBA热身热身实际范例由以上范例可看出使用VBA的优点减少重复工作利用程序达成较负责的控制，简化人力并减少错误发生26Excel VBA入门入门VBA热身宏的录制与编辑排疑解难使用Help解读&简化程序码Visual Basic简介27宏的录制与编辑宏的录制与编辑宏的录制(1)28宏名称的一个字必须是英文字母或者中文字不能有空白字源设定执行宏的快捷键宏的录制与编辑宏的录制与编辑(续续续续)宏的录制(2)29宏的录制与编辑宏的录制与编辑(续续续续)在此状态下，所用操作在此状态下，所用操作的动作会被录制成宏的动作会被录制成宏按下停止键完成录制按下停止键完成录制宏的录制(3)30宏的录制与编辑宏的录制与编辑(续续续续)Visual Basic编辑窗口工程资源管理器属性窗口边界指示区程式碼視窗物件清单全模块视图钮过程视图纽程序事件清单31宏的录制与编辑宏的录制与编辑(续续续续)32宏的录制与编辑宏的录制与编辑(续续续续)使用程式码窗口设定自己喜欢的窗口模式工具工具/选项选项33执行宏执行宏工具工具/宏宏/宏宏/执执行行or：直接在代码窗口：直接在代码窗口点击运行按钮点击运行按钮34指定宏指定宏-指定到工作列指定到工作列Excel 工具栏/工具/自定义123将自定义按钮拖至工具栏上35指定宏指定宏指定到工作表内的对象指定到工作表内的对象36保存与关闭保存与关闭保存在VB编辑器中保存后离开直接在Excel中存檔导入/导出宏*.frm：窗口*.bas：程序代码*.cls：类别Excel VBA37宏录制与编辑宏录制与编辑-练习练习录制一个宏利用VB编辑器浏览并编辑程序代码运行宏在VB编辑器中运行在Excel中运行建立按钮运行38学而时习之学而时习之你知道什么是宏了吗？你会录制宏了吗？你知道几种执行宏的方式？是否会使用Visual Basic编辑器看得懂工程资源管理起中显示的讯息吗？会使用程序代码窗口中的一些控制项了吗？39Excel VBA入门入门VBA热身宏的录制与编辑排疑解难使用Help解读&简化程序码Visual Basic简介40排难解疑排难解疑-善用善用Help41排难解疑排难解疑-善用善用Help(续续续续)多参考程序代码，将有助于多参考程序代码，将有助于提高提高programming能力能力42Excel VBA入门入门VBA热身宏的录制与编辑排疑解难使用Help解读&简化程序码Visual Basic简介43Visual Basic简介简介基本概念物件、属性、方法程序结构声明区程序区SubFunctionProperty&Event44VBA基本概念基本概念VBA对象为导向的程序开发工具事件驱动模式了解对象、属性、方法以及事件将有助于熟练使用VBA45工作表工作表Excel之对象之对象单元格单元格工作簿工作簿46工作簿、工作表与单元格工作簿、工作表与单元格Excel VBA的主要工作就是在处理工作步、工作表与单元格。Bobby根据使用经验，整理出相关的用法和技巧。这部分内容为Excel VBA的精华，听完后保证实力倍增。zzz47工作簿工作簿Workbooks新建文件新建工作簿范例：范例：Workbooks.Add打开已有文件打开一个已经存在的工作簿(*.xls文件)范例范例1：Workbooks.Open(“c:test.xls”)范例范例2：Dim Wkb as WorkbookSet Wkb=Workbooks.Open(FileName:=“c:test.xls”)48工作簿工作簿Workbooks(续续续续)打开工作簿在多个已经打开的workbook中，选取其中一个workbook范例：范例：Workbooks(“test.xls”).Activate 保存保存工作簿(*.xls文件)范例范例1：wkb.SaveAs Filename:=(“c:test1.xls”)范例范例2：ActiveWorkbook.SaveActiveWorkbook.CloseActiveWorkbook.Close SaveChanges:=TrueActiveWorkbook.Close SaveChanges:=False49工作表工作表Sheet在workbook中，包含了工作表对象与图表对象。范例：名称参照：名称参照：ActiveWorkbook.Sheets(“Sheet2”)ActiveWorkbook.Worksheets(“Sheet2”)序号参照：序号参照：ActiveWorkbook.Sheets(2)ActiveWorkbook.Worksheets(2)50工作表工作表Sheet(续续续续)复制/移动 与 新建/删除 工作表范例：Worksheets(“Sheet2”).Copy After:=Worksheets(1)Sheets(“Sheet2”).Move Befor:=Sheets(1)Sheets.AddSheets.Delete取消/隐藏范例：sheets(“Sheet1”).Visible=FalseSheets(“Sheet1”).Visible=True51单元格单元格Cell及范围对象及范围对象单元格是Excel工作表中最基本的对象，也是最重要的对象。学习“如何选择范围”是VBA最重要的部分。52单元格单元格Cell及范围对象及范围对象(续续续续)使用Range属性范例：Range(“B2”).Select 选取单元格选取单元格B2Range(“B2:C5”).Select 选取范围选取范围B2:C5Range(“B2:C5,D6:E9”).Select 多重范围选取多重范围选取Range(“B:B”).Select 选取选取B列列Range(2:7).Select 选取选取27列列Range(ActiveCell,“B9”)选取当前选定单元格到选取当前选定单元格到B9的范围的范围53单元格单元格Cell及范围对象及范围对象(续续续续)使用Cells属性范例：ActiveSheet.Cells.Select 选取当前工作表的所有单元格选取当前工作表的所有单元格Range(“B2:C5”).Cells.Select 选取范围选取范围B2:C5Cells(2,3).Select 选取选取C2Cells(2,3).Value=5 设定设定C2值为值为5Range(Cells(2,2),Cells(5,3).Select?选取范围B2:C5使用Offset属性范例：Cell(2,3).Offset(2,3).Value=“Offset”设定设定C2左左3下下2的的cell值为值为“Offset”54选取单元格范围的技巧选取单元格范围的技巧Sub选取清单范围()Sheets(lot_status).SelectRange(k6).SelectActiveCell.CurrentRegion.SelectEndSub选取整块区域选取整块区域55选取单元格范围的技巧选取单元格范围的技巧(续续续续)选取工作区的边缘范例:Cells(4,4).SelectActiveCell.End(xlToLeft).Select 选取当前工作区最左端的单元格选取当前工作区最左端的单元格ActiveCell.End(xlUp).Select 选取当前工作区最上端的单元格选取当前工作区最上端的单元格ActiveCell.End(xlDown).Select 选取当前工作区最下端的单元格选取当前工作区最下端的单元格ActiveCell.End(xlToRight).Select 选取当前工作区最右端的单元格选取当前工作区最右端的单元格56选取单元格范围的技巧选取单元格范围的技巧(续续续续)列与行的选取Columns(“A”).Select 选取选取A列列Columns(3).Select 选取第三列选取第三列,即即C列列Columns.Select 选取所有列选取所有列Rows(2).Select 选取第二行整行选取第二行整行Union及IntersectUnion(Range(“B2:C6”),Range(“E2:F3”).Select 同时选择同时选择B2:C6,E2:F3两个区域两个区域Intersect(Range(“A1:D10”),Range(“A1:C5”).Select 选取两个区域重选取两个区域重叠的区域叠的区域,如果没有重叠区域如果没有重叠区域,则会报错则会报错57善用善用WithEnd With范例Cells(3,3)=“TEST”Cells(3,3).Font.Bold=TrueCells(3,3).Font.Italic=True改良后的程序代码With Cells(3,3).Value=“TEST”.Font.Bold=True.Font.Italic=TrueEnd WithCells(3,3).SelectWith Selection.Value=“TEST”.Font.Bold=True.Font.Italic=TrueEnd With58程序结构程序结构 变量定义变量定义变量定义在程序一开始就进行变量定义,格式:Dim 变量 as 类型范例Dim a as Integer 定义变量定义变量a 为整数型为整数型 Dim s as String 定义变量定义变量s 为字符串型为字符串型 变量的赋值及注释语句对变量的赋值,采用赋值号=，如X=123：Form1.caption=”我的窗口”注释语句是用来说明程序中某些语句的功能和作用,方法是在程序语句末尾或者单独一行进行说明,说明文字前使用单引号;对变量对变量name_end进行赋值进行赋值注释语句单独一行、注释注释语句单独一行、注释语句在程序语句句末语句在程序语句句末59程序结构程序结构 变量定义变量定义VBA中对变量定义的设定加入 Option Explicit 语句可以强迫用户进行变量定义加快程序运行加快程序运行预留空间预留空间避免误用变量避免误用变量工具工具/选项选项60数据类型数据类型61语句语句判断语句IfThenElse如1：IfABAndC250Thenx=x-100如3:If Number 10 Then Digits=1 ElseIf Number 100 Then Digits=2 Else Digits=3 End If62语句语句(续续续续)判断语句SelectCaseEndCase如1：63语句语句(续续续续)循环语句DoLoop:当条件为 True 时，或直到条件变为 True 时，重复执行一个语句块中的命令 Dim Check,Counter Check=True:Counter=0 设置变量初始值。Do 外层循环。Do While Counter 10 内层循环。Counter=Counter+1 计数器加一。If Counter=10 Then 如果条件成立。Check=False 将标志值设成 False。Exit Do 退出内层循环。End If Loop Loop Until Check=False 退出外层循环。ForNext:以指定次数来重复执行一组语句ForWORDS=3To1Step-1建立10次循环ForChars=0To3建立10次循环Range(A1)=CharsNextCharsIncrementcounterRange(A2)=WORDSNextWORDS64语句语句(续续续续)循环语句ForEachNext:对一个数组或集合对象进行，让所有元素重复执行一次语句ForEachrang2Inrange1Withrange2.interior.colorindex=6EndwithNext65学而时习之学而时习之了解对象、属性、方法、事件的意义了吗？了解Excel的主要对象及相关的属性、方法和时间了吗？(工作簿、工作表、单元格)是否会定义变量?是否会使用判断语句，循环语句？会不会使用Help?能体会Help要告诉我们什么吗?66THANKS67