20.4　利用数据透视表制作外加工订单

示例20.4　外加工订单模板

图20-24所示展示的生产计划工作表中记录了某公司2012年需要进行外加工的生产订单明细数据，客户资料工作表中记录了该公司的客户信息。

图20-24　生产计划数据列表

如果用户希望通过“生产计划”数据列表自动生成向外加工单位下达的生产订单，请参照以下步骤。

步　骤1

向“生产计划”数据列表定义名称DATA。

=OFFSET（生产计划！$A$1,,,COUNTA（生产计划！$A：$A）,COUNTA（生产计划！$1：$1））

步　骤2

以名称“DATA”为数据源在“外加工订单”工作表中创建动态数据透视表，如图20-25所示。

图20-25　创建数据透视表

步　骤3

在数据透视表中插入计算字段“单价”，计算公式为“单价=加工费总额/订单数量”，如图20-26所示。

图20-26　添加计算字段

注意

插入的计算字段是“单价”，而不是“单价”，中间有一个空格，否则将会与数据透视表原有字段重复，会出现错误提示。

步　骤4

在“外加工订单”工作表的第2行A2、C2、E2和G2单元格中输入客户资料的项目并在B2、D2、F2、H2单元格中使用公式引用“客户资料”工作表中的数据，同时，还可以根据数据透视表筛选字段“外加工单位”的选择结果显示详细信息，如图20-27所示。

B2、D2、F2、H2单元格的公式分别为：

B2=VLOOKUP（$B$1，客户资料！$A:$E，MATCH（SUBSTITUTE（外加工订单!A2，”：”，“”），客户资料！$A$1：$E$1,),)

D2=VLOOKUP（$B$1，客户资料!$A:$E,MATCH(SUBSTITUTE(外加工订单!C2，”:”，“”)，客户资料！$A$1：$E$1，），）

F2=VLOOKUP（$B$1，客户资料!$A:$E,MATCH(SUBSTITUTE(外加工订单!E2，”：”，””)，客户资料！$A$1：$E$l，），）

H2=VLOOKUP（$B$1，客户资料!$A:$E,MATCH(SUBSTITUTE(外加工订单！G2，”：”，””)，客户资料！$A$1：$E$1，），）

图20-27　设置显示外加工单位信息的公式

步　骤5

在数据透视表上方插入4行空白行，输入外加工订单的其他相关资料，如公司名称、地址和订单约定条款等，如图20-28所示。

图20-28　输入外加工订单相关资料

步　骤6

美化数据透视表，最后效果如图20-29所示。

图20-29　美化数据透视表

本例通过对生产计划数据库创建动态数据透视表结合Excel函数公式生成动态的“外加工订单”模板，并通过对数据透视表筛选字段“外加工单位”的选择迅速生成各个加工单位的订单，极大地提高了下达订单的规范性和准确性。