2024年最後一天，我接到某工廠生產計畫的需求，他們工廠的物料拖欠很難計算，因為工廠比較小，沒有資訊化，所以物料需求和材料欠款都是手工計算的。
要求：表1是一些需要組裝的產品，有組裝順序，現在需要根據這些組裝產品的零件（表2物料清單）和相應的零件庫存（表3）來計算實際可以組裝的數量，如果沒有，組裝好的零件間隙名稱是什麼？裝配零件的間隙數量是多少？
由於裝配中的許多零件使用相同的零件，因此需要考慮實際裝配數量，以便按順序反映在相應的零件裝配數量中。 例如，如果在裝配序列 1 中使用了 500 個零件，但在裝配 2 之後，可能無法在實際零件中使用 400 個零件，並且只能裝配間隙為 100 個零件的 300 個零件。 可以體現在缺口的名稱和相應的缺口數量上。
因此，流行的需求點是告訴生產計畫什麼可以生產，什麼不能生產，什麼不能生產，欠什麼？你欠多少錢？
圖1 表2是這些組裝好的產品的物料清單，看到這樣的物料清單（BOM）也是很痛苦的，這也是中小工廠的一些特點，基本形式不規範，以後很難做資料分析和整理。
圖 2 和表 3 顯示了零件庫存，這是乙個標準的一維資料表。
圖3 表2是零件的物料清單，即BOM表，如果要將表1的生產進度表和表2的物料清單鏈結起來，則無法鏈結當前BOM表，因此需要對現有的BOM表進行整理。 組織成乙個標準的一維表格。
插入輔助列，將其命名為程式集名稱，然後輸入函式：
b3=if(c3="","",if(c2="零件名稱"，d2，b2）），下拉填充。
功能解釋：此步驟的目的是將裝配名稱放在BOM表的左側，與表1形成聯動，帶出訂單需求。
如果單元格 C3 為空，則返回 null 值 （）""）。
如果單元格 C2 的值等於"零件名稱"，則返回 D2：38281040190。
如果單元格 c2 的值不等於"零件名稱"，B2："程式集的名稱"。
這樣，根據相對參照原理，下拉填充一鍵將裝配體名稱填充到零件右側。
根據此程式集，參考表1中的排程順序，輸入函式：
if(b3="",""， xlookup（b3， 計畫發布！B：B，計畫發布！a:a,0))
然後參考零件清單：
if(b3="",""， xlookup（d3， 庫存數量！c：c，庫存量！d:d,0))
這樣可以組織 BOM。
最終結果如下圖 4 所示
圖4 在底材的計算中，需要對錶2中裝配名稱對應的零件的物料清單資料進行第二次梳理，即兩個約束：
約束 1：零件按原樣組裝在一起。
約束2：在同一零件**中，順序按照排程順序公升序排列，即按上述要求優先扣除排在第一位的裝配體。
根據以下兩個約束條件輸入函式：
sort(sort(filter(b3:f5000,(b3:b5000<>"")*(d3:d5000<>"-")),5),3)
功能定義：過濾b列不為空且d列不等於符號“-”的資料，顯示的結果為b列到f列，然後根據此結果進行排序，第一次排序為第5列，排序方式為公升序，即排程順序，第二次排序為第3列， 也就是說，零件，排序方法也是公升序的。兩種排序等效於上述兩個約束。 效果如下圖5所示
上面圖5中的公式是乙個大動態資料，為了方便後續的二次寫入功能，配合choosecols將其劃分為單列動態陣列，依次輸入1到5和5公式的引數
choosecols(sort(sort(filter(b3:f5000,(b3:b5000<>"")*(d3:d5000<>"-")),5),3),1)
choosecols(sort(sort(filter(b3:f5000,(b3:b5000<>"")*(d3:d5000<>"-")),5),3),2)
判斷供不應求的大邏輯是從庫存中減去需求，在減去需求的同時考慮扣單，比如庫存400，第一需求700，操作-300，第二需求300，累計少付-600，單項需求-300。 分別輸入以下功能：
訂購要求：xlookup（i3，預定發布！B4：B12，計畫發布！c4：c12），需要組裝的程式集數。
累計需求：sumifs（$m$3：m3，$k$3：k3，k3），這是該部分對應的累計需求，如果有通用零件：A和B訂單共享C部分，C對應A的需求量為200，B的需求量為300，累計需求量為500。
累計物料短缺：IF（L3-N3>0,0，L3-N3），傳送庫存的累計需求，大於0，顯示為0;
單片底材：如果（abs（O3）可以組裝：
if(countifs($p$3:$p$60,"<0"，$h$3，h3）>0,0，m3），乙個裝配體必須沒有底材，即沒有負底材，才能組裝，如果沒有底材，則返回m3，m3為裝配需求。最終結果如下圖 6 所示
圖6 表1為生產計畫排程主表，根據物料清單計算的未完成物料資訊可在此表上使用。
可組裝的實際單元數。
xlookup（b4：b12，零件裝配表！I：I，零件裝配臺！q:q)
裝配零件缺口名稱：
textjoin(","，，filter（零件裝配表！$k$3 ， （零件組裝臺！$i$3 = b4）*（零件裝配臺！$p$3:$p$60<0)))
裝配零件間隙數量：
textjoin(","，，filter（零件裝配表！$p 3 美元：$p 60 美元，（零件組裝臺！$i$3 = b4）*（零件裝配臺！$p$3:$p$60<0)))
完成後，如下圖 7 所示
圖7計算了上述步驟中表1中排程部件對應的底料資訊，該需求的核心知識點為：
知識點一：過濾IF的相對引文方法，將不規則資料轉換為標準一維資料
知識點二：使用過濾功能清空包含空白行和特殊符號的資料。
知識點三：使用排序功能將同型別資料排序在一起。
知識點4：用累計庫存減去累計需求，判斷物料是否少付。
知識點5：利用統計功能統計負數來判斷材料是否齊全，從而判斷出可以生產的裝配資料;
知識點6：使用過濾功能加合併功能，將底料部件的名稱和數量參考表1
我是顧哥：
從事製造業20年，在企業運營、連鎖管理、智慧型製造系統等方面具有豐富的實踐經驗。 擅長企業智慧型化和靈活的計畫運營管理，善於通過企業流程優化和標準化、企業管理、計畫運營的引入來提公升企業效率在提高企業準時交貨率、降低企業庫存、輸出智慧型製造人才等方面擁有豐富的經驗。 學習PMC生產計畫，關注Guge計畫！