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

[µo°Ý] ½Ð°Ý¦p¦ó¤ñ¹ï¨âªí®æ¸ê®Æ«á¡A±a¥X¬Û²Å¸ê®Æ

[µo°Ý] ½Ð°Ý¦p¦ó¤ñ¹ï¨âªí®æ¸ê®Æ«á¡A±a¥X¬Û²Å¸ê®Æ

½Ð°Ý¦p¦ó°Ñ·Ó(ªí¤@)ªº¼Æ¾Ú(ÅÜ°Ê­È)¡A¥h¤ñ¹ï(ªí¤G)¸ê®Æ¡A¥B¬°¤ä²¼§O
¦³¬Û²Å¸¹½X(½s¸¹)ªº¸ê®Æ«h±a¥X¨ì(ªí¤T)
¨ä¤¤ªí¤@ªº¼Æ¾Ú¦³¨â­¶¡A¤¤¶¡¦³¤À¬q
ªí¤G«h¬O¤¤¶¡·|¦³ªÅ¥ÕÄæ¦ì

¹Á¸Õ¨Ï¥ÎVLOOKUP¥h¤ñ¹ï¡A¦ý¬O¸Õ¤£¥X¨Ó¡A¦A½Ð¦U¦ì«e½ú­ÌÀ°¦£
·P¿E¤£ºÉ~

²Ö­p¤ä²¼.rar (8.87 KB)

¦^´_ 1# jackson7015
½Ð°Ñ¦Ò

²Ö­p¤ä²¼_2.zip (10.05 KB)

TOP

¦^´_ 1# jackson7015

A18°}¦C¤½¦¡
=IF(ROW(A1)>SUMPRODUCT(ISNUMBER(MATCH($B$4:$B$12&"¤ä²¼",$G$3:$G$36&$I$3:$I$36,0))*1),"",INDEX($A$1:$E$12,SMALL(IF(ISNUMBER(MATCH($B$4:$B$12&"¤ä²¼",$G$3:$G$36&$I$3:$I$36,0)),ROW($B$4:$B$12),""),ROW(A1)),1))
B18°}¦C¤½¦¡
=IF(ROW(A1)>SUMPRODUCT(ISNUMBER(MATCH($B$4:$B$12&"¤ä²¼",$G$3:$G$36&$I$3:$I$36,0))*1),"",INDEX($A$1:$E$12,SMALL(IF(ISNUMBER(MATCH($B$4:$B$12&"¤ä²¼",$G$3:$G$36&$I$3:$I$36,0)),ROW($B$4:$B$12),""),ROW(A1)),2))
C18°}¦C¤½¦¡
=IF(ROW(A1)>SUMPRODUCT(ISNUMBER(MATCH($B$4:$B$12&"¤ä²¼",$G$3:$G$36&$I$3:$I$36,0))*1),"",INDEX($A$1:$E$12,SMALL(IF(ISNUMBER(MATCH($B$4:$B$12&"¤ä²¼",$G$3:$G$36&$I$3:$I$36,0)),ROW($B$4:$B$12),""),ROW(A1)),5))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ jackson7015 ©ó 2014-8-15 08:53 ½s¿è

¦^´_ 2# p212
·PÁÂp212¤j¤jªº¨ó§U¡AÁöµM­n¥H¤é´Á°µ·j´M¼Ðªº¡AµLªk§¹¥þ·j´M
¤£¹L¤]Åý¤p§Ì¦³¥t¥~ªº«ä¦Ò¤è¦V¡A¹Á¸ÕÅܧ󬰲¼§O¤è¦¡·j´M
   
¦^´_ 3# Hsieh
·PÁª©¥D¤j¤jªºÀ°¦£¡A¤½¦¡­pºâ¥¿±`¡F¦ý¬OÁٻݭn®ø¤Æ¤@¤U¤º®e¡A¤×¨ä¬OROW(A1)ªº­pºâ¤è¦¡¦³ÂI¯S§O
¤½¦¡¦³ÂIÃlªø¡A¥B¦³³¡¤ÀªºÀx¦s®æ¬O¦X¨ÖÀx¦s®æ¡AµLªk¨Ï¥Î¯x°}¤½¦¡¡A©Ò¥H§â¤é´Á©Mª÷ÃBªº·j´M¤è¦¡Åܧó¤F¤@¤U
A18=IF(ISERROR(VLOOKUP($B18,IF({1,0},B4:B12,A4:A12),2,0)),"",VLOOKUP($B18,IF({1,0},B4:B12,A4:A12),2,0))
C18=IF(ISERROR(VLOOKUP($B18,$B$4:$E$12,4,0)),"",VLOOKUP($B18,$B$4:$E$12,4,0))
ÁöµM¤W¦¡¤½¦¡¥u¯à¨Ï¥Î¯S©wªí®æ¡A¤£¹L²µu¤@ÂI¦Û¤v¤ñ¸û¦nÀˬd

·PÁ¤j¤j­Ì¤£§[«ü±Ð~:)

TOP

¦^´_ 3# Hsieh
¤£¦n·N«ä¡A½Ð°ÝHsiehª©¤j
¦]¬°¦Û³]®æ¦¡¦³¤£¦P¡A©Ò¥H¤½¦¡ªº´À¥N¦n¹³¦³°ÝÃD
¥i§_³Â·Ð¦bÀ°§Ú¬Ý¬Ý­þ¸Ì»Ý­n§ó¥¿ªº¶Ü

¦]¬°¦b¹BºâROWªº³¡¥÷§Ú¤£¤Ó¤F¸Ñ¬O«ç»ò¹B§@ªº
·Ó¥»«Å®Æªº§ï¹L¥h¦³°ÝÃD
¦b½ÐªO¤jÀ°¦£­×¥¿
·PÁÂ~
²Ö­p¤ä²¼.rar (19.54 KB)

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2014-9-16 17:44 ½s¿è

¦^´_ 5# jackson7015


¤u§@ªí1
B4 =IFERROR(INDEX(¤u§@ªí3!C:C,MATCH(ROW()-3,¤u§@ªí3!$H:$H,)),"")
C4 =IFERROR(INDEX(¤u§@ªí3!D:D,MATCH(ROW()-3,¤u§@ªí3!$H:$H,)),"")
D4 =IFERROR(INDEX(¤u§@ªí3!G:G,MATCH(ROW()-3,¤u§@ªí3!$H:$H,)),"")


¤u§@ªí3
H7 =IF(IFERROR(VLOOKUP(D7,¤u§@ªí2!F:H,3,)="¤ä²¼",FALSE),MAX(¤u§@ªí3!H$1:H6)+1,"")

¥H¤W¦U¤½¦¡¦V¤U½Æ»s
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089
·PÁÂML089ª©¤jªº¦^ÂÐ

´ú¸Õ«áµo²{´X­Ó°ÝÃDÂI
MAX¦b¹Bºâ³¡¤À¥u·|®Ú¾Ú«e5®æ¤ÀªR¡A¦pªG®t¶Z5®æ·|¥X²{§PÂ_¿ù»~
¦pªG¸¹½X¦³­«½Æªº¸Ü¡A¥u¯à¹Bºâ³Ì¥ý¼Æ­È¡F¦³®É­Ô·|¦³2µ§©Î¥H¤W¦P¸¹½X¼Æ­È(ª÷ÃB/¤é´Á¤£¦P)
¤£¾å±o¬O§_¦p¦ó­×¥¿³o°ÝÃD?

¨Ï¥ÎHsiehª©¤jªº°}¦C¤½¦¡¡A¥i¥H¹Bºâ¬Û¦P¸¹½Xªº³¡¤À
¦ý¬O¦Û¤v¨S¦³­×§ï¦¨¥\

TOP

¦^´_ 7# jackson7015

­×§ï
¤u§@ªí3
H7 =IF(IFERROR(VLOOKUP(D7,¤u§@ªí2!F:H,3,)="¤ä²¼",FALSE),MAX(¤u§@ªí3!H$1:H6)+1,"")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# jackson7015

¦pªG¸¹½X¦³­«½Æªº¸Ü¡A¥u¯à¹Bºâ³Ì¥ý¼Æ­È¡F¦³®É­Ô·|¦³2µ§©Î¥H¤W¦P¸¹½X¼Æ­È(ª÷ÃB/¤é´Á¤£¦P)
¤£¾å±o¬O§_¦p¦ó­×¥¿³o°ÝÃD?

   
¬O«ü¤u§@ªí¤T·|¦³2µ§©Î¥H¤W¦P¸¹½X¼Æ­È(ª÷ÃB/¤é´Á¤£¦P)¡A¨º¤u§@ªí¤G¶}¤ä²¼®É¤]·|¤À¨âµ§©Î¦hµ§¶Ü? ©Î¦X¦¨³æµ§
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# jackson7015

¤é´Á        ¸¹½X        (¤£§tµ|)        µ|         (§tµ|)
103/9/12        1001219        9,524-        476-        10,000- ©ú²Óªí¤@
103/3/25        1001219        9,524-        476-        10,000- ©ú²Óªí¤G

¤u§@ªí¤T ¦³2­Ó©ú²Óªí¡A¦U¦³ 1001219 ½s¸¹¡A¤u§@ªí¤G¤¤ 1001219 ½s¸¹¬O­n¹ïÀ³­þ¤@­Ó?
³o¬O¤£¦X²z¹ïÀ³¤è¦¡¡AÅýEXCEL§@·~Åܱo½ÆÂø¡A¸ê®Æ¤@¦hEXCEL§@·~·|¶V¨Ó¶VºC

¤u§@ªí¤GÀ³¸Ó­n¼W¥[ ¤é´Á¡A³o¼Ë¤~¯à¥H ½s¸¹+¤é´Á §@¬°¹ïÀ³¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD