ÄÚÈÝ·¢²¼¸üÐÂʱ¼ä : 2024/12/24 9:11:50ÐÇÆÚÒ» ÏÂÃæÊÇÎÄÕµÄÈ«²¿ÄÚÈÝÇëÈÏÕæÔĶÁ¡£
ExcelһЩµç×Ó±í¸ñ¹«Ê½´óÈ«
1¡¢ ²éÕÒÖظ´ÄÚÈݹ«Ê½£º=IF(COUNTIF(A:A,A2)>1,\Öظ´\¡£
2¡¢ ÓóöÉúÄêÔÂÀ´¼ÆËãÄêÁ乫ʽ£º=TRUNC((DAYS360(H6,\¡£ 3¡¢ ´ÓÊäÈëµÄ18λÉí·ÝÖ¤ºÅµÄ³öÉúÄêÔ¼ÆË㹫ʽ£º
=CONCATENATE(MID(E2,7,4),\¡£ 4¡¢ ´ÓÊäÈëµÄÉí·ÝÖ¤ºÅÂëÄÚÈÃϵͳ×Ô¶¯ÌáÈ¡ÐԱ𣬿ÉÒÔÊäÈëÒÔϹ«Ê½£º
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,\ÄÐ\Å®\ÄÐ\Å®\¹«Ê½Äڵġ°C2¡±´ú±íµÄÊÇÊäÈëÉí·ÝÖ¤ºÅÂëµÄµ¥Ôª¸ñ¡£
1¡¢ÇóºÍ£º =SUM(K2:K56) ¡ª¡ª¶ÔK2µ½K56ÕâÒ»ÇøÓò½øÐÐÇóºÍ£»
2¡¢Æ½¾ùÊý£º =AVERAGE(K2:K56) ¡ª¡ª¶ÔK2 K56ÕâÒ»ÇøÓòÇóƽ¾ùÊý£» 3¡¢ÅÅÃû£º =RANK(K2£¬K$2:K$56) ¡ª¡ª¶Ô55ÃûѧÉúµÄ³É¼¨½øÐÐÅÅÃû£» 4¡¢µÈ¼¶£º =IF(K2>=85,\ÓÅ\Á¼\¼°¸ñ\²»¼°¸ñ\
5¡¢Ñ§ÆÚ×ÜÆÀ£º =K2*0.3+M2*0.3+N2*0.4 ¡ª¡ª¼ÙÉèKÁС¢MÁкÍNÁзֱð´æ·Å×ÅѧÉúµÄ¡°Æ½Ê±×ÜÆÀ¡±¡¢¡°ÆÚÖС±¡¢¡°ÆÚÄ©¡±ÈýÏî³É¼¨£»
6¡¢×î¸ß·Ö£º =MAX(K2:K56) ¡ª¡ªÇóK2µ½K56ÇøÓò£¨55ÃûѧÉú£©µÄ×î¸ß·Ö£» 7¡¢×îµÍ·Ö£º =MIN(K2:K56) ¡ª¡ªÇóK2µ½K56ÇøÓò£¨55ÃûѧÉú£©µÄ×îµÍ·Ö£» 8¡¢·ÖÊý¶ÎÈËÊýͳ¼Æ£º
£¨1£© =COUNTIF(K2:K56,\¡ª¡ªÇóK2µ½K56ÇøÓò100·ÖµÄÈËÊý£»¼ÙÉè°Ñ½á¹û´æ·ÅÓÚK57µ¥Ôª¸ñ£»
£¨2£© =COUNTIF(K2:K56,\£K57 ¡ª¡ªÇóK2µ½K56ÇøÓò95¡«99.5·ÖµÄÈËÊý£»¼ÙÉè°Ñ½á¹û´æ·ÅÓÚK58µ¥Ôª¸ñ£»
£¨3£©=COUNTIF(K2:K56,\£SUM(K57:K58) ¡ª¡ªÇóK2µ½K56ÇøÓò90¡«94.5·ÖµÄÈËÊý£»¼ÙÉè°Ñ½á¹û´æ·ÅÓÚK59µ¥Ôª¸ñ£»
£¨4£©=COUNTIF(K2:K56,\£SUM(K57:K59) ¡ª¡ªÇóK2µ½K56ÇøÓò85¡«89.5·ÖµÄÈËÊý£»¼ÙÉè°Ñ½á¹û´æ·ÅÓÚK60µ¥Ôª¸ñ£»
£¨5£©=COUNTIF(K2:K56,\£SUM(K57:K60) ¡ª¡ªÇóK2µ½K56ÇøÓò70¡«84.5·ÖµÄÈËÊý£»¼ÙÉè°Ñ½á¹û´æ·ÅÓÚK61µ¥Ôª¸ñ£»
£¨6£©=COUNTIF(K2:K56,\£SUM(K57:K61) ¡ª¡ªÇóK2µ½K56ÇøÓò60¡«69.5·ÖµÄÈËÊý£»¼ÙÉè°Ñ½á¹û´æ·ÅÓÚK62µ¥Ôª¸ñ£»
£¨7£© =COUNTIF(K2:K56,\¡ª¡ªÇóK2µ½K56ÇøÓò60·ÖÒÔϵÄÈËÊý£»¼ÙÉè°Ñ½á¹û´æ·ÅÓÚK63µ¥Ôª¸ñ£»
˵Ã÷£ºCOUNTIFº¯ÊýÒ²¿É¼ÆËãijһÇøÓòÄС¢Å®ÉúÈËÊý¡£
È磺=COUNTIF(C2:C351,\ÄÐ\¡ª¡ªÇóC2µ½C351ÇøÓò£¨¹²350ÈË£©ÄÐÐÔÈËÊý£» 9¡¢ÓÅÐãÂÊ£º =SUM(K57:K60)/55*100 10¡¢¼°¸ñÂÊ£º =SUM(K57:K62)/55*100 11¡¢±ê×¼²î£º =STDEV(K2:K56) ¡ª¡ªÇóK2µ½K56ÇøÓò(55ÈË)µÄ³É¼¨²¨¶¯Çé¿ö£¨ÊýֵԽС£¬ËµÃ÷¸Ã°àѧÉú¼äµÄ³É¼¨²îÒì½ÏС£¬·´Ö®£¬ËµÃ÷¸Ã°à´æÔÚÁ½¼«·Ö»¯£©£»
12¡¢Ìõ¼þÇóºÍ£º =SUMIF(B2:B56,\ÄÐ\£¬K2:K56) ¡ª¡ª¼ÙÉèBÁдæ·ÅѧÉúµÄÐÔ±ð£¬KÁдæ·ÅѧÉúµÄ·ÖÊý£¬Ôò´Ëº¯Êý·µ»ØµÄ½á¹û±íʾÇó¸Ã°àÄÐÉúµÄ³É¼¨Ö®ºÍ£» 13¡¢¶àÌõ¼þÇóºÍ£º£û =SUM(IF(C3:C322=\ÄÐ\£ý ¡ª¡ª¼ÙÉèCÁУ¨C3:C322ÇøÓò£©´æ·ÅѧÉúµÄÐÔ±ð£¬GÁУ¨G3:G322ÇøÓò£©´æ·ÅѧÉúËùÔڰ༶´úÂ루1¡¢2¡¢3¡¢4¡¢5£©£¬Ôò´Ëº¯Êý·µ»ØµÄ½á¹û±íʾÇóÒ»°àµÄÄÐÉúÈËÊý£»ÕâÊÇÒ»¸öÊý×麯Êý£¬ÊäÍêºóÒª°´Ctrl£«Shift£«Enter×éºÏ¼ü(²úÉú¡°£û¡¡£ý¡±)¡£¡°£û£ý¡±²»ÄÜÊÖ¹¤ÊäÈ룬ֻÄÜÓÃ×éºÏ¼ü²úÉú¡£
14¡¢¸ù¾Ý³öÉúÈÕÆÚ×Ô¶¯¼ÆËãÖÜË꣺=TRUNC((DAYS360(D3,NOW( )))/360,0) ¡ª¡ª¡ª¼ÙÉèDÁдæ·ÅѧÉúµÄ³öÉúÈÕÆÚ£¬EÁÐÊäÈë¸Ãº¯ÊýºóÔò²úÉú¸ÃÉúµÄÖÜËê¡£ 15¡¢ÔÚWordÖÐÈý¸öСÇÏÃÅ£º
¢ÙÁ¬ÐøÊäÈëÈý¸ö¡°~¡±¿ÉµÃÒ»Ìõ²¨ÀËÏß¡£ ¢ÚÁ¬ÐøÊäÈëÈý¸ö¡°-¡±¿ÉµÃÒ»ÌõÖ±Ïß¡£ Á¬ÐøÊäÈëÈý¸ö¡°=¡±¿ÉµÃÒ»ÌõË«Ö±Ïß¡£
Ò»¡¢excelÖе±Ä³Ò»µ¥Ôª¸ñ·ûºÏÌض¨Ìõ¼þ£¬ÈçºÎÔÚÁíÒ»µ¥Ôª¸ñÏÔʾÌض¨µÄÑÕÉ«±ÈÈ磺 A1¡µ1ʱ£¬C1ÏÔʾºìÉ« 0 1¡¢µ¥Ôª»÷C1µ¥Ôª¸ñ£¬µã¡°¸ñʽ¡±>¡°Ìõ¼þ¸ñʽ¡±£¬Ìõ¼þ1ÉèΪ£º ¹«Ê½ =A1=1 2¡¢µã¡°¸ñʽ¡±->¡°×ÖÌ塱->¡°ÑÕÉ«¡±£¬µã»÷ºìÉ«ºóµã¡°È·¶¨¡±¡£ Ìõ¼þ2ÉèΪ£º ¹«Ê½ =AND(A1>0,A1<1) 3¡¢µã¡°¸ñʽ¡±->¡°×ÖÌ塱->¡°ÑÕÉ«¡±£¬µã»÷ÂÌÉ«ºóµã¡°È·¶¨¡±¡£ Ìõ¼þ3ÉèΪ£º ¹«Ê½ =A1<0 µã¡°¸ñʽ¡±->¡°×ÖÌ塱->¡°ÑÕÉ«¡±£¬µã»÷»ÆÉ«ºóµã¡°È·¶¨¡±¡£ 4¡¢Èý¸öÌõ¼þÉ趨ºÃºó£¬µã¡°È·¶¨¡±¼´³ö¡£ ¶þ¡¢EXCELÖÐÈçºÎ¿ØÖÆÿÁÐÊý¾ÝµÄ³¤¶È²¢±ÜÃâÖظ´Â¼Èë 1¡¢ÓÃÊý¾ÝÓÐЧÐÔ¶¨ÒåÊý¾Ý³¤¶È¡£ ÓÃÊó±êÑ¡¶¨ÄãÒªÊäÈëµÄÊý¾Ý·¶Î§£¬µã\Êý¾Ý\ÓÐЧÐÔ\ÉèÖÃ\£¬\ÓÐЧÐÔÌõ¼þ\Éè³É\ÔÊÐí\Îı¾³¤¶È\µÈÓÚ\£¨¾ßÌåÌõ¼þ¿É¸ù¾ÝÄãµÄÐèÒª¸Ä±ä£©¡£ »¹¿ÉÒÔ¶¨ÒåһЩÌáʾÐÅÏ¢¡¢³ö´í¾¯¸æÐÅÏ¢ºÍÊÇ·ñ´ò¿ªÖÐÎÄÊäÈë·¨µÈ£¬¶¨ÒåºÃºóµã\È·¶¨\¡£ 2¡¢ÓÃÌõ¼þ¸ñʽ±ÜÃâÖظ´¡£ Ñ¡¶¨AÁУ¬µã\¸ñʽ\Ìõ¼þ¸ñʽ\£¬½«Ìõ¼þÉè³É¡°¹«Ê½=COUNTIF($A:$A,$A1)>1¡±£¬µã\¸ñʽ\×ÖÌå\ÑÕÉ«\£¬Ñ¡¶¨ºìÉ«ºóµãÁ½´Î\È·¶¨\¡£ ÕâÑùÉ趨ºÃºóÄãÊäÈëÊý¾ÝÈç¹û³¤¶È²»¶Ô»áÓÐÌáʾ£¬Èç¹ûÊý¾ÝÖظ´×ÖÌ彫»á±ä³ÉºìÉ«¡£ Èý¡¢ÔÚEXCELÖÐÈçºÎ°ÑBÁÐÓëAÁв»Í¬Ö®´¦±êʶ³öÀ´£¿ £¨Ò»£©¡¢Èç¹ûÊÇÒªÇóA¡¢BÁ½ÁеÄͬһÐÐÊý¾ÝÏà±È½Ï£º ¼Ù¶¨µÚÒ»ÐÐΪ±íÍ·£¬µ¥»÷A2µ¥Ôª¸ñ£¬µã¡°¸ñʽ¡±->¡°Ìõ¼þ¸ñʽ¡±£¬½«Ìõ¼þÉèΪ: ¡°µ¥Ôª¸ñÊýÖµ¡± ¡°²»µÈÓÚ¡±=B2 µã¡°¸ñʽ¡±->¡°×ÖÌ塱->¡°ÑÕÉ«¡±£¬Ñ¡ÖкìÉ«£¬µãÁ½´Î¡°È·¶¨¡±¡£ Óøñʽˢ½«A2µ¥Ôª¸ñµÄÌõ¼þ¸ñʽÏòϸ´ÖÆ¡£ BÁпɲÎÕÕ´Ë·½·¨ÉèÖᣠ£¨¶þ£©¡¢Èç¹ûÊÇAÁÐÓëBÁÐÕûÌå±È½Ï£¨¼´ÏàͬÊý¾Ý²»ÔÚͬһÐУ©£º ¼Ù¶¨µÚÒ»ÐÐΪ±íÍ·£¬µ¥»÷A2µ¥Ôª¸ñ£¬µã¡°¸ñʽ¡±->¡°Ìõ¼þ¸ñʽ¡±£¬½«Ìõ¼þÉèΪ: ¡°¹«Ê½¡±=COUNTIF($B:$B,$A2)=0 µã¡°¸ñʽ¡±->¡°×ÖÌ塱->¡°ÑÕÉ«¡±£¬Ñ¡ÖкìÉ«£¬µãÁ½´Î¡°È·¶¨¡±¡£ Óøñʽˢ½«A2µ¥Ôª¸ñµÄÌõ¼þ¸ñʽÏòϸ´ÖÆ¡£ BÁпɲÎÕÕ´Ë·½·¨ÉèÖᣠ°´ÒÔÉÏ·½·¨ÉèÖúó£¬ABÁоùÓеÄÊý¾Ý²»×ÅÉ«£¬AÁÐÓÐBÁÐÎÞ»òÕßBÁÐÓÐAÁÐÎÞµÄÊý¾Ý±ê¼Ç ΪºìÉ«×ÖÌå¡£ ËÄ¡¢EXCELÖÐÔõÑùÅúÁ¿µØ´¦Àí°´ÐÐÅÅÐò ¼Ù¶¨ÓдóÁ¿µÄÊý¾Ý(ÊýÖµ)£¬ÐèÒª½«Ã¿Ò»Ðа´´Ó´óµ½Ð¡ÅÅÐò£¬ÈçºÎ²Ù×÷£¿ ÓÉÓÚ°´ÐÐÅÅÐòÓë°´ÁÐÅÅÐò¶¼ÊÇÖ»ÄÜÓÐÒ»¸öÖ÷¹Ø¼ü×Ö,Ö÷¹Ø¼ü×ÖÏàͬʱ²ÅÄÜ°´´Î¹Ø¼ü×ÖÅÅÐò¡£ËùÒÔ£¬ÕâÒ»ÎÊÌâ²»ÄÜÓÃÅÅÐòÀ´½â¾ö¡£½â¾ö·½·¨ÈçÏ£º 1¡¢¼Ù¶¨ÄãµÄÊý¾ÝÔÚAÖÁEÁУ¬ÇëÔÚF1µ¥Ôª¸ñÊäÈ빫ʽ£º =LARGE($A1:$E1,COLUMN(A1)) ÓÃÌî³ä±ú½«¹«Ê½ÏòÓÒÏòϸ´ÖƵ½ÏàÓ¦·¶Î§¡£ ÄãÔÓÐÊý¾Ý½«°´ÐдӴóµ½Ð¡ÅÅÐò³öÏÖÔÚFÖÁJÁС£ÈçÓÐÐèÒª¿ÉÓá°Ñ¡ÔñÐÔÕ³Ìù/ÊýÖµ¡±¸´ÖƵ½ÆäËûµØ·½¡£ ×¢£ºµÚ1²½µÄ¹«Ê½¿É¸ù¾ÝÄãµÄʵ¼ÊÇé¿ö£¨Êý¾Ý·¶Î§£©×÷ÏàÓ¦µÄÐ޸ġ£Èç¹ûÒª´ÓСµ½´óÅÅÐò,¹«Ê½¸ÄΪ:=SMALL($A1:$E1,COLUMN(A1)) Îå¡¢ÇÉÓú¯Êý×éºÏ½øÐжàÌõ¼þµÄ¼ÆÊýͳ¼Æ Àý£ºµÚÒ»ÐÐΪ±íÍ·£¬AÁÐÊÇ¡°ÐÕÃû¡±£¬BÁÐÊÇ¡°°à¼¶¡±£¬CÁÐÊÇ¡°ÓïÎijɼ¨¡±£¬DÁÐÊÇ¡°Â¼È¡½á¹û¡±£¬ÏÖÔÚҪͳ¼Æ¡°°à¼¶¡±Îª¡°¶þ¡±£¬¡°ÓïÎijɼ¨¡±´óÓÚµÈÓÚ104£¬¡°Â¼È¡½á¹û¡±Îª¡°Öر¾¡±µÄÈËÊý¡£Í³¼Æ½á¹û´æ·ÅÔÚ±¾¹¤×÷±íµÄÆäËûÁС£ ¹«Ê½ÈçÏ£º =SUM(IF((B2:B9999=\¶þ\Öر¾\ÊäÈëÍ깫ʽºó°´Ctrl+Shift+Enter¼ü,ÈÃËü×Ô¶¯¼ÓÉÏÊý×鹫ʽ·ûºÅ\¡£ Áù¡¢ÈçºÎÅжϵ¥Ôª¸ñÀïÊÇ·ñ°üº¬Ö¸¶¨Îı¾£¿ ¼Ù¶¨¶ÔA1µ¥Ôª¸ñ½øÐÐÅжÏÓÐÎÞ\Ö¸¶¨Îı¾\ÒÔÏÂÈÎÒ»¹«Ê½¾ù¿É: =IF(COUNTIF(A1,\Ö¸¶¨Îı¾\ÓÐ\ÎÞ\=IF(ISERROR(FIND(\Ö¸¶¨Îı¾\ÎÞ\ÓÐ\ÇóijһÇøÓòÄÚ²»Öظ´µÄÊý¾Ý¸öÊý ÀýÈçÇóA1:A100·¶Î§ÄÚ²»Öظ´Êý¾ÝµÄ¸öÊý£¬Ä³¸öÊýÖظ´¶à´Î³öÏÖÖ»ËãÒ»¸ö¡£ÓÐÁ½ÖÖ¼ÆËã·½·¨£º Ò»ÊÇÀûÓÃÊý×鹫ʽ£º =SUM(1/COUNTIF(A1:A100,A1:A100)) ÊäÈëÍ깫ʽºó°´Ctrl+Shift+Enter¼ü,ÈÃËü×Ô¶¯¼ÓÉÏÊý×鹫ʽ·ûºÅ\¡£ ¶þÊÇÀûÓó˻ýÇóºÍº¯Êý£º =SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)) Æß¡¢Ò»¸ö¹¤×÷±¡ÖÐÓÐÐí¶à¹¤×÷±íÈçºÎ¿ìËÙÕûÀí³öÒ»¸öĿ¼¹¤×÷±í 1¡¢Óúê3.0È¡³ö¸÷¹¤×÷±íµÄÃû³Æ£¬·½·¨£º Ctrl+F3³öÏÖ×Ô¶¨ÒåÃû³Æ¶Ô»°¿ò£¬È¡ÃûΪX£¬ÔÚ¡°ÒýÓÃλÖ᱿òÖÐÊäÈ룺 =MID(GET.WORKBOOK(1),FIND(\È·¶¨ 2¡¢ÓÃHYPERLINKº¯ÊýÅúÁ¿²åÈëÁ¬½Ó£¬·½·¨£º ÔÚĿ¼¹¤×÷±í£¨Ò»°ãΪµÚÒ»¸ösheet£©µÄA2µ¥Ôª¸ñÊäÈ빫ʽ£º =HYPERLINK(\½«¹«Ê½ÏòÏÂÌî³ä£¬Ö±µ½³ö´íΪֹ£¬Ä¿Â¼¾ÍÉú³ÉÁË¡£ ¹¤×÷ÖÐ100%»áÓõ½µÄ¼¸¸öExcelº¯Êý