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

[µo°Ý] ¦p¦ó»s§@¤£­«½Æªº¸ê®ÆÅçÃÒ¤U©Ô¿z¿ï²M³æ¡H

[µo°Ý] ¦p¦ó»s§@¤£­«½Æªº¸ê®ÆÅçÃÒ¤U©Ô¿z¿ï²M³æ¡H

KÄæ¤W¬O¦³¸ê®Æ¤£Â_¿é¤Jªº¤é´Á¡A±q³oÄ椤¨ú¥X¦~¥÷¡A¦bA2©MA3®æ¤W»s§@¤@­Ó¤£­«½Æªº¸ê®ÆÅçÃÒ¤U©Ô¿z¿ï²M³æ¡H
ÁÂÁ¡I

ABook1.rar (15.7 KB)

¦^´_ 1# maiko

°Ñ¦Ò¬Ý¬Ý
ABook1.zip (14.91 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-1 10:49 ½s¿è

¦^´_ 1# maiko
¥H½d¨ÒÀÉ ABook1.xlsx¸ê®ÆÄæ¦ì°t¸m¸Ñ
1¡B©w¸q¦WºÙ¡uDate¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J =YEAR(OFFSET(Sheet1!$K$2,,,COUNTA(Sheet1!$K:$K)-1))¡C
2¡BÀx¦s®æS1¿é¤JÄæ¦ì¦WYear¡AÀx¦s®æS2¿é¤J°}¦C¤½¦¡ =IFERROR(INDEX(Date,MATCH(0,COUNTIF($S$1:S1,Date),0)),"")¡A¦V¤U½Æ»s¤½¦¡¡C
3¡B©w¸q¦WºÙ¡uYear¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J=OFFSET(Sheet1!$S$2,,,COUNT(Sheet1!$S:$S))¡A¦A©óÀx¦s®æA2»s§@²M³æ¡A²M³æ¡u¨Ó·½¡v¿é¤J =Year¡C
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 1# maiko
Sorry! ¸Éªþ¥ó

ABook1_°Ñ¦Ò.zip (16.99 KB)

TOP

S2 =YEAR(LOOKUP(DATE(IF(N(S1),S1,YEAR(MAX(K:K))),1,0),K:K))
¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-1 16:19 ½s¿è

¦^´_ 5# ML089
½Ð±ÐML089¤j¤j
¨Ì­ì°Ý½d¨ÒÀÉ¡A¥H¤@¯ë¤½¦¡ S2=YEAR(LOOKUP(DATE(IF(N(S1),S1,YEAR(MAX(K:K))),1,0),K:K)) ¤U©Ô½Æ»s¤½¦¡
Àx¦s®æS2¡BS3¡BS4¹Bºâµ²ªG¤À§O¬°2013¡B2012¡B#N/A¡A¦Ó¤£¬°2014¡B2013¡B2012¡A½Ð°Ý°ÝÃD¥X¦b­þ¨à¡H
­Y±NS2§ï¦¨=YEAR(LOOKUP(DATE(IF(N(S1),S1,YEAR(MAX(K:K))+1),1,0),K:K)) ¡A¦ü¥G¥i¥H±o¨ì¥¿½Tµª®×¡C
·q½Ð«ü¾É¡AÁÂÁ¡I

TOP

¦^´_ 6# p212

¤p¥¢»~¡A¤½¦¡½Ð­×¥¿
=IF(N(S1),YEAR(LOOKUP(DATE(S1,1,0),K:K)),YEAR(MAX(K:K)))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁ¦U¦ì¤j¤j¡A¤w¸g¸Ñ¨M¤F¡AÁÂÁ½ç±Ð¡C

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD