Hsieh

¦U¦ì¥ý¶i¤j­ô¤Î¤j©j­Ì¡G
    ¦]¥Ø«e¤½¥q­n¨D¡A¨C¤ë»Ý¨Ì¬ÛÃö¥Í²£¸ê°T´£¨Ñ¥H¤U¸ê®Æ¡A¦]¤p©f§Ú¹ï©óExcel¨ç¼Æ»{ ...
q88007 µoªí©ó 2010-6-13 13:35

5¡B­Y©w¸q¨C¤ë§¡¦³¥X³f¡A¥B¤ë¥­§¡¥X³f¼Æ¶q¨ìn®É¡A«h©w¸q¸Ó«¬¦¡¬°AÃþ¡A¦³´Xµ§²Å¦X¡A¥B²Å¦X¤§«¬¦¡¦³­þ¨Ç??
6¡B­Y©w¸q¤ë¥­§¡¥X³f¼Æ¶q¨ìn®É¡A«h©w¸q¸Ó«¬¦¡¬°BÃþ¡A¦³´Xµ§²Å¦X¡A¥B²Å¦X¤§«¬¦¡¦³­þ¨Ç??
ªí¥ÜÃþ§O¤]¤w¸g¬O¦Û°ÊÂà¥X¶Ü?
«Øij§â³o¨Ç¸ê®Æ°µ¦¨ÀÉ®×,¨Ã¥Î¤â¤u§¹¦¨³o¨Ç¸ê®Æ¾ã²z«á·Q­nªº§Î¦¡«á¤W¶Ç
¤ñ¸û¯à°÷¤F¸Ñ§Aªº»Ý¨D

Hsieh

¦^´_ 6# q88007
¸Õ¸Õ¬Ý
¨C¤ë§¡¦³¥X³f¤ë¥­§¡¶W¹L100ªºAÃþ§O¼Æ¶q=SUMPRODUCT((TRIM($A$2:$A$1314)="A")*($Q$2:$Q$1314>100)*($S$2:$S$1314=12))
¨C¤ë§¡¦³¥X³f¤ë¥­§¡¶W¹L100ªºAÃþ§O²M³æ
U2°}¦C¤½¦¡
{=IF(ROW(A1)>SUMPRODUCT((TRIM($A$2:$A$1314)="A")*($Q$2:$Q$1314>100)*($S$2:$S$1314=12)),"",INDIRECT("B"&SMALL(IF((TRIM($A$2:$A$1314)="A")*($Q$2:$Q$1314>100)*($S$2:$S$1314=12),ROW($2:$1314),FALSE),ROW(A1))))}

Hsieh

¦^´_ 8# q88007


    Àɮפ¤¶À©³³¡¤À,¥HAÃþ§O¥Ü½d °ÝÃD1.rar (66.78 KB)

¤U¸ü¦¸¼Æ: 7

2010-6-22 22:16

Hsieh

¦^´_ 10# q88007
¦³¤@¼Ë¶Ü?
=SUMPRODUCT((TRIM($A$2:$A$1314)="A")*($Q$2:$Q$1314>100)*($S$1:$S$1314=12))
sumproductªº©Ò¦³°}¦C¤j¤p­n¬Û¦P

²M³æ¤½¦¡­n¥H°}¦C¤½¦¡¿é¤J
CTRL+SHIFT+ENTER¨Ï¤j¬A©·¦Û°Ê²£¥Í

Hsieh

¦^´_ 12# q88007


    {=IF(ROW(A1)>SUMPRODUCT(($Q$2:$Q$1314>100)*($S$2:$S$1314=12)),"",INDIRECT("B"&SMALL(IF(($Q$2:$Q$1314>100)*($S$2:$S$1314=12),ROW($2:$1314),FALSE),ROW(A1))))}
±ø¥ó§R°£´N¦n¤F

Hsieh

¦^´_ 16# q88007


    {=IF(ROW(A1)>SUMPRODUCT(($Q$2:$Q$1314<249)*($Q$2:$Q$1314>100)*($S$2:$S$1314=12)),"",INDIRECT("B"&SMALL(IF(($Q$2:$Q$1314<249)*($Q$2:$Q$1314>100)*($S$2:$S$1314=12),ROW($2:$1314),FALSE),ROW(A1))))}