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

[µo°Ý] vbaªº¿z¿ï¥\¯à (¨ú®ø³¡¤À¿z¿ï)

¥»©«³Ì«á¥Ñ °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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ 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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ °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:;
1016.png

TOP

¦^´_ 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À³¸Ó°÷
¹ï¾Ô²Î­p - ½Æ»s.rar (666.38 KB)
  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
(³Ó¸ò±Ñ¤¤¶¡¦³¤@­Ó³ÆµùÄæ¦ì)
Microsoft_Excel_-_¹ï¾Ô²Î­p_-_½Æ»s.xls-2020-10-18-01-26-43.png
2020-10-18 01:29

Microsoft_Visual_Basic_-_¹ï¾Ô²Î­p_-_½Æ»s.xls_[°õ¦æ¤¤]_-_[Mo-2020-10-18-01-26-11.png
2020-10-18 01:29

Microsoft_Visual_Basic_-_¹ï¾Ô²Î­p_-_½Æ»s.xls_[¤¤Â_]_-_[Mod-2020-10-18-01-26-19.png
2020-10-18 01:29


¥[Á`«áÀ³¸ÓÀ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
Microsoft_Excel_-_¹ï¾Ô²Î­p_-_½Æ»s.xls-2020-10-18-01-49-27.png
2020-10-18 01:55

Microsoft_Excel_-_¹ï¾Ô²Î­p_-_½Æ»s.xls-2020-10-18-01-49-47.png
2020-10-18 01:55

Microsoft_Excel_-_¹ï¾Ô²Î­p_-_½Æ»s.xls-2020-10-18-01-50-04.png
2020-10-18 01:55


¥Ø«e¬O¤ñ¹ïA~CX and DBªø¤@¼Ë
§â¤W­z±ø¥ó¤@¼Ëªº¦CªºCY(³Ó³õ) | DA(±Ñ³õ)  ¥[Á`¶ñ¤J¦P¤@¦C
¦Ü©ó¶ñ¤J­þ¤@¦CÀu¥ý¦Ò¼{   DC OR DE OR DK¦³­È ¨ä¦¸¬OCZ¦³­È
Microsoft_Excel_-_¹ï¾Ô²Î­p.xls-2020-10-18-02-11-54.png
2020-10-18 02:13

jcchiang©Ò²z¸Ñªº±ø¥ó¬O¹ïªº
Microsoft_Excel_-_¹ï¾Ô²Î­p_-_½Æ»s.xls-2020-10-18-01-49-27.png
Microsoft_Excel_-_¹ï¾Ô²Î­p_-_½Æ»s.xls-2020-10-18-01-49-47.png
Microsoft_Excel_-_¹ï¾Ô²Î­p_-_½Æ»s.xls-2020-10-18-01-50-04.png

TOP

¦^´_ 26# wei9133

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

javascript:;

¹ï¾Ô²Î­p - ½Æ»s1019.rar (23.49 KB)

TOP

¦^´_ 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

TOP

¦^´_ 28# jcchiang

§A¦n
¥ý¥H5®æ¬°°ò·Çª½±µ°õ¦æ
¸g´ú¸Õ¡A³Ó²v³¡¤À¤£ºÞ¦³¨S¦³­«½Æ³£·|ª½±µ+1¤W¥h
¹Ï¤@
¹Ï1.png
2020-10-20 22:59

(­ì©l¸ê®Æ)
¹Ï¤G
¹Ï2.png
2020-10-20 22:59

(²£¥X¸ê®Æ)

³Ó²v³o®æ¦³°ÝÃDªº¼Ë¤l
§ÚÁ`¦@¥H5®æ°µ´ú¸Õ¡A1~5®æ½Æ»s¤@¥÷©ñ¦b¤U­±¦C¤¤¡A¶È§ó§ï¬P¶H³¡¤À
¥X¨Óµ²ªG¬O¿ù»~ªº
¹Ï¤T
¹Ï3.png
2020-10-20 22:59

(­ì©l¸ê®Æ)
¹Ï¥|
¹Ï4.png
2020-10-20 22:59

(²£¥X¸ê®Æ)

¥¿½TÀ³¸Ó¬°
¹Ï5
¹Ï5.png
2020-10-20 22:59




¥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ÁÂ

TOP

¦^´_ 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
¹Ï1.png
2020-10-20 23:19


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

¹Ï2
¹Ï2.png
2020-10-20 23:19

ªì©l

¹Ï3
¹Ï3.png
2020-10-20 23:19

°õ¦æ«á

¥¿½TÀ³¸Ó¬O
¹Ï4
¹Ï4.png
2020-10-20 23:19

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD