想要優(yōu)雅的Excel數(shù)據(jù)去重，還得是unique函數(shù)
「如何將數(shù)據(jù)中的重復(fù)數(shù)據(jù)去除，只保留或提取不重復(fù)的數(shù)據(jù)，該怎么操作呢？」
本期用3種方法來(lái)解決上面的問(wèn)題。
手動(dòng)操作去重提取
unique高級(jí)數(shù)組函數(shù)一步提取
普通數(shù)組函數(shù)提?。芍苯犹子霉剑?/p>
點(diǎn)贊收藏，根據(jù)你的需要選擇合適的方法。
問(wèn)題：需求
例如下圖的數(shù)據(jù)表，最下方存在兩條重復(fù)數(shù)據(jù)，同時(shí)也存在一個(gè)客戶(hù)有多筆訂單的情況。
需求有三個(gè)：
將數(shù)據(jù)去重，只保留不重復(fù)的數(shù)據(jù)；
提取出去重后的客戶(hù)姓名；
提取只出現(xiàn)過(guò)一次的客戶(hù)姓名；
1.軟件內(nèi)置刪除重復(fù)值操作
復(fù)制原數(shù)據(jù)到新的一張表中，選中所有數(shù)據(jù)。
在數(shù)據(jù)選項(xiàng)卡找到刪除重復(fù)值按鈕。
在彈出的窗口中，數(shù)據(jù)有標(biāo)題記得勾選標(biāo)題，下方的三列選項(xiàng)，全部勾上，代表只有當(dāng)三列數(shù)據(jù)都完全一致，才算重復(fù)項(xiàng)。
點(diǎn)擊確認(rèn)刪除后，會(huì)發(fā)現(xiàn)雖然存在多個(gè)重復(fù)姓名，但是只有一個(gè)三列全部重復(fù)的數(shù)據(jù)被刪除了。
如果想要去重客戶(hù)姓名，則可以「只勾選客戶(hù)姓名」，最終結(jié)果如下圖所示。
此時(shí)的訂單號(hào)和消費(fèi)金額已經(jīng)沒(méi)有意義了，可以刪除。
如果想要提取只出現(xiàn)過(guò)一次的客戶(hù)姓名，則可以使用輔助列方法。
在原數(shù)據(jù)右側(cè)添加輔助公式列，公式向下填充，統(tǒng)計(jì)姓名出現(xiàn)次數(shù)
=COUNTIF($B$2:$B$8,B2)
統(tǒng)計(jì)客戶(hù)姓名出現(xiàn)次數(shù)后篩選提取次數(shù)為1的數(shù)據(jù)。
提示：CTRL+SHIFT+L可以快速開(kāi)關(guān)篩選
通過(guò)輔助列，你可實(shí)現(xiàn)各種亂七八糟的去重操作，比如將所有列數(shù)據(jù)用&鏈接成一個(gè)字符串，然后統(tǒng)計(jì)篩選去重。
不過(guò)，上面的方法，怎么看都感覺(jué)不夠優(yōu)雅！
如果你的軟件版本帶有unique函數(shù)，那上面的操作，只需幾行函數(shù)公式就搞定了。
2.unique去重函數(shù)
Microsoft 365，Excel 2021以及WPS最新版支持該函數(shù)
UNIQUE函數(shù)作用，就是返回列表或范圍中的一系列唯一值。
參數(shù)如下：
=UNIQUE (array，[by_col]，[exactly_once])
一共三個(gè)參數(shù)，通常情況下，你不需要做特別設(shè)置。
比如需求1中，將所有數(shù)據(jù)按行去重。
如果你是Microsoft 365用戶(hù)，直接輸入公式：
=UNIQUE(A1:C8)
回車(chē)就可以得到去重后的結(jié)果。
如果是WPS，需要按照普通數(shù)組公式使用方法。
先選擇承載去重后數(shù)據(jù)的空單元格區(qū)域。
也就是E1:G7單元格，這個(gè)選擇范圍，是根據(jù)去重后的數(shù)據(jù)大小確認(rèn)，可以比最終的數(shù)據(jù)范圍大，但不能小，否則會(huì)顯示不全數(shù)據(jù)。
再輸入上面的公式。
最后按數(shù)組確認(rèn)鍵CTRL+SHIFT+回車(chē)確認(rèn)數(shù)組公式。
需求2要求提取去重姓名，只需要將函數(shù)的第一個(gè)參數(shù)array，也就是待去重的數(shù)據(jù)區(qū)域，縮小到姓名區(qū)域即可，公式如下：
=UNIQUE(B1:B8)
需求3要求提取只出現(xiàn)過(guò)一次的客戶(hù)姓名，將unique函數(shù)的第三個(gè)參數(shù)改成1，即可提取恰好出現(xiàn)過(guò)一次的數(shù)據(jù)，公式如下：
=UNIQUE(B1:B8,,1)
如果你的軟件里沒(méi)有unique函數(shù)，也由于某些原因，無(wú)法更新軟件，可以試試下面的普通數(shù)組公式法來(lái)代替unique，實(shí)現(xiàn)部分相同的功能。
3.普通數(shù)組公式代替unique去重單列數(shù)據(jù)
例如將客戶(hù)姓名列數(shù)據(jù)進(jìn)行去重，完整的公式如下：
=INDEX($B$2:$B$8,SMALL(IF(MATCH($B$2:$B$8,$B$2:$B$8,0)+(ROW($B$2)-1)=ROW($B$2:$B$8),ROW($B$2:$B$8)-(ROW($B$2)-1)),ROW(INDIRECT("1:"&COUNT(IF(MATCH($B$2:$B$8,$B$2:$B$8,0)+(ROW($B$2)-1)=ROW($B$2:$B$8),ROW($B$2:$B$8)-(ROW($B$2)-1)))))))將公式中的下方數(shù)據(jù)，替換為自己表格中的實(shí)際待去重區(qū)域地址即可
$B$2:$B$8為待去重區(qū)域單元格引用地址；
$B$2為待去重區(qū)域「第一個(gè)單元格」的引用地址；
WPS請(qǐng)按照CSE數(shù)組公式輸入三步驟（上文寫(xiě)過(guò)），來(lái)錄入公式。
完成結(jié)果如圖：
看到這么長(zhǎng)的數(shù)組公式，不用慌，分析清楚結(jié)構(gòu)后，你就能直接替換其中的數(shù)據(jù)范圍，自己編寫(xiě)出適合自己數(shù)據(jù)的公式。
3.1 普通數(shù)組函數(shù)公式解析
公式的核心就是通過(guò)index函數(shù)，從給定的數(shù)據(jù)范圍里，按照給定的位置數(shù)組，輸出對(duì)應(yīng)數(shù)據(jù)范圍的數(shù)據(jù)。
「基本公式index提取數(shù)據(jù)」
例如=index(B2:B8,{1;2;3})就會(huì)返回B2:B8區(qū)域的第1、2和3位的數(shù)據(jù)。
那現(xiàn)在最關(guān)鍵的就是如何計(jì)算得出{1;2;3}這個(gè)位置數(shù)組，來(lái)告訴index提取這三個(gè)位置的數(shù)據(jù)。
「match函數(shù)查找每個(gè)數(shù)據(jù)位置」
我們使用match函數(shù)，用來(lái)查找指定數(shù)據(jù)在數(shù)據(jù)范圍中的位置數(shù)據(jù)。也就是MATCH($B$2:$B$8,$B$2:$B$8,0),得出結(jié)果為{1;2;3;1;2;1;1}。
通過(guò)選中整個(gè)查找區(qū)域到整個(gè)查找區(qū)域去匹配，我們就能得到，每一個(gè)客戶(hù)姓名，首次出現(xiàn)在查找區(qū)域的位置。
從數(shù)字上，我們就能夠看出，出現(xiàn)重復(fù)數(shù)字的就是存在重復(fù)情況。
「使用if函數(shù)判斷是否首次出現(xiàn)」
使用ROW($B$2:$B$8)，我們能夠得到客戶(hù)姓名數(shù)據(jù)的每一行位置數(shù)據(jù)，也就是{2;3;4;5;6;7;8}。
如果match函數(shù)查找到的數(shù)據(jù)位置與row函數(shù)本身的數(shù)據(jù)位置一致，則說(shuō)明，該行數(shù)據(jù)是首次出現(xiàn)，否則說(shuō)明前面已經(jīng)出現(xiàn)過(guò)了。
需要注意的是，由于客戶(hù)姓名所在區(qū)域是從B2開(kāi)始的，這導(dǎo)致row(B2)返回的值也是從2開(kāi)始。
而match函數(shù)返回的位置數(shù)據(jù)，是相對(duì)于查找數(shù)據(jù)范圍從1開(kāi)始計(jì)數(shù)，因此我們需要給match函數(shù)加上這個(gè)相對(duì)位置差。
這個(gè)相對(duì)位置差的計(jì)算方法由選擇的客戶(hù)姓名數(shù)據(jù)范圍的第一個(gè)單元格位置$B$2減1得到，也就是ROW($B$2)-1，一定要添加$絕對(duì)引用。
最終if函數(shù)的條件式就寫(xiě)好了：MATCH($B$2:$B$8,$B$2:$B$8,0)+(ROW($B$2)-1)=ROW($B$2:$B$8)。
如果成立，我們就返回對(duì)應(yīng)的位置數(shù)，你可以寫(xiě)成MATCH($B$2:$B$8,$B$2:$B$8,0)，也可以寫(xiě)成ROW($B$2:$B$8)-(ROW($B$2)-1)。
如果不成立，則說(shuō)明前面已經(jīng)出現(xiàn)過(guò)這個(gè)姓名數(shù)據(jù)了，就默認(rèn)返回False即可。
完整的if函數(shù)部分公式如下：IF(MATCH($B$2:$B$8,$B$2:$B$8,0)+(ROW($B$2)-1)=ROW($B$2:$B$8),ROW($B$2:$B$8)-(ROW($B$2)-1))
該部分公式計(jì)算結(jié)果為：{1;2;3;FALSE;FALSE;FALSE;FALSE}
「使用small函數(shù)，依次提取最小的數(shù)字」
我們通過(guò)if函數(shù)得到了去重后的位置數(shù)組{1;2;3;FALSE;FALSE;FALSE;FALSE}，現(xiàn)在只要使用small函數(shù)去提取出其中的{1;2;3}，就完成了整個(gè)公式。
small函數(shù)一共有2個(gè)參數(shù)，第一個(gè)是數(shù)組，第二個(gè)是要提取第幾位最小值。
我們這里需要提取第1、2和3位最小值，其中false不參與比較，可以把第二個(gè)參數(shù)寫(xiě)成{1;2;3}數(shù)組帶入，一次性返回small函數(shù)提取的值。
公式如下：small({1;2;3;FALSE;FALSE;FALSE;FALSE},{1;2;3})
那問(wèn)題的關(guān)鍵就是如何生成{1;2;3}這種序列數(shù)組，而且剛好生成3個(gè)。
想要了解，序列函數(shù)技巧，可以參考Sequence等差序列函數(shù)這篇文章。
??
使用row(1:3),就可以直接生成{1;2;3}，但是如果表格被刪除了1到3行中的任意行，函數(shù)會(huì)報(bào)錯(cuò)。
所以我們可以使用indirect函數(shù)，把原本的1:3行地址引用改成文本的indirect("1:3")，這樣外界的操作就不會(huì)影響函數(shù)公式，最終的公式為ROW(INDIRECT("1:3"))。
那么如何指定生成多少個(gè)呢？
我們可以使用count函數(shù)去統(tǒng)計(jì)前面if函數(shù)的結(jié)果中，數(shù)字的部分有多少個(gè)，就能直接獲得需要的個(gè)數(shù)。
所以，別猶豫，直接復(fù)制前面的if函數(shù)公式部分，再用count函數(shù)嵌套后，替換掉ROW(INDIRECT("1:3"))這里的3，注意文本和公式拼接要使用&符號(hào)。
那最終small函數(shù)的第二個(gè)參數(shù)，需求生成{1;2;3}的函數(shù)公式就是ROW(INDIRECT("1:"&COUNT(IF(MATCH($B$2:$B$8,$B$2:$B$8,0)+(ROW($B$2)-1)=ROW($B$2:$B$8),ROW($B$2:$B$8)-(ROW($B$2)-1)))))
別看這部分這么長(zhǎng)，其實(shí)count函數(shù)里的公式都是前面寫(xiě)過(guò)的。 最后完整的公式就成功出爐：=INDEX($B$2:$B$8, SMALL( IF( MATCH($B$2:$B$8,$B$2:$B$8,0)+(ROW($B$2)-1)=ROW($B$2:$B$8),ROW($B$2:$B$8)-(ROW($B$2)-1) ), ROW(INDIRECT("1:"&COUNT(IF(MATCH($B$2:$B$8,$B$2:$B$8,0)+(ROW($B$2)-1)=ROW($B$2:$B$8),ROW($B$2:$B$8)-(ROW($B$2)-1))))) ) )
3.2那么，如何實(shí)現(xiàn)需求3中的，只提取恰好出現(xiàn)1次的數(shù)據(jù)呢？
函數(shù)公式如下：
=INDEX($B$2:$B$8,SMALL(IF(COUNTIF($B$2:$B$8,$B$2:$B$8)=1,ROW($B$2:$B$8)-(ROW($B$2)-1)),ROW(INDIRECT("1:"&COUNT(IF(COUNTIF($B$2:$B$8,$B$2:$B$8)=1,ROW($B$2:$B$8)-(ROW($B$2)-1)))))))關(guān)鍵的不同點(diǎn)，在于把match函數(shù)，改成了countif函數(shù)，由查找位置數(shù)，變成了統(tǒng)計(jì)出現(xiàn)次數(shù)，并判斷是否次數(shù)等于1。
最后如果還有相關(guān)問(wèn)題，歡迎在評(píng)論區(qū)留言討論，或在以溪同學(xué)好眾工回復(fù)「群」加入Excel討論~
你學(xué)會(huì)了嗎？
