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

½Ð±Ð«e½ú!¹ïÀ³½d³ò²Î­p

½Ð±Ð«e½ú!¹ïÀ³½d³ò²Î­p

½Ð±Ð«e½ú¤j¤j!¹ïÀ³½d³ò²Î­p?¦pªþ¥ó  ·PÁÂ!

Book1.rar (2.42 KB)

·PÁ ¡I·PÁ ¡I gong ¤jªº¼ö¤ß ¡I

TOP

MATCH(1,no!$F:$F,0)-1,¦bOFFSET()¤ºªº·N¸q¬O§ä¨ì1ªº©Ò¦b¦C¼Æ
OFFSET(X,X,X,-30,X),-30ªº·N¸q¬O¦^±À30¦C½d³ò

MATCH(1,no!$F:$F,0)-30,¦bOFFSET()¤ºªº·N¸q¬O§ä¨ì1ªº©Ò¦b¦C«á
¦A­Ëºâ30¦C
OFFSET(X,X,X,30,X),±q­Ëºâ«á¤§¦C¦A©¹¤U¿ï30µ§ªº½d³ò
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¦^´_ 7# gong


    gong¤j¤jªº¤]¥¿½T,ÁÂÁ±z!  §Ú¨Ó¾Ç²ß¬°¦óMATCH(1,no!$F:$F,0)-1   »PMATCH(1,no!$F:$F,0)-30  
·|¬O¤@¼Ëªºµ²ªG!

TOP

§ó¥¿
=COUNTIF(OFFSET(no!$F$1,MATCH(1,no!$F:$F,0)-1,2,-30,5),B$2)
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¥»©«³Ì«á¥Ñ duck_simon ©ó 2010-6-22 14:38 ½s¿è

·PÁ½Ѧì¤j¤j¦p¦¹§Ö³t¸Ñµª,«e2¦ì¤j¤j¥i¯à©¿²¤§Ú·N«ä,FÄæ¬O"ÅܼÆ"
gong ¤j¤j»~·|¤pªº·N«ä,sorry:¤W±À¥]§tFÄ檺1,©Ò¥H¤½¦¡·|"¤Ö²Î­p¤@´Á"..
Hsieh¤j¤jªº¤½¦¡¥¿½T¥i¥Î,·PÁ½Ѧì¤j¤j«e½ú,ÁÂÁÂ!

TOP

=COUNTIF(OFFSET(no!$F$1,MATCH(1,no!$F:$F,0),2,-30,5),B$2)
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

=COUNTIF(OFFSET(no!$F$1,MATCH(1,no!$F:$F,0)-30,2,30,5),B$2)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# duck_simon

²q¤@²q¬O³o·N«ä¶Ü ?

B3

=SUMPRODUCT((no!$F$5:$F$40<=30)*(no!$H$5:$L$40=B$2))
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¬O³o¼Ë¶Ü~~~
§Ú¬O¥Î¨ç¼Æ¥h°µ²Î­pªº

Book1.zip (2.96 KB)

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD