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

[µo°Ý] ½Ð°Ý ·j´M¤@©T½s¸¹ ¦Ü ªÅ¥Õ ¦A©¹¤U·j´Á·j´M ¨ú¥X¤u§@ªí¥þ³¡¬Û¦P¼Ð¸¹¤§¸ê®Æ

[µo°Ý] ½Ð°Ý ·j´M¤@©T½s¸¹ ¦Ü ªÅ¥Õ ¦A©¹¤U·j´Á·j´M ¨ú¥X¤u§@ªí¥þ³¡¬Û¦P¼Ð¸¹¤§¸ê®Æ

¥»©«³Ì«á¥Ñ mycmyc ©ó 2012-4-23 17:33 ½s¿è

½Ð°Ý ·j´M¤@©T½s¸¹ ¦Ü ªÅ¥Õ   ¦A©¹¤U·j´Á·j´M¤@©T½s¸¹ ¦Ü ªÅ¥Õ¶¡ ¨Ã¨ú¥X¤u§@ªí¥þ³¡¬Û¦P¼Ð¸¹¤§¸ê®Æ

¹B¥Î ¨ç¼Æ ·j´M ¨ú¥X ¸ê®Æ
¦p¹Ï ¬õ¦â ¸ê®Æ ¨ú¥X ¨ì¥t¤u§@ªí

¥Î ¬ì¥Ø¥N¸¹  6180 ·j´M ¦Ü ªÅ¥Õ¦æ   ¦A©¹¤U·j´Á6180 ¦Ü ªÅ¥Õ¦æ  ¨ú¥X¤u§@ªí¥þ³¡6180¦Ü ªÅ¥Õ¦æ ¤§¸ê®Æ

¸Õ¤F«Ü¦h¤èªk¦h¥¢±Ñ  ½Ð°Ý  ¹B¥Î ¨ç¼Æ  ¦³¿ìªk   °µ³o¤@·j´M¶Ü?  ÁÂÁ  ³Â·Ð«ü±Ð

0



Book1.rar (11.46 KB)
HI

¦^´_ 1# mycmyc

»²§UÄæ
E2=IF(B2="","",IF(OR(B2=6180,E1="Y"),"Y",""))   ©¹¤U½Æ»s
G2=IF(INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)="","",INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)&"")   °}¦C¤½¦¡  ©¹¥k½Æ»s ©¹¤U½Æ»s

B00K1.rar (9.28 KB)

TOP

¥»©«³Ì«á¥Ñ mycmyc ©ó 2012-4-23 23:37 ½s¿è

¦^´_ 2# register313

·PÁ register313  ¤j¤j«ü¾É


»²§UÄæ
E2=IF(B2="","",IF(OR(B2=6180,E1="Y"),"Y",""))   
¯u¬O¥©«ä   Åý½ÆÂø°ÝÃD³æ¯Â¤Æ  

§Ú¤@ª½·Q¥Î , ¤@¦æ¸Ñ¨M,  ¦ý¦hµLªk¹F¦¨   
ÁÂÁ§A
G2=IF(INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)="","",INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)&"")  
¥i¥H¦³ §óºë² §@ªk¶Ü¡H  
¹ï¤£°_  «_¬N ­n¨D  
ÁÂÁ§A
HI

TOP

¦^´_ 3# mycmyc

G2=INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)&""   °}¦C¤½¦¡

TOP

¦^´_ 4# register313

·PÁÂ register313 ¤j¤j
ÁÂÁÂ
¤p§Ì¤£¤~  »Ý­n®É¶¡®ø¤Æ
¦ý ¥Ñ°J·PÁ ¤j¤j À°¦£

ÁÂÁ§A
HI

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-4-25 15:19 ½s¿è

¦^´_ 2# register313


    ¬ü¤¤¤£¨¬¡A(¦p¹Ï)

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

TOP

¥»©«³Ì«á¥Ñ mycmyc ©ó 2012-4-25 18:55 ½s¿è

¦^´_ 6# Hsieh

·PÁÂ Hsieh  ¤j¤j
©p¼F®`¬Ý¥X ¤p§Ì »Ý¨D
·j´M ¨Ã§â¾ã­Ó ¦¬¤ä ¾ã­Ó ¦C¥X
¨Ã»¡©ú¤F  ¿òº| ¤§³B
¦p B24 §Y  ¿òº| ¤§³B
·PÁÂ
¦ý¤p§Ì ¦³¨Ç¤£©ú¥Õ
COUNTIF(z,$F$1)>0
¨ä¤¤ Z  ¥Nªí·N«ä
INDIRECT(CHAR(COLUMN(B5)+64)&a)
&a  ¥Nªí·N«ä

ÁÙ¦³  §Ú¨S¯à¤O ªí©p¨ç¼Æ  §ï¦¨ ¦b Sheet2 ¦C¥X
§Ú §Æ±æ µ²ªG ¦bSheet2 ¦C¥X
½Ð©p«ü±Ð  ÁÂÁ©p
HI

TOP

¦^´_ 7# mycmyc
x¡By¡Bz¡Ba³£¬O©w¸q¦WºÙ¡A§@¥ÎÀx¦s®æ¦ì©ó²Ä2¦C®É¡A«Ø¥ß¦¹4­Ó¦WºÙ
1¡B¨ú±oA1¨ì¦¹¦C¬°¤î¡A³Ì«á¤@­Ó¤å¦r¦ì¸m¡A¦]¬°§Aªº¤é´Á¨Ã«D¥¿½T¤é´Á®æ¦¡¡A¬O¥H¤å¦r¦ê§Î¦¨ªº¸ê®Æ¡A©Ò¥H¥²¶·¨Ï¥Î¤å¦r·j´M
x°Ñ·Ó¨ì¤½¦¡
=LOOKUP("ùÕ",Sheet1!$A$1:$A2,ROW(Sheet1!$1:2))
¥i§ï¦¨
x°Ñ·Ó¨ì¤½¦¡
=LOOKUP(2,1/(Sheet1!$A$1:$A2<>""),ROW(Sheet1!$1:2))
³o¼Ë¥i°t¦X¥ô¦ó¸ê®Æ«¬ºA
2¡B¥»¦C¥H¤U¨ì²Ä2000¦CªºBÄæ­Y¬°ªÅ¥Õ´N¶Ç¦^¦C¸¹¡A¨D±o³o¨Ç¦C¸¹ªº³Ì¤p­È¡A³o¼Ë¥i¨ú±o¸ê®Æ§ÀºÝ¡C
y°Ñ·Ó¨ì¤½¦¡
=MIN(IF(Sheet1!$B2:$B$2000="",ROW(Sheet1!$B2:$B$2000),""))
3¡B¶Ç¦^¨C­Ó°Ï°ì½d³ò
z°Ñ·Ó¨ì¤½¦¡
=INDIRECT("Sheet1!B"&x&":B"&y-1)
4¡B¶Ç¦^EÄ檺©Ò¦³¼Æ­È
a°Ñ·Ó¨ì¤½¦¡
=SMALL(Sheet1!$E$2:$E$2000,ROW(!A1))
5¡BE2¤½¦¡=IF((B2<>"")*(COUNTIF(z,$F$1)>0),ROW(),"")
°²¦pBÄ椣µ¥©óªÅ¥Õ¡A¦Ó¥B©Ò¦b¦Cªº³sÄò¸ê®Æ½d³ò¤¤§t¦³·j´M·Ç«hF1ªº­È¡A´N¶Ç¦^¸Ó¦Cªº¦C¸¹¡C
6¡BSheet2!A2¤½¦¡=IF(ISERROR(a),"",INDIRECT("Sheet1!"&CHAR(COLUMN(A1)+64)&a))
¦pªG¶W¹LSheet1!EÄæ¼Æ­Èªº¼Æ¶q¡Aa´N·|¬O¿ù»~­È¡A©Ò¥H§PÂ_a¬°¼Æ­È´N±a¤JINDIRECT("Sheet1!"&CHAR(COLUMN(A1)+64)&a)³o¬q¨Ó­pºâ
CHAR(COLUMN(A1)+64)³o·|¶Ç¦^¦r¦ê"A"¡A¦V¥k½Æ»s®É´N¦¨¬°CHAR(COLUMN(B1)+64)¡A¶Ç¦^¦r¦ê"B"¡A¥H¦¹Ãþ±À
INDIRECT¬O¥HÀx¦s®æ°Ñ·Ó¦r¦ê¨Ó¶Ç¦^¸Ó¦ì¸m¡A©Ò¥H¡A("Sheet1!"&CHAR(COLUMN(A1)+64)&a´Nµ¥©ó¦r¦ê"Sheet1!A2"

Åܰʽd³ò.rar (7.58 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ mycmyc ©ó 2012-4-25 23:20 ½s¿è

¦^´_ 8# Hsieh

·PÁ    Hsieh ¤j¤j  ¼·ªÅ °µ¸Ô²Ó¸Ñ»¡
­ì¨Ó x¡By¡Bz¡Ba³£¬O©w¸q¦WºÙ   
¹ï¤£°_ ¨S¸Ô²Ó Àˬd
INDIRECT("Sheet1!"&CHAR(COLUMN(B1)+64)&a)
§Ú¦³¦bµ{¦¡¦C ¤@¤@¬d¸ß  
¹ï&a ¤£©ú¥Õ  ©Ò¥H¬A¸¹¿ù©ñ¦ì¸m   ¦Ó¤£¦¨¥\

·PÁ §A¤@¤@¸ÑÄÀ  §ÚµLªk°¨¤W²z¸Ñ
§Ú±o ¤@¤@¹Á¸Õ ÁÂÁ§A
¤£À´¤§³B ¦b·Ð½Ð§A «ü¾É

ÁÂÁ§A  ·P®¦
HI

TOP

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD