SUMPRODUCT¦h±ø¥ó¥Îªk

PJChen

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)

¤U¸ü¦¸¼Æ: 9

2017-6-27 07:41

¥­¤ßÀR®ð

½Ð±Ð 15# PJChen
LAMPªþ¥[ÀÉ,Äæ¦ì¦WºÙ¤W¤èªº...+...-¡AÃþ¦ü¤p­pªºÁY©ñ¬O¦p¦ó°µ¥X¨Óªº??
¦¹¥\¯à¹ï¤u§@¤W,Åã¥Ü§t¦³¤j¶q¸ê®Æªºªí³æ¡AÁÙº¡¹ê¥Îªº¡C

PJChen

¦^´_ 17# aer
¦^´_ 16# ML089

§Ú¬Ý¤F¥b¤Ñ¤]¨S¬Ý¥X¿ù»~¦b­þ¡I...·PÁ¤F.

aer

¦^´_ 15# PJChen

B2¤½¦¡¨ç¼Æ¦WºÙÁä¿ù¬° inedx¡A­×¥¿¬° index «á¡A¤½¦¡§Y¥i¥¿±`¹B§@

B3=VLOOKUP($A3,®w¦s!$F:$R,MATCH(B$1,®w¦s!$F$1:$R$1,),)

ML089

¦^´_ 15# PJChen

B2Àx¦s®æ
=INEDX(®w¦s!$A:$AH,MATCH($A2,®w¦s!$F:$F,),MATCH(B$1,®w¦s!$1:$1,))
­×§ï¬°
=INDEX(®w¦s!$A:$AH,MATCH($A2,®w¦s!$F:$F,),MATCH(B$1,®w¦s!$1:$1,))

PJChen

¦^´_ 11# ML089

½Ð¤j¤j¦A«ü¾É¤U,¬°¤°»òÃþ¦üªº¥Îªk,¦b³o­ÓÀɮפ¤¡A«o±a¤£¥Xµ²ªG¡H

LAMP.rar (142.4 KB)

¤U¸ü¦¸¼Æ: 12

2017-7-4 13:19

PJChen

¦^´_ 13# zz5151353

ÁÂÁ§A,³o¤½¦¡¤]¥i¥H¥Î,¥u¬O³£µLªk¸Ñ¨M¦P¤@±i­q³æ,¦P¤@¤Ñ¬£¨®2¦¸¦ý¤£¦P³f¹B¤½¥q,¸ê®Æ·|±a¤£¥X¨Óªº°ÝÃD.

zz5151353

¬£¨®³æ¤u§@ªí
B3 ¤U©ì©Ô
=VLOOKUP($A$2,¬£¨®!$B:$O,MATCH($A3,¬£¨®!$B$1:$O$1,),)

PJChen

¦^´_ 11# ML089

ÁÂÁ¡I¤]¦æ¡A³o¤]¬O¤@­Ó¤èªk.

ML089

¦^´_ 10# PJChen

³Ì¦n ¤@±i¬£¨®°õ¦æ¤@¶µ¥ô°È¡A³o¼Ë¬£¨®³æ¸¹´N¬O¸ê®Æ®wªºKEY(¯Á¤Þ¸¹½X)®e©ö¬d¸ß¡C

­Y¬O ¬£¨®³æ¸¹ ¦³¦hµ§¥ô°È¡A©ñ¸ê®Æ®É¤S¬OÂkÀɦA¤@°_¡A¬d¸ß¤]¤£§xÃø¡A´N¬O ¥ý¬d¸ß²Ä¤@µ§¦ì¸m¦A¥[1¦C´N¬O

¬d¸ß²Ä¤@µ§
B3 =INDEX(¬£¨®!$A:$Q,MATCH($A$2,¬£¨®!B:B,),MATCH($A3,¬£¨®!$1:$1,))

¬d¸ß²Ä¤Gµ§
B3 =INDEX(¬£¨®!$A:$Q,MATCH($A$2,¬£¨®!B:B,)+1,MATCH($A3,¬£¨®!$1:$1,))

¬d¸ß²Ä¤Tµ§
B3 =INDEX(¬£¨®!$A:$Q,MATCH($A$2,¬£¨®!B:B,)+2,MATCH($A3,¬£¨®!$1:$1,))   

­Y­n¨¾¤î¨S¦³²Ä¤Gµ§©Î²Ä¤Tµ§¡A¥i¥H¨Ï¥Î COUNTIF ¨Ó­pºâ¦³´Xµ§
B3 =IF(COLUMN(A1)>COUNTIF(¬£¨®!$B:$B,$A$2),"",INDEX(¬£¨®!$A:$Q,MATCH($A$2,¬£¨®!$B:$B,)+COLUMN(A1)-1,MATCH($A3,¬£¨®!$1:$1,)))
¥k©Ô (²Ä¤@µ§¡B²Ä¤Gµ§¡B...)
¤U©Ô