大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 2. 名称框和编辑栏用于定义单元格或单元区域的名称, 或根据名称查找单元格或单元格区域 显示活动单元格的地址 3. 工作表标签显示工作表名称, 用于工作表之间的切换 右击该标签, 可对工作表进行重命名 复制等操作 4. 工作簿工作簿是处理

Transcription

1 第 6 章 Excel 2007 高级应用 Excel 2007 是微软公司 Office 2007 办公组件之一, 主要提供制作电子表格 完成复杂的数 据运算 数据分析和预测, 并且具有强大的图表制作功能 广泛应用于财务 管理 统计分析 市场营销 工程计算等方面 6.1 Excel 2007 主要界面介绍 Excel 2007 窗口界面与 Word 2007 界面一样, 功能选择区也是由选项卡与选项卡中的组构 成的 下面具体介绍窗口组成, 如图 6-1 所示 图 6-1 Excel 2007 窗口 1. 标题栏 标题栏位于窗口顶部, 显示应用程序和文件名称 在标题栏的左侧是快速访问工作栏和 Office 按钮, 其功能和作用与 Word 相同 157

2 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 2. 名称框和编辑栏用于定义单元格或单元区域的名称, 或根据名称查找单元格或单元格区域 显示活动单元格的地址 3. 工作表标签显示工作表名称, 用于工作表之间的切换 右击该标签, 可对工作表进行重命名 复制等操作 4. 工作簿工作簿是处理和存储数据的 Excel 文件, 一个 Excel 文件就是一个工作簿, 其扩展名为.xlsx 当启动 Excel 2007 时, 系统会自动创建一个工作簿, 该工作簿的默认名称为 Book1. xlsx 5. 工作表工作表由若干行 若干列组成, 也称电子表格 一个工作簿由许多张工作表集合而成 一个工作簿至少包含一张工作表, 默认情况下包含 3 张工作表 在同一时刻, 用户只能对一张工作表进行编辑 处理 6. 行 列 单元格工作表的数据工作区中, 以数字标识的为行, 以英文字母标识的为列 一行和一列交叉处为一单元格, 单元格是组成工作表的最小单位 单元格可以包括文字 数值 日期 时间 公式和函数等信息 每一个单元格对应一个单元格地址, 用列的字母加上行的数字来表示 例如, 如果选择一个单元格位于 B 列第 6 行, 则在编辑栏左边的地址框中显示该单元格地址为 B6 6.2 电子表格的制作 启动 Excel 2007 后, 用户可以通过单击 Office 按钮, 执行相应命令创建工作簿, 也可以通过自定义快速访问工作栏中的新建按钮创建 创建一个空白工作簿, 然后可以在工作表的单元格中输入数据 输入数据 在工作表中, 单击要输入数据的单元格, 单元格的周围出现黑框, 表示该单元格已成为活 动单元格, 编辑栏左边的名称框显示活动单元格的名称, 用户可直接在单元格内录入数据, 也 可以在编辑栏中输入数据, 所输入内容会同时出现在编辑栏和单元格中 输入数据后, 用以下方式确认或取消输入 : 按 Enter 键确认, 将活动单元格下移一个单元 按 Tab 键确认, 将活动单元格右移一个单元 按 键确认, 切换到上 下 左 右单元 按 输入 按钮 确认 按 Esc 键或单击 取消 按钮, 取消输入数据 1. 输入文本 文本由字母 字符串 任何符号和汉字等字符型数据组成 Excel 会自动将数据单元格左 158

3 第 6 章 Excel 2007 高级应用 边对齐 对于数字形式的文本, 如学号 身份证号等, 输入时应在数字前加上单引号 ( 英文状态下输入 ) 例 : 学号 : , 输入 : 确认后, 单元格以显示 2. 输入数值数值一般由整数 小数等组成 输入数值型数据时,Excel 会自动将数据单元格右边对齐 当数值的长度超过单元格的宽度时,Excel 将自动使用科学计数法来表示输入的数值 输入正数时, 只需直接输入该数字即可 输入负数时, 必须在数字前加一个负号 - 或给数字加上圆括号 输入分数时, 应在输入的分数前加 0 如要输入 1/6, 应先输入数字 0, 然后输入一个空格, 再输入分数 1/6 输入百分比数据, 可以直接在数值后输入百分号 % 3. 输入日期和时间输入时间时, 使用冒号 : 将时 分 秒隔开, 如 16:20 输入日期时使用反斜杠 / 或 - 将年 月 日隔开, 如输入 11/5/16 或者 若需要快速输入当前日期时, 只需按 Ctrl+ ; 组合键 ; 若需要快速输入当前时间, 按 Ctrl+Shift+ ; 组合键 输入以 12 小时制输入的时间和日期, 可以在时间后加一个空格并输入 AM 或者 PM, 否则 Excel 自动以 24 小时制来处理时间 在某个单元格中同时输入日期和时间, 日期和时间要用空格隔开 4. 利用序列自动填充序列是指一组有规律的数据 在 Excel 中, 某单元格中输入了序列中的某个元素后, 再拖动单元格的填充柄, 其后的单元格中自动填充序列中的元素 这称为 自动填充序列 (1) 内置序列在 Excel 中已经定义了一些常用序列, 单击 Office 按钮, 单击 Excel 选项 按钮, 打开 Excel 选项 对话框 在 常用 选项卡中, 单击 编辑自定义列表 按钮, 打开 自定义序列 对话框, 如图 6-2 所示 从该对话框中可查看内置序列 图 6-2 内置序列 对话框 图 6-3 自定义序列 对话框 (2) 自定义序列经常输入的一些序列, 但 Excel 内置序列里没有, 如果希望用填充柄自动填充, 必须将其设置为 自定义序列 打开 自定义序列 对话框, 在 输入序列 文本框中输入自定义的序列, 单击 添加 159

4 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 按钮, 将输入的序列添加到该对话框左侧的 自定义序列 列表中, 如图 6-3 所示 单击 确定 按钮完成自定义序列 建立公式 公式是对工作表中的数值执行计算的等式 公式以等号 (=) 开头 公式也可以包括下列部分或全部内容 : 函数 引用 运算符和常量 例 :=PI()*B2^2 该公式中,PI() 是函数返回值为 ,B2 是引用返回单元格 B2 中的值, 公式中的数字 2 是常量, * ^ 是运算符 1. 在公式中使用常量常量是不用计算的值 例如, 日期 数字 266 以及文本 总人数, 都是常量 表达式或由表达式得出的结果不是常量 如果在公式中使用常量而不是对单元格的引用 ( 例如, = ), 则只有在用户更改公式时其结果才会更改 2. 在公式中使用运算符运算符用于指定要对公式中的元素执行的计算类型 计算时有一个默认的次序, 但可以使用括号更改计算次序 计算运算符分为 4 种不同类型 : 算术 比较 文本连接和引用 (1) 算术运算符算术运算符可以完成基本的数学运算, 包括加法 减法 乘法 除法 百分比和乘方等, 见表 6-1 表 6-1 算术运算符 算术运算符 含义 示例 +( 加号 ) 加法 3+3 ( 减号 ) 减法 3 1 *( 星号 ) 乘法 3*3 /( 正斜杠 ) 除法 3/3 %( 百分号 ) 百分比 20% ^( 脱字号 ) 乘方 3^2 (2) 比较运算符 比较运算符可以比较 2 个值, 见表 6-2, 当用以下运算符比较 2 个值时, 结果为逻辑值 : True 或 False 表 6-2 比较运算符 比较运算符 含义 示例 =( 等号 ) 等于 A1=B1 >( 大于号 ) 大于 A1>B1 <( 小于号 ) 小于 A1<B1 >=( 大于等于号 ) 大于或等于 A1>=B1 160

5 第 6 章 Excel 2007 高级应用 续表 比较运算符 含义 示例 <=( 小于等于号 ) 小于或等于 A1<=B1 <>( 不等号 ) 不等于 A1<>B1 (3) 文本连接运算符 可以使用 与 号 (&) 联接或连接一个或多个文本字符串, 以生成一段文本, 见表 6-3 表 6-3 文本连接运算符 文本运算符 含义 示例 &( 与号 ) 将两个值连接或串起来产生一个连续的文本值 "North"&"wind" (4) 引用运算符 可以使用以下运算符对单元格区域进行合并计算, 见表 6-4 表 6-4 引用运算符 引用运算符含义示例 :( 冒号 ) 区域运算符, 生成对 2 个引用之间所有单元格的引用 ( 包括这 两个引用 ) B5:B15,( 逗号 ) 联合运算符, 将多个引用合并为一个引用 SUM(B5:B15,D5:D15) ( 空格 ) 交集运算符, 生成对 2 个引用中共有的单元格的引用 B7:D7 C6:C8 3.Excel 运算的优先级 如果一个公式中有若干个运算符,Excel 将按表 6-5 中的优先级进行计算 如果一个公式中 的若干个运算符具有相同的优先级 ( 例如, 如果一个公式中既有乘号又有除号 ),Excel 将从左 到右进行计算 表 6-5 Excel 运算优先级 优先级运算符说明 1 :( 冒号 ) ( 单个空格 ),( 逗号 ) 引用运算符 2 负数 ( 如 1) 3 % 百分比 4 ^ 乘方 5 * 和 / 乘和除 6 + 和 加和减 7 & 连接两个文本字符串 ( 串连 ) 8 = < > <= >= <> 比较运算符 若要更改求值的顺序, 请将公式中要先计算的部分用括号括起来 例如, 公式 =6+2*5, 因 161

6 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 为 Excel 先进行乘法运算后进行加法运算 将 2 与 5 相乘, 然后再加上 6, 得到 16 结果 ; 如果用括号括起来 =(6+2)*5,Excel 将先求出 6 加 2 之和, 再用结果乘以 5 得 使用函数 函数是预定义的公式, 通过使用参数的特定数值按特定的顺序或结构执行计算 函数可用于执行简单或复杂的计算 1. 输入函数在工作表中插入函数, 可以直接手动输入, 也可以通过 函数库 组输入 方法 1: 手动输入单击要输入函数的单元格, 使之成为活动单元格, 直接在单元格或编辑栏中输入函数名和函数参数 例 :=AVERAGE(C6:F6) 方法 2 : 通过 函数库 组输入选择 公式 选项卡, 单击 函数库 组中的相应类型的下拉按钮, 在其列表中选择函数或单击 插入函数, 在 插入函数 对话框中, 选择所需函数, 在弹出的 函数参数 对话框中设置函数参数 2. 常用函数 Excel 库函数有加载宏和自动化函数 多维数据集函数 数据库函数 日期和时间函数 工程函数 财务函数 信息函数 逻辑函数 查找和引用函数 数学和三角函数 统计函数和文本函数共 12 类 3. 单元格的引用引用的作用在于标识工作表上的单元格或单元格区域 通过引用, 可以在一个公式中使用工作表不同部分中包含的数据, 或者在多个公式中使用同一个单元格的数值 还可以引用同一个工作簿中其他工作表上的单元格和其他工作簿中的数据 根据不同要求使用不同的引用样式, 也可对单元格进行相对引用 绝对引用和混合引用 (1)A1 引用样式 A1 引用样式是默认引用样式, 默认情况下,Excel 使用 A1 引用样式, 此样式引用字母标识列 ( 从 A 到 XFD, 共 列 ) 和数字标识行 ( 从 1 到 ) 这些字母和数字称为行号和列标 见表 6-6 表 6-6 Excel A1 引用样式 引用列 A 和行 10 交叉处的单元格在列 A 和行 10 ~ 行 20 之间的单元格区域在行 15 和列 B ~ 列 E 之间的单元格区域 引用表示 A10 A10:A20 B15:E15 行 5 中的全部单元格 5:5 行 5 ~ 行 10 之间的全部单元格 5:10 列 H 中的全部单元格 H:H 162

