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

[¤À¨É] Âø¶µ²Ó¥Ø¤Î¶O¥Î¦X­p

[¤À¨É] Âø¶µ²Ó¥Ø¤Î¶O¥Î¦X­p

ªÀ°Ïªº¦¬¤äªí¤j³£©T©wªºÄæ¦ìªº¼Æ¦r¦X­p¡A¨ä¤¤¤@¶µÂø¶µª÷ÃB¨ä»¡©ú¦p¤U "«H«Ê$120+½¦¤ô$20+¥ÕªO$500+°j¯¾°w$120+¥ÕªOÀ¿$160"¡A
¦p¦ó±qÂø¶µ»¡©ú±N¨ä¤¤ªºª÷ÃB¦X­p?

A1 = "60­Ó«H«Ê$120+1²~½¦¤ô$20+1±i¥ÕªO$500+6²°°j¯¾°w$120+2­Ó¥ÕªOÀ¿$160"

¨C­Ó­n­pºâªºª÷ÃB«e­±­n¥[ $
B1 =SUMPRODUCT(--TEXT(LEFT(TEXT(MID(SUBSTITUTE(A1&"X","$",REPT(" ",16)),ROW($1:$999),16),),15),"[<>];;!0;!0"))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

http://blog.xuite.net/hcm19522/twblog/358541881

TOP

¦^´_ 12# ML089


    ¦nªº¡I·PÁ§A¡ã:)

TOP

¦^´_ 11# 074063


    ¼Ï¶s¤ÀªRªí§Ú¤£·|
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 10# ML089


    ¤Ó´Î¤F, ·PÁ¤j¤j¤£³Ó¨ä·Ð«ü¾É, ÁöµMÁÙ¤£¤F¸Ñ²[¼Æ¼gªk¡AÁÙ¬Oµ¹§A¤@­ÓÆg¡I:D

¡@¤p§Ì¦³­Ó¼Ï¶s¤ÀªRªíªº°ÝÃD¡A¤j¤j¦³ªÅªº¸ÜÀ°¦£¬Ý¬Ý¬O§_¦³¸Ñ

¡@http://forum.twbts.com/redirect. ... 65&fromuid=5958

TOP

¦^´_ 9# 074063


     =SUM(--TEXT(LEFT(TEXT(LEFT(IF(D10:F10<>"",D10:F10,$D$9:$F$9)&"X",ROW($2:$16)),),ROW($1:$15)),"[<>];;!0;!0")) »Ý¨Ï¥Î¤TÁä¿é¤J(CTRL+SHIFT+ENTER)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089

    =SUM(--TEXT(LEFT(TEXT(LEFT(IF(D10:F10<>"",D10:F10,$D$9:$F$9),ROW($2:$16)),),ROW($1:$15)),"[<>];;!0;!0"))  »Ý¨Ï¥Î¤TÁä¿é¤J(CTRL+SHIFT+ENTER)
     
    µLªkºâ¥X¥¿½TÁ`©M
        
    D9:F9¦³¼Æ¦r¤Î²Å¸¹,  D10:F10¥H«áªºÀx¦s®æ¬Ò¥u¬O¼Æ¦r....

TOP

¦^´_ 7# 074063


A1:D1
§ï¬°
IF(D10:F10<>"",D10:F10,$D$9:$F$9)


=SUM(--TEXT(LEFT(TEXT(LEFT(IF(D10:F10<>"",D10:F10,$D$9:$F$9),ROW($2:$16)),),ROW($1:$15)),"[<>];;!0;!0"))
»Ý¨Ï¥Î¤TÁä¿é¤J(CTRL+SHIFT+ENTER)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089

    ­ì¤½¦¡=SUM(IF(D10:F10<>"",D10:F10,$D$9:$F$9))  ... §PÂ_D10:F10¬O§_¦³¼Æ­È, ­Y¨S¦³¥HD9:F9¹ïÀ³Àx¦s­ì¼Æ­È¥N¤J¥[Á`
    ¦]D9:F9¦³¼Æ¦r¤Î²Å¸¹, ·Q§â=SUMPRODUCT(--TEXT(LEFT(TEXT(LEFT(A1:D1,ROW($2:$16)),),ROW($1:$15)),"[<>];;!0;!0")) ¥N¤J¤£ª¾«ç»òµÛ¤â
    .
    .
    .
    ¥t°ÝROW($2:$16)¡BROW($1:$15) ³o­Óªº·N«ä, ­Y¸ê®ÆÀx¦s®æ²§°Ê¦p¦ó­×§ï

TOP

¦^´_ 5# 074063

=SUMPRODUCT(--TEXT(LEFT(TEXT(LEFT(A1:D1,ROW($2:$16)),),ROW($1:$15)),"[<>];;!0;!0"))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD