å°é¼ æ æ¾å¨åå
æ ¼å³ä¸è§ï¼å½é¼ æ åæååå½¢ç¶æ¶åæä½å·¦é®ï¼å¾ä¸æå³å¯å¤å¶å½ååå
æ ¼
å
¬å¼è³ä¸æ¹ï¼å¾å·¦å¾å³æä¹è½å¤å¶å½ååå
æ ¼å
¬å¼è³å·¦åæå³è¾¹åå
æ ¼ã
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)ââ对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ååå«åæ¾çå¦ççâå¹³æ¶æ»è¯âãâæä¸âãâææ«âä¸é¡¹æ绩ï¼
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()))
å°å
¬å¼åä¸å¡«å
ï¼ç´å°åºé为æ¢ï¼ç®å½å°±çæäºã