2021是灾难的一年，全球半导体缺料，交货期延长，价格猛涨；
2021是辛苦的一年，天天追料，天天开会，天天市场找料，开发替代料。
最辛苦的就是广大供应链（采购）的同仁。
如何快速找到最缺的那些物料，如何快速了解缺料状况，如何提高和供应商的开会效率？
这就必须要有供应需求平衡表（Supply Demand Balance Report）,有的公司也叫缺料表（Shortage report），SD report, DS report.
如何利用Excel, 根据需求状况和供应状况，快速地制作供应需求平衡表呢？
下面我将分享如何用Excel制作供应需求平衡表？
第一步，准备四张张表。
- 需求表。可以从系统导出，或手工制作。
- 供应表。即供应商回复的交货排成。可以从系统导出，或手工制作。
- 库存表。可以从系统导出。
- 缺料零件明细。
需求表。
本例按月份制作需求表，为了便于利用公式，我们都使用每个月的一天。
如果只想显示月份，可以利用设置单元格格式，把格式设为mmm 或m"月".
- mmm.显示英文月份
- m"月".显示中文月份
供应表
由于交货日期是具体到每天，而需求表是按月份的（每月一号）。为了便于利用公式，我们还要添加一列辅助日期列，把日期变成每月一号。
在交货日期后插入一列，并在F2输入公式，并向下复制填充，
=DATE(YEAR(E2),MONTH(E2),1)
- Year()是日期函数，返回年份；
- Month()是日期函数，返回月份；
- Date()是日期函数，返回年月日，语法格式为Date(年份,月份,日)
库存表.
库存表，很简单，直接从系统导出即可。
缺料零件明细。
缺料零件明细，也很简单，把自己负责的零件复制过去即可，另外加上必要的信息，如制造商，制造商料号等。
第二步，为每个零件号，快速插入两行空白行。
供应需求平衡表一般是三列，需求(Demand)，供应(Supply)，缺料(Balance, Shortage).
所以要为每个零件号准备三列。
隔行插入空白行的方法有很多，在此，我们介绍一种最简单的方法。
- 在最后一列插入辅助列“序列”
- 然后按顺序编号。本例有四颗料号，用1~4编号。
- 把编号再向下复制两次。
- 按"序列"升序排序。
- 删掉辅助列。
第三步，创建模板。
- 添加库存列。
- 添加供需列。
- 添加月份。直接从需求表复制即可。
第三步，设置公式。
设置库存公式。
- 在E2输入公式=VLOOKUP(A2,库存表!A:E,5,0)
- 选择E2:E4,然后向下复制
设置需求公式。
- 在G2输入公式
=VLOOKUP($A2,需求表!$A:$P,MATCH(G$1,需求表!$1:$1,0))
Vlookup+Match的使用方法，可以参考我前面的文章“Vlookup之多列数据查询”。
- 向右复制填充
- 筛选需求，选择G2:R2, 向下拖拉填充公式。
设置供应公式
- 在G3输入公式
=SUMIFS(供应表!$G:$G,供应表!$A:$A,缺料零件明细!$A2,供应表!$F:$F,缺料零件明细!G$1)
Sumifs是多条件求和函数，语法个是为：
Sum(求和区域，条件区域1，条件1，条件区域2，条件2）
- 向右复制填充
- 筛选供应，选择G3:R3, 向下拖拉填充公式。
设置缺料公式
- 在G4输入公式=E2+G3-G2， 即库存+供应-需求。
- 在H4输入公式=G4+H3-H2，即上月剩余+本月供应-本月需求。
- 将H4的公式向右复制填充。
- 筛选缺料，选择G4:R4, 向下拖拉填充公式。
第四步，格式设置。如果小于零，就显示红色。
- 选择区域，
- 右键，选择设置单元格格式，
- 选择自定义，选择#,##0;[红色]-#,##0。 也可以手工输入0;[red]-0
大功告成。模板设置好后，以后可以直接套用，如果有新料，可以直接加进来。
如果缺料，就显示红色，一目了然。
所以信息都在一个页面里(all in one page), 不用各种表找来找去，浪费时间。老板看了，也会夸奖我们。
也可以直接发给供应商，供应商可以根据此报表安排优先级生产。
此模板也使用于销售/客服服务/项目管理(Sales,CS,PM)和生产计划(PMC, Planner)，用这个报表，可以很好地跟内部沟通，跟客户沟通。
以后总会用得上，值得关注，收藏，点赞，转发。
