[µo°Ý] ­pºâ¥X²{¦¸¼Æ(¤£§t0)

PJChen

½Ð±Ð¥ý¶i:
-AÄ檺¼Æ­È·|¦³ÅܤÆ(¼W¥[ or ´î¤Ö)
-BÄæ­pºâAÄæ¼Æ¦r¥X²{ªº¦¸¼Æ(¹J0«h0,¤£­pºâ)
-°£¤F¥Îcountif¥~,¦³µL¨ä¥L¨ç¼Æ¥i¨ú¥N¨Ï¥Î?

­pºâ¥X²{¦¸¼Æ(¤£§t0).zip (8.02 KB)

¤U¸ü¦¸¼Æ: 6

2013-5-1 08:50

sunnyso

¦^´_ 1# PJChen

=IF(A2=0,0,SUMPRODUCT((OFFSET($A$2,,,COUNTA(A:A)-1,)=A2)*1))

PJChen

¦^´_ 2# sunnyso
½Ð°Ý³o­Ó¤½¦¡,¥u­nAÄ榳¥ô¤@ªÅ®æ®É,«K·|¦³¿ù»~­È?

sunnyso

¦^´_ 3# PJChen

¤°»ò¿ù»~­È? ¦³¹Ï¶Ü?

ªÅ®æ­Y­n·í0, ¥i¦bif ¤¤±Æ°£

PJChen

¦^´_ 4# sunnyso
²{A52¬°ªÅ®æ,E53:E57«K¥X²{¿ù»~,­YAÄ榳ªÅ®æ¥X²{®É,¯à§_¹F¦¨¥H¤U?
1) ¨ÏEÄæ ¹JªÅ®æ=ªÅ®æ
2) ¨ÏEÄæ ¹J0=0

1. E53=IF(A53="","",IF(A53=0,0,SUMPRODUCT((OFFSET($A$2,,,COUNTA(A:A)-1,)=A53)*1)))

½Æ»s¥N½X

¤U¸ü (19.28 KB)

2013-5-1 13:30

sunnyso

¦^´_ 5# PJChen

¤W­Óªþ¥ó¨Ó¬Ý¬Ý

sunnyso

¦^´_ 5# PJChen

§â°ÊºA½d³ò¥h±¼
=IF(A2="","",IF(A2=0,0,SUMPRODUCT(($A$2:$A$1000=A2)*1)))

sunnyso

¦^´_ 5# PJChen

I1 = MAX(IF(A:A<>"", ROW(A:A),"")) (Ctrl+Shift+Enter)

=IF(A2="","",IF(A2=0,0,SUMPRODUCT((OFFSET($A$2,,,$I$1-1,)=A2)*1)))

PJChen

¦^´_ 7# sunnyso

ÁÂÁ§Aµ¹§Ú³\¦hªº«Øij,7¼Óªº¤½¦¡¬O¥¿½Tªº.

­pºâ¥X²{¦¸¼Æ(¤£§t0).zip (12.06 KB)

¤U¸ü¦¸¼Æ: 5

2013-5-1 19:03