返回列表 上一主題 發帖

[發問] 找重複資料項

mhl9mhl9

帖子
214 
主題
74 
精華
0 
積分
296 
點名
0  
作業系統
win7 
軟體版本
office2007 
閱讀權限
20 
性別
男 
來自
hk 
註冊時間
2013-6-17 
最後登錄
2018-11-3 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2014-4-5 15:49 | 只看該作者

[發問] 找重複資料項

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

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
2#
發表於 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),"重複","")
下拉
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

mhl9mhl9

帖子
214 
主題
74 
精華
0 
積分
296 
點名
0  
作業系統
win7 
軟體版本
office2007 
閱讀權限
20 
性別
男 
來自
hk 
註冊時間
2013-6-17 
最後登錄
2018-11-3 
3#
發表於 2014-4-5 21:33 | 只看該作者
回復 2# ML089

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

TOP

mhl9mhl9

帖子
214 
主題
74 
精華
0 
積分
296 
點名
0  
作業系統
win7 
軟體版本
office2007 
閱讀權限
20 
性別
男 
來自
hk 
註冊時間
2013-6-17 
最後登錄
2018-11-3 
4#
發表於 2014-4-5 21:35 | 只看該作者
回復 2# ML089
原來160000條資料,因為文件超過1M不能上傳,所以留下50000條,
lmh

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
5#
發表於 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沒有辦法給你提供意見。
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

mhl9mhl9

帖子
214 
主題
74 
精華
0 
積分
296 
點名
0  
作業系統
win7 
軟體版本
office2007 
閱讀權限
20 
性別
男 
來自
hk 
註冊時間
2013-6-17 
最後登錄
2018-11-3 
6#
發表於 2014-4-6 04:40 | 只看該作者
回復 5# ML089
十分謝謝,雖然我最初提出的問題沒有用你的方法(文件已用VB改妥了,不想再多煩,特別是排序后能不能正常工作不知道)
但看了你的公式受到啓發,我把文件原來用自定義函數改為直接寫在儲存格里,用了3個過渡數據,(共6個公式,每個公式要向下copy160000格)
出乎我意料,速度竟然快了3倍多,原來7分鐘完成一個操作,現在102秒就完成,感覺上舒服了好多.但連續工作了7小時!
每次到這個網上看看,總會有收穫,謝謝你.
lmh

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
7#
發表於 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
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

mhl9mhl9

帖子
214 
主題
74 
精華
0 
積分
296 
點名
0  
作業系統
win7 
軟體版本
office2007 
閱讀權限
20 
性別
男 
來自
hk 
註冊時間
2013-6-17 
最後登錄
2018-11-3 
8#
發表於 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取唯一值,看了你的介紹,學習到取唯一值之外,還可以依重複值出現次序,分二祖顯示重複值,真是太感謝你了.
lmh

TOP

mhl9mhl9

帖子
214 
主題
74 
精華
0 
積分
296 
點名
0  
作業系統
win7 
軟體版本
office2007 
閱讀權限
20 
性別
男 
來自
hk 
註冊時間
2013-6-17 
最後登錄
2018-11-3 
9#
發表於 2014-4-6 12:05 | 只看該作者
回復 7# GBKEE

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

TOP

        靜思自在 : 受人點水之恩，須當湧泉以報。
返回列表 上一主題