新浪博客

EXCEL实用程序：A表对应数据写B表

2013-04-16 11:51阅读：

http://blog.sina.cn/dpool/blog/u/1470962642

EXCEL实用程序：A表对应数据写B表

EXCEL具备强大的数据处理能力，但像数据库那样连接二个表，并且把一个表的数据写到另一个表中，就显得为难了。或许有人会复制粘贴，但二个表可能记录数不同、记录顺序不同，所以排序后复制粘贴无法做到一一对应。EXCEL中的函数VLOOKUP提供了这种处理能力，但该函数使用复杂、运算量大。ACCESS数据库软件能轻松处理不同表之间的连接与处理，但许多人并不掌握ACCESS。本人根据平时应用需要，使用VB编写了一个小程序，实现连接二个EXCEL表实现互写的功能，本文介绍该程序的使用方法及代码概要，并提供程序和测试数据下载。


一、程序使用方法：
1、程序界面：
EXCEL实用程序：A表对应数据写B表

2、使用方法：
如界面所示，使用过程分5步：打开第1个表、打开第2个表、设置二个表的连接字段、在A表中选择需要添加到B表的字段、执行写入功能。程序界面提供了执行信息提示，如遇常见错误和问题一般会弹出对话框提示。可以在操作过程中“显示”或“隐藏”EXCEL程序。
3、注意事项：
1）、A表或B表可以是同一个EXCEL文件的不同表格，也可以是不同EXCEL文件的表格；EXCEL术语中，一个文件叫工作薄，里面的表格叫“工作表”；
2）、数据表准备：二个数据表必须是规则的数据库格式，几行几列，第1行为各数据列的名称，如下面图示，第2行开始是数据行；
3）、连接二个表的列（数据库术语一般叫“字段”），必须是同一格式。事实上，这二列往往是完全相同的，比如学号或身份证号，但一般不用姓名（因为姓名往往有重复，不唯一）；也就是说A表中该字段必须有唯一的值，不然B表中对应记录不知道应该查A表中的哪个值。程序会自动判断A表所选的连接字段中是否所有的值都唯一，如果有重复，则给出提示并退出；
4）、每次操作，可以同时添加一列或多列到B表中。如果B表的某记录在A表中找不到相同的值，则会写入一个错误信息“#N/A”，这是由公式自动生成，不过程序中可以选择是否清除这些信息；
5）、当操作时，打开“显示EXCEL”功能时，可以边操作边观察EXCEL数据表的变化情况。尽管在程序暂停时直接操作
EXCEL表，但并不建议，以免程序继续运行时可能产生未知错误。

４、实际测试剪图：（表中数据均为虚拟数据）


EXCEL实用程序：A表对应数据写B表

二、程序核心设计（以下内容适用于VB编程）
1、VB控制EXCEL：
在VB中调用EXCEL操作，先要引用EXCEL库文件：如下图
EXCEL实用程序：A表对应数据写B表
　　然后定义应用程序、工作薄对象、工作表对象后，就可以引用了。如下语句：
Dim XLapp As New Excel.Application XLapp表示EXCEL程序本身
Dim XLbook1 As New Excel.Workbook XLbook1表示一个工作薄对象（一个EXCEL文件）
Dim XLsheet1, XLsheet2 As New Excel.Worksheet 　　XLsheet1表示一个工作本对象
当需要打开一个EXCEL文件前，先建立应用程序对象，然后逐级建立对象，对象一旦建立，就可以引用了，如下面代码：
Set XLapp = CreateObject('Excel.Application') '创建EXCEL对象
Set XLbook1 = XLapp.Workbooks.Open(PathA) 　'使用工作薄的OPEN方法打开工作薄
Set XLsheet1 = XLbook1.Sheets(1) 'XLsheet1引用了工作薄XLbook1的第1个工作表
XLsheet1.Activate '激活工作表XLbook1
Lsheet1.Range('A1').Select '选中该表中的A1单元格

2、常用引用或操作代码：
a)、获取数据所在区域的行列：选中A1后获取数据区域的最大行号和最大列号
XLsheet1.Range('A1').Select
Max_R1 = ActiveCell.SpecialCells(xlLastCell).Row '获取数据区域行列
Max_C1 = ActiveCell.SpecialCells(xlLastCell).Column

b)、引用单元格，如要把表1中的某单元格值，写到表2中的某单元格：
　　XLsheet2.Cells(1, j) = XLsheet1.Cells(1, i)
　　表1中第1行第i列对应的单元格的值写到表2的1行j列中。cells(m,n)是比较灵活的引用方式，可以使用变量m,n来表示对应行列值，往往用在循环中。另一种引用方式 Range('A1')，比较适用引用固定单元格，如A列的不同行，也可使用变量来引用，如：Range('A' & i)，但列如果使用变量就较难引用了，所以还是使用Cells(m,n)方便些。第三种方式是R1C1引用方式，往往用在公式中，也可以灵活地使用变量，详见下面公式使用说明。
　　引用行或列，比较容易，如：Columns(i).Select表示选中第i列，Rows(j)表示第j行。

