å¯ä»¥ç¨å ¬å¼è§£å³ã以ä¸å¾ä¸çå§å为ä¾ï¼
D2è¾å ¥å ¬å¼
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
ç»æ为3ï¼æ¢ä¸éå¤æ°ä¸º3个ï¼å¦ä¸å¾
å ¬å¼è§£æï¼
COUNTIF(A2:A8,A2:A8)æ¯ä¸ªæ°ç»è®¡ç®çä¾åï¼ç»æ为ä¸ä¸ªæ°ç»ï¼å°±æ¯A2:A8æ¯ä¸ªå§ååºç°ç次æ°ï¼
COUNTIF(A2:A8,A2)
COUNTIF(A2:A8,A3)
â¦â¦
COUNTIF(A2:A8,A8)
å¨B2è¾å ¥å ¬å¼
=COUNTIF(A$2:A$8,A2)
å ¬å¼ä¸æå¤å¶ï¼ç»æå°±æ¯COUNTIF(A2:A8,A2:A8)ç计ç®ç»æï¼ä¾æ¬¡è®¡ç®æ¯ä¸ªå§åçåºç°æ¬¡æ°ã
å¦ä¸å¾ï¼
åç¨1/åºç°æ¬¡æ°ï¼å¦å¼ ä¸åºç°2次ï¼åæ¯æ¬¡è®¡ç®1/2次ï¼æ±åå¾1ï¼æååºç°3次ï¼æ¯æ¬¡è®¡ç®1/3次ï¼æ±åå¾1ï¼ä¾æ¤ç±»æ¨ï¼å¾å°ä¸éå¤å§åç个æ°ã
ç¸å½äºå¨C2è¾å ¥
=1/B2
å ¬å¼ä¸æåæ±åçç»æï¼å¦ä¸å¾ï¼
以ä¸æ¯å¯¹å ¬å¼=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))çæå解æï¼æåçæ±åä¹æ以ç¨SUMPRODUCTï¼èä¸æ¯ä½¿ç¨SUMï¼æ¯å 为è¿æ¯æ°ç»è®¡ç®ï¼æ°ç»å ¬å¼å¿ é¡»æCtrl+Shift+Enterä¸é®ç¡®è®¤è¾å ¥ï¼è使ç¨SUMPRODUCTå½æ°ååªéæEnterå°±å¯ä»¥äºï¼å½ç¶ä¹å¯ä»¥æå ¬å¼æ¹ä¸ºï¼
=SUM(1/COUNTIF(A2:A8,A2:A8))
åæCtrl+Shift+Enterä¸é®ç¡®è®¤è¾å ¥ï¼Excelä¼èªå¨å¨å ¬å¼å¤é¢åµå¥ä¸å¯¹å¤§æ¬å·ï¼è¡¨ç¤ºæ°ç»å ¬å¼ï¼
{=SUM(1/COUNTIF(A2:A8,A2:A8))}
å¦ä¸å¾æ示ï¼