1ãABSå½æ° ãã
å½æ°å称ï¼ABS ãã
主è¦åè½ï¼æ±åºç¸åºæ°åçç»å¯¹å¼ã ãã
使ç¨æ ¼å¼ï¼ABS(number) ãã
åæ°è¯´æï¼number代表éè¦æ±ç»å¯¹å¼çæ°å¼æå¼ç¨çåå
æ ¼ã ãã
åºç¨ä¸¾ä¾ï¼å¦æå¨B2åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=ABS(A2)ï¼åå¨A2åå
æ ¼ä¸æ 论è¾å
¥æ£æ°ï¼å¦100ï¼è¿æ¯è´æ°ï¼å¦-100ï¼ï¼B2ä¸åæ¾ç¤ºåºæ£æ°ï¼å¦100ï¼ã ãã
ç¹å«æéï¼å¦ænumberåæ°ä¸æ¯æ°å¼ï¼èæ¯ä¸äºå符ï¼å¦Açï¼ï¼åB2ä¸è¿åé误å¼â#VALUEï¼âããã
ãã
2ãANDå½æ° ãã
å½æ°å称ï¼AND ãã
主è¦åè½ï¼è¿åé»è¾å¼ï¼å¦æææåæ°å¼å为é»è¾âçï¼TRUEï¼âï¼åè¿åé»è¾âçï¼TRUEï¼âï¼åä¹è¿åé»è¾âåï¼FALSEï¼âã ãã
使ç¨æ ¼å¼ï¼AND(logical1,logical2, ...) ãã
åæ°è¯´æï¼Logical1,Logical2,Logical3â¦â¦ï¼è¡¨ç¤ºå¾
æµè¯çæ¡ä»¶å¼æ表达å¼ï¼æå¤è¿30个ããã
åºç¨ä¸¾ä¾ï¼å¨C5åå
æ ¼è¾å
¥å
¬å¼ï¼=AND(A5>=60,B5>=60)ï¼ç¡®è®¤ãå¦æC5ä¸è¿åTRUEï¼è¯´æA5åB5ä¸çæ°å¼å大äºçäº60ï¼å¦æè¿åFALSEï¼è¯´æA5åB5ä¸çæ°å¼è³å°æä¸ä¸ªå°äº60ã ãã
ç¹å«æéï¼å¦ææå®çé»è¾æ¡ä»¶åæ°ä¸å
å«éé»è¾å¼æ¶ï¼åå½æ°è¿åé误å¼â#VALUE!âæâ#NAMEâããã
ãã
3ãAVERAGEå½æ° ãã
å½æ°å称ï¼AVERAGE ãã
主è¦åè½ï¼æ±åºææåæ°çç®æ¯å¹³åå¼ã ãã
使ç¨æ ¼å¼ï¼AVERAGE(number1,number2,â¦â¦) ãã
åæ°è¯´æï¼number1,number2,â¦â¦ï¼éè¦æ±å¹³åå¼çæ°å¼æå¼ç¨åå
æ ¼ï¼åºåï¼ï¼åæ°ä¸è¶
è¿30个ã ãã
åºç¨ä¸¾ä¾ï¼å¨B8åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=AVERAGE(B7:D7,F7:H7,7,8)ï¼ç¡®è®¤åï¼å³å¯æ±åºB7è³D7åºåãF7è³H7åºåä¸çæ°å¼å7ã8çå¹³åå¼ã ãã
ç¹å«æéï¼å¦æå¼ç¨åºåä¸å
å«â0âå¼åå
æ ¼ï¼å计ç®å¨å
ï¼å¦æå¼ç¨åºåä¸å
å«ç©ºç½æå符åå
æ ¼ï¼åä¸è®¡ç®å¨å
ããã
ãã
4ãCOLUMN å½æ° ãã
å½æ°å称ï¼COLUMN ãã
主è¦åè½ï¼æ¾ç¤ºæå¼ç¨åå
æ ¼çåæ å·å¼ã ãã
使ç¨æ ¼å¼ï¼COLUMN(reference) ãã
åæ°è¯´æï¼reference为å¼ç¨çåå
æ ¼ã ãã
åºç¨ä¸¾ä¾ï¼å¨C11åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=COLUMN(B11)ï¼ç¡®è®¤åæ¾ç¤ºä¸º2ï¼å³Båï¼ã ãã
ç¹å«æéï¼å¦æå¨B11åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=COLUMN()ï¼ä¹æ¾ç¤ºåº2ï¼ä¸ä¹ç¸å¯¹åºçè¿æä¸ä¸ªè¿åè¡æ å·å¼çå½æ°ââROW(reference)ããã
ãã
5ãCONCATENATEå½æ° ãã
å½æ°å称ï¼CONCATENATE ãã
主è¦åè½ï¼å°å¤ä¸ªå符ææ¬æåå
æ ¼ä¸çæ°æ®è¿æ¥å¨ä¸èµ·ï¼æ¾ç¤ºå¨ä¸ä¸ªåå
æ ¼ä¸ã ãã
使ç¨æ ¼å¼ï¼CONCATENATE(Text1ï¼Textâ¦â¦) ãã
åæ°è¯´æï¼Text1ãText2â¦â¦ä¸ºéè¦è¿æ¥çå符ææ¬æå¼ç¨çåå
æ ¼ã ãã
åºç¨ä¸¾ä¾ï¼å¨C14åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=CONCATENATE(A14,"@",B14,".com")ï¼ç¡®è®¤åï¼å³å¯å°A14åå
æ ¼ä¸å符ã@ãB14åå
æ ¼ä¸çå符å.comè¿æ¥æä¸ä¸ªæ´ä½ï¼æ¾ç¤ºå¨C14åå
æ ¼ä¸ã ãã
ç¹å«æéï¼å¦æåæ°ä¸æ¯å¼ç¨çåå
æ ¼ï¼ä¸ä¸ºææ¬æ ¼å¼çï¼è¯·ç»åæ°å ä¸è±æç¶æä¸çåå¼å·ï¼å¦æå°ä¸è¿°å
¬å¼æ¹ä¸ºï¼=A14&"@"&B14&".com"ï¼ä¹è½è¾¾å°ç¸åçç®çããã
ãã
6ãCOUNTIFå½æ° ãã
å½æ°å称ï¼COUNTIF ãã
主è¦åè½ï¼ç»è®¡æ个åå
æ ¼åºåä¸ç¬¦åæå®æ¡ä»¶çåå
æ ¼æ°ç®ã ãã
使ç¨æ ¼å¼ï¼COUNTIF(Range,Criteria) ãã
åæ°è¯´æï¼Range代表è¦ç»è®¡çåå
æ ¼åºåï¼Criteria表示æå®çæ¡ä»¶è¡¨è¾¾å¼ããã ãã
åºç¨ä¸¾ä¾ï¼å¨C17åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=COUNTIF(B1:B13,">=80")ï¼ç¡®è®¤åï¼å³å¯ç»è®¡åºB1è³B13åå
æ ¼åºåä¸ï¼æ°å¼å¤§äºçäº80çåå
æ ¼æ°ç®ããã
ç¹å«æéï¼å
许å¼ç¨çåå
æ ¼åºåä¸æ空ç½åå
æ ¼åºç°ããã
ãã
7ãDATEå½æ° ãã
å½æ°å称ï¼DATE ãã
主è¦åè½ï¼ç»åºæå®æ°å¼çæ¥æã ãã
使ç¨æ ¼å¼ï¼DATE(year,month,day) ãã
åæ°è¯´æï¼year为æå®ç年份æ°å¼ï¼å°äº9999ï¼ï¼month为æå®çæ份æ°å¼ï¼å¯ä»¥å¤§äº12ï¼ï¼day为æå®ç天æ°ã ãã
åºç¨ä¸¾ä¾ï¼å¨C20åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=DATE(2003,13,35)ï¼ç¡®è®¤åï¼æ¾ç¤ºåº2004-2-4ã ãã
ç¹å«æéï¼ç±äºä¸è¿°å
¬å¼ä¸ï¼æ份为13ï¼å¤äºä¸ä¸ªæï¼é¡ºå»¶è³2004å¹´1æï¼å¤©æ°ä¸º35ï¼æ¯2004å¹´1æçå®é
天æ°åå¤äº4天ï¼æ
å顺延è³2004å¹´2æ4æ¥ããã
ãã
8ãå½æ°å称ï¼DATEDIFãã
主è¦åè½ï¼è®¡ç®è¿å两个æ¥æåæ°çå·®å¼ããã
使ç¨æ ¼å¼ï¼=DATEDIF(date1,date2,"y")ã=DATEDIF(date1,date2,"m")ã=DATEDIF(date1,date2,"d")ãã
åæ°è¯´æï¼date1代表åé¢ä¸ä¸ªæ¥æï¼date2代表åé¢ä¸ä¸ªæ¥æï¼yï¼mãdï¼è¦æ±è¿å两个æ¥æç¸å·®çå¹´ï¼æã天ï¼æ°ããã
åºç¨ä¸¾ä¾ï¼å¨C23åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=DATEDIF(A23,TODAY(),"y")ï¼ç¡®è®¤åè¿åç³»ç»å½åæ¥æ[ç¨TODAY()表示ï¼ä¸A23åå
æ ¼ä¸æ¥æçå·®å¼ï¼å¹¶è¿åç¸å·®çå¹´æ°ããã
ç¹å«æéï¼è¿æ¯Excelä¸çä¸ä¸ªéèå½æ°ï¼å¨å½æ°å导ä¸æ¯æ¾ä¸å°çï¼å¯ä»¥ç´æ¥è¾å
¥ä½¿ç¨ï¼å¯¹äºè®¡ç®å¹´é¾ãå·¥é¾çé常ææããã
ãã
9ãDAYå½æ°ãã
å½æ°å称ï¼DAYãã
主è¦åè½ï¼æ±åºæå®æ¥ææå¼ç¨åå
æ ¼ä¸çæ¥æç天æ°ããã
使ç¨æ ¼å¼ï¼DAY(serial_number)ãã
åæ°è¯´æï¼serial_number代表æå®çæ¥ææå¼ç¨çåå
æ ¼ããã
åºç¨ä¸¾ä¾ï¼è¾å
¥å
¬å¼ï¼=DAY("2003-12-18")ï¼ç¡®è®¤åï¼æ¾ç¤ºåº18ããã
ç¹å«æéï¼å¦ææ¯ç»å®çæ¥æï¼è¯·å
å«å¨è±æåå¼å·ä¸ããã
ãã
10ãDCOUNTå½æ°ãã
å½æ°å称ï¼DCOUNTãã
主è¦åè½ï¼è¿åæ°æ®åºæå表çåä¸æ»¡è¶³æå®æ¡ä»¶å¹¶ä¸å
å«æ°åçåå
æ ¼æ°ç®ããã
使ç¨æ ¼å¼ï¼DCOUNT(database,field,criteria)ãã
åæ°è¯´æï¼Database表示éè¦ç»è®¡çåå
æ ¼åºåï¼Field表示å½æ°æ使ç¨çæ°æ®åï¼å¨ç¬¬ä¸è¡å¿
é¡»è¦ææ å¿é¡¹ï¼ï¼Criteriaå
å«æ¡ä»¶çåå
æ ¼åºåããã
åºç¨ä¸¾ä¾ï¼å¦å¾1æ示ï¼å¨F4åå
æ ¼ä¸è¾å
¥å
¬å¼ï¼=DCOUNT(A1:D11,"è¯æ",F1:G2)ï¼ç¡®è®¤åå³å¯æ±åºâè¯æâåä¸ï¼æ绩大äºçäº70ï¼èå°äº80çæ°å¼åå
æ ¼æ°ç®ï¼ç¸å½äºåæ°æ®µäººæ°ï¼ã
ã ã
ãã
ç¹å«æéï¼å¦æå°ä¸è¿°å
¬å¼ä¿®æ¹ä¸ºï¼=DCOUNT(A1:D11,,F1:G2)ï¼ä¹å¯ä»¥è¾¾å°ç¸åç®çããã
温馨提示:答案为网友推荐,仅供参考