麻辣家族討論版版 - Powered by Discuz! Board

標題: 比對兩欄資料，列出相同及不相同的值 [打印本頁]

作者: leiru 時間: 2010-8-27 11:09 標題: 比對兩欄資料，列出相同及不相同的值

請問如何比對兩欄資料,同時又列出相同及不同的資料

作者: Hsieh 時間: 2010-8-27 19:44

回復 1# leiru

試試看
[attach]2611[/attach]

作者: leiru 時間: 2010-8-27 23:48

謝謝Hsieh版主回覆.

作者: Hsieh 時間: 2010-9-22 23:12

回復 4# kaigan89

[attach]2849[/attach]

作者: oobird 時間: 2010-9-22 23:57

借用一下謝兄的文件。
陣列公式在低階的電腦如我的電腦開啟檔案就要一段時間，不妨參考一下進階篩選。

[attach]2850[/attach]

作者: toomellowhaw 時間: 2010-9-23 14:26

A and B
IF(ROW(1:26)>COUNT(MATCH(A2:A27,B2:B27,)),"",SMALL(IF(A2:A27*ISNUMBER(MATCH(A2:A27,B2:B27,)),A2:A27,""),ROW(1:26)))

only A
IF(ROW(1:26)>(COUNT(A2:A27)-COUNT(MATCH(A2:A27,B2:B27,))),"",SMALL(IF(A2:A27*ISNA(MATCH(A2:A27,B2:B27,)),A2:A27,""),ROW(1:26)))

only B
IF(ROW(1:26)>(COUNT(B2:B27)-COUNT(MATCH(B2:B27,A2:A27,))),"",SMALL(IF(B2:B27*ISNA(MATCH(B2:B27,A2:A$27,)),B2:B27,""),ROW(1:26)))

都是陣列公式，參考，請指教..
[attach]2852[/attach]

作者: toomellowhaw 時間: 2010-9-23 14:56

回復 4# Hsieh

研究了一下版主的公式，果然簡潔多了！佩服..
還有疑問要請教一下：
公式中ROW(A1)*2 ===> 為什麼要乘2？？
依樣畫葫蘆試試，結果出現這樣的符號 #NUM! ，不知道我是哪裡弄錯了？..

作者: Hsieh 時間: 2010-9-23 15:07

回復 7# toomellowhaw
因為ab同時存在所以數量只剩一半
若是a跟b相同會有2個以上就會出錯
所以還是進階篩選來的實用

作者: toomellowhaw 時間: 2010-9-23 20:47

回復 8# Hsieh

原來如此，感謝解說..

再來好好研究一下進階篩選..

作者: irenemak2007 時間: 2010-9-24 08:11

版主，因為我不能下載檔案，可否列出公式來看看是怎樣做？

作者: kaigan89 時間: 2010-9-24 11:23

回復 kaigan89
Hsieh 發表於 2010-9-22 23:12

謝謝超級版主熱心貼圖！
可惜圖放大之後還是不清楚
只能半猜半測
還是沒學會！可惜了一次好題材

作者: Luna 時間: 2010-9-24 14:57

[attach]2864[/attach]回復 1# leiru

如果這樣子呢??
[attach]2864[/attach]

作者: Hsieh 時間: 2010-9-24 15:08

回復 12# kaigan89

[attach]2865[/attach]

作者: Luna 時間: 2010-9-24 17:50

本帖最後由 Luna 於 2010-9-28 13:39 編輯

回復 1# leiru

不知怎在同一格顯示所以......我只會土方法....所以呢~~

土方法再一 :

=IF((A3=B3)*(A3=""),"AB都沒有",IF((A3=B3),"AB相同"&A3,IF((B3>A3>B3)*(A3=""),"相異B="&B3&","&"A沒有",IF((A3>B3>A3)*(B3=""),"相異A="&A3&","&"B沒有","相異 A"&B3&","&"B"&A3))))

[attach]2905[/attach]

作者: gong 時間: 2010-9-25 21:21

笨方法但我常用的提供參考
1.將a,b二欄合併貼c,
2.d=countif(c$1:c1,c1)=1(篩選1取不重覆值),
3.e=countif(a:a,c1)=0(a欄沒有的)
4.f=countif(b:b,c1)=0(b欄沒有的)

作者: kaigan89 時間: 2010-9-27 08:28

回復 leiru

如果這樣子呢??
Luna 發表於 2010-9-24 14:57

謝謝leiru貼圖
這樣就看得清楚了！也看懂了！

作者: kaigan89 時間: 2010-9-27 08:33

本帖最後由 kaigan89 於 2010-9-27 08:46 編輯

回復 kaigan89
Hsieh 發表於 2010-9-24 15:08

謝謝版大熱心貼圖，這回看清楚了！
請問：
篩選準則$A$1

A$2，但A1是空白的，代表什麼意思？為什麼可以是空白的？
測試一下，篩選得到的結果是A ，這結果是什麼意思？請指教謝謝

作者: Hsieh 時間: 2010-9-27 10:11

回復 17# kaigan89

EXCEL說明如下

將公式結果用作條件
您可以將公式的計算結果作為準則使用。在使用公式建立準則時，請不要將欄標籤作為準則標籤使用，而應該將準則標籤留空白，或使用範圍中不是欄標籤的標籤。

這就類似sql擷取計算結果資料做為新標籤欄位
留空白標籤則會自動以篩選欄位標籤做為新標籤
重要的是公式以上次圖示的h2公式
=COUNTIF($B$1:$B$335,A2)>0
這若是在一般公式被解讀成
b欄中等於a2的數量
excel很聰明會自動把a欄所有值去做計算
只要a欄中的值在b攔中的數量大於0
就是符合條件就會被篩選出來

作者: kaigan89 時間: 2010-9-27 14:55

本帖最後由 Hsieh 於 2010-9-27 15:13 編輯

[attach]2889[/attach]

感謝超級版大詳細解說！
我自行製作一檔試作
結果相同的資料欄位還是被篩選出來如圖
不知問題何在？
=COUNTIF($A$1:$A$365,A2)>0 其中第二引數A2是單指A2嗎？
還是另有用意？

作者: Hsieh 時間: 2010-9-27 15:30

=COUNTIF($A$1:$A$365,A2)>0
這樣a2:a365每個值都會符合條件
這跟你貼圖又不一樣
=COUNTIF($B$2:$B$74,A2)>0
公式意義是B欄中計算A攔值的個數大於0
也就是在B欄也同時出現的A欄的值
而你是針對A欄做篩選
那就是A跟B同時出現的值

作者: trainee 時間: 2010-9-28 23:04

回復 2# Hsieh

好用的例子。新丁已下載作學習之用！

經試用，[B有A無]、[AB都有]這兩項都無問題。
但[A有B無]就出現問題，煩請版主再賜教！

作者: Hsieh 時間: 2010-9-28 23:11

回復 21# trainee
你測試是甚麼問題?

作者: trainee 時間: 2010-9-29 07:56

回復 22# Hsieh

我於A1-A5順序輸入1-5，於B1-B5輸入3-7。
結果[A有B無]空白， [B有A無]顯示6及7，而[AB都有]顯示3、4及5。
理論上[A有B無]應顯示1及2才正確！？
懇請版主賜教！

作者: Hsieh 時間: 2010-9-29 08:08

回復 23# trainee

定義名稱打錯了
=SMALL(IF(COUNTIF(rng2,rng1)=0,rng1),ROW(A1))

作者: trainee 時間: 2010-9-29 09:46

回復 24# Hsieh

多謝版主賜教！

可以了，如此簡單，新丁亦不察覺，真的要多下功夫！

作者: dereko983 時間: 2012-6-6 15:51

@@剛好因為這個問題GOOGLE到這一帖
特地加入會員
雖然還不能下載，但先感謝版主的回應。

作者: yc1031 時間: 2012-6-7 00:43

回復 2# Hsieh

您好!

想請問, 假如, 我A欄的資料, 不見得是27個, 有可能是10個, 也有可能是100個, 那我該怎麼做呢? 感謝!
=INDEX(A:A,SMALL(IF(MATCH($A$1:$A$27,$A$1:$A$27,0)=ROW($1:$27),ROW($1:$27),4^8),ROW(1:1)))&""

作者: Hsieh 時間: 2012-6-7 06:54

回復 27# yc1031

http://forum.twbts.com/thread-1405-1-1.html

作者: ANGELAC 時間: 2012-12-28 12:41

:) thanks !!

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)