[µo°Ý] ·í¥X²{¼Æ¦rªºÄæ¦ì¤£¤@©w®É,¦p¦ó¥Î¤@­P©Ê¤½¦¡¨Dµª®×?

PJChen

¨DJÄ檺µª®×,¸Ó¦p¦ó¥N¤J¤@­P©Êªº¤½¦¡¡H    ¨DJÄ椽¦¡.rar (7.93 KB)

¤U¸ü¦¸¼Æ: 9

2017-8-6 16:07

¶¶¦ì       
1        ­q³æ¼Æ¶q
2        BOM
3        ½ÐÁʼƶq
4        ±ÄÁʼƶq
5        ¶i³f¼Æ¶q
6        »â®Æ¼Æ¶q
*******
1        B,H¬O³Q´î¼ÆªºÄæ¦ì
2        (A,D:H)-(H+ B)=JÄ檺µª®×
3        (A,D:H)Äæ¥u¯à¥Î¨ä¤¤¤@­Ó¼Æ¦r
4        ¸Ó¥Î­þ¤@­Ó¼Æ¦r¥H¶¶¦ì¨M©w
5        ·í²Ä1¶¶¦ì¦³¼Æ¦r®É,¥Î¸Ó¼Æ¦r-(H+ B)=J
6        ¨C­Ó¶¶¦ì³£¦³¼Æ¦r®É,¥H²Ä1¶¶¦ì¬°Àu¥ý
7        (A,D:H)Äæ,­þ­ÓÄæ¦ì·|¥X²{¼Æ¦r,¨S¦³¤@©w

PJChen

¦^´_ 8# ­ã´£³¡ªL

¬Oªº,²Ä¤@ºØ¥þ³¡Àx¦s®æ¬°0®É,·|¥X²{#N/A,¬Ý¨ìHsiehªº¤½¦¡¤§«á,§Úª¾¹D¥i¥H¦b±z­ì¤½¦¡µy¥[­×§ï¤]¦¨.

=IFERROR(IF(COUNT(A10,D10:H10),1/LOOKUP(9^9,1/N(OFFSET(A10,,{7,6,5,4,3,0}))),0)-B10-H10,0)

²Ä2ºØ¬O­ì¥»´N¥i¥H°£¿ù.

·PÁÂ...

PJChen

¦^´_ 7# Hsieh

«x,...¤j¤j ¤½¦¡¤S¤£¤Ó¤@¼Ë¤F,¦h¾Ç¨ì¤@ºØ,·PÁÂ.

­ã´£³¡ªL

¦^´_ 6# PJChen


=IF(SUM(A2,D2:H2),1/LOOKUP(9^9,1/N(OFFSET(A2,,{7,6,5,4,3,0})))-B2-H2,0)
²Ä¤G­Ó°}¦C¤½¦¡IFERROR, ¥»¨Ó´N¥i¥H°£¿ù¬°0

­Y¦³°ÝÃD, ³Ì¦n´£¨ÑÀɮפιê¨Ò~~~

Hsieh

¦^´_ 6# PJChen
=IFERROR(LOOKUP(2,1/(N(OFFSET(A2,,{7,6,5,4,3,0}))<>0),N(OFFSET(A2,,{7,6,5,4,3,0})))-H2-B2,0)

PJChen

¦^´_ 4# ­ã´£³¡ªL

½Ð±Ð¤j¤j,

¦b¹ê»Ú§@·~¤¤,¦³¬Y¨ÇµL®Ä­q³æ,·|¾É­P©Ò¦³ªºÀx¦s®æ¥þ³¡¬°0¡A¦ý¤S¤£¯à§R°£¸Ó¦C,³o®É§Ú§Æ±æ¥i¥H¦bJÄ檺«Ý°tÅã¥Ü¬°0¡A½Ð°Ý¸Ó¦p¦ó­×§ï¤½¦¡¡H

PJChen

¦^´_ 4# ­ã´£³¡ªL
·PÁ¤j¤j,
¤G­Ó¤½¦¡³£¥i¥H¨D±o¥¿½Tµª®×.

¦^´_ 4# fengetianxia
ÁÂÁ±z,
¤£¹L¥N¥Xªºµª®×µLªk§¹¥þ¥¿½T.

­ã´£³¡ªL

¤@¯ë¤½¦¡¡G
=IF(COUNT(A2,D2:H2),1/LOOKUP(9^9,1/N(OFFSET(A2,,{7,6,5,4,3,0}))),0)-B2-H2

©Î¡G°}¦C¤½¦¡¡]¤TÁä¿é¤J¡^
=IFERROR(IF(A2,A2,HLOOKUP("*",IF(D2:H2,D2:H2&""),1,))-B2-H2,0)

fengetianxia

=OFFSET(A2,0,IF(A2<>"",1,MIN(IF(D2:H2<>"",COLUMN(D2:H2),9^9)))-1,1,1)-B2-H2
¼Æ²Õ¤½¦¡

PJChen

¸É¥R¡GªÅªºÄæ¦ì­n¸É0,¤£¬OªÅ®æ.