¬O§_¥i¨Ï¥Î¤½¦¡¦Û°Ê°µ¤@²³æ®Ö®ø©ú²Óªí???
- ©«¤l
- 134
- ¥DÃD
- 27
- ºëµØ
- 0
- ¿n¤À
- 168
- ÂI¦W
- 0
- §@·~¨t²Î
- WINXP
- ³nÅ骩¥»
- EXCEL 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2010-10-6
- ³Ì«áµn¿ý
- 2018-5-13
|
¬O§_¥i¨Ï¥Î¤½¦¡¦Û°Ê°µ¤@²³æ®Ö®ø©ú²Óªí???
book3.rar (2.02 KB)
¸ê®Æ·|¦³q³æ,¥X³f©ú²Ó¡A¬O§_¥i¨Ï¥Î¤½¦¡¦Û°Ê®M¨ú¨ä¥¼µ²¼Æ¾Ú©ódÄæ¦ì¡]ªþ¥ó¤¤¡^¡AÁÂÁÂ...!!! |
|
|
|
|
|
|
- ©«¤l
- 812
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 862
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¤¤
- µù¥U®É¶¡
- 2010-5-13
- ³Ì«áµn¿ý
- 2019-10-10
|
d2=C2-SUMPRODUCT((¥X³f©ú²Ó!$A$2:$A$10=A2)*(¥X³f©ú²Ó!$B$2:$B$10=B2)*(¥X³f©ú²Ó!$C$2:$C$10)) |
|
|
|
|
|
|
- ©«¤l
- 134
- ¥DÃD
- 27
- ºëµØ
- 0
- ¿n¤À
- 168
- ÂI¦W
- 0
- §@·~¨t²Î
- WINXP
- ³nÅ骩¥»
- EXCEL 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2010-10-6
- ³Ì«áµn¿ý
- 2018-5-13
|
·PÁÂANGELAªº¦^ÂСA¦ý¤p§Ì©¿²¤´£¨ì¤@ÂI¡A´N¬O¦Pq³æ¸¹¤Î®Æ¸¹¦³¥i¯à·|¦³«ÂЪº¡]´N¬O¦P¤@q³æ¦³¦P®É¸Ó®Æ¸¹¤U¤Gµ§¥H¤W==>¦]À³¤Gq³æªº»Ý¨D¤é¤£¦P)¡A¥H¤W¤½¦¡§YµLªk®M¨ú¥X¥¿½T¼ÆÈ |
|
|
|
|
|
|
- ©«¤l
- 812
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 862
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¤¤
- µù¥U®É¶¡
- 2010-5-13
- ³Ì«áµn¿ý
- 2019-10-10
|
¥»©«³Ì«á¥Ñ ANGELA ©ó 2011-3-9 15:23 ½s¿è
¦^´_ 3# p6703
½Ð¨Ì¼Ë¹º¸¬Äª.ÁÙ¬O¥ÎSUMPRODUCT¨ú¥Nc2 |
|
|
|
|
|
|
- ©«¤l
- 134
- ¥DÃD
- 27
- ºëµØ
- 0
- ¿n¤À
- 168
- ÂI¦W
- 0
- §@·~¨t²Î
- WINXP
- ³nÅ骩¥»
- EXCEL 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2010-10-6
- ³Ì«áµn¿ý
- 2018-5-13
|
¤p§Ì¤£§÷¡A¥¼¯à©ú¥ÕANGELAªº·N«ä¡A¬O§_¥i©ú½T§iª¾¡AÁÂÁÂ...!!! |
|
|
|
|
|
|
- ©«¤l
- 812
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 862
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¤¤
- µù¥U®É¶¡
- 2010-5-13
- ³Ì«áµn¿ý
- 2019-10-10
|
¥»©«³Ì«á¥Ñ 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µª®×,¬On§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 |
|
|
|
|
|
|
- ©«¤l
- 134
- ¥DÃD
- 27
- ºëµØ
- 0
- ¿n¤À
- 168
- ÂI¦W
- 0
- §@·~¨t²Î
- WINXP
- ³nÅ骩¥»
- EXCEL 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2010-10-6
- ³Ì«áµn¿ý
- 2018-5-13
|
·PÁÂANGELA¸Ñ»¡¡A©Ò¥H´N¬O®»¨ú¤G¦¸ªº¼Æ¶q¬Û´î¡A§Y¥i±o¥X»Ýn¼Æ¾Ú |
|
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
ÁÂÁ½׾Â,ÁÂÁ¦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
|
|
|
|
|