我們在工作中，經(jīng)常會遇到帶單位的數(shù)據(jù)運(yùn)算，比如對帶有單位的數(shù)據(jù)求和、提取不同單位中的數(shù)值，合并不同單位的數(shù)量等等。下面就通過 5 個典型的案例，一起和大家探討，如何應(yīng)對處理這些帶單位的數(shù)據(jù)。

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

1、帶單位的數(shù)字求和

如下圖，對 C 列的金額求和。

? 首先用 SUBSTITUTE 函數(shù)將 C 列中的字符 "元" 替換成空字符：

=SUBSTITUTE(C3:C8"元""")

? 這時生成的一列數(shù)字還是文本型的，不能直接求和，需要在前面加兩個負(fù)號轉(zhuǎn)換成數(shù)值：

=--SUBSTITUTE(C3:C8"元""")

? 最后就可以用 SUM 函數(shù)，對轉(zhuǎn)化后的數(shù)值求和，得到最終結(jié)果：

=SUM(--SUBSTITUTE(C3:C8,"元",""))

2、提取不同單位中的數(shù)值

如下圖，從 B 列帶有不同單位的文本中，提取數(shù)值到 C 列：

? 首先用 ROW ($1:9) 生成一組由 1 開始遞增的序列值：

=ROW($1:9)

? 然后以這組序列值作為 LEFT 函數(shù)的第二個參數(shù)，由左至右逐一擴(kuò)展截取當(dāng)前 B 列文本中的字符，生成一列文本數(shù)組：

=LEFT(B3,ROW($1:9))

? 最后用 LOOKUP 函數(shù)，在這列數(shù)組中找到最后一個數(shù)值，返回結(jié)果：

3、拆分帶有多個單位的文本

如下圖，從 C 列中拆分帶有多個單位的數(shù)字到 D:F 列：

? 首先用 LEFT 函數(shù)從當(dāng)前 C 列的文本中，提取單位前面的字符：

=LEFT($C3,FIND(D$2$C3)-1)

? 然后用 RIGHT 函數(shù)，由右至左逐一擴(kuò)展提取字符的個數(shù)，截取字符，生成一列數(shù)組：

=RIGHT(LEFT($C3,FIND(D$2,?$C3)-1)ROW($1:9))

? 接著用 LOOKUP 函數(shù)，在這列數(shù)組中找到最后一個數(shù)值，再填充復(fù)制公式：

=-LOOKUP(????1,????-RIGHT(????????LEFT($C3,?FIND(D$2,?$C3)?-?1),????????ROW($1:9)????))

? 最后用 IFNA 函數(shù)屏蔽公式中的錯誤值（#N / A）返回結(jié)果：

=IFNA(????-LOOKUP(????????1,????????-RIGHT(????????????LEFT($C3,?FIND(D$2,?$C3)?-?1),????????????ROW($1:9)????????)????),????"")

4、不同單位的數(shù)量合并

如下圖，將 C:E 列的數(shù)值與對應(yīng)的單位合并成如 F 列的效果：

? 首先用 TEXT 函數(shù)對當(dāng)前行的數(shù)值，進(jìn)行文本格式化設(shè)置，如果是正數(shù)則顯示數(shù)值和單位組成的字符，否則返回空字符，生成一行文本數(shù)組。

=TEXT(C3:E3,0&C$2:E$2&"")

? 再用 CONCAT 函數(shù)將這行文本數(shù)組合并成一串字符，得到結(jié)果:

=CONCAT(TEXT(C3:E3,0&C$2:E$2&""))

5、單位轉(zhuǎn)化

如下圖，根據(jù) D 列的箱規(guī)，將 C 列的數(shù)量轉(zhuǎn)換成如 E 列的效果：

? 首先用當(dāng)前的數(shù)量除以箱規(guī)，再用 INT 函數(shù)取整得到箱數(shù)：

=INT(C4/D4)

? 然后 TEXT 函數(shù)對箱數(shù)進(jìn)行文本格式設(shè)置，如果是正數(shù)則數(shù)值與單位連接，否則返回空字符。

=TEXT(INT(C4/D4)"0?箱")

? 再用 MOD 函數(shù)根據(jù)當(dāng)前的數(shù)量和箱規(guī)求余數(shù)，得到剩下的件數(shù)：

=TEXT(MOD(C4,D4),"0?件;;")

? 最后將兩個公式用連接符」&「連接在一起，返回最終結(jié)果：

=TEXT(INT(C4?/?D4),?"0?箱;;")?&????TEXT(MOD(C4,?D4),?"0?件;;")

好了，今天就分享到這里。

本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：趙驕陽

以上就是Excel帶單位的數(shù)字計(jì)算方法，希望大家喜歡，請繼續(xù)關(guān)注腳本之家。

相關(guān)推薦：

excel表格怎么算全年休假天數(shù)? Excel公式快速計(jì)算2024假期天數(shù)的技巧

excel里輸入公式后只顯示公式怎么辦? excel表格公式不計(jì)算了的解決辦法

最新評論