Excel应用探讨-表格的比较.doc

Excel 应用探讨应用探讨 表格的比较表格的比较 作者 岳池县长田小学 杨长和 前段时间 学校要我做近三年在校学生的相关资料 其中涉及 到每学年各班学生变动情况 这可有点麻烦 因为以前虽然每学年 都有填报 但数据可能并不真实 而且可能也并不适合这次报表的 要求 如果要把几个学年学生花名册拿来一一核对 那也是相当麻 烦的事 于是我就捉摸着 Excel 本身有着强大的功能 要比较一个 班相邻两个学年学生变化情况 就是比较两个表中不同的行 应该 是可以做到的 在网上搜索了一翻 倒是找到了一些相关介绍 不过许多都是 别人写的软件 不知道具体的实现原理 于是决定自己来解决这个 问题 一来可以把实现方法留着 可能以后会用得着 二来我也一 直想深入学习下 Excel 但因为平时事情不少 也没找到学习的动力 这次正好可利用利用 首先分析下要实现的功能 现以小学四年级一班为例 现在在 读学生名册 2010 年秋入学 为表 1 上学年在读学生名册 2009 年秋入学 为表 2 第一 要找出表 2 中存在而表 1 中不存在 即 本期已转出学生 的记录 第二 要找出表 1 中存在而表 2 中不存 在 即本期转入学生 的记录 其实 两个功能的实现是一样的 所以就仅以第一个功能为例 查询 Excel 帮助 要想学好 Excel 先学习使用好帮助是必须的 找到了这样一个函数 VLOOKUP lookup value table array col index num range lookup 对 Excel 了解不多的可能要多读几遍才能看懂函数的使用说明 该函数 有四个参数 lookup value 可以是一个数值或是对某个单元格的引 用 table array 是对表格中某个区域的引用 col index num 表示 要返回的单元格列序号 range lookup 为一逻辑值 为 true 或省略 表示可近似匹配 false 表示精确匹配 例如 VLOOKUP 2009 A1 2010 A I 1 表示在表 2010 中的 A I 列 中的 A 列查询与表 2009 中的 A1 单元格匹配的单元格 匹配成功则 返回表 2010 中匹配的行的 A 列 当然 我们是要找表 2009 中有而 2010 中没有的行 显然上面的函数并不能满足要求 改成 IF ISERROR VLOOKUP 2009 A1 2010 A I 1 2009 A1 等于号表示在单元格中输入公式 先在另一个表中的 A1 单元格 输入这个公式 当然表 2009 和表 2010 在工作簿中已经存在 否则 会出错 还要注意 我的表都没有列标题 数据都是从第一行开始 有列标题的就只能从第二行开始 当然得有些小改动 这个公式 中 VLOOKUP 功能同前面一样 但整个公式的功能则是当匹配成功 则在 A1 单元格中输入空格 否则复制表 2009 的 A1 单元格到当前 单元格 该生在表 2010 中没有 即已转出 把当前单元格横向 复制 即可复制一个学生的所有相关信息 当然 如果没转出 则 都不会复制 全为空 选中第一行中可能有信息的单元格 再按 列拖动复制则可依次检查表 2009 中对应行的学生是否已转出 这 样把 A1 单元格处理完后 几秒钟就能搞定 当然 上面的方法还是显得不够专业 要制一个转出学生的信 息表 显然还得花点儿时间把刚刚得到的转出学生信息一行行复制 于是我又想 能不能用 VB 进一步提升这个功能呢 打开 VB 帮助 查了一下 好象在 VB 中不能直接调用这个函数 没办法 只能自 己来写类似功能的函数了 为了能方便比较不同的表 我把这个函数写成了以三个表格名 为参数的通用函数 Function comp SheetA As Worksheet SheetB As Worksheet SheetC As Worksheet 其中 SheetA 为要比较的表 SheetB 为被比较的表 SheetC 为存放结果的表 即存放 SheetA 中 有而 SheetB 中没有的记录 比较倒是不难 采用两层循环嵌套 内 层循环为用 SheetA 中的一个记录依次比较 SheetB 中有数据的所有 记录 外层循环把 SheetA 中作比较的记录行号依次加 1 直到没数 据的行结束循环 完整的函数如下 Function comp SheetA As Worksheet SheetB As Worksheet SheetC As Worksheet Dim i As Integer Dim j As Integer Dim l As Integer Dim k As Integer Dim x As Integer i 1 j 1 l 1 For x 1 To 100这个循环先清空需存放结果的表 If SheetC Cells x 1 Then SheetC Cells x 1 End If If SheetC Cells x 2 Then SheetC Cells x 2 End If Next Do While SheetB Cells i 1 k 0 j 1 Do While SheetA Cells j 1 If SheetB Cells i 1 SheetA Cells j 1 Then k 1 Exit Do表 B 中有表 A 中的当前行 则 k 标记为 1 结束 取表 A 的下一行接着比较 Else j j 1 End If Loop If k 0 Then k 标记为 0 表示表 B 中没有表 A 的当前行 SheetC Cells l 1 SheetB Cells i 1 l l 1 End If i i 1 Loop SheetC Cells l 1 合计转学人数 SheetC Cells l 2 l 1 End Function 为了方便使用 作了一个窗体 如下图 三个表名就通过窗 体文本框输入 单击命令按钮后调用上面的函数执行比较 窗体的代码如下 Private Sub CommandButton1 Click Dim text1 As String Dim text2 As String Dim text3 As String text1 TextBox1 Text text2 TextBox2 Text text3 TextBox3 Text Dim Worksheets1 As Worksheet Dim Worksheets2 As Worksheet Dim Worksheets3 As Worksheet Set Worksheets1 Application Worksheets text1 Set Worksheets2 Application Worksheets text2 Set Worksheets3 Application Worksheets text3 comp Worksheets1 Worksheets2 Worksheets3 调用比较函数 End If End Sub 当然 函数的功能比较单一 还缺乏必要的错误处理机制 比 较时也只是以 A 列为关键字 没有考虑其它列 我也还没考虑怎样 能在其它的工作簿中方便地调用 不过 主