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

¤é´ÁÅã¥Ü°ÝÃD

¤é´ÁÅã¥Ü°ÝÃD

½Ð°Ý¦U¦ì¥ý¶i¡A¦b Sheet2 ¤º¦³¨C­Ó¤Hªº¤é´Á¸ê®Æ¡A¨C­Ó¤H¤é´Á¦h¤Ö¤£¤@¡A¦]©m¦W¨Ã¤£¦b¦P¤@Ä椺¡A¦Ó¬O¦b¬Y¤@¦C¡A­n¦b Sheet1 ¿é¤J©m¦W«áÅã¥Ü Sheet2 ¦P©m¦Wªº¥þ³¡¤é´Á¸ê®Æ¡A¸Õ¹L¦h¨ç¼Æ¤½¦¡¡A¤´µLªk¥¿½Tªº¤é´Á¸ê®Æ¡A¥B ¬Y©m¦W Sheet2 Àx¦s®æµL¤é´Á¸ê®Æ«o·|Åã¥Ü1900/01/01¡A½Ð°Ý¦p¦ó¯àÅã¥Ü¥¿½Tªº¸ê®Æ¡AÁÂÁ¡I ¤é´ÁÅã¥Ü°ÝÃD.rar (1.93 KB)

¦^´_ 10# hcm19522


    ·PÁ h ¤jªº«ü¾É¡A¤£©T©wÄæ¼Æ¥i¥H¦]À³¥¼¨Óªº»Ý¨D¡AÁÂÁ¡I

TOP

http://blog.xuite.net/hcm19522/twblog/362661570
¥»ÃDµL©T©w¤GÄæ ÁÂÁ "­ã¤j" «ü¾É ½Ð°Ñ¦Ò

TOP

¦^´_ 5# hcm19522


    H¤j¦n¡A¨C­Ó¤H©T©w¨âÄæ¡A¬O§Ú¨S¦³ªí¹F²M·¡¡A¬O¤´­n«ö Sheet2 ­ì¨C­Ó¤H¤é´Á±Æ¦C¶¶§ÇÅã¥Ü¡AÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-12-4 22:21 ½s¿è

¦^´_ 6# hcm19522


=LOOKUP(ROW(1:9),IF(A1:A9<>"",ROW(1:9)),A1:A9)
=LOOKUP(ROW(1:9),ROW(1:9)/(A1:A9<>""),A1:A9)

{"QQ";"QQ";"QQ";"QQ";"WW";"WW";"WW";"WW";"WW"}

²Ä¤@®æ¤£¥iªÅ¥Õ~~

TOP

¦^´_ 4# ­ã´£³¡ªL


    ²a¤j¦n¡A«ü¾É¤½¦¡¸g´ú¸Õ¥i¨Ï¥Î¡A¨Ã±N¤½¦¡µy°µ­×§ï¡A²Å¦X­ì¤é´Á¥ª¥k¨âÄæÅã¥Ü¡AÁÂÁ¡I

TOP

½Ð±Ð "­ã¤j "
¨Ò A1=QQ   A2:A4=ªÅ¥Õ  A5=WW   A6:A9=ªÅ¥Õ
¦p¦óµ{¦¡¤Ï¥Õ «ö F9 §e²{ "QQ";"QQ";"QQ";"QQ;"WW";"WW";"WW";"WW";"WW"
µ{¦¡¸Õ«Ü¦h µLªk§e²{ ·P®¦

TOP

Sheet1!B3=IFERROR(INDIRECT("Sheet2!"&TEXT(RIGHT(SMALL(IF((IF(Sheet2!$A1:$H1="",Sheet2!$B1:$I1,Sheet2!$A1:$H1)=$A3)*(Sheet2!$B2:$I8<>""),ROW($B2:$I8)*100+COLUMN($B2:$I8)*10001),COLUMN(A1)),4),"!R0C00"),),"")
¶E¦C ¥k©Ô ¬O§_©T©w¢±Äæ

TOP

¢Ï¢³¡G¤U©Ô
=IF(ROW(A1)>COUNT(OFFSET(Sheet2!A$1,1,MATCH(A$3,Sheet2!$1:$1,)-1,99,2)),"",SMALL(OFFSET(Sheet2!A$1,1,MATCH(A$3,Sheet2!$1:$1,)-1,99,2),ROW(A1)))


©Î¡G
=LOOKUP("z",IF({1,0},"",TEXT(SMALL(OFFSET(Sheet2!A$1,1,MATCH(A$3,Sheet2!$1:$1,)-1,99,2),ROW(A1)),"yyyy/mm/dd")))

ª`·N¡G¦¹¤½¦¡¨ú¥Xªº¤é´Á¬°¡e¤å¦r®æ¦¡¡f


OFFSET(Sheet2!A$1,1,MATCH(A$3,Sheet2!$1:$1,)-1,99,2))¡@¬O°²³]¤é´Á¸ê®Æ³Ì¦h¢¸¢¸¦C¡A¥i¦Û¦æ½Õ¾ã¡ã¡ã

TOP

¦^´_ 2# ­ã´£³¡ªL

­ã¤j¦n¡A¨C­Ó¤Hªº¤é´Á³£¥u¦³¤GÄæ¡AÁÂÁ¡I

TOP

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