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

[µo°Ý] SUMIF °ÝÃD

¥»©«³Ì«á¥Ñ 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

¦^´_ 6# jesscc


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

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 : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD