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

Âù±ø¥óªº®æ¦¡¤Æ±ø¥ó¡C

Âù±ø¥óªº®æ¦¡¤Æ±ø¥ó¡C

¥»©«³Ì«á¥Ñ papaya ©ó 2019-10-26 07:54 ½s¿è


³Æµù¡J
½Ð¥H2003ª©¨ç¼Æ¸ÑÃD¡CÁÂÁ¡I

B2¡JAO11ªº¤½¦¡±ø¥ó»Ý¨D¡J
¨ú¨C³æÄæ²Ä2¦C(§t)¥H¤Uªº³Ì¤j¼Æ(­«½Æ¨ú¨ä1¡AEX¡JAFÄæ¡AAMÄæ)¡A
¥B¸Ó³Ì¤j¼Æ¤]¬O¥þ³¡½d³ò(B2:AO11)ªº«e10¤j¼Æ(¦P¦W¦¸¥i­«½Æ)®É¡A«h±N¸ÓÀx¦s®æ¼Ð¥Üºñ¦â¡C

½Ð°Ý¡JB2¡JAO11ªº®æ¦¡¤Æ±ø¥ó¤½¦¡¡H
ÁÂÁ¡I
´ú¸ÕÀÉ¡J ®æ¦¡¤Æ±ø¥ó.rar (6.35 KB)

¥»©«³Ì«á¥Ñ papaya ©ó 2019-10-27 19:43 ½s¿è

°²¦p¥H1­Ó®æ¦¡¤Æ±ø¥óµLªk§¹¦¨¡A¥H2­Ó®æ¦¡¤Æ±ø¥ó¨Ó§¹¦¨¤]¥i¥H¡C

¥Ø«e§Ú¿ï¨úB2:AO11«á¡A¥H
±ø¥ó(¤@)
=(B2>0)*(B2=MAX(B$2:B$11))      ¼Ð¥ÜÂŦâ®Ø½u
­«½Æ¨ú¨ä1ªº®æ¦¡¤Æ±ø¥ó¤½¦¡¡A§Ú¤£·|¼g¡A½Ð¦U¦ì°ª¤â«ü±Ð­×¥¿¡C

±ø¥ó(¤G)
=(B2>0)*(SUMPRODUCT((B2<=$B$2:$AO$11)/COUNTIF($B$2:$AO$11,$B$2:$AO$11))<=10)     ¼Ð¥Üºñ¦â
³o¬O®M¥Îhcm19522¤j¤jªº¤½¦¡¡A¦ý¤£ª¾¬O¤£¬O¤£¾A¥ÎªºÃö«Y?¶]¤£¥X¥¿½Tµª®×^^///¡A
¦pªG¦³¾÷·|ªº¸Ü¡A©|½Ðhcm19522¤j¤j«ü¥¿¡C

¥H¤W  ½Ð¦U¦ì¤j¤j¤£§[½ç±Ð!ÁÂÁÂ!

TOP

±ø¥ó(¤G)
=(B2>0)*(SUMPRODUCT((B2<=$B$2:$AO$11)/COUNTIF($B$2:$AO$11,$B$2:$AO$11))<=10)     ¼Ð¥Üºñ¦â
¶]¤£¥X¥¿½Tµª®×ªº­ì¦]~¤£¯à¦³ªÅ®æ¡C
½Ð°Ý­n¦p¦ó­×¥¿?

½Ð¦U¦ì¤j¤j¤£§[½ç±Ð!ÁÂÁÂ!

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 4# hcm19522
·PÁ¤j¤j¸ÑÃD¡A´ú¸Õ¦¨¥\~¤Ó¯«¤F^^

¤£¦n·N«ä¡A¨CÄ檺³Ì¤j¼Æ¡A¦³­«½ÆªÌ¥u¨ú1­Óªº®æ¦¡¤Æ±ø¥ó¤½¦¡    =>¼Ð¥ÜÂŦâ®Ø½u
¯à§_½Ð±z¦A¦¸«ü±Ð?ÁÂÁ±z!

TOP

¦^´_ 5# papaya


    4¼Ó ¤w­×¥¿~~~~~~~
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2019-10-29 19:47 ½s¿è

¦^´_ 6# hcm19522
·PÁ¤j¤j¦A¦¸¸Ñ´b¡A´ú¸Õ¦¨¥\~±z¯uªº¬O¤Ó¼F®`¤F^^

=====================================
¤£¦n·N«ä¡AÁÙ¦³2­Ó¤£¨º»òÁc½Æªº°ÝÃD¡A¥i§_«_¬N¦A½Ð±z«ü¾É¡J
³Æµù : AÄæ³£¬°³sÄòªº¯Â¼Æ­È(§t0­È)¡AµLªÅ¥Õ®æ¡C
B2=IF(A2,IF(COUNTIF(A$1:A1,A2)>0,ROW()-LOOKUP(1,0/(A$1:A1=A2),ROW($1:1)),""),"")
Q1_AÄæÀx¦s®æ=0­È¡A¤]­n­pºâ (EX¡JB9À³¸Ó¬O=4)®É¡AB2¤½¦¡­n¦p¦ó­×¥¿¡H

Q2_­×¥¿«áªºB2¤½¦¡ªºA2¦p§ï¬°>=C$1©Î>=D$1¡A©Î>=E$1¡AC2¤½¦¡­n¦p¦ó½s¼g¡H

´ú¸ÕÀÉ®× : ¶¡¹j¦C+1ªº­pºâ.rar (3.4 KB)

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2019-10-29 21:39 ½s¿è

¦^´_ 7# papaya

¦] IF(A2,~   --> A2­Y=0-->«á­±"",©Ò¥H§ï  IF(A2<>"",~  (ª`·N ªÅ¥Õ®æ¤]¥i¯à¬O 0)
Q2:IF(A2>=C$1,~,"")     IF(A2>=D$1,~,"")     IF(A2>=E$1,~,"")

IF(A1,8,99)-->A1±ø¥ó¦¨¥ß ©Î«D0-->8   ;A1±ø¥ó¤£¦¨¥ß ©Î 0 ©ÎªÅ¥Õ-->99)    A1¥Nªí¼Æ¦r ©Î §PÂ_¦¡ (¦pC1=D1) ©Î ¹Bºâ¦¡ (¦pC1-D1)   ,­Y³æ¬O¤¤­^¤å «D«ü¥O (TRUE FALSE)-->¿ù»~
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 8# hcm19522
´ú¸Õ¦¨¥\¡C¸U¤À·PÁ±z¤£¹½¨ä·Ðªº­@¤ß«ü¾É¡C
±ß¦w^^

TOP

¤è¦¡¤G (ª¾¼Æ¦r³Ì¤j 99)=(LARGE(IF(COUNTIF($B$2:$AO$11,ROW($1:$99)),ROW($1:$99)),10)<=B2)*(COUNTIF(B$2:B2,B2)=1)*(MAX(B$2:B$11)=B2)
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD