ãã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ãä»è¾å
¥ç身份è¯å·ç å
让系ç»èªå¨æåæ§å«ï¼å¯ä»¥è¾å
¥ä»¥ä¸å
¬å¼ï¼
ãã=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(K2:K56) ââ对K2 K56è¿ä¸åºåæ±å¹³åæ°ï¼
ãã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ååå«åæ¾çå¦ççâå¹³æ¶æ»è¯âãâæä¸âãâææ«âä¸é¡¹æ绩ï¼
ãã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")ï¼K57 ââæ±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åå
æ ¼ï¼
ããï¼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å以ä¸ç人æ°ï¼å设æç»æåæ¾äºK63åå
æ ¼ï¼
ãã说æï¼COUNTIFå½æ°ä¹å¯è®¡ç®æä¸åºåç·ã女ç人æ°ã
ããå¦ï¼=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="ç·",IF(G3:G322=1,1,0)))ï½ ââå设Cåï¼C3:C322åºåï¼åæ¾å¦ççæ§å«ï¼Gåï¼G3:G322åºåï¼åæ¾å¦çæå¨ç级代ç ï¼1ã2ã3ã4ã5ï¼ï¼åæ¤å½æ°è¿åçç»æ表示æ±ä¸ççç·ç人æ°ï¼è¿æ¯ä¸ä¸ªæ°ç»å½æ°ï¼è¾å®åè¦æCtrlï¼Shiftï¼Enterç»åé®(产çâï½â¦â¦ï½â)ãâï½ï½âä¸è½æå·¥è¾å
¥ï¼åªè½ç¨ç»åé®äº§çã
ãã14ãæ ¹æ®åºçæ¥æèªå¨è®¡ç®å¨å²ï¼=TRUNC((DAYS360(D3,NOW( )))/360,0)
温馨提示:答案为网友推荐,仅供参考