第08章 小型财务收支管理器

1、主要功能： l收支类型定义； l基本收支数据输入或修改； l按年度和收支类型制作统计表、统计图。 主要特点： l用Excel和VBA实现； l收支类型可随时定义和修改，输入或修改基本收支数据时，可由一位数字替换为 对应的收支类型名； l以表格和直条图两种形式输出分类汇总数据。 主要技术： l单元格批注内容的动态更新与显示； l直接代换式输入方法的实现； l数据有效性、光标焦点控制； l下拉列表项的动态更新； l分级显示控制； l分类汇总数据的筛选与图表化。 8.1 工作簿设计工作簿设计 l三张工作表：“收支项目”、“基本数据”和 “统计图表”。 1. “收支项目收支项目”工作表工作表 l收支标

2、志“S”表示收入项、“Z”表示支出项； l代码用09当中的一位数字。 l选中A2单元格，冻结窗格。 8.1 工作簿设计工作簿设计 2. “基本数据基本数据”工作表工作表 lA列数字作为文本处理。列数字作为文本处理。 lB、C列两位小数。列两位小数。 l在在“工具工具”菜单中选择菜单中选择“选项选项”命令，取消命令，取消 “零值零值”复选项。复选项。 l选中选中A2单元格，冻结窗格。单元格，冻结窗格。 l选中选中A1单元格，设置自动筛选功能。单元格，设置自动筛选功能。 l用用“控件工具箱控件工具箱”添加组合框添加组合框ComboBox1、 ComboBox2。 8.1 工作簿设计工作簿设计 3.

3、 “统计图表统计图表”工作表工作表 lA列数字作为文本处理。列数字作为文本处理。 lB、C列两位小数。列两位小数。 l用用“控件工具箱控件工具箱”添加组合框添加组合框ComboBox1、 选项按钮选项按钮OptionButton1 和和OptionButton12。 l隐藏工作表中的零值。隐藏工作表中的零值。 8.1 工作簿设计工作簿设计 l在在B5:B9、D5:D9区域输入模拟的区域输入模拟的“收入金额收入金额” 和和“收支类型收支类型”数据。数据。 l选中选中B5:B9、D5:D9区域，单击区域，单击“常用常用”工具工具 栏的栏的“图表向导图表向导”按钮。按钮。 l选择选择“柱形图柱形图”

4、和默认的子图表类型，单击和默认的子图表类型，单击 “完成完成”按钮。按钮。 l选中图表区右侧的选中图表区右侧的“图例图例”项，删除。项，删除。 8.1 工作簿设计工作簿设计 l在图表区上单击鼠标右键，选择在图表区上单击鼠标右键，选择“源数据源数据”命令。在命令。在 “系列系列”选项卡中，设置选项卡中，设置“值值”为为“=统计图统计图 表表!$B$5:$B$9”，“分类轴标志分类轴标志”为为“=统计图统计图 表表!$D$5:$D$9”，“名称名称”为为“=统计图表统计图表!$C$1”。 l在图表区上单击鼠标右键，选择在图表区上单击鼠标右键，选择“图表区格式图表区格式”命令。命令。 在在“图案图案

5、”选项卡中，选中选项卡中，选中“阴影阴影”和和“圆角圆角”复选复选 项，填充项，填充“双色双色”、“斜上斜上”效果。在效果。在“属性属性”选项选项 卡中，设置对象位置为卡中，设置对象位置为“大小、位置均固定大小、位置均固定”。 l在绘图区上单击鼠标右键，选择在绘图区上单击鼠标右键，选择“绘图区格式绘图区格式”命令。命令。 设置区域颜色为设置区域颜色为“白色白色”。 l在分类轴上单击鼠标右键，选择在分类轴上单击鼠标右键，选择“坐标轴格式坐标轴格式”命令。命令。 在在“对齐对齐”选项卡中，设置文本方向为选项卡中，设置文本方向为45度。度。 8.1 工作簿设计工作簿设计 l在图表标题上单击鼠标右键，

6、选择在图表标题上单击鼠标右键，选择“图表标题图表标题 格式格式”命令。设置标题为命令。设置标题为“宋体宋体”、12号字，号字， 文字颜色为文字颜色为“橙色橙色”。 l在任意一个柱形上单击鼠标右键，选择在任意一个柱形上单击鼠标右键，选择“数据数据 系列格式系列格式”命令。在命令。在“图案图案”选项卡中，单击选项卡中，单击 “填充效果填充效果”按钮，填充按钮，填充“茶色茶色”，“垂直垂直” 变形效果。变形效果。 l在数值轴主要网格线上单击鼠标右键，选择在数值轴主要网格线上单击鼠标右键，选择 “网格线格式网格线格式”命令。在命令。在“图案图案”选项卡中，选项卡中， 设置线条样式为设置线条样式为“虚线

7、虚线”。 8.2 组合框列表项的添加组合框列表项的添加 工作簿的工作簿的Open事件代码：事件代码： l声明一维数组声明一维数组b，每个元素用来标识一个年份值是否，每个元素用来标识一个年份值是否 被收集到组合框中。用于将被收集到组合框中。用于将“基本数据基本数据”工作表中不工作表中不 重复的年份值添加到组合框中。重复的年份值添加到组合框中。 l用对象变量用对象变量sh1、sh2表示表示“基本数据基本数据”和和“统计图统计图 表表”工作表。工作表。 l把把“基本数据基本数据”工作表第工作表第1列的年份，排除重复值后，列的年份，排除重复值后， 添加到添加到“基本数据基本数据”工作表的工作表的Com

8、boBox1和和“统计统计 图表图表”工作表的工作表的ComboBox1中。中。 l把把“收支项目收支项目”工作表第工作表第2列的列的“收支标志收支标志”和第和第3列列 的的“收支类型名收支类型名”拼接，添加到拼接，添加到“基本数据基本数据”工作表工作表 的的ComboBox2中。中。 8.3 基本数据维护基本数据维护 l针对针对“基本数据基本数据”工作表进行操作。工作表进行操作。 1. 工作表的工作表的SelectionChange事件代码事件代码 l它根据当前单元格位置进行相应的处理。它根据当前单元格位置进行相应的处理。 l如果是第如果是第1行，则不执行其他操作，直接退出。行，则不执行其他

9、操作，直接退出。 l如果超过第如果超过第4列，则定位到下一行的第列，则定位到下一行的第2列。列。 l如果超过如果超过D列数据列数据1行以上，则定位到行以上，则定位到D列数据列数据 下一行的第下一行的第2列，然后退出。列，然后退出。 l如果当前行第如果当前行第1列单元格的内容为空，则将第列单元格的内容为空，则将第1 列最后一个有效的列最后一个有效的“年月年月”数据复制到该单元数据复制到该单元 格。格。 8.3 基本数据维护基本数据维护 l如果当前单元格处于第如果当前单元格处于第4列，则进行以下操作：列，则进行以下操作： (1) 取出当前行第取出当前行第2列、第列、第3列的列的“收入金额收入金额”

10、和和“支出金额支出金额”。 (2) 如果如果“收入金额收入金额”和和“支出金额支出金额”全都大于零，或者全部等于零，全都大于零，或者全部等于零， 则将光标定位到当前行第则将光标定位到当前行第2列，然后退出。列，然后退出。 (3) 如果只是如果只是“收入金额收入金额”大于零，则调用自定义函数大于零，则调用自定义函数zfc，取，取“收支收支 项目项目”工作表中的所有收入类型代码和对应的收支类型名，形成工作表中的所有收入类型代码和对应的收支类型名，形成 一个字符串，送给变量一个字符串，送给变量lbx。 (4) 如果只是如果只是“支出金额支出金额”大于零，则调用自定义函数大于零，则调用自定义函数zfc

11、，取，取“收支收支 项目项目”工作表中的所有支出类型代码和对应的收支类型名，形成工作表中的所有支出类型代码和对应的收支类型名，形成 一个字符串，送给变量一个字符串，送给变量lbx。 (5) 在当前单元格中，先删除原有的数据有效性条件，再设置新的数在当前单元格中，先删除原有的数据有效性条件，再设置新的数 据有效性条件，将变量据有效性条件，将变量lbx的值作为数据有效性提示信息。的值作为数据有效性提示信息。 8.3 基本数据维护基本数据维护 2. 自定义函数自定义函数zfc l功能：功能： 取取“收支项目收支项目”工作表的收入或支出类型代工作表的收入或支出类型代 码、名称，拼接成一个字符串，作为函

12、数值。码、名称，拼接成一个字符串，作为函数值。 l参数：参数： sz作为收支标志，它的值为作为收支标志，它的值为“S”表示收入项，表示收入项， “Z”表示支出项。表示支出项。 8.3 基本数据维护基本数据维护 3. 工作表的工作表的Change事件代码事件代码 l它对当前单元格的位置进行判断，如果是第它对当前单元格的位置进行判断，如果是第4列，并列，并 且行号大于且行号大于1，则进行以下操作：，则进行以下操作： l取出当前单元格值，送给变量取出当前单元格值，送给变量v。取出当前行第。取出当前行第2列的列的 值，送给变量值，送给变量sr。 l如果当前单元格的值为空，则不执行其他操作，直接如果当前

13、单元格的值为空，则不执行其他操作，直接 退出。退出。 l如果当前单元格的值不是数字，也不执行其他操作，如果当前单元格的值不是数字，也不执行其他操作， 直接退出。直接退出。 l如果当前行第如果当前行第2列的值（收入金额）大于零，则调用列的值（收入金额）大于零，则调用 自定义函数自定义函数th，将当前单元格输入的代码，将当前单元格输入的代码v替换为对替换为对 应的收入类型名。否则，调用自定义函数应的收入类型名。否则，调用自定义函数th，将当前，将当前 单元格输入的代码单元格输入的代码v替换为对应的支出类型名。替换为对应的支出类型名。 8.3 基本数据维护基本数据维护 4. 自定义函数自定义函数th

14、 l功能：功能： 取出代码对应的收入或支出类型名。取出代码对应的收入或支出类型名。 l参数：参数： v、sz分别为代码和收支标志，分别为代码和收支标志，sz的值为的值为“S” 表示收入项，表示收入项，“Z”表示支出项。表示支出项。 l采用了一种采用了一种“直接代换式输入直接代换式输入”技术。在输入技术。在输入 枚举型数据时，只要输入事先预置的代码，就枚举型数据时，只要输入事先预置的代码，就 可以自动代换为相应的内容。可以自动代换为相应的内容。 8.4 基本数据查询基本数据查询 l对对“基本数据基本数据”工作表中进行操作。工作表中进行操作。 l工作表中的两个组合框，分别用来选择工作表中的两个组合

15、框，分别用来选择“年份年份” 和和“收支类型收支类型”，对数据进行筛选，实现查询，对数据进行筛选，实现查询 功能。功能。 1. 组合框代码组合框代码 l组合框组合框ComboBox1、ComboBox1的的 Change事件代码：事件代码： 调用子程序调用子程序“查询查询”。 8.4 基本数据查询基本数据查询 2. “查询查询”子程序子程序 l用对象变量用对象变量rg表示当前工作表表示当前工作表A1单元格对应单元格对应 的数据区。的数据区。 l按按ComboBox1的选项对数据区第的选项对数据区第1列进行筛列进行筛 选。选。 l按按ComboBox2的选项对数据区第的选项对数据区第4列进行筛列

16、进行筛 选。选。 l调整当前工作表的垂直滚动条。调整当前工作表的垂直滚动条。 8.5 分类汇总图表分类汇总图表 l对对“统计图表统计图表”工作表进行操作。工作表进行操作。 l一个组合框、两个选项按钮。一个组合框、两个选项按钮。 1. 组合框、选项按钮代码组合框、选项按钮代码 l组合框组合框ComboBox1的的Change事件代码，选事件代码，选 项按钮项按钮OptionButton1和和OptionButton2的的 Click事件代码：事件代码： 都调用子程序都调用子程序tjtb。 8.5 分类汇总图表分类汇总图表 2. tjtb子程序子程序 l第一部分，对第一部分，对“基本数据基本数据”

17、工作表的数据进行工作表的数据进行 筛选，复制到当前工作表指定区域。筛选，复制到当前工作表指定区域。 l第二部分，对当前工作表目标数据区的数据进第二部分，对当前工作表目标数据区的数据进 行排序、分类汇总。行排序、分类汇总。 l第三部分，设置图表属性。第三部分，设置图表属性。 l第四部分，收尾处理。第四部分，收尾处理。 8.6 运行与测试运行与测试 1. 收支项目信息维护收支项目信息维护 l添加、删除、修改各收支项目的名称、收支标志和代码。添加、删除、修改各收支项目的名称、收支标志和代码。 2. 基本数据维护基本数据维护 l用通常方法对数据进行增、删、改等操作。用通常方法对数据进行增、删、改等操作。 l“收支类型收支类型”列，自动检测，用一位数字代替对应