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

[µo°Ý] offset»P©w¸q¦WºÙ

[µo°Ý] offset»P©w¸q¦WºÙ

¥»©«³Ì«á¥Ñ Bodhidharma ©ó 2013-4-10 02:08 ½s¿è

³Ìªñ¦b¬ã¨s©w¸q¦WºÙªº­ì²z
¦]¦¹¨Ï¥Î³Ì¤j¤l§Ç¦C(maximum subarray)¨Ó§@¬°´ú¸Õ
(°Ñ¦Ò¡Ghttp://emn178.pixnet.net/blog/post/88907691)
·Q­n¥Îexcel¤½¦¡¨Ó¹ê²{Kadane'sºtºâªk¡G
(³o­Ó¥Îµ{¦¡¼g«Ü²³æ¡Aª½±µ¥Îexcel¤½¦¡´NÅܱo¶W½ÆÂø¡A¤£¹L§Ú¥D­n¬O¬°¤F¬ã¨s©w¸q¦WºÙ»Poffset¨ç¼Æ¡A©Ò¥H¨è·N¬D³o­Ó¨Ò¤l)


AÄ欰input_array¡A±ý­pºâ¸Óarrayªº³Ì¤j¤l§Ç¦C
BÄ欰¼ÒÀÀKadane's ºtºâªk

°ÝÃD1¡GBÄæ¨Ï¥Î©w¸q¦WºÙtolast´N¥i¥H¥¿±`¹B§@¡A¦ý¬O¦pªG¤£¥Î©w¸q¦WºÙ¡A¹³GÄætolastªº³¡¤À´N·|¥X¿ù¡A·Q½Ð±Ð®t§O¦b­þ¡H
°ÝÃD2¡GBÄ椤¦³¨Ï¥Îoffset¨ç¼Æ¨Ó°ÊºA§ì¨ú¸ê®Æ¡A¦b³oºØ±¡ªp¤U¡A°²³]§Ú§Æ±æ±N¤§Åܦ¨©w¸q¦WºÙ¡AµM«á¤£¨Ï¥Î¥ô¦ó»²§UÄæ´N¯à¹F¨ìD1ªº®ÄªG¡A·Q½Ð±Ð­n«ç»ò¼g¡H(³Ì¦n¬OAÄ檺input_array¤]¥i¥H°ÊºA¨ú±o½d³ò¡A¤£ºÞªø«×¬°¦ó³£¯à¾A¥Î)

maximum subarray.rar (6.94 KB)

¦^´_  ML089

=MAX(SUBTOTAL(9,OFFSET(A$1,ROW()-1,,-ROW(INDIRECT("1:"&ROW())))))
°ò¥»¤W¬Ohttp://em ...
Bodhidharma µoªí©ó 2013-4-10 23:34


¤½¦¡¦³8°éªº­­¨î¡A¤Ó¦h§PÂ_¤£§Q©ó¤½¦¡µo®i¡A¼É¤Oªk¬O°}¦C¤½¦¡³Ì±`¥Îªº¤èªk
Kadane'sºtºâªk§ÚÁÙ¨S¦³®É¶¡¬Ý¡Aµ¥¨ì©P¥¼¦³®É¶¡¦A¨Ó¬ã¨s
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# Hsieh

³o­Ó¥u³B²z¨ìA1:{A2,A3...An}¤¤³Ì¤jªº¤l§Ç¦C
¨S¦³³B²zA2:{A3,A4...An},A3:{A4,A5,...An}...ªº³Ì¤j¤l§Ç¦C

TOP

¦^´_ 6# ML089

=MAX(SUBTOTAL(9,OFFSET(A$1,ROW()-1,,-ROW(INDIRECT("1:"&ROW())))))
°ò¥»¤W¬Ohttp://emn178.pixnet.net/blog/post/88907691¤¤ªº¡u§ï¨}¦¡¼É¤Oªk¡v
¤]´N¬O²Ä¤@¦C¦^¶ÇA1¤¤³Ì¤jªº
²Ä¤G¦C¦^¶Ç{A1:A2,A2}³Ì¤jªº
²Ä¤T¦C¦^¶Ç{A1:A3,A2:A3,A3}³Ì¤jªº
²Än¦C¦^¶Ç{A1:An,A2:An,...An-1:An}¤¤³Ì¤jªº
µM«á¦A¨ú³on¦C³Ì¤jªº¡A§Y¬O¡u³Ì¤j¤l§Ç¦C¡v

³oÁöµM¤£¬OKadane'sºtºâªk¡A¦ý¬Oµ²ªG·|¤@¼Ë
(¦Ó¥B¦]¬°excel¨S¦³¿ìªkª½±µ³]ÅܼơA©Ò¥H§Ú¥Î«Ü¸Þ²§ªº¤è¦¡¼ÒÀÀªºKadane'sºtºâªk¡A¦]¦¹®É¶¡½ÆÂø«×¤]¤£·|¤ñ¸û¦n)

§Úªº(­ì¤å¤¤²Ä¤G­Ó)°ÝÃD¬O¡G¦p¦ó¤£¨Ï¥Î³o­Ó¸É§U¦C¡Aª½±µ±o¥X³Ì¤j¤l§Ç¦C¡H
§â§Aªº¤½¦¡¤¤ªºrow()§ï¦¨row(offset($A$A,,,counta(A:A),))¡A¤]¤£¹ï¡K
=MAX(MAX(SUBTOTAL(9,OFFSET(A$1,ROW(offset($A$A,,,counta(A:A),))-1,,-ROW(INDIRECT("1:"&ROW(offset($A$A,,,counta(A:A),))))))))

TOP

°Ú¡A³o¼Ë·|¦³¤°»ò®t§O¶Ü¡H
Bodhidharma µoªí©ó 2013-4-10 19:05



    ¨º¤½¦¡¬O¼ÒÀÀ§A­ì¥ýªº B1¤½¦¡

¤U¦¡¬O¼ÒÀÀ ¶Wª© ªº B1¤½¦¡
=MAX(SUBTOTAL(9,OFFSET(A$1,ROW()-1,,-ROW(INDIRECT("1:"&ROW())))))
¤TÁä¿é¤J
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# Bodhidharma

¸Õ¸Õ¬Ý°ÊºA½d³ò»P»¼¼W½d³ò¥[Á`°}¦C
    maximum subarray.rar (7.79 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  Bodhidharma


    B1¥i¥Î¤U¦¡´À¥N
=SUBTOTAL(9,OFFSET(A$1,,,ROW()))
ML089 µoªí©ó 2013-4-10 10:24


°Ú¡A³o¼Ë·|¦³¤°»ò®t§O¶Ü¡H

TOP

¦^´_ 2# Bodhidharma


    B1¥i¥Î¤U¦¡´À¥N
=SUBTOTAL(9,OFFSET(A$1,,,ROW()))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ Bodhidharma ©ó 2013-4-10 03:27 ½s¿è

¦^´_ 1# Bodhidharma

B1´«¤@ºØ¼gªk¡G
=SUM(OFFSET($A$1,IFERROR(INDEX(MATCH(2,1/(OFFSET($B$1,,,ROW()-1,)<0)),),0),,ROW()-IFERROR(INDEX(MATCH(2,1/(OFFSET($B$1,,,ROW()-1,)<0)),),0),))
¥Îindex(match(2,1/tolast<0),)¨ú¥N{LARGE(IF(tolast<0,ROW(tolast),""),1)}ªº°}¦C¤½¦¡ (note:©_©Ç§Ú·íªì·F³Â¤£¥Îmax­n¥Îlarge...)
³o¼Ë¤lªº¸Ü¡A¤£¹L¥Îtolast©Î(OFFSET($B$1,,,ROW()-1,)³£¥i¥H¤F
©Ò¥H¬Ý°_¨Ó¬O°}¦C¤½¦¡ªº°ÝÃD¡H ¤£¹LÁÙ¬O·d¤£À´¬O¤°»ò­ì²z¡K

¤£¹L§Y¨Ï³o¼Ë§ï¡A§ÚÁÙ¬O¤£ª¾¹D¦p¦ó±N³oºØDPªºªF¦è¼g¦¨¤@­Ó¦¡¤l(D1)

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD