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

¨D¼Ë¥»­È¤p©ó(¤j©ó)¼Ð·Ç­È²[¼Æ

¨D¼Ë¥»­È¤p©ó(¤j©ó)¼Ð·Ç­È²[¼Æ

¥»©«³Ì«á¥Ñ 074063 ©ó 2015-10-28 22:09 ½s¿è

Q1.rar (8.22 KB)

¦pªþ¥ó,
¼Ð·Ç½d³ò­È¦b¤W¤U­­¦b¦P¤@­ÓÀx¦s®æ(¦pB1®æ¦¡)
¨D~ B2(¼Ë¥»¼Æ):     B8¥H¤U¦³«DªÅ¥ÕÀx¦s®æ­Ó¼Æ¦X
        B3(§C©ó¼Ð·Ç): ¼Ë¥»­È¤º§C©ó¼Ð·Ç­È(B1)­Ó¼Æ¦X
        B4(°ª©ó¼Ð·Ç): ¼Ë¥»­È¤º°ª©ó¼Ð·Ç­È(B1)­Ó¼Æ¦X

³Ò·Ð¦U¦ì¤j¤j¤£§[½ç±Ð ^^

¸É¥R: ¦pªþ¥óB1®æ¦¡, ¦p¦ó¨ú¥X "~" ¥H¥ª¤Î¥H¥kªº­È? (B1ªº­È¦ì¼Æ¤£¤@©w, ¦ý¤W¤U­­­È¤¤¶¡¤@©w¦³­Ó"~")

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-10-29 10:14 ½s¿è

¥þ³¡¼Ë¥»­Ó¼Æ¡G=COUNT(B7:B106)
§C©ó¼Ð·Ç­Ó¼Æ¡D¢Ð¢²¡G=COUNTIF(B7:B106,"<"&LEFT(B1,FIND("~",B1)-1))
°ª©ó¼Ð·Ç­Ó¼Æ¡D¢Ð¢³¡G=COUNTIF(B7:B106,">"&MID(B1,FIND("~",B1)+1,9))

¤¶©ó4.20~4.70­Ó¼Æ¡G
=COUNTIF(B7:B106,">="&LEFT(B1,FIND("~",B1)-1))-COUNTIF(B7:B106,">"&MID(B1,FIND("~",B1)+1,9))
=SUM(COUNTIF(B7:B106,{">=",">"}&MID(SUBSTITUTE(B1,"~",REPT(0,50)),{1,25},50))*{1,-1})
=COUNT(B7:B106)-B3-B4

TOP

¨D~ B2(¼Ë¥»¼Æ):     B8¥H¤U¦³«DªÅ¥ÕÀx¦s®æ­Ó¼Æ©M
¬O§_¬°:
¨D~ B2(¼Ë¥»¼Æ):     B7¥H¤U¦³«DªÅ¥ÕÀx¦s®æ­Ó¼Æ©M

¸Õ¸Õ¬Ý:
B2: =COUNTA($B$7B$65536)
B3: =COUNTIF($B$7B$65536,">="&LEFT($B$1,FIND("~",$B$1)-1))
B4: =COUNTIF($B$7B$65536,"<=" & RIGHT($B$1,LEN($B$1)-FIND("~",$B$1)))

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-10-29 11:15 ½s¿è

§ó¥¿¡G
B2: =COUNTA($B$7B$65536)
COUNTA·|³s¤å¦rµ¥­pºâ¶i¥h, COUNT ¥u­pºâ¼Æ¦rÀx¦s®æ
§ó¥¿¬° =COUNT($B$7B$65536)

B3: =COUNTIF($B$7B$65536,">="&LEFT($B$1,FIND("~",$B$1)-1))
§ó¥¿¬° =COUNTIF($B$7B$65536,">"&LEFT($B$1,FIND("~",$B$1)-1))
B4: =COUNTIF($B$7B$65536,"<=" & RIGHT($B$1,LEN($B$1)-FIND("~",$B$1)))
§ó¥¿¬° =COUNTIF($B$7B$65536,"<" & RIGHT($B$1,LEN($B$1)-FIND("~",$B$1)))

TOP

¦^´_ 2# ­ã´£³¡ªL


    «Ü¹ê¥Î~·PÁ ­ã´£³¡ªL ¤j¤j¸Ñµª ^^

TOP

¦^´_ 4# yen956


    ÁöµMºâ¥X¨Óªº­Ó¼Æ¤£¤Ó¥¿½T~¤£¹LÁÙ¬O·PÁ yen956 ¤j¤j¤£§[½ç±Ð ^^

TOP

¦^´_ 6# 074063
­Ó¼ÆªGµM¤j¤j¤£¥¿½T, ÁÂÁ«ü¥¿!!(> »P < À³¹ï½Õ¤~¹ï)
¥¿½T¦p¤U:
B2:        =COUNT($B$7:$B$65536)
B3:        =COUNTIF($B$7:$B$65536,">"&LEFT($B$1,FIND("~",$B$1)-1))
B4:        =COUNTIF($B$7:$B$65536,"<" & RIGHT($B$1,LEN($B$1)-FIND("~",$B$1)))

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD