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

[µo°Ý] ­«½Æ¤º®e®É¶¡¥[Á`¨Ã§R°£­«½Æ«O¯d°ß¤@­È

¦^´_ 9# samwang


    ·PÁ«e½úªº«ü¾É, ¨âºØ¤è¦¡³£¦³¾Ç°_¨Ó!! ¥H«á¦³°ÝÃD¦A½Ð«e½ú¦h¦h«ü±Ð!!

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-15 20:07 ½s¿è

¦^´_ 10# v03586

§A·|¥Î¦r¨åª«¥óªº¸Ü¡A¾ÇSamwangªº¼gªk´N¥i¥H¤F

hcm19522¤j¤j¡A¤]ÃÒ©ú¤F¨ç¼Æ¥i¥H¸Ñ¨M99.99% ªº°ÝÃD

§Úªº¼gªk¥²¶·­n¥ý¸g¹L±Æ¦C¡A¤£µM·|¦³°ÝÃD~~

¦^ÀY¬Ý§Ú¼gªºªF¦è¡A¼gªº¦³ÂI²ÖÂØ~~~²¤Â²¤Æ¦p¤U~


Sub ¥[Á`()
Dim Arr, PKey$, §R°£¦C As Range
Arr = [A1].CurrentRegion    '§ìÀx¦s®æ¸ê®Æ ¨ì Arr °}¦C
Set §R°£¦C = Rows(UBound(Arr) + 1)  '³o¦æ³æ¯Â¥u¬OÁקKUnion¸õ¥X¿ù»~¡A¥ý©w­Ó½d³ò
For R& = 2 To UBound(Arr)  '¤£¦PªºKey¬ö¿ýÀY¤@­Ó¦C¸¹¡A¬Û¦PKey°µ²Ö¥[¡A°O¿ý¤§«á­n§R°£ªº¦C¸¹
  Key$ = Arr(R, 9) & Arr(R, 10)
  If Key <> PKey Then
    R0& = R: PKey = Key
    Arr(R, 13) = Arr(R, 12)
  Else
    Arr(R0, 13) = Arr(R0, 13) + Arr(R, 12)
     Set §R°£¦C = Union(§R°£¦C, Rows(R))
  End If
Next R
[A1].Resize(UBound(Arr), UBound(Arr, 2)) = Arr  'Arr¸ê®Æ­Ë¦^¥hÀx¦s®æ
§R°£¦C.Delete   '¥i§ï¬° §R°£¦C.Select ½T»{§R°£½d³ò
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 1# v03586


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò,ÁÂÁ¦U¦ì«e½ú,ÁÂÁ½׾Â
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ßªº¸Ñ¨M¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:
20230324_1.jpg
2023-3-24 15:07


°õ¦æµ²ªG:
20230324_2.jpg
2023-3-24 15:08



Option Explicit
Sub TEST_2()
Dim Brr, Y, T$, C%, j%, i&, xA As Range
'¡ô«Å§iÅܼÆ:(Brr,Y)¬O³q¥Î«¬ÅܼÆ,T¬O¦r¦êÅܼÆ,
'(C,j)¬Oµu¾ã¼Æ,i¬Oªø¾ã¼Æ,xA¬OÀx¦s®æÅܼÆ

Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Set xA = Range([M1], Cells(Rows.Count, 1).End(3)): Brr = xA
'¡ô¥OxA³oÀx¦s®æÅܼƬO [M1]ÂX®i¨ìAÄæ³Ì«á¦³¤º®eÀx¦s®æ
'¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥HxAÅܼÆ(Àx¦s®æ­È)±a¤J

C = UBound(Brr, 2)
'¡ô¥OC³oµu¾ã¼ÆÅܼƬO Brr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q2¨ì Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Brr(i, 9) & "|" & Brr(i, 10)
   '¡ô¥OT³o¦r¦êÅܼƬO i°j°é¦C²Ä9ÄæBrr°}¦C­È ³s±µ "|",
   '¦A³s±µ i°j°é¦C²Ä10ÄæBrr°}¦C­È,©Ò²Õ¦¨ªº·s¦r¦ê

   If Y(T) = "" Then
   '¡ô¦pªGTÅܼƬdY¦r¨åªºitem­È¬OªÅ¦r¤¸?
   '(³o°Ý¥y¤w¸g±N TÅܼƷíkey,item¬OªÅ¦r¤¸,¯Ç¤JY¦r¨å¤F,¤w¼W¥[­Ó·skey)

      Y(T) = Y.Count + 1
      '¡ô¥O TÅܼƷíkey,item¬O Y¦r¨åkey¼Æ¶q + 1
      For j = 1 To C - 1: Brr(Y(T), j) = Brr(i, j): Next
      '¡ô³]¶¶°j°é!j±q1¨ì CÅܼÆ-1,³°Äò±N¸Ó¦C¦UÄæ­È±a¤J«ü©w¦C¦PÄæ¦ì¸m
      Brr(Y(T), 13) = Brr(Y(T), 12): GoTo i01
      '¡ô¥O(TÅܼƬdY¦r¨åitem­È)¦C²Ä13ÄæBrr°}¦C­È¬O
      '(TÅܼƬdY¦r¨åitem­È)¦C²Ä12ÄæBrr°}¦C­È
      '¥Oµ{§Ç¸õ¨ì i01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ

   End If
   Brr(Y(T), 13) = Brr(Y(T), 13) + Brr(i, 12)
   '¡ô¥O(TÅܼƬdY¦r¨åitem­È)¦C²Ä13ÄæBrr°}¦C­È¬O
   '¦Û¨­­È + (TÅܼƬdY¦r¨åitem­È)¦C²Ä12ÄæBrr°}¦C­È

i01: Next
ActiveSheet.UsedRange.Clear
'¡ô¥O¦³¨Ï¥ÎÀx¦s®æ½d³ò°µ²M°£
xA.Resize(Y.Count + 1, C) = Brr
'¡ô¥OxAÅܼÆ(Àx¦s®æ)²Ä1®æÂX®i¦V¤U Y¦r¨åkey¼Æ¶q+1¦C,
'¦V¥kÂX®iCÅܼÆÄæ,³o½d³òÀx¦s®æ­È¥HBrr°}¦C­È±a¤J

Set Y = Nothing: Set xA = Nothing: Erase Brr
'ÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD