編按：
工作中，我們常常需要對Excel中的各種數(shù)據(jù)進行統(tǒng)計求和，常見的各種求和操作顯然已經(jīng)難不倒我們。可是當突然面對帶單位的數(shù)字時，我們還是會有種難以下手的感覺……如何對帶單位的數(shù)字進行求和等計算？如果數(shù)字所帶單位長度和名稱均不同，又該如何處理呢？今天，小E將帶來三種解決此類問題的方法，保你一學(xué)就會！
 

帶單位數(shù)字的計算問題很讓人頭疼，其根源在于數(shù)據(jù)源的不規(guī)范性。

今天的文章不是討論表格的使用規(guī)范，也不是操作技巧的教程。今天的案例將通過分析幾種常見問題，由淺入深的對帶單位數(shù)字的計算問題進行一次梳理，讓大家能夠舉一反三。不僅知其然，更要知其所以然。

第一類情況：數(shù)字后面的單位都是統(tǒng)一的。

如下圖所示，每個人的銷售額后面都有一個“元”字。

要對這樣的一列數(shù)字求和，比較常用的是SUMPRODUCT和SUBSTITUTE組合，公式為：

= SUMPRODUCT (1* B2:B14 (B2:B14,"元",""))&"元"

思考：

這個公式的原理比較簡單，首先是SUBSTITUTE(B2:B14,"元","")這一部分。關(guān)于SUBSTITUTE的用法，之前有專門的教程，這個函數(shù)的功能是把單元格內(nèi)指定的字符換成另一個內(nèi)容，有點類似于查找替換功能。

思路解析：

①  平時使用函數(shù)的時候，第一參數(shù)是一個單元格。在本例中第一參數(shù)使用的是數(shù)據(jù)區(qū)域，目的是把B2:B14這個范圍內(nèi)的每個單元格中的“元”字替換為空，也就是清除單位。

②  由于SUBSTITUTE的結(jié)果是文本格式，不能直接求和，所以在前面用1*將結(jié)果轉(zhuǎn)為數(shù)字。1*也可以寫成--，也就是兩個減號，利用負負得正來實現(xiàn)文本轉(zhuǎn)數(shù)字的效果。

③  1*B2:B14 (B2:B14,"元","")得到的是一組數(shù)字，要對一組數(shù)字求和需要用到SUMPRODUCT函數(shù)。如果用SUM函數(shù)的話，需要同時按Ctrl、shift和回車鍵才行。

④  最后在求和結(jié)果后連接一個“元”字保持整體一致。

思路擴展：

如果單位是兩個字的話，方法也是一樣的，例如數(shù)量單位都是“公斤”，則求和公式對應(yīng)修改為=SUMPRODUCT(1*SUBSTITUTE(C2:C14,"公斤",""))&"公斤"即可。如下圖：

第二類情況：單位不一致，但是單位的長度一致。

思考：

這種情況比較少見，例如每個業(yè)務(wù)員需要領(lǐng)取不同包裝方式和數(shù)量的贈品答謝客戶，現(xiàn)在需要對贈品數(shù)量（可以理解為份數(shù)，不管是一盒還是一包都算作一份包裝下的贈品）進行匯總，具體數(shù)據(jù)如圖所示。

這種情況一般用公式=SUMPRODUCT(LEFT(D2:D14,LEN(D2:D14)-1)*1)解決，與第一種情況的區(qū)別在于將SUBSTITUTE這部分改為LEFT-LEN組合，LEFT函數(shù)的作用是從單元格數(shù)據(jù)的最左邊開始提取指定字數(shù)的內(nèi)容。

思路解析：

①  在本例中，難點是如何確定數(shù)字的位數(shù)，因此借助了LEN函數(shù)輔助。LEN函數(shù)的功能是統(tǒng)計單元格內(nèi)數(shù)據(jù)的字數(shù)，因為單位都是一個字，所以數(shù)字的位數(shù)就是整體內(nèi)容的字數(shù)減1。公式中的LEFT(D2:D14,LEN(D2:D14)-1)就是來提取數(shù)字的。

②  同樣，LEFT函數(shù)得到的也是文本，需要處理后才能求和。這和第一類問題的原理完全一樣，不再贅述。

第三類情況：單位不統(tǒng)一，字數(shù)也不一致。

這種情況一般是針對同一行的數(shù)據(jù)進行計算，例如下面這個例子。

思考：

金額=單價*數(shù)量，這個公式原本很簡單，但是因為數(shù)量中存在著字數(shù)不等的單位，就需要先將數(shù)量中的數(shù)字提取出來后才能計算金額。

思路解析：

①  上面的圖中用到的公式是=B2*-LOOKUP(1,-LEFT(C2,ROW($1:$9)))。它看似與前兩類狀況是一樣的“帶單位數(shù)字的計算”的問題，但是有本質(zhì)上的區(qū)別。

②  前兩類是數(shù)組計算，后面這類問題的本質(zhì)卻是找出對單元格內(nèi)的數(shù)字的提取方法，進而再去計算。就本例而言，還有一個更為常見的公式套路：=B2*LEFT(C2,LEN(C2)*2-LENB(C2))

關(guān)于如何從單元格提取數(shù)字，之前有一篇很詳細的教程，本文就不再贅述。

小結(jié)：

老菜鳥還是要再次強調(diào)數(shù)據(jù)源的規(guī)范性！不論何種情況，規(guī)范的數(shù)據(jù)源是高效工作的前提。有些視覺效果可以用自定義格式去實現(xiàn)，例如統(tǒng)一添加單位“元”。

總之，一個單元格不要出現(xiàn)兩種屬性的內(nèi)容，數(shù)字和單位分開存放才是最合理的。

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

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

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

相關(guān)推薦：

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

Excel教程：Excel規(guī)范的數(shù)據(jù)錄入

求和，我是認真的(Excel函數(shù)教程）

DSUM，最簡單的條件求和函數(shù)！你知道不？

版權(quán)申明：

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