c)、保存或不保存工作表：
　　对工作表的操作完成后，需要保存或不保存退出。保存使用工作薄XLbook的Save方法：XLbook1.Save；如果不保存，则使用：XLbook.Saved = True，告诉EXCEL工作薄已经保存过了（实际上没有保存），EXCEL会信以为真，在退出时不再提醒也不作保存直接退出了。

3、本程序使用的核心公式介绍：
　　本程序的核心公式使用的是VLOOKUP函数，描述如下：
功能：VLOOKUP 函数搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。
语法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value 必需。要在表格或区域的第一列中搜索的值；
table_array 必需。包含数据的单元格区域。
col_index_num 必需。table_array 参数中必须返回的匹配值的列号。
range_lookup 可选。一个逻辑值，指定希望 VLOOKUP 查找精确匹配值还是近似匹配值。
示例：=VLOOKUP(A2,'工作表2'!$A$2:$F$7425,3,FALSE)
　　示例解释：比如在工作表1的C2单元格中键入以上公式，EXCEL执行的操作就是在工作表2的A2:F7425区域（这个区域为以A2单元格为左上角，以F7425单元格为右下角的数据块，共有A-F共6列、2-7425共7424行，是一个比较大的区块）的第一列中，搜索与工作表2 的A2单元格值相同的单元格，假设搜索到在工作表2的A1000单元格中，则返回工作表1的单元格C1000（注意第3个参数是3，表示第3列）的值，写入到工作表2中公式所在单元格C2中。
　　注意：这个公式写在1个单元格中，尽管搜索了一大块区域，但最终返回的仅是一个单元格数据。如果需要把工作表A的整1列写到B表中，则需在B表的该列全部单元格中应用此公式。可使用整列填充公式的方法，填充时A2自动会变为A3、A4...，而工作表2的数据区域必须使用“$”符界定为固定区域，不能随填充而变化。

4、三种不同的调用语法：
　　EXCEL数据表公式、EXCEL的VBA公式、VB语言调用EXCEL公式时，该公式的写法完全不同，编程时要特别加以区分，以下仍以上述公式为例进行说明。
a)、EXCEL数据表中使用公式：----------------------
　　=VLOOKUP(A2,'工作表2'!$A$2:$F$7425,3,FALSE)
　　直接在单元中键入此公式，不需另加引号。注意第二个参数中区域的引用，工作表需要用单引号包括，之后跟一个间隔符（感叹号），再写数据区域，数据区域需用$界定。全部字符特别是标点均不能为中文全角。

b)、EXCEL中VBA的代码使用公式格式：------------------
　　VBA是VB的子集，集成在EXCEL里，可直接控制EXCEL。通过以下方法获得上述公式的VBA代码：通过工具菜单下宏的功能中来录制一个宏，操作步骤为写入以上公式。录制后，进入工具——宏——VB编辑器查看代码，发现键入公式不再是以上格式，而变为：
Range('M2').Select 选中M2单元格
ActiveCell.FormulaR1C1 = '=VLOOKUP(RC[-5],'工作表2!R2C1:R7425C6,3,FALSE)'
　　　　　　　　　　　　　　　　　　　　　　　　　　活动单元格中键入公式
Range('M2').Select 　　　再选中M2单格
Selection.AutoFill Destination:=Range('M2:M499') 填充列（填充从M2到M499）

　　对比直接录入时的格式，VBA代码中此公式已面目全非：
　　直接录入： =VLOOKUP(A2,'工作表2'!$A$2:$F$7425,3,FALSE)
　　VBA生成： '=VLOOKUP(RC[-5],'工作表2'!R2C1:R7425C6,3,FALSE)'
　　说明：VBA代码表述公式时，不能使用“A2”这种引用方式，而是使用R1C1引用，这可以从上述代码“FormulaR1C1”看出，而且只能使用此种方式。R1C1引用直接用R定义行，用C定义列，有二种指定行列的办法，一是使用偏移量，如R[3]表示当前行往下3行，RC[-5]表示当前行（R后没有跟数字）、往左5列（所以是负12），在本实例中就是A2单元格。而R2C1:R7425C6就代表数据区域，好理解。公式中其他格式不变。

c)、VB中该公式的语法：-------------------------

我的更多文章

下载客户端阅读体验更佳

APP专享