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

SUMPRODUCT¦h±ø¥ó¥Îªk

SUMPRODUCT¦h±ø¥ó¥Îªk

1. Quotation ¤u§@ªí AÄæ=¶×Á`¤u§@ªí ¦C1
2. Quotation ¤u§@ªí CÄæ=¶×Á`¤u§@ªí ¦C4
3. Quotation ¤u§@ªí ¦C2=¶×Á`¤u§@ªí AÄæ
4. §Æ±æ¥Î¤½¦¡±a¥XQuotation ¤u§@ªí Q~ANÄæ©Ò¦³¼Æ¾Ú

½Ð±Ð¥ý¶i­Ì¡G
§Ú¥Î¥h°µ,¬°¦ó³£±a¤£¥X¨Ó¼Æ­È,©ÎªÌ¦³¨ä¥L¨ç¼Æ¥i¥Î?   Freight Quotation_1.rar (19.45 KB)

C7 ³o·N«ä¶Ü¡H
=SUMPRODUCT((C$4=Quotation!$C$4:$C$50)*(Quotation!$A$4:$A$50=C$1)*ISNUMBER(FIND(Quotation!$Q$2:$AN$2,$A7))*Quotation!$Q$4:$AN$50)

TOP

¦^´_ 2# zz5151353

C7
=SUMPRODUCT((C$4=Quotation!$C$4:$C$50)*(Quotation!$A$4:$A$50=C$1)*ISNUMBER(FIND(Quotation!$Q$2:$AN$2,$A7))*Quotation!$Q$4:$AN$50)

¤Ó·PÁ¤F¡A´N¬O³o¼Ë¡I½Ð°Ý¤j¤j2­Ó°ÝÃD¡G
1. ¬õ¦rªº³¡¥÷¦³®É©ñ«e­±¡A¦³®É¦b«á­±¡A¦b·N¸q¤W¦³¨S¦³¤°»ò¤£¦P¡H
2. ¦pªG§Ú§â¤½¦¡®M¥Î¦bC6´N¤£¦¨¥ß¡A¦]¬°¥¦¬O"¤å¦r",½Ð°Ý³oºØ±¡§Î­n«ç»ò§ï¤½¦¡¡H¦³¨S¦³¤å¦r¤Î¼Æ¦r¥i³q¥Îªº¡H

TOP

°ÝÃD1 , ¬Oµ´¹ï¸ò¬Û¹ï¤Þ¥Î°ÝÃD¡A²³æ»¡¨º¸Ì­nÂê©w´N©ñ­Ó $ ¸¹,¨Ì¾Ú©ì©Ô¤è¦V

°ÝÃD2
±j¨î¤å¦r§ÎºA¼Æ¦rÂà´«¦¨¯Â¼Æ¦r  --  , *1

C6
=VLOOKUP(C$1,Quotation!$A:$F,6,)

TOP

¦^´_ 4# zz5151353

¤j¤j,

1. ±zªº·N«ä¬O»¡¯Â¤å¦rµLªk¥Îsumproduct¥u¯à¥ÎVLOOKUP ?¤£¹L¦]¬°§Ú±`­n¥Î¨ì¦h­Ó±ø¥óªº¥\¯à¡A©Ò¥H¤~·|·Q¨ì¥Îsumproduct,¦Óvlookup¥u¯à¤@­Ó±ø¥ó.

2. §Ú§â¸ê®Ækey¦n,µo²{³\¦h¿ù»~¡A¥i¥HÀ°§Ú¬Ý¤@¤U¶Ü¡H§Ú¥Î¶À¦âªº°Ï¶ô¬O¥¿½Tªº. Freight Quotation_2.rar (18.13 KB)

TOP

¥»©«³Ì«á¥Ñ zz5151353 ©ó 2017-6-27 13:40 ½s¿è

C21 ¤U©Ô¤Î¥k©Ô¡Aª`·N´£¨Ñ¶À¦â°Ï°ì¼Æ¾Ú¸ò¹ê»Ú¦ì¸m¦³³¡¥÷¬O¤£¦P¦ì¸mªº
=SUMPRODUCT((C$4=Quotation!$C$4:$C$500)*(Quotation!$A$4:$A$500=C$1)*(Quotation!$B$4:$B$500=C$2)*(Quotation!$Q$2:$AO$2=$A21)*Quotation!$Q$4:$AO$500)

Freight_Quotation_2.rar (10.42 KB)

TOP

¦^´_ 6# zz5151353

ÁÂÁ§Aªº´£¿ô,²{¦b§¹¥þ¥¿½T¤F^^

TOP

¦^´_ 6# zz5151353

½Ð°Ý¤j¤j,
¥t¤@­ÓÀɥιp¦Pªº¤èªk,¦b¬£¨®³æ¤u§@ªí¤¤¬°¦ó¤å¦r³¡¤À¥Î±z»¡ªº±j¨îÂà¤å¦r(±j¨î¤å¦r§ÎºA¼Æ¦rÂà´«¦¨¯Â¼Æ¦r  --  , *1),ÁÙ¬O¤£¦æ¡I ¬£¨®.rar (10.4 KB)

TOP

B3 =INDEX(¬£¨®!$A:$N,MATCH($A$2,¬£¨®!B:B,),MATCH($A3,¬£¨®!$1:$1,))

¤½¦¡¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 9# ML089

½Ð°Ý¤j¤j¡A
§Ú­n¬O¦P¤@±i­q³æ,¦P¤@¤Ñ¬£¨®2¦¸¦ý¤£¦P³f¹B¤½¥q,¸ê®Æ·|±a¤£¥X¨Ó¡A³o­n¦p¦ó­×¥¿¤½¦¡¡H ¬£¨®.rar (12.36 KB)

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡µLªk¾B¾×¡j©È®É¶¡®ø³u¡Aªá¤F³\¦h¤ß¦å¡A·QºÉ¦U¦¡¤èªk­n¾B¾×®É¶¡¡Aµ²ªG¬O¡G®ö¶O¤F§ó¦h®É¶¡¡A¥B¤@µL©Ò¦¨¡I
ªð¦^¦Cªí ¤W¤@¥DÃD