Excelè¡¨æ ¼æå并åæ
åµçåæï¼
ä¸å½å¼æåï¼å
¶å®å°±æ¯å¯¹ä¸ä¸ªæ°å¨ä¸ç»æ°æ®ä¸æå第å çç»è®¡ï¼éå¤æ°æåç¸åï¼æ¯å®å°çæ大æ°çæååªä½ä¸çº§ãé常å¯ä»¥ä½¿ç¨countifï¼frequencyåSUMPRODUCTçå½æ°ç»åå®ç°ã
ããä¸é¢æä¾å ç§ç¸å
³çä¸å½å¼æå解æ³ï¼
ããä¸å½å¼æå解æ³ä¸ï¼
ããå¨C2åå
æ ¼è¾å
¥å
¬å¼ï¼=SUM(IF($B$2:$B$6>B2,1/COUNTIF($B$2:$B$6,$B$2:$B$6)))+1ï¼ç¶åæctrl+shift+enterä¸é®ç»æãä¸æå¤å¶å
¬å¼å³å¯å¾åºå
¶ä½çæåã
ããå
¬å¼è§£éï¼
ããCOUNTIF($B$2:$B$6,$B$2:$B$6)é¨åï¼è¿æ¯ä¸ä¸ªæ°ç»è¿ç®ç¨æ³ï¼å®çè¿ç®è¿ç¨æ¯ï¼
ããCOUNTIF($B$2:$B$6,B2)
ããCOUNTIF($B$2:$B$6,B3)
ããCOUNTIF($B$2:$B$6,B4)
ããâ¦â¦
ããåå«ç»è®¡B2ãB3ãB4åå
æ ¼å¨B2ï¼B6åºåä¸åºç°ç次æ°ãå¾å°ç»æ为ï¼1,1,1,2,2ãå
¶ä¸â1â代表æ¤åå
æ ¼ä¸çå
容å¨B2ï¼B6åºåä¸åªåºç°ä¸æ¬¡ï¼å³æ²¡æéå¤ï¼â2â代表æ¤åå
æ ¼ä¸çå
容å¨B2ï¼B6åºåééå¤2次ãè¿ä¸æ¥çæä½ï¼å¯ä»¥å¾å°æ°æ®æ¯å¦æéå¤å以åéå¤ç次æ°ã
ãã1/COUNTIF($B$2:$B$6,$B$2:$B$6)é¨åï¼
ããå¨å
¬å¼ç¼è¾æ éä¸è¿é¨åå
¬å¼ï¼æF9é®æ¥çè¿ç®ç»æ为ï¼{1;1;1;0.5;0.5}ã
ããIF($B$2:$B$6>B2,â¦â¦)é¨åï¼
ããIF第ä¸åæ°ï¼$B$2:$B$6>B2çç»ææ¯ï¼{FALSE;TRUE;FALSE;TRUE;TRUE}ï¼æææ¯B2åå
æ ¼ä¸çå
容åå«åB2ï¼B6åºåå
çå个åå
æ ¼å
容è¿è¡å¤§å°æ¯è¾ã
ããâIF($B$2:$B$6>B2,1/COUNTIF($B$2:$B$6,$B$2:$B$6))âï¼è¿éIFçç¥äºç¬¬ä¸åæ°ï¼å æ¤å½å¾å°FALSEæ¶ï¼æ¤æ¶å°è¿åç»æâFALSEâï¼å½å¾å°TRUEæ¶ï¼æ¤æ¶å°è¿å对åºçç»æï¼å¾å°çç»ææ¯{FALSE;1;FALSE;0.5;0.5}ã
ããæ¥çSUMå½æ°å¯¹IFå½æ°å
çç»æè¿è¡å æ»ï¼å¾å°ç»æâ3âã为ä»ä¹è¿è¦åå ä¸â1âå¢ï¼åå æ¯IFå½æ°å
çæµè¯æ¡ä»¶æ¯â>âï¼å¯¹äºâB2ï¼B6åºåâéçæ大å¼â72âèè¨ï¼å¾å°çç»ææ¯{FALSE;FALSE;FALSE;FALSE;FALSE}ï¼é£ä¹SUMå½æ°è®¡ç®å¾å°çå¼å°±æ¯â0âï¼æ¾ç¶æå第0ä½ï¼ä¸ç¬¦å常è¯ï¼å æ¤è¦é¢å¤å ä¸â1âã
ããè¿ä¸ªä¸å½å¼æåå
¬å¼çæ ¸å¿é¨åå°±æ¯ï¼â1/COUNTIF($B$2:$B$6,$B$2:$B$6)âï¼ç®çæ¯é¿å
éå¤è®¡ç®ç¸å项ã
ããä¸å½å¼æå解æ³äºï¼
ããC2åå
æ ¼è¾å
¥å
¬å¼ï¼=SUMPRODUCT(($B$2:$B$6>=B2)/COUNTIF($B$2:$B$6,$B$2:$B$6))ï¼ä¸æå¤å¶å³å¯ã
ããè¿ä¸ªå
¬å¼çæè·¯ä¹æ¯ä¸éå¤è®¡æ°ãå
·ä½çå
¬å¼è§£éï¼è¯·åçæç« ï¼
http://www.ittribalwo.com/show.asp?id=1257ããä¸å½å¼æå解æ³ä¸ï¼
ãã使ç¨FREQUENCY å½æ°çæ°ç»è§£æ³å®æãB2å
¬å¼ä¸ºï¼=SUM(--(FREQUENCY(B$2:B$6,IF(B$2:B$6>=B2,B$2:B$6))>0))ï¼ä¸é®ç»æã
ããä¸å½å¼æå解æ³åï¼
ãã=SUMPRODUCT((B$2:B$6>B2)*(1/(COUNTIF(B$2:B$6,B$2:B$6))))+1
ããä¸å½å¼æå解æ³äºï¼
ãã=SUM(--IF($B$2:$B$6>=B2,MATCH($B$2:$B$6,$B$2:$B$6,)=ROW($2:$6)-1))