如何通過Excel的高級篩選功能選出不重複的單詞？

12-27

請問，如何通過Excel的「高級篩選」功能，把單詞A和單詞B的相同部分，從單詞A里篩選掉？
單詞A和單詞B整體有相同的，不要求必須同一行相同。比如單詞A，B5=a，單詞B，C6=a，但也算重複，就篩選掉。
數據鏈接：http://pan.baidu.com/s/1qWW3oSk

我查了很多資料，昨晚折騰了3個小時，各種關鍵詞檢索解決方案，都沒搞定，也想過使用檢查「重複」的方式，也沒成功。我自己手動挑選了一段時間，因為單詞較多，且容易出錯，就放棄這種方式了。

下面是我看到的一小段操作：

篩選的過程並不複雜，甚至連代碼都不用寫。可以將單詞導入到Excel表格中，利用Excel的高級篩選功能，將高頻詞作為列表區域，BNC前5000詞作為條件區域，反向篩選後就可以得到最終的高頻詞表了。

實在沒辦法了，折騰了好久，都沒搞定。可恥的做一次伸手黨，謝謝大家啦！:)
如果有知友願意，處理完，可以發我郵箱，謝謝！winneryoung@126.com

說實話，感覺以上的答案又笨重又不靈活，難道數據更新一次就要重複篩選一次。我用數組函數，完美而靈活地實現題主的需求。

源數據和結果如下。

在E2使用的數組函數如下，其中Col_A（B2:B8）和Col_B（C2:C8）是自定義的名稱或者數據塊：

=INDEX(Col_A,SMALL(IF(ISERROR(MATCH(Col_A,Col_B,0)),ROW(Col_A)-1),ROW(A1)))

邏輯如下：

如果在某個Col_A的元素在Col_B中能找到，則該元素被賦值為空，否則為所在的行數
利用Small函數，選出第幾小的行數
利用Index函數，指定所需的Col_A中的元素。

如果還不清楚，請考慮參見我搞的這個反饋活動：粉絲回饋：程序讓你更性感 - 數據冰山 - 知乎專欄。

————————————————————
更多文章關注我的專欄：數據冰山 - 知乎專欄

我理解為【單詞A】在【單詞B】里出現，則需要被標記。
那麼最簡單的方法就是用VLOOKUP
我只需要在D列添加【是否存在】，然後在D2里寫上公式=VLOOKUP(B2,C:C,1,FALSE)
結果如圖：

其中#N/A就是不存在的，其他就是存在的。以上是我想到的最直接的方法，也許會有更簡單的~（根據評論修改了幾個錯誤）

題主的問題可以歸納成一類常見的問題，即：

找出所有屬於 A 且不屬於 B 的項

解決這個問題常見的思路是

遍歷 A 中所有項
判斷其是否屬於 B
取所有不屬於 B 的項

在 Excel 中，想要遍歷一個集合最常用的辦法是

加輔助列
或者跑一段 for each 代碼

在之前幾位的答案中可以看到他們是如何實現並優化這個思路的。

那麼問題來了！

有沒有更加簡單一點，不加輔助列也不用寫代碼的辦法？

讓我們回到問題本身，如果把 A 和 B 看做集合，那麼我們所要求的就是：

① $A-B$

同學們注意！我要變形了！

② $A-B=A-(Acap B)$

等等，這不是變得更複雜了么…
咳咳，看起來表達式是變得更複雜了（雖然其實也是…）

但有的時候

A ∩ B 會比 B 更容易求！

比如題主的這個問題

在 Excel 中有個 Bug 般的求交集的辦法

那就是

條件格式中的突出顯示重複值

如果再配上按顏色篩選…

順著這個思路，我們看一下具體如何操作：

1、選擇 B 列和 C 列去重複值，保證集合本身沒有重複值

2、選擇 B、C 兩列使用條件格式中的突出顯示重複值

3、選擇 B 列按單元格顏色篩選，大功告成！

4、當然，有強迫症的同學也可以把結果複製到另一列再改個名字什麼的…

關注餅乾，少加點班

更多文章索引：餅乾的食用指北

謝謝 @卡卡卡卡邀請

回答問題前先來理一下題主的需求：

如何使用高級篩選把單詞A和單詞B的相同部分，從單詞A里篩選掉？單詞A和單詞B整體有相同的，不要求必須同一行相同。

根據你的描述，可以把問題描述成這樣：在單詞B（C列）中查找單詞A（B列），如果找到了（就是單詞A和單詞B相同的部分），就不篩選，沒找到就將其篩選出來。
因而就將問題轉換為「查找+篩選」，解決這類問題，有二種方法：
1、輔助列法。在輔助列使用vlookup查找，然後篩選出顯示為#N/A的值（就是沒查找到的值）
2、直接使用高級篩選。

下面分別介紹

方法一：輔助列法

略，具體方法參見 @靳偉的解決方案2
方法二：高級篩選法
既然題主問的是高級篩選法，自然要用高級篩選答題才符合題主要求。
高級篩選可以使用公式來設定篩選條件，此時有以下幾點要注意：

公式的計算結果必須計算為 TRUE 或 FALSE。如下圖E2單元格的值為為false。
不能將列標籤用作條件標籤，請將條件標籤保留為空，或者為不等於列標籤的其他文字。如E1單元格不能為「單詞A」。
用於創建條件的公式必須使用相對引用來引用第一行數據中的對應單元格。如E1單元格公式中的B2。
公式中的所有其他引用必須是絕對引用。如E1單元格公式中的$C$2:$C$5001。

了解這幾點後，我們就開始解答本題
步驟1：將E1：E2作為條件單元格區域。在E2單元格輸入篩選公式：

=ISNA(VLOOKUP(B2,$C$2:$C$5001,1,0))

步驟2：點擊「數據」選項卡「排序和篩選」組中的高級篩選按鈕，按下圖進行設置

如果勾選「在原有區域顯示篩選結果」就不用指定「複製到」的單元格區域。
步驟3：篩選後結果如下：

然後，將J列數據刪除即可。
當然，也可指定僅返回A列和B列欄位：
在H1輸入「詞頻」，I1輸入「單詞A」，然後在高級篩選對話框的「複製到」欄輸入：Sheet1!$H$1:$I$1，即可指定僅返回A列和B列欄位。

能實現. 你這個需求相當於跨表操作. 如果用資料庫的話非常容易實現. 但是用Excel公式的話, 老衲目前實現方式比較Ugly.
暫時標為解決方案1吧.
等有空再想解決方案2(based on vlookup? )

以下是分解步驟.
1. 添加兩列輔助列.

添加這兩列的原因是在單詞前後添加空格, 來區別eel和wheel這種不完全匹配的問題.

這樣的話"_the_"只能匹配上"00001_the_", 而不再能匹配上"00028_there_"

2. 自定義函數 ifContains(word, range). 請注意絕對引用.

自定義函數要打開"開發工具", 添加模塊, 輸入代碼

代碼如下

Public Function ifContains(aword, arange)


Dim index As Integer

index = 0
For Each cell In arange.Cells

    If (InStr(1, cell.Value, aword.Value) &> 0) Then

        index = index + 1

    End If
Next
ifContains = index

End Function

這段代碼的意思也很簡單, 就是看看"單詞A2"裡面的"_the_", 是否能在"單詞B2"這一列中找到個什麼"00001_the_", 或者"8987_the_". 如果能找到就記錄一下找到的次數. 找不到自然是0次了.

3. 等幾秒鐘完成, 然後對 ifContains列進行過濾操作, 挑出來其中等於0的(找到了0次).