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

[µo°Ý] ¦p¦ó²Î­p¿z¿ïªºÁ`©M

[µo°Ý] ¦p¦ó²Î­p¿z¿ïªºÁ`©M

¦p¦ó¦b³o¼g¤W¤½¦¡
²Î­p °ò¥»¸ê®Æ¸Ìªº
¥xªF¿¤ ¦Ó¥B¬O¤hªL°Ïªº¤H¼ÆÁ`©M
(¤£ºÞ1°Ï©Î2°Ï³£¥[¦b¤@°_)

¨Ì¦¹Ãþ±À.


²Î­p¿z¿ï.rar (9.3 KB)
­YµL¶~¨Æ±¾¤ßÀY
«K¬O¤H¶¡¦n®É¸`

¦^´_ 1# cbl0924


    b2=SUMPRODUCT((°ò¥»¸ê®Æ!$A$2:$A$54=$A2)*(LEFT(°ò¥»¸ê®Æ!$C$2:$C$54,2)=B$1)*°ò¥»¸ê®Æ!$B$2:$B$54)

   ¦V¥k¡B¦V¤U©Ô½Æ»s¡C

TOP

¦^´_ 2# owen06


    ½Ð°Ý¦b¨º°ò¥»¸ê®Æ¸Ìªº½d³ò¤£¯à¬O¾ã±øªºAÄæ¦ì¶Ü ¡H ¡]BÄæ»PCÄæ¤]¤@¼Ë¡^
¦]¬°¸ê®Æ·|¼W¥[©Î´î¤Ö°Ú
­YµL¶~¨Æ±¾¤ßÀY
«K¬O¤H¶¡¦n®É¸`

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-12-23 09:03 ½s¿è

¦^´_ 3# cbl0924
¸ê®Æ·|¼W´î®É¥i¦Ò¼{¨Ï¥Î¡u°ÊºA½d³ò¡v³B²z
1.©w¸q¦WºÙ
(1)¡u¿¤¥«¡v¡A°Ñ·Ó¨ì¿é¤J=OFFSET(°ò¥»¸ê®Æ!$A$2,,,COUNTA(°ò¥»¸ê®Æ!$A:$A)-1)
(2)¡u¤H¼Æ¡v¡A°Ñ·Ó¨ì¿é¤J=OFFSET(°ò¥»¸ê®Æ!$B$2,,,COUNTA(°ò¥»¸ê®Æ!$B:$B)-1)
(3)¡u°Ï§O¡v¡A°Ñ·Ó¨ì¿é¤J=OFFSET(°ò¥»¸ê®Æ!$C$2,,,COUNTA(°ò¥»¸ê®Æ!$C:$C)-1)
2.¡u¿z¿ï²Î­p¡v¤u§@ªí¤§Àx¦s®æB2¿é¤J
=SUMPRODUCT((¿¤¥«=$A2)*(LEFT(°Ï§O,2)=B$1)*¤H¼Æ)
¦V¥k¡B¦V¤U½Æ»s¤½¦¡
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 4# p212


    ¦pªG¤w¤W­±owen ªº¤½¦¡ ¬O¿z¿ï²Î­p³æ¤@­Ó°Ï¨S°ÝÃD

¥Howenªº¤½¦¡¨Ó»¡
¦pªG§Ú­n±N¤j¦P¸ò ¤¤¥¿ ¥[¦b¦P¤@­ÓÀx¦s®æ
§Ú¦bleft «e­±¥[ or «ç»ò´Nºâ¤£¥X¨Ó
¬O§Úor ¥[¿ù¤F¡H¡H
­YµL¶~¨Æ±¾¤ßÀY
«K¬O¤H¶¡¦n®É¸`

TOP

¥»©«³Ì«á¥Ñ owen06 ©ó 2014-12-23 10:17 ½s¿è

¦^´_ 5# cbl0924


   =SUMPRODUCT((°ò¥»¸ê®Æ!$A$2:$A$54=$A4)*((LEFT(°ò¥»¸ê®Æ!$C$2:$C$54,2)="¤j¦P")+(LEFT(°ò¥»¸ê®Æ!$C$2:$C$54,2)="¤¤¥¿"))*°ò¥»¸ê®Æ!$B$2:$B$54)

  ¦b¦PÄæ¦ì¦³¦h±ø¥óªº¸Ü¡A°ò¥»¤Wªº»yªk¬O³o¼Ë¡A§A¦A¦Û¦æÀ³¥Î§ï¦¨§A­nªº¤è¦¡§Y¥i¡C

TOP

¦^´_ 6# owen06


    ©ú¥Õ  ¦hÁ  ¦hÁÂ

¥[Á`¬O¨S°ÝÃD¤F
¦pªG­n­p¦¸©O¡H
¦p¥xªF¿¤ ¤hªLx°Ï ¦³174¤H  ¨S°ÝÃD¤F
¦³3¦æ
³o­Ó3 ¸Ó«ç»ò¼g¥X¨Ó©O¡H
­YµL¶~¨Æ±¾¤ßÀY
«K¬O¤H¶¡¦n®É¸`

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-12-23 10:48 ½s¿è

¦^´_ 7# cbl0924
©Ó4#
¡u¿z¿ï²Î­p¡v¤u§@ªí¤§Àx¦s®æB2¤½¦¡­×§ï¬°
=SUMPRODUCT((¿¤¥«=$A2)*(LEFT(°Ï§O,2)=B$1))
¦V¥k¡B¦V¤U½Æ»s¤½¦¡
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 7# cbl0924

    §A¬O­nºâ¥X¥xªFªº¤hªLX°Ï¦@¥X²{´X­Ó¶Ü¡H¦pªG¬Oªº¸Ü
    =SUMPRODUCT((°ò¥»¸ê®Æ!$A$2:$A$54="¥xªF")*(LEFT(°ò¥»¸ê®Æ!$C$2:$C$54,2)="¤hªL"))
    ·§©À¬O³o¼Ë¡A§A¦A±N"¥xªF"¡B"¤hªL"§ï¦¨¦Û¤vªí®æ¹ïÀ³ªºÀx¦s®æ§Y¥i¡C

TOP

¦^´_ 9# owen06


    §¹¦¨
ÁÂÁ¨â¦ìªº±Ð¾É

½Ð°Ý¤½¦¡¸Ìªº*·N«ä¬O ¡H
­YµL¶~¨Æ±¾¤ßÀY
«K¬O¤H¶¡¦n®É¸`

TOP

        ÀR«ä¦Û¦b : ¤H­n¦Û·R¡A¤~¯à·R´¶¤Ñ¤Uªº¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD