é®é¢æ¼æ´å¤å¤åï¼åªè½å°½å¯è½å
¨é¨èèäºï¼
1ã å¤å¶Aåæ°æ®å°ä¸ä¸ªæ°è¡¨æ ¼ï¼ç¶åä½¿ç¨ æ°æ®==ãå é¤éå¤é¡¹ï¼å°±è·å¾äºäººåååï¼å¯ä»¥å°æ°çå§åæ°æ®å¤å¶å° Fåï¼å
¬å¼ä»¥æ¤æ°æ®å¶ä½ï¼
2ãF2åå®æ¯ç¬¬ä¸ä¸ªååï¼å¯ä»¥å¨ G2è¾å
¥ï¼
=SUMPRODUCT((A:A=F2)*(C:C=1)*(E:E="2ï¼é«é¾åº¦ï¼")*(D:D))/SUMPRODUCT((A:A=F2)*(C:C=1)*(E:E="2ï¼é«é¾åº¦ï¼")*1)
以ä¸å
¬å¼ä¸ c:c=1 æ¯å设ç»è®¡æ£ç¡®çæç®ï¼è¦ç®éçå¹³åæ¶é´å°±æä¸é¢ç C:C=1 æ¢æ C:C=0å³å¯ï¼ï¼å¦æä¸åºåæ¯å¦æ£ç¡®ï¼å¯ä»¥ï¼
=SUMPRODUCT((A:A=F2)*(E:E="2ï¼é«é¾åº¦ï¼")*(D:D))/SUMPRODUCT((A:A=F2)*(E:E="2ï¼é«é¾åº¦ï¼")*1)
å
¬å¼ä¸ E:E="2ï¼é«é¾åº¦ï¼" æ¯ç»è®¡ é«é¾åº¦çï¼æ¢æ E:E="1ï¼ä½é¾åº¦ï¼"
å°±å¯ä»¥ç»è®¡ä½é¾åº¦çäºï¼ä¸åºåé¾åº¦å¯ä»¥ ï¼
=SUMPRODUCT((A:A=F2)*(D:D))/SUMPRODUCT((A:A=F2)*1)
追é®é常æè°¢ä½ ï¼
ææç
§ä½ çæ¹æ³åäºç¬¬1æ¥ï¼å°ç¬¬2æ¥æ¶ï¼è¾å
¥è¿ä¸ªå
¬å¼å为ä»ä¹ä¼æ¥éå¢ï¼