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的這幾個特性,完美地做到了忽略錯誤值取最後一個有效值!
以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
更多相關影片教學:請點我
更多相關文章:請點我