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

½Ð±Ð¡G¦X¨ÖÀx¦s®æ¥[Á`³]©w

½Ð±Ð¡G¦X¨ÖÀx¦s®æ¥[Á`³]©w

½Ð°Ý¸óÄæ¸m¤¤­n¦p¦ó¥[Á`
EÄæ²Å¦XAÄæ¤ÎFÄæ²Å¦XCÄæ¥[Á`BÄæ¦ÜGÄæ(¦pªþÀÉ)

¬¡­¶Ã¯1.zip (136.58 KB)

joyce

¦^´_ 1# leiru


    =SUMIFS(B:B,C:C,F2,A:A,"<>"&MID(LOOKUP("¼_",E$2:E2),3,9))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# ML089


    ÁÂÁ¦^ÂÐ,¤£¹LÀx¦s®æG3ªºµª®×¤£¹ï,¥¿½T¬O$979258
½Ð°Ý¬O­þ¸Ì°ÝÃD
joyce

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2016-7-13 15:28 ½s¿è

¦^´_ 3# leiru
1.©w¸q½d³ò
(1)©w¸q¡u¥N½X¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=OFFSET(¤u§@ªí1!$A$2,,,COUNT(¤u§@ªí1!$B:$B))
(2)©w¸q¡uª÷ÃB¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=OFFSET(¤u§@ªí1!$B$2,,,COUNT(¤u§@ªí1!$B:$B))
(3)©w¸q¡uÃþ§O¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=OFFSET(¤u§@ªí1!$C$2,,,COUNT(¤u§@ªí1!$B:$B))
2.Àx¦s®æG2¿é¤J
=SUMPRODUCT((¥N½X<>MID($E$2,2,4))*(Ãþ§O=F2)*ª÷ÃB)
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I

¬¡­¶Ã¯1_ref.zip (143.41 KB)

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-7-13 16:17 ½s¿è

¦^´_ 3# leiru

¥i¥H¥Î
=SUMIFS(B:B,C:C,F2) - SUMIFS(B:B,C:C,F2,A:A,MID(LOOKUP("¼_",E$2:E2),2,9))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089


        ML089 ¤j®v:
ÁÂÁ±z,§Ú¦bdÄæ³]©wistext(a2)©¹¤U©Ô¥h§PÂ_¬O¤£¬O¤å¦r,µ²ªG±o¨ìªºµª®×¬O:TRUE,³o¼Ë¬Ý°_¨ÓAÄæ³£¬O¤å¦r®æ¦¡,
½Ð°Ý¦³¼Æ¦r®æ¦¡¬O¦b­þ¤@Àx¦s®æ©Î¬O«ç»ò§PÂ_??

ÁÂÁ±z­@¤ß«ü¾É
joyce

TOP

¥»©«³Ì«á¥Ñ KCC ©ó 2016-7-13 16:48 ½s¿è

¦^´_ 6# leiru

    §â§Aªº­ì©l¸ê®Æ¡A¸ê­å¬°³q¥Î®æ¦¡´N·|¥¿±`
    ´N§Ú©Òª¾¡Asumif¡Bsumifs ¤Uµ¥©óªº±ø¥ó®É¡A¤å¦r«¬¼Æ¦r·|³Q·í§@¼Æ¦r³B²z¡A¥]¬A "1"¡B"01"....
    ¦ý¬O¤U¦¨ "<>¼Æ¦r"´N·|«ÜÄYÂÔªº¤@©w­n¼Æ¦r¤~¦æ¡A©Ò¥H "<>7070" ¤£·|§â "7070" ªº¶µ¥Ø±Æ°£¡A¦]¬°¹ê»Ú¹B§@¥u·|±Æ°£ 7070ªº¶µ¥Ø
    ³o¬O­Ó¤Hªº¤@¨Ç¸gÅç¡A¥¼¸g¸Ô´ú¡A¶È¨Ñ°Ñ¦Ò
1

µû¤À¤H¼Æ

TOP

¦^´_ 7# KCC

¦^´_ 6# leiru

    §â§Aªº­ì©l¸ê®Æ¡A¸ê­å¬°³q¥Î®æ¦¡´N·|¥¿±`
    ´N§Ú©Òª¾¡Asumif¡Bsumifs ¤Uµ¥©óªº±ø¥ó®É¡A¤å¦r«¬¼Æ¦r·|³Q·í§@¼Æ¦r³B²z¡A¥]¬A "1"¡B"01"....
    ¦ý¬O¤U¦¨ "<>¼Æ¦r"´N·|«ÜÄYÂÔªº¤@©w­n¼Æ¦r¤~¦æ¡A©Ò¥H "<>7070" ¤£·|§â "7070" ªº¶µ¥Ø±Æ°£¡A¦]¬°¹ê»Ú¹B§@¥u·|±Æ°£ 7070ªº¶µ¥Ø
    ³o¬O­Ó¤Hªº¤@¨Ç¸gÅç¡A¥¼¸g¸Ô´ú¡A¶È¨Ñ°Ñ¦Ò


´ú¸Õ¹L½T¹ê¦p§A©Ò­z

sumif¡Bsumifs ªº±ø¥ó =¡B>¡B<¡B<>¡A
¨ä¤¤ "=" ªº±ø¥ó®É¡A¤å¦r«¬¼Æ¦r·|³Q·í§@¼Æ¦r³B²z¡A¥]¬A "1"¡B"01"....
¨ä¥L  >¡B<¡B<>ªº±ø¥ó¡A¤å¦r«¬¼Æ¦r·|³Q·í§@¤å¦r³B²z¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁ¨â¦ì¤j®v»¡©ú
joyce

TOP

¦A½Ð±Ð¡G
½Ð°ÝG13¦ÜG23Àx¦s®æ­n¦p¦ó³]©w¡AG20Àx¦s®æªºµª®×¬°-46,106

¬¡­¶Ã¯1_ref.zip (140.01 KB)

joyce

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD