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

[µo°Ý] ¤£³W«h¸õ¦C­Ó¼Æ¥[Á`

[µo°Ý] ¤£³W«h¸õ¦C­Ó¼Æ¥[Á`

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-12-9 11:52 ½s¿è


¦³ª¦¤å¨ì¤£³W«h¸õ¦C­Ó¼Æ¥[Á`ªº2003ª©¨ç¼Æ¤½¦¡
https://blog.xuite.net/hcm19522/twblog/588557495
=COUNT(0/(IF(ISNA(MATCH(ROW(A3:A51),SMALL(IF(A3:A51="",ROW(3:51)-{0,1}),ROW(1:19)),)),A3:A51)<6))   °}¦C
½Ð¸Ô¨£Sheet1!A1ªº¤½¦¡¡C

·Q®M¥Î¦bSheet2!A1
¦ý¤£¸Ñ¤½¦¡¤¤ROW(1:19)ªº¥Ñ¨Ó¡AµLªk±o¨ì¥¿½Tµª®×¡C
EX¡JSheet2!A2
=COUNT(0/(IF(ISNA(MATCH(ROW(A3:A71),SMALL(IF(A3:A71="",ROW(3:71)-{0,1}),ROW(1:19)),)),A3:A71)<6))   °}¦C
Sheet2!A2¡JC2ªº¥[Á`­Ó¼Æ­è¦n³£=Sheet2!A1¡JC1ªº¥¿½Tµª®×+1­Ó¡C

½Ð°Ý¡JSheet2!A1ªº¥¿½T¤½¦¡¡H
ÁÂÁ¡I
´ú¸ÕÀÉ : ¤£³W«h¸õ¦C­Ó¼Æ¥[Á`.rar (3.94 KB)

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-12-9 13:24 ½s¿è

©êºp!§Ñ¤F¤½¦¡ÅÞ¿è±ø¥óªº¤å¦r»¡©ú^^"
Sheet2!A1ªº¤½¦¡»Ý¨D¡J
A3¡JA63¨C5¦C¸õ2¦C¡A­pºâ"<6"ªº¼Æ¦r­Ó¼Æ©MA66¡JA69ªº"<6"ªº¼Æ¦r­Ó¼Æ¤§­Ó¼ÆÁ`©M¡C

TOP

=SUMPRODUCT((A4:A71<>"")*(A3:A70<>"")*(A3:A70<6))

TOP

¦^´_ 3# ­ã´£³¡ªL
­ã´£ª©¥D :
±z¯uªº«Ü¼F®`¡A§Q¥Î¤T¬qªºTRUE;FALSE¥æ¶°¡A´N¥i¥H¸ÑÃD¡C
¨ü¯q¨}¦h¡A·P®¦³á~~~

TOP

        ÀR«ä¦Û¦b : §ïÅܦۤv¬O¦Û±Ï¡A¼vÅT§O¤H¬O±Ï¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD