欠料计算：从入门到精通，供应链管理的终极指南
全文约2600字；阅读时间：约7分钟；听完时间：约14分钟
每周日，我都会对本周（第27周）古老师发布的文章进行一个自我总结，详细记录笔记，并添加关键索引词，以便于日后快速查找。本周的关键词是"欠料计算"。 Around this topic, I wrote a series of articles covering calculations from finished products to components, parts, and even the most complex multi-level substitute materials.
Monday: 揭秘PMC欠料计算的艺术
Monday: The Art of PMC Material Shortage Calculation
设计思路： The basic formula for material shortage is: Shortage = Demand - Current Inventory
Core Functions:
- XLOOKUP: Lookup function for inventory reference (unique codes)
- SUMIFS: Multi-condition sum function for inventory consolidation (multi-location)
- FILTER: Filter function used with SUM for conditional summing
创新亮点： Using FILTER function for multi-condition summing solves SUMIFS's limitation with memory arrays, improving calculation flexibility and efficiency.
Tuesday: 智能供应链：XLOOKUP巧解欠料谜题
Tuesday: Smart Supply Chain: XLOOKUP Solves Material Shortage Puzzles
设计思路： For single-level BOM shortage calculation:
- Reference product demand (parent quantity) from BOM
- Multiply by component consumption ratio
- Compare with inventory to determine shortage
Core Functions: XLOOKUP for demand/inventory lookup, IF for shortage condition check
Wednesday: 欠料运算精进
Wednesday: Advanced Material Shortage Calculation
设计思路： For components shared by multiple parents, we use conditional summing instead of traditional lookup to solve "one-to-many" problems.
Core Functions: UNIQUE for deduplication, SUMIFS for demand consolidation, XLOOKUP for inventory
Thursday: 多产品的精细化排程
Thursday: Detailed Scheduling for Multiple Products
设计思路： For dated product demands, we use FILTER to select BOMs and REDUCE for progressive consolidation.
创新亮点： REDUCE+LAMBDA achieves component demand consolidation across products.
Friday: 库存天数计算
Friday: Inventory Days Calculation
设计思路： Combine XLOOKUP (inventory) with SUMIFS (demand), use SCAN for cumulative demand, N for logical conversion.
Saturday: 物料替代与MRP计算
Saturday: Material Substitution and MRP Calculation
设计思路： Establish standardized substitution rules: part classification, priority order, substitutability (1=no substitute, >1=has substitutes).
总结 Summary
本周我们系统性地学习了欠料计算的各个方面，从基础公式到高级应用，掌握了XLOOKUP、SUMIFS、FILTER、REDUCE等函数在供应链管理中的创新应用。 This series provides comprehensive knowledge and practical tools for PMC professionals to improve production planning accuracy and efficiency.
