-
1 # 雷哥office
-
2 # EXCEL學習微課堂
一、用VLOOKUP提取單元格字串的數值
如下圖的案例中,字元中間的數值有2位數、3位數和4位數的,而且位置不一樣,用VLOOKUP函式提取資料的公式為:{=VLOOKUP(9E+307,MID(B2,MIN(IF(ISNUMBER(--MID(B2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2)},需要注意的是,這個函式公式是陣列公式,公式寫完後要按CTRL+SHIFT+ENTER結束,否則會報錯。
二、用快速填充(CTRL+E)的方法提取數值快速填充是EXCEL2013以上版本的中一個新功能,她能模擬、識別你的操作,推測你內心的想法,然後按照你的想法進行資料填充。
使用快速填充時,首先直接輸入1組或2組資料,讓Excel自動識別你的意圖,再按CTRL+E進行快速填充。下圖案例用的是EXCEL2016版本,輸入第1個數據12和第2資料5000後,系統自動識別你的意圖就是提取資料,這時按CTRL就提取了所有的資料,是不是特別快呢?
動圖如下:
三、用自定義函式Myget提取函式有兩引數,=myget(①從哪提取,②提取什麼)
=myget(字串,0) 取出數字
=myget(字串,1) 取出中文字元
=myget(字串,2) 取出英文字母
=myget(字串,3) 取出特殊字元
=myget(字串,4) 取出取第一個數字的位置
=myget(字串,5) 取出取最後一個數字的位置
方法步驟:
首先匯入自定義函式的模板檔案,點開發工具→VBA或都按ALT+F11 調出VBA 編輯器 →在工程視窗右鍵選擇【匯入檔案】→選擇VBA 模組檔案(提取中文、英文和數字自定義函式myget.bas)→關閉VBA 編輯器。然後再輸入公式即可。
動圖演示:
-
3 # Excel自學成才
Excel中如何使用VLOOKUP函式提取單元格字串中間的數值
使用的公式是:
=VLOOKUP(9E+307,MID(A2,MIN(IF(ISNUMBER(--MID(A2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2)
輸入完公式按CTRL+SHIFT+ENTER鍵
❶使用使用MID(A2,ROW(1,99),1),把所有的資料拆開,最大長度是99個
=MID($A$2,ROW(),1)
❷在前面的公式加上兩個負號,乘以兩次-1,數字能得到結果,非數字出現錯誤
=--D1
❸判斷是否是數字,如果是數字,返回行號
=IF(ISNUMBER(E1),ROW())
❹再使用min()函式,獲取了這個出現數字的第1個位置,是4個位置
=MIN(F1:F99)
❺然後再使用MID()函式,從第4位開始拆解資訊,依次取1,2,3,4...99位得到一個數組
MID($A$2,$G$1,ROW())*1
❻用*{1,1}得到了兩列陣列
=MID($A$2,$G$1,ROW())*1
❼最後使用VLOOKUP的模糊查詢得到了最終的結果:
=VLOOKUP(9E+307,H:I,2)
這些輔助列的過程,就是公式的思路過程,所以說,在這個思路內我們可以調整公式,例如使用這個公式:
=VLOOKUP(9^9,--MID(A2,MIN(IF(ISNUMBER(--MID(A2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99)),1,1)
按三鍵也能得到最終的結果
我們要學習用這種方法來理解公式,這個技巧,你學會了麼?
回覆列表
① Vlookup函式語法
② MID函式和LEFT函式講解
大家理解了vlookup函式和MID等函式的基本含義後,下面我們一起來看看,如何使用vlookup提取數字。
Vlookup提取數字大家先來看效果,輸入公式,按下【ctrl+shift+enter】後,可以發現數值已經提取出來了。 修改數字後,數字依然可以自動提取出來。
那麼,公式那麼長,很多小夥伴估計會說,“哀家搞不懂,寶寶心裡苦”。鑑於此,雷哥來跟大家講解下公式哈,請大家認真閱讀哦。
=VLOOKUP(9E+307,MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2)
① 引數1:9E+307: 是科學計數法,是指9*10^307,表示很大的數字;常被用來數字查詢。
② 引數2:MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1}, 查詢區域。
-Min函式,表示取最小值;
-IF函式,邏輯函式;
-ISNUMBER,返回數值資料;
-ROW,返回資料的行號。
④ 引數4:省略了,表示模糊查詢;
先用MID+ ROW函式對字串擷取每一位字元,在用ISNUMBER判斷資料型別是否是數值型別。對於數值型別的資料,返回其位置數,並用MID函式提取最小值(數值的第一個符號的位置,即數字起點),然後從該位置做為起點,擷取1-99之前的數值。
因為MID提取的是文字,因此需要資料*1,把資料格式轉化為數值格式。
最後,藉助Vlookup函式的模糊查詢,返回目標結果。
常見錯誤彙總:
1 查詢目標和查詢區域第一列的格式不一致=VLOOKUP(G3,A2:D12,4,1) 查詢時,返回錯誤值#N/A 。
診斷分析:雷哥透過他的火眼金睛,立馬就發現了問題。這是因為查詢值(11208)與查詢範圍第一列(工號)資料格式不一致導致的。
藥方:透過【分列】功能,把G列的資料轉化為常規格式
2 查詢目標不在查詢區域的第一列診斷分析:雷哥指出,這是因為查詢目標(金鵬)不在查詢區域(A2:D12)的第一列。
藥方:因此只要把查詢區域改為B2:D12,返回列數改為3即可
3 返回的列數超過了查詢的範圍藥方:返回列數由4改為3即可
① 可以透過Vlookup提取文字中的數字,關鍵是引數2的理解;
② 使用Vlookup時,有常見的3種錯誤。平時一定要引起注意!