1 2 3 4 5 6

廣告

網友您好,若是您覺得我寫的工具或文章對您有幫助,
而您或者您的朋友有在博客來購物,可以透過底下連結

如何在博客來購物贊助網站或是點選計數器下方的博客來圖示來讓網站可以永續經營。

2008年11月13日 星期四

如何在Excel中大批篩選比對資料

Excel中有篩選功能,可是一次只能篩選一種條件,如果要多筆比對資料,大部分的人都會用Vlookup的功能,但Vlookup一次只能找出一欄的資料,如果很多欄位就很麻煩,所以我就寫了這一支自動比對的程式。
1.這是要處理前的資料 2. 到http://272586.blogspot.com/2007/04/blog-post_28.html,把相關資料copy上去 3. 按下大批篩選資料 4.篩選出來的資料在目的區,再把這些copy到Excel就完成了
PS:如果比對的欄位為負值,就代表是大批篩選去除資料(2009/12/3)

59 則留言:

Wen Hua 提到...

一整天大大您好:
我有一個excel工作表,範圍有30列,300欄,要進行前後兩欄資料比對,找出名稱相同者,不知道該如何處理,麻煩您了,非常感謝。
BILL

一整天 提到...

Wen Hua您好:
我會多一個欄位,裡面輸入您要判斷的欄位
,如=IF(A1=B1,"相同","不相同"),再依照
這個欄位來篩選相同的資料.

Wen Hua 提到...

一整天大大您好:
這個公式好像名稱必須同一列比對結果才會相同,希望找出只要在前期有出現就可以,也就是B1要比對A1至A30,B2也要比對A1至A30,直到該B欄最後一列儲存格B30比對A1至A30完成,比對的結果把相同的名稱,另外顯示出來在其他位置,不知有何方法,麻煩您了。
Wen hua

一整天 提到...

Wen Hua您好:
那您可以在B1用=if(countif(A$1:A$30,B1)>0,"相同","不相同")

Wen Hua 提到...

一整天大大您好:
這個公式太好用了,之前自己比對看得眼花撩亂,又常會漏掉,非常感謝!另外,不知是否可以將比對結果是相同的名稱,自動帶出來,再次麻煩您了。
Wen hua

一整天 提到...

WenHua:
您可以利用排序或是篩選的功能,就可以把結果相同的資料排在一起了.

Wen Hua 提到...

一整天大大您好:
試了一下結果是”相同”可排在一起,但原來的”名稱”是否也可以顯示出來?謝謝您了。

Wen Hua 提到...

彰化一整天大大您好:
調整了您的公式,比對結果如果是相同,名稱已可顯示出來了,非常感謝您的幫助。
=IF(COUNTIF(A$1:A$30,B2)>0,B2,"不相同")
Wen hua

aa 提到...

一整天大大您好:
這個功能相當好用,可以多條件篩選出自己所要的列,想再多請教一個問題,如果今天我想要顯示的答案是多條件刪除列後的結果該怎麼辦呢? 引用您舉的例子,假設我想要刪除的是班級名稱中含有"一甲"或"一乙"的列,也就是顯示出的列都是除了班級名稱含有"一甲"或"一乙"以外其他所有的列,請問我能有什麼方法可以做呢?萬分感激,祝事事順心

一整天 提到...

aa您好:
我把程式作了修改,只要在要比對的欄位數輸入負值,就會是比對不到的資料出現.

BB 提到...

一整天大大您好:感謝您修改程式,不過我試了好幾次,似乎都無法出現答案XD,請問我用vlookup可以達到我想要的結果嗎?感謝

一整天 提到...

bb您好:
您可以把還沒按下按鈕的畫面抓給我(或是錯誤畫面)嗎?寄你的測試資料給我,我幫您看看是那出了問題,我的email在首頁右上方.

aa 提到...

一整天大大您好,我是bb,我已經把我的問題寄給您了,請您過目,非常感謝

history 提到...

彰化一整天:

你好。冒昧寫電郵給你。因爲我真是需要高人指點。

學校舉行口試,一共有24班,每班都有10位以上,20位以下的老師為主考官。出來了一大堆資料。

過去兩個星期,除了公務的時間外,我確實努力看書和找資料,但是還是沒有辦法整合這些資料。

如果你願意,我是否可以寄上我的部分資料,請你看看指點?

不勝感激。


某一位老師上

history 提到...

彰化一整天:

你好。冒昧寫電郵給你。因爲我真是需要高人指點。

學校舉行口試,一共有24班,每班都有10位以上,20位以下的老師為主考官。出來了一大堆資料。

過去兩個星期,除了公務的時間外,我確實努力看書和找資料,但是還是沒有辦法整合這些資料。

如果你願意,我是否可以寄上我的部分資料,請你看看指點?

不勝感激。


某一位老師上

一整天 提到...

history您好:
您可以寄給我部分檔案.我再看要如何幫您.

arvalis 提到...

一整天大大您好:
您的大批篩選比對幫了我非常大的忙,謝謝!
但是我發現有一個問題,資料己經篩選出來,但是沒有辦法依照我在(目的)當中排列的順序來呈現結果,會依原本(來源)內的順序,有沒有辦法可以讓(目的)中所篩選出的結果,就是我在目的中所排的順序?非常感謝!

一整天 提到...

arvalis您好:
這部分的修改需要花一點時間,您可以寄email到我信箱,我改好再email通知您。

arvalis 提到...

一整天大大您好:
期待您的修改早日完工,麻煩您了!謝謝!

ally 提到...

版主:
您好!
有一問題想請教您,
如何將excle的列轉行?

列*行=40000多*28
如果使用transpose,
電腦會跑不動.

附上檔案,
請您幫忙看看,
非常感謝!

祝平安
ALLY敬上

history 提到...

Thank you.you are really doing something good.

一整天 提到...

ally您好:
您的附件我沒收到,如果在Excel2003中,它的欄位最多為256欄,所以列轉行會無法顯示。

ally 提到...

版主
您好!
問題已經解決了.
謝謝!
那個動作只是步驟之一,
所以另行方法將問題解決了.
謝謝您!
祝平安
Ally敬上

mammer 提到...

你好,想請教您進階篩選的問題,我常用進階篩選來取得二欄的交集資料,但後來發現在篩選文字時,excel會將部份match的結果也挑出來,查了一些資料,microsoft online中則說明若要找完全符合條件的文字,則要輸入=''=text''(http://office.microsoft.com/zh-tw/excel/HP052001781028.aspx),這是要怎麼輸入呢?? 假設rang=A1:A99,criteria=B1:B20,皆為文字

一整天 提到...

mammer您好:
它的意思是如果您的儲存格的內容是"一整天",則只要字串有"一整天"的就會出來,但是如果您在儲存格中輸入="一整天",則是完全一樣才會出現,輸入="一*",則”一”開頭的字串就會出現,如果您還是看不懂,您可以寄email給我,我再寄Excel範例給您看。

SKC 提到...

  您好,己經依您的方法下載了离離線版做長期使用。但出現一個問題。
  如我把檔案Save as D:\tooldb.mhtml ,離線版是正常的。但當我把D:\tooldb.mhtml 檔檔移去 D:\App 或 C:\program files,後,有關的程式就用不到了。

  請問是什麼原因?能解決嗎?我用 Vista SP1,IE7沒有做過升級。又或可否上傳一個可做「移動」的離線版給我?

  謝謝。

一整天 提到...

skc您好:
應該是不會出問題才對,您可以下載精簡版試看看
精簡版網址:http://f272586.googlepages.com/272586.htm
把它另存為.htm,再試看看.

SKC 提到...

  您好,已在 IE7 測試精簡版,也是需要保存 mhtml 才能使用。我已刪除 IE7 的所有 Cache 做測試、IE7 的設定是預設的、也沒有感染過任何的病毒、沒有增加過任何的 Add-ons。因我本身用 Firefox。
  將檔案移去其他的子目錄後,就不能使用。

一整天 提到...

SKC您好:
我這邊沒有vista的機器可以試,您有試過在其它的機器會不會有這樣的問題.或是你寄一下您儲存的檔案給我,我這邊試看看.ps:因為這個網頁工具只可以在ie的環境下執行.

SKC 提到...

  已在 Winxp 的 IE 6 試過,問題是一樣的。如在 D:\ 保存 Mhtml 後。移去 D:\temp 內,再打開就不能用了。希望你自己試一試。不移動檔案,則正常。

一整天 提到...

SKC您好:
我去下載了Firefox,並用了IE相容的Tab去儲存html,就會出現您說的問題,所以你用IE去儲存html就不會出現您說的問題.

SKC 提到...

您好,我有用 Firefox,但是我更知道 Firefox 與 IE 不兼容的。請問,我是否會笨到用 Firefox 去 Save MHTML?

我再說一次,我是用 IE6、IE7,分別在 Winxp SP3 與 Vista SP1 上,保存為「MHTLM」的。分別在二臺電臺,兩臺電腦都有「同樣的問題」,我已能確定,不是我這方面的事。我猜測是「程式的 Bugs」或「IE 本身的一些問題」。

我本身是懂得用電腦的。
  

Sam 提到...

彰化一整天你好:
看到你的熱心回應,本身也有一些EXCEL的小問題想要請教你,原始檔案不知寄到何處比較方便,謝謝!

Sam 提到...

彰化一整天你好:
看到你的熱心回應,本身也有一些EXCEL的小問題想要請教你,原始檔案不知寄到何處比較方便,謝謝!

一整天 提到...

SKC您好:
很抱歉,可能是我用詞的問題,造成您的誤會,我是因為在我的電腦上試不出您說的問題,我的環境是windows xp sp3加ie6,才會想說下載firefox並安裝ie相容性,試完真的也會無法使用,才會認為可能是這個問題,但是你說的「程式的 Bugs」或「IE 本身的一些問題」也是有可能的,您可以試看看,精簡版網址:http://f272586.googlepages.com/272586.htm
用檢視原始碼的方式,把內容copy,再新增一個文字檔貼上.把它存成.html看看會不會還是有這個題,另外如果您是要用大批篩選比對資料功能,我有寫一個Excel版的,功能一樣,您可以下載試看看.
網址:http://272586.blogspot.com/2009/12/excel.html

一整天 提到...

sam您好:
您可以寄到在首頁右上方的Email,寄完留個訊息.避免信件到垃圾信箱.

arvalis 提到...

一整天大大您好:
您的大批篩選比對以目的為序,幫了我非常大的忙,謝謝!
如果我不是要篩選,而是要把在資料列中符合篩選條件的資料標記出來,而其它的資料還要保留下來,不知道可不可以做到?

一整天 提到...

arvalis您好:
我己將您要的功能加入了.請到範例檔案找findtool.xls重新下載.
http://sites.google.com/site/272586

arvalis 提到...
作者已經移除這則留言。
arvalis 提到...

一整天大大您好:
我已經下載了新的更新檔,測試過後會在原始檔中被篩選的列,最後會加上*,不知標記符號可不可以自行填入,這樣在標記各種不同身份較方便,麻煩您了,非常感謝。

一整天 提到...

arvalis您好:
我己將您要的功能加入了.請到範例檔案找findtool.xls重新下載.
http://sites.google.com/site/272586

Vincent 提到...

一整天大大您好,

不知您可否幫忙, 我在工作上有每週週報更新的部分需要作大量比對, 但是跟您的範例不同, 您的範例是比對出資料A和資料B有無資料的差異, 可是我需要的是同一訂單, 同一產品在本週週報跟上週週報的差異, 基本上有數量跟交貨期的差異,有更簡易的方式嗎? 謝謝您

一整天 提到...

Vincent您好:
如果您的資料筆數及順序都一樣,可以利用
http://272586.blogspot.com/2009/09/excel.html
在Excel中比對二筆資料的不同並把不同處標示出來
或是
2009/5/9修改以儲存格為單位進行比對(只會比對同一列的資料),使用時機=>比對兩個工作表差異的資料
http://f272586.googlepages.com/compareab_v2.xls

提到...

敝人有4欄資料要比對
有兩欄分別是客戶要訂購的型號欄位與數量欄位,另外兩欄是工廠報價裡的型號與數量,型號欄位是英數字夾雜,但客戶跟工廠之間,相同產品,有的型號可能不同,有時候工廠希望客戶多買一些,數量也會多報,所以工廠報的數量跟客人要求的也不完全一樣,請問該怎麼把客人跟工廠的相同產品,放在同一列上,以便比對兩者間數量欄位的差異?

提到...

一整天您好

敝人有4欄資料要比對
有兩欄分別是客戶要訂購的型號欄位與數量欄位,另外兩欄是工廠報價裡的型號與數量,型號欄位是英數字夾雜,但客戶跟工廠之間,相同產品,有的型號可能不同,有時候工廠希望客戶多買一些,數量也會多報,所以工廠報的數量跟客人要求的也不完全一樣,請問該怎麼把客人跟工廠的相同產品,放在同一列上,以便比對兩者間數量欄位的差異?

一整天 提到...

謝您好:
您方便給我範例檔案嗎?順便寄給我您處理前跟處理後的情形或畫面
您可以寄到imingho@gmail.com

michael 提到...

請問是否有可以比對"兩欄"間"大量"且有分隔符號資料的功能,這樣一次可以比對很多列
,像是欄位A與欄位B的比較,但A欄第一列為12,1,14第二列為24,33而B欄第一列為12,14,第二列為24故兩欄間第一列不同為1
,第二列不同為23,感謝大大解答!~

michael 提到...

請問是否有可以比對"兩欄"間"大量"且有分隔符號資料的功能,這樣一次可以比對很多列
,像是欄位A與欄位B的比較,但A欄第一列為12,1,14第二列為24,33而B欄第一列為12,14,第二列為24故兩欄間第一列不同為1
,第二列不同為23,感謝大大解答!~

一整天 提到...

michael您好:
您可以參考

http://272586.blogspot.com/2010/05/excel.html

利用Excel比對二筆有分隔符號資料的不同

阿捷 提到...

一整天先生您好:
請問有無方法可將excel一欄原始資料,貼上另sheet已經篩選過的可見儲存格上(整欄貼).謝謝

黃柏豪 提到...

老師您好:
感謝您的幫助,讓我們行政效率上大大提升很多,著實獲益良多,我們希望能贊助您,將在星期一上班日款項匯入,真的非常感謝您~
在此同時,也遇到一些問題,懇請老師幫忙,如能得到您的回音,將會一解我們心頭大患阿~ 我們將問題寄到老師的信箱囉~在此先謝謝老師了~

一整天 提到...

您好,
因為您的資料是從網路上複製下來,最前面函有一個看不見的字元,會造成比對不出來,
您可以用len函數時查看,就會知道有幾個看不見的字元.相關請看.

快速修正儲存格中看不見的字元
http://blog.bestdaylong.com/2011/12/blog-post.html

Excel無法做函數運算的解決方法
http://blog.bestdaylong.com/2007/09/excel_21.html

明諺 提到...

很實用,工作上正好用到。謝謝你^^

Squall Lex 提到...

蔡老師您好~
剛才提問~有參考了你給我的檔案~
如果是想要比對條件以外的~
EX.
處理前裡面符合的資料不要顯示~
沒有符合的資料顯示~

那麼~程式碼的部分~
If find_method = "完全相同" Or find_method = "" Then
If Sheet2.Cells(j, Abs(f)) = Sheet4.Cells(i, 1) And Sheet2.Cells(j, Abs(f)) <> "" Then

改成
If Sheet2.Cells(j, Abs(f)) <> Sheet4.Cells(i, 1) And Sheet2.Cells(j, Abs(f)) <> ""

但是我看不太懂這一段
Else
If InStr(Sheet2.Cells(j, Abs(f)), Sheet4.Cells(i, 1)) > 0 And Sheet2.Cells(j, Abs(f)) <> "" Then
Sheet4.Cells(i, 2) = Sheet4.Cells(i, 2) + 1 '出現次數
For k = 1 To n
temp = Sheet2.Cells(j, k)
Sheet3.Cells(l, k) = temp

是甚麼含意 ?

蔡明和 提到...

您只要把搜尋欄位的數字改成負號,就是沒有符合的資料顯示

壽司 提到...

您好,請問如上所述B1用=if(countif(A$1:A$30,B1)>0,"相同","不相同"),只要包含A$1字串就是相同,要怎麼改寫式子,請指點迷津,感謝。

壽司 提到...
作者已經移除這則留言。
壽司 提到...
作者已經移除這則留言。
蔡明和 提到...

=if(A$1=b1,"相同","不相同")

贊助

彰化一整天粉絲團

About Me

我的相片

很多人問我為什麼叫"一整天",其實這是有原故的.有一天看一本書.就是說如何介紹自己.裡面說到如何讓別人對你的名字有印象.就要讓別人跟你的名字或是身體的一些特徵有關.比如很胖.就會被別人取小胖或是胖子.或是跟名字有關的諧音,那天看布袋戲中有一個叫”一頁書”的角色.所以我就想我的名字.可以想出什麼字出來.結果我把我的名字"明和",拆成日月和,日月和在一起,就代表白天晚上都在一起.就取名為"一整天",而這個"一整天"跟我的名字有關也方便別人記憶,後來發現"一整天"這個名詞太常被用,所以就在"一整天"前面加上"彰化"變成"彰化一整天",為什麼要加彰化.因為我老家在彰化,所以以後別人要找我的部落格的話.我都會跟他們說,請到yahoo或是google打"彰化一整天"第一筆出現的就是我的Blog. PS:現在只要輸入"一整天"可以找到.

Google+ Followers