BOM計算（全國一等獎）

第8章BOM計算物料清單（BillofMaterial，BOM），是定義產(chǎn)品結(jié)構(gòu)的技術(shù)文件，表明了產(chǎn)品→部件→組件→零件→原材料之間的結(jié)構(gòu)關(guān)系，以及每個組裝件包含的下屬部件（或零件）的數(shù)量和提前期（LeadTime）。BOM又稱為產(chǎn)品結(jié)構(gòu)表或產(chǎn)品結(jié)構(gòu)樹，在某些工業(yè)領(lǐng)域，可能稱為“配方”、“要素表”或其他名稱。18.1任務(wù)描述根據(jù)BOM計算生產(chǎn)一定數(shù)量的產(chǎn)品所需的材料。在實際使用中，BOM一般是多層的，本案例為簡便起見，采用單層BOM（產(chǎn)品→材料）。28.2操作步驟1.錄入材料清單2.錄入BOM表3.錄入生產(chǎn)計劃34.計算生產(chǎn)所需材料用量公式：=SUMPRODUCT(SUMIF(INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!A:A"),

A2,

INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!B:B"))

*N(INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!C2")))4INDIRECT函數(shù)用途：返回由文本字符串指定的引用。此函數(shù)立即對引用進行計算，并顯示其內(nèi)容。當需要更改公式中單元格的引用，而不更改公式本身，即可使用INDIRECT函數(shù)。語法：INDIRECT(ref_text,a1)。參數(shù)：

Ref_text是對單元格的引用，此單元格可以包含A1樣式的引用、R1C1樣式的引用、定義為引用的名稱或?qū)ξ谋咀址畣卧竦囊谩?/p>

A1為一邏輯值，指明包含在單元格ref_text中的引用的類型。如果a1為TRUE或省略，ref_text被解釋為A1-樣式的引用。如果a1為FALSE，ref_text被解釋為R1C1-樣式的引用。實例：如果單元格A1存放有文本"B1"，而B1單元格中存放了數(shù)值68.75，則公式“=INDIRECT($A$1)”返回68.75。5“用量”公式推導若只有產(chǎn)品1，則公式為：

=SUMIF(產(chǎn)品1!$A$5:$A$9,A2,產(chǎn)品1!$B$5:$B$9)*產(chǎn)品1!$C$2表示材料1在產(chǎn)品1中的總用量。因不同產(chǎn)品所需材料的種類數(shù)不等，為通用考慮，上述公式可改寫為：

=SUMIF(產(chǎn)品1!A:A,A2,產(chǎn)品1!B:B)*產(chǎn)品1!$C$2以上公式只能表示產(chǎn)品1，若要表示所有產(chǎn)品，可將公式改為數(shù)組公式，即將“產(chǎn)品1”改為“產(chǎn)品1～產(chǎn)品n”（本案例中n為3）：

=SUMIF(INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!A:A"),

A2,

INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!B:B"))

*N(INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!C2"))這是關(guān)鍵的一步，其中，

INDIRECT("1:3")：返回從1到3的引用。

ROW(INDIRECT("1:3")：返回1到3的序列。67

INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!A:A")：三維引用，分別取得工作表“產(chǎn)品1”～“產(chǎn)品3”的A列，即“材料編碼”字段。

INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!B:B")：三維引用，分別取得工作表“產(chǎn)品1”～“產(chǎn)品3”的B列，即“單位用量”字段。

SUMIF(INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!A:A"),

A2,

INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!B:B"))：三維引用，分別取得材料1在產(chǎn)品1～產(chǎn)品3中的單位用量。

N(INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!C2"))：三維引用，分別取得產(chǎn)品1～產(chǎn)品3的生產(chǎn)數(shù)量。

SUMIF(INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!A:A"),

A2,

INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!B:B"))

*N(INDIRECT("產(chǎn)品"&ROW(INDIRECT("1:3"))&"!C2")))：三維引用，分別取得材料1在產(chǎn)品1～產(chǎn)品3中的用量。最后，使用SUMPRODUCT