編按：

一個單元格內(nèi)只放一個屬性的數(shù)據(jù)是Excel錄入規(guī)范的要點，但是在日常工作中，我們還是容易遇到不按規(guī)范錄入的源數(shù)據(jù)。那么，當遇到多個不同屬性的數(shù)據(jù)擠在一個單元格中的情況時，我們應該怎么處理呢？如何才能在繁雜的數(shù)據(jù)中，精確提取到我們所需要的數(shù)據(jù)呢？小E今天帶來的就是解決此類問題的兩種方法——分列法和公式法！

正文：

臨近春節(jié)，公司組織了各類團拜活動，行政部門的小劉負責統(tǒng)計大家需要的物資數(shù)量并配合采購發(fā)放，可是大家匯總的結果讓小劉頭疼不已。數(shù)據(jù)是這樣的：

之前講過的Excel錄入規(guī)范，即一個單元格只能存放一種屬性的數(shù)據(jù)。但在這個數(shù)據(jù)表中，一個單元格存放了三個屬性的數(shù)據(jù)，數(shù)量、單位和名稱都合并到了一起存放。這讓小劉感到束手無策，根本無法匯總。

那么該怎么解決呢？

以下，從兩個方面來給出解決方案：

1、從處理數(shù)據(jù)源這個方向下手處理。

2、針對這個數(shù)據(jù)源找到一個適合的匯總公式。

方案一：改造數(shù)據(jù)源

既然問題出在數(shù)據(jù)源錄入不規(guī)范，將原本應該是三列存放的數(shù)據(jù)放在了一列，那么只要把一列再拆分到三列，問題就可以解決了。因此現(xiàn)在要做的就是根據(jù)B列的內(nèi)容，得到C、D、E三列結果。

這就涉及到數(shù)據(jù)提取的方法，以下分別說明。

1、名稱怎么提??？

可以使用公式=RIGHT(B2,LENB(B2)-LEN(B2)-1)提取名稱。

思路分析：

名稱在單元格的右側(cè)，因此用RIGHT函數(shù)提取。在這個問題中的關鍵在于確定名稱的字數(shù)。確定單元格內(nèi)漢字的字數(shù)通常用=LENB(B2)-LEN(B2)這個套路。

公式解析：

LEN和LENB都是統(tǒng)計字數(shù)的函數(shù)，區(qū)別在于LEN不分單字節(jié)和雙字節(jié)，LENB是區(qū)分單字節(jié)和雙字節(jié)的。比如，統(tǒng)計一個漢字時，LEN的結果是1，而LENB的結果是2。因此，在統(tǒng)計B2單元格字數(shù)時，LEN(B2)的結果是6，而LENB(B2)的結果就是9，LENB(B2)-LEN(B2)即可得出B2單元格內(nèi)漢字的字數(shù)。

結果如下圖所示，大家理解一下即可明白。

因為統(tǒng)計出的漢字中還有一個漢字是量詞單位，所以，贈品名稱的字數(shù)需要在漢字的總字數(shù)中減1，即LENB(B2)-LEN(B2)-1。最后，再用Right函數(shù)提取名稱就完成了。

2、數(shù)量怎么提取？

可以使用公式=LEFT(B2,LEN(B2)*2-LENB(B2))提取數(shù)量

數(shù)量在單元格的左側(cè)，因此用LEFT函數(shù)提取。如圖，同樣用LEN函數(shù)和LENB函數(shù)配合計算出數(shù)字的長度。

這個公式的原理，大家研究一下就可以明白了，在此不再贅述。

實際上要解決小劉的問題，有名稱和數(shù)量就足夠了，使用SUMIF函數(shù)就能得到最終的結果。

公式=SUMIF(C:C,I2,F:F)相信同學們都不陌生，就不解釋了。

在這里補充一句，如果要直接用SUMIF函數(shù)的話，公式LEFT(B2,LEN(B2)*2-LENB(B2))的前面需要加--，把LEFT函數(shù)得到的文本型結果轉(zhuǎn)變?yōu)閿?shù)字型結果。

3、擴展學習：單位怎么提取？

這個問題與今天的主題無關，只當是再熟悉一下LEN和LENB的應用吧，公式為：

=MID(B2,LEN(B2)*2-LENB(B2)+1,1)

其中的具體原理，大家可以自己梳理一下，很簡單。

以上的三個問題，都不止一種公式可以實現(xiàn)，有興趣的同學不妨開動腦筋，看看自己能否用其他公式做到。

接下來要介紹的是不改造數(shù)據(jù)源，直接用公式一步到位的方法。

這個公式就是=SUM(IFERROR(--LEFT(B:B,FIND(I2,B:B)-2),))

下面簡單解釋一下這個公式的原理。

以茶葉為例，FIND(I2,B:B)可以實現(xiàn)兩個功能，如果B列包含“茶葉”的數(shù)據(jù)則得到“茶葉”這兩個字所在的位置，如果不含“茶葉”則會得到錯誤值。

如下圖所示，B列實際上有四個單元格是包含了“茶葉”的，FIND得到的就是四個數(shù)字5、4、4、4，其余都是錯誤值。

FIND(I2,B:B)-2的結果中同樣只有四個數(shù)字，分別是3、2、2、2。

注意第一個數(shù)字3，就是第一個茶葉所對應的數(shù)量的長度，這一步的作用也就很容易理解了，就是得到每個名稱所對應的數(shù)量的長度。

長度確定了，就可以直接用LEFT函數(shù)去提取數(shù)量。因此就有了LEFT(B:B,FIND(I2,B:B)-2)，對應的結果如圖所示。

到這一步，只需要將數(shù)值轉(zhuǎn)為文本，將錯誤值變成0，再用SUM求和就是每個贈品的數(shù)量合計了。

數(shù)值轉(zhuǎn)文本是通過“- -”實現(xiàn)的，錯誤值變成0則是IFERROR的特性。

最后通過SUM函數(shù)完成求和即可。

注意這個公式是數(shù)組公式，需要按著Ctrl、shift和回車鍵完成輸入。

小結：一個不規(guī)范數(shù)據(jù)的統(tǒng)計問題，帶出來很多值得思考的問題。今天的教程里有很多公式、函數(shù)在應用方面的細節(jié)問題，十分期待各位同學將本期內(nèi)容的收獲與大家分享，共同進步。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

Excel運用規(guī)范1：一個單元格只記錄一條信息

Excel規(guī)范2：如何做一張能滿足后續(xù)分析需求的表格？

只再說最后一次：Excel數(shù)據(jù)源表家規(guī)

大神專場：Excel數(shù)據(jù)處理常用技巧兩則：分列轉(zhuǎn)置）

版權申明：

本文作者老菜鳥；同時部落窩教育享有專有使用權。若需轉(zhuǎn)載請聯(lián)系部落窩教育。