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

[µo°Ý] ¥ÎVBA °µSumif °ÝÃD

[µo°Ý] ¥ÎVBA °µSumif °ÝÃD

§Ú¦³¤@­Ó¤u§@ªí,»Ý­n±NÃþ«¬¥[Ãþ¡C»Ý­n¥ÎVBA ¨Ó¦Û°Ê¤ÀÃþ¤Î¥[Á`¡C

¨Ò¦pA¦ÜGÄæ, ¨C¤@­Ó²Õ§O, Job ¤£¦Pªº¸Ü,§Ú·|¦Û¦æ ¥Î 4¦C¤À¹j¶}, ¦P®É­n±NItem ³B ¤ÀÃþ¥[Á`°_¨Ó¡C

¦b DÄæ ¦C¥XItem ªºÃþ§O, ¨ä«á¦bLine ´N¥Îsumif ¥[Á`, qty ³B¤]¬O¡C


J¦ÜPÄæ¬Oµ²ªGÅã¥Ü,§Ú°µ¨Ò¤l»¡©ú ¦Ó¤w,¥­®É¥u·|¦³A¦ÜGÄæ¡C

·Q¥ÎVBA ¦Û°Ê¦b³Ì«á¦Û¤v¶ñ¤J¤ÀÃþ¤Î¶i¦æ¤ÀÃþ¥[Á`¡C


¨Ò¦pM6¦ÜO7 ¬O­n¥ÎVBA ¥þ¦Û°Ê±o¥Xµ²ªG¡C ¦]¬°¥­®É¥u¦³AÄæ¦ÜGÄæ,©Ò¥H§Ú·Q³]©w¤@­Ó«ö¶sÂIÀ»,«h¦bD6¦ÜF7 Äæ ±o¥X M6¦ÜO7 ªº¦P®Éµ²ªG¡C

¥H¤U´X­ÓJob ³£¬O¤@¼Ë¡C

Item , DÄæ³Ì¦h¦³¤T­ÓÃþ§O¡C¦pªGJob ¥u¦³¤@­ÓÃþ§O «h§¹¥þ¤£¥Î°Ê§@¡C (LV0998),

³Ì«á¬O LV0999, ¦pªGItem ¥u¦³¤@­Ó ´N¥u¬O¶i¦æ¥[Á`´N¥i¥H,¤£¥Î¥t¥~¥[¤@­ÓItem ¦WºÙ¶i¥h¡C

«D±`·PÁ¤j®a¡C


VBA sumif.zip (8.77 KB)

¦^´_ 1# stephenlee
½Ð°Ý stephenlee¤j¤j Job °£¤F¥H¤U³o¨ÇÁÙ¦³¨ä¥¦ªº¶Ü?
LV1024-1-4
LJG0430-1-6
LV0997-1-8
LV0998-1-1
LV0999-1-2

TOP

¥»©«³Ì«á¥Ñ samwang ©ó 2021-5-16 21:03 ½s¿è

¦^´_ 1# stephenlee


  ½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, xD, Ar(), T, UT, T2%, T3%, i&, M%, N%
Set xD = CreateObject("Scripting.Dictionary")
R = [a65536].End(3).Row + 1
Arr = Range("a1:g" & R)
For i = 2 To UBound(Arr)
    T = Arr(i, 4): T2 = Arr(i, 5): T3 = Arr(i, 6): UT = Arr(i - 1, 4)
    If T = "" And UT <> "" Then GoTo 98
    If T = "" Then GoTo 99
    If xD.Exists(T & "") Then
        M = xD(T & ""): Ar(2, M) = Ar(2, M) + T2: Ar(3, M) = Ar(3, M) + T3
    Else
        N = N + 1: xD(T & "") = N
        ReDim Preserve Ar(1 To 3, 1 To N)
        If Left(Arr(i, 1), 6) <> "LV0999" Then Ar(1, N) = T
        Ar(2, N) = T2: Ar(3, N) = T3
    End If
98:  If T = "" Then
        If M > 0 Then Cells(i, 4).Resize(N, 3) = Application.Transpose(Ar)
        N = 0: M = 0: Set xD = Nothing: Erase Ar
        Set xD = CreateObject("Scripting.Dictionary")
    End If
99: Next
End Sub

TOP

¦^´_  stephenlee
½Ð°Ý stephenlee¤j¤j Job °£¤F¥H¤U³o¨ÇÁÙ¦³¨ä¥¦ªº¶Ü?
LV1024-1-4
LJG0430-1-6
LV099 ...
°a¤ªºµ µoªí©ó 2021-5-15 21:43



¦³ªº,¦³¨ä¥L,¤£¬O¤W­±ªº¸ê®Æ¤F,¨C¦¸¤£¦P¡C ¦³¤j¤j¤w¸gÀ°§Ú³B²z¦n,ÁÂÁ§Aªá®É¶¡À°¦£¡C

TOP

¦^´_  stephenlee


  ½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, xD, Ar(), T, UT, T2%, T3%, i&, M%, N ...
samwang µoªí©ó 2021-5-16 21:00



§ÚÁÂÁ§A¤~¹ï,§¹¥þ²Å¦X­n¨D,ÁÂÁ§Aªá¤F³o»ò¦h®É¶¡À°§Ú¬ã¨s,·PÁ¡C

TOP

¦^´_ 4# stephenlee

§Ú¤]¬O¶¶«K½m²ß §Ú­Ì¤¬¬ÛÀ°§U ¦³¦h¾lªº®É¶¡ ¶¶«KÀ°§Ú´ú¸Õ¬Ý¬Ý¦³¨S¦³°ÝÃD ·PÁÂ
VBA sumif0520.rar (35.18 KB)

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD