ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¥uÅã¥Ü¨S¦³­«ÂЪº¸ê®Æ

¥uÅã¥Ü¨S¦³­«ÂЪº¸ê®Æ

¦U¦ì°ª¤â:
¦³¨âÄæ¸ê®Æ, ¦p¦ó±N¨âÄæ¸ê®Æ¦X¨Ö«á
¦A¥uÅã¥Ü¨S¦³­«ÂЪº¸ê®Æ
§Ú²{®É»Ý­n¥[¤W¤@Äæ¥ý±N¸ê®Æ¦X¨Ö,
¦A¥Îcountif¥hÅã¥Ü, ¦ý¥i¥H¦³§ó²¤èªk¶Ü
½Ð«ü±Ð:

¸ê®Æ1        ¸ê®Æ2        Åã¥Ü¨S¦³­«Âиê®Æ
1        a        1a
1        a       
1        b        1b
1        b       
1        c        1c
2        a        2a
2        a       
2        b        2b
2        b       
2        c        2c
2        c       
3        a        3a
3        b        3b
3        c        3c
3        d        3d

Book14.zip (2.25 KB)

=IF(SUMPRODUCT(1*(A$2:A2&B$2:B2=A2&B2))=1,A2&B2,"")

TOP

¦hÁÂoobird
¤S¬O¥Î¨ìsumproduct, §Ú©l²×­ø©ú¥Õ³o¤½¦¡¥Îªk
¥i§_Á¿¸Ñsumproduct ÂI¥Î¶Ü?

TOP

¥ý¥HSUMPRODUCT
google¤@¤U§a¡C
ÁÙ¦³ª¼ÂI¦A´£¥X°Q½×

TOP

=IF(SUM(--(COUNTIF($C$2:$C$18,$C$2:$C$18)=1))>=ROW(A1),INDEX($C$2:$C$18,SMALL(IF(COUNTIF($C$2:$C$18,$C$2:$C$18)=1,ROW($C$2:$C$18),""),ROW(A1))-1),"")

°}¦C¤½¦¡¿é¤J§¹¶·«ö Ctrl+Shift+Enter ¤TÁä¤~¥i±o¨ì

0823.rar (3.03 KB)

TOP

¥»©«³Ì«á¥Ñ enoch ©ó 2011-8-25 14:45 ½s¿è

Ú»¹L»¡©ú«á, ©l²×¦³°ÝÃD,
sumproduct Ãþ¦üsumif ¤Î countif ¦ýÁÙ¤À­ø²MÂI¥Î
­Y¥H¤U­±¨Ò¤l, ·íºØÃþ¬Û¦P, ¥i¥H¥uÅã¥Ü³Ì¤j¼Æ¶qªºª÷ÃB¶Ü?
¨Ò¦pÄ«ªG :$18  ;¾í $19
½Ð«ü±Ð

¼Æ¶q  ºØÃþ  ª÷ÃB
15    Ä«ªG  $5
20    Ä«ªG  $10
35    Ä«ªG  $18
2     ¾í    $7
3     ¾í    $19

TOP

TOP

¦³ÂI·d¤£²M·¡ªO¤j¼ÐÃD¬O¦b°Ý¬Æ»ò¡A°w¹ï¦b´£°Ýªº°ÝÃD

=INDEX($C$2:$C$6,MATCH(MAX(($B$2:$B$6=E2)*($A$2:$A$6)),$A$2:$A$6,0))

°}¦C¤½¦¡¿é¤J§¹¶·«ö Ctrl+Shift+Enter ¤TÁä

0825.rar (3.58 KB)

¸Õ¬Ý¬Ý!!!

TOP

¥»©«³Ì«á¥Ñ gong ©ó 2011-8-27 21:38 ½s¿è

¤£¦n·N«ä, ¬O§Ú¤ÓÃi¨S¦³¤À¶}°ÝÃD,
¦Ó¨â¦ì°ª¤â©Ò±Ð¾Éªº¨Ï§Ú±o¯q¤£¤Ö

man65boy ªº¤½¦¡¥X²{¤Ö¤Ö°ÝÃD:
=INDEX($C$2:$C$6,MATCH(MAX(($B$2:$B$6=E2)*($A$2:$A$6)),$A$2:$A$6,0))
·íÄ«ªG»P¾íªº¼Æ¶q¬Û¦P®É, ¤½¦¡¥u·|¨úÄ«ªGªºª÷ÃB

TOP

©ç¨Æ..ÅçÃÒ¤Ó¤Ö...¨Sª`·N¨ì¡A¦b¸Õ¸Õ¥H¤U¨ç¼Æ

=IF(ISNA(INDEX($C$2:$C$20,MATCH(E2&MAX(IF(($B$2:$B$20=E2),$A$2:$A$20)),$B$2:$B$20&$A$2:$A$20,0))),"",INDEX($C$2:$C$20,MATCH(E2&MAX(IF(($B$2:$B$20=E2),$A$2:$A$20)),$B$2:$B$20&$A$2:$A$20,0)))

°}¦C¤½¦¡¿é¤J§¹¶·«ö Ctrl+Shift+Enter ¤TÁä
0827.rar (3.98 KB)
1

µû¤À¤H¼Æ

    • gong: EÄæ¨ú°ß¤@­È¼gªº«Ü¦n,­È±o¾Ç²ßª÷¿ú + 2

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD