326-Excel应用（物料需求核定与采购计划编制） .doc

excel应用(物料需求核定与采购计划编制) 【试验目的】1. 掌握工作区地建立与应用2. 掌握筛选无重复数据3. 运用sumproduct()行数分类汇总【实验内容】1. 工作区的建立与使用2. 物料需求核定3. 筛选无重复数据(物料编码)4. 分类汇总(分需求单位汇总)5. 分类汇总(分用途类别汇总)6. 采购计划编制【实验步骤】1. 工作区的建立与使用1.1. 文件准备1.1.1. 在优盘（或当地硬盘）建立“采购计划”文件夹1.1.2. 将文件“物料平衡表.xls”移至“采购计划”文件夹1.1.3. 打开“物料平衡表.xls”，选择“物料编码”工作表，右击工作表标签，弹出菜单1.1.4. 选择“移动和复制工作表”，弹出对话框，选择“新建工作簿”1.1.5. 将新建工作簿“book1.xls”保存命名为“物料编码,xls” （保存到“采购计划”文件夹，下同）1.1.6. 新建工作簿，保存命名为“物料需求核算.xls” 1.1.7. 新建工作簿，保存命名为“采购计划.xls”1.1.8. 关闭、保存所有excel文件1.2. 建立工作区1.2.1. 打开“物料编码,xls”、“物料平衡表.xls”、“物料需求核算.xls”、“采购计划.xls”1.2.2. 选择菜单“文件/保存工作区”1.2.3. 保存命名为“采购计划.xlw”1.3. 打开工作区1.3.1. 打开“采购计划.xlw”excel将同时打开相关文件1.3.2. 从“物料平衡表.xls”复制名称定义code、name、unit的引用位置（观察其与原来定义的区别），在物料需求核算.xls、采购计划.xls定义名称code、name、unit。2. 物料需求核定2.1. 进入物料需求核算.xls2.2. 将下表拷贝至sheet1，工作表重命名为“生产物料需求”表（1.3a）2008年生产物料需求核算表需求单位产品计划产量单位物料需求消耗定额定额单位物料单位需求量物料编码名称与规格一分厂甲2500件钢锭500kg/件二分厂乙1000件钢锭1000kg/件三分厂丙1800件工字钢500kg/件三分厂丙刀具1500三分厂丙轴承/55mm2500u 引用物料编码、物料单位（下同）u 计算钢锭、钢锭、工字钢的需求量（消耗定额计划产量）2.3. 将下表拷贝至sheet2，工作表重命名为“基建物料需求”表（1.3b）2008年基建物料需求计划表需求单位物料需求物料单位需求量编码名称与规格基建科木材/原木100水泥/4254502.4. 将下表拷贝至sheet3，工作表重命名为“维修物料需求”表（1.3）2008年维修物料需求计划表需求单位物料需求物料单位需求量物料编码名称与规格一分厂薄钢板/2.5mm200一分厂槽钢/12200一分厂木材/原木80一分厂水泥/425350一分厂轴承/55mm800一分厂铜芯橡皮线/by502000一分厂工作服/棉布600二分厂铜芯橡皮线/by502000二分厂水泥/425150二分厂轴承/55mm400二分厂工作服/棉布110三分厂铜芯橡皮线/by501500三分厂薄钢板/2.5mm110三分厂电石/一级80三分厂水泥/425120三分厂木材/原木170四分厂角钢/10300四分厂槽钢/12120四分厂木材/原木110四分厂水泥/425180四分厂电石/一级60四分厂工作服/棉布1402.5. 插入工作表，重命名为“技改物料需求”，移至最后，拷贝下表：表（1.3）2008年技改项目物料需求计划表维修项目物料需求物料单位需求量编码名称与规格1、高炉大修薄钢板/2.5mm801、高炉大修槽钢/12901、高炉大修水泥/425601、高炉大修电石/一级102、厂房大修木材/原木1202、厂房大修水泥/425402、厂房大修角钢/10502、厂房大修电石/一级203、余热工程木材/原木403、余热工程水泥/4251504、福利设施木材/原木4004、福利设施水泥/4252004、福利设施角钢/10302.6. 插入工作表，重命名为“物料需求汇总1”，移至最后，拷贝下表：表(1.3f)2008年物料需求汇总表(1)序号用途类别物料编码物料名称及规格计量单位分单位需求量备注合计一分厂二分厂三分厂四分厂2.7. 插入工作表，重命名为“物料需求汇总2”，移至最后，拷贝下表：表(1.3gf)2008年物料需求汇总表(2)序号物料编码物料名称及规格计量单位按用途类别需求量备注合计生产物料基建物料维修物料技改物料3. 筛选无重复数据(物料编码)3.1. 选择“生产物料需求”工作表，选中物料编码区域3.2. 选择菜单“数据/筛选/高级筛选”3.3. 在“高级筛选”对话框中，选中“讲筛选结果复制到其他位置”，在工作表中选定适当的单元格，选中“选择不重复的纪录”，选择“确定”，excel将自动显示选择结果3.4. 对“基建物料需求”、“维修物料需求”、“技改物料需求”工作表重复3.2、3.3步骤。3.5. 将筛选结果复制到“物料需求汇总1”，并填充用料类别；3.6. 引用“物料名称及规格”，“计量单位”4. 分类汇总(分需求单位汇总)4.1. 分单位需求量（“生产物料/维修物料”）合计4.1.1. 选中“物料需求汇总1”工作表4.1.2. 在相应区域的左上角，插入函数sumproduct()，确定参数。u 参数aeeay1：选中“生产物料需求/维修物料需求”工作表的物料编码区域（绝对引用），输入“=”，选中单元格所在行的物料编码（列绝对引用）；u 参数aeeay2：选中“生产物料需求/维修物料需求”工作表的需求单位区域（绝对引用），输入“=”，选中单元格所在列的需求单位（行绝对引用）；u 参数aeeay3：选中“生产物料需求/维修物料需求”工作表的需求量区域（绝对引用）；u 选择“确定”后，在辑栏内分别在3个参数两端加括号，将其间的“，”改成“*”号。u 按住“ctrl+shift”键，点击“enter”键。4.1.3. 将所设置的公式填充至相应的区域。4.1.4. 设置单元格格式，当数据为0时，不显示。4.1.5. “生产物料/维修物料”区域的“合计”，分别对对分单位需求量求和。4.2. 不分需求单位的“基建物料/技改物料”的“合计”：4.2.1. 分别在“基建物料/技改物料”的“合计”区域左上角，插入函数sumproduct()，确定参数。u 参数aeeay1：选中“基建物料需求/技改物料需求”工作表的物料编码区域（绝对引用），输入“=”，选中单元格所在行的物料编码（列绝对引用）；u 参数aeeay3：选中“基建物料需求/技改物料需求”工作表的需求量区域（绝对引用）；u 选择确定后，在辑栏内分别在2个参数两端加括号，将其间的“，”改成“*”u 按住“ctrl+shift”键，点击“enter”键。4.2.2. 将所设置的公式填充至相应的区域。5. 分类汇总(分用途类别汇总)5.1. 选中“物料需求汇总1”工作表5.2. 参照步骤3.1、3.2、3.3，筛选物料编码(无重复数据)5.3. 将筛选的物料编码拷贝至“物料需求汇总2”工作表的相应位置5.4. 选择“物料需求汇总2”工作表5.5. 引用“物料名称及规格”，“计量单位”5.6. 在“按用途类别需求量”区域的左上角，插入函数sumproduct()，确定参数。u 参数aeeay1：选中“物料需求汇总1”工作表的物料编码区域（绝对引用），输入“=”，选中单元格所在行的物料编码（列绝对引用）；u 参数aeeay2：选中“物料需求汇总1”工作表的用途类别区域（绝对引用），输入“=”，选中单元格所在列的用途类别（行绝对引用）；u 参数aeeay3：选中“物料需求汇总1”工作表的需求量“合计”区域（绝对引用）；u 选择确定后，在辑栏内分别在3个参数两端加括号，将其间的“，”改成“*”号。u 按住“ctrl+shift”键，点击“enter”键。6. 采购计划编制6.1. 选择“采购计划.xls”工作簿6.2. “期末储备”核定6.2.1. 将下表拷贝至“sheet1”，工作表重命名为“期末储备”表(1.4a)2008年底物料储备量估计物料编码物料名称及规格计量单位期末储备量备注钢锭薄钢板/2.5mm200工字钢500槽钢/12150角钢/10180木材/原木250水泥/425400电石/一级120工作服/棉布500刀具100铜芯橡皮线/by50600轴承/55mm600【备注】钢锭总需要量2250t，全年按360天计算，供应周期为60天，供应期平均临时需求量(保险储备量)为100t；按照下列公式计算期末储备量，填入上表。日需求量 总需要量日历天数周转储备量 日需求量供应周期期末储备量 周转储备量2 保险储备量 6.2.2. 引用“物料编码”，“计量单位”6.3. 采购参数6.3.1. 将下表拷贝至“sheet1”，工作表重命名为“物料单价及订购次数”表(1.4b)2008年度物料单价及订购次数一览表物料类别物料编码物料名称及规格计量单位单价(元 )订购次数钢材钢锭250015钢材薄钢板/2.5mm400020钢材工字钢300010钢材槽钢/12320010钢材角钢/10380010建材木材/原木260020建材水泥/42560020防护工作服/棉布12012工具刀具4012电料铜芯橡皮线/by50816轴承轴承/55mm5020化工电石/一级800126.3.2. 引用“物料编码”，“计量单位”6.4. 采购计划编制6.4.1. 将下表拷贝至“sheet3”，工作表重命名为“物料采购计划表”表(1.5)2008年物料采购计划表序号物料编码物料名称及规格计量单位期初库存计 划需求量期末储备计划采购量单价(元)订购次数一 次订购量订购金额(元)6.4.2. 将“物料需求核算.xls”中的“物料需求汇总2”工作表的物料编码拷贝至“物料采购计划表”的物料编码6.4.3. 填充序号6.4.4. 引用“物料名称及规格”，“计量单位”6.4.5. 引用数据“期初库存”、“计划需求量”、期末储备、单价(元)、订购次数u 在相应区域中插入函数index(数据引用区域，match(物料编码单元格,物料编码引用区域，0)；其中“物料编码单元格”指向物料编码，相对引用；数据引用区域、物料编码引用区域按下表