Board logo

標題: [發問] 找重複資料項 [打印本頁]
作者: mhl9mhl9    時間: 2014-4-5 15:49     標題: 找重複資料項

A1:A160000是我的資料,我想找出其中有重複的資料,無論用什麽方法標誌出來都可以
我試了在B1:B160000輸入陣列公式"=countif($A$1:$A$160000,A1:A160000)",結果是幾乎失敗,好久都沒有顯示結果.
有什麽好方法介紹?謝謝
作者: ML089    時間: 2014-4-5 17:22

回復 1# mhl9mhl9

陣列公式"=countif($A$1:$A$160000,A1:A160000)" 一定要跑很久

建議先將A欄排序
B1公式
=IF(COUNT(0/(OFFSET(A1,-1,)=A1),0/(A2=A1)),"重複","")
下拉

若第一列為表頭，資料由第二列開始，公式更簡單
B2公式
=IF((A2=A1)+(A2=A3),"重複","")
下拉
作者: mhl9mhl9    時間: 2014-4-5 21:33

回復 2# ML089

[attach]17945[/attach]
   謝謝你,你的方法簡單易明,我留下copy
因為等不及網上答覆,我自己寫了一個,見附件,速度還滿意.
我再講一講自己的要求:
我的文件里資料庫是用戶自己輸入,我怕他們重複輸入資料, 所以要做一個"檢查重複資料"的功能.
資料庫共有14欄,只有"B,C,D,E"不能完全重複,其他欄位因為可以改動,所以不管它了,我只管"同分公司同日期同車間同組"不能重複
我把BCDE這4欄相加,用2次collection能取唯一值的方法,在"Temp頁"show出重複的資料.
你是不是有時間看看能不能用你的方法,多了個排序,我的資料排序結果也不知道可不可以用你的公式.
再次謝謝
作者: mhl9mhl9    時間: 2014-4-5 21:35

回復 2# ML089
原來160000條資料,因為文件超過1M不能上傳,所以留下50000條,
作者: ML089    時間: 2014-4-6 01:54

回復 3# mhl9mhl9

一、排序 B、C、D、E欄 ，適用於EXCEL2007板以上，EXCEL 2003只能排序3欄可以另外加 輔助欄 將B、C、D、E欄連接(F2=B2&C2&D2&E2)成一欄來排序
二、查詢重覆公式
G2 =IF((B2&C2&D2&E2=B1&C1&D1&E1)+(B2&C2&D2&E2=B3&C3&D3&E3),"重複","")
下拉

若你會用VBA當然會更加便利，我只會簡單VBA沒有辦法給你提供意見。
作者: mhl9mhl9    時間: 2014-4-6 04:40

回復 5# ML089
十分謝謝,雖然我最初提出的問題沒有用你的方法(文件已用VB改妥了,不想再多煩,特別是排序后能不能正常工作不知道)
但看了你的公式受到啓發,我把文件原來用自定義函數改為直接寫在儲存格里,用了3個過渡數據,(共6個公式,每個公式要向下copy160000格)
出乎我意料,速度竟然快了3倍多,原來7分鐘完成一個操作,現在102秒就完成,感覺上舒服了好多.但連續工作了7小時!
每次到這個網上看看,總會有收穫,謝謝你.
作者: GBKEE    時間: 2014-4-6 07:17

回復 6# mhl9mhl9
  1. Option Explicit
  2. Sub Ex()
  3.     Dim D As Object, Rng(1 To 2) As Range, R As Range, Ar As String, T As Date
  4.     Set D = CreateObject("scripting.dictionary")
  5.     T = Time
  6.     With Sheets("資料庫")
  7.         .Rows.Hidden = False '取消 存格格的隱藏
  8.         Set Rng(1) = .Range("B:E").SpecialCells(xlCellTypeConstants).Rows
  9.         Rng(1).Interior.ColorIndex = xlNone                            '取消     圖樣顏色
  10.         For Each R In Rng(1)
  11.             Ar = Join(Application.Transpose(Application.Transpose(R.Value)), ",")
  12.             If D.exists(Ar) Then
  13.                 D(Ar).Interior.Color = vbYellow                        '有重複   圖樣顯示黃色
  14.                 R.Interior.Color = vbRed                               '重複資料 圖樣顯示紅色
  15.                 If Rng(2) Is Nothing Then
  16.                     Set Rng(2) = Union(.Rows(1), R, D(Ar))
  17.                 Else
  18.                     Set Rng(2) = Union(Rng(2), R, D(Ar))
  19.                 End If
  20.             Else
  21.                 Set D(Ar) = R
  22.             End If
  23.         Next
  24.         If Not Rng(2) Is Nothing Then
  25.             .Rows.Hidden = True
  26.             Rng(2).Rows.Hidden = False
  27.             .Cells(1).Activate
  28.         End If
  29.         MsgBox Application.Text(Time - T, "共費時SS秒")
  30.     End With
  31. End Sub
複製代碼
作者: mhl9mhl9    時間: 2014-4-6 11:47

回復 7# GBKEE
GBKEE,你總有令人興奮的答案給我,我試了你的方法:1速度極快(160000條,16秒).2不用過渡sheet(Temp),user不用頁間跳來跳去,既方便多多,而且不易出錯.3把重複資料分成2組,讓user可以選擇紅色儲存格整列刪除之,方便多多.
我原設計是找到重複列,全部刪除,要求user,再重新輸入一次"有關分公司有關日期(所有車間所有組)",因為出現重複資料,user一定至少分2次輸入資料的,那麼2次之間,user有可能改動"F:N"的資料,如果依重複資料出現先後分成2組,刪除紅色,那麼一定是后一次輸入的資料,刪得對不對得實際做做看,如果一定刪除第二次輸入(紅色),那麼又多了一個好處:4 user省卻重新輸入一次操作.
我知道dictionary也可以獲得唯一值,但具體做法不清楚,我一直用collection取唯一值,看了你的介紹,學習到取唯一值之外,還可以依重複值出現次序,分二祖顯示重複值,真是太感謝你了.
作者: mhl9mhl9    時間: 2014-4-6 12:05

回復 7# GBKEE

by the way,剛才試了,選擇紅色整列刪除不行,因為會把隱藏的列也刪除了,用依儲存格底色篩選出紅色再刪除就沒有問題了.



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