p212

¦^´_ 9# rszo
©Ó10#¦^ÂÐ_±z­nªºµª®×¬O¡u13¡v¦Ó¤£¬°¡u14¡v¡H
½Ð°Ñ¦Ò¤U¦C§@ªk
1¡B½Ð¦b9#½d¨ÒÀɤ§D2Àx¦s®æ¿é¤J
=IF(C2="","",IF(DATEDIF(B2,$A$2,"ym")=0,INT(YEARFRAC(B2,$A$2)-1),INT(YEARFRAC(B2,$A$2))))
¦V¤U½Æ»s¤½¦¡
2¡B½Ð¦b9#½d¨ÒÀɤ§J2Àx¦s®æ¿é¤J
=IF(I2="","",IF(DATEDIF(H2,$G$2,"ym")=0,INT(YEARFRAC(H2,$G$2)-1),INT(YEARFRAC(H2,$G$2))))
¦V¤U½Æ»s¤½¦¡

rszo

¦^´_ 10# p212


  ÁÂÁ±zªá®É¶¡¡A¦b¦¹§Ú¦A¾ã²z¤@¤U¡A§K±o¤j®a¬Ý±oÀYµh
¦bstart_date¬°1999/7/13¥Bend_date¬°2013/7/31®É¡A¨Ï¥ÎYEARFRAC¨ç¼Æºë·Ç¦a­pºâ¸Ó¤G¤é´Á¬Û®t¬°¡u14.05¡v¦~¤§±¡ªp¤U¡A
±z­nªºµª®×¬O¡u13¡v¦Ó¤£¬°¡u14¡v¡HA:§Ú§Æ±æ±o¨ìªºµª®×¬°13

§Ú§Æ±æ¦bC¦C¤¤­Y¬O"¤ë¥÷¬°0"ªº¸Ü«hD¦C¿é¥X¦~¥÷-1,¦ýC¦C¦~¥÷­Y¬°1ªº±¡ªp¤UD¦C¿é¥X¦~¥÷¤£-1,¤@¤Á±¡ªp¬Ò¶·C¦C¤¤¦~¥÷>1 and ¤ë¥÷¬°0 ªº±¡ªp¤U¤~°õ¦æ.

§Ú¥Ø«eªºÅÞ¿è¬O³o¼Ë(¤£ª¾¹D¦³¨S¦³ÅÞ¿è¿ù»~ÁÙ¬O¤Ó½ÆÂø¤Æ):
IF N¦~0¤ë®É N=0«hN=0 §_«h IF N¦~0¤ë®ÉN=1.«hN=1§_«hIF N¦~0¤ë®É N-1§_«hN=N
¤£¬O«Ü¼ôifªº¥Îªk¡A¹Á¸Õ¦n¦h¦¸³£¨S¿ìªk¥´¥X§¹¾ãªº¤½¦¡ ½Ð¦U¦ì°ª¤âÀ°¦£¸Ñ´b

¸Ô±¡ªþ¥ó:

test.zip (8.23 KB)

javascript:;
¤U¸ü¦¸¼Æ: 2

2013-8-26 16:53

rszo

¦^´_ 11# p212


¦A¦¸ªº·PÁ±z!¥H¦¹¤½¦¡¤U¥h¶]¥i¥H°õ¦æ
¤£¹L12#¤¤·s¼Wªºªþ¥ó¤º1¦~0­Ó¤ëªº±¡ªp¤U¤£·Q­n¦~¥÷-1ªº¸Ü ³o­n«ç¼Ë¼W¥[ifÁÙ¬O¨ä¥L¨ç¼Æ¤U¥hRUN©O?

p212

¦^´_ 13# rszo
¥H12#´£¨Ñªºªþ¥óÀɭק綠¦¡
½Ð¦bD3Àx¦s®æ¿é¤J
=IF(C3="","",IF(AND(DATEDIF(B3,A3,"y")>1,DATEDIF(B3,A3,"ym")=0),INT(YEARFRAC(B3,A3)-1),INT(YEARFRAC(B3,A3))))

rszo

¦^´_ 14# p212


    ·PÁ±zªº¦^´_!¥H±zµ¹ªº¤½¦¡¤U¥h¥i¥H¶¶§Q°õ¦æ
§Ú²{¦b«h¦b´ú¸Õ¦³¨S¦³¬Æ»ò¯S©w¤é´Á·|¾É­P¦³»~®tªº±¡ªpµo¥Í.
¬Ý¨Ó§ÚªºÅÞ¿è·§©ÀÁÙ¤Ó®t¤F»Ý­n¦A¥[±j..

rszo

¦^´_ 14# p212


­è´ú¸Õ¤F¤@¤U¡A¦n¹³¦brunªº®É­Ô­Y¬OA»PB¦C¤é´Á¥u¬Û®t1¤Ñªº¸Ü
·|Åܦ¨:
            A                           B                        C                       D
  2013/7/31         2011/8/1          1¦~11­Ó¤ë            2
³o±¡ªp¬O¤£¬O¦]¬°¤é´Á¤Ó±µªñ©Ò¥H¤p¼ÆÂIµL­­Áͪñ©ó0.999999=1©O?

p212

¦^´_ 16# rszo
1¡BYEARFRAC(start_date,end_date,basis)¨ç¼Æ¥Î¥Hºë·Ç¦a­pºâ¨â­Ó¤é´Á¬Û®t¦h¤Ö¦~(§t¤p¼Æ)
¨ä¤¤basis«Y«ü©Ò±Ä¥Îªº¤é­p§Q°òªºÃþ«¬¡A­YµL¯S®í»Ý¨D¡A«Øijbasis±Ä¥Î0©Î¬Ù²¤§Y¥i¡C
½Ð¨Ì±zªº»Ý¨D¿ï¾Übasis­×§ï
basis-----------¤é­p§Q°òªºÃþ«¬
0©Î¬Ù²¤--------US (NASD) 30/360
1---------------¹ê»Ú¤Ñ¼Æ/¹ê»Ú¤Ñ¼Æ
2---------------¹ê»Ú/360
3---------------¹ê»Ú/365
4---------------¼Ú¬w 30/360

2¡Bbasis±Ä¥Î¡u1¡v¥i¯à¸û¶Kªñ±zªº»Ý¨D¡A©Ó13#¤§D3Àx¦s®æ¤½¦¡­×§ï°Ñ¦Ò¦p¤U¡C
=IF(C3="","",IF(AND(DATEDIF(B3,A3,"y")>1,DATEDIF(B3,A3,"ym")=0),INT(YEARFRAC(B3,A3,1)-1),INT(YEARFRAC(B3,A3,1))))