Board logo

¼ÐÃD: [µo°Ý] vbaªº¿z¿ï¥\¯à (¨ú®ø³¡¤À¿z¿ï) [¥´¦L¥»­¶]

§@ªÌ: wei9133    ®É¶¡: 2020-10-6 00:33     ¼ÐÃD: vbaªº¿z¿ï¥\¯à (¨ú®ø³¡¤À¿z¿ï)

¦p¦ó¨ú®ø³¡¤À¤w³Q¿z¿ïªº¿ï¶µ
¿z¿ïÁ`¦@¤À¦¨«e«á°Ï¬q¡A¤À§O¬O1~49 & 51~99¡A50ªº¦ì¸m¬O¤¤¶¡¤À®æ
»Ý¨D¬O¦b¤£¼vÅT51~99³Q¿z¿ïªº³¡¤À¡A±N1~49¥þ³¡§ï¬°"¥þ³¡"´N¬O­ì©l¥¼¿z¿ïªºª¬ºA¡A¤Ï¤§¥çµM¡C

sub ¿z¿ï«eºÝ  ()
Dim var_min, var_max,i, j, s As Integer
var_min = 1  '«e¥b¬q°_
var_max = 49 '«e¥b¬q¨´
For i = var_min To var_max
i = i + 1
Selection.AutoFilter Field:=i
Next i
End Sub

sub ¿z¿ï«áºÝ  ()
Dim var_min, var_max,i, j, s As Integer
var_min = 51  '«á¥b¬q°_
var_max = 99  '«á¥b¬q¨´
For i = var_min To var_max
i = i + 1
Selection.AutoFilter Field:=i
Next i
End Sub
¥Ñ¿ý»s¥i¥H±o¨ì¡A¤@¯ëªº¿z¿ï«ü¥O¬O
Selection.AutoFilter Field:=5, Criteria1:="W" '±N²Ä5Äæ¿z¿ï¬°W
¦ý¬O¿ï¾Ü¨ú®ø¸ÓÄæ¦ìªº¿z¿ï¡A¿ï¾Ü¥þ³¡«h¥u·|¬O
Selection.AutoFilter Field:=i
'±q¿ý»s¬Ý°_¨Ó¨S¦³µ¹«á­±³o¦ê«ü©w­È´N¬O¥þ³¡¤F
'±N¸Ó®æ¿z¿ï¬°"¥þ³¡"


©Ò¥H¦³¤F¤W­±ªºµ{¦¡½X¡A¦ý¬Ý°_¨Ó¬O¨S¦³®ÄªGªº¡C
·Q½Ð°Ý¨ú®ø¿z¿ïªº«ü¥O¬O? (Criteria1:= ?)
ÁÙ¦³¦pªG¤£¥Î°j°é¦³ª½±µ¿ï¨ú½d³òªº¤èªk¶Ü?
©Î¬O¥u¯à¥Î°j°é¡A¦³¥Î¤å¦r¦Ó«DÄæ¦ì¼Æªº¤èªk?
¹³¥Ø«e1~49¨ä¹ê¬OA~AX¡A51~99¨ä¹ê¬OAY~CU
¥i¥Hª½±µ«ü©w¤å¦r¤U¥h°j°é¶Ü?
²¦³ºª½±µ¬Ý¨ìªº¬O¤å¦r¡A¤ñ¦A¥h´«ºâ¼Æ¦rª½Æ[¡C
§@ªÌ: ikboy    ®É¶¡: 2020-10-6 10:03

¦P¤@­¶¤º­nÅã¥Ü¨âªO¶ô¿z¿ï, ­n¥ÎListObjects, ¼Ó¥D³Ì¦n±N¼ÒÀÀ®ÄªGªþ¥ó¤W¸ü¤W¨Ó¡C
§@ªÌ: wei9133    ®É¶¡: 2020-10-6 20:25

¥»©«³Ì«á¥Ñ wei9133 ©ó 2020-10-6 20:28 ½s¿è

¦^´_ 2# ikboy

¦pªþ¥ó
    [attach]32570[/attach]

ÁÙ¦³¡A§Ñ¤FÁ¿¡Aª©¥»¬O excel2003
³Â·Ð¤F!
§Ú¬O¨S¬ÝÀ´§A»¡ªº¡A§Ú¬O·Q¤£°Ê¤w³Q¿z¿ïªº³¡¤À(«ü©w½d³ò)
±N¥t¥~³Ñ¤Uªº³Q¿z¿ï³¡¤À¥þ³¡­«¸m¬°¥þ³¡ª¬ºA(¥¼³Q¿z¿ï¹Lªºª¬ºA)
§@ªÌ: ikboy    ®É¶¡: 2020-10-6 21:02

¨S¬Ý¨ì§A­n¨Dªº®ÄªG, ½Ð¤â°Ê¼ÒÀÀ¤W¨Ó¡C
§@ªÌ: wei9133    ®É¶¡: 2020-10-7 00:04

¥»©«³Ì«á¥Ñ wei9133 ©ó 2020-10-7 00:13 ½s¿è

¦^´_ 4# ikboy
[attach]32574[/attach]
ªþ¥ó¥k¥bÃä¤w¿z¿ï¡A·Q¦b¤£°Ê¨ì¥k¥bÃä¤w¿z¿ïªº±ø¥ó
±N¥ª¥bÃä³Q¿z¿ïªº±ø¥ó­«¸m¬°¥þ³¡
[attach]32573[/attach]


A ¤w¿z¿ï V
B ¤w¿z¿ï W
F ¤w¿z¿ï ªÅ®æ
W ¤w¿z¿ï W
X ¤w¿z¿ï ªÅ®æ

¤Ï¤è¦V¥çµM (¥ª¥b¿ï¨úª¬ªp¤U¡A¥k¥bÃä¥þ³¡­«¸m¬°­ì©l¥¼¿z¿ïª¬ºA)

;===============================================
[attach]32575[/attach]
[attach]32576[/attach]
¤W­±³o­ÓAY~CU­n¿z¿ïªº¤w¸g½T©w¤F
´N¬O­n§âA~AWªº¥þ³¡­«¸m¬°¥¼¿z¿ïª¬ºA
[attach]32577[/attach]
[attach]32578[/attach]
§@ªÌ: ikboy    ®É¶¡: 2020-10-7 11:24

¦b¦P¤@­¶¤Î¬Û¦P¦æ±NA-AW¦C, AY-CU¦Cµø¬°¨â­Ó¤£¦PªO¶ô§@¿z¿ï³B²z¬O¤£¦æªº, ²³æªºÁ|¨Ò: A2¿z¿ï«á¬OÅã¥Ü¸ê®Æ,AY2¿z¿ï«á¬O¤£Åã¥Ü¸ê®Æ, ¨º²Ä2¦æ¨ì©³­nÅã¥Ü©ÎÁôÂÃ!!
§é°J¿ìªk¤À­¶, ¨Ã±Æ¡Cªþ¥ó§Ú¤â°Ê°µªº, ¬Ý¬Ý³o«ä¸ô¦æ¤£¡C
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-7 12:28

¦^´_ 6# ikboy
½Ð°Ý§Aªº·N«ä¬O ¿z¿ï«áªºµ²ªG¦A¿z¿ï¤@¦¸©ÎµÛ§ó¦h¦¸¶Ü?
§@ªÌ: ikboy    ®É¶¡: 2020-10-7 13:23

¦^´_  ikboy
½Ð°Ý§Aªº·N«ä¬O ¿z¿ï«áªºµ²ªG¦A¿z¿ï¤@¦¸©ÎµÛ§ó¦h¦¸¶Ü?
°a¤ªºµ µoªí©ó 2020-10-7 12:28



    ½Ð¬Ý¼Ó¥D¦b5#¦^ÂÐ, ¥Ñ¨ä¬OA~AW »PAY~CU¨º¬q,§A·|©ú¥Õ¤F¡C
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-7 14:12

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-7 14:15 ½s¿è

¦^´_ 1# wei9133

¨Ã±ÆÅã¥Ü¶Ü?
  1. Sub ««ª½¨Ã±ÆÅã¥Ü¥[¿z¿ï()
  2.     Application.ScreenUpdating = False
  3.     ActiveWindow.NewWindow '¶}±Ò¦h­Ó¬Û¦P¤u§@ï
  4.     Windows.Arrange ArrangeStyle:=xlVertical '««ª½¨Ã±ÆÅã¥Ü
  5.    
  6.     Windows("zz.xls:1").Activate '¤Á´«¨ì¤u§@ªí2
  7.     Sheets(2).Cells(51, 1).AutoFilter '¿z¿ï¤u§@ªí2
  8.     Sheets(2).Select 'ÂI¿ï¤u§@ªí2

  9.     Windows("zz.xls:2").Activate '¤Á´«¨ì¤u§@ªí1
  10.     Sheets(1).Cells(1, 1).AutoFilter '¿z¿ï¤u§@ªí1
  11.     Application.ScreenUpdating = True
  12. End Sub
½Æ»s¥N½X

§@ªÌ: wei9133    ®É¶¡: 2020-10-7 19:26

¦^´_ 9# °a¤ªºµ
§A¦n¡A§Ú¨S¦³´ú¸Õ§Aªº¥¨¶°
¤£¹L¬Ý´Nª¾¹D¤£¹ï¡A¦]¬°ªF¦è¬O¦b¦P¤@­Ó¬¡­¶Ã¯¸Ì
¥u¬O¤À¦¨«e­±¸ò«á­±¦Ó¤w
¸Ô²Ó¥i¥H¬Ý¬Ý¤W­±ªºªþ¥ó
À³¸Ó³o¼ËÁ¿
°²³]¦³A~ZÄæ (¹ê»Ú¤W§ó¦h)
§âA~L¥þ³¡¿z¿ï¦n¤F
­n§âM~Zªº¿z¿ï±ø¥ó¥þ³¡²MªÅ¬°¹w³]
¤j·§´N¥u¬O³o¼Ë¦Ó¤w
§@ªÌ: wei9133    ®É¶¡: 2020-10-7 19:56

¥»©«³Ì«á¥Ñ wei9133 ©ó 2020-10-7 20:00 ½s¿è

¦^´_ 6# ikboy

¤£¹ï¡A§Úª½±µÁ¿§Ú­nªºµ²ªG
¨ä¹ê­ì¥»´N¬O­n§â©Ò¦³¬Û¦Pªº¦æ¼Ð¥Ü¥X¨Ó¡AµM«á¥[Á`°_¨Ó
§Ú¨S¦³­n©î¶}¥Lªº·N«ä
¨C¤@¦æ³£¬O¤@­Ó¹ï¾Ô¬ö¿ý(¦bªþ¥óCZ¦æ¦³¬P¶H)
©Ò¥H­n¤ñ¹ïªº¨ä¹ê¬O¡A¬O§_¦³A~CV³£ªø±o¤@¼Ëªº¦C(»Ý­n¥]§tCZ)¡A¦]¬°¤£¦P¬P¶H¤]·|¦³A~CV·Qªº¤@¼Ëªºª¬ºA¥X²{
¤£¹L¦]¬°ÁÙ¦³CW~CY¬O¤£·|¬Û¦Pªº­È¡A©Ò¥HµLªkª½±µ¤ñ¹ï
²{¦b§Ú¬O¤â°Ê§âA~AWªº¬D¿ï¥X¨Ó«á¡A¦A¥h¤@­Ó¤@­Ó§âAY~CU¿z¿ï¥X¨Ó¡A¤@¼Ëªº¦X¨Ö(³s¬P¶H³£¤@¼Ëªº³¡¤À)¡AµM«á³Ó³õ+1
©Ò¥H·Q­n§Ú¦b©T©wA~AW¿z¿ï±ø¥óªºª¬ªp¤U¡A¥h­«¸mAY~CUªº¿z¿ï±ø¥ó


ÁÙ¬O¬Ý¤£À´ªº³Â·Ð²¾¾r¼v¤ù
https://sendvid.com/kvj69nqz
¶W³s§Ë¤£¥X¨Ó¡A½Ð¦Û¤v½Æ»sºô§}¨ìºô§}¦C§a
¹ê»Ú¬O´N¬O­n³Ì«á´X¬í¨º­Ó°Ê§@¦Ó¤w
¥u¬O¤£¥uÂIªº¨º´X­Ó¡A¦Ó¬O«á¤è¥þ³¡³£ÂI¦¨¥þ³¡
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-7 20:06

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-7 20:12 ½s¿è

¦^´_ 10# wei9133

A~L ­ì¥»ªº¸ê®Æ®æ¦¡­n«O¯d¶Ü?  ÁÙ¬O¥u«O¯d¿z¿ï«áªº?
M~Z ¤£°µ¥ô¦ó°Ê§@?
³o¼Ëªº¸Ü A~L ªº®æ¦¡¸ê®Æ·|§ïÅÜ µLªk´_­ì ¦]¬°¬O ¥ý§R°£   A~L ªº¸ê®Æ ¦b¶K¤W¿z¿ï«áªº¸ê®Æ  ¦pªG¬O¦b¦P¤@­Ó¬¡­¶Ã¯¸Ì
©ÎµÛ ª½±µ·s¼W¤@­Ó ·sªº ¤u§@ªí¦b§âµ²ªG¶K¤W ?
§Ú¬Ý¤@¤U ¼v¤ù¦n¤F >"<
§@ªÌ: wei9133    ®É¶¡: 2020-10-8 00:45

¦^´_ 12# °a¤ªºµ
§A¦n
¦b³Ìªìªº»Ý¨D¤¤§Ú¨S¦³­n§R¥ô¦ó¸ê®Æ
§Ú¬O­n¤ñ¹ï¬O§_¦³¬Û¦Pªº¸ê®Æ(¦C)
¤~­n©T©w[¥ª|¥k]¤@Ã䪺¤w¿z¿ï±ø¥ó


À³¸Ó«ç»ò»¡¤~¯àÅý§A²z¸Ñ©O0.0a

A~L ­ì¥»ªº¸ê®Æ®æ¦¡­n«O¯d¶Ü?  ÁÙ¬O¥u«O¯d¿z¿ï«áªº?
M~Z ¤£°µ¥ô¦ó°Ê§@?


A~L¤w¸g¿z¿ï¹L¤F¡A³£¤£­n°Ê¥¦
M~Z¨C¤@­Ó³£§â¿z¿ï¿ï³æ©Ô¶}¨ÓÂI"¥þ³¡"
[attach]32583[/attach]

§Úı±o§Ú­Ì·¾³qªº°ÝÃDÀ³¸Ó¥X¦b³o­Ó"¥þ³¡"¤W­±...

·PÁ§Aªá®É¶¡À°§Ú·Q¸Ñ¨M¤è®× m(_ _)m
§@ªÌ: jcchiang    ®É¶¡: 2020-10-8 09:04

¦^´_ 13# wei9133

¼Ó¥D»Ý¨Dªº¿z¿ï¼Ò¦¡¦b¦P¤@sheet¬OµLªk°õ¦æªº(ikboy¤w¸g»¡©ú¤F)
¦pªG¥u¬O­n­pºâ¦U¬P¶H³Ó²v/±Ñ§½«Øij¥i¥Î¤ñ¹ïªº
¥u¬OµLªk¬Ý¥X³o2¦U°Ï¶ô(A~AW)¤º¸ê®Æªº¬ÛÃö©Ê&³Ó²v/±Ñ§½­pºâ¤è¦¡??
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-8 12:24

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-8 12:31 ½s¿è

¦^´_ 13# wei9133
³o¤£¬O¥Î¿z¿ï ¦Ó¬O¥Î¤ñ¹ïªº¤è¦¡  
§A¬Ý¤@¤U¬O¤£¬O³o¼Ë?
  1. Public Sub ¤ñ¹ï½m²ß()
  2.     Application.ScreenUpdating = False
  3.     Dim A, B, i, k
  4.     k = 1
  5.     E = 345
  6.     For X = 1 To Cells(1, 1).End(xlDown).Row
  7.         A = Range(Cells(X, 49), Cells(X, 1))
  8.         B = Range(Cells(X, 99), Cells(X, 51))
  9.         
  10.         For i = 1 To UBound(B, 2)
  11.         If A(1, k) = "" Then A(1, k) = "-"
  12.         If B(1, i) = "" Then B(1, i) = "-"
  13.              f = f & A(1, k)
  14.              r = r & B(1, i)
  15.             If k <= UBound(A, 2) Then k = k + 1
  16.         Next i
  17.         
  18.         k = 1
  19.         
  20.         If f = r Then
  21.            Cells(E, 1).Resize(1, 104) = Cells(X, 1).Resize(1, 104).Value
  22.            E = E + 1
  23.         End If
  24.         
  25.         f = "": r = ""
  26.     Next X
  27.     Application.ScreenUpdating = True
  28. End Sub
½Æ»s¥N½X

§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-8 23:20

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-8 23:21 ½s¿è

¦^´_ 13# wei9133

³o¬O­×§ï¹Lªº  ¶¶§Ç¬O 1~49 »P 51~99 ¬Û¦P  ³Ó²v=1 -> ¿ý»sªº±Æ§Ç->¿ý»sªº§R°£­«½Æ
¿ý»sªº§R°£­«½Æ¦³ÂI©Ç©Çªº ¤£¹LÁÙ¬O¥i¥H¥Î §Ú§ä¤£¨ì­ì¦] §R°£«á ®æ¦¡ÁÙ¬O·|¦s¦b¦ý¼Æ­È¤å¦r¤w³Q§R°£
­×§ï½d³ò«á §R°£ªº¦C¦ì¤£·|©¹¤W¸É >"< «á¨Ó¤S§ï¦^¨Ó...¤£ª¾¹D¬°¬Æ»ò..¨þ
¼v¤ù¬Ý°_¨Ó¬O³o¼Ë ¤£ª¾¹D¬O¤£¬O§A­nªº §Ú¤]¬O¶¶«K½m²ß
  
javascript:;
§@ªÌ: wei9133    ®É¶¡: 2020-10-9 00:30

¦^´_ 14# jcchiang

¨ä¹ê§Ú¹ê»Ú¤W´N¬O¤ñ¹ï¡A¤£¹L¬O¤H¤u¤ñ¹ï
©Ò¥H¤~·|·Q­n©T©w¤@Ã䪺¿z¿ï«á±ø¥ó¡A±N¥t¥~¥bÃä¥þ³¡ÂI¦^­ì©l¥¼³Q¿z¿ïªºª¬ºA
³o¼Ë¤è«K¬Ý¥t¤@Ãä¬O§_¦³¤@¼Ëªº²Õ¦X
³o±i¹Ïªí¸Ì­±Åã¥Ü¤£¥X±Ñ§½¡A¦]¬°±Ñ§½¬O¬ö¿ýªº®É­Ôµo²{¿é¤F¤â°Ê¶ñ¤Wªº
©Ò¥H¹ê»Ú¤ñ¹ï¥Ø¼ÐÀ³¸Ó¬O
A~CV³£ªø±o¤@¼Ò¤@¼Ë¥BCZ¤]¤@¼Ëªº³¡¤À
A~CV¤À§OÀ³¹ï³Ó±Ñ¤èªº¥X³õ¤Hª«¡A¦ÓCZ¬O¬P¶H(³o­Ó°Ñ¼ÆÂù¤è¦@¥Î)
A~AW¬O±Ñ¤è²Õ¦X¡AAY~CU¬O±Ñ¤è²Õ¦X
AX¸òCV³o¨â®æ¥X¾Ô¤H¼Æ¬O³Ó±Ñ¤èªº¥X¾Ô¤H¼Æ¡A¥H½T«O¥¼²Î­p¿ù»~
(¥¿±`¬°5¡A­Y¿ù¤F¦³¥Î®æ¦¡¤Æ¼Ð¥X¥Hµo²{¿é¤J¿ù»~)

¥Ø«e§Ú¤H¤u²Î­pªº¬yµ{¬O
¥ý©T©w¥ªÃ䪺¿z¿ï±ø¥ó¡A³o¼Ë¿z¿ï¥X¨Óªº±Ñ¤è¨¤¦â´N³Q©T©w¤F (¤Ï¤§´N¬O©T©w³Ó¤è¿z¿ï±ø¥ó¦A¥h¬D¿ï±Ñ¤è¨¤¦â)
¦A±q¥kÃä¬D¿ï±ø¥ó¿z¿ï¡Aµo²{¨â±øªøªº¤@¼Ò¤@¼Ëªº(A~CV & CZ)¡A½T»{¤@¤U³Æµù(CX)ªºÃC¦â¶¥¯Å
(¦³¥i¯àA~CV³£¤@¼Ë¦ýZC¬O¤£¦Pªº)
[attach]32588[/attach]
(²{¦b¬Ý°_¨Óµu¬O¦]¬°ÁôÂäF¤¤¶¡ªº³¡¤À)
µM«á§â³o´X±ø¤@¼Ëªº³Ó³õ(CW)±Ñ³õ(CY)¥[°_¨Ó¶ñ¨ì­n¯d¤Uªº¨º¤@±øªº³Ó³õ(CW)±Ñ³õ(CY)Äæ¦ì
(³o­Ó®É­Ô§Ú·|¬Ý¤@¤U³Æµù¨M©w­n¯d­þ¤@±ø)
µM«á§â¯d¤U¤§¥~ªº¨ä¥L±ø§R°£¡C
§@ªÌ: wei9133    ®É¶¡: 2020-10-9 00:46

¦^´_ 16# °a¤ªºµ
§Ú¨ä¹ê¤]¨S¬ÝÀ´§AªºªF¦è¡A¥u¦³¤@­Ó¬¡­¶Ã¯ªºªF¦è«ç»òÅܦ¨¨â­Ó¤F
¦Ó¥B§Ú°õ¦æ¥¦ÁÙ³ø¿ù¤F
[attach]32590[/attach]
[attach]32589[/attach]

§Ú­n¤ñ¹ïªº©l²×¦b¤@­Ó¬¡­¶Ã¯¸Ì
¥Ø«e¬O¤H¤u¬D¿ïA~CV³£ªø±o¤@¼Ò¤@¼Ë¥BCZ¤]¤@¼Ëªº³¡¤À
µM«á¥[Á`±Ñ³õ¸ò³Ó³õ¼Æ¶ñ¤J¨ä¤¤¤@¦Cªº¸Ó®æ¤¤¯d¦s
³Ñ¤U§R°£
§@ªÌ: wei9133    ®É¶¡: 2020-10-9 01:06

¥»©«³Ì«á¥Ñ wei9133 ©ó 2020-10-9 01:16 ½s¿è

¦^´_ 17# wei9133

§Ú§â§Ú¨ãÅ骺¾Þ§@¿ý¦¨¼v¤ù¤F¡A¬Ý¤@¤U©Î³\¯àÀ´?

https://sendvid.com/suu270r2

¨ä¤¤¾ãÄ檽±µ¿z¿ï¦¨V©ÎWªº³¡¤À¦]¬°§Ú¤w¸g¿ý¦¨¥¨¶°©Ò¥H¨S¦³¥h©Ô¤U©Ô¦¡¿ï³æ
  1. Sub ¿z¿ï¬°V() '^U
  2. Dim i As Integer
  3. i = ActiveCell.Column 'Àò¨úÄæ¦ì­È
  4. Cells(1, i).Select
  5.     Selection.AutoFilter Field:=i, Criteria1:="V"
  6.     '±N¸Ó®æ¿z¿ï¬°"V"
  7. End Sub
  8. Sub ¿z¿ï¬°W() '^I
  9. Dim i As Integer
  10. i = ActiveCell.Column 'Àò¨úÄæ¦ì­È
  11. Cells(1, i).Select
  12.     Selection.AutoFilter Field:=i, Criteria1:="W"
  13.     '±N¸Ó®æ¿z¿ï¬°"W"
  14. End Sub
  15. Sub ¿z¿ï¬°ªÅ®æ() '^B
  16. Dim i As Integer
  17. i = ActiveCell.Column 'Àò¨úÄæ¦ì­È
  18. Cells(1, i).Select
  19.     Selection.AutoFilter Field:=i, Criteria1:="="
  20.     '±N¸Ó®æ¿z¿ï¬°"ªÅ®æ"
  21. End Sub
  22. Sub ¿z¿ï¬°«DªÅ®æ() 'O
  23. Dim i As Integer
  24. i = ActiveCell.Column 'Àò¨úÄæ¦ì­È
  25. Cells(1, i).Select
  26.     Selection.AutoFilter Field:=i, Criteria1:="<>"
  27.     '±N¸Ó®æ¿z¿ï¬°"«DªÅ®æ"
  28. End Sub

  29. Sub ¿z¿ï¬°¥þ¿ï() '^+Q
  30. Dim i As Integer
  31. i = ActiveCell.Column 'Àò¨úÄæ¦ì­È
  32. Cells(1, i).Select
  33.     Selection.AutoFilter Field:=i ' Criteria1:="<>" ¡A¬Ý°_¨Ó¨S¦³µ¹«á­±³o¦ê«ü©w­È´N¬O¥þ³¡¤F
  34.     '±N¸Ó®æ¿z¿ï¬°"¥þ³¡"
  35. End Sub
½Æ»s¥N½X
¤@¶}©l§Ú»Ý­nªº¥\¯à»Ý¨D¨ä¹ê¬O³o­Ó
¦b§Ú¿ï§¹¬Y¤@Ã䤧«á¡A§â¥t¤@Ãä¥þ³¡¥Ñ¿z¿ï¤U©Ô¦¡¿ï³æ¿ï¦^"¥þ¿ï"

https://sendvid.com/3ifimegq

©Ò¥H¤~·|¥X²{³oºØªF¦è
  1. sub ¿z¿ï«áºÝ  ()
  2. Sub ¿z¿ï«áºÝ()
  3. Dim var_min, var_max, i, j, s As Integer
  4. var_min = 51  '«á¥b¬q°_
  5. var_max = 99  '«á¥b¬q¨´
  6. For j = var_min To var_max
  7. j = j + 1
  8. Cells(1, j).Select
  9. Selection.AutoFilter Field:=j
  10. Next j
  11. End Sub
½Æ»s¥N½X
¦ý¬O³o²Õ¥N½X¦n¹³¨S¥Î¡A©Ò¥H¤~·|¨Ó°Ý
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-9 09:52

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

¦^´_ 19# wei9133

§A­n¥ý§â ¤u§@ªí2 §R°£ ¦A·s¼W¤@­Ó ¤u§@ªí2¦b°õ¦æ     ¤u§@ªí2 ¬O½Æ»sªº¤º®e §Ú¨S¦³°Ê¤u§@ªí1ªº¤º®e  °õ¦æ Module1
  1. Public Sub ¦h¦C¤ñ¹ï½m²ß()
  2.     Sheets(2).Select
  3.     Rows("2:2").Select
  4.     ActiveWindow.FreezePanes = False 'Ãö³¬­áµ²µøµ¡
  5.     Application.ScreenUpdating = False
  6.     Call ¥¨¶°1 '¿ý»sªº±Æ§Ç
  7.     k = 1

  8.     For X = Cells(1, 1).End(xlDown).Row To 2 Step -1
  9.         A = Range(Cells(X, 49), Cells(X, 1)) '§â1~49¤º®e ©ñ¨ì°}¦C
  10.         B = Range(Cells(X, 99), Cells(X, 51)) '§â51~99¤º®e ©ñ¨ì°}¦C
  11.         
  12.         For I = 1 To UBound(B, 2) '¦êÁp "-" ¸¹¤è«K¤ñ¹ï
  13.             If A(1, k) = "" Then A(1, k) = "-"
  14.             If B(1, I) = "" Then B(1, I) = "-"
  15.             f = f & A(1, k)
  16.             r = r & B(1, I)
  17.             If k <= UBound(A, 2) Then k = k + 1
  18.         Next I
  19.         
  20.         k = 1
  21.         
  22.         If f = r And f <> "-" Then '­Y1~49 »P 51~99 ¬Û¦P ´N¦b ³Ó²vÄæ¦ì ¿é¤J"1"¨Ã¤Ï¶À¦â
  23.             Cells(X, 101) = "1"
  24.             Cells(X, 101).Interior.Color = RGB(255, 255, 0)
  25.         End If
  26.         
  27.         f = "": r = ""
  28.     Next X

  29.     Call ¥¨¶°2 '¿ý»sªº§R°£­«½Æ
  30.     Application.ScreenUpdating = True
  31.     Rows("2:2").Select
  32.     ActiveWindow.FreezePanes = True  '¶}±Ò­áµ²µøµ¡


  33. End Sub
½Æ»s¥N½X

§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-12 04:15

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-12 04:24 ½s¿è

¦^´_ 19# wei9133
§â ³Ó²v ¸ò ±Ñ§½ §ï¬°¦³­«½Æ´N¥[1 ¦³ªÅ¦AÀ°§Ú¬Ý¬Ý ¬O¤£¬O³o¼Ë ÁÂÁÂ
  1. Public Sub ¦h¦C¤ñ¹ï½m²ß()
  2. Application.ScreenUpdating = False
  3. Dim Arr, i&, t&, A1$, A2$
  4. Arr = Range(Sheets(3).Cells(Rows.Count, 1).End(xlUp), Sheets(3).Cells(2, 100))
  5. ReDim A(LBound(Arr) To UBound(Arr))
  6.     '¦h¦C¤ñ¹ï
  7.     For t = 1 To UBound(Arr, 1)
  8.         For i = UBound(Arr, 1) To t + 1 Step -1
  9.         A1 = ¦êÁp_¤å¦r(Application.WorksheetFunction.Index(Arr, t, 0))
  10.         A2 = ¦êÁp_¤å¦r(Application.WorksheetFunction.Index(Arr, i, 0))
  11.         A1 = A1 & Cells(t + 1, 104)
  12.         A2 = A2 & Cells(i + 1, 104)
  13.         If A1 = A2 Then
  14.             Cells(t + 1, 101) = Cells(t + 1, 101) + 1
  15.             Cells(i + 1, 101) = Cells(i + 1, 101) + 1
  16.             Cells(t + 1, 103) = Cells(t + 1, 103) + 1
  17.             Cells(i + 1, 103) = Cells(i + 1, 103) + 1
  18.             Cells(t + 1, 104).Interior.Color = RGB(255, 255, 0)
  19.             Cells(i + 1, 104).Interior.Color = RGB(255, 255, 0)
  20.         End If
  21.         Next i
  22.     Next t
  23.     '§R°£­«½Æ
  24.     For X = 2 To Cells(2, 1).End(xlDown).Row
  25.         For Y = Cells(2, 1).End(xlDown).Row To X + 1 Step -1
  26.             If Cells(X, 104).Interior.Color = RGB(255, 255, 0) _
  27.             And Cells(Y, 104).Interior.Color = RGB(255, 255, 0) Then
  28.             If Cells(X, 104) = Cells(Y, 104) Then
  29.                Rows(Y).Delete
  30.             End If
  31.         End If
  32.         Next Y
  33.     Next X
  34. Application.ScreenUpdating = True
  35. End Sub
  36. Public Function ¦êÁp_¤å¦r(A)
  37.         f = ""
  38.         For i = 1 To UBound(A)
  39.             If A(i) = "" Then A(i) = "-"
  40.             f = f & A(i)
  41.         Next i
  42.         ¦êÁp_¤å¦r = f
  43. End Function
½Æ»s¥N½X

§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-12 05:30

¦^´_ 18# wei9133

­è¤~µo²{¶]¤Ó¤[¤F ©Ò¥H§ï¤F¤@¤U ¦³¤ñ¸û¦n¤@ÂI ¦ý¬OÁÙ¬O«ÜºC
  1. Public Sub ¦h¦C¤ñ¹ï½m²ß()
  2. Application.ScreenUpdating = False
  3. Dim Arr, i&, j&, t&, tj&, x&, y&, T1$, T2$, T3$, T4$
  4. Arr = Range(Cells(Rows.Count, 1).End(xlUp), Cells(2, 100))
  5.     For t = 1 To UBound(Arr, 1)
  6.             TI = "": T2 = ""
  7.             For tj = 1 To UBound(Arr, 2)
  8.                 If Arr(t, tj) = "" Then
  9.                    Arr(t, tj) = "-"
  10.                    Arr(t, tj) = Arr(t, tj) & Arr(t, tj)
  11.                 End If
  12.                 T1 = Arr(t, tj)
  13.                 T2 = T2 & T1
  14.                 If tj = UBound(Arr, 2) Then T2 = T2 & Cells(t + 1, 104)
  15.             Next tj
  16.         For i = UBound(Arr, 1) To t + 1 Step -1
  17.             T3 = "": T4 = ""
  18.             For j = 1 To UBound(Arr, 2)
  19.                 If Arr(i, j) = "" Then
  20.                    Arr(i, j) = "-"
  21.                    Arr(i, j) = Arr(i, j) & Arr(i, j)
  22.                 End If
  23.                 T3 = Arr(i, j)
  24.                 T4 = T4 & T3
  25.                 If j = UBound(Arr, 2) Then T4 = T4 & Cells(i + 1, 104)
  26.             Next j
  27.             If T4 = T2 Then
  28.                 Cells(t + 1, 101) = Cells(t + 1, 101) + Cells(t + 1, 101)
  29.                 Cells(i + 1, 101) = Cells(i + 1, 101) + Cells(i + 1, 101)
  30.                 Cells(t + 1, 103) = Cells(t + 1, 103) - Cells(t + 1, 103)
  31.                 Cells(i + 1, 103) = Cells(i + 1, 103) - Cells(i + 1, 103)
  32.                 Cells(t + 1, 104).Interior.Color = RGB(255, 255, 0)
  33.                 Cells(i + 1, 104).Interior.Color = RGB(255, 255, 0)
  34.             End If
  35.         Next i
  36.     Next t
  37.     For x = 2 To Cells(2, 1).End(xlDown).Row
  38.         For y = Cells(2, 1).End(xlDown).Row To x + 1 Step -1
  39.             If Cells(x, 104).Interior.Color = RGB(255, 255, 0) _
  40.             And Cells(y, 104).Interior.Color = RGB(255, 255, 0) Then
  41.             If Cells(x, 104) = Cells(y, 104) Then
  42.                Rows(y).Delete
  43.             End If
  44.         End If
  45.         Next y
  46.     Next x
  47. Application.ScreenUpdating = True
  48. End Sub
½Æ»s¥N½X

§@ªÌ: wei9133    ®É¶¡: 2020-10-15 06:54

¥»©«³Ì«á¥Ñ wei9133 ©ó 2020-10-15 06:56 ½s¿è

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

§A¦n¡A¦]¬°«á­±³o­Ó¤w¸gÅܦ¨¤ñ¹ï¤F¡A©Ò¥H¸ò¤@¶}©lªº§@ªk¤w¸g¤£¤@¼Ë
µM«á³o¨â¤Ñ¤S¼W¥[·s­^¶¯¡A©Ò¥H®æ¤l¤S¤£¤@¼Ë¤F..

¦Ó¤ñ¹ïªº³¡¤À¦]¬°«á­±ÁÙ¥[¤W¾ã¦X¦¨¤@¦æªº³¡¤À
¨º´N¤S¥²¶·¦Ò¼{¨ì´X­Ó±ø¥ó
1.A~CX(±Ñ³ÓÂù¤è¥X°}) & DB(¬P¶H)ªøªº¤@¼Ò¤@¼Ëªº¦C
2.¦³¨Ç¤ñ¸û±`¥X²{ªº²Õ¦X·|¦³¼È¦sªº¿z¿ï§Ö±¶¡A¸Ì­±¤]¦³­Èªº
  ¨ãÅé¬OCZ(³Æµù)DC¸òDE¦³Âù¤è¥X°}ªºÂ²¼g¤è«K¿z¿ï«á§Ö³t¿ëÃÑ
  (¬Ý¤£À´ªº¸Ü°õ¦æ "Sub ¼È¦s¿z¿ï()"§Ö±¶Áä ctrl+L¡A·|¤ñ¸û¦n²z¸Ñ)
  ©ÎµÛ³o¼ËÁ¿§PÂ_ ¤W­±³o¤T­Ó¨ä¤¤¤@­Ó¦³­Èªº¡A¦X¨Öªº°ò¥»´N¬O¥¦¡A§â¨ä¥Lªº³Ó±Ñ³õ³£¥[¨ì³o¤@¦C¤W­±
  
3.©Ò¥H¬O§â²Å¦X"±ø¥ó¤@"ªº¦C¥h¤ñ¹ï¡A¥þ³¡¥[¦b²Å¦X"±ø¥ó¤G"ªº¦C¤W­±¡AµM«á§â¸Ó¦Cªº³Ó±Ñ³õ³£¥[°_¨Ó¶ñ¦b¦U¦Ûªº³Ó±Ñ³õ
  ­n³Q¦X¨Öªº³Ó³õ¸ÓÄæ¦ì¼Æ¦r­n¥[1¡A¦]¬°¥X²{³o¤@¦C¥»¨­´N¬O³Ó¤F¤@³õ¡A¦h³Ó¤@³õ¤~·|¦b¦b³Ó³õ¼Æ¤W¥[¤W¼Æ¦r
  ¦Ó±Ñ³õ«h§_
  Ä´¦p
  ¦@¦³¤T¦C²Å¦X¤W­z©Ò¦³±ø¥ó A~CX¬Û¦P¡BDB¬Û¦P
  ¦Ó³Ó³õ³¡¥÷ªº­È«h¤À§O¬°3¡BªÅ®æ¡B1¡A±Ñ³õªº­È«h¬°1¡BªÅ®æ¡BªÅ®æ
  ºâ¥X¨Ó¦X¨Öªº³Ó³õÄæ¦ìÀ³¬°"6"¡A±Ñ³õ«h¬°"1"
  ºâªk¬O³o¼Ëªº¡A°²³]3¨º®æ«O¯d¡A¦ÓªÅ®æ¥Nªí³Ó1³õ¡A³Ó³õ¶ñ¤J1ªº¹ê»Ú¤W¬O"·í¦C"¥["³Ó1³õ"
  ©Ò¥H¥[¥X¨Ó¬O"6"

https://mega.nz/file/7dQmCLjI#QCQsI9fga6rZlsXJYLfrfaGz2ZUVmFLaCXtTOLFNvqI
§Úª½±µªþ¤WÀɮסA§A¥Î¦¹ÀÉ®×´ú¸Õ§Y¥i¡A¦³­ì©lÀɮפ]¦³§U©ó§A²z¸Ñ§Ú»¡ªº¨ì©³¬O¬Æ»ò
·íµM¦pªG¦³§ó¦nªº¤èªk¤]½Ð´£¥X
¦Ü©ó¦pªG¦³©¯¦³¼g¥Xvbaµ{¦¡½Xªº¸Ü
·Ð½Ðª½±µ¦^ÂЩ󩫤l¤§¤¤
(¦]¬°«áÄòÁÙ·|¦³·s­^¶¯¥[¤J¡A©Ò¥H³Â·ÐÀ°§Úµù¸Ñ­Y®æ¤l©¹«á±Àªº¸Ü­n§ï­þ­Ó¦a¤è)


¤W­±Àɮפ¤¤w¦³¥¨¶°¡A¦p¦³ºÃ¼{¥i¥H¤£¶}±Ò
¥¨¶°¥\¥Î¤À§O¬°Module2 ¶î©³¦â(®æ¦¡¤Æ±ø¥ó)
Module1 ¿z¿ï(V|W|ªÅ®æ|«DªÅ®æ|¨ú®ø¿z¿ï)/¦r«¬ÃC¦â¥[²Ê

P.S.­nª`·Nª©¥»¥Îªº¬OEXCEL 2003¡A§Ú¤£²M·¡VBAªºª©¥»¬O§_³q¥Î¡C
§@ªÌ: jcchiang    ®É¶¡: 2020-10-16 08:39

¦^´_ 23# wei9133

1.megaªºÀɮצ]¤½¥q­­¨îµLªk¤U¸ü,©Ò¥H¨Ï¥Î«e­±ªºÀɮפº®e¼g
2.A~CV+¬P¶H ¬Û¦Pªº¦C²Î­p¨ä³Ó³õ&±Ñ§½(¨Ï¥ÎÀɮפºÄæ¦ìªº­È²Ö­p)
3.«O¯dªº¦C³Ó³õ¦h¥[1
4.³Ó³õ¬°ªÅ¥Õªº¶ñ¤J1,µø³Ó³õ¬°1
5.µ{¦¡µL°õ¦æ§R°£¦Cªº°Ê§@,±N¸ê®Æ¦C¦b[a400]¦ì¸m
¥H¤W¬O§Ú¯à²z¸Ñªº³¡¥÷

Sub ex3()
Dim d As Object, ar As Object, r As Object
Dim i%, AA$, a

Set d = CreateObject("Scripting.Dictionary")
Set ar = [A1].CurrentRegion

For i = 1 To ar.Rows.Count
   AA = Join(Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 100))), ",") & "," & ar(i, 104) '«Ø¥ß§PÂ_±ø¥ó
   If ar(i, 101) = "" Then ar(i, 101) = 1 '³Ó³õªÅ¥Õ¶ñ¤J1
   If Not d.exists(AA) Then   '¦r¨å¤º¬dµL¸Ó±ø¥ó
      d(AA) = ar(i, 1).Resize(, 104) '¼W¥[¦r¨å¸ê®Æ
   Else
      a = Application.Transpose(Application.Transpose(d(AA)))   '±N¦r¨å¸ê®Æ¨ú¥X
      a(101) = a(101) + ar(i, 101) '³Ó²v²Ö¥[
      a(102) = a(102) + 1           '³Æµù:¬Û²Åªºµ§¼Æ(¤£¥]§t²Ä¤@µ§)
      a(103) = a(103) + ar(i, 103) '±Ñ§½²Ö¥[
      d(AA) = a   '±N¸ê®Æ©ñ¦^¦r¨å
   End If
Next
[a400].Resize(d.Count, 104) = Application.Transpose(Application.Transpose(d.items)) '±N¦r¨å¸ê®Æ¦C¥X
For Each r In Range([cw401], [cw401].End(4))  '«O¯d³Ó³õ+1
   r.Value = r.Value + 1
Next
Set d = Nothing
End Sub
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-16 19:23

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-16 19:26 ½s¿è

¦^´_ 23# wei9133

ÀÉ®×µLªk¶}±Ò ¬Ý­n¤£­n¦A¤W¶Ç¤@¦¸ ¤£¥Î¥þ³¡ ¦³³¡¤À ¥i¥H´ú¸Õ ´N¥i¥H¤F
§A¥ý¬Ý¬Ý jcchiang«e½ú  ¼gªº¬O¤£¬O§A­nªºÅÞ¿è   ¦]¬°jcchiang«e½úªº¼gªk³t«×·|§Ö«Ü¦h

javascript:;
§@ªÌ: wei9133    ®É¶¡: 2020-10-18 02:15

¦^´_ 24# jcchiang

¦^´_ 24# jcchiang

¨â¦ì¦n
­º¥ý¥ý·PÁ¤G¦ìªá®É¶¡À°§Ú·Q¿ìªk

¦Ü©óªÅ¶¡ªº³¡¤À
§Ú­«·s´«­ÓªÅ¶¡
googl
https://drive.google.com/file/d/1VFZMILovCRpQT6AGyaAd8ZNb9VAZSFlO/view?usp=sharing
onedrive
https://1drv.ms/u/s!Amaq2OY73W7WiSHXDt88TA7rsUgC?e=BmwuJh

ªþ¥ó:§Ú¦³¬åªF¦è¦]¬°½×¾Â¥uµ¹1MB¡A¤£¹L¥Î¨Ó²z¸Ñ»Ý¨DÀ³¸Ó°÷
[attach]32632[/attach]
  1. Sub ex3()
  2. Dim d As Object, ar As Object, r As Object
  3. Dim i%, AA$, a

  4. Set d = CreateObject("Scripting.Dictionary")
  5. Set ar = [A1].CurrentRegion

  6. For i = 1 To ar.Rows.Count
  7.    AA = Join(Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 102))), ",") & "," & ar(i, 106) '«Ø¥ß§PÂ_±ø¥ó
  8.    If ar(i, 103) = "" Then ar(i, 103) = 1 '³Ó³õªÅ¥Õ¶ñ¤J1
  9.    If Not d.exists(AA) Then   '¦r¨å¤º¬dµL¸Ó±ø¥ó
  10.       d(AA) = ar(i, 1).Resize(, 106) '¼W¥[¦r¨å¸ê®Æ
  11.    Else
  12.       a = Application.Transpose(Application.Transpose(d(AA)))   '±N¦r¨å¸ê®Æ¨ú¥X
  13.       a(103) = a(103) + ar(i, 103) '³Ó²v²Ö¥[
  14.       a(104) = a(104) + 1           '³Æµù:¬Û²Åªºµ§¼Æ(¤£¥]§t²Ä¤@µ§)
  15.       a(105) = a(105) + ar(i, 105) '±Ñ§½²Ö¥[
  16.       d(AA) = a   '±N¸ê®Æ©ñ¦^¦r¨å
  17.    End If
  18. Next
  19. [a14000].Resize(d.Count, 106) = Application.Transpose(Application.Transpose(d.items)) '±N¦r¨å¸ê®Æ¦C¥X
  20. For Each r In Range([cw14001], [cw14001].End(4))  '«O¯d³Ó³õ+1
  21.    r.Value = r.Value + 1
  22. Next
  23. Set d = Nothing
  24. End Sub
½Æ»s¥N½X
¦]¬°¥[¤F·s­^¶¯©Ò¥H¦ì¸m¤£¤@¼Ë¤F
§Ú§âµ{¦¡ªº¼Æ¦r³£©¹«á±À¡A¦ý¬O¬Ý°_¨Ó¬O¦³°ÝÃDªº
À³¸Ó¬O¦]¬°«e­±ªº¨S¦³µ¹¹Ï©Ò¥H³o¸Ì·|¦³°ÝÃD
(³Ó¸ò±Ñ¤¤¶¡¦³¤@­Ó³ÆµùÄæ¦ì)
[attach]32622[/attach]
[attach]32624[/attach]
[attach]32623[/attach]

¥[Á`«áÀ³¸ÓÀu¥ý¶ñ¦b³oºØ¦C¤W DC OR DE OR DK¦³­È ¨ä¦¸¬OCZ¦³­È

P.S.
¦b§â¼Æ­È¥[¤W¥h¤§«e¦³Åýµ{¦¡¶]¹L¤@¹M
¤£¹L¸ò¹w­pªº¤@¼Ë¡A®æ¤l¤£¹ï©Ò¥H¬O¦³°ÝÃDªº
¦ýµo²{¦³´X­Ó·Q°Ý°Ý¥i§_Åܧ󪺳¡¤À
¨ä¤@
¥Ø«e¸ê®Æ¤w¦³¤E¤d¦h¦æ¡A©Ò¥H¨ä¹ê¤ñ¹ï¥X¨Ó¸ê®Æ¬O§_¥¿½T§Ú¤]µLªkÅçÃÒ
©Ò¥H¥i§_±N¹B¦æ§¹¦¨«áªºª½±µ²£¥Í¥t¤@­Ó¬¡­¶Ã¯¡A§Úª½±µ¬Ý¨â­Ó¬¡·~諸¦C¼Æ¬O§_¦³®t²§
(·íµMªì©lÅçÃÒ¹B§@ªº®É­Ô¥i¥H¥ý§â©T©wªº¸ê®Æ½Æ»s¦h¦æ¦A¨ì²£¥Íªº¬¡­¶Ã¯¥h¬Ý¬Û¦P¦C¬O§_¦³¥[Á`¤W¥h´Nª¾¹D¤F)
ex.
­ì©l¸ê®Æ(sheet1)9000¦æ¡A²£¥Íªº·s¬¡­¶Ã¯(sheet2)(¤ñ¹ï¹Lªº¸ê®Æ)¡AÅܦ¨8800¡A³o¼Ë´N¥i¥Hª¾¹D½T¹ê¦³Å|¤W¥h¤F
¦A¤H¤u¥h½T»{sheet1¸òsheet2ªº®t²§ÂI´N¥i¥H½T»{µ{¦¡¬O§_¥¿½T

¨ä¤G
°õ¦æªº®É­Ô¬O§_¥i¥[¤W
Application.Calculation = xlCalculationManual 'Ãö³¬¦Û°Ê­pºâ
Application.ScreenUpdating = False 'Ãö³¬¿Ã¹õ¨ê·s
¥þ³¡µ²§ô«á¦A¥[¤W
Application.Calculation = xlCalculationAutomatic '¶}±Ò¦Û°Ê­pºâ
Application.ScreenUpdating = True '¶}±Ò¿Ã¹õ¨ê·s
ÁקKµ{¦¡¤@ª½­«·s­pºâÀx¦s®æ?
(§Ú¥Ø«eµLªk´ú¡A¦]¬°§Ú¤]¤£ª¾¹D¥Ø«eªºµ{¦¡¨ì©³¹ï¤£¹ï)

to °a¤ªºµ
©êºp¤§«e§Ú¨S¦Û¤v¤U¦^¨Ó´ú¹L¡A³o¦¸§Ú¦³¤U¦^¨Ó´ú¹L¤F¡AÀ³¸Ó¥i¥H¥Î¤F
p.s. µLªk¸ÑÀ£¤]¦³¥i¯à¬O¦]¬°rarª©¥»¹L¡A¥i¥H¸Õ¬Ý¬Ý·sª©ªºrar¡A¥Ø«e­Ó¤Hª©¬O§K¶Oªº¡C

¯uªº¨Sªk¤U¸üªº¸Ü¡A§Ú§â²Ä¤@¦C³£©ç¤U¨Ó¤F
[attach]32628[/attach]
[attach]32629[/attach]
[attach]32630[/attach]

¥Ø«e¬O¤ñ¹ïA~CX and DBªø¤@¼Ë
§â¤W­z±ø¥ó¤@¼Ëªº¦CªºCY(³Ó³õ) | DA(±Ñ³õ)  ¥[Á`¶ñ¤J¦P¤@¦C
¦Ü©ó¶ñ¤J­þ¤@¦CÀu¥ý¦Ò¼{   DC OR DE OR DK¦³­È ¨ä¦¸¬OCZ¦³­È
[attach]32631[/attach]
jcchiang©Ò²z¸Ñªº±ø¥ó¬O¹ïªº
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-19 00:22

¦^´_ 26# wei9133

¦³ªÅ¬Ý¤@¤U ¤£ª¾¹D¬O¤£¬O§A­nªº  

javascript:;
§@ªÌ: jcchiang    ®É¶¡: 2020-10-19 10:23

¦^´_ 26# wei9133

¸ê®Æ§ï©ñ¸m©ó²Ä¤G­ÓSheet
Sub ex3()
Dim d As Object, ar As Object, r
Dim i%, AA$, a
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
Set ar = Sheets("¹ï¾Ô²Î­p").[a1].CurrentRegion

For i = 1 To ar.Rows.Count
   AA = Join(Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 102))), ",") & "," & ar(i, 106) '«Ø¥ß§PÂ_±ø¥ó
   If Not d.exists(AA) Then   '¦r¨å¤º¬dµL¸Ó±ø¥ó
      a = Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 115)))
      If a(103) = "" Then a(103) = 1 '³Ó³õªÅ¥Õ¶ñ¤J1
      d(AA) = a '±N¸ê®Æ©ñ¦^¦r¨å
   Else
      a = Application.Transpose(Application.Transpose(d(AA)))   '±N¦r¨å¸ê®Æ¨ú¥X
      If ar(i, 103) = "" Then a(103) = a(103) + 1 Else a(103) = a(103) + ar(i, 103) '³Ó³õªÅ¥Õ³Ó³õ²Ö¥[1,¤£¬OªÅ¥Õ«h±NÄæ¦ì­È¬Û¥[
      a(105) = a(105) + ar(i, 105) '±Ñ§½²Ö¥[
      For Each r In Array(104, 107, 109, 115) '±N³Æµù,DC,DE,DKÄæ¦ì¸ê®Æ¦X¨Ö
         If a(r) <> "" And ar(i, r) <> "" Then '¦pªG¦r¨å»PÄæ¦ì³£¦³¸ê®Æ,¨Ï¥Î","¬Û³s
            a(r) = a(r) & "," & ar(i, r)
         ElseIf a(r) = "" And ar(i, r) <> "" Then '¦pªG¦r¨å¸ê®Æ¬°ªÅ¥Õ,Äæ¦ì¬O¦³¸ê®Æªº,¨Ï¥ÎÄæ¦ì¸ê®Æ
            a(r) = ar(i, r)
         End If
      Next
      d(AA) = a   '±N¸ê®Æ©ñ¦^¦r¨å
   End If
Next
With Sheets(2)  '¦b²Ä¤G­ÓSheet¶ñ¤J¸ê®Æ
.[a1].CurrentRegion.Clear '²M°£Sheet¸ê®Æ
.[a1].Resize(d.Count, 115) = Application.Transpose(Application.Transpose(d.items)) '±N¦r¨å¸ê®Æ¦C¥X
For Each r In .Range(.[cy2], .[cy2].End(4))  '«O¯d³Ó³õ+1
   r.Value = r.Value + 1
Next
End With
Set d = Nothing
Application.ScreenUpdating = True
End Sub
§@ªÌ: wei9133    ®É¶¡: 2020-10-20 22:59

¦^´_ 28# jcchiang

§A¦n
¥ý¥H5®æ¬°°ò·Çª½±µ°õ¦æ
¸g´ú¸Õ¡A³Ó²v³¡¤À¤£ºÞ¦³¨S¦³­«½Æ³£·|ª½±µ+1¤W¥h
¹Ï¤@[attach]32636[/attach]
(­ì©l¸ê®Æ)
¹Ï¤G[attach]32637[/attach]
(²£¥X¸ê®Æ)

³Ó²v³o®æ¦³°ÝÃDªº¼Ë¤l
§ÚÁ`¦@¥H5®æ°µ´ú¸Õ¡A1~5®æ½Æ»s¤@¥÷©ñ¦b¤U­±¦C¤¤¡A¶È§ó§ï¬P¶H³¡¤À
¥X¨Óµ²ªG¬O¿ù»~ªº
¹Ï¤T[attach]32638[/attach]
(­ì©l¸ê®Æ)
¹Ï¥|[attach]32639[/attach]
(²£¥X¸ê®Æ)

¥¿½TÀ³¸Ó¬°
¹Ï5[attach]32640[/attach]



¥t¥~¡A¤§«e­n­×¥¿«á¸ê®Æ©ñ¶isheet2¤º¥»¨Ó´N¬O¬°¤F¤ñ¹ïµ{¦¡¬O§_°õ¦æ¥¿½T
³o¦¸¤]¬O¦hÁ«¤F³oÂI¤~¯à¬Ý¥X¥[Á`ªº³¡¤À¤£¹ï
¦Ó§Úªº²Ä¤G¦ì¸m¬¡­¶Ã¯¨ä¹ê¬O¦³¨ä¥L¸ê®Æªº¡A³o¼Ë´Nª½±µ§â¸ê®Æ¥þ»\¹L¥h¤F
¥i¥H³Â·Ð§ï¦¨½Æ»s¤@¥÷·í«e(sheet)°õ¦æµ{¦¡ªº°Æ¥»¡AµM«áª½±µ¦b°õ¦æ¬¡­¶Ã¯¤¤±N­«½Æ¦C§R°£¥[Á`?
(«Ø¥ß°Æ¥»§@¬°³Æ¥÷¡Aª½±µ¦b»Ý­n¹B¦æªº¬¡­¶Ã¯¤º°µ§R°£¡A­YµLªk
§Ú·Q¨ìªº¬O°õ¦æ«á§â¥Ø«eªºsheet1§R°£¡Asheet2§ï¦W¦¨sheet1¦Ó¤w)

½Æ»s°Æ¥»ªº«ü¥O¥i¥Hªº¸ÜÀ°§Ú¥[µù¸Ñ¡A½T»{µ{¦¡³£¥¿±`¹B¦æ¤§«á´N¤£»Ý­n°µ°Æ¥»¤F
¦]¬°°Æ¥»¥»¨Ó´N¬O¬°¤FÅçÃÒ¬O§_¥¿½T°õ¦æ¦Ó¦s¦bªº
·PÁÂ
§@ªÌ: wei9133    ®É¶¡: 2020-10-20 23:19

¦^´_ 27# °a¤ªºµ
§A¦n
ª½±µ°õ¦æ§Aªºªþ¥ó¡A³Ó²vªº½T·|¼W¥[
¥H§R°£¤U­±´X¦æ¡A¶È½Æ»s2~3¦æ°µ°Æ¥»¡A¶K¦b5~6¦æ
°õ¦æ¤£·|¹B§@(ÃhºÃ¬O¦]¬°§ÚªÅ¦æ¤F)
¹Ï1[attach]32641[/attach]

¨Ã¥B¦b§Aªì©lªºÀɮפ¤¡A°õ¦æ«á¨Ã¥¼¦X¨Ö¡A¦Ó¬Oª½±µ¥[¤W³Ó²v(³õ)

¹Ï2[attach]32642[/attach]
ªì©l

¹Ï3[attach]32643[/attach]
°õ¦æ«á

¥¿½TÀ³¸Ó¬O
¹Ï4[attach]32644[/attach]
§@ªÌ: jcchiang    ®É¶¡: 2020-10-21 08:26

¦^´_ 29# wei9133

³Ó²v­pºâ¬O§_¬°¨S¦³­«½Æªº´N¤£¦h¥[1³õ¶Ü??
¦]¬°§Aªº³Ó³õºâªk³£¤£¤@¼Ë
¥H§A¦b#30¼Óªº¶K¹Ï
¤HÃþ4³õ³Ó²v¬°(1,2,ªÅ,ªÅ),³Ó²vÀ³¬°1+2+1+1=5«O¯d¦A¥[1,©Ò¥H¬°6
¦ý¤O¶q­^¶¯2³õ³Ó²v¬°(ªÅ,3),³Ó²vÀ³¬°1+3=4«O¯d¦A¥[1,©Ò¥H¬°5,¦ýµ¹ªº¥¿½T³Ó²v«o¬°3

(#23¼Ó:¦Ó³Ó³õ³¡¥÷ªº­È«h¤À§O¬°3¡BªÅ®æ¡B1¡A±Ñ³õªº­È«h¬°1¡BªÅ®æ¡BªÅ®æ
  ºâ¥X¨Ó¦X¨Öªº³Ó³õÄæ¦ìÀ³¬°"6"¡A±Ñ³õ«h¬°"1"
   ºâªk¬O³o¼Ëªº¡A°²³]3¨º®æ«O¯d¡A¦ÓªÅ®æ¥Nªí³Ó1³õ¡A³Ó³õ¶ñ¤J1ªº¹ê»Ú¤W¬O"·í¦C"¥["³Ó1³õ"
   ©Ò¥H¥[¥X¨Ó¬O"6")
³Ó³õ(3,ªÅ,1)=6,¦pªG¥HªÅ¬°1,¹ê»Ú¤]¬O5,¦hªº1³õ¤£´N¬O¦Ó¥~¼W¥[ªº¶Ü??

¥t¥~¸ê®Æ©ñ¸m¨ä¥LSheet¬O¤£¥h§ïÅܭ즳¸ê®Æ¥H«KÅçÃÒ,¦Ó¥Bµ{¦¡µù¸Ñ¤]¦³¼g©ñ¸m©ó²Ä¤G­ÓSheet
¦p­n§ï©ñ©ó¨ä¥L¦ì¸m©Î°µªk,µ{¦¡¤¤³£¦³µù¸Ñ,½Ð¦Û¦æ·L½Õ,ÁÂÁÂ!!
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-21 21:56

¦^´_ 30# wei9133

§A§â jcchiang«e½úªº ¥H¤U³o¬q§ï¤@¤U ¬Ý¬Ý ¬O¤£¬O§A­nªºµ²ªG

Sub ex3()
Dim d As Object, ar As Object, r
Dim i%, AA$, a
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
Set ar = Sheets(1).[a1].CurrentRegion

For i = 1 To ar.Rows.Count
   AA = Join(Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 102))), ",") & "," & ar(i, 106) '«Ø¥ß§PÂ_±ø¥ó
   If Not d.exists(AA) Then   '¦r¨å¤º¬dµL¸Ó±ø¥ó
      a = Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 115)))
      If a(103) = "" Then a(103) = 1 '³Ó³õªÅ¥Õ¶ñ¤J1
      d(AA) = a '±N¸ê®Æ©ñ¦^¦r¨å
   Else
      a = Application.Transpose(Application.Transpose(d(AA)))   '±N¦r¨å¸ê®Æ¨ú¥X
      If ar(i, 103) = "" Then a(103) = a(103) + 1 Else a(103) = a(103) + ar(i, 103) '³Ó³õªÅ¥Õ³Ó³õ²Ö¥[1,¤£¬OªÅ¥Õ«h±NÄæ¦ì­È¬Û¥[
      a(105) = a(105) + ar(i, 105) '±Ñ§½²Ö¥[
      For Each r In Array(104, 107, 109, 115) '±N³Æµù,DC,DE,DKÄæ¦ì¸ê®Æ¦X¨Ö
         If a(r) <> "" And ar(i, r) <> "" Then '¦pªG¦r¨å»PÄæ¦ì³£¦³¸ê®Æ,¨Ï¥Î","¬Û³s
            a(r) = a(r) & "," & ar(i, r)
         ElseIf a(r) = "" And ar(i, r) <> "" Then '¦pªG¦r¨å¸ê®Æ¬°ªÅ¥Õ,Äæ¦ì¬O¦³¸ê®Æªº,¨Ï¥ÎÄæ¦ì¸ê®Æ
            a(r) = ar(i, r)
         End If
      Next
      d(AA) = a   '±N¸ê®Æ©ñ¦^¦r¨å
   End If
Next
With Sheets(2)  '¦b²Ä¤G­ÓSheet¶ñ¤J¸ê®Æ
.[a1].CurrentRegion.Clear '²M°£Sheet¸ê®Æ
.[a1].Resize(d.Count, 115) = Application.Transpose(Application.Transpose(d.items)) '±N¦r¨å¸ê®Æ¦C¥X
'For Each r In .Range(.[cy2], .[cy2].End(4))  '«O¯d³Ó³õ+1
'   r.Value = r.Value + 1
'Next

End With
Set d = Nothing
Application.ScreenUpdating = True
End Sub
§@ªÌ: wei9133    ®É¶¡: 2020-10-22 06:23

¦^´_ 31# jcchiang

30Fªº½T¬O§Úºâ¿ù¤F
¤O¶q­^¶¯À³¸Ó¬O4

(#23¼Ó:¦Ó³Ó³õ³¡¥÷ªº­È«h¤À§O¬°3¡BªÅ®æ¡B1¡A±Ñ³õªº­È«h¬°1¡BªÅ®æ¡BªÅ®æ
  ºâ¥X¨Ó¦X¨Öªº³Ó³õÄæ¦ìÀ³¬°"6"¡A±Ñ³õ«h¬°"1"
   ºâªk¬O³o¼Ëªº¡A°²³]3¨º®æ«O¯d¡A¦ÓªÅ®æ¥Nªí³Ó1³õ¡A³Ó³õ¶ñ¤J1ªº¹ê»Ú¤W¬O"·í¦C"¥["³Ó1³õ"
   ©Ò¥H¥[¥X¨Ó¬O"6")

²Ä¤G¬qªº³¡¤À§Aªº²z¸Ñ¬O¹ïªº

³Ó³õ(3,ªÅ,1)=6,¦pªG¥HªÅ¬°1,¹ê»Ú¤]¬O5,¦hªº1³õ¤£´N¬O¦Ó¥~¼W¥[ªº¶Ü??
³o­Ó¨ä¹ê§Ú¨S¬ÝÀ´

§Ú³o¼Ë¬Ý§A¯à¤£¯à²z¸Ñ
¬ö¿ý¥X¨Óªº·í¦C¡A¥»¨­´N¥Nªí¨º¦¸ªº³Ó³õ¡A·í§Ú²Î­p¨ì¤@¼Ò¤@¼Ëªº¹ï¾Ô¡A´N¦b³Ó³õ³B+1
©Ò¥H¨C¤@¦C³£¥Nªí¤F1¡A¦Ó³Ó³õ¬O¥t¥~¥[¤W¥hªº¡A¤~·|Åܦ¨³B²z¨ì¸Ó®æ­Y¸Ó®æ¦³­È­n+1¤W¥h
³Ó²v¨º®æ­YµL¥t¥~ªº¼Æ­È¡A¾ã¦Cµø¬°1

===================¤À¹j½u======================
§Ú®³¤F§A¦b28F´£¨Ñªºµ{¦¡½X¤U¥h°õ¦æ
¹Ï1
[attach]32649[/attach]
¹Ï¤@ªº²Ä¤@¸ò²Ä¤G¦C³Ó²vÄæ¦ì³£¬O0¡A¥Nªí¦U¦Û·í¦Cªº³Ó³õ¡A¦Ó³o¨â­Óµ²ºc³£¤@¼Ë
©Ò¥H¹B¦æ¥X¨ÓÀ³¸Ó¬O
¦X¨Ö°_¨Ó¡A³Ó²v¼g1  (¹ê»Ú¤W¬OĹ2³õ¨S¿ù¡A¦ý¦Û¤v³o¤@¦C´N¥Nªí¤F¤@³õ¡A©Ò¥H³Ó²v¨º®æ¥u·|¼g1)
²Ä¤T¦æ¬O¿W¦Û¦Û¤v¤@­Ó¡A¨S¦³¬Û¦P¦C¡A©Ò¥H´N¥u¦³«O¯d¡A¦Ü©ó³Ó²v¦]¬°¸Ó®æ¬O0¡A¥B¥u¦³¥¦¦Û¤v¡A©Ò¥HÀ³¸Ó¯dªÅ
§Ú·Q°õ¦æ¥X¨Óªºµ²ªG
¹Ï2
[attach]32650[/attach]
===============================================
¹ê»Ú°õ¦æ¥X¨Óªºµ²ªG
¹Ï3
[attach]32651[/attach]
§Ú°õ¦æªºµ²ªG¥X¨Ó·Pı¬O¡A³Ó²v¨º®æµL½×¬OªÅÁÙ¬O1³£·|³Qµø¬°1
¦ý¹ê»Ú¤WÀ³¸Ó¬O¸Ó¦Cµ¥©ó1¡A³Ó²v¨º®æ­Y¦³¼Æ¦r¡A
¥B¸Ó¦C­n³Q¨Ö¨ì¥t¤@¦Cªº¸Ü¡A´N­n¥H¸Ó®æ¼Æ¦r¥[¤W¦Û¤v³o¤@¦C¥Nªíªº1

¦Ó¬O³Ó²v¨º®æ¬OªÅ­È¥BµL¬Û¦P¦C¥i¦X¨Öªº¡A«O¯d¸Ó¦C¡A³Ó²v¨º®æ¤]´NÁÙ¬O0
(¦]¬°Ä¹ªº¨ÌÂÂ¥u¦³¤@³õ¡A¦Ó¨º³õ´N¬O¸Ó¦C¥»¨­)

==============================================
­Y³o¼Ë¯uªº«ÜÃø³Q²z¸Ñªº¸Ü¡A§Ú¥i¥H§ïÅܲέp¤è¦¡
¾ã¦C¤£¥Nªí¥ô¦ó¼Æ¦r¡AĹªº¦¸¼Æ¥þ¼g¦b³Ó²v¨ºùØ
³o­Ó²Õ¦XŤ@¦¸´N¼g1¡AŨ⦸´N¼g2
³o¼Ë´N¤£·|¦³­n­pºâ¥»¨­¦C¬°1ªº°ÝÃD¤F

§Aªº¾ã­Óµ{¦¡§Ú¦A¬ã¨s¬Ý¬Ý­n§ï­þ¸Ì¤~·|²Å¦X§Úªº»Ý¨D
·PÁ¨â¦ì
§@ªÌ: wei9133    ®É¶¡: 2020-10-22 06:31

¦^´_  wei9133

§A§â jcchiang«e½úªº ¥H¤U³o¬q§ï¤@¤U ¬Ý¬Ý ¬O¤£¬O§A­nªºµ²ªG

Sub ex3()
Dim d As Ob ...
°a¤ªºµ µoªí©ó 2020-10-21 21:56

§A¦n
¦]¬°´ú¸Õ§¹
³Ó²v¨º®æµL½×¬OªÅÁÙ¬O1³£·|³Qµø¬°1¡A¦ý¹ê»Ú¤WÀ³¸Ó¬OªÅ¬°1¡A¼g1¹ê»ÚÀ³¬°2 (­n³Q¦X¨Öªºª¬ªp¤U)
¦Ó¤£³Q¦X¨Öªºª¬ªp¤UªÅ´N¬OªÅ¡A¸Ó®æ¤£À³¦³­È (¦]¬°¸Ó¦C¦Û¤v´N¬O1)
©Ò¥H¥uµù¸Ñ±¼°j°é¥[1ªº³¡¤ÀÁÙ¬O¨S¥Îªº
³Ó²v¨º®æ¦³­Èªº¥¿½T¤F¡AªÅªº´N·|¦³°ÝÃD¡A¤Ï¤§¥çµM
©Ò¥H§A§ïªº³o¼ËÁÙ¬O¤£¤Ó¹ï
·PÁ§A¤F
§@ªÌ: jcchiang    ®É¶¡: 2020-10-22 08:22

¦^´_ 33# wei9133

#33
¦³2³õ¤@¼Ë(³Ó²vÄ欰"ªÅ","ªÅ")¦X¨Ö³Ó²v¬°1
¥u¦³1³õ(³Ó²vÄ欰"ªÅ")³Ó²v¬°"ªÅ"

´XºØª¬ªp¦p¦ó­pºâ
2³õ(³Ó²v¬°"3","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°4)
3³õ(³Ó²v¬°"3","ªÅ","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°4)
3³õ(³Ó²v¬°"ªÅ","ªÅ","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°1)
3³õ(³Ó²v¬°"3","1","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°5)
4³õ(³Ó²v¬°"ªÅ","3","1","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°5)
¥u¦³¤@³õ¬O§_³Ó²vÄæ¦ì³£¤£ÅÜ
¦h³õªº¥u­n³Ó²v¬°ªÅªº¤£ºÞ´X³õ³£¥uºâ1³õ³Ó³õ,¨ä¾l³Ó²vÄ榳­Èªºª½±µ²Ö¥[­È
§@ªÌ: jcchiang    ®É¶¡: 2020-10-22 10:24

¦^´_ 33# wei9133

1.¸ê®Æ¦ì¸m©ñ¸m²Ä¤G­Ósheet,½Ð¦Û¦æ­×§ï©ñ¸m¦ì¸m
2.³Ó³õ­pºâ¤è¦¡
-->¥u¦³1µ§¸ê®Æ,³Ó³õ³£¤£ÅÜ°Ê
-->2µ§¥H¤W¸ê®Æ,©Ò¦³ªº"ªÅ"³£ºâ¼W¥[1³õ,¦³­Èªºª½±µ²Ö¥[

Sub ex4()
Dim d As Object, ar As Object, r
Dim i%, AA$, a
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
Set ar = Sheets("¹ï¾Ô²Î­p").[a1].CurrentRegion

For i = 1 To ar.Rows.Count
   AA = Join(Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 102))), ",") & "," & ar(i, 106) '«Ø¥ß§PÂ_±ø¥ó
   If Not d.exists(AA) Then   '¦r¨å¤º¬dµL¸Ó±ø¥ó
      a = Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 115)))
      ReDim Preserve a(1 To UBound(a) + 2)
      If a(103) = "" Then a(UBound(a) - 1) = 1 '¬ö¿ý³Ó³õªÅ¥Õ¼Æ
      a(UBound(a)) = 1 '¬ö¿ýµ§¼Æ
      d(AA) = a '±N¸ê®Æ©ñ¦^¦r¨å
   Else
      a = Application.Transpose(Application.Transpose(d(AA)))   '±N¦r¨å¸ê®Æ¨ú¥X
      If ar(i, 103) = "" Then a(UBound(a) - 1) = a(UBound(a) - 1) + 1 Else a(103) = a(103) + ar(i, 103) '³Ó³õªÅ¥Õ¬ö¿ý²Ö¥[1,¤£¬OªÅ¥Õ«h±NÄæ¦ì­È¬Û¥[
      a(UBound(a)) = a(UBound(a)) + 1 ''¬ö¿ýµ§¼Æ+1
      a(105) = a(105) + ar(i, 105) '±Ñ§½²Ö¥[
      For Each r In Array(104, 107, 109, 115) '±N³Æµù,DC,DE,DKÄæ¦ì¸ê®Æ¦X¨Ö
         If a(r) <> "" And ar(i, r) <> "" Then '¦pªG¦r¨å»PÄæ¦ì³£¦³¸ê®Æ,¨Ï¥Î","¬Û³s
            a(r) = a(r) & "," & ar(i, r)
         ElseIf a(r) = "" And ar(i, r) <> "" Then '¦pªG¦r¨å¸ê®Æ¬°ªÅ¥Õ,Äæ¦ì¬O¦³¸ê®Æªº,¨Ï¥ÎÄæ¦ì¸ê®Æ
            a(r) = ar(i, r)
         End If
      Next
      d(AA) = a   '±N¸ê®Æ©ñ¦^¦r¨å
   End If
Next
With Sheets(2)  '¦b²Ä¤G­ÓSheet¶ñ¤J¸ê®Æ
.[a1].CurrentRegion.Clear '²M°£Sheet¸ê®Æ
.[a1].Resize(d.Count, UBound(a)) = Application.Transpose(Application.Transpose(d.items)) '±N¦r¨å¸ê®Æ¦C¥X
For Each r In .Range(.[cy2], .[cy65535].End(3))
   If r.Offset(, 14) > 1 And r.Offset(, 13) > 0 Then r.Value = r.Value + 1 '2µ§¸ê®Æ¥H¤W¥B¦³³Ó³õ¬°"ªÅ"ªº³Ó³õ+1
Next
i = .[a1].CurrentRegion.Columns.Count
.Range(Cells(1, i - 1), Cells(65535, i).End(3)).Clear '²M°£ªÅ¥Õ&¸ê®Æ­pºâµ§¼Æ
End With
Set d = Nothing
Application.ScreenUpdating = True
End Sub
§@ªÌ: ­ã´£³¡ªL    ®É¶¡: 2020-10-23 10:18

²Õ¦X1--a/b/d/f/h-¤HÃþ(³Ó¤è)   ²Õ¦X2--b/d/e/f/g-¤HÃþ(±Ñ¤è) --- ¦@¥X²{5¦¸
²Õ¦X2--b/d/e/f/g-¤HÃþ(³Ó¤è)   ²Õ¦X1--a/b/d/f/h-¤HÃþ(±Ñ¤è) --- ¦@¥X²{3¦¸
ÁöµM¥ª¥k¹ï½Õ, ¦ýÀ³ºâ¦P¤@²Õ¦X¹ï¾Ô§a!

²Õ¦X1--a/b/d/f/h-¤HÃþ -- ³Ó5±Ñ3
²Õ¦X2--b/d/e/f/g-¤HÃþ -- ³Ó3±Ñ5

³o³Ó±Ñ²v¦p¦ó­pºâ???
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-24 21:06

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-24 21:11 ½s¿è

¦^´_ 34# wei9133

½Ð°Ý§A­nªºµ²ªG¬O¤£¬O³o¼Ë
¥ý¥ª¥k¤ñ¹ï ­Y¬O 1~51  ¸ò 52~102 ¬Û¦P®É + ¬P¶H106 ¦¹®É(³Ó²v =  "" ©Î ±Ñ§½ = ""  ¥»¨­´N¬O =1 ©ÎµÛ =-1 ?)  ·í§@´M§ä¤ñ¹ïªº¥Ø¼Ð  
¦A´M§ä¤ñ¹ï¤W¤U 1~102 + ¬P¶H106 ­Y¬Û¦P®É  ¦b¬Ý 103³Ó²v  ¸ò  105±Ñ§½ ¶i¦æ²Ö¥[
²Ö¥[«áªº¼Æ­È­n¦b ¦X¨Ö¸Ó¦Cªº¶¶§Ç¤W
¦X¨Ö¸Ó¦Cªº¶¶§Ç¬O   ¥ý107 A  ­Y =""  ´N¦X¨Ö¦b 109  X   ­Y ="" ´N¦X¨Ö¦b   115¼È¦s   ­Y =""  ´N¦X¨Ö¦b  104³Æµù
ÁÙ¬O»¡ ´N¹³ jcchiang «e½ú ¸ò ­ã´£¤j¤j ©Ò»¡ªº ³o¼Ë½ÆÂøªº²Õ¦Xºâªk?
©ÎµÛ¯à§_²³æ©úÁA´N¦n §Ú¹ê¦b¤£¤Ó©ú¥Õ©êºp...¤p§Ì¼Æ¾Ç¤£¦n
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-25 01:20

¦^´_ 34# wei9133

¦³ªÅÀ°§Ú¬Ý¤@¤U ¬O¤£¬O³o¼Ëªºµ²ªG ÁÂÁÂ

javascript:;
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-25 10:40

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-25 10:49 ½s¿è

¦^´_ 34# wei9133

·Pı±Ñ§½ ©Ç©Çªº ©Ò¥H§ï¤F¤@¤U ¦³ªÅÀ°§Ú¬Ý¤@¤U ·PÁ ¶]ªº³t«×ºC¤F¤@¨Ç ¤£ª¾¦p¦ó¥[§Ö³t«×.....
  1. Public Sub ½m²ß1025()
  2. Application.ScreenUpdating = False
  3. Sheets(1).Select
  4. Sheets(2).[a1].CurrentRegion.Clear
  5. Dim Arr, D, xD, xD1, x&, y&, k&, T1$, T2$, T3$, T4$
  6. Set xD = CreateObject("Scripting.Dictionary")
  7. Set xD1 = CreateObject("Scripting.Dictionary")
  8. Arr = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, 115))
  9. For x = 2 To UBound(Arr, 1)
  10.     T1 = ""
  11.     For y = 1 To 51
  12.         T1 = T1 & Arr(x, y)
  13.         If Arr(x, y) = "" Then T1 = T1 & "-"
  14.     Next y
  15.     T3 = ""
  16.     For y = 52 To 102
  17.         T3 = T3 & Arr(x, y)
  18.         If Arr(x, y) = "" Then T3 = T3 & "-"
  19.     Next y
  20.     If T1 = T3 Then
  21.        T1 = T1 & T3 & Arr(x, 106)
  22.        T3 = ""
  23.         If Arr(x, 103) = "" Then
  24.            Arr(x, 103) = 1
  25.            xD(T1) = xD(T1) + Arr(x, 103)
  26.         ElseIf Arr(x, 103) <> "" Then
  27.            xD(T1) = xD(T1) + Arr(x, 103)
  28.         End If
  29.         xD1(T1) = xD1(T1) + Arr(x, 105)
  30.     End If
  31. Next x
  32. T1 = "": T3 = ""
  33. For Each D In xD
  34.     For x = UBound(Arr, 1) To 2 Step -1
  35.         T2 = ""
  36.         For y = 1 To 51
  37.             T2 = T2 & Arr(x, y)
  38.             If Arr(x, y) = "" Then T2 = T2 & "-"
  39.         Next y
  40.         T4 = ""
  41.         For y = 52 To 102
  42.             T4 = T4 & Arr(x, y)
  43.             If Arr(x, y) = "" Then T4 = T4 & "-"
  44.         Next y
  45.         If T2 = T4 Then
  46.             T2 = T2 & T4 & Arr(x, 106)
  47.             T4 = ""
  48.             If D = T2 Then
  49.                 Arr(x, 103) = xD(D)
  50.                 Arr(x, 105) = xD1(D)
  51.             End If
  52.         End If
  53.     Next x
  54. Next D
  55. T2 = "": T4 = "": D = "": k = 1
  56. For x = 2 To UBound(Arr, 1)
  57.     If Arr(x, 103) <> "" Or Arr(x, 105) <> "" Then
  58.         If Arr(x, 107) <> "" Or Arr(x, 109) <> "" _
  59.         Or Arr(x, 115) <> "" Or Arr(x, 104) <> "" Then
  60.             k = k + 1
  61.         End If
  62.         For y = 1 To UBound(Arr, 2)
  63.             If Arr(x, 107) <> "" Or Arr(x, 109) <> "" _
  64.             Or Arr(x, 115) <> "" Or Arr(x, 104) <> "" Then
  65.                 Arr(k, y) = Arr(x, y)
  66.             End If
  67.         Next y
  68.     End If
  69. Next x
  70. Set xD = Nothing
  71. Set xD1 = Nothing
  72. Sheets(2).Range("A1").Resize(k, UBound(Arr, 2)) = ""
  73. Sheets(2).Range("A1").Resize(k, UBound(Arr, 2)) = Arr
  74. Erase Arr
  75. Sheets(2).Select
  76. Application.ScreenUpdating = True
  77. End Sub
½Æ»s¥N½X

§@ªÌ: wei9133    ®É¶¡: 2020-10-28 11:42

¦^´_  wei9133

#33
¦³2³õ¤@¼Ë(³Ó²vÄ欰"ªÅ","ªÅ")¦X¨Ö³Ó²v¬°1
¥u¦³1³õ(³Ó²vÄ欰"ªÅ")³Ó²v¬°"ªÅ"

´X ...
jcchiang µoªí©ó 2020-10-22 08:22



   
#33
¦³2³õ¤@¼Ë(³Ó²vÄ欰"ªÅ","ªÅ")¦X¨Ö³Ó²v¬°1
¥u¦³1³õ(³Ó²vÄ欰"ªÅ")³Ó²v¬°"ªÅ"

´XºØª¬ªp¦p¦ó­pºâ
2³õ(³Ó²v¬°"3","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°4)
3³õ(³Ó²v¬°"3","ªÅ","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°4)
3³õ(³Ó²v¬°"ªÅ","ªÅ","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°1)
3³õ(³Ó²v¬°"3","1","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°5)
4³õ(³Ó²v¬°"ªÅ","3","1","ªÅ")¦X¨Ö³Ó²v??(¬O§_¬°5)
¥u¦³¤@³õ¬O§_³Ó²vÄæ¦ì³£¤£ÅÜ
¦h³õªº¥u­n³Ó²v¬°ªÅªº¤£ºÞ´X³õ³£¥uºâ1³õ³Ó³õ,¨ä¾l³Ó²vÄ榳­Èªºª½±µ²Ö¥[­È

§A¦n¡A©êºp²{¦b¤~¦^´_

2³õ(³Ó²v¬°"3","ªÅ")¦X¨Ö³Ó²v??  
Á`¦@³Ó5³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 4³õ

3³õ(³Ó²v¬°"3","ªÅ","ªÅ")¦X¨Ö³Ó²v??  
Á`¦@³Ó6³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 5³õ

3³õ(³Ó²v¬°"ªÅ","ªÅ","ªÅ")¦X¨Ö³Ó²v??  
Á`¦@³Ó3³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 2³õ

3³õ(³Ó²v¬°"3","1","ªÅ")¦X¨Ö³Ó²v??     
Á`¦@³Ó7³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 6³õ

4³õ(³Ó²v¬°"ªÅ","3","1","ªÅ")¦X¨Ö³Ó²v??
Á`¦@³Ó7³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 6³õ

¥H¤U°l¥[¡A¬Ý¯à§_²z¸Ñ

¶È1¦CµL¨ä¥L¬Û¦PªÌ (¸Ó®æ³Ó²v¬° "3")
Á`¦@³Ó4³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 3³õ

¶È1¦CµL¨ä¥L¬Û¦PªÌ (¸Ó®æ³Ó²v¬° "ªÅ")
Á`¦@³Ó1³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° ªÅ³õ

¦@2¦C¬Û¦P ³Ó²v¬° "2","ªÅ"
Á`¦@³Ó4³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 3³õ

¦@2¦C¬Û¦P ³Ó²v¬° "ªÅ","ªÅ"
Á`¦@³Ó2³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 1³õ

¦@2¦C¬Û¦P ³Ó²v¬° "2","1"
Á`¦@³Ó5³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 4³õ

¦@3¦C¬Û¦P ³Ó²v¬° "2","ªÅ","1"
Á`¦@³Ó6³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 5³õ

¦@4¦C¬Û¦P ³Ó²v¬° "7","ªÅ","3","ªÅ"
Á`¦@³Ó14³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 13³õ

¥H¤W³£¬O¦X¨Ö§¹«á¶È¯d¤@¦C

¥u¦³¤@³õ¬O§_³Ó²vÄæ¦ì³£¤£ÅÜ
¨S¦³2­Ó¬Û¦Pªº¦C³Ó²vÄ椣ÅܵL»~

¦h³õªº¥u­n³Ó²v¬°ªÅªº¤£ºÞ´X³õ³£¥uºâ1³õ³Ó³õ,¨ä¾l³Ó²vÄ榳­Èªºª½±µ²Ö¥[­È

µn°O¹Lªº¤@¦C´N¬O¤@¡A«á­±³Ó²vÄ榳­È´N¥Nªíµn°Oªº·í¤U¦³2­Ó¤@¼Ëªº²Õ¦XÀò³Ó
¦]¬°µn°O¤£¨£±o¬O¦P¤@¤Ñ¡A©Ò¥H¤~·|¥X²{
¦P¼Ëªº¦C¦ý³Ó²v¤£¦Pªºª¬ªp¡A³o­Ó®É­Ô´N»Ý­n¦X¨Ö
¥ý«e´N¬O³£¤â¤u¦X¨Ö
§@ªÌ: wei9133    ®É¶¡: 2020-10-28 12:03

¦^´_ 37# ­ã´£³¡ªL


      
³oªº½T¬O¦P¤@¹ï¾Ô¡A§Ú¥Ø«e¨S¿ìªk²Î­p¨ì³o»ò²Ó¡A©Ò¥H¤~·|¦U¦Û¶ñ¤W³Ó¸ò±Ñ³õ
        ¦]¬°¦³¤À§Ú¥h¥´¸ò³Q¥´ªºª¬ªp¡A§Ú´N¬O²Î­pŸò¿é¦Ó¤w
        ¤@­Ó¤@­ÓºI¹Ï¡AµM«á§â¸ê®Æ¥´¶iexcel¸Ì
       
        ¦]¬°­n¥´ªº®É­Ô·|¥Îexcel¥h¿z¿ï¼Ä¹ï¤è±ø¥ó¡AµM«á¥h¥´¡A¥´Ä¹¦b³Ó³õ+1¡A¥´¿é¦b±Ñ³õ+1
        µM«á±Ñ³õ¦h¤F¦A¥Î¿z¿ï¥h§ä¬Û¹ïªº³¡¤À¡A§â¨âÃ䪺¼Æ¦r´«¹L¨Ó
       
        °ò¥»¤W³o­Ó¬ö¿ýªíµÛ­«¦b¥´Ä¹ªº³¡¤À¡A¤ñ¸û³Â·Ðªº³¡¤À¦b©ó¼Ä¹ï¤è¦³¨Ç¬O½Òª÷­^¶¯
        ©Ò¥H·|¥X²{¶È¦³´X³õ³Ó²v¦ý±Ñ²v«Ü°ªªº¦C¡A³o­Ó´N¨S±o¤ÏÂà¤F

       
        ¦^¨ì¥DÃD
        ³o±iªí¬O®³¨Ó°Ñ¦Ò¼Ä¹ï¤è¥X°}®É§Ú­n¥X¬Æ»ò²Õ¦X¤~¦³¸û°ª³Ó²v
       
        ²Õ¦X1--a/b/d/f/h-¤HÃþ -- ³Ó5±Ñ3
        ²Õ¦X2--b/d/e/f/g-¤HÃþ -- ³Ó3±Ñ5
        ²z½×¤W¨ÓÁ¿¡A­Y§ð¦uÂù¤è²Õ¦X¤¤¨S¦³§t§Ú¨S¦³ªº½Òª÷­^¶¯¡A§Ú·|¯d¦s³Ó²v°ªªº¨º²Õ
        ¤]´N¬O²Õ¦X1¡AµM«á¸I¨ì¹ï¤è¥X°}²Õ¦X2´N®³²Õ¦X1¥h¥´
       
        ¦ý¦]¬°¦³½Òª÷­^¶¯¦s¦b³o­Ó´N·|½ÆÂø«Ü¦h
        ¦]¬°§Ú¨S¦³¨º­Ó­^¶¯¡A´N¥u¯àµn°O²Õ¦X2
        ÁöµM±Ñ³õ¤ñ³Ó³õ°ª¡A¦ý¬O§Ú¥u¦³²Õ¦X2¥i¥H¥X
       
        ©Ò¥H¥Ø«e¥u¯à³o¼Ëµn°O¦Ó¤w
§@ªÌ: wei9133    ®É¶¡: 2020-10-28 12:37

¦^´_  wei9133

¦³ªÅÀ°§Ú¬Ý¤@¤U ¬O¤£¬O³o¼Ëªºµ²ªG ÁÂÁÂ

javascript:;
°a¤ªºµ µoªí©ó 2020-10-25 01:20



        §A¦n¡AÀ³¸Ó¤£¹ï
    °õ¦æ§¹¥uŤ@³õªº³£³Q§R±¼¤F
    §AÀ°§Ú¬Ý¤@¤U#41§A¬O§_¯à°÷²z¸Ñ
       
        ¦X¨Ö«e¨C¦C³£¤w¸gµø¬°1¤F(³Ó²vµL­Èªºª¬ªp)
       
        À³¸Ó³o¼ËÁ¿¡A³Ó²vµL­È¬°1¡A¦³­È´N¥[¤W¥h§A§â³Ó²v¨º®æ¤ºªº¼Æ¦r¤@«ß+1
        ³Ì«á§âÁ`¼Æ¥[°_¨Ó-1
        (-1¬O¦]¬°¸Ó¦C¦Û¤v´N¬O1)
       

        ¥Ø«e¦³3¦C¤@¼Ë (³o¸Ì¤w¸g1~102¸ò106³]©w¬°¤@¼Ë¤F)
        ³Ó²v®æ¤À§O¬°
          
        ²Ä¤@¦C³Ó²v"ªÅ" = ³o¦CÁ`¦@³Ó1³õ
        ²Ä¤G¦C³Ó²v"2"  = ³o¦CÁ`¦@³Ó3³õ
        ²Ä¤T¦C³Ó²v"ªÅ" = ³o¦CÁ`¦@³Ó1³õ
       
    ³o¤T¦C­n¦X¨Ö¡A©Ò¥HÁ`¦@¬OŤF5³õ
        ¯d¤U¤@¦C¡A³Ó³õ¶ñ¤J4
        (ÁÙ¦³¤@³õ´N¬O¯d¤Uªº¨º¤@¦C)
       
        ;======================================

        ¥t¤@­Ó±¡ªp
        ¥þ³¡§ä§¹´N¥u¦³³o¤@¦C¡AµL¥t¤@¦Cªø±o¤@¼Ëªº
        ©Ò¥HÅܦ¨
       
        ²Ä¤@¦C³Ó²v"ªÅ" = ³o¦CÁ`¦@³Ó1³õ
       
        ¨S±o¦X¨Ö
        ¯d¤U¤@¦C¡A³Ó³õ¤£¶ñ
        (¦]¬°¥»´NµL­È)
       
        ;======================================

        ¥þ³¡§ä§¹´N¥u¦³³o¤@¦C¡AµL¥t¤@¦Cªø±o¤@¼Ëªº
        ©Ò¥HÅܦ¨
       
        ²Ä¤@¦C³Ó²v"3" = ³o¦CÁ`¦@³Ó4³õ
       
        ¨S±o¦X¨Ö
        ¯d¤U¤@¦C¡A³Ó³õ¶ñ3
        (¯d¤U³o¤@¦C¬°1¡A³Ó³õ¼g3)
       
        ¨ä¹ê³Ó³õÀ³¸Ó²z¸Ñ¬°¦hĹªº¦¸¼Æ
       
       
        §A­Ìªº²z¸ÑÀ³¸Ó³£¬O¸Ó¦C¤£­p¼Æ¡A³Ó³õ´N¬OÁ`³Ó¼Æ
        ¦ý³o¼Ë´N¤£¥i¯à¥X²{³Ó²v¬°ªÅªº®æ¤l¤F
        ¦]¬°¨C®æ¦Ü¤ÖÀ³¸Ó³£­n¬O1¡C

        ¦Ó§Ú¦b¥´¸ê®Æªº®É­Ô¤w¸g§â¸Ó¦Cµø¬°1¤F
        ¥X²{¸Ó¦C´N¬O³Ó1³õ¡A¦³¦AŦA+1¦b³Ó³õ¤W­±
        ©Ò¥H¨C¦Cªº³Ó³õ¸Ó®æªº¼Æ¦r¼Æ¨ä¹ê¥¼¥]§t¦Û¤v¥»¨­¡A¦X¨Öªº®É­Ô´N­n§â¥L¥[¤W¥h
§@ªÌ: wei9133    ®É¶¡: 2020-10-28 13:04

¦^´_  wei9133

1.¸ê®Æ¦ì¸m©ñ¸m²Ä¤G­Ósheet,½Ð¦Û¦æ­×§ï©ñ¸m¦ì¸m
2.³Ó³õ­pºâ¤è¦¡
-->¥u¦³1µ§¸ê®Æ,³Ó³õ³£ ...
jcchiang µoªí©ó 2020-10-22 10:24



    §A¦n¡A³o­Ó°õ¦æ·|µo¥Í¿ù»~
[attach]32658[/attach]
[attach]32659[/attach]

³Ó³õ­pºâ¤è¦¡
-->¥u¦³1µ§¸ê®Æ,³Ó³õ³£¤£ÅÜ°Ê
-->2µ§¥H¤W¸ê®Æ,©Ò¦³ªº"ªÅ"³£ºâ¼W¥[1³õ,¦³­Èªºª½±µ²Ö¥[

¦³­ÈªºÀ³¸Ó¬O¸Ó­È+1
(¦]¬°¸Ó¦C¥»¨­´N¬O1)
¥i¥H¬Ý¬Ý#41ªºªTÁ|

        §A­Ìªº²z¸ÑÀ³¸Ó³£¬O¸Ó¦C¤£­p¼Æ¡A³Ó³õ´N¬OÁ`³Ó¼Æ
        ¦ý³o¼Ë´N¤£¥i¯à¥X²{³Ó²v¬°ªÅªº®æ¤l¤F
        ¦]¬°¨C®æ¦Ü¤ÖÀ³¸Ó³£­n¬O1¡C


        ¤£¹L³o­Ó°ÝÃD¥i¥H³z¹L§Ú§ïÅܲέp¤è¦¡¸Ñ¨M¡A¤£¹L¤W­±·|µo¥Í¿ù»~ªº³¡¤À­n¥ý¸Ñ¨M
§@ªÌ: jcchiang    ®É¶¡: 2020-10-29 11:30

¥»©«³Ì«á¥Ñ jcchiang ©ó 2020-10-29 11:31 ½s¿è

¦^´_ 44# wei9133

1.¨º¬qµ{¦¡§Ú°õ¦æ¨S¦³°ÝÃD(¥u¬O²M°£­p¼Æ¸ê®Æ,·sªºµ{¦¡¤w¤£»Ý­n)
2.¥u­n¬O¥u¦³1¦Cªººû«ù­ì¸ê®Æ
   ²Ä¤G¦C¶}©l,°£³Ó²vÄæ¦ì¼Æ­È²Ö¥[,¨C¦C¦A¥[1(²Ä¤@¦C¤£¥[)
¶È1¦CµL¨ä¥L¬Û¦PªÌ (¸Ó®æ³Ó²v¬° "3")
Á`¦@³Ó4³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 3³õ
-->1¦Cªººû«ù­ì¸ê®Æ

¶È1¦CµL¨ä¥L¬Û¦PªÌ (¸Ó®æ³Ó²v¬° "ªÅ")
Á`¦@³Ó1³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° ªÅ³õ
-->1¦Cªººû«ù­ì¸ê®Æ

¦@2¦C¬Û¦P ³Ó²v¬° "2","ªÅ"
Á`¦@³Ó4³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 3³õ
-->2¦C¥H¤W("2"+"(0+1)"=3)

¦@2¦C¬Û¦P ³Ó²v¬° "ªÅ","ªÅ"
Á`¦@³Ó2³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 1³õ
-->2¦C¥H¤W("0"+"(0+1)"=1)

¦@2¦C¬Û¦P ³Ó²v¬° "2","1"
Á`¦@³Ó5³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 4³õ
-->2¦C¥H¤W("2"+"(1+1)"=4)

¦@3¦C¬Û¦P ³Ó²v¬° "2","ªÅ","1"
Á`¦@³Ó6³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 5³õ
-->2¦C¥H¤W("2"+"(0+1)","(1+1)"=5)

¦@4¦C¬Û¦P ³Ó²v¬° "7","ªÅ","3","ªÅ"
Á`¦@³Ó14³õ¡A¦X¨Ö«á³Ó²vÄæ¼Ð°O¬° 13³õ
-->2¦C¥H¤W("7"+"(0+1)"+"(3+1)"+"(0+1)"=13)
¦pªGÁÙ¬O¤£¹ï,½Ð¼g­pºâ¤½¦¡(¥u¼g´X³õ«ÜÃø²z¸Ñ)

Sub ex5()
Dim d As Object, ar As Object, r
Dim i%, AA$, a
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
Set ar = Sheets("¹ï¾Ô²Î­p").[a1].CurrentRegion
For i = 1 To ar.Rows.Count
   AA = Join(Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 102))), ",") & "," & ar(i, 106) '«Ø¥ß§PÂ_±ø¥ó
   If Not d.exists(AA) Then   '¦r¨å¤º¬dµL¸Ó±ø¥ó
      d(AA) = Application.Transpose(Application.Transpose(ar(i, 1).Resize(, 115)))
   Else
      a = Application.Transpose(Application.Transpose(d(AA)))   '±N¦r¨å¸ê®Æ¨ú¥X
      a(103) = a(103) + ar(i, 103) + 1 '²Ä¤Gµ§¥H¤W³Ó³õ³£¦h¥[1
      For Each r In Array(104, 107, 109, 115) '±N³Æµù,DC,DE,DKÄæ¦ì¸ê®Æ¦X¨Ö
         If a(r) <> "" And ar(i, r) <> "" Then '¦pªG¦r¨å»PÄæ¦ì³£¦³¸ê®Æ,¨Ï¥Î","¬Û³s
            a(r) = a(r) & "," & ar(i, r)
         ElseIf a(r) = "" And ar(i, r) <> "" Then '¦pªG¦r¨å¸ê®Æ¬°ªÅ¥Õ,Äæ¦ì¬O¦³¸ê®Æªº,¨Ï¥ÎÄæ¦ì¸ê®Æ
            a(r) = ar(i, r)
         End If
      Next
      d(AA) = a   '±N¸ê®Æ©ñ¦^¦r¨å
   End If
Next
With Sheets(2)  '¦b²Ä¤G­ÓSheet¶ñ¤J¸ê®Æ
.[a1].CurrentRegion.Clear '²M°£Sheet¸ê®Æ
.[a1].Resize(d.Count, UBound(a)) = Application.Transpose(Application.Transpose(d.items)) '±N¦r¨å¸ê®Æ¦C¥X
End With
Set d = Nothing
Application.ScreenUpdating = True
End Sub
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-30 01:10

¦^´_ 43# wei9133

¦³ªÅÀ°§Ú¬Ý¤@¤U ¬O¤£¬O³o¼Ë  ¦pªG¦³°ÝÃD ½Ð§i¶D§Ú°ÝÃD¥X¦b­þ¸Ì ·PÁÂ
javascript:;
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-30 01:29

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-30 01:36 ½s¿è

¦^´_ 43# wei9133

©ÎµÛ§ï¦¨³o¼Ë ¬Ý¬Ý ¬O¤£¬O§A­nªºµ²ªG  ÁÙ¬O»¡  jcchiang«e½ú ªº¤~¬O§A­nªºµ²ªG
  1. Public Sub ½m²ß1030()
  2. Sheets(2).Select
  3. Rows(2).Select
  4. ActiveWindow.FreezePanes = False
  5. Application.ScreenUpdating = False
  6. Sheets(2).[a1].CurrentRegion.Clear
  7. Sheets(1).Select
  8. Dim Arr, d, xD, x&, y&, k&, T1$, T2$, T3$, T4$
  9. Set xD = CreateObject("Scripting.Dictionary")
  10. Arr = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, 115))
  11. For x = 2 To UBound(Arr, 1)
  12.     T1 = ""
  13.     For y = 1 To 51
  14.         T1 = T1 & Arr(x, y)
  15.         If Arr(x, y) = "" Then T1 = T1 & "-"
  16.     Next y
  17.     T3 = ""
  18.     For y = 52 To 102
  19.         T3 = T3 & Arr(x, y)
  20.         If Arr(x, y) = "" Then T3 = T3 & "-"
  21.     Next y
  22.     If T1 = T3 Then
  23.        T1 = T1 & T3 & Arr(x, 106)
  24.        T3 = ""
  25.         If Arr(x, 103) = "" Then
  26.            Arr(x, 103) = 1
  27.            xD(T1) = xD(T1) + Arr(x, 103)
  28.         ElseIf Arr(x, 103) <> "" Then
  29.            xD(T1) = xD(T1) + Arr(x, 103)
  30.         End If
  31.         xD(T1 & 105) = xD(T1 & 105) + Arr(x, 105)
  32.     End If
  33. Next x
  34. T1 = "": T3 = ""
  35. For Each d In xD
  36.     For x = UBound(Arr, 1) To 2 Step -1
  37.         T2 = ""
  38.         For y = 1 To 51
  39.             T2 = T2 & Arr(x, y)
  40.             If Arr(x, y) = "" Then T2 = T2 & "-"
  41.         Next y
  42.         T4 = ""
  43.         For y = 52 To 102
  44.             T4 = T4 & Arr(x, y)
  45.             If Arr(x, y) = "" Then T4 = T4 & "-"
  46.         Next y
  47.         If T2 = T4 Then
  48.             T2 = T2 & T4 & Arr(x, 106)
  49.             T4 = ""
  50.             If d = T2 Then
  51.                 E = E + 1
  52.                 If E = 1 Then
  53.                    If Arr(x, 103) > 0 Then Arr(x, 103) = xD(d)
  54.                    If Arr(x, 103) <= 1 Then Arr(x, 103) = ""
  55.                 Else
  56.                     Arr(x, 103) = xD(d) - 1
  57.                     If Arr(x, 103) < 0 Then Arr(x, 103) = Arr(x, 103) * -1
  58.                 End If
  59.                 Arr(x, 105) = xD(d & 105)
  60.                 If xD(d & 105) = 0 Then Arr(x, 105) = ""
  61.             End If
  62.         End If
  63.     Next x
  64.     E = 0
  65. Next d
  66. T2 = "": T4 = "": d = "": k = 1
  67. Set xD = Nothing
  68. For x = 2 To UBound(Arr, 1)
  69.     If Arr(x, 107) <> "" Or Arr(x, 109) <> "" _
  70.     Or Arr(x, 115) <> "" Or Arr(x, 104) <> "" Then
  71.         k = k + 1
  72.         For y = 1 To UBound(Arr, 2)
  73.             Arr(k, y) = Arr(x, y)
  74.         Next y
  75.     End If
  76. Next x
  77. T2 = "": T4 = ""
  78. Sheets(2).Range("A1").Resize(k, UBound(Arr, 2)) = ""
  79. Sheets(2).Range("A1").Resize(k, UBound(Arr, 2)) = Arr
  80. Erase Arr
  81. Application.ScreenUpdating = True
  82. Sheets(2).Select
  83. Rows(2).Select
  84. ActiveWindow.FreezePanes = True
  85. Cells(Rows.Count, 106).End(xlUp).Select
  86. End Sub
½Æ»s¥N½X

§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-30 10:09

¦^´_ 43# wei9133
©êºp­è¤~µo²{²Ö¥[³Ó²v¦³°ÝÃD §ï¤@¤U  ¦³±±¦AÀ°§Ú¬Ý¤@¤U  ·PÁÂ


javascript:;
§@ªÌ: ­ã´£³¡ªL    ®É¶¡: 2020-10-30 10:41

°ò¥»·§©À:
¸ê®ÆªíÀ³¬O"¬y¤ôªí"»P"²Î­pªí"¤À¶},
1) ¬y¤ôªí: ¬°©Ò¦³¹ï¾Ô°O¿ý, ¥i­«ÂÐ, ¤]¥i²Ö¿n, ¤]¥i±N¤w³Q²Î­p¹Lªº§R°£, ´î¤Ö¤ñ¹ï¤u§@¤Î®É¶¡,
    ³Ó³õ¬°ªÅªº, ªí¥Ü¬O·s°O¿ý, ²Î­p¹L¤F¶ñ¤J1, ¥H§K¦A°õ¦æ²Î­p®É¤S­p¤@¦¸
2) ²Î­pªí: ¥u¯d¦U²Õ¦Xªº°ß¤@, ²զXª½±µ²Ö­p, ·s²Õ¦X«h·s¼W¤@µ§, «OÃÒ¤£­«ÂÐ,
    ¥²¶·¦³¹ï¾ÔÁ`¦¸¼Æ, ¤Î³Ó³õ¼Æ, ¤~¯à´«ºâ³Ó²v, ²Î­p§¹«á, ¥HÁ`¹ï¾Ô¼Æ¬°¥D,³Ó²v¬°¦¸±Æ§Ç,
   __¹L¥h¤w¦³ªº¹ï¾Ô°O¿ý²Î­p, ¶·¨Æ¥ý¤â°Ê«Ø¥ß
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-30 20:30

¦^´_ 49# ­ã´£³¡ªL


·PÁ ·Ç¤j«ü¾É ¤£ª¾¹D³o¼Ë§ï ¬O¤£¬O¦³±µªñ ·Ç¤j»¡ªº¤èªk
¬Ý°_¨ÓÁÙ¬O¦³®t«Ü¦h µ²ªG»P jcchiang«e½úªº¤£¦P  ¤£ª¾¦p¦ó­×§ï...
  1. Public Sub ½m²ß1030_02()
  2. Sheets(2).Select
  3. Rows(2).Select
  4. ActiveWindow.FreezePanes = False
  5. Application.ScreenUpdating = False
  6. Sheets(2).[a1].CurrentRegion.Clear
  7. Sheets(1).Select
  8. Dim Arr, D, xD, x&, y&, k&, T1$, T2$, T3$, T4$
  9. Set xD = CreateObject("Scripting.Dictionary")
  10. Arr = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, 115))
  11. For x = 2 To UBound(Arr, 1)
  12.     T1 = ""
  13.     For y = 1 To 51
  14.         T1 = T1 & Arr(x, y)
  15.         If Arr(x, y) = "" Then T1 = T1 & "-"
  16.     Next y
  17.     T3 = ""
  18.     For y = 52 To 102
  19.         T3 = T3 & Arr(x, y)
  20.         If Arr(x, y) = "" Then T3 = T3 & "-"
  21.     Next y
  22.     If T1 = T3 Then
  23.         T1 = T1 & T3 & Arr(x, 106)
  24.         T3 = ""
  25.         If Arr(x, 103) = "" Then
  26.            Arr(x, 103) = 1
  27.            xD(T1) = xD(T1) + Arr(x, 103)
  28.         ElseIf Arr(x, 103) <> "" Then
  29.            xD(T1) = xD(T1) + Arr(x, 103) + 1
  30.         End If
  31.         xD(T1 & 105) = xD(T1 & 105) + Arr(x, 105)
  32.     End If
  33. Next x
  34. T1 = "": k = 1
  35. For Each D In xD
  36.     For x = 2 To UBound(Arr, 1)
  37.         T2 = ""
  38.         For y = 1 To 51
  39.             T2 = T2 & Arr(x, y)
  40.             If Arr(x, y) = "" Then T2 = T2 & "-"
  41.         Next y
  42.         T4 = ""
  43.         For y = 52 To 102
  44.             T4 = T4 & Arr(x, y)
  45.             If Arr(x, y) = "" Then T4 = T4 & "-"
  46.         Next y
  47.         If T2 = T4 Then
  48.             T2 = T2 & T4 & Arr(x, 106)
  49.             T4 = ""
  50.             If D = T2 Then
  51.                 k = k + 1
  52.                 Arr(x, 103) = xD(D) - 1
  53.                 Arr(x, 105) = xD(D & 105)
  54.                 If Arr(x, 107) <> "" Or Arr(x, 109) <> "" _
  55.                 Or Arr(x, 115) <> "" Or Arr(x, 104) <> "" Then
  56.                      For y = 1 To UBound(Arr, 2)
  57.                          Arr(k, y) = Arr(x, y)
  58.                          If Arr(k, 103) = 0 Then Arr(k, 103) = ""
  59.                          If Arr(k, 105) = 0 Then Arr(k, 105) = ""
  60.                      Next y
  61.                 Exit For
  62.                 End If
  63.             End If
  64.         End If
  65.     Next x
  66. Next D
  67. T2 = "": Set xD = Nothing
  68. Sheets(2).Range("A1").Resize(k - 1, UBound(Arr, 2)) = ""
  69. Sheets(2).Range("A1").Resize(k - 1, UBound(Arr, 2)) = Arr
  70. Erase Arr
  71. Application.ScreenUpdating = True
  72. Sheets(2).Select
  73. Rows(2).Select
  74. ActiveWindow.FreezePanes = True
  75. Cells(Rows.Count, 106).End(xlUp).Select
  76. End Sub
½Æ»s¥N½X

§@ªÌ: ­ã´£³¡ªL    ®É¶¡: 2020-10-31 09:19

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


§Ú¤]¤£ª¾¹ï¤£¹ï? __­pºâÅÞ¿è¤]ÁÙ·d¤£²M·¡
¨Ìªí¨Ó¬Ý, ¥ª¤è¬°³Ó¤è, ¥k¤è¬°±Ñ¤è, ¦ý¦p¦óª¾¹D"§Ú¤è"¬O¥ªÁÙ¬O¥k???
©Ò¥H, ³oªí¥u¯à²Î­p"³Ó³õ¼Æ", ¦Ó«D"³Ó²v"
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-10-31 12:12

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-31 12:18 ½s¿è

¦^´_ 41# wei9133

½Ð°Ý ³Ó²v ²z½×¤WÀ³¸Ó¬O   ³Ó²v = ³Ó³õ/(³Ó³õ+±Ñ§½)*100%   ¬O§_¬O³o¼Ë?
¦pªG +1  -1  ¦n¹³©Ç©Çªº
Cells(x, 116) = Format(Cells(x, 103) / (Cells(x, 103) + Cells(x, 105)), "###%")
§@ªÌ: wei9133    ®É¶¡: 2020-10-31 23:59

¦^´_ 49# ­ã´£³¡ªL


   
°ò¥»·§©À:
¸ê®ÆªíÀ³¬O"¬y¤ôªí"»P"²Î­pªí"¤À¶},
1) ¬y¤ôªí: ¬°©Ò¦³¹ï¾Ô°O¿ý, ¥i­«ÂÐ, ¤]¥i²Ö¿n, ¤]¥i±N¤w³Q²Î­p¹Lªº§R°£, ´î¤Ö¤ñ¹ï¤u§@¤Î®É¶¡,
    ³Ó³õ¬°ªÅªº, ªí¥Ü¬O·s°O¿ý, ²Î­p¹L¤F¶ñ¤J1, ¥H§K¦A°õ¦æ²Î­p®É¤S­p¤@¦¸
2) ²Î­pªí: ¥u¯d¦U²Õ¦Xªº°ß¤@, ²զXª½±µ²Ö­p, ·s²Õ¦X«h·s¼W¤@µ§, «OÃÒ¤£­«ÂÐ,
    ¥²¶·¦³¹ï¾ÔÁ`¦¸¼Æ, ¤Î³Ó³õ¼Æ, ¤~¯à´«ºâ³Ó²v, ²Î­p§¹«á, ¥HÁ`¹ï¾Ô¼Æ¬°¥D,³Ó²v¬°¦¸±Æ§Ç,
   __¹L¥h¤w¦³ªº¹ï¾Ô°O¿ý²Î­p, ¶·¨Æ¥ý¤â°Ê«Ø¥ß


§Ú¥Ø«e¬O³o³£¥u°µ¨ì§A¼gªº²Ä¤@¬q¡A¤â°Ê°õ¦æ²Ä¤G¬q
(¬d¸ß¨ìªº®É­Ôµo²{¤@¼Ëªº´N§â¤@¼Ëªº¦X¨Ö¡A³Ó³õÄæ¦ì¥[Á`¡A­«½Æªº§R±¼)


§Ú¤]¤£ª¾¹ï¤£¹ï? __­pºâÅÞ¿è¤]ÁÙ·d¤£²M·¡
¨Ìªí¨Ó¬Ý, ¥ª¤è¬°³Ó¤è, ¥k¤è¬°±Ñ¤è, ¦ý¦p¦óª¾¹D"§Ú¤è"¬O¥ªÁÙ¬O¥k???
©Ò¥H, ³oªí¥u¯à²Î­p"³Ó³õ¼Æ", ¦Ó«D"³Ó²v"


¥ªÃä¬O±Ñ¤è¡A¥kÃä¬O³Ó¤è¡A¤£¤À¼Ä§Ú
¬d¸ßªº®É­Ô±N¹ï¤è¥X¾Ô¨¤¦â(»Ý±Ñ¤è)¿z¿ï°_¨Ó¡A´N¥i¥H¬Ý¥k¥b³¡ªº³Ó¤è¡A¦³­þ´XºØ²Õ¦X¥i³Ó¡AÁ`¦@³Ó¤F´X³õ¡A±Ñ¤F´X³õ
µM«á¥Î¥k(³Ó)¤èªº²Õ¦X¥h¥´¥ª(±Ñ)¤èªº²Õ¦X

ªº½T¡ACY¨ºÄæ½T¤Á¦WºÙÀ³¸Ó¥s³Ó³õ¼Æ¡A¦Ó«D³Ó²v¡C

§@ªÌ: wei9133    ®É¶¡: 2020-11-1 00:10

¦^´_  wei9133

½Ð°Ý ³Ó²v ²z½×¤WÀ³¸Ó¬O   ³Ó²v = ³Ó³õ/(³Ó³õ+±Ñ§½)*100%   ¬O§_¬O³o¼Ë?
¦pªG +1  -1  ...
°a¤ªºµ µoªí©ó 2020-10-31 12:12


¦]¬°§Ú¥´ªº¦WºÙ¤£ÄYÂÔ³y¦¨»~·|orz
§Ú§â®æ¤l¦WºÙ§ï±¼¤F¡ACY¬O³Ó³õDA¬O±Ñ³õ
¦p­ã´£³¡ªL´£¨ì¡A¨º­Ó®æ¤lÀ³¸Ó¬O³Ó³õ¼Æ¡A«á­±À³¸Ó¬O±Ñ³õ¼Æ
·s¦C¥»¨­´N¥Nªí¤@¦¸³Ó§Q¡A¦³¬Û¦P±ø¥óªº³Ó§Q´N¦X¨Ö¡AµM«á§â³Ó³õ¼Æ¥[¤W¥h
±Ñ³õ«h¬Oª½±µ¥[¤W¥h
¥ª¤è¬°¿é±¼ªº²Õ¦X¡A¥k¤è¬°¥i¥H¥´Ä¹¥ª¤è°t¸mªº²Õ¦X

¦Ü©ó±Ñ³õ«ç»ò¨Óªº
¥Î¿z¿ï§ä¥X¹ï¤è¥X¾Ôªº¨¤¦â¡AµM«á¬Ý¥kÃä¥i¥HĹªº²Õ¦X¡A®³¥h¥´
µ²ªG¿é¤F¡A±Ñ³õ´N¦Û¤v¥[1¤W¥h

¦]¬°¹ï¾Ô¬O¦³¾÷²v©Êªº¡A´Nºâ¬O¥i¥HĹªº²Õ¦X¤]¥i¯à·|¿é¡A©Ò¥H¤~¦³¶ñ¤W±Ñ³õªº®æ¤l
¥i¯à²Ä¤@¦¸²Î­pªº®É­Ô¹®­Æ¥´¹L¤F¡AµM«á¥Î¦P¼Ë²Õ¦X¦A¥h¥´¡Aµ²ªG¤@ª½¿é
±Ñ§½´N·|¤@ª½¥[¤W¥h¡A­Y±Ñ³õ»·¤j©ó³Ó³õ¡A´N¥Nªí²Î­p¿ù»~
§Ú·|¦b¦Û¤v§â¥¦ÄA­Ë¹L¨Ó
(§â¥ª¥kªº°t¸mÄA­Ë¡A±Ñ³Ó³õªº¼Æ¦r¤]´«¹L¨Ó)
¨Ò¦p

AB VS CD  Ä¹5 ¿é10
¦Û¤v§â¥¦§ï¦¨
CD VS AV Ĺ10 ¿é5

¦Ü©ó§A¤W­±µ¹ªºvba§Ú±ß¤@ÂI§ä®É¶¡´ú¸Õ
·PÁÂ
§@ªÌ: wei9133    ®É¶¡: 2020-11-2 02:53

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


    §A¦n#50ªº¥¨¶°¡AµLªk¥¿±`°õ¦æ
[attach]32667[/attach]
[attach]32668[/attach]
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-11-2 20:36

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-11-2 20:39 ½s¿è

¦^´_ 55# wei9133

§A¦³·s¼W ¤u§@ªí¶Ü?  ¥Î³o­Ó¸Õ¸Õ¬Ý  
§Ú¦³§â jcchiang«e½úªº¤]©ñ¶i¥h¤F  Sub ex5()  µ²ªG¤£¤Ó¤@¼Ë  
¦A¬Ý¬Ý§Ú­þ¸Ì¦³°ÝÃD¦b§i¶D§Ú ·PÁÂ

javascript:;
§@ªÌ: wei9133    ®É¶¡: 2020-11-6 22:02

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


  §A¦n¡A²Ê²¤ª½±µ´ú¸Õ§Aµ¹ªºÀɮסA§A¥i¯à¨S¦³¤ñ¹ï¨ì106(¬P¶H)Äæ
ª½±µ±NÀɮ׸ü¦^¡A¨Ã±N¬P¶HÄæ¥H¼Æ¦C¤U©Ô
Åý¨äÅܦ¨1~17¡A°õ¦æ¤§«á²z½×¤W¨ÓÁ¿¡A¦]¬°17­Ó¬P¶H¦ì¸m³£¤£¦P©Ò¥H¦Ü¤ÖÀ³¸Ó­n¦³17¦C
[attach]32670[/attach]
¹ê»Ú®ÄªG«o¬O
¦³¨â­Ó14¡A16¡B17®ø¥¢¡A¥Nªí³o­Ó¦b³o¸Ì¤w¸g¬O¦³°ÝÃDªº¤F
[attach]32671[/attach]
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-11-11 15:18

¦^´_ 57# wei9133
À°§Ú¬Ý¤@¤U ³oµ²ªG ¥i¤£¥i¥H  ·PÁÂ

javascript:;
§@ªÌ: wei9133    ®É¶¡: 2020-11-13 03:51

¥»©«³Ì«á¥Ñ wei9133 ©ó 2020-11-13 03:52 ½s¿è

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


    §A¦n¡A¥Ø«e´ú¸ÕÁÙ¦³¨Ç°ÝÃD¡A³Ó³õ¥[Á`³¡¤À¹ï¤F
¤£¹L¤ñ¹ï³¡¤À¦³¨Ç°ÝÃD¡C
¸Ô²Ó½Ð§A¬Ý¹Ï¤ù

[attach]32680[/attach]
[attach]32681[/attach]
[attach]32682[/attach]
[attach]32683[/attach]
[attach]32684[/attach]
[attach]32685[/attach]
°ÝÃD±q³o¸Ì¶}©l
[attach]32686[/attach]

[attach]32687[/attach]

¦pªG½×¾Âªº¹Ï¤£¤è«K¬Ýªº¸Ü
³Â·Ð²¾¾r¬Ûï
(½Ð±q³Ì«á¤@±i©¹«e¬Ý)
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-11-14 22:03

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-11-14 22:05 ½s¿è

¦^´_ 59# wei9133

¥ª¥k¤£¥Î¤ñ¹ï¤F¶Ü?    §Ú§â¥ª¥k¤ñ¹ïµù¸Ñ±¼¤F  ¦³ªÅ§A¦A¸Õ¸Õ¬Ý µ²ªG¬O§_¥i¥H ·PÁÂ


javascript:;
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-11-14 22:22

¦^´_ 59# wei9133

¦pªG¥ª¥k¤£¥Î¤ñ¹ï ¨º´Nª½±µ¤ñ¹ï¬P¶H ¦A¶i¦æ³Ó³õ ¸ò ±Ñ³õ ¥[Á`¥i¥H¶Ü?
§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-11-16 23:44

¦^´_ 59# wei9133

¦³ªÅÀ°§Ú¸Õ¸Õ¬Ý  ³o­ÓÀ³¸Ó¥i¥H  ¦ý¬O¦³¤@­Ó«Ü¤jªº°ÝÃD ...¦pªG¸ê®Æ«Ü¦h ·|¶]«D±`ºC....
  1. Public Sub ½m²ß1116()
  2. Sheets(2).Select
  3. Rows(2).Select
  4. ActiveWindow.FreezePanes = False
  5. Application.ScreenUpdating = False
  6. Sheets(2).[A1].CurrentRegion.Clear
  7. Sheets(1).Select
  8. Dim Arr, D, xD, x&, y&, k&, T1$, T2$, T3$, T4$
  9. Set xD = CreateObject("Scripting.Dictionary")
  10. Arr = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, 115))
  11. For x = 2 To UBound(Arr, 1)
  12.     T1 = ""
  13.     For y = 1 To 51
  14.         T1 = T1 & Arr(x, y)
  15.         If Arr(x, y) = "" Then T1 = T1 & "-"
  16.     Next y
  17.     T3 = ""
  18.     For y = 52 To 102
  19.         T3 = T3 & Arr(x, y)
  20.         If Arr(x, y) = "" Then T3 = T3 & "-"
  21.     Next y
  22.     T1 = T1 & T3 & Arr(x, 106)
  23.     T3 = ""
  24.     If Arr(x, 103) = "" Then
  25.         Arr(x, 103) = 1
  26.         xD(T1) = xD(T1) + Arr(x, 103)
  27.     ElseIf Arr(x, 103) <> "" Then
  28.         xD(T1) = xD(T1) + Arr(x, 103) + 1
  29.     End If
  30.     xD(T1 & 105) = xD(T1 & 105) + Arr(x, 105)
  31.     xD(Arr(x, 106)) = xD(Arr(x, 106)) + 1
  32. Next x
  33. T1 = "": k = 2
  34. For Each D In xD
  35.     For x = 2 To UBound(Arr, 1)
  36.         T2 = ""
  37.         For y = 1 To 51
  38.             T2 = T2 & Arr(x, y)
  39.             If Arr(x, y) = "" Then T2 = T2 & "-"
  40.         Next y
  41.         T4 = ""
  42.         For y = 52 To 102
  43.             T4 = T4 & Arr(x, y)
  44.             If Arr(x, y) = "" Then T4 = T4 & "-"
  45.         Next y
  46.         T2 = T2 & T4 & Arr(x, 106)
  47.         T4 = ""
  48.         If D = T2 Then
  49.             Arr(x, 103) = xD(D) - 1
  50.             Arr(x, 105) = xD(D & 105)
  51.             If Arr(x, 107) <> "" Or Arr(x, 109) <> "" _
  52.             Or Arr(x, 115) <> "" Or Arr(x, 104) <> "" Then
  53.                  For y = 1 To UBound(Arr, 2)
  54.                      Arr(k, y) = Arr(x, y)
  55.                  Next y
  56.             k = k + 1
  57.             Exit For
  58.             End If
  59.         End If
  60.         If D = Arr(x, 106) And xD(D) = 1 _
  61.         And Arr(x, 107) = "" And Arr(x, 109) = "" _
  62.         And Arr(x, 115) = "" And Arr(x, 104) = "" Then
  63.             For y = 1 To UBound(Arr, 2)
  64.                 Arr(k, y) = Arr(x, y)
  65.             Next y
  66.         k = k + 1
  67.         Exit For
  68.         End If
  69.     Next x
  70. If Arr(k - 1, 103) = 0 Then Arr(k - 1, 103) = ""
  71. If Arr(k - 1, 105) = 0 Then Arr(k - 1, 105) = ""
  72. Debug.Print k
  73. Debug.Print D
  74. Next D
  75. T2 = "": Set xD = Nothing
  76. Sheets(2).Range("A1").Resize(k - 1, UBound(Arr, 2)) = ""
  77. Sheets(2).Range("A1").Resize(k - 1, UBound(Arr, 2)) = Arr
  78. Erase Arr
  79. Application.ScreenUpdating = True
  80. Sheets(2).Select
  81. Rows(2).Select
  82. ActiveWindow.FreezePanes = True
  83. Cells(Rows.Count, 106).End(xlUp).Select
  84. End Sub
½Æ»s¥N½X

§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-11-18 15:12

¦^´_ 59# wei9133

³o·|¤ñ¸û§Ö¤@ÂI ¦ý¬OÁÙ¬O«ÜºC... ¦³ªÅÀ°§Ú¸Õ¸Õ¬Ý¦³¨S¦³°ÝÃD   ·PÁÂ
  1. Public Sub ½m²ß1118()
  2. Sheets(2).Select
  3. Rows(2).Select
  4. ActiveWindow.FreezePanes = False
  5. Application.ScreenUpdating = False
  6. Sheets(2).[A1].CurrentRegion.Clear
  7. Sheets(1).Select
  8. Dim Arr, D, xD, x&, y&, k&, T1$, T3$, E()
  9. Set xD = CreateObject("Scripting.Dictionary")
  10. Arr = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, 115))
  11. For x = 2 To UBound(Arr, 1)
  12.     T1 = ""
  13.     For y = 1 To 51
  14.         T1 = T1 & Arr(x, y)
  15.         If Arr(x, y) = "" Then T1 = T1 & "|"
  16.     Next y
  17.     T3 = ""
  18.     For y = 52 To 102
  19.         T3 = T3 & Arr(x, y)
  20.         If Arr(x, y) = "" Then T3 = T3 & "|"
  21.     Next y
  22.     T1 = T1 & T3 & Arr(x, 106)
  23.     ReDim Preserve E(x)
  24.     E(x) = T1
  25.     T3 = ""
  26.     If Arr(x, 103) = "" Then
  27.         Arr(x, 103) = 1
  28.         xD(T1) = xD(T1) + Arr(x, 103)
  29.     ElseIf Arr(x, 103) <> "" Then
  30.         xD(T1) = xD(T1) + Arr(x, 103) + 1
  31.     End If
  32.     xD(T1 & 105) = xD(T1 & 105) + Arr(x, 105)
  33.     xD(Arr(x, 106)) = xD(Arr(x, 106)) + 1
  34. Next x
  35. T1 = "": k = 2
  36. For Each D In xD
  37.     For x = 2 To UBound(Arr, 1)
  38.         If D = E(x) Then
  39.             Arr(x, 103) = xD(D) - 1
  40.             Arr(x, 105) = xD(D & 105)
  41.             If Arr(x, 107) <> "" Or Arr(x, 109) <> "" _
  42.             Or Arr(x, 115) <> "" Or Arr(x, 104) <> "" Then
  43.                  For y = 1 To UBound(Arr, 2)
  44.                      Arr(k, y) = Arr(x, y)
  45.                  Next y
  46.             k = k + 1
  47.             Exit For
  48.             End If
  49.         End If
  50.         If D = E(x) And xD(D) = 1 _
  51.         And Arr(x, 107) = "" And Arr(x, 109) = "" _
  52.         And Arr(x, 115) = "" And Arr(x, 104) = "" Then
  53.             For y = 1 To UBound(Arr, 2)
  54.                 Arr(k, y) = Arr(x, y)
  55.             Next y
  56.         k = k + 1
  57.         Exit For
  58.         End If
  59.     Next x
  60. If Arr(k - 1, 103) = 0 Then Arr(k - 1, 103) = ""
  61. If Arr(k - 1, 105) = 0 Then Arr(k - 1, 105) = ""
  62. Next D
  63. Set xD = Nothing: Erase E
  64. Sheets(2).Range("A1").Resize(k - 1, UBound(Arr, 2)) = Arr
  65. Erase Arr
  66. Application.ScreenUpdating = True
  67. Sheets(2).Select
  68. Rows(2).Select
  69. ActiveWindow.FreezePanes = True
  70. Cells(Rows.Count, 106).End(xlUp).Select
  71. End Sub
½Æ»s¥N½X

§@ªÌ: °a¤ªºµ    ®É¶¡: 2020-11-20 21:18

¦^´_ 59# wei9133

­è¤~¸Õ¤F¤@¤Uµo²{ 106¬P¶H¦³°ÝÃD §ï¤F¤@¤U  ¡A¦³ªÅ¦AÀ°§Ú¸Õ¸Õ¬Ý ¦³¨S¦³°ÝÃD¡A·PÁÂ

javascript:;




Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)