ͨ³££¬ÔÚExcelÖÐÒªµÃµ½Ò»ÁÐÊýÖµÖеĵڶþ´óÊý¿ÉÓÃLARGEº¯Êý£¬Èç¡°=LARGE(A1:A10,2)¡±£¬µ«ÓÐʱ»áÓöµ½Óжà¸ö×î´óÖµµÄÇé¿ö£¬ÀýÈçAÁÐΪ¡°{9;9;9;4;8;4;8;5;5;6}¡±£¬ÉÏÊö¹«Ê½·µ»ØµÄ»¹ÊÇ¡°9¡±£¬¶øÎÒÃÇÒªµÃµ½µÄ¡°µÚ¶þ´ó¡±ÊýÖµÊÇ¡°8¡±¡£ÕâÖÖÇé¿öÏ¿ÉÓÃÏÂÃæµÄһЩ¹«Ê½£¬¼ÙÈçÊý¾ÝÔÚA1:A10£º
¹«Ê½Ò»£º
=LARGE(A1:A10,COUNTIF(A1:A10,MAX(A1:A10))+1)
¹«Ê½¶þ£¨Êý×鹫ʽ£©£º
=MAX((A1:A10<MAX(A1:A10))*A1:A10)
ÊäÈëÍê±ÏÐè°´Ctrl+Shift+Enter½áÊø£¬ÏÂͬ¡£
¹«Ê½Èý£º
=LARGE(IF(FREQUENCY(A1:A10,A1:A10),A1:A10),2)
¹«Ê½ËÄ£¨Êý×鹫ʽ£©£º
=LARGE(IF(MATCH(A1:A10,A1:A10,)=ROW(1:10),A1:A10),2)frequency函数计算频数
×îºóÁ½¸ö¹«Ê½²»½öÄÜ·µ»Ø¡°µÚ¶þ´ó¡±ÊýÖµ£¬»¹ÄÜ·µ»Ø¡°µÚN´ó¡±ÊýÖµ¡£½«ÆäÖеġ°2¡±¸ÄΪN¼´¿É·µ»Ø¡°µÚN´ó¡±ÊýÖµ¡£
˵Ã÷£º
1.µÚÒ»¸ö¹«Ê½ÏÈͳ¼ÆA1:A10ÖÐ×î´óÖµµÄÊýÁ¿£¬ÔÙ¼ÓÉÏ¡°1¡±×÷ΪLARGEº¯ÊýµÄµÚ
¶þ¸ö²ÎÊýµÃµ½ËùÐè¡°µÚ¶þ´ó¡±ÊýÖµ¡£
2.µÚ¶þ¸ö¹«Ê½ÏÈÇó³öËùÓÐСÓÚA1:A10ÖÐ×î´óÖµµÄÊý£¬ÔÙÓÃMAXº¯Êý·µ»ØÆäÖеÄ×î´óÖµ£¬¼´¡°µÚ¶þ´ó¡±ÊýÖµ¡£
3.FREQUENCYº¯Êý¼ÆËãÊýÖµÔÚÇøÓòÄڵijöÏÖƵÂÊ£¬²¢·µ»ØÒ»¸ö´¹Ö±Êý×é¡£µÚÈý¸ö¹«Ê½ÖÐFREQUENCYº¯ÊýµÄÁ½¸ö²ÎÊý¶¼ÊÇ¡°A1:A10¡±£¬·µ»ØµÄ´¹Ö±Êý×é¶ÔÓ¦A1:A10ËùÓÐÖظ´ÖµµÄλÖô¦Îª¡°0¡±¡£ÕâÑù£¬½áºÏIFº¯Êý¿É·µ»ØA1:A10Öв»°üº¬Öظ´ÖµµÄÊý×飬×îºóÓÃLARGEº¯Êý·µ»Ø¡°µÚ¶þ´ó¡±ÊýÖµ¡£Ö®ËùÒÔÖظ´Ïî¶ÔӦλÖ÷µ»Ø¡°0¡±£¬ÕâÀï±ÊÕß½áºÏ×Ô¼ºµÄÀí½â¼òµ¥½âÊÍһϣ¬½ö¹©²Î¿¼¡£FREQUENCYº¯ÊýµÄÓ﷨Ϊ£º
FREQUENCY(data_array, bins_array)
±¾ÀýA1:A10¼°FREQUENCY(A1:A10,A1:A10)µÄ·µ»ØÖµ·Ö±ðÊÇ£º
A1:A10£º {9;9;9;4;8;4;8;5;5;6}
·µ»ØµÄ´¹Ö±Êý×飺 {3;0;0;2;2;0;0;2;0;1;0}
¿ÉÒÔ¿´µ½£¬µÚ¶þ¸ö¡¢µÚÈý¸ö¡°9¡±¶ÔÓ¦·µ»ØÖµµÄλÖô¦¶¼Îª¡°0¡±£¬µÚ¶þ´Î³öÏֵġ°4¡±¡¢¡°8¡±¼°¡°5¡±¶ÔӦλÖô¦Ò²¶¼Îª¡°0¡±¡£¼ÙÈçFREQUENCYº¯ÊýµÚ¶þ¸ö²ÎÊýÊý×é¡°bins_array¡±ÖÐÓÐN¸öÊýÖµ£¬¼´Ê¹¸Ã²ÎÊýÊý×éÖеÄÊýֵûÓа´´ÓСµ½´óµÄ˳ÐòÅÅÁУ¨Èç±¾Àý£©£¬ FREQUENCYº¯ÊýÒ²»áÔÚ¼ÆËãʱ×Ô¶¯½«Æä°´´ÓСµ½´óµÄ˳ÐòÅÅÁУ¬²¢½«Õû¸öÊýÖµÇøÓò»®·ÖΪN+1¸öÇø¼ä£¬¼ÆËãµÚÒ»¸ö²ÎÊýÊý×é¡°data_array¡±ÖеÄÊýÖµÔÚÕâN+1¸öÇø¼äÖеijöÏÖÊýÁ¿£¬Í¬Ê±ÒÔ´¹Ö±Êý×éµÄÐÎʽ·µ»Ø£¬ÕâÒ²ÊÇ FREQUENCYº¯Êý·µ»ØµÄÊý×é±ÈµÚ¶þ¸ö²ÎÊýÊý×é¡°bins_array¡±¶àÒ»¸öÊýÖµµÄÔÒò¡£¿ÉÒÔ¿´µ½£¬·µ»ØµÄ´¹Ö±Êý×éÖи÷ÊýÖµ³öÏÖµÄλÖÃÓëµÚ¶þ¸ö²ÎÊýÊý×é¡°bins_array¡±ÖеÄÊýֵλÖÃÊÇÒ»Ò»¶ÔÓ¦µÄ£¬×îºó¶à³öµÄÒ»¸öÊýΪ¡°data_array¡±ÖдóÓÚ¡°bins_array¡±×î´óÖµµÄÊýÁ¿¡£±¾ÀýFREQUENCYº¯ÊýµÚ¶þ¸ö²ÎÊýÊý×é¡°bins_array¡±Ò²Îª¡°A1:A10¡±£¬ÅÅÐòºóΪ¡°{4;4;5;5;6;8;8;9;9;9}¡±£¬10¸öÊý¹²»®·ÖÁË11¸öÇø¼ä£¬ÆäÖÐÓÐÈý¸ö¡°9¡±£¬µÚÒ»¸ö¡°9¡±¶ÔÓ¦µÄÇø¼äΪ¡°>8¡±ÇÒ¡°<=9¡±,A1:A10ÖÐÕâÑùµÄÊýÓÐ3¸ö£¨¼´3¸ö¡°9¡±
£©£¬·µ»Ø¡°3¡±£¬µÚ¶þ¸öºÍµÚÈý¸ö¡°9¡±Ëù¶ÔÓ¦µÄÇø¼ä¶¼Îª¡°>9¡±ÇÒ¡°<=9¡±£¬Õâ¸öÇø¼äÊDz»´æÔڵģ¬Æä¶ÔӦλÖö¼·µ»Ø¡°0¡±¡£ÆäËûÖظ´ÊýÖµ¶ÔÓ¦µÄ·µ»ØÖµ¡°0¡±ÒÀ´ËÀàÍÆ£¬²Î¿¼ÏÂ±í¡£
4.×îºóÒ»¸ö¹«Ê½ÖС°IF(MATCH(A1:A10,A1:A10,)=ROW(1:10),A1:A10)¡±µÄ×÷ÓÃÓëµÚÒ»¸ö¹«Ê½ÀàËÆ£¬²»ÔÙ׸Êö¡£
发表评论