Excel高级功能培训 PSMT质量部 王新民 2006-8-1

目录 公式/函数 宏与VBA Excel的常用对象 几个例子 Page 2

挑战第一关 善用公式与函数 Page 3

公式-定义 有哪些公式符号？ 使用相对、绝对、和混合引用（快捷键：F4） 引用当前工作表外的单元格 算术运算符：+（加）、–（减）、*（乘）、/（除）、^（幂） 比较运算符：=、>、<、>=、<=、<> 文本连接运算符：&（和号） 使用相对、绝对、和混合引用（快捷键：F4） 相对引用：当把公式复制到别的单元格时，行或列引用会改变。引用的是实际偏移量。 绝对引用：复制公式时，行和列引用不会改变。引用的是单元格的实际地址。 混合引用：行或列中有一个是相对引用，另一个是绝对引用。 引用当前工作表外的单元格 =[WorkbookName]SheetName!CellAddress =‘C:\My Documents\[Budget For 2003]Sheet1’A1 Page 4

公式-调试 Page 5

常用函数-数值计算函数 SUM 求和 MIN & MAX 最小值&最大值 AVERAGE 求平均值 COUNTIF 满足条件的值的个数 COUNTA 计算非空单元格数目 SUMIF 满足条件的值的总和 SQRT 开平方 Page 6

常用函数-数值计算函数 COUNT(value1,value2,...) 逻辑值和错误值不被计算 Page 7

常用函数-数值计算函数 COUNTA(value1,value2,...) 错误值或其他无法转换成数字的文字也被计算在内 逻辑值和错误值也计算在内 Page 8

常用函数-数值计算函数 SUMIF(range,criteria,sum_range) 根据指定条件对若干单元格求和 {=SUM(IF((C2:C6>4)*(B2:B6="苹果"),D2:D6))} 数组参数用Ctrl+Shift+Enter输入 Page 9

常用函数-数值计算函数 PRODUCT(number1,number2,...) 将所有以参数形式给出的数字相乘，并返回乘积值 Page 10

常用函数-数值计算函数 SUMPRODUCT(array1,array2,array3, ...) 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 Page 11

条件格式 最多可以设置三个条件 Page 12

数据有效性 Page 13

练习一 求该列数据中“A”的个数 答案： =COUNTIF(B2:B10,"A") Page 14

练习二 求网球的销售额？ 求四季度网球的销售额？ 答案： =SUMIF(A14:A10,"网球",C4:C10)＝11170 答案： {=SUM(IF((A4:A10="网球")*(B4:B10="四季度"),C4:C10))}=6500 Page 15

挑战第二关 认识宏与VBA Page 16

什么是宏 宏是一段定义好的操作，它可以是一段程序代码，也可以是一连串的指令集合。 宏的作用可以使频繁执行的动作自动化 节省时间，提高工作效率，又能减少失误 学VBA建议先从宏入手 Page 17

录制宏 执行宏的操作 开始录制宏 操作步骤 完成宏录制 运行宏 需要经常重复执行的某项任务，就可以把执行这些任务的步骤全部录制在宏里，把宏变为可自动执行的任务 开始录制宏 操作步骤 完成宏录制 执行宏的操作 执行宏的命令 在工具栏上设置宏按钮 使用窗体控件 使用图形对象执行宏 运行宏 Page 18

宏(安全性) 宏的保护方法： 安全级【高】 通常适用于用户没有安装防病毒软件的时候 安全级【中】 当打开的文件中包含宏时，会弹出提示框由用户确认是否启用宏 安全级【低】 默认为启用宏，Office将不对宏作出任何检查 Page 19

宏的结构 宏名 批注 宏程序 Sub 主界面() ' ' 主界面 Macro ' Coren Lee 记录的宏 2002-11-5 Sheets("主界面").Select End Sub 批注 宏程序 Page 20

Visual Basic编辑器 用来编辑宏程序或编写VBA代码 进入编辑器的方法 右击工作表名称 快捷图标 编辑状态下双击控件 Page 21

Visual Basic编辑器 如何对VBA代码进行密码保护 第一步：设置密码 第二步：折叠 Page 22

Visual Basic编辑器 几个技巧 单步执行：F8 执行到光标处：Ctrl+F8 设置断点 Page 23

Visual Basic编辑器 监视窗口 代码窗口 立即窗口 本地窗口 在“立即”窗口，可以输入任何语句并立即执行它 可以通过“本地”窗口观察宏程序所使用的变量、表达式、对象的变化来寻找程序错误 Page 24

什么是工程 用户窗体 文件，例如Excel中的工作表以及图表工作表 模块 类模块 ActiveX文件 引用的其他工程 工程就是模块的集合，包括： 用户窗体 文件，例如Excel中的工作表以及图表工作表 模块 类模块 ActiveX文件 引用的其他工程 Page 25

Visual Basic for Application 宏 VBA 为何要使用 VBA 宏缺少伸缩的弹性 记录了许多不需要的步骤 记录了许多不需要的资料 无法使用较深入的功能 无法完成重复性的工作 无法完成需要条件判断的工作 Page 26

VBA与Visual Basic的差异 Visual Basic以界面为中心的开发方式 重点在使用者如何进行操作 重点在使用者最后看到什么 Page 27

