Excel腦洞大開(kāi):用99個(gè)空格來(lái)提取單元格數(shù)據(jù),你會(huì)嗎?
?
作者:老菜鳥(niǎo)來(lái)源:部落窩教育發(fā)布時(shí)間:2019-01-02 14:16:14點(diǎn)擊:3528
腦洞大開(kāi),提取單元格數(shù)據(jù)用99個(gè)空格就成!真想扒開(kāi)哪些大神的腦袋看看,是怎么相處這樣匪夷所思的方法的。
需要從規(guī)格型號(hào)中提取容值、封裝、耐壓三組數(shù)據(jù),如下:
數(shù)據(jù)源在A列,數(shù)據(jù)量很大,需要提取的容值、封裝和耐壓三項(xiàng)數(shù)據(jù)在單元格中的字節(jié)位置不固定。能找到的規(guī)律就是提取的數(shù)據(jù)分別位于數(shù)據(jù)源的第二、三、四個(gè)逗號(hào)之后。
當(dāng)我們遇到問(wèn)題的時(shí)候,找到規(guī)律是解決問(wèn)題的關(guān)鍵?,F(xiàn)在規(guī)律找到了,因此解決方法也就有了。這里有三種方法,從最簡(jiǎn)單的快捷鍵操作到腦洞大開(kāi)的空格替換法都有,以下分別進(jìn)行介紹。
1.快速填充法Ctrl+E
操作要點(diǎn):
(1)在B2單元格輸入0402時(shí),要先輸入一個(gè)單引號(hào),或者把單元格修改為文本格式再輸入;
(2)只輸入一個(gè)數(shù)據(jù)可能無(wú)法通過(guò)Ctrl+E得到正確結(jié)果,這時(shí)候連續(xù)輸入兩個(gè)數(shù)據(jù)就可以了。
提示:組合鍵Ctrl+ E只能在Excel2013及以上的版本才能使用。
就本例而言,Ctrl+ E略微顯得有些麻煩,因此再介紹一種用分列的處理方法。
2.分列法
操作要點(diǎn):
(1)分列過(guò)程中使用逗號(hào)進(jìn)行分隔;
(2)需要跳過(guò)不導(dǎo)入的列;
(3)對(duì)容值這列數(shù)據(jù)設(shè)置為文本格式;
(4)手工指定數(shù)據(jù)存放的目標(biāo)區(qū)域。
相比第一個(gè)方法來(lái)說(shuō),使用分列就簡(jiǎn)單了許多,同時(shí)通過(guò)這個(gè)例子,大家也可以對(duì)分列這個(gè)強(qiáng)大的功能有了深入的了解。
使用分列雖然比較方便,但如果經(jīng)常要處理這類數(shù)據(jù)的話,操作量也是蠻大的,最后我們?cè)賮?lái)分享一個(gè)公式的做法。
3. 腦洞大開(kāi)法:TRIM-MID-SUBSTITUTE-REPT組合公式
使用公式:=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),COLUMN(B1)*99,99))
右拉下拉即可得到所需的結(jié)果。
公式解析:
這個(gè)公式里用到了五個(gè)函數(shù),其中有我們比較熟悉的MID和COLUMN,也有我們不太常用的TRIM、SUBSTITUTE和REPT函數(shù)。下面簡(jiǎn)單來(lái)解釋一下這個(gè)公式的思路。
公式的核心部分是SUBSTITUTE($A2,",",REPT(" ",99)),這部分的作用是進(jìn)行替換。
SUBSTITUTE函數(shù)格式為:
SUBSTITUTE(在哪里替換,替換什么,換成什么,換第幾個(gè))
例如:
公式=SUBSTITUTE($A2,",","-",3)的效果就是把A2單元格的第3個(gè)逗號(hào)換成-號(hào)。
當(dāng)省略第四參數(shù)的時(shí)候,代表逗號(hào)全部替換,如圖:
本例中是把A2中的逗號(hào)換成了REPT(" ",99),也就是99個(gè)空格。
REPT函數(shù)的格式為:
REPT(要重復(fù)的字符,重復(fù)次數(shù))
例如:
REPT(“★”,5),就是將★重復(fù)五次。
至于公式中為什么要用99個(gè)空格,完全是一種套路,繼續(xù)看完公式的其他部分或許就理解了。
使用SUBSTITUTE得到的數(shù)據(jù)還需要用MID函數(shù)來(lái)進(jìn)行提取。MID函數(shù)大家應(yīng)該比較熟悉了,基本格式為:MID(數(shù)據(jù)源,從什么位置開(kāi)始取,取幾個(gè)字)。在本例中要提取的數(shù)據(jù)源就是SUBSTITUTE(),而要提取的容值的位置原本是在第2個(gè)逗號(hào)之后,由于我們把逗號(hào)換成了99個(gè)空格,要提取的位置前面至少有兩組空格也就是2*99個(gè)字符;相應(yīng)封裝的提取的位置是3*99,耐壓的是4*99。采用公式右拉,所以這里用COLUMN(B1)*99作為提取位置。MID的最后一個(gè)參數(shù)是要取幾個(gè)字符,為了保險(xiǎn)起見(jiàn),統(tǒng)一提取99個(gè)字。
也就是說(shuō),經(jīng)過(guò)MID(SUBSTITUTE(),COLUMN(B1)*99,99)這部分公式運(yùn)算后,得到的結(jié)果是我們實(shí)際需要的容值數(shù)據(jù)包含在前后空格中。為了便于大家理解,臨時(shí)將空格換成-,可以直觀地看出效果:
我們肯定不希望得到的結(jié)果中包含有大量無(wú)用的空格,因此在最外層套一個(gè)TRIM函數(shù)去掉這些空格。TRIM函數(shù)只有一個(gè)參數(shù),功能就是去掉字符串中多余的空格。
理解了這個(gè)公式嗎?為何要用99個(gè)空格呢?
本文配套的練習(xí)課件請(qǐng)加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
數(shù)據(jù)提取《 Excel如何提取數(shù)字?會(huì)這幾招就夠了》
數(shù)據(jù)轉(zhuǎn)置《 Excel數(shù)據(jù)處理常用技巧兩則:分列轉(zhuǎn)置》
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫(xiě)遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)