7 第 6 章 Excel 2007 高级应用 续表 引用 列 H ~ 列 J 之间的全部单元格 引用表示 H:J 列 A ~ 列 E 和行 10 ~ 行 20 之间的单元格区域 A10:E20 (2) 相对引用相对单元格引用是引用的单元格的相对位置 如果公式所在单元格的位置改变, 引用也随之改变 如果多行或多列地复制或填充公式, 引用会自动调整, 如图 6-4 所示 图 6-4 相对单元格引用 (3) 绝对引用 绝对单元格引用 ( 如 $A$1) 总是在特定位置引用单元格 如果公式所在单元格的位置改 变, 绝对引用将保持不变, 如图 6-5 所示 图 6-5 绝对单元格引用 (4) 混合引用混合引用具有绝对列和相对行或绝对行和相对列 绝对引用列采用 $A1 $B1 等形式 绝对引用行采用 A$1 B$1 等形式 如果公式所在单元格的位置改变, 则相对引用将改变, 而绝对引用将不变, 如图 6-6 所示 图 6-6 混合引用 表格格式设置 工作表除了数据正确外, 表格的外观修饰也是不可少的 工作表的修饰可以通过设置数据 的格式 对齐方式 边框 填充颜色和改变底纹等方式实现 163

8 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 1. 设置数据格式选择要进行设置的单元格, 选择 开始 选项卡的 数字 组, 单击 常规 下拉按钮或 数字 组中的 对话框启动器 按钮, 弹出 设置单元格格式 对话框, 选择 数学 选项卡, 在 分类 栏中可以选数据的显示格式 2. 设置对齐方式方法 1 : 选择要进行设置对齐的单元格, 选择 开始 选项卡, 单击 对齐方式 组中的所需的对齐按钮, 即可作相应对齐设置 方法 2 : 选择要进行设置对齐的文本所在的单元格, 选择 开始 选项卡的 对齐方式 组中的 对话框启动器 按钮, 弹出 设置单元格格式 对话框, 选择 对齐 选项卡, 在 文本对齐方式 栏中, 设置文本的水平对齐或垂直对齐 3. 设置边框方法 1 : 选择要进行设置边框的单元格, 选择 开始 选项卡, 单击 字体 组中的 绘制边框线 的下拉按钮, 选择线型, 选择边框 即可为所选单元格添加边框 方法 2 : 选择要进行设置边框的单元格, 选择 开始 选项卡的 字体 组中的 对话框启动器 按钮, 弹出 设置单元格格式 对话框, 选择 边框 选项卡, 在 线条 颜色 预置 边框 栏中, 设置边框的线条样式 颜色 4. 设置颜色和底纹方法 1 : 选择要进行设置颜色的单元格, 选择 开始 选项卡, 单击 字体 组中的 填充颜色 的下拉按钮, 可以单击所列的 主题颜色 和 标准色 或单击 其他颜色, 在 颜色 对话框中, 可以选择 标准 选项卡, 选择标准颜色, 也可以选择 自定义 选项卡, 用户自定义颜色 方法 2 : 选择要进行设置颜色和底纹的单元格, 选择 开始 选项卡的 字体 组中的 对话框启动器 按钮, 弹出 设置单元格格式 对话框, 选择 填充 选项卡, 在 背景色 图案颜色 图案样式 栏中, 设置单元格的背景 底纹颜色和图案 也可单击 填充效果 按钮, 设置渐变颜色和底纹样式 样式的应用 样式是一组已定义的单元格格式, 用户可以利用 Excel 的样式功能快速对单元格的数字格式 对齐方式 字体字号 颜色 边框等内容进行设置 在 Excel 2007 中, 用户不仅可以使用内置的单元格样式, 而具还可以根据需求创建新的单元格样式 1. 条件格式的应用使用条件格式, 可以将工作表中的符合条件的数据所在单元格以不同颜色突显出来 首先选择要使用条件格式的单元格区域, 选择 开始 选项卡中 样式 组, 单击 条件格式 下拉按钮, 根据需要执行不同的命令, 设置相应的格式, 具体选项功能如下 : 突出显示单元格规则执行 突出显示单元格规则 中的命令, 在其级联菜单中选择相应的选项, 在弹出的对话框中为单元格设置格式 或选择 其他规则 命令, 在 新建格式规则 对话框中, 创建新的规则 项目选取规则执行 项目选取规则 命令, 在其级联菜单中选择相应的选项, 并进行相应设置, 或选择 164

9 第 6 章 Excel 2007 高级应用 其他规则 命令, 在 新建格式规则 对话框中, 创建新的规则 数据条执行 数据条 命令, 在其级联菜单中选择相应的选项, 或选择 其他规则 命令, 在 新建格式规则 对话框中, 创建新的规则 色阶执行 色阶 命令, 在其级联菜单中选择相应的选项, 或选择 其他规则 命令, 在 新建格式规则 对话框中, 创建新的规则 图标集执行 图标集 命令, 在其级联菜单中选择相应的选项, 或选择 其他规则 命令, 在 新建格式规则 对话框中, 创建新的规则 2. 套用表格格式的应用套用表格格式的应用, 不仅可以美化工作表, 还可以达到快速设置表格的目的 用户可以选择预定义好的表格格式, 也可以自已创建新的表格样式 应用预定义的表格样式首先选择要套用格式的单元格区域, 然后, 单击 样式 组中的 套用表格格式 下拉按钮, 在其列表中, 用户根据 浅 中等深 深色 3 栏内容选择表样 创建新的表格样式选择要套用格式的单元格区域, 单击 样式 组中的 套用表格格式 下拉按钮, 执行 新建表样式 命令, 弹出 新建表快速样式 对话框, 在 名称 文本栏中, 输入新建表格样式名称, 在 表元素 列表中, 选择要设置的表格元素, 再单击 格式 按钮, 弹出 设置单元格格式 对话框, 对该对话框进行设置 3. 单元格样式的应用 样式的应用选择要套用格式的单元格区域, 单击 样式 组中的 单元格样式 下拉按钮, 在其列表中, 用户根据需要选择相应的单元格样式 新建单元格样式单击 样式 组中的 单元格样式 下拉按钮, 执行 新建单元格样式 命令, 在弹出的 样式 对话框中, 用户根据需要进行相应的设置 合并样式合并样式是将工作簿中的单元格样式复制到其他工作簿中 单击 样式 组中的 单元格样式 下拉按钮, 执行 合并样式 命令, 在 合并样式 对话框中, 选择合并样式来源 6.3 图表的应用 图表能直观地表现数据之间的关系和变化趋势, 应用 Excel 图表的强大的功能, 可以快捷地创建有效的 具有专业水准的数据图表 创建图表 图表是由图表区域和区域中的元素组成, 其元素主要包括标题 图例 垂直轴 水平轴 165

10 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 数据系列等对象 图表中的每个数据点都与工作表中的单元格数据相对应, 图例则显示图表数据的种类与对应的颜色 Excel 2007 提供了 11 种标准的图表类型的功能, 用户在创建图表时只需选择图表类型, 便可快捷地创建图表, 具体创建图表的方法如下 : 1. 使用图表选项组选择要创建图表的数据区域, 选择 插入 选项卡, 单击 图表 组中要插入的图表类型下拉按钮, 在其列表中选择即可 2. 使用 插入图表 对话框选择要创建图表的数据区域, 选择 插入 选项卡, 单击 图表 组中的 对话框启动器 按钮, 弹出 插入图表 对话框, 用户在对话框左边列表中选择所需的图表类型, 并在右边侧列表中选择相应的图表 图表的编辑 图表创建后, 可以进行更改图表类型 添加数据项和删除数据项等编辑操作 1. 更改图表类型图表创建以后, 用户可以用下列途径来更改图表类型 : 设计 选项卡中的 类型 组选择图表, 选择 设计 选项卡 类型 组, 单击 更改图表类型 命令, 弹出 更改图表类型 对话框, 在该话框中选择相应的图表类型 插入 选项卡中的 图表 组 插入 选项卡中的 图表 组, 单击 对话框启动器, 弹出 更改图表类型 对话框, 在该话框中选择相应的图表类型 快捷菜单选择图表, 按右键, 在快捷菜单中, 选择 更改图表类型 命令, 在 更改图表类型 对话框中选择相应的图表类型 2. 编辑图表数据 添加数据方法 1 : 在工作表中, 选择要添加的数据, 按右键, 选择快捷菜单中的 复制, 再选中图表, 按右键, 选择快捷菜单中的 粘贴, 图表上即添加了数据 方法 2 : 选择图表, 在工作表中会出现蓝色线框, 将光标指向蓝色线框右下角, 拖动鼠标, 扩大所选数据区域 方法 3 : 选择 设计 选项卡 数据 选项组中的 选择数据 命令, 在 选择数据源 对话框中重新选择数据区域 删除数据方法 1 : 选择图表中要删除的数据系列, 按 Delete 键即可删除图表中的数据 方法 2 : 选择图表, 选择 设计 选项卡 数据 选项组中的 选择数据 命令, 在 选择数据源 对话框中, 在 图例项 栏中选择要删除的项, 单击 删除 按钮 方法 3 : 选择图表, 在工作表中会出现蓝色线框, 将光标指向蓝色线框右下角, 移动鼠标, 缩小所选数据区域 166

11 第 6 章 Excel 2007 高级应用 设置图表格式 图表的格式直接影响图表的整体效果, 当图表创建完成后, 用户可以根据需要, 设置图表 格式, 图表区域如图 6-7 所示 图 6-7 图表区域 设置图表区域格式选择图表, 右击图表区域, 打开快捷菜单, 选择 设置图表区域格式 命令, 在 设置图表区域格式 对话框中设置相应的格式 设置绘图区格式选择图表, 右击绘图区域, 打开快捷菜单, 选择 设置绘图区格式 命令, 在 设置绘图区格式 对话框中设置相应的格式 设置网格线格式选择图表, 右击图表网格线, 打开快捷菜单, 选择 设置网格线格式 命令, 在 设置网格线格式 对话框中设置相应的格式 设置坐标轴格式选择图表, 右击图表坐标轴线, 打开快捷菜单, 选择 设置坐标轴格式 命令, 在 设置坐标轴格式 对话框中设置相应的格式 设置数据系列格式选择图表, 右击图表中数据图, 打开快捷菜单, 选择 设置数据系列格式 命令, 在 设置数据系列格式 对话框中设置相应的格式 添加数据标签选择图表, 右击图表中数据图, 打开快捷菜单, 选择 添加数据标签 命令即可 设置标题格式选择 布局 选项卡的标签组, 单击 图表标题 下拉按钮, 在列表中, 选择 无 居中覆盖标题 和 图表上方 中的一项 设置图例格式选择 布局 选项卡的标签组, 单击 图例 下拉按钮, 在列表中, 选择相的设置 167

12 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 6.4 Excel VBA 基础 Excel VBA 是指以 Excel 环境为母体, 在 VBA 的开发环境 (VBE) 中集成了大量的 Excel 对象和方法 ; 而在程序设计 算法方式 过程实现方面与 VB 基本相同, 通 VBE 能直接调用 Excel 中对象和方法来提供特定功能的开发与定制 Excel VBA 开发环境 Excel VBA 开发环境 (VBE) 是一个非常友好的开发环境, 通过 VBE 开发的功能直接成为 Excel 文件的组成部分, 对 Excel 文件进行保存操作时,VBA 的组成部分宏 模块 用户界面等同时进行了保存 在 Excel 2007 启动后, 如果在主窗口上没有显示 开发工具 选项卡, 则需要进行如下操作 : 单击 Office 按钮, 单击 Excel 选项 命令, 弹出 Excel 选项 对话框, 在 常用 选项卡中选中 在功能区显示 开发工具 选项卡 复选框 单击 确定 按钮即可 单击 开发工具 选项卡, 在 开发工具 选项卡中的 代码 工具栏中单击 Visual Basic 按钮, 即可进入 VBA 界面 VBE 编辑器和 VBA 代码输入调试 本小节对 VBE 编辑器界面及 VBA 代码输入 调试进行简单介绍 1. 打开 VBE 编辑器选择 Visual Basic 工具栏上的 Visual Basic 编辑器 命令按钮来打开 VBE 编辑器, 也可以用下面方式打开 VBE 编辑器 : 在任一工作表标签上单击鼠标右键, 在弹出的菜单中选择 查看代码, 则可进入 VBE 编辑器访问该工作表的代码模块, 如图 6-8 所示 图 6-8 右击工作表标签快捷菜单 刚打开 VBE 编辑器时, 所显示的窗口如图 6-9 所示, 其中没有代码模块窗口,VBA 的菜 单栏主要包含文件 编辑 视图 插入 格式 调试 运行 工具 外接程序 窗口以及帮助 11 个菜单项 168

13 第 6 章 Excel 2007 高级应用 图 6-9 VBA 编辑器窗口 可以在 工程资源管理器 中双击任一对象打开代码窗口, 或者选择菜单 插入 模块 或 插入 类模块 来打开代码窗口, 如图 6-10 所示 图 6-10 VBA 代码窗口选择 VBE 菜单 插入 用户窗体, 即可插入一个用户窗体 当插入用户窗体后, 在 工程资源管理器 窗口中会出现一个用户窗体对象, 如图 6-11 所示 工程属性 窗口显示当前用户窗体的属性, 可对相关属性进行设置或修改 同时, 在用户窗体上用鼠标单击, 会出现 控件工具箱 在 工程资源管理器 窗口双击用户窗体图标, 会出现相应的用户窗体 ; 在用户窗体图标或者是在用户窗体上单击鼠标右键, 然后在弹出的菜单中选择 查看代码, 则会出现用户窗体代码窗口 169

14 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 图 6-11 窗体代码窗口 2. 在 VBE 编辑器中输入 VBA 代码选择 VBE 菜单 插入 用户窗体 模块 类模块 来插入模块或用户窗体以及相应的代码窗口, 也可以在 工程资源管理器 中单击鼠标右键, 从弹出的菜单中选择 插入 用户窗体 模块 类模块 来实现上面的操作 在获取相应的代码模块窗口后, 就可以输入 VBA 代码 在 VBE 编辑器的代码模块中输入 VBA 代码, 通常有以下几种方法 : 手工键盘输入 使用宏录制器, 即选择 开发工具 选项卡, 单击 代码 组 录制宏 按钮, 将所进行的操作自动录制成宏代码 复制 / 粘贴代码, 即将现有的代码复制后, 粘贴到相应的代码模块中 导入代码模块, 即在 VBE 编辑器中选择菜单 文件 导入文件 或在 工程资源管理器 的任一对象上右击鼠标选择菜单 导入文件, 选择相应的代码文件导入 如果某个模块不需要, 可以选择菜单 文件 移除模块, 也可以在相应的模块上单击鼠标右键, 从弹出的菜单中选择 移除模块 ; 也可以选择菜单 文件 导出文件 或在相应的模块上单击鼠标右键后, 从弹出的菜单中选择 导出文件, 将移除的模块保存在相应的文件夹中, 以后可以对其进行导入, 从而加以利用 3. 调试 VBA 代码在 VBE 编辑器的菜单中, 有两项与调试运行有关的菜单项, 即 调试 菜单和 运行 菜单, 提供了各种调试和运行的手段 常用到的有以下几个 : 逐语句 可以按 F8 键对代码按顺序一条一条语句运行, 从而找出语句或逻辑错误 设置断点 在可能存在问题的语句处设置断点 ( 可通过在相应代码前的空白处, 将会出现一个深红色的椭圆即断点 ), 当程序运行至断点处时, 会中止运行 在语句的适当部位设置 Debug.Print 语句, 运行后其结果会显示在 立即窗口 中, 可以此测试或跟踪变量的值 在 立即窗口 中测试 对值的测试或跟踪可以以? 开头, 在 立即窗口 中输入需要测试值的语句, 按 Enter 回车键后将立即出现结果 ; 对执行语句的测试, 可直接在 立即窗口 中输入, 按 Enter 回车键后将执行 170

15 第 6 章 Excel 2007 高级应用 可以按 F5 键直接运行光标所在位置的子程序 在执行程序后, 必须在 Excel 工作表中查看所得到的结果 可以用鼠标单击 VBE 编辑器左上角的 Excel 图标或者是按 Alt+F11 组合键切换到 Excel 界面 4. 利用 VBA 帮助系统如果遇到疑问或错误, 可以利用 Excel 自带的 VBA 帮助系统 在程序有疑问处直接按 F1 键后, 将会出现相关主题帮助信息 可以按 F2 键, 调出 对象浏览器 窗口 ( 如图 6-12 所示 ), 在搜索文本框中输入需要帮助的关键词, 将会在 搜索结果 中出现一系列相关的对象及方法 属性列表, 单击相应的对象则会在 类 和 成员 列表框中显示相应的对象和方法 属性成员列表, 在成员列表中相应的项目上按 F1 键即会出现详细的帮助信息 图 6-12 对象浏览器 窗口 VBA 程序设计 基本数据类型和表达式 1. 基本数据类型数据是程序处理的对象, 数据可以依其本身的特点进行分类 VBA 定义了多种数据类型, 以下介绍几种常用的基本数据类型, 见表 6-7 表 6-7 常用的基本数据类型 数据类型类型名类型标识符字节取值范围 整数型 Integer % ~32767 长整数型 Long & ~ 单精度型 Single! 4 负数 : E38 ~ E-45 正数 : E-45 ~ E38 171

16 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 续表 数据类型类型名类型标识符字节取值范围 双精度型 Double # 8 负数 : E308~ E~324 正数 : E-324~ E308 日期型 Date 年 1 月 1 日 ~9999 年 12 月 31 日 货币型 ~ 字符串型 String $ 2^31 2^16 变长 定长 小数点型 Decimal 无 12 变比因子为 0( 没有小数位 ) 的情形下, 最大的可能值为 +/ 在有 28 个小数位的情况下最大值为 +/ 而最小的非零值为 +/ 字节型 Byte 无 1 0~255 布尔型 Boolean 无 2 False, True 变体型 Variant 无可变可以是任何类型对象型 Object 无 4 可以赋值为任何对象的引用 (1) 整数型 Integer 存储为 16 位 (2 个字节 ) 的数值形式, 其范围为 ~32767 的所有整数, 类型声明字符是百分比符号 (%) 即 dim I as integer 与 dim i % 等效 Iong 存储为 32 位 (4 个字节 ) 有符号的数值形式, 其范围从 ~ 的所有整数 类型声明字符为与号 (&) (2) 实数 Single 存储为 IEEE 32 位 (4 个字节 ) 浮点数值的形式, 它的范围在负数的时候是从 E38 到 E-45, 而在正数的时候是从 E-45 ~ E38 但它只有七位有效数字 Single 的类型声明字符为感叹号 (!) Double 存储为 IEEE 64 位 (8 个字节 ) 浮点数值的形式, 它能表示绝对值小于 的数 有十五位有效数字 Double 的类型声明字符是数字符号 (#) (3) 字符串 String 有 2 种 : 变长 ( 最多可包含大约 20 亿 ( 2^31) 个字符 ) 与定长 ( 可包含 1 到大约 64K ( 2^16 ) 个字符 ) String 的字符码的范围是 0 ~255 字符集的前 128 个字符 (0 ~ 127) 对应于标准的 U.S. 键盘上的字符与符号 这前 128 个字符与 ASCII 字符集中所定义的相同 后 172

17 第 6 章 Excel 2007 高级应用 128 个字符 (128 ~ 255) 则代表特殊字符, 例如国际字符, 重音符号, 货币符号及分数 String 的类型声明字符为美元号 ($) (4) 逻辑型 ( 布尔型 ) Boolean 存储为 16 位 (2 个字节 ) 的数值形式, 但只能是 True 或是 False Boolean 变量的值显示为 True 或 False( 在使用 Print 的时候 ), 或者 #TRUE# 或 #FALSE#( 在使用 Write # 的时候 ) 使用关键字 True 与 False 可将 Boolean 变量赋值为这两个状态中的一个 当转换其他的数值类型为 Boolean 值时,0 会转成 False, 而其他的值则变成 True 当转换 Boolean 值为其他的数据类型时,False 成为 0, 而 True 成为 -1 (5) 变体型 Variant 是一种特殊的数据类型, 除了定长 String 数据及用户定义类型外, 可以包含任何种类的数据 ( 似乎是万能数据类型, 从某种程度上可看做类似于单元格格式中的常规 ) Variant 也可以包含 Empty Error Nothing 及 Null 等特殊值 2. 标识符标识符是一种标识变量 常量 过程 函数 类等语言构成单位的符号, 利用它可以完成对变量 常量 过程 函数 类等的引用 命名规则如下 : 必须以字母开头, 由字母 汉字 数字和下划线组成的字符串 不能与 VB 保留字重名, 如 public private dim integer 等 字符串长度小于 40, 用汉字的长度可达 254 个字符 字符串中字母不区分大小写, 如 AB Ab ab 视为同一个标识符 3. 常量常量是指在程序运行的整个过程中其值始终不可改变的量 在 VBA 中有普通常量 符号常量和系统常量 3 类 (1) 普通常量普通常量的类型和值由它本身的表示形式决定, 包括以下几种 整型常量整型常量是以文字形式出现的整数, 包括正整数 负整数和零 整型常量的表示形式有十进制 八进制和十六进制, 其一般形式如下 十进制整型常量 :[±] 若干个 0~9 的数字八进整型常量 :[±] & 或 &0 若干个 0~7 的数字十六进整型常量 :[±]&H 若干个 0~9 的数字及 A~F 字母 ( 大小写均可 ) 整型常量可以用后加 & 符号表示长整型 实型常量十进制小数形式它是由正负号 (+,-) 数字 (0~9) 和小数点 (.) 或类型符号 (! #) 组成, 即 ±n,±n! 或 ±n#, 其中 n 是 0~9 的数字 例如 : ! 16# 指数形式 ±ne±m 或 ±n.ne±m,±n D ±m 或 ±n.n D ±m 例如 :1.236E+3 和 1.236D+3 相当于 或 字符串常量字符串常量是用一对双引号括起的字符序列 例如 :»abcde»123»book 173

18 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 逻辑常量逻辑常量只有两个 :True,False 将逻辑数据转换成整型时 :True 为 -1,False 为 0 ; 其他数据转换成逻辑数据时 : 非 0 为 True,0 为 False 日期常量用 # 号把日期和时间的字符括起来, 作为日期型常量 例如 : #06/12/11#,# :30:00 PM# (2) 符号常量除了直接用文字表示常量外, 还可以为常量命名, 这就是符号常量 符号常量在使用之前要首先声明 常量声明形式 : Const 符号常量名 [As 类型 ]= 表达式 [, 符号常量 = 表达式 ] 常量说明语句若在过程内部, 则符号常量只能在该过程内有效 ; 若说明语句出现在窗体代码的声明部分, 则窗体以及窗体中各控件的事件过程都能引用该符号常量 表达式可以为文字常数, 也可为包含除 Is 之外的任意算术操作符和逻辑操作符所构成的表达式 例如 : Const PAI As Integer= Const NAME As String= Zhang (3) 系统常量 VB 系统提供了应用程序和控件的系统定义常数 它们存放于系统的对象库中, 在 对象浏览器 中的 VB 和 VBA 对象库中列举了 Visual Basic 的常数 在 VBA 对象库中,color 常数有 vbblack vbred vbgreen vbyellow 等, 例如 : TextBox1.backcolor=vbYellow 将文本框 TextBox1 背景设置为红色 4. 变量在程序的执行过程中其值可以变化的量称为变量 VBA 程序中的变量在使用前应该进行声明, 指定数据类型 声明变量 dim 变量名 as 数据类型 [, 变量名 as 数据类型 ] 例如 : dim i as integer 或 dim i% 声明整数变量 i dim n as long, s as string 或 dim n&,s$ 声明长整数变量 n 和一个字符串变量 s 注意 : 当声明变量省略 as 数据类型 时,VBA 会自动将其默认为 Variant 当一个变量的类型为 Variant 时可以存放任何类型的数据, 并且该变量的实际类型随其存入的值的类型的变化而变化 如 Sub test() Dim v v 为 Variant 型 v = " 你好! " v 为 string 型 v = 123 v 为 integer 型 v = True v 为 boolean 型 End Sub 174

19 第 6 章 Excel 2007 高级应用输入代码后按 F8 键来单步执行这个代码, 在窗口中可看到 v 的类型变化 VBA 提供了 VarType 函数来测定变量的实际数据类型, 格式 VarType( 变量名 ) 注意 : dim i1,i2, i3 as integer 'i1 i2 为 Variant 型,i3 为 integer 型 5. 运算符与表达式表达式是由运算符 括号将数字 字符串 变量 常数 对象成员 函数等连接起来的式子 (1) 算术运算符和表达式算术运算符包括 +( 加法 ) - ( 减法 ) / ( 除法 ) \( 整除 ) Mod( 取模 ) ^( 乘幂 ) 其中 - 作负号时为一元运算符, 其余都为二元运算符 这些基本运算符的意义与数学中相应符号的意义是一致的 它们之间的相对优先级关系与数学中也是一致的, 算术运算符的优先顺序依次为 : 乘幂运算符 (^) 乘法和除法运算符 (* /, 两者没有优先顺序 ) 整除运算符 (\) 取模运算符 (Mod) 加法和减法运算符 (+ -, 两者没有优先顺序 ) 若在同级运算符, 则从左到右运算 使用括号可以改变优先顺序 / 除法运算符, 将 2 个数值或数值表达式相除, 其中除数不能为零, 否则会得到一个错误 \ 整除运算符 将 2 个数值或数值表达式相除, 并返回一个整数, 即舍掉余数或者小数部分 例如,7\3 的值为 2 (2) 连接运算符和表达式 & 连接运算符, 用于连接字符串 它能把一些字符串变量连接在一起, 形成一个新的字符串 在进行连接运算之前, 任何非字符串变量或表达式都应转换为字符串 结果的数据类型为 String 类型 + 混合连接运算符, 它运用很灵活, 但会与加法运算符相混, 表达式的结果数据类型取决于表达式的数据类型 当 2 个表达式都是数值数据时, 用 & 运算符会将两个数值数据连接, 如 5 & 1 连接后为 51, 但用 + 运算符后, 会将两数值相加得到其结果, 如 5+1 进行连接后为 6 当 2 个表达式都是字符串 (Sting) 时, 将对 2 个字符串进行连接为一个字符串 当 2 个表达式为空时,+ 运算符的结果为 0, 而 & 运算符的结果为 Null 值 当一个表达式为数值类型数据, 另一个表达式为字符串类型数据时,+ 运算符将产生类型不匹配的错误, 而 & 运算符则将 2 个表达式连接 未声明变量时, 当一个表达式为数字, 另一个表达式为字母时,+ 运算符和 & 运算符的结果均为数字, 如 j=7,k=student,i=j+k 与 i=j&k 的结果均为 7 当 2 个表达式都为空时,+ 运算符结果为 0, 而 & 运算符结果为 Null 值 (3) 比较运算符和表达式比较运算符包括 <( 小于 ) >( 大于 ) =( 等于 ) >=( 大于或等于 ) <=( 小于或等于 ) <>( 不等于 ), 用于数据元素的比较, 其一般的语法为 : 结果 = 表达式 1 < 比较运算符 > 表达式 2 结果为 True(1) False(0) 或者为 Null 其中比较运算符可以单独使用, 也可以两两结合使用 如果表达式 1 或者表达式 2 本身为 Null 时才会产生 Null 的结果 比较运算符可用于数值比较或字符串变量比较, 也可用于数值与字符串的比较 如果其中一个表达式是数值, 另一个是字符串, 则数值表达式总是 小于 字符串表达式 ; 如果都是字 175

20 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 符串, 则最大的字符串就是最长的字符串 ; 如果字符串一样长, 则小写的大于大写的 还有一个比较运算符, 即 Is 运算符, 可用于判断两个对象变量是否指向同一个对象, 其语法为 : 结果 = 对象 1 Is 对象 2 如果对象 1 和对象 2 都指向同一个对象, 其结果为 True ; 否则, 结果为 False 还可以用 Is 运算符来判断一个对象变量是否符合要求, 例如, 条件判断语句 If objvar Is Nothing Then 表明, 如果对象变量 objvar 没有对象引用, 则为 True, 执行 Then 后面的语句 比较运算符的优先顺序 如果多个比较运算符出现在同一行代码中, 则按从左到右的顺序计算 (4) 逻辑运算符和表达式逻辑运算符允许对一个或多个表达式进行运算, 并返回一个逻辑值 VBA 的逻辑运算符包括 :And( 与 ) Or( 或 ) Not( 非 ) Eqv( 等价 ) Imp( 蕴含 ) Xor( 异或 ) And 与运算, 如果表达式 1 和表达式 2 都是 True, 则结果返回 True ; 只要其中一个表达式为 False, 其结果就是 False ; 如果有表达式为 Null, 则结果为 Null 例如,If x>1 And y<10 Then, 表示如果 x 的值大于 1 且 y 的值小于 10 时, 就执行 Then 后面的代码 又如, 语句 MsgBox ActiveSheet.Name= Sheet2 And Range( A1 )=1 表示如果当前工作表名为 Sheet2 并且当前工作表上的单元格 A1 的值为 1 时, 消息框将显示 True Or 或运算, 即如果表达式 1 或者表达式 2 为 True, 或者表达式 1 和表达式 2 都为 True, 则结果为 True ; 只有 2 个表达式都不是 True 时, 其结果才为 False ; 如果有表达式为 Null, 则结果也是 Null 例如,If x>1 Or y<10 Then, 表示如果 x 的值大于 1, 或者 y 的值小于 10, 则执行 Then 后面的代码 又如, 语句 MsgBox Range( A1 )=1 Or Range( B1 )=2 表示如果当前工作表中的单元格 A1 中的值为 1 或者单元格 B1 中的值为 2 时, 消息框将显示 True Not 非运算, 如果表达式为 True, 则 Not 运算符使该表达式变成 False ; 如果表达式为 False, 则 Not 运算符使该表达式变成 True ; 如果表达式为 Null, 则 Not 运算符的结果仍然是 Null 例如,If Not IsError(x) Then, 如果 IsError 返回 False 则执行 Then 后面的代码, 表示 x 中不包含错误 又如, 语句 Selection.Font.Bold=Not Selection.Font.Bold, 其中 Bold 属性的值为 True 或 False, 使用 Not 运算符会将值 False 改为 True, 将值 True 改为 False, 这样可用于切换所选单元格是否 加粗 这也是 VBA 程序编写时的一个技巧, 用户可以运用 Not 运算符模仿工具栏 加粗 斜体 等按钮的工作模式, 如当点击 加粗 按钮时, 所选单元格中的字体将加粗显示, 再次单击 加粗 按钮, 单元格中的字体又正常显示 Eqv 等价运算, 判断 2 个表达式是否相等, 当 2 个表达式都是 True 或者都是 False 时, 结果返回 True ; 若一个表达式为 True 而另一个表达式为 False 时, 结果返回 False Imp 蕴含运算, 其语法为 : 结果 = 表达式 1 Imp 表达式 2 不管表达式 1, 当表达式 2 为真时, 其结果为 True ; 当 2 个表达式都为 False, 或者表达式 1 为 False, 表达式 2 为 Null 时, 其结果为 True ; 当表达式 1 为 True, 表达式 2 为 False 时, 结果为 False ; 其他情况则为 Null Xor 异或运算, 用于判断 2 个表达式是否不同 若 2 个表达式都是 True 或都是 False 176

21 第 6 章 Excel 2007 高级应用时, 其结果就是 False ; 如果只有一个表达式是 True, 其结果就是 True ; 如果 2 个表达式中有一个是 Null, 其结果是 Null 逻辑运算符的优先顺序依次为 Not And Or Xor Eqv Imp, 如果在同一行代码中多次使用相同的逻辑运算符, 则从左到右进行运算 如果在一行代码中包括多个运算符, 则其进行运算的优先顺序是不相同的 运算符的优先级会影响到表达式的最终结果 当一行代码中包括几类运算符时, 其优先顺序为 : 算术运算符 连接运算符 比较运算符 逻辑运算符 如果要改变同类或不同类运算符的优先顺序, 可以使用括号, 括号内的表达式总比括号外的优先进行运算 基本控制结构结构化程序设计的基本原则是 : 任何程序都可以且只能由 3 种基本流程结构构成, 即顺序结构 选择结构和循环结构 1. 顺序结构顺序结构是 3 种结构中最简单的一种, 语句按照先后顺序, 逐次执行 在顺序结构中, 使用最多的是赋值语句 赋值语句语法形式 : 变量名 = 表达式执行过程说明 : 计算右边表达式的值, 然后赋给左边的变量 例如 :x=y+6 2. 选择结构选择结构是根据计算所得的表达式的值来判断应选择执行哪一个流程的分支 VBA 中提供了多种形式的条件语句来实现选择结构 (1) 单分支语句语法形式 : If < 表达式 > Then 语句 ( 一句或多句语句 ) End If 或 If < 表达式 > Then < 语句 > 说明 : 表达式 : 可以是关系表达式 逻辑表达式 算术表达式 表达式值非零为 True, 零为 False 语句 : 可以是一句, 也可以是多句语句 功能 : 当表达式的值为 True, 执行 Then 后面的语句, 否则不做任何操作 例如 : If i Mod 2 = 1 The 年 MsgBox " 你输入的是 " & i & ", 它是一个奇数 " Endif (2) 双分支语句 If 表达式 Then 语句 1 Else 语句 2 177

22 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) End If 178 或 If 表达式 Then 语句 1 Else 语句 2 功能 : 当表达式的值为 True 时, 执行 Then 后面的语句 1, 否则执行 Else 后面的语句 2 例如 : Sub jopd() Dim i As Integer i =InputBox( 请输入一个整数 ",,0) If i Mod 2 = 0 Then MsgBox " 你输入的是 " & i & ", 它是一个偶数 " Else MsgBox " 你输入的是 " & i & ", 它是一个奇数 " End If End Sub (3) 多分支语句 If 表达式 1 Then 语句 1 ElseIf 表达式 2 Then 语句 2 [Else 语句 n+1] End If 功能 : 根据不同的表达式值确定执行哪个语句 例如 : Sub zfpd() Dim i AsInteger i =InputBox(" 请输入一个整数 ",,0) Ifi > 0 Then MsgBox " 你输入的是 " & i & ", 它是正整数 " Elseif i<0 then MsgBox " 你输入的是 " & i & ", 它是负整数 " Else MsgBox" 你输入的是零 " EndIf End Sub (4)If 语句的嵌套 If 表达式 1 Then If 表达式 2 Then End If End if

23 第 6 章 Excel 2007 高级应用例如 : 比较 3 个数值变量 x y 和 z 的值, 通过交换, 使得 x>y>z x = 15: y = 20: Z = 10 If x < y Then t = x: x = y: y = t If y < Z Then t = y: y = Z: Z = t If x < y Then t = x: x = y: y = t End If End If End If Debug.Print x, y, Z (5)Select case 语句 Select Case 测试表达式 Case 表达式列表 1 语句 1 Case 表达式列表 2 语句 2 [Case Else 语句 n+1] End Select 例如 : Sub test1() Dim Number As Integer Number =InputBox( 请输入一个整数 ",,0) ' 输入变量初值 Select Case Number 判断 Number 的值 Case 1 To 5 ' Number 的值在 1 到 5 之间, 包含 1 和 5 MsgBox 在 1 和 5 之间 " Case 6, 7, 8 Number 的值是 6,7,8 MsgBox 在 6 和 8 之间 " Case Is >= 9 ' Number 的值为 >= 9 MsgBox 大于 8" Case Else 其他数值 MsgBox 小于 1" End Select End Sub 3. 循环结构在一定条件下反复执行一段语句的流程结构 (1)For next 语句 For 循环变量 = 初值 To 终值 [ Step 步长 ] 179

24 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 语句 [ Exit For ] 语句 N ext 循环变量例如 : Sub test2() Dim sum As Integer Sum=0 For i=1 to 100 Step 1 Sum=sum+1 Next End Sub (2)For each next 语句 For each 变量 in 集合对象 / 数组语句 1 [exit for ] 语句 2 Next 例如 : Sub test3() Dim cell As Variant Worksheets(1).Select For Each cell In Range( a1:g10 ) Cell.value= Test Cell.Font.Size=18 Cell.Font.Color=RGB(0,255,10) Next End Sub (3)Do Loop 语句形式 1 : Do While Unit < 条件 > 语句 [Exit Do 语句 ] Loop 形式 2 : Do 语句 [Exit Do 语句 ] Loop While Until < 条件 > 180

25 第 6 章 Excel 2007 高级应用例如 : Sub testkey() Dim psw As String Do While psw <> "12315" psw = InputBox( 请输入密码 ", " 输入密码 ", "") Loop MsgBox 通过验证 " End Sub (4)GoTo 语句无条件地转向到标号或行号指定的那行语句 GoTo[ 标号 行号 ] (5)Exit 语句 ( 退出语句 ) 用于退出某种控制结构的执行 形式 : Exit For Exit Do Exit Sub Exit Function (6)End 语句 ( 结束语句 ) 独立的 End 语句用于结束一个程序的运行, 它可以放在任何事件过程中 过程与函数过程和函数就是执行特定功能的一段程序 函数也是过程, 不过它是一种特殊 能够返回值的 Function 过程 这是过程与函数之间最大的区别 1. 过程过程是构成程序的一个模块, 往往用来完成一个相对独立的功能 过程可以使程序更清晰 更具结构性 Sub 过程是常用 VBA 中常用的过程 过程声明语法格式如下 : [ Public Private ] [ Static ]Sub 子程序名 ( [ 参数 ] ) [As 数据类型 ] [< 子程序语句 >] [Exit Sub] [< 子程序语句 >] End Sub 其中,Public 关键字使这个过程对于所有模块和所有模块的其他过程都可访问 Private 关键字使这个过程仅在同一模块中的其他过程中可访问 默认为 Public, 即所有模块均可访问的公有类型 Static 关键字规定在过程互相调用时保持过程的局域变量不变 参数 是用逗号分开的多个形式参数, 它有下列格式 : [ ByVal ByRef ] 变量名称 [ ( ) ][ As 类型 ] 在参数中,ByVal 指参数按值来传送 ;ByRef 指按地址来传送 (ByRef 是默认选项 ); 参数类型缺省情况下为变体型 最后,End Sub 结束过程的定义 也可以用 Exit Sub 关键字从一个子程序中立即退出 Sub 过程的参数有两种传递方式 : 按值传递 (ByVal) 和按地址传递 (ByRef) 例如 : Sub password (ByVal x as integer, ByRef y as integer) If y=100 then y=x+y else y=x-y 181

26 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) x=x+100 End sub Sub call_password () Dim x1 as integer Dim y1 as integer x1=12 y1=100 Call password (x1,y1) 调用过程方式 :Call 过程名 ( 参数 1, 参数 2 ) End sub 用 debug.print x1,y1 语句, 显示结果 : 说明 y1 按地址传递改变了值, 而 x1 按值传递, 未改变原值子程序是把代码组织到一起 完成一定功能 使程序更简单的代码块 它不返回值, 但是可以通过参数给子程序传值 例如 : 判断过程的调用是否传送参数 Sub CountFiles( Optional intmaxfile As Variant ) If IsMissing( intmaxfile ) Then ' 没有传送 intmaxfile MsgBox(" 没有传送参数 ") Else ' 传送了 intmaxfile MsgBox(" 传送了参数 ") End If End Sub 子程序又分为 2 种 : 事件过程和通用过程 (1) 事件过程当发生窗体或控件的事件 ( 如单击 双击等 ) 时, 则调用相应对象的事件处理程序, 它们就是事件过程, 其名称由 _ 连接对象名称和事件名称构成, 例如 Form1_Click 等 (2) 通用过程把许多事件过程的共同语句抽取出来构成一个通用过程, 由各事件过程来调用, 则可压缩空间和节省调试时间 过程的使用调用子程序的形式有 2 种 : (1)Call 子程序名称 ( 参数 1, 参数 2,, 参数 n) (2) 子过程名称 ( 参数 1, 参数 2,, 参数 n)' 这里的参数均为实参 过程的参数在调用过程时, 参数按它们的传递次序传递到被调用过程或函数中, 传递方式分为 2 种 : (1) 按值传递参数用关键字 ByVal 指出按值传递 把变量传递给过程, 则过程对该参数值的改变不会影响调用者变量的值 例如 : 182

27 第 6 章 Excel 2007 高级应用 Sub Result(ByVal intnumber As Integer) intnumber = intnumber + 10 End Sub 调用程序为 : x = 1 'x 为 1 Call Result( x ) y = x+5 'x 仍为 1,y 为 6 (2) 按地址传递参数把变量的内存地址传递给过程, 在过程中按地址可访问到变量实际的值, 从而可以真正地改变变量原来的值, 默认 ByRef, 则认为是按地址传递参数 例如 : Sub Result(ByRef intnumber As Integer) intnumber = intnumber + 10 End Sub 调用程序为 : x = 1 'x 为 1 Call Result( x ) y = x+5 'x 为 11,y 为 16 除了以上 2 种方式外, 还可以用关键字 Optional 指定可选参数 例如 : Sub Result(ByVal intnumber As Integer,Optional strname As String ) intnumber = intnumber + 10 If IsMissing( strname ) Then ' 没有传送 strname MsgBox(" 没有传送参数 ") Else ' 传送了 strname MsgBox(" 传送了参数 ") End If End Sub 则调用语句可为 Call Result(10, "LiLi") 也可以是 Call Result( 10 ) 还可以给可选参数指定缺省值 例如 : Sub Result(ByVal intnumber As Integer,Optional strname As String ="LiLi") intnumber = intnumber + 10 If IsMissing( strname ) Then ' 没有传送 strname MsgBox(" 没有传送参数 ") Else ' 传送了 strname MsgBox(" 传送了参数 ") 183

28 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) End If End Sub 则调用语句 Call Result(10, "LiLi") 和 Call Result( 10 ) 结果相同, 还可以使用关键字 ParamArray 来说明参数个数是不确定的 例 : 求和函数 Dim x As Integer Dim y As Integer Dim z As Integer Sub Sum( ParamArray Summ() ) For Each x In Summ y = y + x Next x z = y End Sub Private Sub Command1_Click() Sum( 1,2,3,4,5,6 ) Sum( 5,15,20 ) End Sub 2. 函数函数是带有返回值的过程 它可以完成子程序所能完成的工作, 二者不同之处就在于函数可以返回值 一个函数就是一个可以调用和传递参数的代码块, 使用函数可以把整个代码分成便于管理的几个部分 函数声明格式为 : [ Public Private ] Function < 函数名称 > ( [ 参数列表 ] ) [As 类型 ] [< 语名块 >] [Exit Function] [ 函数名称 = 返回值 ] End Function 参数 是用逗号分开的多个形式参数, 它有下列格式 : [ ByVal ByRef ] 变量名称 [ ( ) ] [ As 类型 ] 函数名称 = 返回值是为函数名赋值, 从函数中返回一个值 End Function 结束过程的定义 也可以用 Exit Function 关键字从一个子程序中退出 例如 : Private Sub Command1_Click() Dim intresult As Integer intresult = Add1(6) MsgBox( "Result = " & Str$( intresult ) ) End Sub Function Add1( intadd1 As Integer ) As Integer 184

29 第 6 章 Excel 2007 高级应用 Add1=intAdd + 1 End Function 这个例子可以改为一个记数器 例如 : Private Sub Command1_Click() Dim intresult As Integer intresult = Counter() MsgBox( "Result = " & Str$( intresult ) ) End Sub Function Counter() As Integer Dim intcountvalue As Integer intcountvalue = intcountvalue + 1 Counter = intcountvalue End Function 运行此程序, 用户每单击一次按钮, 调用 Counter() 函数以增加单击按钮的次数, 并且结果显示在一个信息框中 但是此程序并没有实现计数器的功能, 因为返回值总是 1, 为什么呢? 这是因为, 在 Counter() 函数被调用以后,Counter() 函数中的计数器变量 intcountvalue 被重新初始化 如何解决呢? 解决方案有 2 种, 一种是把 intcountvalue 声明为 Static 类型 这就是说, 在调用 Counter() 函数时, 变量可以保持它的值 另一种方案是声明整个函数为 Static 类型 这意味着它中间的全部变量都将保持原有的值 两种方案的代码分别如下 : 第 1 种方案 : Private Sub Command1_Click() Dim intresult As Integer intresult = Counter() MsgBox( "Result = " & Str$( intresult ) ) End Sub Function Counter() As Integer Static intcountvalue As Integer intcountvalue = intcountvalue + 1 Counter = intcountvalue End Function 第 2 种方案 : Private Sub Command1_Click() Dim intresult As Integer intresult = Counter() MsgBox( "Result = " & Str$( intresult ) ) End Sub 185

30 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) Static Function Counter() As Integer Dim intcountvalue As Integer intcountvalue = intcountvalue + 1 Counter = intcountvalue End Function 调用函数的方式是在表达式中写出函数名称和各个实参, 其返回值则可用于表达式中 例如 : 计算圆的面积 Function Area( Radius As Single) As Double Const PI = Area = PI * Radius^2 End Function 调用方法如下 : Result = Area( 5 * 6.3) 函数实际是实现一种映射, 它通过一定的映射规则, 完成运算并返回结果 参数传递也有两种 : 按值传递 (ByVal) 和按地址传递 (ByRef) 例如 : Function password(byval x as integer, byref y as integer) as boolean If y=100 then y=x+y else y=x-y x=x+100 if y>150 then password=true else password=false End Function Sub call_password () Dim x1 as integer Dim y1 as integer x1=60 y1=100 if password(x1,y1) then debug.print x1 end if End sub 常用的 VBA 内部函数 在 VBA 程序语言中有许多内置函数, 可以帮助程序代码设计和减少代码的编写工作, 常 用的 VBA 内部函数如下 : 1. 测试函数 IsNumeric(x) ` 是否为数字, 返回 Boolean 结果,True or False IsDate(x) ` 是否是日期, 返回 Boolean 结果,True or False IsEmpty(x) ` 是否为 Empty, 返回 Boolean 结果,True or False IsArray(x) ` 指出变量是否为一个数组 IsError(expression) ` 指出表达式是否为一个错误值 IsNull(expression) ` 指出表达式是否不包含任何有效数据 (Null) 186

31 第 6 章 Excel 2007 高级应用 IsObject(identifier) ` 指出标识符是否表示对象变量 2.VBA 数学函数 Sin(X) Cos(X) Tan(X) Atan(x) 三角函数, 单位为弧度 Log(x) 返回 x 的自然对数 Exp(x) 返回 e x Abs(x) 返回绝对值 tint(number) Fix(number) 都返回参数的整数部分, 区别 :Int 将 -8.4 转换成 -9, 而 Fix 将 -8.4 转换成 -8 Sgn(number) 返回一个 Variant (Integer), 指出参数的正负号 Sqr(number) 返回一个 Double, 指定参数的平方根 VarType(varname) 返回一个 Integer, 指出变量的子类型 Rnd(x) 返回 0-1 之间的单精度数据,x 为随机种子 3.VBA 字符串函数列表 Trim(string) 去掉 string 左右两端空白 Ltrim(string) 去掉 string 左端空白 Rtrim(string) 去掉 string 右端空白 Len(string) 计算 string 长度 Left(string, x) 取 string 左段 x 个字符组成的字符串 Right(string, x) 取 string 右段 x 个字符组成的字符串 Mid(string, start,x) 取 string 从 start 位开始的 x 个字符组成的字符串 Ucase(string) 转换为大写 Lcase(string) 转换为小写 Space(x) 返回 x 个空白的字符串 Asc(string) 返回一个 integer, 代表字符串中首字母的字符代码 Chr(charcode) 返回 string, 其中包含有与指定的字符代码相关的字符 4.VBA 转换函数列表 CBool(expression) 转换为 Boolean 型 CByte(expression) 转换为 Byte 型 CCur(expression) 转换为 Currency 型 CDate(expression) 转换为 Date 型 CDbl(expression) 转换为 Double 型 CDec(expression) 转换为 Decemal 型 CInt(expression) 转换为 Integer 型 CLng(expression) 转换为 Long 型 CSng(expression) 转换为 Single 型 CStr(expression) 转换为 String 型 CVar(expression) 转换为 Variant 型 Val(string) 转换为数据型 Str(number) 转换为 String 型 5.VBA 时间函数 Now : 返回一个 Variant (Date), 根据计算机系统设置的日期和时间来指定日期和时间 187

32 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) Date : 返回包含系统日期的 Variant (Date) Time : 返回一个指明当前系统时间的 Variant (Date) Timer : 返回一个 Single, 代表从午夜开始到现在经过的秒数 TimeSerial(hour, minute, second) : 返回一个 Variant (Date), 包含具有具体时 分 秒的时间 DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) : 返回 Variant (Long) 的值, 表示两个指定日期间的时间间隔数目 Second(time) : 返回一个 Variant (Integer), 其值为 0 ~ 59 之间的整数, 表示一分钟之中的某一秒 Minute(time) : 返回一个 Variant (Integer), 其值为 0~59 之间的整数, 表示一小时中的某一分钟 Hour(time) : 返回一个 Variant (Integer), 其值为 0~23 之间的整数, 表示一天之中的某一小时 Day(date) : 返回一个 Variant (Integer), 其值为 1 ~ 31 之间的整数, 表示一个月中的某一日 Month(date) : 返回一个 Variant (Integer), 其值为 1 ~ 12 之间的整数, 表示一年中的某一月 Year(date) : 返回 Variant (Integer), 包含表示年份的整数 Weekday(date, [firstdayofweek]) : 返回一个 Variant (Integer), 包含一个整数, 代表某个日期是星期几 6. 系统输入输出函数 (1)InputBox 函数打开一个对话框, 等待用户输入, 返回字符串类型的输入值 形式 :InputBox( 提示 [, 标题 ][, 默认值 ][,x 坐标位置 ][,y 坐标位置 ]) (2)MsgBox 函数和 MsgBox 过程打开一个信息框, 等待用户选择一个按钮 MsgBox 函数返回所选按钮的值,MsgBox 过程不返回值 MsgBox 函数形式 : 变量 [%]=MsgBox( 提示 [, 按钮 ][, 标题 ]) MsgBox 过程形式 : MsgBox 提示 [, 按钮 ][, 标题 ] 按钮项是一整型表达式, 决定信息框按钮的数目和类型及出现在信息框上的图标形式, MsgBox 的按钮设置参见表 6-8 和表 6-9 表 6-8 Buttons 参数值 常数 值 描述 vbokonly 0 只显示 OK 按钮 VbOKCancel 1 显示 OK 及 Cancel 按钮 VbAbortRetryIgnore 2 显示 Abort Retry 及 Ignore 按钮 VbYesNoCancel 3 显示 Yes No 及 Cancel 按钮 VbYesNo 4 显示 Yes 及 No 按钮 188

33 第 6 章 Excel 2007 高级应用 续表 常数 值 描述 VbRetryCancel 5 显示 Retry 及 Cancel 按钮 VbCritical 16 显示 Critical Message 图标 VbQuestion 32 显示 Warning Query 图标 VbExclamation 48 显示 Warning Message 图标 VbInformation 64 显示 Information Message 图标 vbdefaultbutton1 0 第 1 个按钮是默认值 vbdefaultbutton2 256 第 2 个按钮是默认值 vbdefaultbutton3 512 第 3 个按钮是默认值 vbdefaultbutton4 768 第 4 个按钮是默认值 表 6-9 Msgbox 的按钮返回值 常数 值 描述 vbok 1 OK vbcancel 2 Cancel vbabort 3 Abort vbretry 4 Retry vbignore 5 Ignore vbyes 6 Yes vbno 7 No VBA 对象模型的基础语法 Excel 的对象模型是通过层次结构很有逻辑地组织在一起的, 一个对象可以是其他对象的容器, 可以包含其他对象, 而这些对象又包含其他对象 位于顶层的是 Application 对象, 也是 Excel 应用程序本身, 它包含 Excel 中的其他对象, 如 Workbook 对象, 一个 Workbook 对象包含其他一些对象, 如 Worksheet 对象, 而一个 Worksheet 对象又可以包含其他对象, 如 Range 对象等 1. 属性对象都有属性, 用来描述或设置对象的特征 可以使用 VBA 来设置对象的属性, 也可以对一个对象的某些属性进行修改, 从而定义该对象, 还可以引用某对象的属性值 使用属性时, 应将对象和属性组合在一起, 中间用句点分隔 设置 / 修改对象属性的值 :< 对象 >.< 属性 >=< 值 > 访问 / 获取对象属性的值 :< 变量 >=< 对象 >.< 属性 > 例如 : 将 A6 单元格的值赋值为 Hello World! 189

34 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) Public Sub ExcelbaSub1() Dim TStr As String TStr = Hello World! Application.ActiveSheet.Range( B6 ).Value = TStr MsgBox TStr, 给对象属性赋值 End Sub 2. 方法方法可以改变对象的属性值, 也可以对储存在对象中的数据进行某些操作 对象都有方法, 方法就是在对象上执行的动作 如果要为对象指定方法, 应将对象和方法组合在一起, 中间用句点分隔 引用对象方法 :< 对象 >.< 方法 >[< 参数列表 >] 例如 : 当单击工作表上按钮时, 激活 A1 :C5 区域, 并设置为斜体 红色字体 Sub 设置 () Worksheets( Sheet1 ).Activate ' 激活 sheet1 表 Range(Cells(1, 1), Cells(5, 3)).Activate ' 激活 A1:C5 区域 Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True ' 设置斜体 Range(Cells(1, 1), Cells(5, 3)).Font.Color = vbred ' 设置字体红色 End Sub 3. 事件事件是一个对象可以辩认的动作, 如单击鼠标或按下某键等 并且可以写某些代码针对这些动作做出响应 4. 使用对象变量使用对象首先要定义属于哪个类的对象, 然后再给它赋一个具体的值 对象变量是代表一个完整对象的变量, 如工作表或单元格区域, 定义对象变量用 Dim 或 Public 语句来声明 语法 :Dim ( 或 Public) < 变量名 > AS < 对象名 > 例如 :Dim Data As Range ' 将变量 Data 声明为一个 Range 对象 在将变量声明为一个对象变量后, 用 Set 语句将某对象赋值给该变量 Set < 变量名 >=< 某对象 > 例如 :Dim s As Range ' 声明一个单元格对象变量 Set s=cells(2,3) ' 将单元格的值赋给单元格对象变量 综合案例 : 利用 Excel VBA 编辑器设计一个信息对话框, 利用这个对话框, 可以查找成绩表上所需信息 ; 设计一个录入信息窗体添加记录, 新建记录从窗体录入 190 图 6-13 成绩工作表图 6-14 成绩表上添加按钮

35 第 6 章 Excel 2007 高级应用实现上述功能具体操作如下 : 创建 成绩表 的工作表, 如图 6-13 所示 选择 开发工具 选项卡, 单击 控件 组的 插入 下拉按钮, 在工作表上创建两个按钮控件, 如图 6-14 所示 在按钮控件上右击, 选择快捷菜单 编辑文字 项, 更改控件名称 制作工作表 信息框 在 VBA 编辑器中, 双击工程窗口中的 This Workbook, 如图 6-15 所示 图 6-15 VBA 代码窗口在 This Workbook 代码窗口输入代码 : hd = MsgBox( 单击 查找 按钮, 可按姓名查找. 单击 添加记录 按钮, 可向工作表中添加数据, vbinformation, 注意 ) 保存并关闭工作簿, 再次打开该工作簿, 弹出如图 6-16 所示的信息框 图 6-16 信息框样式 制作 对话框 选择 开发工具 选项卡, 单击 代码 组的 录制宏 按钮, 在 宏名 文本框中输入 查找, 单击 确定 按钮, 如图 6-17 所示 开始录制新宏 单击 开始 选项卡, 选择 编辑 组的 查找与选择 项, 选择 查找 项 在 查找和替换 对话框中, 单击 查找 选项卡, 在 查找内容 栏中输入 赵兰 单击 搜索 下拉按钮, 在列表中选择 按列, 单击 查找下一个 按钮 单击 关闭 按钮 单击 代码 组的 停止录制 按钮, 完成录制 单击 代码 组的 宏 按钮, 弹出 宏 对话框, 如图 6-18 选择 查找 宏, 单击 编辑 按钮 191

36 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 图 6-17 录制新宏 对话框 图 6-18 宏 对话框 图 6-19 录制新宏的代码 图 6-20 修改宏代码 在 VBA 编辑器界面可以看到刚才录制新宏的代码, 如图 6-19 所示 在此代码上作修改, 如图 6-20 所示 保存代码, 单击 VBA 编辑器左上角的小按钮, 返回工作表, 在 查找 按钮上右击, 单击快捷菜单上 指定宏, 在 指定宏 对话框, 单击 宏名 栏中的 查找 项, 单击 确定 按钮 192

37 第 6 章 Excel 2007 高级应用 返回工作表, 单击 查找 按钮, 弹出 查找 对话框, 如图 6-21, 输入学生姓名, 单击 确定 按钮, 在工作表中, 活动单元格会锁定该学生位置, 如图 6-22 所示 图 6-21 查找 对话框图 6-22 锁定该学生位置 制作添加记录窗体右击 添加记录 按钮, 单击快捷菜单 指定宏 项 在 指定宏 对话框 宏名 栏中, 输入 添加记录, 单击 新建 按钮 单击 插入 用户窗体 项, 制作 添加记录 窗体, 如图 6-23 所示 图 6-23 制作 添加记录 窗体 作同样 双击 添加 命令按钮, 弹出代码窗口, 输入代码, 如图 6-24 所示 其他按钮代码输入操 图 6-24 代码窗口 193

38 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 图 6-25 添加记录 窗体对话框 单击工作表的 添加记录 按钮, 弹出 添加记录 窗体对话框, 如图 6-25 所示 输入记 录, 单击 添加 按钮, 记录便添加在工作表中 6.5 数据清单的管理 在实际工作中, 往往需要将数据按一定的顺序和层次进行放置, 使得用户对数据的管理更加容易和简便 Excel 可通过创建数据清单来管理数据, 当数据组织成一个数据清单后, 就可以对数据进行排序 筛选 分类汇总等操作 建立数据清单 数据清单是指包含一组相关数据的一系列工作表数据行 Excel 2007 在对数据清单进行管理时, 一般把数据清单看做是一个数据库 数据清单中的行相当于数据库中的记录, 行标题相当于记录名 数据清单中的列相当于数据库中的字段, 列标题相当于数据序列中的字段名 创建数据清单应遵守以下的准则 : 避免在一张工作表中建立多个数据清单, 如果在工作表中还有其他数据, 要与数据清单之间留出空行 空列 在数据清单的第一行里创建列标题, 列标题使用的各种格式应与清单中其他数据有区别 列标题名唯一且同列数据的数据类型和格式应完全相同 单元格中数据的对齐方式可用格式工具栏上的对齐方式按钮来设置, 不要用输入空格的方法调整 数据清单的创建, 操作同普通表格的创建完全相同 首先, 根据数据清单内容创建表结构 ( 第一行列标题 ), 然后移到表结构下的第一个空行, 键入信息, 就可把内容添加到数据清单中, 完成创建工作 194

39 第 6 章 Excel 2007 高级应用 数据排序 数据排序是指按一定规则对数据进行整理 排列, 这样可以为数据进一步处理做好准备 Excel 2007 提供了多种方法对数据清单进行排序, 可以按升序 降序的方式, 也可以由用户自定义排序 1. 数据清单排序首先使数据清单中的一个单元格为活动单元格, 选择 开始 选项卡中的 编辑 组, 单击 排序和筛选 下拉按钮中的 升序 降序 命令, 即可进行升序或降序排序 如果要对多列内容排序, 则需要在 编辑 选项卡中的 排序和筛选 组, 单击 排序和筛选 下拉按钮中的 自定义排序 命令, 弹出 排序 对话框, 在该对话框中, 按 添加条件 按钮, 添加主要关键字 次要关键字以及升序 降序的选择 ; 按 选项 按钮, 选择排序方向 排序方法 2. 数据的高级排序数据的高级排序是指按照多个条件对数据清单进行排序, 这是针对简单排序后仍然有相同数据的情况进行的一种排序方式 数据筛选 数据清单创建完成后, 对它进行的操作通常是从中查找和分析具备特定条件的记录, 而筛选就是一种用于查找数据清单中数据的快速方法 经过筛选后的数据清单只显示包含指定条件的数据行, 以供用户浏览 分析 1. 自动筛选自动筛选为用户提供了在具有大量记录的数据清单中快速查找符合某种条件记录的功能 操作步骤 : 首先在数据清单中单击一个单元格 ( 使之为活动单元格 ) 选择 开始 选项卡中的 编辑 组, 单击 排序和筛选 下拉按钮中的 筛选 命令 ; 或选择 数据 选项卡中的 排序和筛选 组, 单击 筛选 字段名称变成一个下拉列表框的框名, 单击下拉按钮, 直接选择数字 ; 或单击联级命令, 在 自定义自动筛选方式 对话框中, 设置筛选条件, 确定后即可 2. 高级筛选高级筛选适用于复杂条件的筛选 使用高级筛选功能, 必须先建立一个条件区域, 用来指定筛选的数据所需满足的条件 条件区域的第一行是所有作为筛选条件的字段名, 这些字段名与数据清单中的字段名必须完全一样 操作步骤 : 将筛选条件输入在条件区域 ( 在数据清单外任意一个区域 ), 条件区域的第一行为与数据清单相同的列名, 接下的行为对该列设置的条件 若要求筛选出来的数据同时满足某几个条件, 应将这几个条件写在同一行 ; 若只要求满足其中的一个条件, 应将这些条件写在不同的行 首先在数据清单中单击一个单元格 ( 使之为活动单元格 ) 选择 数据 选项卡中的 排序和筛选 组, 单击 高级 命令, 弹出 高级筛选 对话框 195

40 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 在 高级筛选 对话框中, 选择显示筛选结果的位置 ; 如果所选区域的数据清单不完全, 单击 列表区域 后的折叠按钮, 并选择数据清单区域, 再单击该按钮返回对话框, 单击 条件区域 后的折叠按钮, 并选择筛选条件所在的单元格, 再单击该按钮返回对话框 最后按 确定 按钮即可 数据的分类汇总 分类汇总是对数据清单进行数据分析的一种方法 分类汇总对数据库中指定的字段进行分类, 然后统计同一类记录的有关信息 统计的内容可以由用户指定, 也可以统计同一类记录的记录条数, 还可以对某些数值段求和 求平均值和求极值等 1. 创建分类汇总 Excel 2007 可以在数据清单中自动计算分类汇总及总计值 用户只需指定需要进行分类汇总的数据项 待汇总的数值和用于计算的函数 ( 例如 求和 函数 ) 即可 如果要使用自动分类汇总, 工作表必须组织成具有列标志的数据清单 在创建分类汇总之前, 用户必须先根据需要进行分类汇总的数据列对数据清单排序 分类汇总操作步骤 : 将数据清单中需要进行分类汇总的数据列区域的任一个单元格作为活动单元格, 选择 开始 选项卡中的 编辑 组, 单击 排序和筛选 下拉按钮中的 升序 降序 命令, 即可进行升序或降序排序 将数据清单区域中任一单元格作为活动单元格, 选择 数据 选项卡中的 分级显示 组, 单击 分类汇总 命令, 弹出 分类汇总 对话框 在 分类汇总 对话框中, 选择 分类字段 汇总方式 和 选定汇总项 等设置, 按 确定 按钮即可 删除分类汇总步骤 : 将数据清单区域中任一单元格作为活动单元格, 选择 数据 选项卡中的 分级显示 组, 单击 分类汇总 命令, 弹出 分类汇总 对话框 单击 全部删除 按钮, 即可删除分类汇总, 但不会删除数据清单数据 2. 隐藏或显示分类汇总为了方便查看数据, 可将分类汇总后暂时不需要使用的数据隐藏起来, 减小界面的占用空间 当需要查看隐藏的数据时, 可再将其显示 3. 分级显示分级显示可以将某个范围的单元格关联起来, 还可以将其折叠与展开 4. 数据合并通过合并计算, 可以把来自一个或多个源区域的数据进行汇总, 并建立合并计算表 这些源区域与合并计算表可以在同一工作表中, 也可以在同一工作薄的不同工作表中, 还可以在不同的工作簿中 数据透视表 数据透视表是一种可以快速汇总大量数据的交互式方法 使用数据透视表可以深入分析数值数据, 对数值数据进行分类汇总, 对最关注的数据子集进行筛选 排序 分组和有条件地设 196

41 第 6 章 Excel 2007 高级应用置格式 创建数据透视表操作步骤 : 将数据清单区域中任一单元格作为活动单元格 选择 插入 选项卡中的 表 组, 单击 数据透视表 下拉按钮中的 数据透视表 命令, 如图 6-26 所示, 弹出 创建数据透视表 对话框, 如图 6-27 所示 图 6-26 数据透视表 下拉按钮 图 6-27 创建数据透视表 对话框 在 创建数据透视表 对话框中, 选择要分析的数据, 选择放置数据透视表的位置 按 确定 按钮, 即可将数据透视表创建到指定的位置 若要创建数据透视图, 只需在第二步单击 数据透视表 下拉按钮中的 数据透视图 命令, 其他操作不变, 即可创建数据透视图 6.6 数据分析工具的应用 Excel 提供了非常实用的数据分析工具, 利用这些分析工具, 可解决财务管理中的许多问题, 例如财务分析工具 统计分析工具 工程分析工具 规划求解工具 方案管理器等 下面介绍财务管理与分析中常用的数据分析工具 数据表是一组命令的组成部分, 是一个单元格区域, 用于显示公式中某些值的更改对公式结果的影响 使用数据表, 可以通过一步操作计算出多种情况下的值 ; 还可以查看和比较由工作表中不同变化所引起的各种结果 单变量求解 单变量求解是解决假定一个公式要取的某一结果值, 其中变量的引用单元格应取值为多少的问题 在单变量数据表中使用的公式必须引用相同的输入单元格 工作表中的任何单元格都可用作输入单元格 尽管输入单元格不必是数据表的一部分, 但是数据表中的公式必须引用输入单元格 为单变量数据表添加公式时, 如果数据表是列方向的, 则在数据表首行现有公式右面的空白单元格中键入新公式 如果数据表是行方向的, 则在数据表首列现有公式下面的空白单元格中键入新公式 选定数据表, 包括含有新公式的行或列 在 数据 选项卡上的 数据工具 组中, 单击 197

42 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 假设分析, 然后单击 数据表 在弹出的数据表对话框中, 如果数据表是列方向的, 在 输入引用列的单元格 框中, 为输入单元格键入单元格引用 如果数据表是行方向的, 在 输入引用行的单元格 框中, 为输入单元格键入单元格引用 例如 : 某人购车需贷款 元, 贷款期限期 10 年, 贷款利率分别为 9.00% 9.25% 9.5% 的月还款额是多少? 输入基础数据, 在 D2 单元格中输入公式,B3 为输入单元格, 如图 6-28 所示 选择 C2 :D5 单元格区域, 单击 数据 选项卡中 数据工具 组的假设分析下拉按钮的 数据表 命令, 弹出 数据表 对话框, 在 输入引用列的单元格 栏中, 输入 $B$3, 如图 6-29 所示, 单击 确定 即可, 如图 6-30 所示 图 6-28 D2 单元格中输入公式 图 6-29 数据表 对话框 图 6-30 求解结果 如果知道要从公式获得的结果, 但不知道公式获取该结果所需的输入值, 那么使用 单变量求解 功能 具体操作如下 : 在 数据 选项卡上的 数据工具 组中, 单击 假设分析, 然后单击 单变量求解 在 目标单元格 框中, 输入要求解公式所在单元格的引用 198

43 第 6 章 Excel 2007 高级应用在 目标值 框中, 键入所需的结果 在 可变单元格 框中, 输入要调整的值所在单元格的引用 例如 : 已知贷款总额外 元, 贷款期限期 1 年半 (180 月 ), 每月最多还款 900 元, 求年利率, 求解过程如图 6-31~ 图 6-34 所示 图 6-31 输入已知数据 图 6-32 单变量求解 对话框 图 6-33 单变量求解状态 对话框 图 6-34 求解结果 最后求解利率为 7.02% 规划求解 规划求解是一组命令的组成部分, 也称作假设分析 该过程通过更改单元格中的值来查看这些更改对工作表中公式结果的影响 借助 规划求解, 可求得工作表上目标单元格中公式的最优值 规划求解 将对直接或间接与目标单元格中的公式相关的一组单元格进行处理 规划求解加载宏是一个 Microsoft Office Excel 加载项 加载项为 Microsoft Office 提供自定义命令或自定义功能的补充程序 安装 Microsoft Office 或 Excel 后即可使用该程序 但是, 要在 Excel 中使用它, 需要先进行加载 具体操作如下 : 单击 Microsoft Office 按钮, 然后单击 Excel 选项 199

44 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 单击 加载项, 然后在 管理 框中, 选择 Excel 加载项 单击 转到 在 可用加载宏 框中, 选中 规划求解加载项 复选框, 然后单击 确定 即可 加载规划求解加载宏后, 规划求解 命令将出现在 数据 选项卡的 分析 组中 定义并求解问题 : 在 数据 选项卡上的 分析 组中, 单击 规划求解 在 设置目标单元格 框中, 输入目标单元格的单元格引用或名称 目标单元格必须包含公式 执行下列操作之一 : 若要使目标单元格的值尽可能大, 请单击 最大值 若要使目标单元格的值尽可能小, 请单击 最小值 若要使目标单元格为确定值, 请单击 值, 然后在框中键入数值 在 可变单元格 框中, 输入每个可变单元格的名称或引用, 用逗号分隔不相邻的引用 可变单元格必须直接或间接与目标单元格相关 最多可以指定 200 个可变单元格 如果要使 规划求解 根据目标单元格自动建议可变单元格, 请单击 猜测 在 约束 框中, 输入要应用的任何约束条件 单击 求解, 再执行下列操作之一 : 若要在工作表中保存求解值, 单击 规划求解结果 对话框中的 保存规划求解结果 若要恢复原始数据, 单击 恢复为原值 例如 : 某员工的第二年的新工资为 元, 基础工资为 元, 利用规划求解, 年增长率为多少? 输入数据, 如图 6-35 所示, 选择 数据 选项卡的 分析 组, 单击 规划求解 按钮, 弹出 规划求解参数 对话框, 如图 6-36 所示, 设置目标单元格 $C$3( 新工资 ), 设置为 ( 新工资值 ), 可变单元格为 $B$3( 年增长率 ), 单击 求解 按钮, 即得到 B3( 年增长率 ) 的值 弹出 规划求解结果 对话框, 如图 6-37 所示, 选择保存规划求解结果, 单击 确定 按钮完成, 结果如图 6-38 所示 图 6-35 输入数据 图 6-36 规划求解参数 对话框 图 6-37 规划求解结果 对话框 200

45 第 6 章 Excel 2007 高级应用 图 6-38 求解结果 方案管理器 方案是一组命令的组成部分, 一组由 Microsoft Office Excel 保存在工作表中并可进行自动替换的值 用户可以使用方案来预测工作表模型的输出结果 ; 还可以在工作表中创建并保存不同的数值组, 然后切换到任何新方案以查看不同的结果 1. 创建方案在 数据 选项卡上的 数据工具 组中, 单击 假设分析 下拉按钮, 然后单击 方案管理器, 弹出 方案管理器 对话框 在 方案管理器 对话框中, 单击 添加 弹出 添加方案 对话框, 如图 6-39 所示 在 方案名 框中, 键入方案名称 在 可变单元格 框中, 输入对需要更改的单元格的引用 在 保护 下, 选择需要的选项 单击 确定, 弹出 方案变量值 对话框, 如图 6-40 所示 在 方案变量值 对话框中, 键入可变单元格的值, 按 确定 按钮 图 6-39 添加方案 对话框 图 6-40 方案变量值 对话框 如果要创建其他方案, 操作同上, 创建完方案后, 单击 确定, 然后单击 方案管理器 对话框中的 关闭 按钮 2. 管理方案在 数据 选项卡上的 数据工具 组中, 单击 假设分析, 然后单击 方案管理器 弹出 方案管理器 对话框, 如图 6-41 所示, 选择要显示的方案名称, 单击 显示 3. 创建方案摘要报告在 数据 选项卡上的 数据工具 组中, 单击 假设分析, 然后单击 方案管理器 在 方案管理器 对话框中, 单击 摘要 按钮, 弹出 方案摘要 对话框, 如图 6-42 所示, 在 方案摘要 对话框中, 选择报表类型, 在 结果单元格 框中, 输入单元格的引用 按 确定 按钮后, 输出报表 201

46 大学计算机基础 系统工具与环境 ( 文科用 )( 第二版 ) 图 6-41 方案管理器 对话框 图 6-42 方案摘要 对话框 6.7 小结 本章主要介绍了工作簿的创建 数据的输入 公式和函数的使用 表格格式的设置及样式的应用 重点介绍了 Excel VBA 开发环境及 VBA 编程技术, 还介绍了数据清单建立 排序 筛选和分类汇总, 数据图表和数据透视表的创建以及数据分析工具单变量求解 规划求解和方案管理器的应用 习 题 1. 将下表中的数据输入工作表, 采用自动填充方式输入学号, 统计每个学生的总分和平均 分, 计算加权分 ( 自定各科权值 ), 根据平均分, 给出优 良 中 及格和不及格 5 个等级的评 语, 并依总分作降序排序 202

47 第 6 章 Excel 2007 高级应用 2. 在题 1 的工作表中, 筛选出平均成绩高于 80 的学生, 再取消筛选 ; 用高级筛选, 筛选出计应一班或精读高于 80 且平均成绩高于 80 学生 3. 在新工作表上统计题 2 中 5 个成绩等级的人数, 并绘出相应的数据饼图 4. 将下表中的数据输入工作表, 采取自动填充方式输入工号 生活补贴每人 元 ; 岗位津贴根据职务等级计算 : 厅级 元, 处务 元, 科级 元, 办事员 元 ; 个人所得税根据应发工资计算 :3 500 元收下不扣税,3 500~5 000 元扣税 5%,5 000 元以上扣税 10% 实发工资 - 应发工资 - 个人所得税 数据填充后, 统计出各个分公司的人数, 应发工资和实发工资的和 在新工作表上建立数据透视表, 以 分公司 为页字段, 部门 为行字段, 职务等级 为列字段, 姓名 作为数据项, 统计出各部门各职务等级的人数 5. 在题 1 的工作表上, 添加 2 个控件, 一个 查找 按钮, 一个 添加 按钮 点击 查找 按钮, 出现一个信息对话框, 输入要查找的内容, 查找所找记示 ; 点击 添加 按钮, 出现一个录入信息窗体添加记录 6. 某人购车贷款 元, 贷款期限期 2 年, 每月最多还款 900 元, 用单变量求解求贷款的年利率 203