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

¬O§_¥i¨Ï¥Î¤½¦¡¦Û°Ê°µ¤@²³æ®Ö®ø©ú²Óªí???

¬O§_¥i¨Ï¥Î¤½¦¡¦Û°Ê°µ¤@²³æ®Ö®ø©ú²Óªí???

book3.rar (2.02 KB) ¸ê®Æ·|¦³­q³æ,¥X³f©ú²Ó¡A¬O§_¥i¨Ï¥Î¤½¦¡¦Û°Ê®M¨ú¨ä¥¼µ²¼Æ¾Ú©ódÄæ¦ì¡]ªþ¥ó¤¤¡^¡AÁÂÁÂ...!!!

d2=C2-SUMPRODUCT((¥X³f©ú²Ó!$A$2:$A$10=A2)*(¥X³f©ú²Ó!$B$2:$B$10=B2)*(¥X³f©ú²Ó!$C$2:$C$10))

TOP

·PÁÂANGELAªº¦^ÂСA¦ý¤p§Ì©¿²¤´£¨ì¤@ÂI¡A´N¬O¦P­q³æ¸¹¤Î®Æ¸¹¦³¥i¯à·|¦³­«ÂЪº¡]´N¬O¦P¤@­q³æ¦³¦P®É¸Ó®Æ¸¹¤U¤Gµ§¥H¤W==>¦]À³¤G­q³æªº»Ý¨D¤é¤£¦P)¡A¥H¤W¤½¦¡§YµLªk®M¨ú¥X¥¿½T¼Æ­È

TOP

¥»©«³Ì«á¥Ñ ANGELA ©ó 2011-3-9 15:23 ½s¿è

¦^´_ 3# p6703


    ½Ð¨Ì¼Ë¹º¸¬Äª.ÁÙ¬O¥ÎSUMPRODUCT¨ú¥Nc2

TOP

¤p§Ì¤£§÷¡A¥¼¯à©ú¥ÕANGELAªº·N«ä¡A¬O§_¥i©ú½T§iª¾¡AÁÂÁÂ...!!!

TOP

¥»©«³Ì«á¥Ñ ANGELA ©ó 2011-3-14 19:01 ½s¿è

=SUMPRODUCT((A2:A12=A2)*(B2:B12=B2)*C2:C12)-SUMPRODUCT((¥X³f©ú²Ó!$A$2:$A$10=A2)*(¥X³f©ú²Ó!$B$2:$B$10=B2)*(¥X³f©ú²Ó!$C$2:$C$10))
¤£¬O¤£ª½±µ§i³`§Aµª®×,¬O­n§A¸ÕµÛ¥h¤F¸Ñ¤½¦¡ªº§@¥Î­ì²z,§_«h¤U¦¸¹J¨ìÃþ¦ü°ÝÃD,§AÁÙ±o´£°Ý.
=SUMPRODUCT(($A$2:$A$12=A2)*($B$2:$B$12=B2)*$C$2:$C$12)-SUMPRODUCT((¥X³f©ú²Ó!A$2:A$10=A2)*(¥X³f©ú²Ó!B$2:B$10=B2)*(¥X³f©ú²Ó!C$2:C$10))
§â簵³ò§ï¦¨µ´¹ï°Ñ·Ó,´N¥i©¹¤Ucopy

TOP

·PÁÂANGELA¸Ñ»¡¡A©Ò¥H´N¬O®»¨ú¤G¦¸ªº¼Æ¶q¬Û´î¡A§Y¥i±o¥X»Ý­n¼Æ¾Ú

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

¸ê®Æªí:(¥X³f©ú²Ó)


µ²ªGªí°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, Crr, Y, i&, T$, Sh(2)
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Set Sh(1) = Sheets("¥¼µ²­q³æ"): Set Sh(2) = Sheets("¥X³f©ú²Ó")
'¡ô¥OSh³o¤@ºû°}¦C1~2¯Á¤Þ¸¹­È¦U¥H¤u§@ªí±a¤J
Brr = Sh(1).[A1].CurrentRegion: Crr = Sh(2).[A1].CurrentRegion
'¡ô¥OBrr,CrrÅܼƦU¬O¤Gºû°}¦C,¦U¥HÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 2 To UBound(Crr)
   T = Crr(i, 1) & "|" & Crr(i, 2): Y(T) = Y(T) + Crr(i, 3)
Next
'¡ô³]°j°é²Õ¦X(­q³æ³æ¸¹|®Æ¸¹)·s¦r¦ê·íkey,item¬O¼Æ¶q²Ö¥[,¯Ç¤JY¦r¨å¸Ì
For i = 2 To UBound(Brr)
   T = Brr(i, 1) & "|" & Brr(i, 2): Brr(i - 1, 1) = Brr(i, 3) - Y(T)
Next
'¡ô³]°j°é±N(­q³æ³æ¸¹|®Æ¸¹)²Õ¦X¦¨ªº·s¦r¦ê¬dY¦r¨å±o¨ìitem­È,
'·í³Q´î¼Æ°µ¦©°£,¼g¤JBrr°}¦Cªº²Ä1Äæ

Sh(1).[D2].Resize(UBound(Brr) - 1, 1) = Brr
'¡ô±NBrr°}¦C­È¼g¤J"¥¼µ²­q³æ"ªíµ²ªGÄ椤
Application.Goto Sh(1).[D2].Resize(UBound(Brr) - 1, 1)
'¡ô¥OÀx¦s®æ´å¼Ð¸õ¨ì "¥¼µ²­q³æ"ªíµ²ªGÄ椤
Set Y = Nothing: Erase Brr, Crr, Sh
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD