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

[µo°Ý] SUMIF °ÝÃD

[µo°Ý] SUMIF °ÝÃD

¦]¬°¸ê®Æ¶q«ÜÃe¤j¡A¤@¯ë SUMIF ¨ç¼Æ¥Îªk¡A«Ü®e©ö¬Ý±o²´ªá¼º¶Ã¡A¾É­P¿ù»~¡C
¦]¦¹¥u·Q¦b¦³­«½Æ«~¦Wªº²Ä¤@µ§Åã¥ÜÁ`¾P°âÃB¡A³o¼Ë¤l¥u­n¦b¿z¿ï®É­ç°£±¼ªÅ¥Õ¦C´N¤@¥Ø¤FµM¡A¯à§_¥ÎVBA¹ê²{³o¼Ëªº°µªk?
Â^¨ú.PNG
2018-11-23 19:29

test.rar (6.14 KB)
Jess

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-23 23:16 ½s¿è

¦^´_ 1# jesscc


VBAªÖ©w¥i¥H¡A¤£¹L¨ç¼Æ¤]¥i¥H¹F¨ì§r

G5:
=IF(COUNTIF($C$5:$C5,C5)=1,SUMIF($C$5:$C$13,C5,$F$5:$F$13),"")

¤U©Ô

¤£¹L¸ê®Æ¶q¤j¨ç¼Æ¦hªº¸Ü¡A¨C¦¸­«ºâ¥i¯à·|¥d¥dªº
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-23 23:44 ½s¿è

¦^´_ 2# n7822123


VBA¼gªk:

Sub test()
Dim Arr, d As Object
Arr = Range([¤u§@ªí1!O5], [¤u§@ªí1!K5].End(xlDown))
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(Arr): d(Arr(i, 1) & "") = d(Arr(i, 1) & "") + Arr(i, 4): Next
For i = 1 To UBound(Arr)
  Arr(i, 5) = d(Arr(i, 1) & "")
  d.Remove (Arr(i, 1))
Next i
Range([¤u§@ªí1!O5], [¤u§@ªí1!K5].End(xlDown)) = Arr
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-24 00:08 ½s¿è

¦^´_ 3# n7822123


VBA¥ÎFind¼gªk(§ó²µu)

Sub test2()
Dim Arr, i%, d As Object
Arr = Range([¤u§@ªí1!O5], [¤u§@ªí1!K5].End(xlDown))
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(Arr): d(Arr(i, 1) & "") = d(Arr(i, 1) & "") + Arr(i, 4): Next
For Each xx In d.keys
  Sheets("¤u§@ªí1").Columns("K").Find(xx, lookat:=xlWhole).Offset(, 4) = d(xx & "")
Next
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

Sub TEST()
Dim Arr, r&, i&, xD, T$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([F5], [C65536].End(xlUp))
For i = 1 To UBound(Arr)
    T = Arr(i, 1):  r = xD(T):  Arr(i, 1) = Empty
    If r = 0 Then r = i: xD(T) = i
    Arr(r, 1) = Arr(r, 1) + Arr(i, 4)
Next i
[G5].Resize(UBound(Arr)) = Arr
End Sub

TOP

ÁÂÁÂn¤j©M­ã¤j¡AÅý§Ú¤S¾Ç¨ì¤@¨Ç·sªº¼gªk©M«ä¸ô¡C
Jess

TOP

¦^´_ 6# jesscc


  ·Ç¤j¤@­Ó°j°éªº¼gªk¤ñ¸û¤£¦n²z¸Ñ¡A¤£¹L³t«×À³¸Ó¸û§Ö
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 7# n7822123


¥Î¦r¨åÀÉ¥h°O¿ý¡e­º¦¸¡f¥X²{ªº¡e¦C¸¹¡f¡A
§Q¥Î³o¦C¸¹¡A§Y¥i±N²Ö¥[­È©ñ¦b³o­Ó¦ì¸m¡A

¥t¡A§Q¥ÎArr°}¦Cªº¡e²Ä¤@Äæ¡f°µ¬°²Ö¥[­Èªº¦s©ñ°Ï¡A§Y¤£¥²¦A©w¸q¤@­ÓBrr°}¦C,
¦ý¦b°j°é®É,¥ÎÅܼƨú±o¥¦ªº­È¡@¡Ö¡Ö¡@T = Arr(i,1)
µM«á¦A±N¥¦²MªÅ¡@¡Ö¡Ö¡@Arr(i,1) = Empty

TOP

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


ÁÂÁ­ã¤j¸ÑÄÀ¡A¨ä¹ê·í¤U´N¬ÝÀ´°Õ:P

­ã¤j§â¥Î¹Lªº¸ê®ÆÂл\¡A·í°µ²Ö¥[Àx¦sªº¦ì¸m¡A

³oºØ¼gªk¦ü¥G«Ü¬Ù°O¾ÐÅéªÅ¶¡
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD