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

[µo°Ý]½Ð±Ð¦³ÃöVBA¸ê®Æ¤ñ¹ï

[µo°Ý]½Ð±Ð¦³ÃöVBA¸ê®Æ¤ñ¹ï

¦U¦ì«e½ú±z¦n¡A
·Q½Ð±Ð¦p¦ó¨Ï¥ÎVBA»s§@±N¥ªÃ䪺Á`ªí¹ïÀ³Äæ¦ìªº¸ê®Æ¡A§Ö³t¶ñ¤J¥kºÝªºÁ`ªí¤§¤¤¡A
¸ê®Æ¹ïÀ³ªº³¡¤Àexcel¤¤¥i¥H¥Îsumifªº¨ç¼Æ¨Ó³B²z¡A¤p§Ì·Q¸ß°Ý¬O§_ÁÙ¦³§ó§Öªº¤èªk¡A
ÁٽЫe½ú©Î°ª¤â«ü¾É¡A«D±`·PÁÂ~~

­ì©l¡G

³Ì«á§e²{ªºµ²ªG¡G


P.S.
1. AÄæ»PLÄæ¬O¹ïÀ³ªºKey
2. ¥ªºÝÁ`ªí¥X²{ªº¦C¼Æ¤£©w¡A¦ý¬O¥i¯à·|¥X²{­«½Æªº(¦p¥ªÃäÁ`ªí²Ä5¦C»P²Ä9¦C¡A²Ä6¦C»P²Ä8¦C)
3. ­Y¥X²{­«½Æªº³¡¤À¡Aª÷ÃB­n¥[Á`
4. ­Y¹ïÀ³¤£¨ì¡A¥kºÝÁ`ªí­n¸É¤W0

¸ê®Æ¤ñ¹ï.zip (7.7 KB)

¦^´_ 1# david1216jp
½Ð°Ñ¦Ò¡C
  1. Sub test()
  2.     Dim arr
  3.     Dim d As Object
  4.     Set d = CreateObject("Scripting.Dictionary")
  5.     [A1,L1].Value = ""
  6.     arr = [B2].CurrentRegion
  7.     For i = 2 To UBound(arr, 2)
  8.         For j = 2 To UBound(arr)
  9.             d(arr(1, i) & arr(j, 1)) = d(arr(1, i) & arr(j, 1)) + arr(j, i)
  10.         Next j
  11.     Next i
  12.     arr = [M2].CurrentRegion
  13.     For i = 2 To UBound(arr, 2)
  14.         For j = 2 To UBound(arr)
  15.             If d(arr(1, i) & arr(j, 1)) = "" Then
  16.                 arr(j, i) = 0
  17.             Else
  18.                 arr(j, i) = d(arr(1, i) & arr(j, 1))
  19.             End If
  20.         Next j
  21.     Next i
  22.     [M2].CurrentRegion = arr
  23.     [A1,L1].Value = "Á`ªí"
  24.     Set d = Nothing
  25.     arr = ""
  26. End Sub
½Æ»s¥N½X

TOP

·PÁÂKubi¤j¤j´£¨Ñ¤èªk¡A§Ú³oºÝ¦A¸Õ¸Õ¬Ý¡A«D±`·PÁ±zªºÀ°¦£~~
§Ú³oºÝ¦b½×¾Â¡Bºô¸ôª¦¤å¥u¦³¸Ñ¨M¶Ç°e­Èªº¤èªk(¥ªÃä¡÷¥kÃä)¡A¦ý¬O¦X¨Ö¦P¦Cª÷ÃBªº³¡¤À´N¥d¦í¤F¡A³o¸Ì¯uªºª×ªêÂÃÀs¡A¦A¦¸·PÁÂKubi«e½ú!!

TOP

Sub test()

i = 3
Do Until Cells(i, 1) = ""

    Index_a = Cells(i, 1)


    k = 3
    Do Until Cells(k, 12) = ""
        Index_b = Cells(k, 12)

   
        If Index_a = Index_b Then
            For t = 1 To 9
                Cells(k, 12 + t) = Cells(k, 12 + t) + Cells(i, 1 + t)
            Next t
        End If
        
    k = k + 1
    Loop




i = i + 1
Loop







End Sub


ÁÙ¨S°µ 4. ­Y¹ïÀ³¤£¨ì¡A¥kºÝÁ`ªí­n¸É¤W0
¤¬¬Û¥æ¬y

TOP

©êºp¡A¤@¦£§Ñ¤F¦^ÂФj®a~~
·PÁÂKubi¤j¤j»Padrian_9832¤j¤j¡A§Ú¦Û¤v¤]ÁÙ¦b¾Ç²ß¤¤¡A¦A¦¸·PÁÂ!!

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD