å¨è¡¨æ ¼éè¾å
¥=ï¼B1-A1ï¼
å¨B1åå
æ ¼æ¾å
¥è¡ä»· A1æ¾å
¥ææ¬ä»·
èªå¨ç®åºæ¥
Excel常ç¨çµåè¡¨æ ¼å
¬å¼å¤§å
¨ 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) âââå设Dååæ¾å¦ççåºçæ¥æï¼Eåè¾å
¥è¯¥å½æ°åå产ç该ççå¨å²ã 15ãå¨Wordä¸ä¸ä¸ªå°çªé¨ï¼ â è¿ç»è¾å
¥ä¸ä¸ªâ~âå¯å¾ä¸æ¡æ³¢æµªçº¿ã â¡è¿ç»è¾å
¥ä¸ä¸ªâ-âå¯å¾ä¸æ¡ç´çº¿ã è¿ç»è¾å
¥ä¸ä¸ªâ=âå¯å¾ä¸æ¡åç´çº¿ã ä¸ãexcelä¸å½æä¸åå
æ ¼ç¬¦åç¹å®æ¡ä»¶ï¼å¦ä½å¨å¦ä¸åå
æ ¼æ¾ç¤ºç¹å®çé¢è²æ¯å¦ï¼ A1ã1æ¶ï¼C1æ¾ç¤ºçº¢è² 0<A1<1æ¶ï¼C1æ¾ç¤ºç»¿è² A1<0æ¶ï¼C1æ¾ç¤ºé»è² æ¹æ³å¦ä¸ï¼ 1ãåå
å»C1åå
æ ¼ï¼ç¹âæ ¼å¼â>âæ¡ä»¶æ ¼å¼âï¼æ¡ä»¶1è®¾ä¸ºï¼ å
¬å¼ =A1=1 2ãç¹âæ ¼å¼â->âåä½â->âé¢è²âï¼ç¹å»çº¢è²åç¹âç¡®å®âã æ¡ä»¶2è®¾ä¸ºï¼ å
¬å¼ =AND(A1>0,A1<1) 3ãç¹âæ ¼å¼â->âåä½â->âé¢è²âï¼ç¹å»ç»¿è²åç¹âç¡®å®âã æ¡ä»¶3è®¾ä¸ºï¼ å
¬å¼ =A1<0 ç¹âæ ¼å¼â->âåä½â->âé¢è²âï¼ç¹å»é»è²åç¹âç¡®å®âã 4ãä¸ä¸ªæ¡ä»¶è®¾å®å¥½åï¼ç¹âç¡®å®âå³åºã äºãEXCELä¸å¦ä½æ§å¶æ¯åæ°æ®çé¿åº¦å¹¶é¿å
éå¤å½å
¥ 1ãç¨æ°æ®æææ§å®ä¹æ°æ®é¿åº¦ã ç¨é¼ æ éå®ä½ è¦è¾å
¥çæ°æ®èå´ï¼ç¹"æ°æ®"->"æææ§"->"设置"ï¼"æææ§æ¡ä»¶"设æ"å
许""ææ¬é¿åº¦""çäº""5"ï¼å
·ä½æ¡ä»¶å¯æ ¹æ®ä½ çéè¦æ¹åï¼ã è¿å¯ä»¥å®ä¹ä¸äºæ示信æ¯ãåºéè¦åä¿¡æ¯åæ¯å¦æå¼ä¸æè¾å
¥æ³çï¼å®ä¹å¥½åç¹"ç¡®å®"ã 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åæ¯âè¯ææ绩âï¼Dåæ¯âå½åç»æâï¼ç°å¨è¦ç»è®¡âç级â为âäºâï¼âè¯ææ绩â大äºçäº104ï¼âå½åç»æâ为âéæ¬âç人æ°ãç»è®¡ç»æåæ¾å¨æ¬å·¥ä½è¡¨çå
¶ä»åã å
¬å¼å¦ä¸ï¼ =SUM(IF((B2:B9999="äº")*(C2:C9999>=104)*(D2:D9999="éæ¬"),1,0)) è¾å
¥å®å
¬å¼åæCtrl+Shift+Enteré®,让å®èªå¨å ä¸æ°ç»å
¬å¼ç¬¦å·"{}"ã å
ãå¦ä½å¤æåå
æ ¼éæ¯å¦å
å«æå®ææ¬ï¼ åå®å¯¹A1åå
æ ¼è¿è¡å¤æææ "æå®ææ¬",以ä¸ä»»ä¸å
¬å¼åå¯: =IF(COUNTIF(A1,"*"&"æå®ææ¬"&"*")=1,"æ","æ ") =IF(ISERROR(FIND("æå®ææ¬",A1,1)),"æ ","æ") æ±æä¸åºåå
ä¸éå¤çæ°æ®ä¸ªæ° ä¾å¦æ±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("]",GET.WORKBOOK(1))+1,100) ç¡®å® 2ãç¨HYPERLINKå½æ°æ¹éæå
¥è¿æ¥ï¼æ¹æ³ï¼ å¨ç®å½å·¥ä½è¡¨ï¼ä¸è¬ä¸ºç¬¬ä¸ä¸ªsheetï¼çA2åå
æ ¼è¾å
¥å
¬å¼ï¼ =HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW())) å°å
¬å¼åä¸å¡«å
ï¼ç´å°åºé为æ¢ï¼ç®å½å°±çæäºã
温馨提示:答案为网友推荐,仅供参考