在 Excel 當(dāng)中,有一項(xiàng)功能很有意思,它的名稱叫【分列】,但它能做的事情可不僅僅只是分個(gè)列,有些看上去和分列功能八竿子打不著關(guān)系的疑難問(wèn)題,也可以用分列來(lái)解決。
如果要提名 Excel 當(dāng)中最喜歡管閑事的功能,那就非它莫屬了!
Excel 中的【分列】功能位于【數(shù)據(jù)】選項(xiàng)卡下「數(shù)據(jù)工具」組中,如下圖所示。
當(dāng)將鼠標(biāo)移動(dòng)懸停到【分列】命令時(shí),會(huì)有該命令的提示說(shuō)明。
分列:將單列文本拆分為多列。
例如,您可以將全名列分隔成單獨(dú)的名字列和姓氏列。
您可以選擇拆分方式:固定寬度或者在各個(gè)逗號(hào)、句點(diǎn)或其他字符處進(jìn)行拆分。
可能你還不熟悉該命令的提示說(shuō)明,所以一起先來(lái)看看它的基本用法吧。
01
按照分隔符將單列數(shù)據(jù)分隔成多列
分列的最基本用法就是將一列以固定分隔符分隔的數(shù)據(jù)進(jìn)行分列,比如下面 A 列數(shù)據(jù)是以 \ 分隔符分隔的,我們可以用分列功能將其按照 \ 分隔符分成多列。
操作步驟
? 先選中要分列的數(shù)據(jù),在【數(shù)據(jù)】菜單欄下選擇【分列】,默認(rèn)選擇「分隔符號(hào)」,然后點(diǎn)擊【下一步】。
? 勾選「其他」,在后面的輸入框中鍵入 \ ,再點(diǎn)擊【下一步】。
? 默認(rèn)設(shè)置,點(diǎn)擊【完成】。
02
按照固定寬度分列
我們都知道,身份證號(hào)的第 7 位到第 14 位數(shù)字是出生日期。
那么如果我們需要從一段 18 位的身份證號(hào)中提取出生日期,也可以利用分列功能快速實(shí)現(xiàn)。
如果有一列身份證號(hào)碼,提取出生日期可以按照固定寬度對(duì)身份證號(hào)碼分列。
操作步驟
? 先選中數(shù)據(jù)列,在【數(shù)據(jù)】菜單欄下選擇【分列】,選擇「固定寬度」,點(diǎn)擊【下一步】。
? 在「數(shù)據(jù)預(yù)覽」下建立分列線,具體操作辦法上面有提示,這里是在第 6 位數(shù)字和第 7 位數(shù)字間以及第 14 位數(shù)字和第 15 位數(shù)字間建立了分列線,然后點(diǎn)擊【下一步】。
? 在我們畫(huà)好線的這三列中,對(duì)于我們不要的第一列和第三列,在「數(shù)據(jù)預(yù)覽」中分別選中它們,然后勾選上面的「不導(dǎo)入此列(跳過(guò))」。
? 選中中間的出生日期列,勾選上面的「日期」,在「目標(biāo)區(qū)域」里選擇要生成新列的首個(gè)單元格位置,點(diǎn)擊【完成】。
好啦,以上介紹的都是分列的基本用法,在 Excel 長(zhǎng)期的發(fā)展過(guò)程中,眾多 Excel 高手還挖掘出了分列的很多另類(lèi)用法,用于解決某類(lèi)特殊的問(wèn)題,可以起到意想不到的效果,下面跟我一起看看吧~
03
用分列解決 SUM 函數(shù)結(jié)果總是為 0 的問(wèn)題
當(dāng)數(shù)字以文本形式存儲(chǔ)時(shí),求和公式無(wú)法對(duì)其進(jìn)行求和,結(jié)果總是顯示 0。這時(shí)候可以用分列功能快速地將文本數(shù)字轉(zhuǎn)換為以數(shù)值存儲(chǔ)的形式,這樣 SUM 函數(shù)就可以計(jì)算出正確的結(jié)果。
04
用分列解決 VLOOKUP 函數(shù)返回#N/A 的問(wèn)題
18 位身份證號(hào)碼的前 6 位數(shù)字對(duì)應(yīng)的是我國(guó)的行政區(qū)劃代碼,如下圖所示,用公式查找對(duì)應(yīng)的行政區(qū)劃名稱時(shí),結(jié)果卻返回#N/A。
我們輸入的公式為
=VLOOKUP(LEFT(C2,6),A:B,2,0)
C2 單元格內(nèi)的身份證號(hào)碼的前 6 位是 110101,對(duì)應(yīng)的明明是 A3 單元格的行政區(qū)劃代碼,可結(jié)果卻找不到,返回#N/A。
這是因?yàn)?A 列的行政區(qū)劃代碼是以數(shù)值形式存儲(chǔ)的,而 LEFT(C2,6)函數(shù)返回的是文本形式的 110101,VLOOKUP 函數(shù)查找時(shí)按照對(duì)應(yīng)的格式查找,即數(shù)值格式來(lái)查找,沒(méi)有找到文本形式的 110101,所以返回#N/A。
想解決這個(gè)問(wèn)題,也可以用分列功能快速地將 A 列以數(shù)值形式存儲(chǔ)的內(nèi)容轉(zhuǎn)換為以文本形式存儲(chǔ)。轉(zhuǎn)換之后,VLOOKUP 函數(shù)就能按照對(duì)應(yīng)的格式查找到對(duì)應(yīng)的結(jié)果了。
當(dāng)然,除了使用分列功能,我們也可以通過(guò)修改函數(shù)公式為 1*LEFT(C2,6),強(qiáng)制將 LEFT(C2,6)函數(shù)返回的文本數(shù)據(jù)轉(zhuǎn)換為數(shù)值形式,從而查找到正確的值。
05
用分列解決日期、時(shí)間格式無(wú)法識(shí)別的問(wèn)題
在創(chuàng)建含有日期字段的數(shù)據(jù)透視表時(shí),數(shù)據(jù)表面上看上去都是日期,但是卻無(wú)法將日期字段分組。如下圖所示,【分組字段】命令是灰色的不可選。
遇到這種情況,我們也可以搬出分列功能,將日期字段列進(jìn)行分列。
同時(shí)設(shè)置分列后的格式為日期格式,再對(duì)數(shù)據(jù)透視表進(jìn)行刷新,就可以用【分組字段】功能了。
看到了沒(méi),在各種不同的場(chǎng)合靈活地運(yùn)用分列功能,可以化繁為簡(jiǎn)。
下次,如果再遇到格式問(wèn)題,不妨用用分列功能。
小 E 來(lái)幫大家總結(jié)一下這個(gè)愛(ài)管閑事的【分列】都可以做什么吧~
? 按照分隔符號(hào)進(jìn)行數(shù)據(jù)拆分
? 按照固定寬度進(jìn)行數(shù)據(jù)拆分
? 規(guī)范數(shù)據(jù)格式
比如:用分列將以文本存儲(chǔ)的內(nèi)容轉(zhuǎn)換為以數(shù)值存儲(chǔ)
用分列將以數(shù)值存儲(chǔ)的內(nèi)容轉(zhuǎn)換為以文本存儲(chǔ)
用分列將以文本存儲(chǔ)的日期轉(zhuǎn)換為以日期格式存儲(chǔ)
先別急著走,你如果喜歡小 E,請(qǐng)按下面步驟把小 E 設(shè)置為★星標(biāo)訂閱號(hào)吧!
秋葉 Excel
◆ ◆ ◆
在秋葉 Excel 中,我們特意制作了「精華文章分類(lèi)寶典」供您查閱。寶典分類(lèi)里,有近百篇詳盡的教學(xué)文章,隨時(shí)隨地為你解決問(wèn)題。
進(jìn)入公眾號(hào),點(diǎn)擊菜單欄中的【快速學(xué)習(xí)】,就能找到它啦。
作者:Excel 研究院-水星釣魚(yú)
聯(lián)系客服