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

[µo°Ý] §Ú·Q­n±N¡u®Æ¸¹¡v«e¤K½X¤@¼Ëªº¥[Á`¡uÁ`¦s¡vªº¼Æ¶q

[µo°Ý] §Ú·Q­n±N¡u®Æ¸¹¡v«e¤K½X¤@¼Ëªº¥[Á`¡uÁ`¦s¡vªº¼Æ¶q

§Ú·Q­n±N¡u®Æ¸¹¡v«e¤K½X¤@¼Ëªº¥[Á`¡uÁ`¦s¡vªº¼Æ¶q
°ò¥»¤½¦¡¤w¸g¦³¼g¥X¨Ó
¦ý¬O §Ú¥Ø«eªº°ÝÃD¬O¤½¦¡¥u¯à¼g¹D¦³¼Æ¾ÚªºÄæ¦ì(A14)
¶W¹L¤W­zÄæ¦ì¤½¦¡Äæ(M4)´N·|¥X²{¿ù»~(#VALUE!)
½Ð±Ð¦U¦ì«e½ú , §Ú­n¦p¦ó°µ¥i¥H§JªAÄæ¦ì¤Wªº­­¨î

¸ê®Æ.rar (2.5 KB)

¸ê®Æ

³¯ªü±l

¥»©«³Ì«á¥Ñ p212 ©ó 2015-12-29 10:27 ½s¿è

¦^´_ 1# cjd080
1.¨Ï¥Î©w¸q¦WºÙ»s§@°ÊºA½d³ò
(1)©w¸q¦WºÙ¡u®Æ¸¹¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J=OFFSET('0130'!$A$2,,,COUNT('0130'!$A:$A))
(2)©w¸q¦WºÙ¡uÁ`¦s¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J=OFFSET('0130'!$G$2,,,COUNT('0130'!$A:$A))
2.Àx¦s®æM4¤½¦¡
(1)1#­ì¤½¦¡­×§ï¦¨ =IF(RIGHT($M$3)<>8,SUM(IF($M$3=VALUE(MID(®Æ¸¹,1,8)),Á`¦s)),"®Æ¸¹¿é¤J¿ù»~"), Ctrl+Shift+Enter¿é¤J¤½¦¡
©Î
(2)=SUMPRODUCT((--LEFT(®Æ¸¹,8)=$M$3)*Á`¦s)
½Ð°Ñ¦Ò

TOP

=IF(M3="","",IF(LEN(M3)=8,SUMPRODUCT(N(LEFT(A2:A100,8)=M3&""),G2:G100),"®Æ¸¹¿é¤J¿ù»~"))

©Î¡G
=IF(M3="","",IF(LEN(M3)=8,SUM(SUMIF(A:A,">="&M3+{0,1}&"000",G:G)*{1,-1}),"®Æ¸¹¿é¤J¿ù»~"))

TOP

=IF(LEN(M3)<>8,"®Æ¸¹¿é¤J¿ù»~",SUMPRODUCT((--LEFT(TEXT(A2:A19,"0;0;0;!0"),8)=M3)*G2:G19))

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD