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

[µo°Ý] ½Ð°Ý¡G[D4]½Ð¶ñ¤J¤°»ò¤½¦¡?¤~¯à¥h°£­«Âжµ¥Ø?

[µo°Ý] ½Ð°Ý¡G[D4]½Ð¶ñ¤J¤°»ò¤½¦¡?¤~¯à¥h°£­«Âжµ¥Ø?

½Ñ¦ì¤j¤j, §A¦n:
¦p¹Ï¡G

½Ð°Ý¡G[D4]½Ð¶ñ¤J¤°»ò¤½¦¡?¤~¯à¥h°£­«Âжµ¥Ø?
ÁÂÁÂ!!(Excel 2003)

¥X¯Ê¶Ô§O.xls
http://www.mediafire.com/download/ovd1d632dz2li6t/%E5%87%BA%E7%BC%BA%E5%8B%A4%E5%88%A5.7z

¥»©«³Ì«á¥Ñ p212 ©ó 2014-5-23 08:34 ½s¿è

¦^´_ 1# yen956
°²³]Äæ¦ì¦WºÙ¤À§O©óÀx¦s®æA1¤ÎÀx¦s®æD1¡A¨ä¹ïÀ³¸ê®Æ¦Û²Ä¤G¦C°_¡A¨ú¥XAÄ檺°ß¤@­È¡G
1¡B©w¸q¦WºÙ¡G¡u¥X¯Ê¶Ô§O¡v¡A°Ñ·Ó¨ì¡G¿é¤J =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)
2¡BÀx¦s®æD2¿é¤J°}¦C¤½¦¡
{=IF(ISNA(MATCH(0,COUNTIF($D$1:D1,¥X¯Ê¶Ô§O),0)),"",INDEX(¥X¯Ê¶Ô§O,MATCH(0,COUNTIF($D$1:D1,¥X¯Ê¶Ô§O),0)))}
3¡B­Y¨Ï¥ÎExcel 2007ª©¥»¥H«áªÌ¡AÀx¦s®æD2¥i¿é¤J°}¦C¤½¦¡
{=IFERROR(INDEX(¥X¯Ê¶Ô§O,MATCH(0,COUNTIF($D$1:D1,¥X¯Ê¶Ô§O),0)),"")}
½Ð°Ñ¦Ò¡I
µù¡GªþÀÉ©Ò¦b¦ì¸m¦n¹³¤£«ç»ò¦w¥þ¡A¥i§_§¨Àɦb´£°Ý¤§¤U¡HÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-5-23 08:52 ½s¿è

¦^´_ 1# yen956
¸É­z2#
1¡B­YAÄæ¸ê®Æ³¡¥÷¥kºÝ¨ã¦³¡u¼Æ¦r¡v¡A«h§Q¥Î¤U¦C°}¦C¤½¦¡©óBÄæ³y»²§UÄæ(§Y¸ê®Æ¥kºÝ¤£§t¼Æ¦r¡AÀx¦s®æB1½Ð°_¤@Äæ¦ì¦WºÙ¦p¡u»²§UÄæ¡v)
{=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:10")),1)*1),ROW(INDIRECT("1:10")),99))-1)}
2¡B2#­ì¦³©w¸q¦WºÙ¥i­×§ï¬°¡G¡u»²§UÄæ¡v°Ñ·Ó¨ì¡G¬° =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1)
3¡BÀx¦s®æD2¿é¤J°}¦C¤½¦¡
{=IF(ISNA(MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)),"",INDEX(»²§UÄæ,MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)))}
4¡B­Y¨Ï¥ÎExcel 2007ª©¥»¥H«áªÌ¡AÀx¦s®æD2¥i¿é¤J°}¦C¤½¦¡
{=IFERROR(INDEX(»²§UÄæ,MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)),"")}
½Ð°Ñ¦Ò¡I

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-5-23 10:45 ½s¿è

¦^´_ 3# p212
¸Õ¤F¥b¤Ñ, ²×©ó¸Õ¥X¨Ó, ­ì¨Ó§Ñ¤F±N[R1C1]Äæ¦W¦C¸¹ªí¥Üªkªº¤Ä¤Ä¥h±¼,
ÁÂÁ¤j¤jªº«ü¾É, ÁÂÁÂ!!
¤S,
  1. 4¡B­Y¨Ï¥ÎExcel 2007ª©¥»¥H«áªÌ¡AÀx¦s®æD2¥i¿é¤J°}¦C¤½¦¡
  2. {=IFERROR(INDEX(»²§UÄæ,MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)),"")}
½Æ»s¥N½X
¦¹¤½¦¡¦bExcel 2007ª©¥»¥H«á, ¤£§ï¤½¦¡¥i¦æ¶Ü?
§Y, ¤½¦¡ {=IF(ISNA(MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)),"",INDEX(»²§UÄæ,MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)))}
¦bExcel 2007ª©¥»¥H«á¥i¦æ¶Ü?

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-5-23 11:18 ½s¿è

¦^´_ 4# yen956
¤½¦¡ {=IF(ISNA(MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)),"",INDEX(»²§UÄæ,MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)))} ¦bExcel 2007¤@¼Ë¥i¥H¥Î¡I
¥ÎIFERROR¤½¦¡¸ûµu¡A¦ý¦b±zªºExcel 2003µLªk¨Ï¥Î¡C

TOP

¦^´_ 5# p212
¤F¸Ñ, ÁÂÁ»¡©ú,
µo²{·s°ÝÃD, ¥Î¤½¦¡ B2 =IF(A2,"T","") ¤U©Ô,
­Y A4,A5 ¬°false, «h B4,B5 ¬°°²ªÅ,
¥Î¿ï¾Ü©Ê¶K¤W¡÷­È,  §Y¥h±¥¤½¦¡, B4,B5 ¤´¬°°²ªÅ,
³o·|¨ÏVBA: [B500].end(xlUP) »~§P,
­Y§ï¥Î ¤£­«Âжi¶¥¿z¿ï, «hµL¦¹°ÝÃD,
¤£ª¾­ì¤½¦¡¯à§_ÁקK¥Î "" (°²ªÅ)ªº°ÝÃD?

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-5-23 13:40 ½s¿è

¦^´_ 6# yen956
{=IF(ISNA(MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)),"",INDEX(»²§UÄæ,MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)))}
©Î
{=IFERROR(INDEX(»²§UÄæ,MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)),"")}
¨ä
COUNTIF($D$1:D1,»²§UÄæ)¡G¹ï$D$1:D1¤£¦s¦b©ó¡u»²§UÄæ¡v¤¤ªº¥ô¦ó­È¡A¨ä°}¦C­pºâÅã¥Ü¬°{0,0,0,0...}¡C
MATCH(0,COUNTIF($D$1:D1,»²§UÄæ),0)¡GMATCH¥Î¥H©w¦ì¡u0¡v¦bCOUNTIF°}¦C{0,0,0,0¡K¡K}¤¤ªº¦ì¸m¡C
¥H1#­ì°Ý¨Ó»¡¡AÅçÃÒµ²ªG§Y¨Ï¡u»²§UÄæ¡v¸ê®Æ¬°¡uFALSE¡v¥ç¤£·|¥X²{±z©Ò¿×ªº¡u°²ªÅ¡v²{¶H¡C
¦Ü©ó¡u¨ÏVBA: [B500].end(xlUP) »~§P¡v¤§°ÝÃD¡A§^¤H¤£ª¾¡A¬ß°ª¤â­Ì«ü¾É¡B¾Ç²ß¡C
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 7# p212
¤j¤j§A¦n, ÁÂÁ¦^ÂÐ!!
¦p¤U¹Ï¡G
  
¥ÎVBA ¡GEndRow=23, <>12==>D13:D23¬O°²ªÅ
D13:D23¦³¤½¦¡©Î¶K¦¨­È³£¤@¼Ë
  1. Private Sub CommandButton1_Click()
  2.     row1 = [D500].End(xlUp).Row
  3.     MsgBox "EndRow=" & row1
  4. End Sub
½Æ»s¥N½X
¥Î¤â°ÊCtrl+ArrowUp ¤]¤@¼Ë

­Y±ND13:D23 Delete, «h EndRow=12(¯uªÅ)

¥X¯Ê¶Ô§O2.7z
http://www.mediafire.com/download/44kgjsj8pyvlso5/%E5%87%BA%E7%BC%BA%E5%8B%A4%E5%88%A52.7z

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD