EXCEL職場必考題-擷取儲存格中的數字並計算
EXCEL資料在建立或是填入時，可以的話最好將文字與會被計算的數字分開欄位填寫，這樣資料未來統計與分析上比較不會有問題，因為文字與數字在同一儲存格中並存，是無法直接進行計算的。
下面舉一個例子，重陽節的時候有購買了一些物品，這些物品名稱與金額都一起填入了相同的儲存格中，這樣會發現SUM要加總時會直接顯0，因為儲存格認定這些都是數字沒辦法加總。
這時候就要運用一些方法將儲存格中的數字給擷取出來加總，這邊分享各種不同的做法
💡新版函數法
新版函數會使用到兩個函數
✍🏾TEXTAFTER取特定文字之後的內容
函數說明=TEXTAFTER(內容,擷取關鍵字,擷取位置,大小寫判斷,找不到時顯示)
✍🏾TEXTBEFORE取特定文字之前的內容
函數說明=TEXTBEFORE(內容,擷取關鍵字,擷取位置,大小寫判斷,找不到時顯示)
C5=SUM(--TEXTBEFORE(TEXTAFTER(B5:B15,":"),"元"))
函數說明
- TEXTAFTER(B5:B15,":")
->擷取:之後的文字 - TEXTBEFORE(TEXTAFTER(B5:B15,":"),"元")
->將步驟1的內容，擷取"元"之前的文字，就會得到金額，此時的數字其實是文字 - TEXTBEFORE(TEXTAFTER(B5:B15,":"),"元")
->將步驟2的內容，轉換成數字 - 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輸入
函數說明
- FIND(":",B5:B15)+1
->找到":"在儲存格中是第幾個位置，在加1就是數字的起始點 - MID(B5:B15,FIND(":",B5:B15)+1,9)
->用MID配合步驟1的公式，擷取出":"之後9個字，本例子":"之後字數都沒有超過9個字，所以直接輸入9，如果實際狀況字數較多，可以自行調整 - --SUBSTITUTE(MID(B5:B15,FIND(":",B5:B15)+1,9),"元","")
->使用SUBSTITUTE將步驟2內容中的"元"，取代成""，也就是取代成沒有資料，這樣就會擷取出金額，在前面加上兩個負號，將文字轉換成數字 - SUM(--SUBSTITUTE(MID(B5:B15,FIND(":",B5:B15)+1,9),"元",""))
->最後用SUM將步驟3的結果全部相加
💡剖析法
可以用資料剖析的方式將金額擷取出來
- 資料-->資料工具-->資料剖析
- 使用分隔符號剖析資料
- 分隔符號選擇"其他"，輸入":"，內容就會依據冒號拆分成兩筆資料
- 金額還有文字的"元"，在剖析一次，分隔符號選擇"其他"，輸入"元"，這樣就會只剩下數字
- 最後再利用SUM函數將剖析出來的金額加總
💡CTRL+E快速填入法
也可以用CTRL+E快速填入的功能，這個方法可以說是最無腦的做法了，但如果資料比較複雜一點，很有可能會誤判
- 現在旁邊的第一個儲存個輸入一筆資料的金額
- 接下來點選下方空白儲存格，按下鍵盤CTRL+E
- 這樣就會自動依據資料的規則填入所有的金額
- 最後再用SUM把資料加總
💡沒有固定符號的數字擷取
上面的範例有":"可以當關鍵字搜尋，如果像下圖一樣，沒有任何關鍵字，CTRL+E一樣可以快速處理，但是如果使用函數的話該怎麼做呢?
💡通用版本函數
C5=--SUBSTITUTE(MID(B5,MATCH("1",TEXT(MID(B5,ROW($1:$9),1),"[>=0]1"),0),9),"元","")
函數說明
- MID(B5,ROW($1:$9),1)
->利用MID配合ROW把儲存格內容變成每一個字獨立拆開的陣列 - TEXT(MID(B5,ROW($1:$9),1),"[>=0]1")
->利用TEXT將步驟1拆開的內容，把陣列中所有數字變成1的字串 - MATCH("1",TEXT(MID(B5,ROW($1:$9),1),"[>=0]1"),0)
->利用MATCH找出步驟2的陣列1的位置，也就是金額的起始位置 - MID(B5,MATCH("1",TEXT(MID(B5,ROW($1:$9),1),"[>=0]1"),0),9)
->利用MID取出步驟3找出來金額的起始位置，在往後擷取9個字，就會得到金額後面加上一個元 - --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),""))
- MID(B5,ROW($1:$9),1)
->利用MID配合ROW把儲存格內容變成每一個字獨立拆開的陣列 - --MID(B5,ROW($1:$9),1)
->把步驟1的陣列前面補上兩個負號，數字維持數字，文字會變成#VALUE - IFERROR(--MID(B5,ROW($1:$9),1),"")
->利用IFERROR將陣列中的#VALUE轉換成""，就會只剩下數字 - --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)))
函數說明
- --MID(B3,ROW($1:$99),1)
->使用MID配合ROW(1:99)將儲存格1~99個字，每一個字獨立擷取出來，然後最前面加上兩個負號，如果是文字就會出現#VALUE，數字就會保持數字 - ISNUMBER(--MID(B3,ROW($1:$99),1))
->利用ISNUMBER邏輯函數，把步驟1的數字變成TRUE，文字變成FALSE - IF(ISNUMBER(--MID(B3,ROW($1:$99),1)),ROW($1:$99))
->用IF判斷，如果步驟2是TURE的話就回傳相對應的ROW(1:99)，就會得到所有數字的位置 - 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)
函數說明
- B3->原始資料
- C3->原始資料中第一個數字是在第幾個字
- D3->原始資料中最後一個數字是在第幾個字
- D3-C3+1->總共有幾個數字
- 這樣就能用MID(資料,第一個數字位置,幾個數字)這串公式擷取出數字囉
💡2019版本以上函數
C3=CONCAT(IFERROR(--MID(B3,ROW($1:$99),1),""))
函數說明
這個函數跟前面的有一個解法是一樣的，可以去參考前面的做法唷
- 👍喜歡的話可以幫忙案個讚、分享來幫助更多人或是右下珍藏起來哦
- 💭留言回復「我只要數字」讓我知道你把這個小技巧學起來了
- ❤️追蹤我的方格子，學習更多職場小技巧
- ☕請我喝杯咖啡，鼓勵我更有動力分享更多優質內容
- 📈訂閱EXCEL設計新思維，學習更多更深更廣的職場技能
😎可以找到我的地方
