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

[µo°Ý] ¦p¦ó¦b°}¦C¸Ì¿z¿ï¥X¤£­«½Æªº­È?

[µo°Ý] ¦p¦ó¦b°}¦C¸Ì¿z¿ï¥X¤£­«½Æªº­È?

·Q½Ð±Ð
¦p¦ó±NA1¨ìD5¤£­«½Æªº­È
¨Ì§ÇÅã²{¦bG1©¹¥kªºÀx¦s®æ?

¦p¹Ï¥Ü

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-9-14 21:12 ½s¿è

¦^´_ 1# zheng211016
­è¦n¥i¥H½m²ß¤@¤U ¤£¹L¤£ª¾ ¸Ó¦p¦ó¦b¦r¨å¥Î¦êÁp¤è¦¡©Î¬O°}¦C¸Ì¾î¦V±Æ§Ç...
  1. Public Sub ¬d¸ß¤£­«½Æ¨Ã¾î¦V±Æ§Ç½m²ß()
  2. If [F1] <> "" Then [F1].CurrentRegion.Clear
  3. Arr = [A1].CurrentRegion
  4. Set xD = CreateObject("Scripting.Dictionary")
  5. For I = 1 To UBound(Arr, 1)
  6.     For j = 1 To UBound(Arr, 2)
  7.         xD(Arr(I, j)) = Arr(I, j)
  8.     Next j
  9. Next I
  10. [F1].Resize(1, xD.Count) = xD.Keys
  11. [F1].CurrentRegion.Sort [F1].CurrentRegion, Orientation:=xlLeftToRight
  12. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ zheng211016 ©ó 2020-9-14 22:40 ½s¿è

¦^´_ 2# °a¤ªºµ


¹ê´ú¨S°ÝÃD «D±`·PÁÂ
¦ý·Q½Ð°Ý¬O¦p¦óÅý¥L¿z¿ïªº?
Ãø¹D¬O
xD.Count
xD.Keys
¯àÅý¥L¦Û°Ê¿ï¥X¤£­«½Æªº­È?

TOP

¦^´_ 1# zheng211016

¸Õ¸Õ¬Ý
¦r¨å(Dictionary)ªºexists§PÂ_¬O§_¬°­«½Æ,¦pªG¦r¨å¤º§ä¤£¨ì¤~©ñ¤J,¦r¨å¤º¤w¦³ªº­È¤£©ñ¤J
Sub Ex()
Dim d As Object
Dim a As Object
Set d = CreateObject("Scripting.Dictionary")
For Each a In Range("a1").CurrentRegion
   If Not d.exists(a.Value) Then d.Add a.Value, ""
Next
[g1].Resize(, d.Count) = d.keys
Range([g1], Cells(1, [g1].End(2).Column)).Sort Range("g1"), 1, Orientation:=xlSortRows
Set d = Nothing
End Sub

TOP

E2:E10 {=IFERROR(SMALL(IF(COUNTIF(F$1:F1,A$1:D$5)=0,A$1:D$5),1),"")

G2:G10 {=IFERROR(INDIRECT(TEXT(SMALL(IF(COUNTIF(G$1:G1,A$1:D$5)=0,ROW($1:$5)/1%+{1,2,3,4}),1),"!R0C00"),),"")

H2"H10 {=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF(COUNTIF(H$1:H1,A$1:D$5)=0,ROW($1:$5)/1%+{1,2,3,4}*10001),1),4),"!R0C00"),),"")

7366.png (6.48 KB)

7366.png

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 3# zheng211016

§Ú¤]¬O·s¤â³Ìªñ¤~¶}©l¾Ç²ß¦r¨å¹B¥Î ÁÙ¦b¾Ç²ß¶¥¬q  
¦³ªÅ§A¤]¥i¥H¬ã¨s¬Ý¬Ý

­ã´£³¡ªL¤j¤j»¡¹Lªº
dic--keys, items
keys--¥u·|¯d¿W¤@µL¤Gªº"¯Á¤Þ­È", ©Ò¥H¤£·|­«ÂÐ
items--¥i¥H®e¯Ç¥ô¦ó«¬ºA¤º®e, ÅܤƸû¦h, ¦ý¥i±q²³æªºµÛ¤â

¨ä¹ê«Ü¦n²z¸Ñ, ¦h´X¦¸½m²ß§Y¥i,
³æÄæ¸ê®Æ:
1) ¨ú±oaÄ椺®eªº°ß¤@­È
2) ­pºâaÄæ¦U°ß¤@­Èªº¥X²{¦¸¼Æ
¨âÄæ¸ê®Æ:
1) ­pºâaÄæ¦U°ß¤@­È¦bbÄ檺¦X­p¼Æ
2) ­pºâaÄæ¦U°ß¤@­È,¥BbÄæ²Å¦X¬Y¤@±ø¥óªº¦¸¼Æ

TOP

¦^´_ 4# jcchiang
¦^´_ 5# hcm19522
¦^´_ 6# °a¤ªºµ

ÁÂÁ¤T¦ì¤j¤jªº«ü±Ð ¤p§Ì¨ü¯q¨}¦h!!!

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD