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鍵
題主圖片裡面已經給出了公式的結果,應該更多的是想知道這個公式是怎麼來的,應該怎麼樣去理解,其實所有的長公式都是有固定的思路組成的,我們以B2的數字來源為例來解讀這個思路的過程
❶使用使用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)
按三鍵也能得到最終的結果
我們要學習用這種方法來理解公式,這個技巧,你學會了麼?
頭條號:Excel自學成才,作者:E博士,職場5年資料分析例項經驗,歡迎關注,主頁有更多精彩內容。
感謝邀請,雷哥跟大家分享下如何使用vlookup函式提取字元。
基本知識講解
① 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,返回資料的行號。
③
引數3
:2,表示返回第2列;
④
引數4
:省略了,表示模糊查詢;
先用MID+ ROW函式對字串擷取每一位字元,在用ISNUMBER判斷資料型別是否是數值型別。對於數值型別的資料,返回其位置數,並用MID函式提取最小值(數值的第一個符號的位置,即數字起點),然後從該位置做為起點,擷取1-99之前的數值。
因為MID提取的是文字,因此需要資料*1,把資料格式轉化為數值格式。
最後,藉助Vlookup函式的模糊查詢,返回目標結果。
常見錯誤彙總:
萌新小西瓜瞭解了Vlookup函式的基本語法,可還是經常會遇到一些錯誤。於是滿懷期待了走進了雷哥診室,希望能夠藥到病除。
1 查詢目標和查詢區域第一列的格式不一致
場景
:萌新小西瓜需要透過員工工號查詢到電腦號碼。使用
=VLOOKUP(G3,A2:D12,4,1) 查詢時,返回錯誤值
#N/A
。
診斷分析
:雷哥透過他的火眼金睛,立馬就發現了問題。這是因為查詢值(11208)與查詢範圍第一列(工號)資料格式不一致導致的。
藥方
:透過【分列】功能,把G列的資料轉化為常規格式
2 查詢目標不在查詢區域的第一列
場景
:萌新小西瓜需要透過員工姓名查詢到電腦號碼。使用 =VLOOKUP(G3,A2:D12,4,FALSE)查詢時,返回錯誤值
#N/A
。
診斷分析
:雷哥指出,這是因為查詢目標(金鵬)不在查詢區域(A2:D12)的第一列。
藥方
:因此只要把查詢區域改為B2:D12,返回列數改為3即可
3 返回的列數超過了查詢的範圍
場景
:萌新小西瓜需要透過員工姓名查詢到電腦號碼。使用 =VLOOKUP(G3,B2:D12,4,FALSE)查詢時,返回錯誤值
#REF!
。
診斷分析
:萌新小西瓜來到雷哥診所,發現了問題所在。查詢區域一共是3列,而返回值則是4,即返回第4列,因此出現了錯誤。
藥方
:返回列數由4改為3即可
① 可以透過Vlookup提取文字中的數字,關鍵是引數2的理解;
② 使用Vlookup時,有常見的3種錯誤。平時一定要引起注意!
雷哥:自媒體【雷哥office】創始人,《競爭力:玩轉職場Excel,從此不加班》作者,微軟Excel專家認證,office培訓師,職場老司機,分享職場經驗 / 辦公技能 / 提高職場效率!
EXCEL提取資料值的方法很多,用VLOOKUP提取數值的公式有點複雜,而且不方便提取不規則字串中的不規則資料(比如文字前後都是資料),現在高版本的EXCEL用智慧填充就可以快速實現資料提取,另外用自定義函式也可以方便提取數值,下面介紹3種提取單元格數值的方法,希望能夠幫到你!
一、用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 編輯器。然後再輸入公式即可。
動圖演示:
我是EXCEL學習微課堂,頭條號教育影片原創作者,分享EXCEL學習的小技巧,小經驗。如果我的回答能幫到您,歡迎點贊、收藏、評論、轉發,更多的EXCEL技能,可以關注今日頭條“EXCEL學習微課堂”。 需要自定義函式mygetVBA程式碼檔案的,在點贊、評論、轉發後私信聯絡我!
EXCEL中提取單元格字串中的數值使用VLOOKUP函式,同時,問題配圖也給出了使用vlookup函式配合多個其他函式的綜合陣列公式,針對這個公式,我想先說明以下兩點:
1、這個公式除了少數專門玩Excel或日常對Excel十分感興趣的牛人之外,絕大多數的Excel使用者是寫不出來的;
2、這個公式其實並不能全面解決數值提取問題,比如有負數,負號是提取不出來的,如下圖所示:
因此,我個人並不推薦針對提取數值的問題使用這種神長公式的解法。
那麼,對於大多數的Excel普通使用者來說,怎麼辦?個人推薦學習
Excel2016的新功能Power Query(Excel2010或Excel2013可到微軟官方下載相應的外掛)的基礎知識,來針對不同的情況靈活使用即可。
一、除了數字和字母、漢字,沒有太多雜亂字元的情況
這種日常工作中最常見使用Power Query非常簡單,只需要一個簡單的函式即可,如下所示:
二、存在雜亂字元的情況
也只按需增加要清除的雜亂字元(比如這裡的“=”號)即可,仍然是一個函式。
總的來說,從雜亂文字中提取數字,或者提取英文字母,或者提取中文等等,沒有固定的套路,即使是VBA,也得根據實際情況來靈活處理,而不可能有一個統一的萬試萬靈的解決方案。
因此,個人建議根據自己的實際情況,選擇一項學起來相對簡單易用,而又能靈活應對不同情況自己能調整適應的技能來解決實際問題。從這個角度來說,相對於Excel函式的神難組合公式,或VBA的高門檻,Power Query在很多情況下的確是一個很好的選擇。
【私信“材料”直接下載系列訓練材料】
【Excel必備基礎小動畫】
【60+函式彙總案例】
【資料透視基礎精選10篇】
【Power Query入門到實戰80篇】
【Power Pivot 基礎精選15篇】
我是大海,微軟認證Excel專家,企業簽約Power BI顧問
讓我們一起學習,共同進步!
【您的關注和轉發鑄就我前行的動力!謝謝支援!】
工作中有時會遇到從一串文字和數值混雜的字串中提取數值的需求,如果字串比較多而且經常變動,與其每次都手動提取數值,就不如寫好一個公式實現自動提取。當資料來源更新時,公式結果還能自動重新整理。
本文詳解使用VLOOKUP函式提取字串中的數值的方法。有文件其中的字串中包含的數值各式各樣,有整數也有一位小數、兩位和多位小數,還有百分比數值,使用公式都可以一次性批次提取(百分號提取出來預設按照小數形式顯示,可以設定格式改變顯示方式)。
首先給出陣列公式,在B2輸入以下陣列,按
=VLOOKUP(9E+307,MID(A2,MIN(IF(ISNUMBER(——MID(A2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2)
即可提取其中的數字。
另還有其他的方法可以實現。
可選用以下方法提取:
1、給出數字的起始位置和長度。示例公式:=——mid(a1,5,3) 公式結果為提取A1單元格中從第5位開始的3個數字。
2、提取某特定字串後的數字。示例公式:=-lookup(0,-mid(a1,find(“ABC”,a1)+LEN(“ABC”),row(1:99))) 公式結果為提取A1單元格中字串ABC之後的數字。
3、數字在一側其他則中文字元組成。示例公式:數字在前,公式=left(a1,2*len(a1)-lenb(a1));數字在後,公式=right(a1,2*len(a1)-lenb(a1))
4、沒有給出任何條件,只要求將單元格中的數字提出。通用公式=-lookup(0,-mid(a1,min(find(row(1:10)-1,a1&1/17)),row(1:99))) 按組合鍵ctrl+shift+enter結束公式。
5、B1輸入後向下填充
=LOOKUP(9E+307,——MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),ROW(INDIRECT(“1:”&LEN(A1)))))
提取字串中數值一般是LOOKUP或MAX等函式組合,用VLOOKUP函式提取字串中的數值,是一個比較複雜的問題,但是也不是做不到。重點是在構建VLOOKUP第二引數,並且要深入理解VLOOKUP函式的特性,尤其是模糊查詢的特性。
如題要求需要用VLOOKUP提到字串中的數值
參考結果如圖
請看具體公式
=VLOOKUP(9^9,——MID(A2,MIN(FIND(ROW($1:$9),A2&1/17)),ROW($1:$9)),1)
公式解析:
1、MIN(FIND(ROW($1:$9),A2&1/17)),定位單元格字串中第一個數字出現的位置;
2、MID(A2,MIN(FIND(ROW($1:$9),A2&1/17)),ROW($1:$9)),用MID函式從字串中第一個數值開始擷取1-9個長度的字元(具體可根據最大數字長度調整);
3、——MID(A2,MIN(FIND(ROW($1:$9),A2&1/17)),ROW($1:$9)),利用——將文字數字轉化為真實數值,以此構建VLOOKUP第二引數;
4、VLOOKUP(9^9,——MID(A2,MIN(FIND(ROW($1:$9),A2&1/17)),ROW($1:$9)),1),利用VLOOKUP模糊查詢的特性,提取出數值。
其實,用MAX來提字串的數值會更簡單,請看下圖示例:
我們可以看到用MAX明顯比用VLOOKUP函式公式顯得簡潔得多。
以上是對本問題的解答,希望對你有所幫助。
歡迎關注@Excel泥瓦匠,Excel學習,E路有你!
office2016之後的版本都可以用Ctrl+E智慧提取,無需用函式