VBA程序的基本架构 对象 属性 方法 事件 面向对象的开发方式 VBA是以操作Office对象为目的的程序语言 对象所属的特性或特征 控制对象的内建动作或功能，以便可以让程序顺利打开 事件 发生于对象上的事情 Page 28

简说对象 方法 事件 属性 Page 29

事件驱动的程序开发 Visual Basic程序的突出特点之一是它的事件驱动机制。该机制使得开发程序时，隐藏了许多Windows程序设计中繁琐的细节，而将主要精力放在程序中事务的处理上 事件列表 事件名称 对象名称 Page 30

ActiveX控件 设计控件 设置控件属性 设置关联单元格 运行 Page 31

VBA的重要语法 Set Object1 = Object Object.Property = Value Object.Method (Setting) Page 32

过程类型分为子过程，函数二类，子过程主要用于一系列的操作，函数主要进行运算 VBA的重要语法 过程类型分为子过程，函数二类，子过程主要用于一系列的操作，函数主要进行运算 函数具有返回值，是通过给函数名赋值来实现的 Page 33

VBA的重要语法 自定义函数 注：自定义函数的特点： 输入参数有变化时，函数才重新计算 Page 34

数据类型 Page 35

VBA控制结构 Select Case 〈测试变量〉 If 〈条件1〉 Then Case 〈表达式1〉 〈语句块1〉 〈语句块1〉 分支结构 Select Case 〈测试变量〉 Case 〈表达式1〉 〈语句块1〉 Case 〈表达式2〉 〈语句块2〉 · Case Else 〈语句块n+1〉 End Select If 〈条件1〉 Then 〈语句块1〉 ElseIf 〈条件2〉 Then 〈语句块2〉 · Else 〈语句块n+1〉 End If Page 36

VBA控制结构 For 〈变量〉=〈初值〉 To 〈终值〉 Step 〈步长〉 〈语句块〉 Next 〈变量〉 循环结构 For 〈变量〉=〈初值〉 To 〈终值〉 Step 〈步长〉 〈语句块〉 Next 〈变量〉 For Each 〈对象变量〉 In 〈对象集〉     〈语句块〉 Next 〈对象变量〉 Do While 〈条件表达式〉 〈语句块〉 Loop Do until 〈条件表达式〉 〈语句块〉 Loop Page 37

VBA常用的几个函数 字符串函数 Page 38

VBA常用的几个函数 字符串函数 Page 39

VBA常用的几个函数 字符串函数 Page 40

VBA常用的几个函数 日期和时间函数 Page 41

VBA常用的几个函数 日期和时间函数 Page 42

VBA常用的几个函数 逻辑测试函数 Page 43

VBA常用的几个函数 类型转换函数 Page 44

VBA常用的几个函数 MsgBox prompt:="this is a msgbox", Buttons:=vbYesNo + vbCritical + vbDefaultButton2 Page 45

InputBox prompt:="请输入姓名", Title:="输入信息框", Default:="张三" VBA常用的几个函数 InputBox prompt:="请输入姓名", Title:="输入信息框", Default:="张三" Page 46

VBA常用的关键字语句 Page 47

挑战第三关 认识Excel的常用对象 Page 48

Excel对象结构 Application Workbooks Windows Addins …… Worksheets Charts Cells Range Page 49

Excel中的对象 Excel中的对象分为两大类：单独对象和集合对象，要引用单独对象只要直接引用对象的名称，要引用集合对象则要通过对象在集合中的索引 通过名称引用 通过顺序号引用 Page 50

Excel中的对象 Application对象 常用属性 常用方法 示例： Page 51

Excel中的对象 Workbook(s)对象 常用属性 常用方法 Page 52

Excel中的对象 指定工作簿中或活动工作簿中所有 Worksheet 对象的集合。每个 Worksheet 对象代表一张工作表 。 示例：将新建工作表插入到活动工作簿的最后一张工作表之前。 ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count) ActiveSheet.Name = "NewSheet" Page 53

Excel中的对象 Range对象 Page 54

Excel中的对象 Range对象 工作表是EXCEL的基干，而Range对象是工作表中最重要的对象之一。 代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域）。 Page 55

Excel中的对象 Sheets("Report").Range("listTitle").Copy Range(arg)（其中 arg 为区域名称）。 Sheets("Report").Range("listTitle").Copy Sheets("NewSheet").Select ActiveSheet.Paste Range(“A1:G10”)： Worksheets("NewSheet").Range("A2:G10").Formula = "=Rand()" Worksheets("NewSheet").Activate Range(.Cells1,.Cells2) With Worksheets("NewSheet") .Range(.Cells(1, 1), .Cells(1, 7)).Borders.LineStyle = xlThick .Range(.Cells(1, 1), .Cells(1, 7)).Font.Bold = True End With Page 56

挑战第四关 示例 Page 57

一个例子 Page 58

一个实例 Internal Audit Form表单 Page 59

一个实例 Page 60

一个文件操作示例 创建一个word格式的文件 在文件中写入样例数据 保存文件 关闭文件 Page 61

Any Question? Page 62

End Thank you！ Page 63