如何判定和標(biāo)記連續(xù)出現(xiàn)多次的數(shù)據(jù)?用TEXTJOIN!
?
作者:ITFANS來源:部落窩教育發(fā)布時(shí)間:2023-03-09 14:14:03點(diǎn)擊:1664
編按:
TEXTJOIN 函數(shù)可以根據(jù)需要添加特定分隔符并連接字符串,配合其他函數(shù)可以解決很多問題。今天介紹3種TEXTJOIN 函數(shù)的經(jīng)典功能。譬如,查詢是否有連續(xù)多次出現(xiàn)的數(shù)據(jù)。這里的重點(diǎn)是連續(xù)出現(xiàn),而非單純的重復(fù)出現(xiàn)。再譬如查找整行重復(fù)的數(shù)據(jù)、按條件合并同類數(shù)據(jù)到一個(gè)單元格中(相當(dāng)于一對(duì)多查找)等等。
Excel 2016開始具備TEXTJOIN函數(shù)。該函數(shù)基本用法就是把字符串連接起來并添加特定分隔符(也可以不添加)。配合其他函數(shù)如FIND、COUNTIF、IF等,可以做一些特別統(tǒng)計(jì),如查找整行相同的數(shù)據(jù)、查找連續(xù)出現(xiàn)多次的數(shù)據(jù)、合并同類數(shù)據(jù)到一個(gè)單元格(相當(dāng)于一對(duì)多查找)等等。
下面我們結(jié)合三個(gè)實(shí)例來介紹TEXTJOIN函數(shù)的經(jīng)典用法。
TEXTJOIN函數(shù)經(jīng)典用法1:查找連續(xù)出現(xiàn)多次的數(shù)據(jù)并標(biāo)記
如圖所示,員工連續(xù)兩次不合格,就需要添加警告標(biāo)記。
在B2輸入公式=IFERROR(IF(FIND("不合格/不合格",TEXTJOIN("/",,C2:J2))>1,"警告"),""),下拉填充即可。
函數(shù)解釋:
1.先使用TEXTJOIN函數(shù)將判定結(jié)果連接起來,將其作為FIND函數(shù)的查找區(qū)域。
2.將FIND函數(shù)查找結(jié)果作為IF函數(shù)的判斷條件,如果在其中找到“不合格/不合格”,即連續(xù)兩次判定結(jié)果(大家可以根據(jù)實(shí)際情況要改為其他字符如簽到,遲到等作為連續(xù)數(shù)據(jù)判斷),那么就顯示“警告”字樣。
3.如果未找到,默認(rèn)會(huì)顯示“#VALUE!”錯(cuò)誤值,在外層使用IFERROR函數(shù)強(qiáng)制顯示為空值。
同理,如果想查找連續(xù)出現(xiàn)三次及以上的數(shù)據(jù)并做標(biāo)記,都可以用此方法。
TEXTJOIN函數(shù)經(jīng)典用法2:快速找出整行重復(fù)的數(shù)據(jù)并刪除
比如下面表格,數(shù)據(jù)是多個(gè)員工分別收集最后整合在一起的?,F(xiàn)在需要將其中整行完全相同的重復(fù)數(shù)據(jù)找出來并刪除。大家先想一想,該怎么判斷整行重復(fù)?
1. 連接行數(shù)據(jù)
在G2單元格輸入公式=TEXTJOIN("/",,A2:F2),然后下拉填充,完成數(shù)據(jù)列的連接。這里將A2:F2的數(shù)據(jù)使用“/”分隔符連接起來,將其作為一個(gè)單元格的數(shù)據(jù)以方便后續(xù)的比較。
2. 根據(jù)逐次出現(xiàn)數(shù)據(jù)判定是否需要?jiǎng)h除
我們逐個(gè)統(tǒng)計(jì)G列中數(shù)據(jù)從上到下第幾次出現(xiàn),并將除第1次出現(xiàn)外的所有數(shù)據(jù)標(biāo)記為“重復(fù)出現(xiàn),需刪除”。
在H2單元格輸入公式=IF(COUNTIF($G$2:G2,G2)>1,"重復(fù)出現(xiàn),需刪除",""),下拉填充即可。
公式解釋:
COUNTIF函數(shù)的統(tǒng)計(jì)區(qū)域使用“$G$2:G2”格式,下拉后會(huì)依次變?yōu)椤?span>$G$2:G3”、“$G$2:G4”……形式,實(shí)現(xiàn)數(shù)據(jù)從上到下第幾次出現(xiàn)的統(tǒng)計(jì)。
3. 篩選數(shù)據(jù)并刪除
選中H列,點(diǎn)擊“數(shù)據(jù)→篩選”,為其添加篩選按鈕后。接著展開篩選選項(xiàng),勾選“重復(fù)出現(xiàn),需刪除”;接著選中篩選后的各行數(shù)據(jù),右擊選擇“刪除行”;最后再次切換到篩選,勾選“全選”,這樣就完成重復(fù)行的刪除操作了。
TEXTJOIN函數(shù)經(jīng)典用法3:按條件合并同類數(shù)據(jù)到一個(gè)單元格中(一對(duì)多查詢)
譬如下面,需要將同組的人員合并到一個(gè)單元格中。
在E2輸入公式=TEXTJOIN(",",,IF($A$2:$A$13=D2,$B$2:$B$13,""))下拉填充即可。(注意:如果版本低,公式輸入結(jié)束時(shí)需要按Ctrl+shift+Enter。)
函數(shù)解釋:
1. IF($A$2:$A$13=D2,$B$2:$B$13,"")部分是數(shù)組運(yùn)算,得到一個(gè)數(shù)組:一組成員數(shù)據(jù)和空值數(shù)據(jù)。
2. TEXTJOIN函數(shù)第2參數(shù)省略,表示忽略空值,如此將一組成員連接起來并添加了逗號(hào)分隔符。
CONCAT函數(shù)也可以實(shí)現(xiàn)該功能,具體可以查看《CONCAT函數(shù)經(jīng)典用法:將文字或者字符串語(yǔ)序顛倒》。
這種按條件合并同類數(shù)據(jù)的做法也可以理解為一對(duì)多查詢,將符合查詢條件的所有值在一個(gè)單元格中展示。
更多Excel實(shí)用教程,歡迎到部落窩教育了解學(xué)習(xí)。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
沒有Textjoin函數(shù),如何解決提取數(shù)據(jù)的問題?
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者ITFANS;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!