办公表格常用公式大全.doc

《办公表格常用公式大全.doc》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、°ì¹«±í¸ñ³£Óù«Ê½´óÈ«1¡¢²éÕÒÖظ´ÄÚÈݹ«Ê½£º=if(countif(a:a,a2)>1,¡±Öظ´¡±,¡±)¡£2¡¢ÓóöÉúÄêÔÂÀ´¼ÆËãÄêÁ乫ʽ£º=trunc((days360(h6,¡±2009/8/30¡å,false))/360,0)¡£3¡¢´ÓÊäÈëµÄ18λÉí·ÝÖ¤ºÅµÄ³öÉúÄêÔ¼ÆË㹫ʽ£º=concatenate(mid(e2,7,4),¡±/¡±,mid(e2,11,2),¡±/¡±,mid(e2,13,2))¡£4¡¢´

2、ÓÊäÈëµÄÉí·ÝÖ¤ºÅÂëÄÚÈÃϵͳ×Ô¶¯ÌáÈ¡ÐԱ𣬿ÉÒÔÊäÈëÒÔϹ«Ê½£º=if(len(c2)=15,if(mod(mid(c2,15,1),2)=1,¡±ÄС±,¡±Å®¡±),if(mod(mid(c2,17,1),2)=1,¡±ÄС±,¡±Å®¡±))¹«Ê½Äڵġ°c2¡±´ú±íµÄÊÇÊäÈëÉí·ÝÖ¤ºÅÂëµÄµ¥Ôª¸ñ¡£1¡¢ÇóºÍ£º=sum(k2:k56)¡ª¡ª¶Ôk2µ½k56ÕâÒ»ÇøÓò½øÐÐÇóºÍ;2¡¢Æ½¾ùÊý£º=average(k

3、2:k56)¡ª¡ª¶Ôk2k56ÕâÒ»ÇøÓòÇóƽ¾ùÊý;3¡¢ÅÅÃû£º=rank(k2£¬k$2:k$56)¡ª¡ª¶Ô55ÃûѧÉúµÄ³É¼¨½øÐÐÅÅÃû;4¡¢µÈ¼¶£º=if(k2>=85,¡±ÓÅ¡±,if(k2>=74,¡±Á¼¡±,if(k2>=60,¡±¼°¸ñ¡±,¡±²»¼°¸ñ¡±)))5¡¢Ñ§ÆÚ×ÜÆÀ£º=k2*0.3+m2*0.3+n2*0.4¡ª¡ª¼ÙÉèkÁС¢mÁкÍnÁзֱð´æ·Å×ÅѧÉúµÄ¡°Æ½Ê±×ÜÆÀ¡±¡¢¡°ÆÚÖС±¡

4、¢¡°ÆÚÄ©¡±ÈýÏî³É¼¨;6¡¢×î¸ß·Ö£º=max(k2:k56)¡ª¡ªÇók2µ½k56ÇøÓò(55ÃûѧÉú)µÄ×î¸ß·Ö;7¡¢×îµÍ·Ö£º=min(k2:k56)¡ª¡ªÇók2µ½k56ÇøÓò(55ÃûѧÉú)µÄ×îµÍ·Ö;8¡¢·ÖÊý¶ÎÈËÊýͳ¼Æ£º(1)=countif(k2:k56,¡±100¡å)¡ª¡ªÇók2µ½k56ÇøÓò100·ÖµÄÈËÊý;¼ÙÉè°Ñ½á¹û´æ·ÅÓÚk57µ¥Ôª¸ñ;(2)=countif(k2:k56,¡±>=95¡å)-k

5、57¡ª¡ªÇók2µ½k56ÇøÓò95¡«99.5·ÖµÄÈËÊý;¼ÙÉè°Ñ½á¹û´æ·ÅÓÚk58µ¥Ôª¸ñ;(3)=countif(k2:k56,¡±>=90¡å)-sum(k57:k58)¡ª¡ªÇók2µ½k56ÇøÓò90¡«94.5·ÖµÄÈËÊý;¼ÙÉè°Ñ½á¹û´æ·ÅÓÚk59µ¥Ôª¸ñ;(4)=countif(k2:k56,¡±>=85¡å)-sum(k57:k59)¡ª¡ªÇók2µ½k56ÇøÓò85¡«89.5·ÖµÄÈËÊý;¼ÙÉè°Ñ½á¹û´æ·ÅÓÚk60µ¥Ôª¸ñ;

6、(5)=countif(k2:k56,¡±>=70¡å)-sum(k57:k60)¡ª¡ªÇók2µ½k56ÇøÓò70¡«84.5·ÖµÄÈËÊý;¼ÙÉè°Ñ½á¹û´æ·ÅÓÚk61µ¥Ôª¸ñ;(6)=countif(k2:k56,¡±>=60¡å)-sum(k57:k61)¡ª¡ªÇók2µ½k56ÇøÓò60¡«69.5·ÖµÄÈËÊý;¼ÙÉè°Ñ½á¹û´æ·ÅÓÚk62µ¥Ôª¸ñ;(7)=countif(k2:k56,¡±<60¡å)¡ª¡ªÇók2µ½k56ÇøÓò60·ÖÒÔϵÄÈËÊý;

7、¼ÙÉè°Ñ½á¹û´æ·ÅÓÚk63µ¥Ôª¸ñ;˵Ã÷£ºcountifº¯ÊýÒ²¿É¼ÆËãijһÇøÓòÄС¢Å®ÉúÈËÊý¡£È磺=countif(c2:c351,¡±ÄС±)¡ª¡ªÇóc2µ½c351ÇøÓò(¹²350ÈË)ÄÐÐÔÈËÊý;9¡¢ÓÅÐãÂÊ£º=sum(k57:k60)/55*10010¡¢¼°¸ñÂÊ£º=sum(k57:k62)/55*10011¡¢±ê×¼²î£º=stdev(k2:k56)¡ª¡ªÇók2µ½k56ÇøÓò(55ÈË)µÄ³É¼¨²¨¶¯Çé¿ö(ÊýÖµÔ½

8、С£¬ËµÃ÷¸Ã°àѧÉú¼äµÄ³É¼¨²îÒì½ÏС£¬·´Ö®£¬ËµÃ÷¸Ã°à´æÔÚÁ½¼«·Ö»¯);12¡¢Ìõ¼þÇóºÍ£º=sumif(b2:b56,¡±ÄС±£¬k2:k56)¡ª¡ª¼