1. 首先調(diào)出"開(kāi)發(fā)工具"這個(gè)菜單. 在選項(xiàng)里面.
<noscript>
</noscript>
2. 點(diǎn)開(kāi)VBA, 進(jìn)入VBA界面
<noscript>
</noscript>
3. 插入一個(gè)模塊, 處女座可以對(duì)這個(gè)模塊起個(gè)好名字. 老衲是水瓶座, 那就哈哈哈哈啦.
<noscript>
</noscript>
4. Coding: 時(shí)間緊就不逐一細(xì)說(shuō)了. 還是非常簡(jiǎn)單的. 老衲的注釋率超過(guò)20%啦～
Public Function deduplicate(duplicateWords As String)
'declaim some vars
Dim wArray As Variant
'Split the long string and Write in and deduplicate with dictionary
wArray = Split(duplicateWords, ",")
Set dic = CreateObject("scripting.dictionary")
For i = 0 To UBound(wArray)
dic(Trim(wArray(i))) = "" 'Trim the string in case of irragular text
Next
'Reconstruct the long string
Dim result As String
For Each wItem In dic
result = result + "," + wItem
Next
deduplicate = Right(result, Len(result) - 1) 'remove the extra comma and return
End Function
5. 這時(shí)候你已經(jīng)有一個(gè)新公式了
<noscript>
</noscript>
6. 這個(gè)函數(shù)只需要把左邊的列選中, 然后拖動(dòng)一下, 問(wèn)題解決. 完美~~
<noscript>
</noscript>
如果還有更多問(wèn)題, 請(qǐng)關(guān)注老衲在知乎的專(zhuān)欄~~從點(diǎn)滴開(kāi)始做更好的自己 - 知乎專(zhuān)欄
?贊同 35 ? ?16 條評(píng)論
?分享
?收藏 ?喜歡
?
收起?
<meta itemprop="name" content="黃晨"><meta itemprop="image" content="https://pic4.zhimg.com/v2-20a25efce4b05a573a67c7e9d3be4635_is.jpg"><meta itemprop="url" content="https://www.zhihu.com/people/huangchen1031"><meta itemprop="zhihu:followerCount" content="2265">
不答題/不回信/不會(huì)Excel
1 人贊同了該回答
<meta itemprop="image"><meta itemprop="upvoteCount" content="1"><meta itemprop="url" content="https://www.zhihu.com/question/51729819/answer/127369448"><meta itemprop="dateCreated" content="2016-10-19T09:53:04.000Z"><meta itemprop="dateModified" content="2016-10-20T02:10:19.000Z"><meta itemprop="commentCount" content="3">
簡(jiǎn)單寫(xiě)了下，利用字典去重
Function duplicate_removal(ByVal str As String, ByVal sign As String)
Dim strArr, strDic
Set strDic = CreateObject("Scripting.Dictionary")
strArr = Split(str, sign)
For i = 1 To UBound(strArr)
strDic.Item(strArr(i)) = strArr(i)
Next i
For Each Key In strDic
duplicate_removal = duplicate_removal + strDic(Key) + sign
Next
duplicate_removal = Left(duplicate_removal, Len(duplicate_removal) - Len(sign))
End Function
使用例子：
對(duì)A1單元格去重，分隔符是中文逗號(hào)
=duplicate_removal(A1,"，")
