p212

¦^´_ 1# shan0948
¸Ñ1¡G°}¦C¤½¦¡
Àx¦s®æH8=OFFSET(E$2,LARGE(IF($A$3:$A$8=DATE(2014,8,1),ROW($A$3:$A$8),FALSE),1)-2,0)
¦V¥k½Æ»s

¸Ñ2¡G°}¦C¤½¦¡
Àx¦s®æH8=INDEX(E$3:E$8,LARGE(IF($A$3:$A$8=DATE(2014,8,1),ROW($A$3:$A$8),FALSE),1)-2)
¦V¥k½Æ»s
½Ð°Ñ¦Ò¡I

p212

¦^´_ 3# shan0948
1¡B¥i¦Ò¼{ª½±µÅܧó°}¦C¤½¦¡¤¤¬õ¦â³¡¤Àªº¦~¤ë¤À¤Î¤é´Á
Àx¦s®æH8=OFFSET(E$2,LARGE(IF($A$3:$A$8=DATE(2014,8,1),ROW($A$3:$A$8),FALSE),1)-2,0)
©Î
Àx¦s®æH8=INDEX(E$3:E$8,LARGE(IF($A$3:$A$8=DATE(2014,8,1),ROW($A$3:$A$8),FALSE),1)-2)

2¡B©ÎªÌ¦Ò¼{«ü©w¬Y¤@¯S©wÀx¦s®æ¬°¡u¤é´Á¡v¤ñ¹ï¼Ðªº¡A¦pÀx¦s®æH6 (©ó¦¹¿é¤J2014/8/1)¡A«h°}¦C¤½¦¡­×§ï¬°
Àx¦s®æH8=OFFSET(E$2,LARGE(IF($A$3:$A$8=$H$6,ROW($A$3:$A$8),FALSE),1)-2,0)
©Î
Àx¦s®æH8=INDEX(E$3:E$8,LARGE(IF($A$3:$A$8=$H$6,ROW($A$3:$A$8),FALSE),1)-2)
½Ð°Ñ¦Ò¡I
µù¡G°}¦C¤½¦¡½Ð¥HCtrl+Shift+Enter¿é¤J

p212

¦^´_ 3# shan0948
­Y¬O¸ê®Æµ§¼ÆÀH¤é´Á¤£Â_¼W¥[¥B­nÀH®É¬Ý¨ì³Ì·sªº¤é´Á¡A«h¥i­×§ï°}¦C¤½¦¡¬°
Àx¦s®æH8=OFFSET(E$2,LARGE(IF($A$3:$A$8=MAX($A$3:$A$8),ROW($A$3:$A$8),FALSE),1)-2,0)
©Î
Àx¦s®æH8=INDEX(E$3:E$8,LARGE(IF($A$3:$A$8=MAX($A$3:$A$8),ROW($A$3:$A$8),FALSE),1)-2)
½Ð°Ñ¦Ò¡I
µù¡G$A$3:$A$8½d³ò½Ð¦Û¦æ½Õ¾ã¡F©Î¥t¥H©w¸q¦WºÙ¤è¦¡³B²z¦¨°ÊºA½d³ò¡A¨Ò¦p©w¸q¦WºÙ¡u¤é´Á¡v¡A°Ñ·Ó¨ì¿é¤J=OFFSET(Sheet1!$A$3,,,COUNTA(Sheet1!$A:$A))¡C¤W¦C¤½¦¡$A$3:$A$8³¡¤À­×§ï¬°¡u¤é´Á¡v¡C