Excel以一個通用公式取出儲存格內容中的數值部份-含負號
|
2970 |
Excel以一個通用公式取出儲存格內容中的數值部份-含負號 |
李孟芬：「錦子老師您好，我有一個表格A欄全部都是文數字的集合，由於筆數最少百筆多則萬筆，我要如何才可以將其中的數字擷取出來到B欄，若為負值，則連負號一併擷取出來？麻煩解惑 ~ 感恩，謝謝！」
錦子老師：「這個問題其實很複雜，也多虧二位線上老師(林文斌、周勝輝)幫忙說明，使我瞭解到自己還是有不足的地方，在此與大家分享二位老師的解答，操作方法如下：
步驟1：點取B2儲存格輸入公式：
=-LOOKUP(9^9,-MIDB(A2,SEARCHB("?",A2),ROW($1:$20)))
並將公式複製到B3:B7儲存格。
這個公式的缺點是無法抓取數字開頭為0的數值。
【公式說明】
=-LOOKUP(9^9,-MIDB(A2,SEARCHB("?",A2),ROW($1:$20))))
公式1：SEARCHB("?",A2)
在A2儲存格內容中尋找數值起始位元。
公式2：-MIDB(A2,公式1,ROW($1:$20))
這裡使用MIDB，而不是使用MID，是為了對應FINDB，通過位元組位置截取部分文字。ROW($1:$20)傳回有序數組{1-20}，作為MIDB函數的第三個參數(要提取的位元組數)，即分別提取1-100個字元，MIDB函數的功能就是從公式1確定的起始位置開始，分別從A2儲存格文字中截取長度為1-20個位元組的20個不等長度字串，而-MIDB則是將不等長度字串執行減法運算，使得非數值資料因無法運算而報錯為#VALUE!，進而將不等長度字串轉化為純數值和錯誤值#VALUE!組成的新常量陣列。
公式3：-LOOKUP(9^9,公式2)
LOOKUP查詢有三個特性：
1．預設查詢範圍是升冪的，即越往後值越大。
2．傳回值應小於且最接近於查詢值。
3．忽略查詢範圍中的錯誤值。
由此，我們賦予查詢值一個極大數9^9，因為LOOKUP的特性1，所以查詢範圍的最後一個非錯誤值為最大值，即該值為傳回值。LOOKUP的這幾個特性，完美地做到了忽略錯誤值取最後一個有效值！
以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~
更多相關影片教學：請點我
更多相關文章：請點我