錦子老師

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

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

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

=-LOOKUP(9^9,-MIDB(A2,MIN(FINDB(LEFT(ROW($1:$11)-2,1),A2&-1/19)),ROW($1:$100)))

再按CTRL+SHIFT+ENTER完成陣列輸入，並將公式複製到B3:B7儲存格。

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

【公式說明】

=-LOOKUP(9^9,-MIDB(A2,MIN(FINDB(LEFT(ROW($1:$11)-2,1),A2&-1/19)),ROW($1:$100)))

公式1：LEFT(ROW(1:11)-2,1)

由11個字元集合{1,2,3,4,5,6,7,8,9,10,11}減2生成另一個11個字元集合{"-",”0”,”1”,”2”,”3”,” 4”,” 5”,”6”,”7”,”8”,”9”}

公式2：FINDB(公式1,A2&-1/19)

公式2是為了確保公式1{"-",0,1,2,…9}的每一個字元均可以在用FINDB所查找的文字中出現，確保FINDB的傳回值不存在錯誤值，並傳回字元{"-",0,1,2,…9}在A2&-1/19出現的位置。

{-,0,1,2,3,4,5,6,7,8,9}在A2&-1/19的位置{17,18,13,22,24,30,21,23,27,28,29}

公式3：MIN(公式2)

傳回公式2的最小值，它就是目標數值在A2中的起始位置，即A2混合文數字中，首次出現負號或阿拉伯數字的位置，即是目標提取數值的起始位置。

公式4：-MIDB(A2, 公式3,ROW($1:$100))

這裡使用MIDB，而不是使用MID，是為了對應FINDB，通過位元組位置截取部分文字。ROW($1:$100)傳回有序數組{1-100}，作為MIDB函數的第三個參數(要提取的位元組數)，即分別提取1-100個字元，MIDB函數的功能就是從公式3確定的起始位置開始，分別從A2儲存格文字中截取長度為1-100個位元組的100個不等長度字串，而-MIDB則是將不等長度字串執行減法運算，使得非數值資料因無法運算而報錯為#VALUE!，進而將不等長度字串轉化為純數值和錯誤值#VALUE!組成的新常量陣列。

公式5：-LOOKUP(9^9,公式4)

LOOKUP查詢有三個特性：

1．預設查詢範圍是升冪的，即越往後值越大。

2．傳回值應小於且最接近於查詢值。

3．忽略查詢範圍中的錯誤值。

由此，我們賦予查詢值一個極大數9^9，因為LOOKUP的特性1，所以查詢範圍的最後一個非錯誤值為最大值，即該值為傳回值。LOOKUP的這幾個特性，完美地做到了忽略錯誤值取最後一個有效值！

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

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

更多相關文章：請點我