編按：

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

正文：

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

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

那么該怎么解決呢？

以下，從兩個(gè)方面來給出解決方案：

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

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

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

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

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

1、名稱怎么提?。?/span>

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

思路分析：

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

公式解析：

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

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

因?yàn)榻y(tǒng)計(jì)出的漢字中還有一個(gè)漢字是量詞單位，所以，贈(zèng)品名稱的字?jǐn)?shù)需要在漢字的總字?jǐn)?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ù)配合計(jì)算出數(shù)字的長度。

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

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

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

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

3、擴(kuò)展學(xué)習(xí)：單位怎么提?。?/span>

這個(gè)問題與今天的主題無關(guān)，只當(dāng)是再熟悉一下LEN和LENB的應(yīng)用吧，公式為：

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

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

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

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

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

下面簡單解釋一下這個(gè)公式的原理。

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

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

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

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

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

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

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

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

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

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

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

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

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

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

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

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

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

版權(quán)申明：

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