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

[µo°Ý] ¦p¦ó¤À§O­pºâ¤@­ÓÀx¦s®æ¤¤ªº¨â¬q¼Æ¦r

[µo°Ý] ¦p¦ó¤À§O­pºâ¤@­ÓÀx¦s®æ¤¤ªº¨â¬q¼Æ¦r

¦p¨Ò¹Ï©Ò¥Ü D2¨ìG2 ³£¥Ñ [¼Æ¦r][*¦r¸¹][¼Æ¦r]²Õ¦¨

§Æ±æ¯à°÷°µ¨ì
B2 ­t³dÁ`©M *¦r¸¹«á¬q¼Æ¦rÁ`©M ¤]´N¬O 10 + 2.9 + 0.08 + 200 = 212.98
¦P²z C2«h­pºâ *¦r¸¹«e¬q¼Æ¦rÁ`©M ¤]´N¬O 20 + 1.5 + 900 + 0.09 = 921.59


¥Ø«e¦Û¤vºN¯Áªº¿ìªk¬O
D3¥i¥Î¤½¦¡ =(LEFT(D2,(FIND("*",D2)-1)))  ¥ý¨ú¥XD2«e¬q¼Æ¦r 20
D4¥i¥Î¤½¦¡ =(RIGHT(D2,LEN(D2)-(FIND("*",D2)))) ¦A¨ú¥XD2«á¬q¼Æ¦r 10

¤§«á¦A¤À§O¥ÎC2=D3+E3+F3+G3 ¨ÓÁ`©M
                        B2=D4+E4+F4+G4 ¨ÓÁ`©M

°ÝÃD1: ¬°¤°»òC2=SUM(D4:G4) ³o¼Ë·|Åã¥Ü¬°0 ¦ýC2=D3+E3+F3+G3 «o¥i¥H¥¿½T­pºâ (ÁöµMÁٻݤâ°Ê³]©w®æ¦¡¬°"¼Æ­È")
°ÝÃD2:¹ê»Ú¥Î¨ìªºªí®æ¤¤¤£¤è«K¦A¨Ï¥ÎÃB¥~ªº[¦C¼Æ]¨Ó­pºâ ±Æª©³]­pÃö«Y
°ÝÃD3:Áa¨Ï§Ú¥i¥H§âC2¼g¦¨ = (LEFT(D2,(FIND("*",D2)-1))) + (LEFT(E2,(FIND("*",E2)-1))) + (LEFT(F2,(FIND("*",F2)-1))) + (LEFT(G2,(FIND("*",G2)-1)))
¼È®É¹F¨ì¤@¦êºâ¦¡§¹¦¨­pºâ,¤£¥ÎÃB¥~¦h¼W¥[¨ä¥LÄæ¦ì,¦ý³o¼Ë¤½¦¡¹ê¦b¤Óªø,¦Ó¥B¤@¥¹ H2Äæ¼W¥[·sªº¼Æ­È,´N¤£¸Õ¥Î¤F

¥H¤W¬O¦Û¤vºN¯Áªº²Â¤èªk¹J¨ìªº°ÝÃD ³Â·Ð¥ý¶i«ü¾É§ïµ½

EXCEL-Q2.jpg
2019-12-25 21:29

§ó¥¿°ÝÃD¤¤ªº¤å¦r±Ô­z

°ÝÃD2:¹ê»Ú¥Î¨ìªºªí®æ¤¤¤£¤è«K¦A¨Ï¥ÎÃB¥~ªº[¦C¼Æ]¨Ó­pºâ ±Æª©³]­pÃö«Y

¤£¬O¦C¼Æ  À³¸Ó¬O"¦æ¼Æ"

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

°ÝÃD1: ¬°¤°»òC2=SUM(D4:G4) ³o¼Ë·|Åã¥Ü¬°0 ¦ýC2=D3+E3+F3+G3 «o¥i¥H¥¿½T­pºâ (ÁöµMÁٻݤâ°Ê³]©w®æ¦¡¬°"¼Æ­È")
__D4:G4¤½¦¡²£¥Íªº¼Æ¦r¬O[¤å¦r®æ¦¡], ©Ò¥HSUM¥X¨Ó·íµM¬°0, C2=D3+E3+F3+G3, "+"¥i±N¤å¦r®æ¦¡ªº¼Æ¦rÂର¼Æ­È­pºâ, ©Ò¥H¨S°ÝÃD

°ÝÃD2¤Î3:
«e©M:=SUMPRODUCT(--(0&LEFT(D2:H2,FIND("*",D2:H2&"*")-1)))
§À©M:=SUMPRODUCT(--(0&MID(D2:H2,FIND("*",D2:H2&"*")+1,9)))

TOP

­º¥ý·PÁ¡iH¤j¡j¤Î¡i­ã¤j¡jªº¼ö¤ß¦^µª
¥Ø«e¤À§O¹Á¸Õ¤F¨â¦ìªº¤èªk¡AÁöµM¦b·Ó§Û¦^½d¨Ò¤¤¨Ï¥Î
³£§¹¥þ¥i¥H¹F¨ì§Ú·Q­nªºµ²ªG¡A¦ý¥Ñ©ó§Ú¬O¼Æ²z&EXCELµæÂû

¥Ø«e§ÚªººN¯Á¤è¦¡¬O¡A©î¶}¤½¦¡­Ó§O¥h¬Ý¦U­Ó¨ç¼Æªº¥\¥Î
§Ú·|ª¾¹D¦bMID(123,2,2)·|±o¨ì23¡A¬O¤°»ò­ì²z
§Ú¤]·|ª¾¹DSUBSTITUTE(12341234,23,"AA",1)·|±o¨ì231AA41234¬O¤°»ò­ì²z
¦ý¤@¨Ç½Ñ¦p {1;9} {1;1;1;1} 9^9 ³o¼Ëªº¦r¦ê¡A¹ê¦b¬O¤£©ú¥Õ¥¦¦b¤½¦¡¤¤ªº·N¸q

¾É­P­Y®M¥Î¨ì¹ê»Ú»Ý­n¨Ï¥Îªºªí®æ¤¤¡A­n­×§ï¤@¨Ç°Ñ·ÓÁÙ¬O½d³ò¤§Ãþªº
·|¤£ª¾¹D±q¦ó§ï°_¡A¨Ò¦pH¤j´£¨Ñªº¤½¦¡·í¤¤
B2:C6=INDEX(MMULT(TRIM(MID(SUBSTITUTE($D2:$G2,"*",REPT(" ",9)),{1;9},9))*1,{1;1;1;1}),3-COLUMN(A1))
¦]¬°¹ê»Ú¹B¥Î¤¤¡A¥i¯à·|¦³¥t¥~ªº¼Æ¾Ú·|¼W¥[¦bH2¡D¬Æ¦Ü¬OI2¡BJ2 µ¥¡K
¥»¨Ó·Q»¡¨Ì¼Ëµe¸¬Äª¡A§â$G2,§ï¦¨$H2´N¦n¡Aµ²ªGµo²{¤£¦æ¡AµM«á´N¥dÃö¤F
¥H¤Wºâ¬O³æ¯Â¤À¨ÉµæÂû¤ß±o¡A¥H¤U¦^¨ì¥DÅ餤©µ¦ùªº·s°ÝÃD

ºî¦X¤W­z¡A§Ú³Ì«á¿ï¾Ü¤F­ã¤jªº¤è¦¡¡A¦ý¦P®É¥t¥~·Q°Ý
SUMPRODUCT(--(0&LEFT(D2:H2,FIND("*",D2:H2&"*")-1)))·í¤¤
¤]¹³§Ú©Ò´£¨ìªº¡A¦pªG¸ê®Æ·|©¹¢Ö¡B¢×¡B¢Ø¡B¡@¢ÙÄæµ¥¡K¼W¥[
ÁöµM¥Ø«e§Ú§âD2:H2§ï¦¨D2:FF2¡A´NÁÙ¬O¥i¥H¹F¦¨­pºâ
¡]FF2ºâ¬O·Q§â¸ê®ÆªºÂ^¨ú½d³ò©Ôªø¡^
¦ýÁ`ı±o¤Ó¤gªk·Ò¿û¡A¤§«á©Î³\·|¹J¨ì¤£¾A¦X³o¼Ë§ïªº±¡ªp
·Q°Ý¬Ý¬Ý¡A·Q­nªí¥Ü¦b²Ä2¦æ¤¤¸ê®Æ­n±qD2©¹«á©µ¦ùªº¼gªk¡H

¥H¤W¡AÁÂÁ¤j®a¼ö¤ß«ü¾É¡ã

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD