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

[¤À¨É] §PÂ_¥X²{¤£¦P§å¸¹´X¦¸

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

Dear ­ã´£³¡ªL,
¦­¦w±z¦n¡I
VBA»yªk¨Ï¥Î¹L¡ATEST OK¡C«D±`·PÁ±zªº¼ö¤ßÀ°¦£¡I
^_^
Just do it.

TOP

­ã¤j¥u¥Î¤F¤@­Ó¦r¨å´N¸Ñ¨M¤F¡A¦Ó¥B®Ä²v¤]«Ü§Ö¡A·PÁ­ã¤j¤À¨É¡AÁÂÁ¡C

TOP

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

Dear ­ã´£³¡ªL,
±ß¤W¦n¡I»P±z½Ð±Ð¤@¤U¡A
§Úªº¸ê®Æ®wÄæ¦ì¦³²§°Ê¹L --> g=¥N¸¹¡Bu=Á`¤½¤ç¼Æ
§Ú±N±zªºVBA»yªk§ó§ï¹L¤U­±ªº¸ê®Æ(¬õ¦r)
¥i¬O§Ú§ï¹L«á¡A«oµLªk±a¥X§Ú·Q­nªºª¬ªp¡A¬O§_­þ¸ÌÁÙ¶·­×§ï©O¡H


Sub Analysis()

Dim Arr, xD, T$, TT$, i&, j%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([ú®w¶q!g1], [ú®w¶q!u65536].End(3)) 'g=¥N¸¹¡Bu=Á`¤½¤ç¼Æ
For i = 2 To UBound(Arr)
     T = Arr(i, 1): TT = T & "|" & Arr(i, 2)
     xD(TT) = xD(TT) + 1
     If xD(TT) = 1 Then xD(T & "/1") = xD(T & "/1") + 1
     xD(T & "/2") = xD(T & "/2") + Arr(i, 15) '15=g~u
Next
Arr = Range([Analysis!b1], [Analysis!a65536].End(3))
For i = 2 To UBound(Arr)
    For j = 1 To 2: Arr(i - 1, j) = xD(Arr(i, 1) & "/" & j): Next
Next
[Analysis!b2].Resize(UBound(Arr) - 1, 2) = Arr


End Sub
Just do it.

TOP

¦^´_ 23# jsc0518


¬Ý¨Ó¨S¿ù,
¬O­þ¸Ìªº°ÝÃD???

µ{¦¡µo¥Í¿ù»~? ÁÙ¬Oµ²ªG?

TOP

¦^´_ 24# ­ã´£³¡ªL
Dear ­ã´£³¡ªL,
©êºp¡A¸Õ¥X¨Ó¤F¡A§Ú§Ë¿ùÄæ¦ì¤F¡C·P®¦Åo¡I
Just do it.

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-31 15:18 ½s¿è

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


    ÁÂÁ«e½ú!
¥H¤U¤ß±oµù¸Ñ!½Ð«e½ú¦A«ü¾É!ÁÂÁÂ
Option Explicit
Sub TEST_A1()
Dim Arr, xD, T$, TT$, i&, j%
'¡ô«Å§iÅܼÆ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxD¬O¦r¨å
Arr = Range([ú®w¶q!e1], [ú®w¶q!y65536].End(3))
'¡ô¥OArr¬O¦r¨å!­Ë¤J ú®w¶qªí[E1] »PYÄæ³Ì«á-¦³¤º®eÀx¦s®æ¤§¶¡
',³Ì¤p¤è¥¿°Ï°ìÀx¦s®æªº­È

For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é!,i±q2 ¨ìArr°}¦C³Ì«á¤@Áa¦V¦C¼Æ
     T = Arr(i, 1)
     '¡ô¥O¬OArr°}¦C°j°é¦C¼Æ ²Ä1Áa¦VÄæ­È(ªí¸Ìªº¥N¸¹Äæ­È)
     TT = T & "|" & Arr(i, 2)
     '¡ô¥OTT¬O ¥N¸¹ & "|" & §å¸¹  ªº²Õ¦X¦r¦ê
     xD(TT) = xD(TT) + 1
     '¡ô²Õ¦X¦r¦ê·íkey!Item²Ö¥[1
     If xD(TT) = 1 Then
     '¡ô¦pªG²Õ¦X¦r¦êªºitem ¬O 1®É(TT²Õ¦X¦r¦ê­è¨ì¤J¦r¨å®É)
        xD(T & "/1") = xD(T & "/1") + 1
        '¡ô¥O ¥N¸¹ & "/1" ªº²Õ¦X¦r¦ê·íkey!Item²Ö¥[1
        '³o¸Ìªº "/1" ¬O¬°¤F«ü¦Vµ²ªG¸ê®ÆªºÄæ¦ì¼Æ1
  '@@
        '¤]´N¬O¦³¤£¦P²Õ¦X®É ¦P¥N¸¹¥X²{¤£¦P§å¸¹¦¸¼Æ+1
     End If
     xD(T & "/2") = xD(T & "/2") + Arr(i, 21)
     '¡ô¥O ¥N¸¹ & "/2" ªº²Õ¦X¦r¦ê·íkey!Item²Ö¥[Á`¤½¤ç¼Æ
     '³o¸Ìªº "/2" ¬O¬°¤F«ü¦Vµ²ªG¸ê®ÆªºÄæ¦ì¼Æ2
   '@@
Next
Arr = Range([Analysis!b1], [Analysis!a65536].End(3))
'¡ô­«·s¥OArr¬O¦r¨å!­Ë¤J Analysisªí[B1] »PAÄæ³Ì«á-¦³¤º®eÀx¦s®æ¤§¶¡
',³Ì¤p¤è¥¿°Ï°ìÀx¦s®æªº­È

For i = 2 To UBound(Arr)
'¡ô³]¥~¶¶°j°é!,i±q2 ¨ìArr°}¦C³Ì«á¤@Áa¦V¦C¼Æ
    For j = 1 To 2
    '¡ô³]¤º¶¶°j°é!,j±q1 ¨ì2
       Arr(i - 1, j) = xD(Arr(i, 1) & "/" & j)
      '¡ô¥ÎAnalysisªíªºÃöÁä¦r·f°t"/"²Å¸¹»P¤º¶¶°j°é¼Æ·íKey!
       '¬d¦r¨å¸Ìªºitem­È ¦p¤W¼Ðµù
@@³B
       '±Nµ²ªGitem­È©ñ¦bÃöÁä¦r¦C«e¤@¦C°j°é¹ïÀ³Äæ¸Ì!
       '¦]¬°«á¤è
##¼Ð¥Ü³B¦³ -1,©Ò¥H¤£·|¾É­PArr³Ì«á¦C¶K¤Jµ²ªG!
    Next
Next
[Analysis!b2].Resize(UBound(Arr) - 1, 2) = Arr    '##
'±NArr°}¦C±qAnalysisªí[B2]¶}©l¶K¤J!
'¦]¬°Resize()= Arrªº¤è¦¡¬Oª½±µ³Ì¥ª¤W¨¤½d³òªºªº¤¸¯À!
'©Ò¥H«e½ú¤~±qArr²Ä¤@¦C¶}©l±a¤Jµ²ªG
'¦pªG¬O«á¾Ç¬°¤F«OÀI°_¨£!·|¥[­ÓBrr¨Ó¸Ëµ²ªG!
'ÁÂÁ«ü¾É!

End Sub

TOP

        ÀR«ä¦Û¦b : ­×¦æ­nô½t­×¤ß¡AÂǨƽm¤ß¡AÀH³B¾i¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD