EXCEL中數(shù)據(jù)的自動(dòng)匹配

摘 要：EXCEL中無(wú)法直接使用SELECT語(yǔ)句進(jìn)行數(shù)據(jù)查詢、定位、匹配，必須依賴(lài)其自身提供的函數(shù)。本文將介紹三種EXCEL中的數(shù)據(jù)自動(dòng)匹配方法，使單元格內(nèi)容能夠自動(dòng)引用、填充關(guān)聯(lián)內(nèi)容，為EXCEL使用者更加快捷的使用數(shù)據(jù)提供參考。

關(guān)鍵詞：自動(dòng)匹配；關(guān)聯(lián)數(shù)據(jù)；EXCEL

中圖分類(lèi)號(hào)：G642 文獻(xiàn)標(biāo)識(shí)碼：A

1 引言

EXCLE作為一款電子表格軟件，提供了直觀的界面、出色的數(shù)據(jù)處理能力和圖表工具，再加上成功的市場(chǎng)營(yíng)銷(xiāo)，使其成為最流行的個(gè)人計(jì)算機(jī)數(shù)據(jù)處理軟件。

使用EXCLE作為數(shù)據(jù)源進(jìn)行數(shù)據(jù)處理、統(tǒng)計(jì)分析和輔助決策等操作時(shí)[1]，經(jīng)常要使用到數(shù)據(jù)查詢和定位功能。雖然EXCEL可以創(chuàng)建二維表格[2]，但無(wú)法像真正的數(shù)據(jù)庫(kù)軟件一樣使用SELECT語(yǔ)句進(jìn)行數(shù)據(jù)的查詢、定位、匹配以及更新[3，4]，而EXCEL提供的“查找”功能，只能根據(jù)用戶提供的關(guān)鍵字進(jìn)行查找定位或者替換，無(wú)法實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)定位、引用和匹配，這些操作必須借助于EXCEL函數(shù)。為了幫助使用者能夠更好的使用EXCEL進(jìn)行關(guān)聯(lián)數(shù)據(jù)的定位、引用，本文將介紹三種利用EXCEL函數(shù)實(shí)現(xiàn)數(shù)據(jù)自動(dòng)匹配的方法。

2 自動(dòng)匹配

定義：在EXCEL的一個(gè)單元格區(qū)域中，某些單元格的值可以自動(dòng)引用另一單元格區(qū)域中相關(guān)單元格的內(nèi)容。

現(xiàn)有兩張表表1和表2，表2中A列已填充數(shù)據(jù)，B列中未填充數(shù)據(jù)。自動(dòng)匹配的目的是讓表2的B列自動(dòng)引用表1中B列的關(guān)聯(lián)數(shù)據(jù)。如表2中的B2單元格內(nèi)應(yīng)自動(dòng)填入“Mike”，B2內(nèi)應(yīng)自動(dòng)填入“Tom”，B3內(nèi)應(yīng)自動(dòng)填入“Marry”……

表1 源數(shù)據(jù)表

表2 引用數(shù)據(jù)

3 實(shí)現(xiàn)方法

3.1 LOOKUP函數(shù)

（1）定義：LOOKUP函數(shù)可從單行或單列區(qū)域或者從一個(gè)數(shù)組返回值。它的語(yǔ)法格式如下：

LOOKUP（lookup_value，lookup_vector，[result_vector]）其中，lookup_value為搜索值，lookup_vector為只包含一行或一列的搜索區(qū)域，result_vector為只包含一行或一列的返回區(qū)域。

（2）使用方法

使用LOOKUP函數(shù)實(shí)現(xiàn)自動(dòng)匹配，只需在表2的B1單元格中輸入：

=LOOKUP（A1，表1！$A$1：$A$3，表1！$B$1：$B$3）。由于引用的單元格區(qū)域是固定不變的，其引用地址要使用絕對(duì)地址，輸入完畢后拖動(dòng)單元格右下角至B3，完成表2中 B列的自動(dòng)匹配。

3.2 VLOOKUP函數(shù)

（1）定義：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查找精確匹配值（true）還是近似匹配值（1）。

（2）使用方法

使用VLOOKUP函數(shù)實(shí)現(xiàn)自動(dòng)匹配，只需在表2的B1單元格中輸入：

=VLOOKUP（A1，表1！$A$1：$B$3，2，TRUE）。單元格區(qū)域的引用要使用絕對(duì)地址，輸入完畢后拖動(dòng)單元格右下角至B3，完成表2中B列的自動(dòng)匹配。如果待定位的數(shù)據(jù)排列是橫向的，應(yīng)使用HLOOKUP函數(shù)，其使用方法和VLOOKUP函數(shù)類(lèi)似，故不再贅述。

3.3 INDEX和MATCH函數(shù)

（1）INDEX函數(shù)定義：返回表格或區(qū)域中的值或值的引用。它的語(yǔ)法格式如下：

INDEX（array， row_num， [column_num]）

其中，Array為單元格區(qū)域或數(shù)組常量，Row_num為區(qū)域或數(shù)組中的某行，函數(shù)從該行返回?cái)?shù)值，Column_num為區(qū)域或數(shù)組中的某列，函數(shù)從該列返回?cái)?shù)值。

（2）MATCH函數(shù)定義：在單元格區(qū)域中搜索指定項(xiàng)，然后返回該項(xiàng)在單元格區(qū)域中的相對(duì)位置。它的語(yǔ)法格式如下：

MATCH（lookup_value， lookup_array， [match_type]）其中，lookup_value為需要在lookup_array中查找的值，lookup_array為要搜索的單元格區(qū)域，match_type為匹配選項(xiàng)，當(dāng)值為1或省略時(shí)返回小于或等于lookup_value的最大值，當(dāng)值為0時(shí)返回等于lookup_value的第一個(gè)值，當(dāng)值為-1時(shí)返回大于或等于lookup_value的最小值。

（3）使用方法

使用INDEX和MATCH函數(shù)實(shí)現(xiàn)自動(dòng)匹配，只需在表2的B1單元格中輸入：

=INDEX（表1！$A$1：$B$3，MATCH（A1，表1！$A$1：$A$3，0），2）。

其中第二個(gè)參數(shù)“MATCH（A1，表1！$A$1：$A$3，0）”函數(shù)返回A1的值在表1A列中的相對(duì)位置，第三個(gè)參數(shù)“2”表示單元格區(qū)域“！$A$1：$B$3”中的第2列即B列。INDEX函數(shù)根據(jù)MATCH函數(shù)指示的位置返回該行中B列的值。單元格區(qū)域的引用要使用絕對(duì)地址，輸入完畢后拖動(dòng)單元格右下角至B3，完成表2中B列的自動(dòng)匹配。

4 總結(jié)

幾種定位方法均能完成數(shù)據(jù)的自動(dòng)匹配，效率相差不大。由于EXCEL無(wú)法像數(shù)據(jù)庫(kù)軟件一樣通過(guò)添加列索引提高查詢效率，所以數(shù)據(jù)預(yù)處理工作特別重要，比如數(shù)據(jù)排序、減少冗余數(shù)據(jù)等，另外在查找數(shù)據(jù)時(shí)可以設(shè)置匹配方式為近似匹配。

參考文獻(xiàn)

[1] 吳智，文清平.基于Excel高校課程考核成績(jī)分析樣板的開(kāi)發(fā)

[J].辦公自動(dòng)化，2012，4：60-62.

[2] E.F.Codd.A Relational Model of Data for Large Shared Data

Banks[J].Communication of the ACM，1970，13（6）：377-387.

[3] 李國(guó)雁，田源.關(guān)系代數(shù)中除運(yùn)算的SQL語(yǔ)句實(shí)現(xiàn)[J].現(xiàn)代計(jì)算

機(jī)，2010，12：40-43.

[4] 李國(guó)雁，李奇.關(guān)系代數(shù)中交運(yùn)算與差運(yùn)算的SQL語(yǔ)句實(shí)現(xiàn)[J].

福建電腦，2009，11：178-179.

作者簡(jiǎn)介：

李國(guó)雁（1979-），男，碩士，講師.研究領(lǐng)域：知識(shí)發(fā)現(xiàn).