gong

d3=SUMPRODUCT((LEFT($A$1:$A$30,5)=$C3&D$2)*1/COUNTIF($A$1:$A$30,$A$1:$A$30))

Hsieh

¦^´_ 1# oiggu
¿ï¨úd3Àx¦s®æ,¥[¤J©w¸q¦WºÙx
¤½¦¡=OFFSET(Sheet1!$A:$A,,,COUNTA(Sheet1!$A:$A),)

¤U¸ü (11.34 KB)

2010-7-24 17:02

d3°}¦C¤½¦¡
{=IF(ISNUMBER(MATCH($C3&D$2,LEFT(x,5),0)),SUM(1/COUNTIF(OFFSET($A$1,MATCH($C3&D$2,LEFT(x,5),0)-1,,COUNTIF(x,"="&$C3&D$2&"*")),OFFSET($A$1,MATCH($C3&D$2,LEFT(x,5),0)-1,,COUNTIF(x,"="&$C3&D$2&"*")))),0)}
¦V¤U¦V¥k½Æ»s

kimbal

¦bd3 ©ñ¤W³o­Ó¤½¦¡:

1. =SUMPRODUCT((MID($A$1:$A$30,2,4)=D$2&"")*(LEFT($A$1:$A$30,1)=$C3)*($A$1:$A$30<>"")/COUNTIF($A$1:$A$30,$A$1:$A$30))

½Æ»s¥N½X

µM«á§Û¨ì¨ä¥L®æ