【原】Excel文本查詢技巧：如何用商品簡稱匹配對應(yīng)全稱

部落窩教育BLW 2024-01-17 發(fā)布于四川

展開全文

編按：在錄入數(shù)據(jù)的時候，有人習(xí)慣錄入全稱，有的習(xí)慣錄入簡稱，那么如何借助函數(shù)來實現(xiàn)簡稱/全稱的相互查詢呢？尤其是如果簡稱中的文字分別來自全稱的前后不同部分，只有把簡稱拆分為單個字再用通配符鏈接起來才能進行查詢。

簡稱和全稱并存，如何實現(xiàn)快速查詢？

下面，我們根據(jù)不同使用場景來介紹如何借助函數(shù)來實現(xiàn)簡稱和全稱之間的轉(zhuǎn)換。

場景1：簡稱是全稱中部分連續(xù)字符

比如一般總賬工作表，會采用全稱“遼寧蓋州金帥蘋果”來進行登記，而各小組在進行手工進賬的時候，又采用的是簡稱“金帥”。一到月末，統(tǒng)計員就要將當(dāng)月臺賬里各個全稱品種的銷量發(fā)送給各大類產(chǎn)品的組長進行核對，下面，我們一起來看看怎么操作。

1.錄入數(shù)據(jù)

新建一個名為“臺賬”工作表，然后按提示輸入統(tǒng)計數(shù)據(jù)。繼續(xù)新建“分賬”工作表，在B~C列按照簡稱輸入各小組手工記賬的數(shù)據(jù)。

2.引用全稱數(shù)據(jù)

切換到“分賬”工作表，定位到A2單元格輸入公式“=VLOOKUP("*"&B2&"*",臺賬!$A$2:$A$5,1,0)”，下拉完成全稱數(shù)據(jù)的引用。

公式解釋：

使用VLOOKUP函數(shù)，以“"*"&B2&"*"”作為查找條件，在臺賬工作表的$A$2:$A$5中查找第1列的數(shù)據(jù)。由于簡稱字符包含于A列中，所以下拉后就可以找到對應(yīng)的全稱了。

3.引用銷售數(shù)據(jù)

繼續(xù)在“分賬”工作表的D2單元格輸入公式“=VLOOKUP(A2,臺賬!$A$2:$B$5,2,0)”，引用全稱銷售數(shù)據(jù)，公式解釋同上。

4.核對數(shù)據(jù)

定位到E2單元格輸入公式“=IF(C2=D2,"","請核對")”，下拉完成數(shù)據(jù)核算標(biāo)注。

公式含義：數(shù)據(jù)一致時則返回空值，不一致就顯示為“請核對”。

場景2：簡稱是全稱中不連續(xù)的字符

在上述操作中，我們借助“"*"&B2&"*"”，即用通配符實現(xiàn)對全稱的引用。

這個引用的前提是：簡稱必須是全稱中部分連續(xù)的文本。但是在實際使用時，可能簡稱字符并不滿足這個條件。

比如 “遼寧蓋州金帥蘋果”的簡稱是“遼金帥”，對于這樣的簡稱，數(shù)據(jù)的引用就需要先拆分簡稱文本為單個字符。

現(xiàn)在，我們要根據(jù)右側(cè)的簡稱數(shù)據(jù)，將銷售額到左側(cè)藍色全稱區(qū)域中。

1．拆分字符

添加“拆分字符”輔助列，在E2單元格輸入公式：

“="*"&MID(D2,1,1)&"*"&MID(D2,2,1)&"*"&MID(D2,3,1)&"*"&MID(D2,4,1)&"*"”，下拉完成拆分操作。

公式解釋：

使用MID函數(shù)依次提取D列簡稱每個字符，然后和通配符“*”連接，最后形成類似“*遼*金*帥**”的形式。這里注意的是，在實際使用時，連接通配符“*”數(shù)量要比D列簡稱中最大字符數(shù)多1個。如本例中，簡稱最大字符數(shù)為4個，那么就使用4個MID函數(shù)提取，使用5個通配符“*”連接，這樣A列名稱才會包含E列。

2．提取數(shù)據(jù)

在B2單元格輸入公式“=LOOKUP(1,0/COUNTIF(A2,E$2:E$5),F$2:F$5)”，下拉填充完成數(shù)據(jù)的引用。

公式解釋：

使用“LOOKUP(1,0)”函數(shù)套路提取數(shù)據(jù)。這里先使用COUNTIF函數(shù)，以A2數(shù)據(jù)為條件，統(tǒng)計區(qū)域為E$2:E$5。由于E$2:E$5使用通配符“*”連接，A2就包含于這個區(qū)域，COUNTIF函數(shù)可以找到其對應(yīng)的數(shù)字，最后通過LOOKUP函數(shù)提取數(shù)據(jù)。

3.完成錄入

在A列輸入全稱數(shù)據(jù)，然后將B、E列公式下拉（注意下拉公式時要更改E$2:E$5、F$2:F$5的區(qū)域，比如可以改為E$2:E$100），并將E列隱藏。以后只要在D、F列輸入簡稱和銷售數(shù)據(jù)，在B列就可以自動完成全稱銷售數(shù)據(jù)的輸入了。