Excel数据处理与分析：快速制作采购申请表

仓管员要掌握各物资商品每天的库存量，对库存不足的物资商品要及时申请采购，避免物品短缺造成不利影响。

本节主要介绍如何在Excel中通过使用函数公式，根据库存表的库存量自动统计需要采购的物品品种以及数量和预估成本。

新建空白工作表，重命名为“采购表”，在单元格中输入标题、日期以及表头文字，其中日期使用TODAY函数自动返回系统当前的日期，如图9.6-1所示。

图9.6-1　新建采购表并输入表头文字

（1）统计需要采购的物品

一般情况下，仓库中不同的物资商品有不同的最低存量，物品的库存数量不应低于对应的最低存量，库存表中的“存量系数”即表示指定最低存量的信息。在本例中，存量系数为“1”的物品，最低存量不得低于500套；存量系数为“2”的物品，最低存量不得低于300套；存量系数为“3”的物品，最低存量不得低于100套。

选择B4单元格，输入数组公式“=IFERROR（INDEX（库存表[产品名称]，SMALL（IF（（库存表[存量系数]={1，2，3}）*（库存表[库存数量]＜{500，300，100}），ROW（库存表[产品名称]）-4），ROW（A1））），""）”，输入完毕后按Ctrl+Shift+Enter组合键结束，并将公式向下填充，即可从库存表中查找引用出低于最低存量的物品名称，这里填充至B10单元格，用户可以根据实际需要决定要填充该公式的行数，结果如图9.6-2所示。

图9.6-2输入查找引用库存不足的物品名称的公式

（2）添加自动的序号

选择A4单元格，输入公式“=IF（B4=""，""，COUNTA（B$4：B4））”，输入完毕后按Enter键结束，并将公式向下填充，即可为B列需要采购的产品自动添加序号，结果如图9.6-3所示。

图9.6-3　输入自动添加序号的公式

（3）查找库存数量

选择C4单元格，输入公式“=IF（B4=""，""，VLOOKUP（B4，库存表[[产品名称]：[库存数量]]，COLUMN（M：M）-1，））”，输入完毕后按Enter键结束，并将公式向下填充，即可根据B列中的产品名称查找引用出库存表中的库存数量，结果如图9.6-4所示。

图9.6-4　输入查找引用库存数量的公式

（4）计算最低采购数量

计算条件为最低存量减去当前库存数量。选择D4单元格，输入公式“=IF（B4=""，""，CHOOSE（VLOOKUP（B4，库存表[[产品名称]：[存量系数]]，4，），500，300，100）-C4）”，输入完毕后按Enter键结束，并将公式向下填充，即可根据B列的名称计算出各种产品的最低应该采购的数量，结果如图9.6-5所示。

图9.6-5　输入计算最低采购数量的公式(www.xing528.com)

（5）计算建议采购数量

本例中，建议采购数量条件为各物品最低存量的2倍。选择E4单元格，输入公式“=IF（AND（B4=""，B3＜＞""），"合计："，IF（B4=""，""，CHOOSE（VLOOKUP（B4，库存表[[产品名称]：[存量系数]]，4，），500，300，100）*2））”，输入完毕后按Enter键结束，并将公式向下填充，即可根据B列的名称计算出各种产品的建议采购数量，并在最后一条数据记录下面显示“合计：”文本信息，结果如图9.6-6所示。

图9.6-6　输入计算建议采购数量的公式

（6）计算预估采购成本

预估采购成本的计算条件是“建议采购数量”乘以“库存表”中的“加权平均单价”。选择F4单元格，输入公式“=IF（AND（B4=""，B3＜＞""），SUM（F$3：F3），IF（B4=""，""，E4*VLO OKUP（B4，库存表！A：M，COLUMN（M：M），）））”，输入完毕后按Enter键结束，并将公式向下填充，即可计算出各种产品的预估采购成本，并在最后一条记录下面对全部产品的预估采购成本进行求和，结果如图9.6-7所示。

图9.6-7　输入计算预估采购成本的公式

（7）自动添加边框

选择A3：F10单元格区域，切换至“开始”选项卡，在“样式”组中单击“条件格式”命令下拉按钮，在打开的列表中选择“新建规则”选项，操作如图9.6-8所示。

图9.6-8　设置条件格式

接着如图9.6-9所示，在打开的“新建格式规则”对话框中，选择“使用公式确定要设置格式的单元格”选项，并在“编辑规则说明”区域的输入框中输入公式“=AND（$A3＜＞""，A$3＜＞""）”，然后单击“格式”按钮设置边框，操作完毕后单击“确定”按钮关闭对话框完成设置。

图9.6-9　输入设置自动添加边框的公式

返回工作表中，即可看到通过条件格式设置的自动添加单元格边框的效果，如图9.6-10所示。

图9.6-10　采购单制作完成

至此，自动统计、计算的采购申请单已经制作完成，当有新增入库记录或出库记录时，该表格会自动根据“库存表”中的存量更新数据。

通过对本章的学习，了解了库存管理工作中的基本套路，物资商品在验收入库时，必须严格根据已审批的请购单按质、按量验收，根据发票记录的名称、规格、型号、单位、数量、单价金额打印入库单，并在货物上标明进货日期，对不符合质量要求的进仓货物要坚决退货，严格把好质量关。发货时要严格审核领用手续是否齐全，严格验证审批人的签名；物品出库或入库要及时打印出库单或入库单，随时查核，做到入单及时，当日单据当日清理，以及要做好月底仓库盘点工作，及时结出月末库存数报予财务主管，做好各种单据报表的归档管理工作。