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

¦~«×¥u¥[¤j©ó¹s

¦~«×¥u¥[¤j©ó¹s

¦U¦ì«e½ú§A­Ì¦n!!   
    ¦~«×¥u¥[¤j©ó¹s¤£ª¾¤½¦¡¬°¦ó?
    ½Ðª¾¹Dªº«e½ú,¤£§[½ç±ÐÁÂÁ¦A¤T!!

Leo40.rar (4.13 KB)

¤£¦n·N«ä, µLªk¤U¸üªþ¥ó
¬O§_¥i¥H ±N½d¨Ò©ç·Ópo¥X or ²M·¡´y­z°ÝÃD

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2015-3-26 11:08 ½s¿è

¦^´_ 1# myleoyes
°²³]Àx¦s®æR1¬°«ü©w¡u¦~¤À¡v¤§¿é¤JÄæ¦ì¡A¥B¥u¥[¤j©ó¹sªº¡uª÷ÃB¡v
1.­Y¿é¤J¦~¤À¬°¡u¥Á°ê¡v¦~¡A«hÀx¦s®æQ1¤½¦¡
=SUMPRODUCT((YEAR($K$4:$K$12)=$R$1+1911)*($Q$4:$Q$12>0)*($Q$4:$Q$12))
2.­Y¿é¤J¦~¤À¬°¡u¦è¤¸¡v¦~¡A«hÀx¦s®æQ1¤½¦¡
=SUMPRODUCT((YEAR($K$4:$K$12)=$R$1)*($Q$4:$Q$12>0)*($Q$4:$Q$12))
½Ð°Ñ¦Ò¡I

Leo40_2.zip (4.56 KB)

TOP

¦^´_ 3# p212
k123456770«e½ú!½Ð¬Ý°ÊµeÀÉ...
«e½ú!§A¥i¯à»~¸Ñ¤p§Ìªº°ÝÃD
         ³o¼Ë¤½¦¡µLªk±o¨ìµª®×
           =SUMPRODUCT((TEXT(K$4:K$12,"YYY")=TEXT(AY2,"YYY"))*SUMIF(Q4:Q12,">0"))
          =SUMPRODUCT((TEXT(K$4:K$12,"YYY")=TEXT(AY2,"YYY"))*SUMIF(Q4:Q12,">0"))
        ©Ò¥H¥Î¸É§UÄæ¬O¥i¥H¹F¦¨»Ý¨D
       AY2=DATE(YEAR(TODAY()),1,1)
      AY3=DATE(YEAR(TODAY()),12,31)
      AY4=IF(AND(K4>=AY$2,K4<=AY$3,Q4>0),Q4,"")¤U©Ô
     ¦ý¤ß·QÀ³¸Ó¤£»Ý­n¾a¸É§UÄæ´N¥i¥H¹F¨ì¥Øªº¤£¬O¶Ü?
      ½Ð«e½ú¤£§[¦A½ç±ÐÁÂÁ¦A¤T!!

Le40o.gif (776.68 KB)

Le40o.gif

Leo40.gif (776.68 KB)

Leo40.gif

TOP

......³o¼Ë¤½¦¡µLªk±o¨ìµª®×
=SUMPRODUCT((TEXT(K$4:K$12,"YYY")=TEXT(AY2,"YYY"))*SUMIF(Q4:Q12,">0"))


=SUMPRODUCT((YEAR(K$4:K$12)=YEAR(TODAY()))*(Q4:Q12>0),Q4:Q12)

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2015-3-27 10:10 ½s¿è

¦^´_ 4# myleoyes
«Ü©êºp»~¸Ñ¤F±zªº·N«ä¡A¥H¼W¥[»²§UÀx¦s®æ¤è¦¡³B²z¡C
±N3#ªº¤½¦¡¤º®e§ï¬°5# JBY¤j´£¨Ñªº­×§ï¤½¦¡§Y¥i¡A©Ò¥H¤£»Ý¥t¼W»²§UÀx¦s®æ (3#¤¤Àx¦s®æR1«ü©w¡u¦~¤À¡v)¡C
=SUMPRODUCT((YEAR($K$4:$K$12)=$R$1)*($Q$4:$Q$12>0)*($Q$4:$Q$12))
¡õ
=SUMPRODUCT((YEAR(K$4:K$12)=YEAR(TODAY()))*(Q4:Q12>0),Q4:Q12)
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 6# p212
¨â¦ì«e½ú!¯u©êºp¤p§Ì¤]§Ë¿ù­ì©lÀɪº¥Î·N
      À³¸Ó AY4=IF(AND(K4>=AY$2,K4<=AY$3,M4>0,Q4>0),Q4,"")
      ¤]´N¬O»¡MÄæ»PQÄæ­n¦P®É¤j©ó¹s¤~¥[Á`
      ¦AªþÀɽФ£§[¦A½ç±ÐÁÂÁ¦A¤T!! ¨¯­WÅo!

Leo40-1.rar (4.17 KB)

TOP

¦^´_ 7# myleoyes

=SUMPRODUCT((YEAR(K$4:K$12)=YEAR(TODAY()))*(M4:M12>0)*(Q4:Q12>0),Q4:Q12)

©ÎªÌ

=SUMPRODUCT((TEXT(K$4:K$12,"yyy")=TEXT(TODAY(),"yyy"))*(M4:M12>0)*(Q4:Q12>0),Q4:Q12)

TOP

¦^´_ 8# JBY
    «e½ú·Q¨ìÅoÁÂÁÂ!!¥t¤@°ÝÃD½Ð±Ð
    L1¬° 67 Åã¥Ü¬° 67¦~¦¸ «¬ºA
    K1¿é¤J1978/2/16 Åã¥Ü¬° 67/2/16 «¬ºA
    K2=CONCATENATE(MID(YEAR(K1),1,4)-1911)
    ·Ó²z»¡ K2=L1¨S¿ù
    «ö¤ÀªR¶s«oµLªk°õ¦æµ{¦¡   
    Sub ¤ÀªR()
        If [K2] <> [L1] Then MsgBox ("«Ü©êºp¨t²ÎµLªk¤ÀªR¡I¿é¤J¸ê®Æ¬° " & [K2] & " ¦~¦¸¤£µ¥©ó¨Ó·½ " & [L1] & " ¦~¦¸ªº¸ê®Æ,½Ð­×§ï¿é¤J¸ê®Æ¢Ý¢Ù¡I¡I"): End
           °õ¦æµ{¦¡
    End Sub
    ¬°¦óK2ªº 67«DL1ªº 67
    ¤£ª¾¦³¦ó¤½¦¡¥i¥H¨ú¥N©Î¨ä¥L¤èªk
    ½Ð¤£§[½ç±ÐÁÂÁ¦A¤T!!¨¯­WÅo!  
    K2¤£¯à¥Îµ{¦¡Âର­È¦p¥k [K2]=Evaluate("=CONCATENATE(MID(YEAR(K1),1,4)-1911)")

TOP

¦^´_ 9# myleoyes
L1: 67

K1: 1978/2/16

K2 =CONCATENATE(MID(YEAR(K1),1,4)-1911)

¬°¦ó K2 ªº 67 «D L1 ªº 67 ?

¦]¬° K2 ¤½¦¡ªð¦^ªº 67 ¬°¤å¥», ¦Ó, L1 ªº 67 ¬°¼Æ­È

....................................................................................................................

K2, §ï¬° =--CONCATENATE(MID(YEAR(K1),1,4)-1911)

¦ý¬O,

¬°¤°»ò¤½¦¡¨Ï¥Î CONCATENATE ©M MID ¨ç¼Æ ??

K2 ¤½¦¡, ¥i¥Hª½±µ¨Ï¥Î :

=YEAR(K1)-1911

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD