EXCEL職場必考題-擷取儲存格中的數字並計算

EXCEL資料在建立或是填入時，可以的話最好將文字與會被計算的數字分開欄位填寫，這樣資料未來統計與分析上比較不會有問題，因為文字與數字在同一儲存格中並存，是無法直接進行計算的。

下面舉一個例子，重陽節的時候有購買了一些物品，這些物品名稱與金額都一起填入了相同的儲存格中，這樣會發現SUM要加總時會直接顯0，因為儲存格認定這些都是數字沒辦法加總。

💡新版函數法

新版函數會使用到兩個函數

✍🏾TEXTAFTER取特定文字之後的內容
函數說明=TEXTAFTER(內容,擷取關鍵字,擷取位置,大小寫判斷,找不到時顯示)

✍🏾TEXTBEFORE取特定文字之前的內容
函數說明=TEXTBEFORE(內容,擷取關鍵字,擷取位置,大小寫判斷,找不到時顯示)

C5=SUM(--TEXTBEFORE(TEXTAFTER(B5:B15,":"),"元"))

函數說明

1. TEXTAFTER(B5:B15,":")
   ->擷取:之後的文字
2. TEXTBEFORE(TEXTAFTER(B5:B15,":"),"元")
   ->將步驟1的內容，擷取"元"之前的文字，就會得到金額，此時的數字其實是文字
3. TEXTBEFORE(TEXTAFTER(B5:B15,":"),"元")
   ->將步驟2的內容，轉換成數字
4. SUM(--TEXTBEFORE(TEXTAFTER(B5:B15,":"),"元"))
   ->用SUM把步驟3所有的數字相加

PS.為什麼要加上兩個負號，可以參考這篇[EXCEL數字VS文字型數字]

💡舊版函數法

如果EXCEL版本較舊，沒有TEXTAFTER與TEXTBEFORE，那麼就要使用元老級的函數，MID、FIND、SUBSTITUTE這三個函數

✍🏾MID擷取儲存格中指定位置與字數的內容
函數說明=MID(內容,起始點,字數)

✍🏾FIND計算指定內容在儲存格是第幾個字
函數說明=FIND(尋找的文字,內容,指定位置)

✍🏾SUBSTITUTE取代指定字元變成另一字元
函數說明=SUBSTITUTE(內容,舊字元,新字元)

C5=SUM(--SUBSTITUTE(MID(B5:B15,FIND(":",B5:B15)+1,9),"元",""))

這個函數為陣列函數，舊版在輸入前，必續按CTRL+SHIFT+ENTER取代ENTER輸入

函數說明

1. FIND(":",B5:B15)+1
   ->找到":"在儲存格中是第幾個位置，在加1就是數字的起始點
2. MID(B5:B15,FIND(":",B5:B15)+1,9)
   ->用MID配合步驟1的公式，擷取出":"之後9個字，本例子":"之後字數都沒有超過9個字，所以直接輸入9，如果實際狀況字數較多，可以自行調整
3. --SUBSTITUTE(MID(B5:B15,FIND(":",B5:B15)+1,9),"元","")
   ->使用SUBSTITUTE將步驟2內容中的"元"，取代成""，也就是取代成沒有資料，這樣就會擷取出金額，在前面加上兩個負號，將文字轉換成數字
4. SUM(--SUBSTITUTE(MID(B5:B15,FIND(":",B5:B15)+1,9),"元",""))
   ->最後用SUM將步驟3的結果全部相加

💡剖析法

可以用資料剖析的方式將金額擷取出來

1. 資料-->資料工具-->資料剖析
2. 使用分隔符號剖析資料
3. 分隔符號選擇"其他"，輸入":"，內容就會依據冒號拆分成兩筆資料
4. 金額還有文字的"元"，在剖析一次，分隔符號選擇"其他"，輸入"元"，這樣就會只剩下數字
5. 最後再利用SUM函數將剖析出來的金額加總

💡CTRL+E快速填入法

也可以用CTRL+E快速填入的功能，這個方法可以說是最無腦的做法了，但如果資料比較複雜一點，很有可能會誤判

1. 現在旁邊的第一個儲存個輸入一筆資料的金額
2. 接下來點選下方空白儲存格，按下鍵盤CTRL+E
3. 這樣就會自動依據資料的規則填入所有的金額
4. 最後再用SUM把資料加總

💡沒有固定符號的數字擷取

上面的範例有":"可以當關鍵字搜尋，如果像下圖一樣，沒有任何關鍵字，CTRL+E一樣可以快速處理，但是如果使用函數的話該怎麼做呢?

💡通用版本函數

C5=--SUBSTITUTE(MID(B5,MATCH("1",TEXT(MID(B5,ROW($1:$9),1),"[>=0]1"),0),9),"元","")

函數說明

1. MID(B5,ROW($1:$9),1)
   ->利用MID配合ROW把儲存格內容變成每一個字獨立拆開的陣列
2. TEXT(MID(B5,ROW($1:$9),1),"[>=0]1")
   ->利用TEXT將步驟1拆開的內容，把陣列中所有數字變成1的字串
3. MATCH("1",TEXT(MID(B5,ROW($1:$9),1),"[>=0]1"),0)
   ->利用MATCH找出步驟2的陣列1的位置，也就是金額的起始位置
4. MID(B5,MATCH("1",TEXT(MID(B5,ROW($1:$9),1),"[>=0]1"),0),9)
   ->利用MID取出步驟3找出來金額的起始位置，在往後擷取9個字，就會得到金額後面加上一個元
5. --SUBSTITUTE(MID(B5,MATCH("1",TEXT(MID(B5,ROW($1:$9),1),"[>=0]1"),0),9),"元","")
   ->最後在用SUBSTITUTE把"元"取代變成""，這樣就會保留數字了，但此時這個數字還是文字，在前面加上兩個負號，那麼金額就順利被截取出來了

💡新版本函數2019以上

2019以上的版本有CONCAT函數，使用就會簡單很多了

C5=--CONCAT(IFERROR(--MID(B5,ROW($1:$9),1),""))

1. MID(B5,ROW($1:$9),1)
   ->利用MID配合ROW把儲存格內容變成每一個字獨立拆開的陣列
2. --MID(B5,ROW($1:$9),1)
   ->把步驟1的陣列前面補上兩個負號，數字維持數字，文字會變成#VALUE
3. IFERROR(--MID(B5,ROW($1:$9),1),"")
   ->利用IFERROR將陣列中的#VALUE轉換成""，就會只剩下數字
4. --CONCAT(IFERROR(--MID(B5,ROW($1:$9),1),""))
   ->利用CONCAT把所有的陣列內容合在一起，就會的到金額，結果會是字串，要在前面加上兩個負號轉換成數值

▶️YT影片教學

💡10/28更新-擷取儲存格中無規律的連續數字

如下圖，這種資料中完全找不到任何規律的資料中，要把連續的數字找出來，要如何用函數解決呢?

💡通用版函數

A.找出儲存個第一個數字的位置

C3=MIN(IF(ISNUMBER(--MID(B3,ROW($1:$99),1)),ROW($1:$99)))

函數說明

1. --MID(B3,ROW($1:$99),1)
   ->使用MID配合ROW(1:99)將儲存格1~99個字，每一個字獨立擷取出來，然後最前面加上兩個負號，如果是文字就會出現#VALUE，數字就會保持數字
2. ISNUMBER(--MID(B3,ROW($1:$99),1))
   ->利用ISNUMBER邏輯函數，把步驟1的數字變成TRUE，文字變成FALSE
3. IF(ISNUMBER(--MID(B3,ROW($1:$99),1)),ROW($1:$99))
   ->用IF判斷，如果步驟2是TURE的話就回傳相對應的ROW(1:99)，就會得到所有數字的位置
4. MIN(IF(ISNUMBER(--MID(B3,ROW($1:$99),1)),ROW($1:$99)))
   ->用MID取出步驟3的最小值，也就是第一個數字的位置

B.找出儲存個最後一個數字的位置

D3=MAX(IF(ISNUMBER(--MID(B3,ROW($1:$99),1)),ROW($1:$99)))

函數說明

跟A的找出儲存格中第一個數字位置都相同，差異只有最後一個，MIN改為MAX，也就是找出數字最後的位置

C.擷取出數字

E3=MID(B3,C3,D3-C3+1)

函數說明

1. B3->原始資料
2. C3->原始資料中第一個數字是在第幾個字
3. D3->原始資料中最後一個數字是在第幾個字
4. D3-C3+1->總共有幾個數字
5. 這樣就能用MID(資料,第一個數字位置,幾個數字)這串公式擷取出數字囉

💡2019版本以上函數

C3=CONCAT(IFERROR(--MID(B3,ROW($1:$99),1),""))

函數說明

這個函數跟前面的有一個解法是一樣的，可以去參考前面的做法唷

• 👍喜歡的話可以幫忙案個讚、分享來幫助更多人或是右下珍藏起來哦
• 💭留言回復「我只要數字」讓我知道你把這個小技巧學起來了
• ❤️追蹤我的方格子，學習更多職場小技巧
• ☕請我喝杯咖啡，鼓勵我更有動力分享更多優質內容
• 📈訂閱EXCEL設計新思維，學習更多更深更廣的職場技能

😎可以找到我的地方

1. LINE社群
2. IG
3. FB粉絲團
4. YOUTUBE
5. TIKTOK
6. DCARD