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

[µo°Ý] ½Ð°Ý­n¦p¦ó±q¥ªÃä«D0ªº¼Æ¦r¶}©l¨ú³Ñ¾l¦r¦ê¡F©Î±q¥kÃ䩹¥ª¨ú¨ì0­È«e¤@¦r¦ê¨ú

[µo°Ý] ½Ð°Ý­n¦p¦ó±q¥ªÃä«D0ªº¼Æ¦r¶}©l¨ú³Ñ¾l¦r¦ê¡F©Î±q¥kÃ䩹¥ª¨ú¨ì0­È«e¤@¦r¦ê¨ú

½Ð°Ý¦U¦ì±M®a¡G
­n¦p¦ó±q¥ªÃä«D0ªº¼Æ¦r¶}©l¨ú³Ñ¾l¦r¦ê¡F©Î±q¥kÃ䩹¥ª¨ú¨ì0­Èªº«e¤@¦r¦ê¡AÁÂÁ±z!
¹Ï¤ù1.png
peter460191

¦^´_ 16# kuoliang123


    kuoliang123  ÁÂÁ±zªº¤À¨É!
peter460191

TOP

¦^´_ 14# peter460191

--ªº¥Îªk
http://www.excelperfect.com/index.php/2010/07/17/studysumproduct/
kuoliang

TOP

¦^´_ 13# Hsieh
¦hÁ¶Wª©ºëÅPªº¸Ñ»¡¡A¤S¦h¾Ç¤@½Ò¡A¶}¤ß¦¬¿ý©óµ§°O¤¤¡I

TOP

¦^´_ 7# Hsieh


    ª©¥D±z¦n¡A§Ú¦b±z¹L¥hªº©«¤l¤¤¬Ý¨ì "--" ªº¥Îªk¤F¡A
   ½Ð°Ý¬O¤£¬O©M¦b¤½¦¡«e¥[ value()ªº·N«ä¤@¼Ë
peter460191

TOP

¦^´_ 12# p212

¤½¦¡Áö¬°¤@¯ë¤½¦¡¿é¤J¡A¦ý¹ê»Ú¹Bºâ¤´¬O°}¦C¤½¦¡¹Bºâ
¦bMID¨ç¼Æ¤º¬O¥ÎA2¦r¤¸¼Æ´î¥h¦r¤¸¼Æªº­°幂°}¦C­È°µ¬°Â^¨ú¦r¦ê
¦p00ab105-01
¦³10­Ó¦r¤¸¡AROW(INDIRECT("A1:A"&LEN(A2)-1))·|±o¨ì1~9ªº¼Æ­È°}¦C
LEN(A2)-1~9«h±o¨ì9~1ªº¼Æ­È°}¦C
MID(A2,9~1,1)´N±q¥k°¼¶}©lÂ^¨ú¦r¦ê±o¨ì{0,-,5,0,1,b,a,0,0}³o¨Ç¦r¦ê
1/({0,-,5,0,1,b,a,0,0}<>0)«h±o¨ì
1»P°£¥H¹sªº¿ù»~²Õ¦¨ªº°}¦C
¥ÎLOOKUP¨ç¼Æ¨ú¥X¦¹°}¦C³Ì«á¤@­Ó¼Æ­È´N¬Oaªº¦ì¸m
LOOKUP¥i¨Ï¨â­Ó°}¦C°Ñ¼Æ°µ¹ïÀ³
²Ä¤G­Ó°Ñ¼Æ°}¦C¥ÎROW(INDIRECT(¡¨A2:A¡¨&LEN(A2))
«h±o¨ì2~10ªº¼Æ­È°}¦C
{0,-,5,0,1,b,a,0,0}»P{2,3,4,5,6,7,8,9,10}
¹ïÀ³aªº¦ì¸m¬O8
RIGHT(A2,8)´N±o¨ì·Q­nªº¦r¦ê
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-22 09:42 ½s¿è

¦^´_ 9# Hsieh
ÁÂÁ¶Wª©¶ÉÅn¬Û±Â¡A¤£¹L¹ï©ó9#ªº¸Ñªk¤´¦³¨Ç³\ªººÃ´b¡A¥i§_«ü¾É¡H
Àx¦s®æB2¤@¯ë¤½¦¡
=RIGHT(A2,LOOKUP(2,1/(MID(A2,LEN(A2)-ROW(INDIRECT("A1:A"&LEN(A2)-1)),1)<>"0"),ROW(INDIRECT("A2:A"&LEN(A2)))))
©î¸Ñ¤ÀªR
1¡B¬°¦óINDIRECT("A2:A"&LEN(A2))»PINDIRECT("A1:A"&LEN(A2)-1)¹Bºâµ²ªG¬O¬Û¦Pªº¡A¦ý«o­n°w¹ï¤£¤@¼Ëªº¡u½d³ò¡v­pºâ¡H
2¡B¬°¦óINDIRECT("A2:A"&LEN(A2))»PINDIRECT("A1:A"&LEN(A2)-1)ªºref_text¬O¡u½d³ò¡vªº±¡ªp¡A¦ý¹Bºâµ²ªG«o¬O¸Ó¤½¦¡©Ò¦bªº¹ïÀ³¦Cªº­È¡H(¦bExcel»¡©úÀɤ¤¥¼¨£¸Ó¥Îªk)
3¡B¤SROW(INDIRECT("A2:A"&LEN(A2)))¦V¤U½Æ»s¤½¦¡¨ä²£¥Íªº­È¬Ò¬°2¡FROW(INDIRECT("A1:A"&LEN(A2)-1))¦V¤U½Æ»s¤½¦¡¨ä²£¥Íªº­È¬Ò¬°1¡A¬°¦óÁÙ­n®M¥Î¤½¦¡¡H

TOP

¦^´_ 7# Hsieh


    ÁÂÁ Hsieh ¶W¯Åª©¥Dªº«ü¾É¡A¥i¥H¥Î
  ¥i¥H½Ð±Ðª©¥D¡A¤½¦¡«eªº¨â­Ó"--"¬O¤°»ò·N«ä?§Úgoogle¤£¨ì
peter460191

TOP

¦^´_ 6# aer


   ÁÂÁ aer ªº±Ð¾É¡A¥i¥H¥Î
  ­n¨Ó¦n¦n¬ã¨s¡C
peter460191

TOP

¦^´_ 8# p212

¥]§t«D¼Æ­È¦r¤¸µLªk¥H¼Æ­ÈÂà´«¤è¦¡¥h°£0
¸Õ¸Õ
=RIGHT(A1,LOOKUP(2,1/(MID(A1,LEN(A1)-ROW(INDIRECT("A1:A"&LEN(A1)-1)),1)<>"0"),ROW(INDIRECT("A2:A"&LEN(A1)))))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD