ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] §ä­«½Æ¸ê®Æ¶µ

[µo°Ý] §ä­«½Æ¸ê®Æ¶µ

A1:A160000¬O§Úªº¸ê®Æ,§Ú·Q§ä¥X¨ä¤¤¦³­«½Æªº¸ê®Æ,µL½×¥Î¤°麽¤èªk¼Ð»x¥X¨Ó³£¥i¥H
§Ú¸Õ¤F¦bB1:B160000¿é¤J°}¦C¤½¦¡"=countif($A$1:$A$160000,A1:A160000)",µ²ªG¬O´X¥G¥¢±Ñ,¦n¤[³£¨S¦³Åã¥Üµ²ªG.
¦³¤°麽¦n¤èªk¤¶²Ð?ÁÂÁÂ
lmh

¦^´_ 1# mhl9mhl9

°}¦C¤½¦¡"=countif($A$1:$A$160000,A1:A160000)" ¤@©w­n¶]«Ü¤[

«Øij¥ý±NAÄæ±Æ§Ç
B1¤½¦¡
=IF(COUNT(0/(OFFSET(A1,-1,)=A1),0/(A2=A1)),"­«½Æ","")
¤U©Ô

­Y²Ä¤@¦C¬°ªíÀY¡A¸ê®Æ¥Ñ²Ä¤G¦C¶}©l¡A¤½¦¡§ó²³æ
B2¤½¦¡
=IF((A2=A1)+(A2=A3),"­«½Æ","")
¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# ML089

Book100.rar (979.01 KB)
   ÁÂÁ§A,§Aªº¤èªk²³æ©ö©ú,§Ú¯d¤Ucopy
¦]¬°µ¥¤£¤Îºô¤WµªÂÐ,§Ú¦Û¤v¼g¤F¤@­Ó,¨£ªþ¥ó,³t«×ÁÙº¡·N.
§Ú¦AÁ¿¤@Á¿¦Û¤vªº­n¨D:
§Úªº¤å¥ó¨½¸ê®Æ®w¬O¥Î¤á¦Û¤v¿é¤J,§Ú©È¥L­Ì­«½Æ¿é¤J¸ê®Æ, ©Ò¥H­n°µ¤@­Ó"Àˬd­«½Æ¸ê®Æ"ªº¥\¯à.
¸ê®Æ®w¦@¦³14Äæ,¥u¦³"B,C,D,E"¤£¯à§¹¥þ­«½Æ,¨ä¥LÄæ¦ì¦]¬°¥i¥H§ï°Ê,©Ò¥H¤£ºÞ¥¦¤F,§Ú¥uºÞ"¦P¤À¤½¥q¦P¤é´Á¦P¨®¶¡¦P²Õ"¤£¯à­«½Æ
§Ú§âBCDE³o4Äæ¬Û¥[,¥Î2¦¸collection¯à¨ú°ß¤@­Èªº¤èªk,¦b"Temp­¶"show¥X­«½Æªº¸ê®Æ.
§A¬O¤£¬O¦³®É¶¡¬Ý¬Ý¯à¤£¯à¥Î§Aªº¤èªk,¦h¤F­Ó±Æ§Ç,§Úªº¸ê®Æ±Æ§Çµ²ªG¤]¤£ª¾¹D¥i¤£¥i¥H¥Î§Aªº¤½¦¡.
¦A¦¸ÁÂÁÂ
lmh

TOP

¦^´_ 2# ML089
­ì¨Ó160000±ø¸ê®Æ,¦]¬°¤å¥ó¶W¹L1M¤£¯à¤W¶Ç,©Ò¥H¯d¤U50000±ø,
lmh

TOP

¦^´_ 3# mhl9mhl9

¤@¡B±Æ§Ç B¡BC¡BD¡BEÄæ ¡A¾A¥Î©óEXCEL2007ªO¥H¤W¡AEXCEL 2003¥u¯à±Æ§Ç3Äæ¥i¥H¥t¥~¥[ »²§UÄæ ±NB¡BC¡BD¡BEÄæ³s±µ(F2=B2&C2&D2&E2)¦¨¤@Äæ¨Ó±Æ§Ç
¤G¡B¬d¸ß­«ÂФ½¦¡
G2 =IF((B2&C2&D2&E2=B1&C1&D1&E1)+(B2&C2&D2&E2=B3&C3&D3&E3),"­«½Æ","")
¤U©Ô

­Y§A·|¥ÎVBA·íµM·|§ó¥[«K§Q¡A§Ú¥u·|²³æVBA¨S¦³¿ìªkµ¹§A´£¨Ñ·N¨£¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089
¤Q¤ÀÁÂÁÂ,ÁöµM§Ú³Ìªì´£¥Xªº°ÝÃD¨S¦³¥Î§Aªº¤èªk(¤å¥ó¤w¥ÎVB§ï§´¤F,¤£·Q¦A¦h·Ð,¯S§O¬O±Æ§Ç¦Z¯à¤£¯à¥¿±`¤u§@¤£ª¾¹D)
¦ý¬Ý¤F§Aªº¤½¦¡¨ü¨ì啓µo,§Ú§â¤å¥ó­ì¨Ó¥Î¦Û©w¸q¨ç¼Æ§ï¬°ª½±µ¼g¦bÀx¦s®æ¨½,¥Î¤F3­Ó¹L´ç¼Æ¾Ú,(¦@6­Ó¤½¦¡,¨C­Ó¤½¦¡­n¦V¤Ucopy160000®æ)
¥X¥G§Ú·N®Æ,³t«×³ºµM§Ö¤F3­¿¦h,­ì¨Ó7¤ÀÄÁ§¹¦¨¤@­Ó¾Þ§@,²{¦b102¬í´N§¹¦¨,·Pı¤WµÎªA¤F¦n¦h.¦ý³sÄò¤u§@¤F7¤p®É!
¨C¦¸¨ì³o­Óºô¤W¬Ý¬Ý,Á`·|¦³¦¬Ã¬,ÁÂÁ§A.
lmh

TOP

¦^´_ 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("¸ê®Æ®w")
  7.         .Rows.Hidden = False '¨ú®ø ¦s®æ®æªºÁôÂÃ
  8.         Set Rng(1) = .Range("B:E").SpecialCells(xlCellTypeConstants).Rows
  9.         Rng(1).Interior.ColorIndex = xlNone                            '¨ú®ø     ¹Ï¼ËÃC¦â
  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, "¦@¶O®ÉSS¬í")
  30.     End With
  31. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE
GBKEE,§AÁ`¦³¥O¤H¿³¾Äªºµª®×µ¹§Ú,§Ú¸Õ¤F§Aªº¤èªk:1³t«×·¥§Ö(160000±ø,16¬í).2¤£¥Î¹L´çsheet(Temp),user¤£¥Î­¶¶¡¸õ¨Ó¸õ¥h,¬J¤è«K¦h¦h,¦Ó¥B¤£©ö¥X¿ù.3§â­«½Æ¸ê®Æ¤À¦¨2²Õ,Åýuser¥i¥H¿ï¾Ü¬õ¦âÀx¦s®æ¾ã¦C§R°£¤§,¤è«K¦h¦h.
§Ú­ì³]­p¬O§ä¨ì­«½Æ¦C,¥þ³¡§R°£,­n¨Duser,¦A­«·s¿é¤J¤@¦¸"¦³Ãö¤À¤½¥q¦³Ãö¤é´Á(©Ò¦³¨®¶¡©Ò¦³²Õ)",¦]¬°¥X²{­«½Æ¸ê®Æ,user¤@©w¦Ü¤Ö¤À2¦¸¿é¤J¸ê®Æªº,¨º»ò2¦¸¤§¶¡,user¦³¥i¯à§ï°Ê"F:N"ªº¸ê®Æ,¦pªG¨Ì­«½Æ¸ê®Æ¥X²{¥ý«á¤À¦¨2²Õ,§R°£¬õ¦â,¨º»ò¤@©w¬O¦Z¤@¦¸¿é¤Jªº¸ê®Æ,§R±o¹ï¤£¹ï±o¹ê»Ú°µ°µ¬Ý,¦pªG¤@©w§R°£²Ä¤G¦¸¿é¤J(¬õ¦â),¨º»ò¤S¦h¤F¤@­Ó¦n³B:4 user¬Ù«o­«·s¿é¤J¤@¦¸¾Þ§@.
§Úª¾¹Ddictionary¤]¥i¥HÀò±o°ß¤@­È,¦ý¨ãÅé°µªk¤£²M·¡,§Ú¤@ª½¥Îcollection¨ú°ß¤@­È,¬Ý¤F§Aªº¤¶²Ð,¾Ç²ß¨ì¨ú°ß¤@­È¤§¥~,ÁÙ¥i¥H¨Ì­«½Æ­È¥X²{¦¸§Ç,¤À¤G¯ªÅã¥Ü­«½Æ­È,¯u¬O¤Ó·PÁ§A¤F.
lmh

TOP

¦^´_ 7# GBKEE

by the way,­è¤~¸Õ¤F,¿ï¾Ü¬õ¦â¾ã¦C§R°£¤£¦æ,¦]¬°·|§âÁôÂ꺦C¤]§R°£¤F,¥Î¨ÌÀx¦s®æ©³¦â¿z¿ï¥X¬õ¦â¦A§R°£´N¨S¦³°ÝÃD¤F.
lmh

TOP

        ÀR«ä¦Û¦b : ¦³¦h¤Ö¤O¶q´N°µ¦h¤Ö¨Æ¡A¤£­n¤ß¦sµ¥«Ý¡Aµ¥«Ý¤~·|¸¨ªÅ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD