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

[µo°Ý] ½Ð°Ý¦p¦ó¥[Á`ªí¤¤«ü©w¤é´Á(©Î´Á¶¡)

[µo°Ý] ½Ð°Ý¦p¦ó¥[Á`ªí¤¤«ü©w¤é´Á(©Î´Á¶¡)

¦pªþªí¤¤¡A·Q­n¦bH23®Ú¾ÚF23©Òµ¹¤©¤§¦~«×¼Æ­È¥[Á`¨äHÄ椧·l¯q¤p­p¡C
§Ú±Ä¥Î¤U¦C¨ç¼Æ¤è¦¡­pºâ¤£¥X¡A¤£ª¾­þ¦ì¥ý¶i¥i¥H¤©¥H±Ð¾É«ü¥¿¤@¤U¡C
=sumif(A3:H23,">=(F24)",H3:H23)

´ú¸ÕÃD.rar (7.68 KB)

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


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, Crr, Z, i&, R&, Y$, N&, J&, V&, T$, xA As Range
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Set xA = Range([H1], [A65536].End(3)): Brr = xA
'¡ô¥OxAÅܼƬO «ü©w½d³òÀx¦s®æ(ª«¥ó),¥OBrrÅܼƬO¼g¤JxAÅܼÆ(Àx¦s®æ)­Èªº¤Gºû°}¦C
ReDim Crr(1 To 1000, 1 To 3)
'¡ô«Å§iCrrÅܼƬO ¤GºûªÅ°}¦C,¯Á¤Þ¸¹~1000¦C,1~3Äæ
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q2 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   Y = Format(Brr(i, 1), "YYYY"): If Y = "" Then GoTo i01 Else R = Z(Y)
   '¡ô¥OYÅܼƬO¤é´Áªº4½X¦~¥÷¦r¦ê,¦pªGYÅܼƬO ªÅ¦r¤¸,´N¸õ¨ì¼Ð¥Ü i01¦ì¸mÄ~Äò°õ¦æ,
   '§_«h´N¥ORÅܼƬO¥HYÅܼƬdZ¦r¨å¦^¶Çitem­È

   If R = 0 Then N = N + 1: R = N: Z(Y) = R: Brr(R, 1) = Y: Brr(R, 2) = "¦~«×¤p­p": Brr(R, 3) = 0
   '¡ô¦pªG¦¹¦~¤À¬O­º¦¸¯Ç¤JZ¦r¨å!´N¥ON²Ö¿nBrr°}¦C©ñµ²ªG¦C¼Æ,
   '¥H¦~¤À¬°key,item¬O¦C¸¹,¯Ç¤JZ¦r¨å,
   '¥OBrr°}¦Cµ²ªG¦C²Ä2Äæ°}¦C­È¬O "¦~«×¤p­p"¦r¦ê,¥O­ì¨ÓBrr°}¦C¸ê®Æ­È³]¬°0

   Brr(R, 3) = Brr(R, 3) + Val(Brr(i, 8))
   '¡ô¥OBrr°}¦Cµ²ªG¦C²Ä3Äæ²Ö¥[ ª÷ÃB
   T = Trim(Brr(i, 3)): If T = "" Then GoTo i01 Else V = Z(T)
   '¡ô¥OTÅܼƬO ªÑ²¼¦WºÙ¦r¦ê,¦pªGTÅܼƬO ªÅ¦r¤¸,´N¸õ¨ì¼Ð¥Ü i01¦ì¸mÄ~Äò°õ¦æ,
   '§_«h´N¥OVÅܼƬO¥HTÅܼƬdZ¦r¨å¦^¶Çitem­È

   If V = 0 Then J = J + 1: V = J: Z(T) = V: Crr(V, 1) = T: Crr(V, 2) = "¾ú¥vÁ`­p"
   '¡ô¦pªGªÑ²¼¦WºÙ¬O­º¦¸¯Ç¤JZ¦r¨å!´N¥OJ²Ö¿nCrr°}¦C©ñµ²ªG¦C¼Æ,
   '¥HªÑ²¼¦WºÙ¬°key,item¬O¦C¸¹,¯Ç¤JZ¦r¨å,
   '¥OCrr°}¦Cµ²ªG¦C²Ä2Äæ°}¦C­È¬O "¾ú¥vÁ`­p"¦r¦ê

   Crr(V, 3) = Crr(V, 3) + Val(Brr(i, 8))
   '¡ô¥OCrr°}¦Cµ²ªG¦C²Ä3Äæ²Ö¥[ ª÷ÃB
i01: Next
ActiveSheet.UsedRange.Offset(xA.Rows.Count).ClearContents
'¡ô¥Oªºµ²ªG¦C²M°£¤º®e
If N = 0 Then Exit Sub
'¡ô¦pªG¦~¤À²Î­p¨S¦³¸ê®Æ!´Nµ²§ôµ{¦¡°õ¦æ
xA(xA.Count + 6).Resize(N, 3) = Brr
'¡ô¥O¦~¤À²Î­p¸ê®Æ¼g¤JÀx¦s®æ
If J = 0 Then Exit Sub
'¡ô¦pªGªÑ²¼¦WºÙ¾ú¥vÁ`­p¨S¦³¸ê®Æ!´Nµ²§ôµ{¦¡°õ¦æ
[A65536].End(3)(N + 3, 6).Resize(J, 3) = Crr
'¡ô¥OªÑ²¼¦WºÙ¾ú¥vÁ`­p²Î­p¸ê®Æ¼g¤JÀx¦s®æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

°}¦C¤½¦¡

{=SUM(IF(ISNUMBER($A$2:$A23),(--TEXT($A$2:$A23,"yyyy")=$F24)*($H$2:$H23)))}

TOP

¦^´_ 10# 97forum

´ú¸ÕÃD.rar (9.05 KB)

1. ±N24¦C³]¬°¬°ªÅ¥Õ¦C¡A¥H«á¿ï24¦C«ö¥kÁä - ´¡¤J ·s¼W¤@¦C
2. F25 = 2008¡AF26 = 2009¡AF27 = 2010
3. H25¤½¦¡¡A½Ð§ï¬°¤U¦¡¦A¸Õ¸Õ¬Ý
=SUMPRODUCT(--(TEXT(A$2:A$24,"yyyy")=F25&""),H$2:H$24)
¤½¦¡©¹¤U½Æ»s

¤½¦¡ÁקK¿ù»~«Øij
1. YEAR() §ï¬° TEXT() ¥iÁקK AÄæ ¦³¤å¦r®É¤§¿ù»~
2. SUMPRODUCT(§PÂ_*¼Æ­È) §ï¬° SUMPRODUCT(--§PÂ_,¼Æ­È)¡A¥iÁקK HÄæ ¦³¤å¦r®É¤§¿ù»~
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 10# 97forum
¦³¥i¯à§ASUMPRODUCT¨ç¼Æ¤ºªº°}¦C¤j¤p¤£¦P
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  ML089


    =SUMPRODUCT((TEXT($A$2A23,"yyyy")=$F24&"")*($H$2H23))
Hsieh µoªí©ó 2012-9-3 11:14



    ÁÂÁ±zªºÀ°¦£¡A¥i¬O¸g¹L´ú¸Õ¡A¦b¤é´ÁÄæ¦ì¤¤¦pªG¬OªÅ¥Õ(©ÎªÌ¬Onull­È)¨äµ²ªG¤´µM¬O¿ù»~ªº¡C§ÚÁÙ¦b¬ã¨s¤F¸Ñ±z©Ò´£¨Ñªº¨ç¼Æµ²ºc¡CµL½×¦p¦óÁÙ¬OÁÂÁÂ¥ý¶iªº±Ð¾É¡C

TOP

¦^´_ 8# Hsieh

ÁÙ¬Oª©¥D¼F®`¡A§ï¤F¦n´X¦¸³£§ï¿ù¡A§Æ±æ¨S»~¾É¥L¤H
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089


    =SUMPRODUCT((TEXT($A$2:$A23,"yyyy")=$F24&"")*($H$2:$H23))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

§ï¬°¤U¦¡¡A¹J¨ì«D¤é´Á´N¤£·|µo¥Í¿ù»~

=SUMPRODUCT(--(TEXT(A14:A23,"yyyy")=F24&""),H14:H23)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089

³o¼Ë¹J¨ì«D¤é´Á®É¤@¼Ë·|¥X¿ù
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD