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

[µo°Ý] ½Ð°Ýsumproduct »Pfind¥i¥Hµ²¦X¨Ï¥Î¶Ü

[µo°Ý] ½Ð°Ýsumproduct »Pfind¥i¥Hµ²¦X¨Ï¥Î¶Ü

¥»©«³Ì«á¥Ñ s7659109 ©ó 2017-1-5 09:09 ½s¿è

§Úªº¤èªk ¬O¥ý¥Îfind¹¢¥XÃöÁä¦r¡A¦Asumproduct,²Î­p
­Y±N¨âªÌµ²¦X¡A¨ç¼Æ¸Ó¦p¦ó¼g¡H

°ÝÃD60.rar (5.13 KB)

§Æ±æ¤ä«ù!

¦^´_ 1# s7659109
D2=IF($B2<>$G$2,0,SUMIFS(¶µ¥Ø©ú²Ó!$D$2:$D$8,¶µ¥Ø©ú²Ó!$C$2:$C$8,¶µ¥Ø²Î­p!A2,¶µ¥Ø©ú²Ó!$F$2:$F$8,"=*"&B2&"*"))

E2=IF($B2=$G$2,0,SUMIFS(¶µ¥Ø©ú²Ó!$D$2:$D$8,¶µ¥Ø©ú²Ó!$C$2:$C$8,¶µ¥Ø²Î­p!A2,¶µ¥Ø©ú²Ó!$F$2:$F$8,"=*"&B2&"*"))

¦V¤Uªí»s

TOP

D2=INDEX(¶µ¥Ø©ú²Ó!D:D,MATCH(B2,¶µ¥Ø©ú²Ó!F:F,))
(µL¶·H:L¦C ,¥ÎFIND)E2:E6{=IF(B2=G2,0,SUM((¶µ¥Ø©ú²Ó!C$2:C$8=A2)*IF(ISERR(FIND(B2,¶µ¥Ø©ú²Ó!F$2:F$8)),,¶µ¥Ø©ú²Ó!D$2:D$8)))
   OR
E2:E6=IF(B2=G2,0,SUMIFS(¶µ¥Ø©ú²Ó!D$2:D$8,¶µ¥Ø©ú²Ó!C$2:C$8,A2,¶µ¥Ø©ú²Ó!F$2:F$8,"*"&B2&"*"))

TOP

¸Ñ¨M¤F¡AÁÂÁ«e½ú¨ó§U¡C
§Æ±æ¤ä«ù!

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD