中国式排名函数:SUMPRODUCT+COUNTIF输入公式:=SUMPRODUCT((C$3:C$9>C3) COUNTIF(C$3:C$9,C$3:C$9))+1解读:公式的前半部分(C$3:C$9>C3)返
ä¸å½å¼æå
å½æ°ï¼SUMPRODUCT+COUNTIF
è¾å ¥å ¬å¼ï¼=SUMPRODUCT((C$3:C$9>C3)/COUNTIF(C$3:C$9,C$3:C$9))+1
解读ï¼å ¬å¼çååé¨å(C$3:C$9>C3)è¿åçæ¯ä¸ä¸ªæ°ç»ï¼åºåC$3:C$9ä¸å¤§äºC3çåå æ ¼ä¸ªæ°ãååé¨åCOUNTIF(C$3:C$9,C$3:C$9)å¯ä»¥ç解为ï¼*1/COUNTIF(C$3:C$9,C$3:C$9),å ¬å¼COUNTIF(C$3:C$9,C$3:C$9)è¿åçå¼ä¸º1ï¼åªæ¯ç¨äºè¾ å©è®¡ç®ãæ以ä¸è¿°å ¬å¼ä¹å¯ä»¥ç®å为ï¼=SUMPRODUCT((C$3:C$9>C3)*1)+1ã
å½æ°ï¼TEXT+MIDå½æ°
è¾å ¥å ¬å¼ï¼=TEXT(MID(C3,7,8),"00-00-00")
解读ï¼
1ãå©ç¨MIDå½æ°ä»C3åå æ ¼ä¸æåä»ç¬¬7个å¼å§ï¼é¿åº¦ä¸º8çå符串ã
2ãå©ç¨TEXTå½æ°å°å符çæ ¼å¼è½¬æ¢ä¸º“00-00-00”çæ ¼å¼ã
å½æ°ï¼COUNTIFãCOUNTIFS
è¾å ¥å ¬å¼ï¼=COUNTIF(D3:D9,"ç·")æ=COUNTIFS(D3:D9,"ç·",C3:C9,">=80")
解读ï¼
1ãCOUNTIFå½æ°ç¨äºåæ¡ä»¶è®¡æ°ï¼æ¨è®¡æ°æ¡ä»¶åªè½æä¸ä¸ªãæ解è¯æ³ç»æ为ï¼COUNTIF(æ¡ä»¶èå´ï¼æ¡ä»¶).
2ãCOUNTIFSå½æ°ç¨äºå¤æ¡ä»¶è®¡æ°ï¼æ¨è®¡æ°æ¡ä»¶å¯ä»¥æå¤ä¸ªãæ解è¯æ³ç»æ为ï¼COUNTIFSï¼æ¡ä»¶èå´1ï¼æ¡ä»¶1ï¼æ¡ä»¶èå´2ï¼æ¡ä»¶2……æ¡ä»¶èå´Nï¼æ¡ä»¶Nï¼ã
æ¡ä»¶æ±å
å½æ°ï¼SUMIFãSUMIFS
è¾å ¥æ¹æ³ï¼=SUMIF(D3:D9,"ç·",C3:C9)æ=SUMIFS(C3:C9,C3:C9,">=80",D3:D9,"ç·")
解读ï¼
1ãSUMIFå½æ°ç¨äºåæ¡ä»¶æ±åãæ¨æ±åæ¡ä»¶åªè½æä¸ä¸ªãæ解è¯æ³ç»æ为ï¼SUMIFï¼æ¡ä»¶èå´ï¼æ¡ä»¶ï¼æ±åèå´ï¼ã
2ãSUMIFSå½æ°ç¨äºå¤æ¡ä»¶æ±åãæ¨æ±åæ¡ä»¶å¯ä»¥æå¤ä¸ªãæ解è¯æ³ç»æï¼SUMIFSï¼æ±åèå´ï¼æ¡ä»¶1èå´ï¼æ¡ä»¶1ï¼æ¡ä»¶2èå´ï¼æ¡ä»¶2,……æ¡ä»¶Nèå´ï¼æ¡ä»¶Nï¼ã
æ¡ä»¶å¤æ
å½æ°ï¼IF
è¾å ¥æ¹æ³ï¼=IF(C3>=90,"ä¼ç§",IF(C3>=80,"è¯å¥½",IF(C3>=60,"åæ ¼","ä¸åæ ¼")))
解读ï¼
IFå½æ°æ¯æ¡ä»¶å¤æå½æ°ï¼æ ¹æ®å¤æç»æè¿å对åºçå¼ï¼å¦æå¤ææ¡ä»¶ä¸ºTRUEï¼åè¿å第ä¸ä¸ªåæ°ï¼å¦æ为FALSEï¼åè¿å第äºä¸ªåæ°ã
æ°æ®æ¥è¯¢
å½æ°ï¼VLOOKUP
è¾å ¥æ¹æ³ï¼=VLOOKUP(H3,B3:C9,2,0)
解读ï¼
å½æ°VLOOKUPçåºæ¬åè½å°±æ¯æ°æ®æ¥è¯¢ãæ解è¯æ³ç»æ为ï¼VLOOKUPï¼æ¥æ¾çå¼ï¼æ¥æ¾èå´ï¼æ¾æ¥æ¾èå´ä¸ç第å åï¼ç²¾åå¹é è¿æ¯æ¨¡ç³å¹é ï¼ã
æ¥è¯¢å¥½ææ¡£
å½æ°ï¼INDEX+MATCH
è¾å ¥æ¹æ³ï¼=INDEX(E3:E9,MATCH(H3,B3:B9,0))
解读ï¼
1ãINDEXå½æ°ï¼è¿åç»å®èå´å è¡å交åå¤çå¼ã
2ãMATCHå½æ°ï¼ç»åºæå®å¼å¨æå®èå´å çæå¨ä½ç½®ã
3ãå ¬å¼ï¼=INDEX(E3:E9,MATCH(H3,B3:B9,0))ï¼æ¥è¯¢E3:E9ä¸ç¬¬MATCH(H3,B3:B9,0)è¡çå¼ï¼å¹¶è¿åã
计ç®å¹´é¾
å½æ°ï¼DATEDIF
è¾å ¥å ¬å¼ï¼=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")&"å¨å²"ã
解读ï¼
1ãå©ç¨MIDè·åC3åå æ ¼ä¸ä»ç¬¬7个å¼å§ï¼é¿åº¦ä¸º8çå符串ã
2ãç¨Textå½æ°å°å符串转æ¢ä¸ºï¼00-00-00çæ ¼å¼ãæ¨1965-08-21ã
3ãå©ç¨DATEDIFå½æ°è®¡ç®åºåå½åæ¥æï¼TODAYï¼ï¼ï¼çç¸å·®å¹´ä»½ï¼yï¼ã