(¤w¸Ñ¨M)½Ð°Ý¦p¦ó²Î­p¤£¦P°Ï¶¡ªº³Ì¤j¼Æ¥Ø?

freeffly

§Ú·Q­nª¾¹DBÄæ¨C¤@­Ó³sÄò¼Æ¦rªº³Ì¤@«á¦U¬O¦h¤Ö ¥BÁ`¦@¥X²{´X¬q¼Æ¦r
°²³]¸ê®Æ¥u¨ì80¦C
«h¥X²{4¬q¼Æ¦r ³Ì«á¤@­Ó¼Æ¤À§O12 3 8 2 ³o¥|­Ó¼Æ¦r

¤£ª¾¹D³oºØ¸ê°T¦³¨S¦³¤ñ¸û¦nªº¤è¬O¨D±o
¥Ø«e§Ú¥u·Q¨ìCOUNTIFªº¤è¦¡¦Ó¥BÁÙ¨S±o¨ì³Ì«áµª®×
½Ð°ª¤â¨ó§U¤@¤U





Book1.rar (31.59 KB)

¤U¸ü¦¸¼Æ: 8

2012-1-20 22:58

freeffly

¦^´_ 5# Hsieh


    ÁÂÁª©¥D¦^À³
    ¸£µ¬ÁÙ¨S¿ìªk²z¸Ñ¦³ÂI¿ù¦ìªº°Ï¶¡µ²ªG
    §Ú¦A·Q·Q

Hsieh

¦^´_ 4# freeffly
SUMPRODUCT¨ç¼Æªº°}¦C¤j¤p¥²¶·¬Û¦P¨S¿ù
³o¸Ì¨â­Ó½d³ò¤£¦P¡A¦ý¨ä¤j¤p(¤¸¯À­Ó¼Æ)¬O¤@¼Ëªº
SMALL(IF(ISNUMBER($B$2:$B$80)*($B$3:$B$81=""),ROW($3:$81),""),ROW(A1))
³o¬O§ä¨ìB3:B81¬°ªÅ¥Õ­È¡A¥BB2:B80¬O¼Æ­Èªº¦ì¸m
SMALL(IF(ISNUMBER($B$3:$B$80)*($B$2:$B$79=""),ROW($3:$80),""),ROW(A1)))
³o¬O§ä¨ìB3:B80¬°¼Æ­È¡A¥BB2:B79¬OªÅ¥Õ­Èªº¦ì¸m
SUMPRODUCT(ISNUMBER($B$2:$B$80)*($B$3:$B$81=""))
«h¬O­pºâ¥XB3:B81¬°ªÅ¥Õ­È¡A¥BB2:B80¬O¼Æ­Èªº¦¸¼Æ(§Y¬°°Ï¶¡¼Æ)

freeffly

¦^´_ 2# Hsieh


    ª©¥D¥i¥H°Ý¤@­Ó°ÝÃD¶Ü?
    sumproduct¸Ì­±ªº°Ï¶¡¥i¥H¥Î¤£¤@¼Ë?
    §Ú¥H«e¥Î¥¦¨Ó§ì¸ê®Æ³£¬O¥Î¬Û¦P°Ï¶¡
    ÁöµM¥ÎF9¬Ý¨ì¤½¦¡µ²ªG¦ý¬OÁÙ·Q¤£¥X  
    ISNUMBER($B$2:$B$80)*($B$3:$B$81="") ¸ò ISNUMBER($B$3:$B$80)*($B$2:$B$79="")
    ³o¨â­Ó¨ì­ì²z

freeffly

¦^´_ 2# Hsieh


    ÁÂÁª©¥D
    ³o¬O§Ú­nªºµª®×
    ¬Ý¨Ó¤S­n¦A«×¤À¸Ñ¤½¦¡¬ã¨s¬ã¨s
    ·P®¦

Hsieh

¦^´_ 1# freeffly
°}¦C¤½¦¡
=IF(ROW(A1)>SUMPRODUCT(ISNUMBER($B$2:$B$80)*($B$3:$B$81="")),"",SMALL(IF(ISNUMBER($B$2:$B$80)*($B$3:$B$81=""),ROW($3:$81),""),ROW(A1))-SMALL(IF(ISNUMBER($B$3:$B$80)*($B$2:$B$79=""),ROW($3:$80),""),ROW(A1)))