Excel实战技巧教程.ppt
基本功能选择性粘贴技巧快速填充技巧活用条件格式名称的奥妙数据有效性绝技数据分析自动筛选与高级筛选数据透视表使用常用函数技巧“&”的作用绝对引用、混合引用、相对引用的概念VLOOKUP函数IF 函数COUNTIF函数,SUMIF函数VBA基础-入门篇粘贴数值图02图03图04将含有公式的数据区域进行复制，然后鼠标移动到需要粘贴的单元格，点鼠标右键,选择“选择性粘贴”选项在粘贴选项内选择“数值”，显示的为转换后的没有公式的数据粘贴格式转置图07图08图09数列填充（图数列填充（图7-9）点击数列表格的右下角，竖直或者水平拉拽至所需部位点击数列表格的右下角，竖直或者水平拉拽至所需部位当左侧存在数列，当左侧存在数列，双击双击数列或公式的右下角，自动向下填充直至左侧为空格数列或公式的右下角，自动向下填充直至左侧为空格直接拉拽数列或公式，然后再选择填充方式直接拉拽数列或公式，然后再选择填充方式2. 公式的填充公式的填充点击公式表格的右下角，竖直或者水平拉拽至所需部位点击公式表格的右下角，竖直或者水平拉拽至所需部位当左侧存在数列，当左侧存在数列，双击双击公式的右下角，自动向下填充直至左侧为空格公式的右下角，自动向下填充直至左侧为空格直接拉拽数列或公式，然后再选择填充方式（图直接拉拽数列或公式，然后再选择填充方式（图10）图10选择所需标识的数据部分选择所需标识的数据部分选择选择 格式格式- 条件格式条件格式根据所需档位进行分档设置根据所需档位进行分档设置附注：最多可进行三种颜色标识附注：最多可进行三种颜色标识图11图12图13选择数据区域的第一个有效数值单元格选择数据区域的第一个有效数值单元格选择选择 格式格式- 条件格式，条件格式，出现图出现图15界面，界面，图图15中的位置中的位置1选择选择“公式公式”位置位置2手动输入公式手动输入公式“countif（A:A,A2）1”点击位置点击位置3进行满足该条件的单元格格式设置进行满足该条件的单元格格式设置(图图16为设置界面为设置界面),设置完毕后点确定设置完毕后点确定设置完成一个单元格后点击工具栏上的格式刷设置完成一个单元格后点击工具栏上的格式刷“ ”,然后将整个工作区域都进行格式复制然后将整个工作区域都进行格式复制完成后效果图见图完成后效果图见图17根据所需档位进行分档设置根据所需档位进行分档设置附注：最多可进行三种颜色标识附注：最多可进行三种颜色标识图14图17图15图16工具栏选择工具栏选择 插入插入 - 名称名称 - 定义，出现图定义，出现图18界面界面在位置在位置1输入自己定义的名称，比如输入自己定义的名称，比如“CO”；在位置在位置2输入需要引用的内容，比如输入需要引用的内容，比如“人民邮电出版社人民邮电出版社”，然后，然后“添加添加”在在EXCEL单元格中输入公式单元格中输入公式“=CO”，则显示的结果为，则显示的结果为“人民邮电出版社人民邮电出版社” 图18图1912 重复上面重复上面A，B步骤，将上面步骤，将上面B步骤的名称定义为步骤的名称定义为“Tax”；位置位置2栏位输入栏位输入“=5%”；在在EXCEL单元格中可直接将原来公式单元格中可直接将原来公式“=5%*100”使用公式使用公式:”=Tax*100”代替代替选中数据区域选中数据区域,将图将图20的位置的位置1输入自己定义的名称输入自己定义的名称”Myrange” ;后续在用户编辑任何单元格或者后续在用户编辑任何单元格或者sheet时时,只要在位置只要在位置1栏位下拉按钮选择栏位下拉按钮选择”Myrange”,即可选中即可选中该区域该区域 附注：图标附注：图标,数据透视表的数据源部分可用数据透视表的数据源部分可用” Myrange” 来替代常规的区域选择来替代常规的区域选择(图图22) 图20图211图22用途用途:限定用户在该单元格的内用只可以进行下拉选择，避免由于用户不同导致数据统计的差异限定用户在该单元格的内用只可以进行下拉选择，避免由于用户不同导致数据统计的差异在合适的位置预先输入允许用户选择的源数据在合适的位置预先输入允许用户选择的源数据(图图23)选中用户操作单元格选中用户操作单元格,在工具栏上选择在工具栏上选择数据数据 - 有效性有效性,出现出现(图图24)选项框选项框位置位置A选择选择”序列序列”位置位置B所有选项的复选框打勾所有选项的复选框打勾位置位置C为定义的数据范围为定义的数据范围,完成后点完成后点”确定确定” 用户操作单元格显示结果如用户操作单元格显示结果如(图图25) 图23图24ABC图25 用途用途: 筛选特定条件的数据筛选特定条件的数据选中含有数据的单元格选中含有数据的单元格,工具栏选择工具栏选择数据数据 - 筛选筛选-自动筛选自动筛选,数据区表头出现下拉键头数据区表头出现下拉键头,样式见样式见(图图26);选择需要进行条件筛选单元格的下拉键头选择需要进行条件筛选单元格的下拉键头,选择选择”自定义自定义”选项选项(图图27)设置自定义选项框设置自定义选项框(图图28)内条件内条件,完成后确定即可完成后确定即可图26图27图28用途用途: 显示不重复记录显示不重复记录选择数据区域选择数据区域,数据数据-筛选筛选-高级筛选高级筛选(图图29),”选择不重复记录选择不重复记录”选项打勾即可选项打勾即可用途用途: 多条件复合筛选多条件复合筛选 1. 在空白单元格定义筛选条件表格在空白单元格定义筛选条件表格(图图30),定义条件的表头必须与数据区域的表头一致定义条件的表头必须与数据区域的表头一致,条件定义顺条件定义顺序序: 从上到下为从上到下为”OR”关系关系,从左到右为从左到右为”AND”关系关系2. 选择数据区域选择数据区域,打开图打开图29界面界面,A栏位选择数据区域栏位选择数据区域,B栏位选择条件区域所在的位置栏位选择条件区域所在的位置(图图30),确定即确定即可可 附注附注: 条件表格与待分析数据表格必须在一个条件表格与待分析数据表格必须在一个sheet内内,如两张表不在一个如两张表不在一个sheet,请选择请选择”将筛选结果将筛选结果复制到其他位置复制到其他位置”选项，最后选择选项，最后选择“复制到复制到”的目标单元格，其它操作方法同的目标单元格，其它操作方法同1，2图29图30AB用途用途: 对不同的条件进行求和、计数等统计操作，数据透视表是交互式报表，可快速合并和比较大量数对不同的条件进行求和、计数等统计操作，数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据选中数据区域内任意单元格选中数据区域内任意单元格,数据数据-数据透视表和数据透视图数据透视表和数据透视图-出现数据透视向导一出现数据透视向导一,直接点直接点”下一下一步步”-向导二界面直接点向导二界面直接点”下一步下一步”-向导三界面选择向导三界面选择”布局布局”-出现图出现图31界面界面图图31界面上，将右边的字段按钮拖到左边图上，设置结束后，点击界面上，将右边的字段按钮拖到左边图上，设置结束后，点击“完成完成“即可，最终界面间图即可，最终界面间图33附注：双击数据区域的字段，可选择数据计算方式，例：求和、计数、最大值等，见图附注：双击数据区域的字段，可选择数据计算方式，例：求和、计数、最大值等，见图32 图31图32图33= A1 & C3，直接串联两个或者多个数据，直接串联两个或者多个数据= A2 & “-” & B2，串联中加入，串联中加入“ ”中间的符号中间的符号图34图35= indirect(“C” & D8)，取，取C（D8）格内的值）格内的值= indirect(“C” & D8+1) ，取，取C（D8）+1 格内的值格内的值表现形式表现形式:$A$1 ，字母数字前都加入，字母数字前都加入”$”符号符号图36图37表现形式表现形式:A$1 或者或者 $A1，字母或者数字前加入，字母或者数字前加入”$”符号符号表现形式表现形式:A1图38附注：按附注：按F4可在不同引用方式间转换可在不同引用方式间转换 参数含义:Lookup value: 为 需要在数组第一列中查找的数值,可以为数值、引用或文本字符串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. 如果存在重复数据如果存在重复数据,系统只会显示第一个数据的结果系统只会显示第一个数据的结果图39例例1:公式公式 IF(A2B12, Over Budget“ , OK)含义为：如果含义为：如果A12大于大于B12，则公式将显示，则公式将显示“Over Budget“，否则公式显示，否则公式显示”OK” 图40图41参数含义:Logical_test 表示计算结果为TRUE 或 FALSE的任意值或表达式.Value_if_true logical_test 为 TRUE时返回的值。Value_if_false logical_test为 FALSE时返回的值。图42Countif函数的运用参数含义:Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本参数含义:Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件。Sum_range 是需要求和的实际单元格。图43Sumif函数的运用目的 了解Excel VBA 建立所有学员使用VBA的能力Excel VBA入门 VBA热身 宏的录制与编辑 Visual Basic简介 解读&简化程序码 排疑解难使用Help VBA热身 宏的录制与编辑 排疑解难使用Help 解读&简化程序码 Visual Basic简介 实际范例 由以上范例可看出使用VBA的优点 减少重复工作 利用程序达成较负责的控制，简化人力并减少错误发生 VBA热身 宏的录制与编辑 排疑解难使用Help 解读&简化程序码 Visual Basic简介宏的录制(1)宏名称的一个字必须是英文字母或者中文字不能有空白字源设定执行宏的快捷键宏的录制(2)在此状态下，所用操作在此状态下，所用操作的动作会被录制成宏的动作会被录制成宏按下停止键完成录制按下停止键完成录制宏的录制(3)Visual Basic编辑窗口工程资源管理器属性窗口边界指示区程式碼視窗物件清单全模块视图钮过程视图纽程序事件清单使用程式码窗口 设定自己喜欢的窗口模式工具工具/ /选项选项工具工具/宏宏/宏宏/执执行行or：直接在代码窗口：直接在代码窗口点击运行按钮点击运行按钮Excel 工具栏/工具/自定义123将自定义按钮拖至工具栏上保存 在VB编辑器中保存后离开 直接在Excel中存檔导入/导出宏 *.frm：窗口 *.bas：程序代码 *.cls：类别Excel VBA录制一个宏利用VB编辑器浏览并编辑程序代码运行宏 在VB编辑器中运行 在Excel中运行 建立按钮运行你知道什么是宏了吗？你会录制宏了吗？你知道几种执行宏的方式？是否会使用Visual Basic编辑器 看得懂工程资源管理起中显示的讯息吗？ 会使用程序代码窗口中的一些控制项了吗？ VBA热身 宏的录制与编辑 排疑解难使用Help 解读&简化程序码 Visual Basic简介多参考程序代码，将有助多参考程序代码，将有助于提高于提高programming能力能力 VBA热身 宏的录制与编辑 排疑解难使用Help 解读&简化程序码 Visual Basic简介基本概念 物件、属性、方法程序结构 声明区 程序区 Sub Function Property & EventVBA 对象为导向的程序开发工具 事件驱动模式了解对象、属性、方法以及事件将有助于熟练使用VBA工作表工作表单元格单元格工作簿工作簿Excel VBA的主要工作就是在处理工作步、工作表与单元格。Bobby根据使用经验，整理出相关的用法和技巧。这部分内容为Excel VBA的精华，听完后保证实力倍增。zzz新建文件 新建工作簿 范例：范例：Workbooks.Add打开已有文件 打开一个已经存在的工作簿(*.xls文件) 范例范例1：Workbooks.Open(“c:test.xls”) 范例范例2：Dim Wkb as WorkbookSet Wkb = Workbooks.Open(FileName:=“c:test.xls”)打开工作簿 在多个已经打开的workbook中，选取其中一个workbook 范例：范例：Workbooks(“test.xls”).Activate 保存 保存工作簿(*.xls文件) 范例范例1：wkb.SaveAs Filename:=(“c:test1.xls”) 范例范例2：ActiveWorkbook.SaveActiveWorkbook.CloseActiveWorkbook.Close SaveChanges:=TrueActiveWorkbook.Close SaveChanges:=False在workbook中，包含了工作表对象与图表对象。范例： 名称参照：名称参照：ActiveWorkbook.Sheets(“Sheet2”)ActiveWorkbook.Worksheets(“Sheet2”) 序号参照：序号参照：ActiveWorkbook.Sheets(2)ActiveWorkbook.Worksheets(2) 复制/移动 与 新建/删除 工作表 范例：Worksheets(“Sheet2”).Copy After:= Worksheets(1)Sheets(“Sheet2”).Move Befor:= Sheets(1)Sheets.AddSheets.Delete 取消/隐藏 范例：sheets(“Sheet1”).Visible = FalseSheets(“Sheet1”).Visible = True单元格是Excel工作表中最基本的对象，也是最重要的对象。学习“如何选择范围”是VBA最重要的部分。使用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的范围的范围使用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”Sub 选取清单范围()Sheets(lot_status).SelectRange(k6).SelectActiveCell.CurrentRegion.SelectEnd Sub选取整块区域选取整块区域选取工作区的边缘 范例: Cells(4,4).Select ActiveCell.End(xlToLeft).Select 选取当前工作区最左端的单元格选取当前工作区最左端的单元格 ActiveCell.End(xlUp).Select 选取当前工作区最上端的单元格选取当前工作区最上端的单元格 ActiveCell.End(xlDown).Select 选取当前工作区最下端的单元格选取当前工作区最下端的单元格 ActiveCell.End(xlToRight).Select 选取当前工作区最右端的单元格选取当前工作区最右端的单元格列与行的选取 Columns(“A”).Select 选取选取A列列 Columns(3).Select 选取第三列选取第三列,即即C列列 Columns.Select 选取所有列选取所有列 Rows(2).Select 选取第二行整行选取第二行整行Union及Intersect Union(Range(“B2:C6”), Range(“E2:F3”).Select 同时选择同时选择B2:C6,E2:F3两个区域两个区域 Intersect(Range(“A1:D10”), Range(“A1:C5”).Select 选取两个区域重选取两个区域重叠的区域叠的区域,如果没有重叠区域如果没有重叠区域,则会报错则会报错范例 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 With变量定义 在程序一开始就进行变量定义,格式: Dim 变量 as 类型 范例Dim a as Integer 定义变量定义变量a 为整数型为整数型 Dim s as String 定义变量定义变量s 为字符串型为字符串型 变量的赋值及注释语句 对变量的赋值,采用赋值号 =，如X=123：Form1.caption=”我的窗口” 注释语句是用来说明程序中某些语句的功能和作用,方法是在程序语句末尾或者单独一行进行说明,说明文字前使用单引号 ; 对变量对变量name_end进行赋值进行赋值注释语句单独一行、注释注释语句单独一行、注释语句在程序语句句末语句在程序语句句末VBA中对变量定义的设定 加入 Option Explicit 语句可以强迫用户进行变量定义加快程序运行加快程序运行预留空间预留空间避免误用变量避免误用变量工具工具/ /选项选项判断语句 IfThenElse 如1：If AB And C250 Then x=x-100 如3: If Number 10 Then Digits = 1 ElseIf Number 100 Then Digits = 2 Else Digits = 3 End If判断语句 Select CaseEnd Case 如1：循环语句 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: 以指定次数来重复执行一组语句 For WORDS = 3 To 1 Step -1 建立 10 次循环 For Chars = 0 To 3 建立 10 次循环 Range(A1) = Chars Next Chars Increment counter Range(A2) = WORDS Next WORDS循环语句 ForEachNext:对一个数组或集合对象进行，让所有元素重复执行一次语句For Each rang2 In range1With range2.interior.colorindex=6End withNext了解对象、属性、方法、事件的意义了吗？了解Excel的主要对象及相关的属性、方法和时间了吗？ (工作簿、工作表、单元格)是否会定义变量?是否会使用判断语句，循环语句？ 会不会使用Help?能体会Help要告诉我们什么吗?
