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

[µo°Ý] ½Ð±Ð¦p¦ó¹ï¤ñ¬d¸ß¦hµ§¤é´Á¸ê®Æ¡A¨ú¥X¬Û²Å¤é´Áªº¯S©w¨ê¥d¸ê®Æ

[µo°Ý] ½Ð±Ð¦p¦ó¹ï¤ñ¬d¸ß¦hµ§¤é´Á¸ê®Æ¡A¨ú¥X¬Û²Å¤é´Áªº¯S©w¨ê¥d¸ê®Æ

¥»©«³Ì«á¥Ñ jackson7015 ©ó 2014-8-19 09:11 ½s¿è

½Ð°Ý¯¸¤Wªº¤j¤j­Ì
¦p¦ó¹ï¤ñ¬d¸ß¬Û²Å¤é´Á«á¡A±a¥X¬Û²Å¤é´Áªº¤¤¤È12ÂI¥H«eªº³Ì«á¤@µ§¨ê¥d¸ê®Æ

¦]¬°¨C¤éªº¨ê¥d®É¶¡¦³³\¦h¬q¡A¦Ó¥B©Ò»Ýªº¤é´Á«D©T©w¤é´Á
©Ò¥H·Q½Ð±Ð¦U¦ì¤j¤j¸Ó¦p¦ó½s¼g¦¹ºØ¤½¦¡¨ç¼Æ?

³ø¤ä³æ.rar (25.85 KB)

¥»©«³Ì«á¥Ñ p212 ©ó 2014-8-19 10:50 ½s¿è

¦^´_ 1# jackson7015
1¡B©w¸q¦WºÙ
(1)©w¸q¡u¤é´Á¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=¤u§@ªí!$A$2:$A$23
(2)©w¸q¡u®É¶¡¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=¤u§@ªí!$B$2:$B$23
µù:(1)»P(2)¥i¥Îª½±µ¿ï¨ú¤u§@ªí!$A$1:$B$23½d³ò¡A¦A«öCtrl+Shift+F3¡A¤Ä¿ï¡u³»ºÝ¦C¡v©w¸q¦WºÙ¡C
(3)´å¼Ð°±©ó¡u(³æ±i)¡v¤u§@ªí¤§Àx¦s®æF8¶i¦æ©w¸q¦WºÙ¡uDate¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=YEAR(' (³æ±i)'!$C8)-1911&TEXT(MONTH(' (³æ±i)'!$C8),"00")&TEXT(DAY(' (³æ±i)'!$C8),"00")
2¡BÀx¦s®æF8¿é¤J
=INDEX(®É¶¡,MATCH(--Date,¤é´Á,0)+COUNTIF(¤é´Á,Date)-1)
½Ð°Ñ¦Ò¡I

³ø¤ä³æ_2.zip (24.58 KB)

TOP

¦^´_ 2# p212

·PÁÂp212¤j¤jªº«ü¾É
ªí®æ¤½¦¡¹B§@¥¿±`
·Q½Ð±Ð¬O§_¯à¤£¨Ï¥Î¦WºÙºÞ²z­ûªº¤è¦¡°µ¹Bºâ?
¦]¬°³ø¤ä³æªºªí®æ¦³¼Æ¤Q¥÷¡A©Ò¥H¦b°µ¦WºÙºÞ²zªº®É­Ô·|¼Ð¥Ü§ó¦h¦WºÙ¡A¦³®É­Ô·|¦³ÂI²V¶Ã

·Q½Ð°Ý¬O§_¦³³æ¤@Àx¦s®æ¤½¦¡½s¼gªº¤è¦¡¡Aª½±µ¹Bºâ¨ú±o¼Æ­È?

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-8-20 09:08 ½s¿è

¦^´_ 3# jackson7015
¡u(³æ±i)¡v¤u§@ªí¤§BÄæ»PCÄ檺Àx¦s®æ®æ¦¡¥i¥H§ï¥Î¡u³q¥Î®æ¦¡¡v¡H¦p¦¹¥iÁקK³z¹L©w¸q¦WºÙ(²¤Æ¤½¦¡)Âà´«­ì¦³CÄ檺¸ê®Æ®æ¦¡¡C
¨Ò¦p¡G
(1)¥ý¡u(³æ±i)¡v¤u§@ªí¤§BÄæ»PCÄ檺Àx¦s®æ®æ¦¡§ï¦¨¡u³q¥Î®æ¦¡¡v
(2)¦A±N¡u(³æ±i)¡v¤u§@ªí¤§Àx¦s®æF3¤½¦¡¨Ì2#ªº=INDEX(®É¶¡,MATCH(--Date,¤é´Á,0)+COUNTIF(¤é´Á,Date)-1)¬[ºc­×§ï¬°
=INDEX(¤u§@ªí!$B$2:$B$23,MATCH(--(LEFT($I$4,3)&TEXT(B8,"00")&TEXT(C8,"00")),¤u§@ªí!$A$2:$A$23,0)+COUNTIF(¤u§@ªí!$A$2:$A$23,LEFT($I$4,3)&TEXT(B8,"00")&TEXT(C8,"00"))-1)
¦p¦¹¥i±N©w¸q¦WºÙªº¡u¤é´Á¡v¡B¡u®É¶¡¡v»P¡uDate¡v§R°£¡C
ª`·N¡I¡u(³æ±i)¡v¤u§@ªí¤§Àx¦s®æI4¤º®e­n²M°£¥ªºÝªÅ¥Õ¤è¥i¥¿½T¦^À³µª®×
½Ð°Ñ¦Ò¡I

³ø¤ä³æ_3.zip (24.69 KB)

TOP

¦^´_ 4# p212

·PÁÂp212¤j¤jªº¤£§[À°¦£
¤½¦¡­×§ïªº®t¤£¦h¤F
¦Û¤v­n­×§ï¤½¦¡ªº³¡¤À³Ñ¤U¡A¡u(³æ±i)¡v¤u§@ªí¤§BÄæ»PCÄ檺Àx¦s®æ®æ¦¡§ï¦¨¡u³q¥Î®æ¦¡¡v
¦]¬°B¡BCÄæ¦ì¬O±q¨ä¥Lªí®æ½Æ»s¹L¨Óªº¡A­Y¥u½Æ»s¡u­È¡vªº¸Ü¡A·|§e²{¤é´Á½X
©Ò¥H¦A¨Ó´N¬O¦Û¤v·Q¿ìªk§â¤j¤j´£¨Ñªº¤½¦¡¡A§âTEXTªº¨ú±o­È´«ºâ¦¨¤é´Á®æ¦¡¤F

¦A¦¸·PÁ¤j¤jªºÀ°¦£~
·P¿E¤£ºÉ~

TOP

¤½¦¡­×¥¿«á¡A¥u§ïCÄ泡¤Àªº¤½¦¡§Y¥i
=INDEX(¤u§@ªí!$B$2:$B$23,MATCH(--(LEFT($I$4,3)&TEXT(B8,"00")&TEXT(C8,"DD")),¤u§@ªí!$A$2:$A$23,0)+COUNTIF(¤u§@ªí!$A$2:$A$23,LEFT($I$4,3)&TEXT(B8,"00")&TEXT(C8,"DD"))-1)

TOP

¦^´_ 6# jackson7015


F8¤½¦¡
=LOOKUP(2,1/((OFFSET(¤u§@ªí!$A$2,,,COUNT(¤u§@ªí!A:A),)=--TEXT(C8,"emmdd"))*(OFFSET(¤u§@ªí!$B$2,,,COUNT(¤u§@ªí!A:A),)<0.5)),OFFSET(¤u§@ªí!$B$2,,,COUNT(¤u§@ªí!A:A),))
¦V¤U½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh
·PÁÂHsiehªO¥Dªº±Ï´©
¤½¦¡¹Bºâ¬Ò¥¿±`
¤£¹L¥i¯à­n¬ã¨s¤@¬q®É¶¡¤F¡A¸Ì­±¦³¨Ç¤£¤ÓÀ´ªº¹Bºâ²Å¸¹
¨Ò¦p--TEXTªº--¤£¾å±o¬O°µ¬°¤°»ò¥Î³~ªº

ÁÂÁª©¥Dªº¨ó§U

TOP

¦^´_ 8# jackson7015

Âন¼Æ­È
­t­t±o¥¿
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh
Hsiehª©¤j±z¦n¡F
·Q½Ð°Ý¤½¦¡¤¤¡A¦b´£¨ú31¤éªº®É¶¡©Î³Ì«á¤@¤éªº®É¶¡¡A¦³®É·|¦³¿ù»~
¬O­n­×¥¿­þ­Ó³¡¤À¶Ü?
¬¡­¶Ã¯1.rar (18.94 KB)

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD