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

[µo°Ý] ±Æ§Çªº°ÝÃD~

[µo°Ý] ±Æ§Çªº°ÝÃD~

Â^¨ú2.PNG
§Ú·Q¦b¤u§@ªí2¤¤¨ú±o¤u§@ªí1¤¤ªº¸ê®Æ¡A¨Ã±Æ§Ç¤j¤p¡A·í§Ú¦b¤u§@ªí2ªºa2¸Ì¥´¤W¤H­û¸¹½X¡A
¤U­±´N·|¨Ìª÷ÃBªº¤j¨ì¤p±Æ§Ç¥X¬Û¦P¤H­û³æ¾Ú½s¸¹¸òª÷ÃB(¦p¹Ï)¡A³æ¾Ú½s¸¹¤£­«´_¡A¤H­û¸¹½X
·|­«´_¡Aª÷ÃB¤]·|­«´_¡A¦³°ª¤â¥i¥HÀ°¦£¶Ü?

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-10 08:35 ½s¿è

¦^´_ 1# ms2001
1¡B°é¿ï¤u§@ªí1¤§A1:C14¡A«öCtrl+Shift+F3¡A¥H¡u³»ºÝ¦C¡v¬°¦WºÙ¡C
2¡Bfor Excel 2007
¤u§@ªí2¤§Àx¦s®æA4¿é¤J°}¦C¤½¦¡ (¥HCtrl+Shift+Enter¿é¤J)
=IFERROR(INDEX(³æ¾Ú½s¸¹,SMALL(IF(¤H­û½s¸¹=$A$2,ROW(¤H­û½s¸¹),FALSE),ROW(1:1))-1),"")
Àx¦s®æB4¿é¤J°}¦C¤½¦¡
=IFERROR(INDEX(ª÷ÃB,SMALL(IF(¤H­û½s¸¹=$A$2,ROW(¤H­û½s¸¹),FALSE),ROW(1:1))-1),"")
for Excel 2003
¤u§@ªí2¤§Àx¦s®æA4¿é¤J°}¦C¤½¦¡ (¥HCtrl+Shift+Enter¿é¤J)
=IF(ISERROR(SMALL(IF(¤H­û½s¸¹=$A$2,ROW(¤H­û½s¸¹),FALSE),ROW(1:1))),"",INDEX(³æ¾Ú½s¸¹,SMALL(IF(¤H­û½s¸¹=$A$2,ROW(¤H­û½s¸¹),FALSE),ROW(1:1))-1))
Àx¦s®æB4¿é¤J°}¦C¤½¦¡
=IF(ISERROR(SMALL(IF(¤H­û½s¸¹=$A$2,ROW(¤H­û½s¸¹),FALSE),ROW(1:1))),"",INDEX(ª÷ÃB,SMALL(IF(¤H­û½s¸¹=$A$2,ROW(¤H­û½s¸¹),FALSE),ROW(1:1))-1))
¦V¤U½Æ»s¤½¦¡
½Ð°Ñ¦Ò¡I

TOP

¸Õ¤F¤@¤U¡A¸ê®Æ·|¥X¨Ó¡A¥i¬O¨Ã¤£·|±Æ§Ç¡A½Ð°Ýp¤j¬O¦³­þ¸Ì¸Ó­×¥¿¶Ü?

TOP

½Ð°Ý­þ¸Ì¥i¥H¬d¨ìISEFFOR, IF¥Îªk?

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-21 15:00 ½s¿è

¦^´_ 4# jenny1203
1¡B¦bExcel»¡©ú¤¤¿é¤J¡uISERROR¡v«á¡A«öEnter¡A¶i¤J¡u¸ê°T¨ç¼Æ¡v§Y¥i¬d±o¨ç¼Æ¥Îªk¡C
2¡B¦bExcel»¡©ú¤¤¿é¤J¡uIF¡v«á¡A«öEnter¡A§Y¥i¬d±o¨ç¼Æ¥Îªk¡C
½Ð°Ñ¦Ò¡I

TOP

·s¤â¾Ç²ß¤¤¡A
·PÁ¤À¨É¡I

TOP

¦^´_ 1# ms2001

¦]¬°ª÷ÃB¥i¯à­«½Æ
¬d¸ß·Ç«h¬°¤H­û¸¹½X
°}¦C¤½¦¡¨ú¥X¸ê®Æ¥u¯à«ö­ì¸ê®Æ¶¶§ÇÂ^¨ú¸ê®Æ
©Ò¥H¡A­Y­nµ²ªG¥Hª÷ÃB±Æ§Ç
¨º´N§â­ì¸ê®Æ¥Hª÷ÃB±Æ§Ç§Y¥i
¨ä¹ê³o¼Ëªº¬d¸ß­Y¬O¸ê®Æ¶q¤jªº®É­Ô·|³y¦¨¨t²Î­t²ü¹L­«
§Q¥Î¸ê®Æ¬d¸ß¥\¯à¬O¤£¿ùªº¿ï¾Ü­ò!
play.gif
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2014-7-30 18:02 ½s¿è

¡Õ«ü©w½s¸¹¿z¿ï¡A¨Ã¥Hª÷ÃB¥Ñ¤j¦Ó¤p±Æ§Ç¡Ö
¶È¥H¦P¤@¤u§@ªí¤U¹F¤½¦¡¡A¤£¦P¤u§@ªí½Ð¦Û¦æ½Õ¾ã¡I
¢Õ¢°¡G«ü©w¤H­û½s¸¹
¢×¢°¡D²Å¦Xµ§¼Æ¡G=COUNTIF(B:B,G1)

¥H¤U¤½¦¡§¡¤U¨ê§¹¦¨¡ã¡ã
¢Ó¢³¡D§Ç¸¹Äæ¡G=ROW(A1)
¢Õ¢³¡Dª÷ÃB¡]¥Ñ¤j¦Ó¤p±Æ§Ç¡^¡D°}¦C¤½¦¡¡G
¡@¡@¡@=IF(ROW(A1)>I$1,"",LARGE((B$2:B$100=G$1)*C$2:C$100,E4))
¢Ô¢³¡D³æ¾Ú¸¹½X¡D°}¦C¤½¦¡¡G
¡@¡@¡@=IF(G4="","",INDEX(A:A,MOD(SMALL(IF(B$2:B$100=G$1,-C$2:C$100*10^6+ROW($2:$100)),E4),10^6)))
¡@¡@¡@©Î¡G
¡@¡@¡@=IF(G4="","",INDEX(A:A,SMALL(IF((B$2:B$100=G$1)*(C$2:C$100=G4),ROW($2:$100)),COUNTIF(G$3:G3,G4)+1)))
¡@
°Ñ¦ÒªþÀÉ¡G
X20140730.rar (3.5 KB)
http://www.funp.net/207794¡@
¡@
µù¡G°}¦C¤½¦¡¹Bºâ¯Ó®É¡A¥²­n®ÉÀ³¥HVBA³B²z¸û¦X¾A¡A
¡@¡@¥t¥~¡A³z¹L¿z¿ï½Æ»s¦A±Æ§Ç¡A¥u­n¼ô½m¨Ç¡A¤]¬O¤£¿ùªº¤èªk¡ã¡ã
¡@

TOP

¤Ó±j¤F¡A¤Ó·PÁ¤F~

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD