EXCEL具備強(qiáng)大的數(shù)據(jù)處理能力,，但像數(shù)據(jù)庫(kù)那樣連接二個(gè)表，并且把一個(gè)表的數(shù)據(jù)寫(xiě)到另一個(gè)表中,，就顯得為難了,。或許有人會(huì)復(fù)制粘貼,，但二個(gè)表可能記錄數(shù)不同、記錄順序不同,，所以排序后復(fù)制粘貼無(wú)法做到一一對(duì)應(yīng),。EXCEL中的函數(shù)VLOOKUP提供了這種處理能力，但該函數(shù)使用復(fù)雜,、運(yùn)算量大,。ACCESS數(shù)據(jù)庫(kù)軟件能輕松處理不同表之間的連接與處理，但許多人并不掌握ACCESS,。本人根據(jù)平時(shí)應(yīng)用需要,，使用VB編寫(xiě)了一個(gè)小程序，實(shí)現(xiàn)連接二個(gè)EXCEL表實(shí)現(xiàn)互寫(xiě)的功能,，本文介紹該程序的使用方法及代碼概要,，并提供程序和測(cè)試數(shù)據(jù)下載。

　　下載：www.      資料下載－工具程序  解壓密碼：[email protected]

一,、程序使用方法：

1,、程序界面：

 

2、使用方法：

     如界面所示,，使用過(guò)程分5步：打開(kāi)第1個(gè)表,、打開(kāi)第2個(gè)表,、設(shè)置二個(gè)表的連接字段、在A(yíng)表中選擇需要添加到B表的字段,、執(zhí)行寫(xiě)入功能,。程序界面提供了執(zhí)行信息提示，如遇常見(jiàn)錯(cuò)誤和問(wèn)題一般會(huì)彈出對(duì)話(huà)框提示,?？梢栽诓僮鬟^(guò)程中“顯示”或“隱藏”EXCEL程序。

3,、注意事項(xiàng)：

    1）,、A表或B表可以是同一個(gè)EXCEL文件的不同表格，也可以是不同EXCEL文件的表格,；EXCEL術(shù)語(yǔ)中,，一個(gè)文件叫工作薄，里面的表格叫“工作表”,；

    2）,、數(shù)據(jù)表準(zhǔn)備：二個(gè)數(shù)據(jù)表必須是規(guī)則的數(shù)據(jù)庫(kù)格式，幾行幾列,，第1行為各數(shù)據(jù)列的名稱(chēng),，如下面圖示，第2行開(kāi)始是數(shù)據(jù)行,；

    3）,、連接二個(gè)表的列（數(shù)據(jù)庫(kù)術(shù)語(yǔ)一般叫“字段”），必須是同一格式,。事實(shí)上,，這二列往往是完全相同的，比如學(xué)號(hào)或身份證號(hào),，但一般不用姓名（因?yàn)樾彰兄貜?fù),，不唯一）；也就是說(shuō)A表中該字段必須有唯一的值,，不然B表中對(duì)應(yīng)記錄不知道應(yīng)該查A表中的哪個(gè)值,。程序會(huì)自動(dòng)判斷A表所選的連接字段中是否所有的值都唯一，如果有重復(fù),，則給出提示并退出,；

    4）、每次操作,，可以同時(shí)添加一列或多列到B表中,。如果B表的某記錄在A(yíng)表中找不到相同的值，則會(huì)寫(xiě)入一個(gè)錯(cuò)誤信息“#N/A”,，這是由公式自動(dòng)生成,，不過(guò)程序中可以選擇是否清除這些信息,；

    5）、當(dāng)操作時(shí),，打開(kāi)“顯示EXCEL”功能時(shí),，可以邊操作邊觀(guān)察EXCEL數(shù)據(jù)表的變化情況。盡管在程序暫停時(shí)直接操作
EXCEL表,，但并不建議,，以免程序繼續(xù)運(yùn)行時(shí)可能產(chǎn)生未知錯(cuò)誤。

４,、實(shí)際測(cè)試剪圖：（表中數(shù)據(jù)均為虛擬數(shù)據(jù)）

二,、程序核心設(shè)計(jì)（以下內(nèi)容適用于VB編程）

1、VB控制EXCEL：

    在VB中調(diào)用EXCEL操作,，先要引用EXCEL庫(kù)文件：如下圖

　　然后定義應(yīng)用程序,、工作薄對(duì)象、工作表對(duì)象后,，就可以引用了,。如下語(yǔ)句：

Dim XLapp As New Excel.Application          XLapp表示EXCEL程序本身
Dim XLbook1 As New Excel.Workbook           XLbook1表示一個(gè)工作薄對(duì)象（一個(gè)EXCEL文件）
Dim XLsheet1, XLsheet2 As New Excel.Worksheet  　　XLsheet1表示一個(gè)工作本對(duì)象
    當(dāng)需要打開(kāi)一個(gè)EXCEL文件前，先建立應(yīng)用程序?qū)ο?，然后逐?jí)建立對(duì)象,，對(duì)象一旦建立，就可以引用了,，如下面代碼：

Set XLapp = CreateObject("Excel.Application")    '創(chuàng)建EXCEL對(duì)象

Set XLbook1 = XLapp.Workbooks.Open(PathA)      　'使用工作薄的OPEN方法打開(kāi)工作薄

Set XLsheet1 = XLbook1.Sheets(1)           'XLsheet1引用了工作薄XLbook1的第1個(gè)工作表

XLsheet1.Activate                                    '激活工作表XLbook1
Lsheet1.Range("A1").Select                           '選中該表中的A1單元格

2,、常用引用或操作代碼：

a)、獲取數(shù)據(jù)所在區(qū)域的行列：選中A1后獲取數(shù)據(jù)區(qū)域的最大行號(hào)和最大列號(hào)

XLsheet1.Range("A1").Select
Max_R1 = ActiveCell.SpecialCells(xlLastCell).Row               '獲取數(shù)據(jù)區(qū)域行列
Max_C1 = ActiveCell.SpecialCells(xlLastCell).Column

b),、引用單元格,，如要把表1中的某單元格值，寫(xiě)到表2中的某單元格：

　　XLsheet2.Cells(1, j) = XLsheet1.Cells(1, i)     

　　表1中第1行第i列對(duì)應(yīng)的單元格的值寫(xiě)到表2的1行j列中,。cells(m,n)是比較靈活的引用方式，可以使用變量m,n來(lái)表示對(duì)應(yīng)行列值,，往往用在循環(huán)中,。另一種引用方式 Range("A1")，比較適用引用固定單元格,，如A列的不同行,，也可使用變量來(lái)引用，如：Range("A" & i),，但列如果使用變量就較難引用了,，所以還是使用Cells(m,n)方便些。第三種方式是R1C1引用方式,，往往用在公式中,，也可以靈活地使用變量,，詳見(jiàn)下面公式使用說(shuō)明。

　　引用行或列,，比較容易,，如：Columns(i).Select表示選中第i列，Rows(j)表示第j行,。

c),、保存或不保存工作表：

　　對(duì)工作表的操作完成后，需要保存或不保存退出,。保存使用工作薄XLbook的Save方法：XLbook1.Save,；如果不保存，則使用：XLbook.Saved = True,，告訴EXCEL工作薄已經(jīng)保存過(guò)了（實(shí)際上沒(méi)有保存）,，EXCEL會(huì)信以為真，在退出時(shí)不再提醒也不作保存直接退出了,。

3,、本程序使用的核心公式介紹：

　　本程序的核心公式使用的是VLOOKUP函數(shù)，描述如下：

功能：VLOOKUP 函數(shù)搜索某個(gè)單元格區(qū)域的第一列,，然后返回該區(qū)域相同行上任何單元格中的值,。

語(yǔ)法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

     lookup_value     必需。要在表格或區(qū)域的第一列中搜索的值,；

     table_array      必需,。包含數(shù)據(jù)的單元格區(qū)域。

     col_index_num    必需,。table_array 參數(shù)中必須返回的匹配值的列號(hào),。

     range_lookup     可選。一個(gè)邏輯值,，指定希望 VLOOKUP 查找精確匹配值還是近似匹配值,。

示例：=VLOOKUP(A2,'工作表2'!$A$2:$F$7425,3,FALSE) 

　　示例解釋?zhuān)罕热缭诠ぷ鞅?的C2單元格中鍵入以上公式，EXCEL執(zhí)行的操作就是在工作表2的A2:F7425區(qū)域（這個(gè)區(qū)域?yàn)橐訟2單元格為左上角,，以F7425單元格為右下角的數(shù)據(jù)塊,，共有A-F共6列、2-7425共7424行,，是一個(gè)比較大的區(qū)塊）的第一列中,，搜索與工作表2 的A2單元格值相同的單元格，假設(shè)搜索到在工作表2的A1000單元格中,，則返回工作表1的單元格C1000（注意第3個(gè)參數(shù)是3,，表示第3列）的值，寫(xiě)入到工作表2中公式所在單元格C2中。

　　注意：這個(gè)公式寫(xiě)在1個(gè)單元格中,，盡管搜索了一大塊區(qū)域,，但最終返回的僅是一個(gè)單元格數(shù)據(jù)。如果需要把工作表A的整1列寫(xiě)到B表中,，則需在B表的該列全部單元格中應(yīng)用此公式,。可使用整列填充公式的方法,，填充時(shí)A2自動(dòng)會(huì)變?yōu)锳3,、A4...，而工作表2的數(shù)據(jù)區(qū)域必須使用“$”符界定為固定區(qū)域,，不能隨填充而變化,。

4、三種不同的調(diào)用語(yǔ)法：

　　EXCEL數(shù)據(jù)表公式,、EXCEL的VBA公式,、VB語(yǔ)言調(diào)用EXCEL公式時(shí)，該公式的寫(xiě)法完全不同,，編程時(shí)要特別加以區(qū)分,，以下仍以上述公式為例進(jìn)行說(shuō)明。

a),、EXCEL數(shù)據(jù)表中使用公式：----------------------

　　=VLOOKUP(A2,'工作表2'!$A$2:$F$7425,3,FALSE)    

　　直接在單元中鍵入此公式,，不需另加引號(hào)。注意第二個(gè)參數(shù)中區(qū)域的引用,，工作表需要用單引號(hào)包括,，之后跟一個(gè)間隔符（感嘆號(hào)），再寫(xiě)數(shù)據(jù)區(qū)域,，數(shù)據(jù)區(qū)域需用$界定,。全部字符特別是標(biāo)點(diǎn)均不能為中文全角。

b),、EXCEL中VBA的代碼使用公式格式：------------------

　　VBA是VB的子集,，集成在EXCEL里，可直接控制EXCEL,。通過(guò)以下方法獲得上述公式的VBA代碼：通過(guò)工具菜單下宏的功能中來(lái)錄制一個(gè)宏,，操作步驟為寫(xiě)入以上公式。錄制后,，進(jìn)入工具——宏——VB編輯器查看代碼，發(fā)現(xiàn)鍵入公式不再是以上格式,，而變?yōu)椋?/font>

Range("M2").Select                                  選中M2單元格
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'工作表2!R2C1:R7425C6,3,FALSE)"            

　　　　　　　　　　　　　　　　　　　　　　　　　　活動(dòng)單元格中鍵入公式
Range("M2").Select                            　　　再選中M2單格                      
Selection.AutoFill Destination:=Range("M2:M499")    填充列（填充從M2到M499）

　　對(duì)比直接錄入時(shí)的格式,，VBA代碼中此公式已面目全非：

　　直接錄入： =VLOOKUP(A2,'工作表2'!$A$2:$F$7425,3,FALSE) 

　　VBA生成： "=VLOOKUP(RC[-5],'工作表2'!R2C1:R7425C6,3,FALSE)"  

　　說(shuō)明：VBA代碼表述公式時(shí)，不能使用“A2”這種引用方式,，而是使用R1C1引用,，這可以從上述代碼“FormulaR1C1”看出,，而且只能使用此種方式。R1C1引用直接用R定義行,，用C定義列,，有二種指定行列的辦法，一是使用偏移量,，如R[3]表示當(dāng)前行往下3行,，RC[-5]表示當(dāng)前行（R后沒(méi)有跟數(shù)字）、往左5列（所以是負(fù)12）,，在本實(shí)例中就是A2單元格,。而R2C1:R7425C6就代表數(shù)據(jù)區(qū)域，好理解,。公式中其他格式不變,。

c)、VB中該公式的語(yǔ)法：-------------------------

     VB編程時(shí)往不同單元格寫(xiě)入該公式,，往往需要使用變量來(lái)表述不同的工作薄,、工作表、數(shù)據(jù)區(qū)域,、引用單元區(qū)域等,，形成公式的字符串流，其生成后的格式如下（生成的公式定符串保存到StrB變量中）：

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

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

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

　　　　　& i & ",FALSE)"

　?。ㄋ{(lán)色字表示固定不變的字符串,，紅色的表示需要在程序執(zhí)行時(shí)變化的變量，&是連接符,，Chr(39)特指一個(gè)ASC字符即單引號(hào))

    說(shuō)明：對(duì)比VBA中的公式,，RC[-12]中的列偏移量12已使用了變量Column_1，工作薄使用了XLbook1.Name 變量,，工作表使用了XLsheet1.Name 變量,，數(shù)據(jù)區(qū)域的結(jié)束單元格中RC也使用了變量Max_R1和Max_C1，返回哪一列數(shù)據(jù)也使用了變量i,。其他全部使用字符串,，用一對(duì)"括起。而單引號(hào)則不能直接用在公式中,，需要用Chr(39)來(lái)替換,。與前面公式不同的是，這里的數(shù)據(jù)區(qū)域來(lái)自另一個(gè)EXCEL文件（工作?。?，所以多了XLbook1.Name ，當(dāng)程序執(zhí)行后，生成的StrB字符串流就會(huì)是：

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

5,、字典對(duì)象及其使用：

    在程序中需要判斷某批數(shù)據(jù)是否重復(fù)時(shí),，往往使用“字典對(duì)象”，VB中有這個(gè)對(duì)象,，VBA中也有,。本程序中需要判斷一列數(shù)據(jù)中所有單元格是否存在重復(fù)，如整個(gè)數(shù)據(jù)表中的“學(xué)號(hào)”,、“身份證號(hào)”一般都不允許重復(fù),，如有重復(fù)則會(huì)導(dǎo)致錯(cuò)誤。

1）,、本程序關(guān)于VB字典對(duì)象的代碼實(shí)例：

01行：arr = XLsheet2.Range("A2:A500"))               '把數(shù)據(jù)區(qū)域A2:A500賦給一個(gè)數(shù)組arr

02行：Set d = CreateObject("Scripting.Dictionary")   '設(shè)置d為一個(gè)字典對(duì)象
03行：For s = 1 To UBound(arr)　　　　　　　　　　　 '從1到數(shù)組最大下標(biāo)循環(huán)
04行：    d(arr(s,1)) = d(arr(s,1)) + 1              '往字典對(duì)象d里添加項(xiàng)并計(jì)數(shù)
05行：Next
06行：a = d.keys                                     '所有的關(guān)鍵字項(xiàng)賦給數(shù)組a
07行：b = d.items                                    '所有的關(guān)鍵字項(xiàng)的數(shù)目賦給數(shù)組b
08行：For j = 0 To d.Count - 1

09行：   Debug.Print "項(xiàng)目" & a(j)) & "出現(xiàn)次數(shù)：" & b(j)

10行：Next                                   '把所有關(guān)鍵字,、出現(xiàn)次數(shù)打印在立即窗口

　　代碼詳釋?zhuān)?/font>

01行：arr是一個(gè)數(shù)組，可以把EXCEL的一個(gè)范圍區(qū)域直接賦給數(shù)組,，一般區(qū)域R行C列,，則這個(gè)數(shù)組也是R行C列的二維數(shù)據(jù)，如果是1列區(qū)域,，那就是R行1列,，引用時(shí)也要按二維數(shù)組處理；

02行：在VB中設(shè)置創(chuàng)建一個(gè)字典對(duì)象,；

03行-05行：UBound(arr）函數(shù)獲得arr數(shù)組的最大下標(biāo),，從而知道該列有多少行。然后用(arr(s,1))循環(huán)獲得每一行的值,，注意這里引用數(shù)組時(shí)是二維形式,，d(arr(s,1))表示：字典對(duì)象d中某個(gè)關(guān)鍵字為arr(s,1)的項(xiàng)的項(xiàng)目，這里用于計(jì)數(shù)關(guān)鍵字重復(fù)出現(xiàn)的次數(shù),。04行就表示,，對(duì)一個(gè)關(guān)鍵字arr(s,1)，如果未出現(xiàn)過(guò)就添加到字典對(duì)象中,，如果已出現(xiàn)過(guò)則該項(xiàng)目計(jì)數(shù)加1,；

06行－07行：當(dāng)原EXCEL數(shù)據(jù)區(qū)域的數(shù)據(jù)全部存入字典對(duì)象d后，注意原數(shù)據(jù)可以有重復(fù),，但存入字典對(duì)象d后,，所形成一個(gè)新的數(shù)組，該數(shù)組實(shí)際是有二列,，第一列是所有不重復(fù)關(guān)鍵字集合,，第二列是該關(guān)鍵字的出現(xiàn)次數(shù)。06行把存放所有關(guān)鍵字的數(shù)組賦給a,，07行把所在存放對(duì)應(yīng)項(xiàng)目（這里是計(jì)數(shù)值）的數(shù)組賦給b,。

08行-10行：把字典對(duì)象里所有關(guān)鍵字和項(xiàng)目對(duì)一一打印到立即窗口,。

2）、字典對(duì)象使用文檔 

1,、字典對(duì)象是由 Microsoft 腳本庫(kù)提供，并不是現(xiàn)成的 Visual Basic 組件,，也不是VBA的組件,，包含在庫(kù)文件scrrun.dll中，使用字典對(duì)象有2種引用方法：

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

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

2,、字典對(duì)象的主要原理：字典對(duì)象實(shí)際上是維護(hù)一個(gè)數(shù)據(jù)表,，該表有2列、任意行,，行的多少僅受存儲(chǔ)空間限制,。第1列鍵(key)，特點(diǎn)是不能重復(fù),，第2列項(xiàng)目(item),，與key相關(guān)聯(lián)。使用時(shí),，key可以是除數(shù)組外的任意數(shù)據(jù)類(lèi)型,，item則可存入各類(lèi)雜七雜八的數(shù)據(jù)類(lèi)型包括數(shù)組。

3,、引用方法：

引用項(xiàng)目：d.item(key)  可以使用某個(gè)指定的鍵key來(lái)獲取對(duì)應(yīng)的項(xiàng)目item,，也可簡(jiǎn)寫(xiě)成d(key)

鍵的總數(shù)：d.count

獲取全部key:  d.keys  返回一個(gè)數(shù)組包含全部key

獲取全部item: d.items 返回一個(gè)數(shù)組包含全部item

寫(xiě)入一個(gè)新鍵：d.add key,item  

判斷某鍵是否存在： d.Exists(key)

刪除一個(gè)鍵：d.Remove(key)

刪除全部鍵：d.RemoveAll   實(shí)際上就是清空了整個(gè)字典對(duì)象

如果引用一個(gè)不存在的鍵，字典對(duì)象就會(huì)添加這個(gè)鍵,。

   如需vb源程序代碼,，請(qǐng)留言。