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

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

　　下载：www.      资料下载－工具程序  解压密码：[e-mail]

 

 

一、程序使用方法：

1、程序界面：

 

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表，但并不建议，以免程序继续运行时可能产生未知错误。

 

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

 

二、程序核心设计（以下内容适用于VB编程）

1、VB控制EXCEL：

    在VB中调用EXCEL操作，先要引用EXCEL库文件：如下图

　　然后定义应用程序、工作薄对象、工作表对象后，就可以引用了。如下语句：

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中该公式的语法：-------------------------

     VB编程时往不同单元格写入该公式，往往需要使用变量来表述不同的工作薄、工作表、数据区域、引用单元区域等，形成公式的字符串流，其生成后的格式如下（生成的公式定符串保存到StrB变量中）：

StrB = "=VLOOKUP(RC[-" & Column_1  & "]," _

　　　　　& Chr(39) & "[" &  XLbook1.Name & "]" & XLsheet1.Name & Chr(39) _

　　　　　& "!R 2C1:R" & Max_R1 & "C" & Max_C1 & ","  _

　　　　　& i & ",FALSE)"

　　（蓝色字表示固定不变的字符串，红色的表示需要在程序执行时变化的变量，&是连接符，Chr(39)特指一个ASC字符即单引号)

    说明：对比VBA中的公式，RC[-12]中的列偏移量12已使用了变量Column_1，工作薄使用了XLbook1.Name 变量，工作表使用了XLsheet1.Name 变量，数据区域的结束单元格中RC也使用了变量Max_R1和Max_C1，返回哪一列数据也使用了变量i。其他全部使用字符串，用一对"括起。而单引号则不能直接用在公式中，需要用Chr(39)来替换。与前面公式不同的是，这里的数据区域来自另一个EXCEL文件（工作薄），所以多了XLbook1.Name ，当程序执行后，生成的StrB字符串流就会是：

　　"=VLOOKUP(RC[-12],'[工作薄1.xls]工作表2'!R2C1:R7425C6,3,FALSE)"

5、字典对象及其使用：

    在程序中需要判断某批数据是否重复时，往往使用“字典对象”，VB中有这个对象，VBA中也有。本程序中需要判断一列数据中所有单元格是否存在重复，如整个数据表中的“学号”、“身份证号”一般都不允许重复，如有重复则会导致错误。

 

1）、本程序关于VB字典对象的代码实例：

01行：arr = XLsheet2.Range("A2:A500"))               '把数据区域A2:A500赋给一个数组arr

02行：Set d = CreateObject("Scripting.Dictionary")   '设置d为一个字典对象
03行：For s = 1 To UBound(arr)　　　　　　　　　　　 '从1到数组最大下标循环
04行：    d(arr(s,1)) = d(arr(s,1)) + 1              '往字典对象d里添加项并计数
05行：Next
06行：a = d.keys                                     '所有的关键字项赋给数组a
07行：b = d.items                                    '所有的关键字项的数目赋给数组b
08行：For j = 0 To d.Count - 1

09行：   Debug.Print "项目" & a(j)) & "出现次数：" & b(j)

10行：Next                                   '把所有关键字、出现次数打印在立即窗口

　　代码详释：

01行：arr是一个数组，可以把EXCEL的一个范围区域直接赋给数组，一般区域R行C列，则这个数组也是R行C列的二维数据，如果是1列区域，那就是R行1列，引用时也要按二维数组处理；

02行：在VB中设置创建一个字典对象；

03行-05行：UBound(arr）函数获得arr数组的最大下标，从而知道该列有多少行。然后用(arr(s,1))循环获得每一行的值，注意这里引用数组时是二维形式，d(arr(s,1))表示：字典对象d中某个关键字为arr(s,1)的项的项目，这里用于计数关键字重复出现的次数。04行就表示，对一个关键字arr(s,1)，如果未出现过就添加到字典对象中，如果已出现过则该项目计数加1；

06行－07行：当原EXCEL数据区域的数据全部存入字典对象d后，注意原数据可以有重复，但存入字典对象d后，所形成一个新的数组，该数组实际是有二列，第一列是所有不重复关键字集合，第二列是该关键字的出现次数。06行把存放所有关键字的数组赋给a，07行把所在存放对应项目（这里是计数值）的数组赋给b。

08行-10行：把字典对象里所有关键字和项目对一一打印到立即窗口。

 

2）、字典对象使用文档 

1、字典对象是由 Microsoft 脚本库提供，并不是现成的 Visual Basic 组件，也不是VBA的组件，包含在库文件scrrun.dll中，使用字典对象有2种引用方法：

　　方法1：引用 c:\windows\system32\scrrun.dll 然后 dim d as new Dictionary

　　方法2： Set d = CreateObject("Scripting.Dictionary")

2、字典对象的主要原理：字典对象实际上是维护一个数据表，该表有2列、任意行，行的多少仅受存储空间限制。第1列键(key)，特点是不能重复，第2列项目(item)，与key相关联。使用时，key可以是除数组外的任意数据类型，item则可存入各类杂七杂八的数据类型包括数组。

3、引用方法：

引用项目：d.item(key)  可以使用某个指定的键key来获取对应的项目item，也可简写成d(key)

键的总数：d.count

获取全部key:  d.keys  返回一个数组包含全部key

获取全部item: d.items 返回一个数组包含全部item

写入一个新键：d.add key,item  

判断某键是否存在： d.Exists(key)

删除一个键：d.Remove(key)

删除全部键：d.RemoveAll   实际上就是清空了整个字典对象

如果引用一个不存在的键，字典对象就会添加这个键。

 

   如需vb源程序代码，请留言。