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

[µo°Ý] vba¨Ï¥Î¦h±ø¥ó¥[Á`

[µo°Ý] vba¨Ï¥Î¦h±ø¥ó¥[Á`

¦]¬°¸ê®Æ¤Ó¦h¤F¡A¨Ï¥Îsumifs®M¤J¤½¦¡»Ý¶]5¤ÀÄÁ¥H¤W¡A¤£ª¾¹D¬O§_¦³¤ñ¸û§Ö³tªº¤è¦¡?
Å¥»¡°}¦C¦n¹³·|§Ö«Ü¦h~
°}¦C³¡¤À¦]¬°¤£¼ô±x¦p¦ó¨Ï¥Î¡A¤£ª¾¬O§_¥i¥HÀ°¦£¸Ñ´b... (±ø¥ó¦p¤ÏÂŦⳡ¤À)

Â^¨ú.JPG
2021-8-6 23:05


test.zip (265.2 KB)
¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-8-7 04:45 ½s¿è

test V1.zip (723.97 KB) ¦^´_ 1# yifan2599


¥D­n¥Î¦r¨å³Ðªº
§Úªº¹q¸£­n¶]18¬í¥ª¥k


¤]¥Î¦hºû°}¦C¥h¸Õ
µ²ªG°O¾ÐÅé®e¶q¬µ¤F ³Ð¤£¥X¨Ó 1»õ¦h®æ

¤£¹L¥ÎÂI©_²]§Þ¥©¤]¬O¥i¥H

TOP

¦^´_ 2# singo1232001


    °}¦Cª©°µ¦n¤F
¥Î¤F7ºû¥h¤Á
¤j·§5¬í¥i¥H§¹¦¨ ¤£¹L¤£±ÀÂ˳oºØ¤èªk «Ü®e©ö¿ù test v2.zip (511.09 KB)

TOP

test v3.zip (485.13 KB) ¦^´_ 1# yifan2599

¤]¸É¤W¤@¯ë°}¦Cªº¼gªk
§Úªº¹q¸£¤j·§40¬í

TOP

Sub TEST_A1()
Dim Arr, Brr, xD, R&, C%, i&, j%, k%, T$, TT$, TM
TM = Timer
R = [®t²§!a1].Cells(Rows.Count, 1).End(xlUp).Row - 3
C = [®t²§!a4].Cells(1, Columns.Count).End(xlToLeft).Column
If R < 2 Or C < 9 Then Exit Sub
'---------------------------------------
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([Data!h1], [Data!a1].Cells(Rows.Count, 1).End(xlUp))
For i = 2 To UBound(Arr)
    For j = 1 To 6
        T = T & "|" & Arr(i, Mid(234517, j, 1))
    Next j
    xD(T) = xD(T) + Val(Arr(i, 8)): T = ""
Next i
'-------------------------------------
Arr = [®t²§!a4].Resize(R, C)
ReDim Brr(1 To R - 1, 1 To C - 8)
For i = 2 To R
    T = ""
    For j = 1 To 5: T = T & "|" & Arr(i, j): Next j
    For k = 1 To UBound(Brr, 2)
        TT = T & "|" & Arr(1, k + 8)
        If xD.Exists(TT) Then Brr(i - 1, k) = xD(TT)
    Next k
Next i
'-------------------------------------
[®t²§!i5].Resize(R - 1, C - 8) = Brr
Arr = "": Brr = "": Set xD = Nothing
MsgBox Timer - TM
End Sub

''¤j¬ù1¬í

TOP

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


    ³o©Û¯uªº­»!
midªº¥Îªk§ó¬OµeÀsÂI·ú!

TOP

¦^´_ 6# singo1232001


    ¯uªºÆZ§®ªº.... ¬ã¨s¶W¤[..
    ÁöµMÁÙ¬O¤£¤ÓÀ´.. XD
¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

TOP

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


   ·PÁ¸Ѵb¡A³t«×Ãz§Ö~
   ¤£¹L[®t²§]Äæ¦ì³¡¤À¨S¦³­pºâ¥X¨Ó..
   ¤£ª¾¹D¸Ó¥[¤J­þ¬q»yªk©O?

Â^¨ú.JPG
2021-8-7 17:17
¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

TOP

Sub TEST_A1()
Dim Arr, Brr, xD, R&, C%, i&, j%, k%, T$, TT$, TS$(2), TC$, TM
TM = Timer
R = [®t²§!a1].Cells(Rows.Count, 1).End(xlUp).Row - 3
C = [®t²§!a4].Cells(1, Columns.Count).End(xlToLeft).Column
If R < 2 Or C < 9 Then Exit Sub
'---------------------------------------
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([Data!h1], [Data!a1].Cells(Rows.Count, 1).End(xlUp))
For i = 2 To UBound(Arr)
    For j = 1 To 6
        T = T & "|" & Arr(i, Mid(234517, j, 1))
    Next j
    xD(T) = xD(T) + Val(Arr(i, 8)): T = ""
Next i
'-------------------------------------
Arr = [®t²§!a4].Resize(R, C)
ReDim Brr(1 To R - 1, 1 To C - 8)
For i = 2 To R
    T = ""
    For j = 1 To 4: T = T & "|" & Arr(i, j): Next j
    For k = 1 To UBound(Brr, 2)
        TT = T & "|" & Arr(i, 5) & "|" & Arr(1, k + 8)
        TC = T & "|®t²§" & "|" & Arr(1, k + 8)
        If xD.Exists(TT) Then
           Brr(i - 1, k) = xD(TT):  xD(TC) = ""
           For j = 1 To 2: TS(j) = T & "|ª©¥»" & j & "|" & Arr(1, k + 8): Next j
        End If
        If Arr(i, 5) = "®t²§" Then
           If xD.Exists(TC) Then Brr(i - 1, k) = xD(TS(2)) - xD(TS(1))
        End If
    Next k
Next i
'-------------------------------------
[®t²§!i5].Resize(R - 1, C - 8) = Brr
Arr = "": Brr = "": Set xD = Nothing
MsgBox Timer - TM
End Sub


'®t²§ = ª©¥»2 - ª©¥»1 ???


===========================

TOP

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



    ¬Oªº¡Aª©¥»2-ª©¥»1¡C
    ¤£¹L¦WºÙ¥i¯à·|ÅÜ¡A¥i¯à·|¬Oª©¥»ªº¤é´Á
¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD