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

[µo°Ý] excel 2003±Æ§Ç°ÝÃD~½Ð¦U¦ìÀ°¦£~ÁÂÁÂ!!

[µo°Ý] excel 2003±Æ§Ç°ÝÃD~½Ð¦U¦ìÀ°¦£~ÁÂÁÂ!!

§Ú·Q­n±Nªþ¥óªºÀÉ®×AÄæ¤H­û±Æ§Ç¡A¨Ò¦p¤H­ûB A I ¥Nªí3­Ó¤Hªº¥N¸¹ºÙ¿×¡A¤À3¦¸±Æ§Ç¡A­º¥ý«ö·Ó²Ä¤@­Ó¦r¥À±Æ§Ç«á¡A±N²Ä¤@­Ó¦r¥À¬Û¦Pªº¤H­ûªº²{ª÷ª÷ÃB¡A©óCÄæ¦ì¸m(§YAÄæ±Æ§Ç«á¡A²Ä¤@­Ó¦r¥À¬Û¦Pªº¤H­ûªºCÄæ¦ì¸m³B)°µ¤@­Ó¤p­p¡A·íµM¤H­û¦pªG¼W´îCÄæ¤p­p¦ì¸m³B¤]­n¸òµÛÅÜ°Ê¡A²Ä2¦¸±Æ§Ç«h¬O«ö·Ó¤¤¶¡¦r¥À©M¤¤¤å¦r±Æ§Ç¡AµM«á¤@¼Ë¡A²Ä¤G­Ó¦r¥À©Î¤¤¤å¬Û¦Pªº¤H­ûªº²{ª÷ª÷ÃB¡A©óCÄæ¦ì¸m(§YAÄæ±Æ§Ç«á¡A²Ä¤G­Ó¦r¥À¬Û¦Pªº¤H­ûªºCÄæ¦ì¸m³B)°µ¤@­Ó¤p­p¡A·íµM¤H­û¦pªG¼W´îCÄæ¤p­p¦ì¸m³B¤]­n¸òµÛÅÜ°Ê¡A²Ä3¦¸±Æ§Ç«h¸ò²Ä2¦¸±Æ§Ç¦P¨BÆJ¡A3¦¸±Æ§Ç§¹«á¡A±N¨C¤@¦¸±Æ§Ç§¹ªºµ²ªG¡A½Æ»s¨ìsheets2¼ÐÅÒ­¶¡A¤£ª¾¦³­þ¦ìªB¤Í¥i¥HÀ°§Ú~¥Îvba©Î¨ç¼Æ©Î¥¨¶°¥u­n¯à¹F¨ì¦¹®ÄªG³£¥i¡A·PÁÂÀ°¦£!! sort.rar (3.75 KB)
HUNG

¦^´_ 1# kaivenliu
¼W¥[3Ä滲§UÄæ¦ì
°µ3¦¸±Æ§Ç¤Î¤p­p¡A½Æ»s¶K¤W

sort.rar (4.83 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ kaivenliu ©ó 2011-2-17 20:18 ½s¿è
¦^´_  kaivenliu
¼W¥[3Ä滲§UÄæ¦ì
°µ3¦¸±Æ§Ç¤Î¤p­p¡A½Æ»s¶K¤W
Hsieh µoªí©ó 2011-2-17 09:03

ª©¥D§A¦n¡G
      ÁÂÁ§A°µ¤À²Õ3¦¸±Æ§Çªº¤è¦¡¡A¥»¤H¨ü¯q¨}¦h¡A¥u¬O¯à§_°µ¥X¦p§Úªþ¥óÀÉ©ósheet1°µ§¹±Æ§Ç«á¡A±N±Æ§Ç¥H«á¦Û°Ê½Æ»s¨ìsheet2¨Ã¤À¦¨3­Óªí®æ¡A¨Ã¦Û°Ê°µ¦X­p©M¥[Á`(¤£­n¤â°Êªº)¡A¦A¦¸·PÁ§A~
p.s.¥t¥~¦]¬°§ÚÅv­­¤£¨¬¡A©Ò¥HµLªk¤U¸üª©¥D­×§ï§¹ªºÀɮסA¦pª©¥D§A­×§ï§¹§ÚªºÀɮ׫á¡A¯à§_¶}©ñÅý§Ú¤U¸ü¡A¤£¬Æ·P¿E¡AÁÂÁÂ~~

sort1.rar (7.32 KB)
HUNG

TOP

¦^´_ 3# kaivenliu
«Øij§A±N¤T­Ó¦W¦r¥Î¸ê®Æ­åªR¦¨3Äæ·|²³æ¤@ÂI
­Y¨Ì·Ó§A²{¦bªº®æ¦¡¡A¦b¥[¤JA:C»²§UÄæ«á¤½¦¡ÁÙ¬O«ÜÁcÂø
ÁÙ¬O¥ý¨Ì§Aªº®æ¬O¥ý°µ¥X¨Ó(¦pªþ¥ó)
ªí®æ«Ø¥ß½Ð¿í¦u¸ê®Æ®w³W«h¡A¥H§Q¤ÀªR²Î­p
sort1.rar (17.97 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


    ª©¥D§A¦n:
          ¥i§_¶}©ñÅý§Ú¤U¸ü§AÀ°§Ú­×§ïªºÀÉ®×,ÁÂÁÂ~~
HUNG

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-2-18 14:35 ½s¿è

¦^´_ 5# kaivenliu
§Ú¨S¦³Åv­­¶}©ñ¤U¸üÅv­­¡A½Ð¸Ô¾\ª©³W
§Ú§ó§Æ±æ§A¯àÅ¥§Ú©ÒÄU¡A±N¸ê®Æªí¥¿³W¤Æ
¤£µMµ¥§A¬ÝÀ´§Úªº¤½¦¡¥i¯à·|®ö¶O§A«Ü¦h®É¶¡
©w¸q¦WºÙ:
¦b²Ä¥|¦C³Q¿ï¨úª¬ºA¤U
w=CHAR(SMALL(CODE(Sheet1!$C$2:$C$34),ROW(!$A1)))
x=SUBSTITUTE(Sheet1!$D4," ","",1)
y=CHAR(SMALL(CODE(Sheet1!$A$2:$A$34),ROW(!$A1)))
z=CHAR(SMALL(CODE(MID(Sheet1!$D$2:$D$34,FIND("  ",Sheet1!$D$2:$D$34)+2,1)),ROW(!$A1)))
½Ð±NJ3´¡¤J2­ÓªÅ¥ÕÁä¡A§_«h¤½¦¡·|¥X¿ù
¤H­û§ï¦¨¤H  ­û
***********************************
D4°}¦C¤½¦¡
{=IF(ROW(A1)>COUNTA(Sheet1!$A$2:$A$34),"",INDIRECT("Sheet1!D"&SMALL(IF(Sheet1!$A$2:$A$34=y,ROW($A$2:$A$34),""),SUMPRODUCT((MID($D$3:D3,1,1)=y)*1)+1)))}

E4°}¦C¤½¦¡
{=IF(ROW(B1)>COUNTA(Sheet1!$A$2:$A$34),"",INDIRECT("Sheet1!E"&SMALL(IF(Sheet1!$A$2:$A$34=y,ROW($A$2:$A$34),""),SUMPRODUCT((MID($D$3:E3,1,1)=y)*1)+1)))}

F4=IF(MID(D4,1,1)=MID(D5,1,1),"",SUM($E$4:E4)-SUM($F3:F$4))

J4°}¦C¤½¦¡
{=IF(ROW(A1)>COUNTA(Sheet1!$D$2:$D$34),"  ",INDIRECT("Sheet1!D"&SMALL(IF(MID(Sheet1!$D$2:$D$34,FIND("  ",Sheet1!$D$2:$D$34)+2,1)=z,ROW($D$2:$D$34),""),SUMPRODUCT((MID($J$3:J3,FIND("  ",Sheet2!$J$3:$J3)+2,1)=z)*1)+1)))}

K4°}¦C¤½¦¡
{=IF(ROW(B1)>COUNTA(Sheet1!$D$2:$D$34),"",INDIRECT("Sheet1!E"&SMALL(IF(MID(Sheet1!$D$2:$D$34,FIND("  ",Sheet1!$D$2:$D$34)+2,1)=z,ROW($D$2:$D$34),""),SUMPRODUCT((MID($J$3:K3,FIND("  ",Sheet2!$J$3:$J3)+2,1)=z)*1)+1)))}

L4=IF(MID(J4,FIND("  ",J4)+2,1)=MID(J5,FIND("  ",J5)+2,1),"",SUM($K$4:K4)-SUM($L3:L$4))

P4°}¦C¤½¦¡
{=IF(ROW(A1)>COUNTA(Sheet1!$C$2:$C$34),"  ",INDIRECT("Sheet1!D"&SMALL(IF(Sheet1!$C$2:$C$34=w,ROW($C$2:$C$34),""),SUMPRODUCT((MID(SUBSTITUTE($P$3:$P3&"  ","  ","",1)&"  ",FIND("  ",SUBSTITUTE($P$3:$P3&"  ","  ","",1)&"  ")+2,1)=w)*1)+1)))}

Q4°}¦C¤½¦¡
{=IF(ROW(B1)>COUNTA(Sheet1!$C$2:$C$34),"  ",INDIRECT("Sheet1!E"&SMALL(IF(Sheet1!$C$2:$C$34=w,ROW($C$2:$C$34),""),SUMPRODUCT((MID(SUBSTITUTE($P$3:$P3&"  ","  ","",1)&"  ",FIND("  ",SUBSTITUTE($P$3:$P3&"  ","  ","",1)&"  ")+2,1)=w)*1)+1)))}

R4=IF(P4="  ","",IF(MID(SUBSTITUTE($P5&"  ","  ","",1),FIND("  ",SUBSTITUTE($P5&"  ","  ","",1))+2,1)<>w,SUM($Q$3:Q4)-SUM($R$3:R3),""))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh


    ª©¥D§A¦n:
          ·PÁ§Aªº±Ð¾É,ªº½T§A¼gªº¤½¦¡¤Î®M¥Îªº¨ç¼Æ¹ï§Ú¨Ó»¡²`¶ø¤F¤@¨Ç,§Ú·|¸ÕµÛ±N¸ê®Æªí¥¿³W¤Æ¬Ý¬Ý,¯à§_¦X¥G§Ú©Ò¥Î,¤£¹L§ÚÁÙ¬O·|¸ÕµÛ¤F¸Ñ§A¤½¦¡ªº·N¸q,Á`¤§«D±`ªº·PÁ§AÀ°§Ú¸Ñµª~
HUNG

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD