u7490074

¦^´_  u7490074

ÀɮקڬݤF¡A¦ý¨S¦³¬Ý¨ì¨Ï¥Î INDIRECT("M"&ROW())ªº¥Øªº¡AµL±q¤F¸Ñ¤Î»¡©ú¡A¬O§_¦b¦A¸É¥R ...
ML089 µoªí©ó 2014-10-1 09:05

INDIRECT("M"&ROW())©ÎINDIRECT("b"&ROW())©ÎINDIRECT("c"&ROW())...µ¥ : §¡¬O¦Ò¶q¦bN13Àx¦s®æ««ª½¤U©Ô®É¡A¦C¸¹·|ÅÜ¡A¤~¥Î³o¼Ëªº¤g¤è¦¡°Õ~

ML089

¦^´_ 14# ML089


    14¼Ó¤½¦¡¨Ï¥Î°}¦C¤è¦¡¤ñ¸ûºC¤@¨Ç¡Aª½±µ¼Æ¾Ç­pºâ¤ñ¸û§Ö

N13
=ROUND($H13/($C13-$B13)*(MAX(0,MIN($C13,DATE(YEAR($B13),N$12+1,1))-$B13)),) - SUM($M13:M13,-$M13)

¤½¦¡ = ¥|±Ë¥î¤J¦Ü¾ã¼Æ( Á`§Q®§/Á`¤Ñ¼Æ * (°_¤é´Á¦Ü¦U¤ë©³¤Ñ¼Æ) ) - «e­±ªº§Q®§¦X­p

ML089

¦^´_ 12# u7490074


    AG13ÅçÃÒ¦X­p³o¼Ë¼g·|¦³°ÝÃD¡A¦]¬°EXCELÀx¦s®æ¥i¥H®æ¦¡¤Æ¬°¾ã¼Æ¦ý¨ä¹ê¬O¦³§t¤p¼Æªº¼Æ¦r¡A¥ÎSUM(....)ªºÁ`¼Æ·|¹ï¡A¦ý¥Î²´·ú¬Ý¥Î­pºâ¾÷­pºâ´N¬O·|¤£¤@¼Ë¡C

±N =SUM(N13:AE13)
§ï¬° =SUMPRODUCT(ROUND(N13:AE13,0))

ML089

¦^´_ 12# u7490074
N13
=IF(AND(N$12<=12,DATE(YEAR($B13),MONTH($B13)+1,)=DATE(YEAR($B13),N$12+1,)),ROUND($H13*($L13/$G13),0),IF(AND(N$12<=12,DATE(YEAR($C13),MONTH($C13)+1,)=DATE(YEAR($B13),N$12+1,)),ROUND($H13*($M13/$G13),0),IF(AND(N$12>12,DATE(YEAR($C13),MONTH($C13)+1,)=DATE(YEAR($B13)+1,(N$12-12)+1,)),ROUND($H13*($M13/$G13),0),0)))+IF(AND(N$12<=12,DATE(YEAR($B13),MONTH($B13)+1,)<DATE(YEAR($B13),N$12+1,),DATE(YEAR($B13),N$12+1,)<DATE(YEAR($C13),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($C13),N$12+1,))/$G13),0),IF(AND(N$12>12,DATE(YEAR($B13),MONTH($B13)+1,)<DATE(YEAR($B13)+1,(N$12-12)+1,),DATE(YEAR($B13)+1,(N$12-12)+1,)<DATE(YEAR($C13),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($B13)+1,(N$12-12)+1,))/$G13),0),0))

N13¤½¦¡¥i¥H²¤Æ¡A¥u»Ý­n °_¤é´Á/¨´¤é´Á/§Q®§Á`¶O¥Î¤Î¤ë¼Æµ¥¸ê®Æ´N¥i¥H­pºâ

=ROUND($H13/($C13-$B13)*FREQUENCY(ROW(INDIRECT($B13&":"&$C13-1)),DATE(YEAR($B13),N$12+1,)),) - SUM($M13:M13,-$M13)

¨C¤ë§Q®§¶O¥Î·|¥|±Ë¥î¤J¡A»~®t²Ö¿n¶W¹L1¤¸®É¸Ó¤ë·|¼W¥[1¤¸

ML089

¦^´_ 12# u7490074

ÀɮקڬݤF¡A¦ý¨S¦³¬Ý¨ì¨Ï¥Î INDIRECT("M"&ROW())ªº¥Øªº¡AµL±q¤F¸Ñ¤Î»¡©ú¡A¬O§_¦b¦A¸É¥R¤@¤U
¨Ï¥Î­ì¤½¦¡¤¤¨Ï¥Î INDIRECT("M"&ROW()) ³oºØ¤èªk¤Ó½ÆÂø

§A¥i¥H±N­ì¥ý¤½¦¡¤¤»Ý­nªº6­ÓÀx¦s®æ¤À§O¥Î¤Þ¥Î
R1 = 13
R2 = INDIRECT("B"&R1)
R3 = INDIRECT("C"&R1)
R4 = INDIRECT("G"&R1)
R5 = INDIRECT("H"&R1)
R6 = INDIRECT("L"&R1)
R7 = INDIRECT("M"&R1)
¦A±NR2~R7±a¤J¤½¦¡¤¤
§A¤]¥i¥H¥ý½Æ»sªí®æ¡A¥Î °Å¤U/¶K¤W ªº¤è¦¡¨Ó²¾°Ê B13¡BC13...L13¡BM13¡A¤½¦¡´N¯à¦Û°Ê§ïÅÜ¡A¦A±N¤½¦¡¤º®e½Æ»s§A­nªº¦a¤è¡C

u7490074

ÀÉ®×Äæ¦ìN~AE¡G¬Oºâ¶O¥Î¤ÀÅuªº¤ë¥÷(ºâÀY¤£ºâ§À)¡A¶W¹L12(§YYÄæ)¡A´Nºâ¬O¹j¦~(¥»°Ó«~³Ì¦hµo¦æ6­Ó¤ë¡A©Ò¥H¤~¦³ ...
u7490074 µoªí©ó 2014-9-30 15:30

¹ï¤F,¦]¬°³o¼Ëªøªº¤½¦¡¡öµLªG¦A¥[¤WINDIRECT("M"&ROW())«h¦]¬°¤½¦¡¤Óªø·|³QEXCEL©Úµ´¦A­×§ï~~~§xÂZ¥B¤£¦n½s­×~~


¡iR13Àx¦s®æ¡j
=IF(AND(R12<=12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)),ROUND($H13*($L13/$G13),0),IF(AND(R12<=12,DATE(YEAR(INDIRECT("$c"&ROW())),MONTH(INDIRECT("$c"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)),ROUND($H13*($M13/$G13),0),IF(AND(R12>12,DATE(YEAR(INDIRECT("$C"&ROW())),MONTH(INDIRECT("$C"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,)),ROUND($H13*($M13/$G13),0),0)))+IF(AND(R12<=12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)<DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,),DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)<DATE(YEAR(INDIRECT("$C"&ROW())),MONTH(INDIRECT("$C"&ROW()))+1,)),ROUND($H13*(DAY(DATE(YEAR(INDIRECT("$C"&ROW())),R12+1,))/$G13),0),)+IF(AND(R12>12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)<DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,),DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,)<DATE(YEAR(INDIRECT("$C"&ROW())),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($B13)+1,(R12-12)+1,))/$G13),0),0)

u7490074

ÀÉ®×Äæ¦ìN~AE¡G¬Oºâ¶O¥Î¤ÀÅuªº¤ë¥÷(ºâÀY¤£ºâ§À)¡A¶W¹L12(§YYÄæ)¡A´Nºâ¬O¹j¦~(¥»°Ó«~³Ì¦hµo¦æ6­Ó¤ë¡A©Ò¥H¤~¦³13¤ë~18¤ë)
==>³æ¤@Àx¦s®æ¤½¦¡¤Ó¦h¡AÀˮ֫ܨ¯­W·|º|¡A¥B¨C´«¤@¦C¡A°lºî¦C´N­n°ÊºAÅÜ°Ê

Àɮצp¤Uºô§} : ­n¤U¸ü¨ì­Ó¤H¹q¸£¤~¯à¬Ý~
https://docs.google.com/file/d/0BytNKnm2laTvQXFaQUE2bTNRRlNXMThwQjFFOVlNR0gzTFF3/edit?usp=sharing

ML089

¦^´_ 9# u7490074

¬O¤@©w­n¥ÎINDIRECT("F"&ROW())¤~¯à±o¨ìF18Àx¦s®æªº­È¶Ü?  »P¦bA18Àx¦s®æ¿é¤J=F18¼gªk¡A­n¦h¤£¤Ö»yªk¹ï¶Ü.

A18 =F18 ·íµM¤ñ A18 =INDIRECT("F"&ROW()) ²³æ¦h¤F
¨C­Ó¨ç¼Æ¦U¦³¨Ï¥Î®É¾÷¡C¥Ø«e¬O¬Ý¤£¥X¨Ó§A­n«ç»ò¥Î¡A¦³ÀÉ®×»¡©ú¤ñ¸û®e©ö¤F¸Ñ§A­n¤°»ò?


§AªºT2¡BU2¤½¦¡¤£ÅÜ¡A¨äµ²ªG¤Þ¥Î¦^¨Ó´N¥i¥H

u7490074

¦^´_  u7490074

²q²q¬Ý¡A¬O¤£¬O³o¼Ë
=SUM(INDIRECT("Y"&ROW()&"¡GZ"&ROW()))
ML089 µoªí©ó 2014-9-29 21:40

¬O¥i¥H¥Î¡A¦ý¦pªG³æ¤@Àx¦s®æ¥´¤J="F"&ROW()   .......°²³]¦b²Ä18¦C   «h±o¨ì¬OF18¡A
¬O¤@©w­n¥ÎINDIRECT("F"&ROW())¤~¯à±o¨ìF18Àx¦s®æªº­È¶Ü?  »P¦bA18Àx¦s®æ¿é¤J=F18¼gªk¡A­n¦h¤£¤Ö»yªk¹ï¶Ü.......¦]¬°§Ú¦³¦h§P§O¦¡¡A©Ò¥H¤@­ÓÀx¦s®æ¤º©ñ¦nªøªº»yªk¡A¦p¤U¡A¦³¤èªk¯à¦Aºë²or§ó²©úªº»yªk¶Ü?

¡iÀx¦s®æA~L¤º®e¡j
°_©l¦~«×                   ²×¨´¦~«×                                  ³æ¾Ú½s¸¹               ­ì¥»ª÷             §Q²v        Á`¤Ñ¼Æ        Á`§Q®§¶O¥Î     Á`¤ë¼Æ           °_¤ë      ¨´¤ë        °_¤ë¤Ñ¼Æ  ¨´¤ë¤Ñ¼Æ
2014¦~08¤ë04¤é        2014¦~10¤ë06¤é        40C000800        30,000,000         1.080%        63         55,920                 3               8         10         28         5

¡iÀx¦s®æM~AD¤º®e¡j
1        2        3        4        5        6        7        8        9        10        11        12        13        14        15        16        17        18
0         0         0         0         0         0         0         24,853         26,629         4,438         0         0         0         0         0         0         0         0

¡iÀx¦s®æT2¤º®e¡j
¨ä¤¤¡A8¤ë¥÷=24853¤¸=IF(AND(U2<=12,DATE(YEAR($B3),MONTH($B3)+1,)=DATE(YEAR($B3),U2+1,)),ROUND($H3*($L3/$G3),0),IF(AND(U2>12,DATE(YEAR($B3),MONTH($B3)+1,)=DATE(YEAR($B3),(U2-12)+1,)),ROUND($H3*($L3/$G3),0),IF(AND(U2<=12,DATE(YEAR($C3),MONTH($C3)+1,)=DATE(YEAR($B3),U2+1,)),ROUND($H3*($M3/$G3),0),IF(AND(U2>12,DATE(YEAR($C3),MONTH($C3)+1,)=DATE(YEAR($B3)+1,(U2-12)+1,)),ROUND($H3*($M3/$G3),0),0))))+IF(AND(U2<=12,DATE(YEAR($B3),MONTH($B3)+1,)<DATE(YEAR($B3),U2+1,),DATE(YEAR($B3),U2+1,)<DATE(YEAR($C3),MONTH($C3)+1,)),ROUND($H3*(DAY(DATE(YEAR($B3)+1,(U2-12)+1,))/$G3),0),IF(AND(U2>12,DATE(YEAR($B3),MONTH($B3)+1,)<DATE(YEAR($B3)+1,(U2-12)+1,),DATE(YEAR($B3)+1,(U2-12)+1,)<DATE(YEAR($C3),MONTH($C3)+1,)),ROUND($H3*(DAY(DATE(YEAR($B3)+1,(U2-12)+1,))/$G3),0),0))

¡iÀx¦s®æU2¤º®e¡j
¨ä¤¤¡A9¤ë¥÷=26629¤¸=IF(AND(V2<=12,DATE(YEAR($B3),MONTH($B3)+1,)=DATE(YEAR($B3),V2+1,)),ROUND($H3*($L3/$G3),0),IF(AND(V2>12,DATE(YEAR($B3),MONTH($B3)+1,)=DATE(YEAR($B3),(V2-12)+1,)),ROUND($H3*($L3/$G3),0),IF(AND(V2<=12,DATE(YEAR($C3),MONTH($C3)+1,)=DATE(YEAR($B3),V2+1,)),ROUND($H3*($M3/$G3),0),IF(AND(V2>12,DATE(YEAR($C3),MONTH($C3)+1,)=DATE(YEAR($B3)+1,(V2-12)+1,)),ROUND($H3*($M3/$G3),0),0))))+IF(AND(V2<=12,DATE(YEAR($B3),MONTH($B3)+1,)<DATE(YEAR($B3),V2+1,),DATE(YEAR($B3),V2+1,)<DATE(YEAR($C3),MONTH($C3)+1,)),ROUND($H3*(DAY(DATE(YEAR($B3)+1,(V2-12)+1,))/$G3),0),IF(AND(V2>12,DATE(YEAR($B3),MONTH($B3)+1,)<DATE(YEAR($B3)+1,(V2-12)+1,),DATE(YEAR($B3)+1,(V2-12)+1,)<DATE(YEAR($C3),MONTH($C3)+1,)),ROUND($H3*(DAY(DATE(YEAR($B3)+1,(V2-12)+1,))/$G3),0),0))

ML089

¦^´_ 7# u7490074

²q²q¬Ý¡A¬O¤£¬O³o¼Ë
=SUM(INDIRECT("Y"&ROW()&"¡GZ"&ROW()))