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

¤£³sÄòÀx¦s®æ¨Ì¤ñ¨Ò­pºâ¥­§¡

¤£³sÄòÀx¦s®æ¨Ì¤ñ¨Ò­pºâ¥­§¡

TOP 50¡B30¡B20ªº­pºâµ²ªG¥Ø«e³¡¤À¬O¤H¤u­pºâ«á¤â°Ê¿é¤J¤½¦¡¤º
TOP 50%¡B30%¡B20%¥Ø«e·Q¨ìªº¤èªk»PTOP 50¡B30¡B20¬Û¦P¡A³£»Ý­n¤H¤u­pºâ«á¿é¤J
¥Ñ©ó¨C¦¸ªº¸ê®Æ¶q¥i¯à¤£¬Û¦P
½Ð°Ý¸Ó«ç»ò­×§ï¤½¦¡¦¨¥i¥H¦Û°Ê§PÂ_«á­pºâ¥X¬Û¦Pªºµ²ªG?
·Q¾ã²z¦¨¦p¤Uªºµ²ªG¡AªþÀɦ³raw data
¥ýÁÂÁÂÀ°¦£ªº¦U¦ì

               >80    <80
³Ì¤j      85.40     31.08
³Ì¤p          81.88  1.27
¥­§¡         83.16  3.78
TOP 50    82.51     1.44
TOP 30      82.39  1.39
TOP 20      82.31  1.37
TOP 50%         
TOP 30%         
TOP 20%

Book2.zip (8.14 KB)

TOP 50¡@¤£¬O¡e¥Ñ¤j¦Ü¤p«e¢´¢¯¡f¡H
=AVERAGE(LARGE(sort!E$2:AO$99,ROW($1:$50)))¡@°}¦C

­ì¤½¦¡¡G=AVERAGE(LARGE(sort!E$2:AO$99,ROW($199:$248)))
¬O¡e>80¡f¼Æ­Èªº¡e¥Ñ¤p¦Ó¤j«e¢´¢¯¡f

¡e¤£³sÄòÀx¦s®æ¡f¡A¥u¯à¥Î OFFSET ©Î INDIRECT¡A¦ý«Ü¦h¨ç¼Æ¤£¦Y¡eÁp¶°¡f¡A
¤â°Ê«ö CTRL¡e¸õ¿ï¡f¦UÄæ¦ì¦A¡e©w¸q¦WºÙ¡f¸û¥i¦æ¡A¦ý°ÊºA¼u©Ê¤p¡A
¦WºÙ¡GxArea
=sort!$E:$E,sort!$I:$I,sort!$M:$M,sort!$Q:$Q,sort!$U:$U,sort!$Y:$Y,sort!$AC:$AC,sort!$AG:$AG,sort!$AK:$AK,sort!$AO:$AO

«e¢´¢¯¤j¥­§¡¡G=AVERAGE(LARGE(xArea,ROW($1:$50)))¡@°}¦C

TOP

¨ä¹ê¨º¨Ç¼Æ¦r¬O®É¶¡¡A©Ò¥H§Æ±æ®É¶¡·U¤Ö·U¦n
¤~·|³y¦¨TOP 50­n¥ýºâ¥XÁ`¼Æ«á«e±À
¦b¬Ý¤Fª©¥Dªº¦^´_«á¬ðµM¦³­Ó·Qªk
¦pªG³]­p2­Ó°ÊºA¿ï®Øªº¸Ü
¤@­Ó¿ï80¥H¤W¡B¤@­Ó¿ï80¥H¤U
³o¼Ë¤l¤@¨Óª½±µ¨Ï¥Îª©¥Dªº¤½¦¡¼gªk
§âlarge§ï¦¨small§Y¥i±o¨ì§Ú­nªº®ÄªG¤F
¤£ª¾¹D¥i¤£¥i¦æ¡Aµ¥µ¥¨Ó¸Õ¸Õ¬Ý

TOP

http://blog.xuite.net/hcm19522/twblog/343417217

TOP

¦Û¤v¾ã²z«á¸Õ¥Îhcm19522ªº¤èªk
¦ýºâ¥X¨ÓªºªF¦è³£«Ü©_©Ç
§Ú¼gªº¤½¦¡¬O{=AVERAGE(IF(±Æ§ÇP<=SMALL(±Æ§ÇP,CHOOSE(ROW(A1),50,30,20)),±Æ§ÇP))}
¤£ª¾¹D¿ù¦b¨º¸Ì

¾ã²z¼Æ¾Úªº¤è¦¡¦³ÂI²Â(­ì¥»·Q¥Î°ÊºA¿ï¨ú¦ì¸m¡A¤£¹L°ÊºAÁÙ¬O¤£¯à¸õ¿ï)
´X¥G¨C¦¸³£­n®Ö¹ï¤@¦¸¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-9-25 18:22 ½s¿è

=SMALL({1,2,2,3,3,3,6,8,9},4)¡@²Ä¢³¤p­È¬°¡e¢²¡f
¦ý°}¦C¤¤¡Õ¡×¢²ªº¦³¢µ­Ó¡A

©Ò¥H¤½¦¡ªº¥­§¡¬O¢µ­Ó¼Æ¦rªº¥­§¡{1,2,2,3,3,3}¡A
=AVERAGE(IF({1,2,2,3,3,3,6,8,9}<=SMALL({1,2,2,3,3,3,6,8,9},4),{1,2,2,3,3,3,6,8,9}))

¦Ó¤£¬O«e¢³¤p­Èªº¥­ÉÊ{1,2,2,3}¡I
=AVERAGE(SMALL({1,2,2,3,3,3,6,8,9},{1,2,3,4}))
¡@
¡@
¥t¡D¤£³sÄòÀx¦s®æ¸ûÃø¤U¤½¦¡¡ã¡ã

TOP

        ÀR«ä¦Û¦b : §Ú­Ì³Ì¤jªº¼Ä¤H¤£¬O§O¤H¡D¥i¯à¬O¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD