jsc0518

¦^´_ 23# samwang
Dear samwang,
OK¡A¥i¥H¥Î¡C¦b»P±z½Ð±Ð¤@¤U¡A°õ¦æ«áH1Äæ¦ìªº"VBA"³£·|Åܦ¨ªÅ¥Õ¡C
¬O§_¥i¥H­×­q¡H

¤U¸ü (49.66 KB)

2021-12-10 15:04

Sumif-VBA-V2-ok-¥i¥Î.rar (21.67 KB)

¤U¸ü¦¸¼Æ: 2

2021-12-10 15:04

samwang

¦^´_ 22# jsc0518

Arr-->Brr
Brr(i, 1) = xD(Brr(i, 1) & "")

jsc0518

¦^´_ 20# samwang
Dear samwang,
¦­¤W¦n¡A¥i¥HÀ°§Ú¬Ý¤@¤Uªþ¥[Àɮ׶ܡH
§Ú¦³¹ê»Ú¨Ì¾Ú»Ý¨D¡A½Õ¾ã¤FexcelÄæ¦ì¦ì¸m¡A¥H¤Î­×§ï¹L±zªºVBA
¥i¬O¦b¤u§@ªí"Analysis"¤ºªºHÄæ¦ì«o¬O±a¥XªÅ¥Õ
¥i¥HÀ°§Ú¬Ý¤º®e­þ¸Ì¦³»~¶Ü¡H
·P®¦¡I


    1111.rar (21.77 KB)

¤U¸ü¦¸¼Æ: 3

2021-12-10 11:18

jsc0518

¦^´_ 20# samwang
Dear samwang,
±z¦n¡I±z©ÒªþªºÀÉ®×´ú¸Õ¥¿±`¡C§Ú§ï¤F±zªºVBA¦Ü§ÚªºEXCEL¤W¡A¦³°ÝÃD¡Aµe­±¦p¤U

¬õ¦r¬O§Ú§ï¹Lªº
----------------------------------------------------------------------
Sub test22()
Dim Arr, Brr, xD, i&, T$
Set xD = CreateObject("Scripting.Dictionary")
Tm = Timer
Arr = [ú®w¶q!g1].CurrentRegion
For i = 2 To UBound(Arr)
    T = Arr(i, 1)
    xD(T) = Val(xD(T)) + Val(Arr(i, 13))
Next
With Sheets("Analysis")
    Brr = .Range(.[a1], .[a65536].End(3))
    For i = 1 To UBound(Brr)
        Brr(i, 1) = xD(Arr(i, 1) & "")
    Next
    .[g1].Resize(UBound(Brr)) = Brr
End With
MsgBox Timer - Tm
End Sub
----------------------------------------------------------------------

¤U¸ü (33.76 KB)

2021-12-8 22:03

¤U¸ü (43.98 KB)

2021-12-8 22:03

samwang

¦^´_  samwang
Dear samwang,
±ß¤W¦n¡I·PÁ±zªº¼ö¤ß¦^´_¡C¦]¥D­nÀɮ׸ê®Æ¤º®e¬°¤½¥qªº¸ê®Æ¡A©Ò¥H¶È¯à¥Î¥Ü ...
jsc0518 µoªí©ó 2021-12-8 20:56

§Ú´ú¸Õ¨S°ÝÃD¦pªþ¥ó¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

jsc0518

¦^´_ 18# samwang
Dear samwang,
±ß¤W¦n¡I·PÁ±zªº¼ö¤ß¦^´_¡C¦]¥D­nÀɮ׸ê®Æ¤º®e¬°¤½¥qªº¸ê®Æ¡A©Ò¥H¶È¯à¥Î¥Ü½dÀÉ®×µ¹±z¡A©êºp ><"
­è­è¬ORUN¤F¤@¤U¡A¦³¿ù»~°T®§¡I

¤U¸ü (49.96 KB)

2021-12-8 20:56

samwang

¦^´_  samwang
Dear samwang,
±z¦n¡I¸ê®Æ¤@¦h¯uªº·|¶]«Ü¤[¡A¥t¥~§Ú®M¥ÎªºEXCELÀɮפº¦³§Ú¦³¼g«Ü¦hªº¨ç¼Æ ...
jsc0518 µoªí©ó 2021-12-8 15:24

¦]¬°¨S¦³ªþÀÉ¡A¥u¯à¥Î12#ªºÀÉ®×¥h¼g¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, Brr, xD, i&, T$
Set xD = CreateObject("Scripting.Dictionary")
Tm = Timer
Arr = [ROUND!a1].CurrentRegion
For i = 2 To UBound(Arr)
    T = Arr(i, 1)
    xD(T) = Val(xD(T)) + Val(Arr(i, 13))
Next
With Sheets("Sumifs")
    Brr = .Range(.[a1], .[a65536].End(3))
    For i = 1 To UBound(Brr)
        Brr(i, 1) = xD(Arr(i, 1) & "")
    Next
    .[g1].Resize(UBound(Brr)) = Brr
End With
MsgBox Timer - Tm
End Sub

samwang

¦^´_  samwang
Dear samwang,
±z¦n¡I¸ê®Æ¤@¦h¯uªº·|¶]«Ü¤[¡A¥t¥~§Ú®M¥ÎªºEXCELÀɮפº¦³§Ú¦³¼g«Ü¦hªº¨ç¼Æ ...
jsc0518 µoªí©ó 2021-12-8 15:24

½Ð°Ý¤è«K¥i¥Hªþ¤WÀɮ׶Ü?
¥H§Q´ú¸Õ¥Î
ÁÂÁÂ

jsc0518

¦^´_ 15# samwang
Dear samwang,
±z¦n¡I¸ê®Æ¤@¦h¯uªº·|¶]«Ü¤[¡A¥t¥~§Ú®M¥ÎªºEXCELÀɮפº¦³§Ú¦³¼g«Ü¦hªº¨ç¼Æ¡A¦b°õ¦æsumif¡Bsumifs³o2­ÓVBA»yªk³£¶]«ÜºC¡A¬O§_¥i¥H¦A²¤Æ»yªk©Î¬O¹Bºâ®É¶¡ÁYµu©O¡H
Thank you.

Excel °õ¦æVBA ®Éªºµe­±

¤U¸ü (5.84 KB)

2021-12-8 15:23

Sub Sumif()

For I = 2 To [a65536].End(3).Row
Cells(I, "H") = Application.Sumif(Sheets("ROUND").Range("A2:A65536"), Cells(I, "A"), Sheets("ROUND").Range("M2:M65536"))
Next

End Sub







Sub Sumifs()

For I = 2 To [a65536].End(3).Row
Cells(I, "G") = Application.Sumifs((Sheets("ROUND").[M2:M10000]), (Sheets("ROUND").[A2:A10000]), Cells(I, "A"))
Next

End Sub

samwang

¦^´_ 14# jsc0518

¦pªG¸ê®Æ¦hªº¸Ü¡A13#·|¤ñ¸ûºC¤@ÂI¡AÁÂÁÂ