2970Excel以一個通用公式取出儲存格內容中的數值部份-含負號

Excel以一個通用公式取出儲存格內容中的數值部份-含負號

2970

Excel以一個通用公式取出儲存格內容中的數值部份-含負號

李孟芬:「錦子老師您好,我有一個表格A欄全部都是文數字的集合,由於筆數最少百筆多則萬筆,我要如何才可以將其中的數字擷取出來到B欄,若為負值,則連負號一併擷取出來?麻煩解惑 ~ 感恩,謝謝!」

image

錦子老師:「這個問題其實很複雜,也多虧二位線上老師(林文斌周勝輝)幫忙說明,使我瞭解到自己還是有不足的地方,在此與大家分享二位老師的解答,操作方法如下:

步驟1:點取B2儲存格輸入公式:

=-LOOKUP(9^9,-MIDB(A2,SEARCHB("?",A2),ROW($1:$20)))

並將公式複製到B3:B7儲存格。

image

這個公式的缺點是無法抓取數字開頭為0的數值。

【公式說明】

=-LOOKUP(9^9,-MIDB(A2,SEARCHB("?",A2),ROW($1:$20))))

公式1SEARCHB("?",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的這幾個特性,完美地做到了忽略錯誤值取最後一個有效值!

 

以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

更多相關影片教學:請點我

更多相關文章:請點我