從數(shù)據(jù)列中提取連續(xù)數(shù)的起始和終止值的最簡(jiǎn)便方法
?
作者:老菜鳥(niǎo)來(lái)源:部落窩教育發(fā)布時(shí)間:2020-05-25 15:34:52點(diǎn)擊:2884
編按:
哈嘍,大家好!怎么從一列數(shù)據(jù)中找出連續(xù)數(shù)并提取連續(xù)數(shù)的起始值和終止值?今天給大家分享一個(gè)最簡(jiǎn)單的、通過(guò)輔助列來(lái)解決這個(gè)問(wèn)題的方法。我們將建立兩個(gè)輔助列找到各段連續(xù)數(shù)的起始和終止值,然后用常用函數(shù)index、match把它們分列顯示出來(lái)。趕緊來(lái)看看吧!
近日在答疑群看到一個(gè)整理編號(hào)的問(wèn)題,覺(jué)得非常經(jīng)典,特分享給大家。
問(wèn)題如下圖所示:
數(shù)據(jù)源為A列,為了方便后面介紹,這里虛擬了23條數(shù)據(jù),實(shí)際上可能是成千上萬(wàn)條數(shù)據(jù)?,F(xiàn)在的問(wèn)題是需要將這一列卡號(hào)按照連續(xù)性整理成號(hào)段的形式,也就是C列和D列的結(jié)果,為了便于大家看明白,特意標(biāo)注了不同的顏色。
問(wèn)題明白了就該想想解決辦法,這個(gè)問(wèn)題似乎很有規(guī)律,但是又有點(diǎn)無(wú)從下手的感覺(jué),于是各種聲音都出現(xiàn)了,有說(shuō)用VBA的,有說(shuō)用PQ的,但是今天老菜鳥(niǎo)要和大家分享的辦法就是用函數(shù)公式,而且是幾個(gè)非?;A(chǔ)的函數(shù),只不過(guò)還用了兩個(gè)輔助列,下面就來(lái)分享解決思路。
要解決這個(gè)問(wèn)題需要做兩件事,第一是確定分段節(jié)點(diǎn),也就是找到號(hào)碼不連續(xù)的位置。第二就是分組,把連續(xù)的號(hào)碼視為一組。當(dāng)解決了這兩個(gè)問(wèn)題之后,最后的結(jié)果就非常容易得到了。輔助列1解決分段節(jié)點(diǎn)的問(wèn)題,公式為:
=IFERROR(IF(RIGHT(A2,4)-RIGHT(A1,4)<>1,ROW(),),1)
因?yàn)榭ㄌ?hào)是包含了字母的字符串,要判斷是否連續(xù)只能通過(guò)右邊的幾位數(shù)字來(lái)確定,也就是RIGHT(A2,4)-RIGHT(A1,4)。
結(jié)果為1時(shí),說(shuō)明是連續(xù)的,為了讓結(jié)果更加直觀,需要加個(gè)IF函數(shù)處理一下,于是就有了IF(RIGHT(A2,4)-RIGHT(A1,4)<>1,ROW(),)。
注意到D2中錯(cuò)誤值,是因?yàn)閿?shù)據(jù)源中的第一行是文字而造成的,要處理這個(gè)問(wèn)題很簡(jiǎn)單,再加一個(gè)IFERROR就可以了,將錯(cuò)誤值修正為1,這就是輔助列1的完整思路。
接下來(lái)要處理的就是分組問(wèn)題,有了輔助列1,這個(gè)問(wèn)題就很容易解決,公式為:
=COUNTIF($B$1:B2,">0")
COUNTIF也是比較常用的一個(gè)函數(shù)了,這個(gè)地方就是實(shí)現(xiàn)對(duì)大于零的數(shù)字進(jìn)行計(jì)數(shù),從而達(dá)到分組的效果。
有了這兩個(gè)輔助列,最后再來(lái)介紹起始編號(hào)和終止編號(hào)是如何得到的。
起始編號(hào)的確定其實(shí)就是一個(gè)反向查找的問(wèn)題,第一個(gè)起始編號(hào)就是輔助列2中數(shù)字1所對(duì)應(yīng)的編號(hào),第二個(gè)起始編號(hào)就是輔助列2中數(shù)字2所對(duì)應(yīng)的編號(hào),以此類(lèi)推。
明白了這一點(diǎn)之后,起始編號(hào)的公式就會(huì)有很多種寫(xiě)法,老菜鳥(niǎo)使用了INDEX和MATCH這對(duì)黃金搭檔組合,公式為:
=INDEX(A:A,MATCH(ROW(A1),C:C,))
終止編號(hào)的公式與起始編號(hào)的公式僅有一字之差,公式為:
=INDEX(A:A,MATCH(ROW(A1),C:C,1))
區(qū)別就在于MATCH函數(shù)的第三參數(shù),在計(jì)算起始編號(hào)的公式中省略了第三參數(shù),表示0,含義為精確查找,而在計(jì)算終止編號(hào)的公式中第三參數(shù)為1,含義為升序的模糊查找。關(guān)于這一點(diǎn),在之前講解的MATCH函數(shù)教程中都有介紹,這里只簡(jiǎn)單說(shuō)一下區(qū)別,如果查找值存在多個(gè)時(shí),精確查找返回第一個(gè)符合條件的結(jié)果,當(dāng)數(shù)據(jù)按升序排列時(shí),模糊查找返回最后一個(gè)符合條件的結(jié)果,在本例中,查找值是1、2、3這些數(shù)字,第一個(gè)符合條件的結(jié)果就是起始值,而最后一個(gè)符合條件的結(jié)果就是終止值。
結(jié)束語(yǔ):實(shí)際工作中遇到的很多問(wèn)題,如果要一步到位的話往往會(huì)很難,但是如果能夠發(fā)現(xiàn)解決問(wèn)題的關(guān)鍵步驟,并通過(guò)輔助列這個(gè)手段去拆解問(wèn)題的話,要比追求一步到位的解法更有效率。
本文配套的練習(xí)課件請(qǐng)加入QQ群:1043683754下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
Excel中的編號(hào)問(wèn)題《TEXT和SUMPRODUCT強(qiáng)強(qiáng)聯(lián)合,只為解決一個(gè)“微不足道”的編號(hào)問(wèn)題??》
Countifs函數(shù)解析《同樣是countifs函數(shù),為什么同事卻使得比你好?原因在這里!》
MATCH函數(shù)解析《MATCH:函數(shù)哲學(xué)家,找巨人做伴。新出道必學(xué)!》
INDEX函數(shù)解析《INDEX:函數(shù)中的精確制導(dǎo)導(dǎo)彈,最強(qiáng)大的瘸子》
最熱教程
- 像綠皮火車(chē)一樣長(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ù)