采购成本分 析表.docVIP

采购成本分析表-创建数据变化表 4.1.1 创建数据变化表 　　本案例首先创建采购成本和储存成本在不同批次下的数据变化表，然后利用公式计算最小成本、采购批次和采购量，接下来添加年采购量、年采购成本和单位存储成本滚动条窗体，最后制作存储成本和采购成本的散点图。 　　本案例分析中会使用到下面的公式： 　　采购数量=年采购量/年采购批次 　　平均存量=采购数量/2 　　存储成本=平均存量×单位存储成本 　　采购成本=年采购批次×采购成本 　　总成本=存储成本+采购成本 　　 Step 1 创建工作簿 　　启动Excel自动新建一个工作簿，然后保存并命名为“采购成本分析图.xls”。 　　Step 2 重命名工作表 　　双击Sheet1的工作表标签进入工作表重命名状态，输入“采购成本分析”后按键确认。 　　Step 3 删除多余的工作表 　　①单击Sheet2工作表标签，然后按住键单击Sheet3工作表标签，以选中这两个工作表。 　　②右键单击选定的工作表标签，从弹出的快捷菜单中选择“删除”。 　　 Step 4 输入表格标题 　　如图所示输入表格各个字段的标题内容。 　　Step 5 输入表格数据 　　①在A2:A13单元格区域输入“年采购批次”。 　　②调整A列和H列列宽以完全显示文本。 　　 Step 6 设置单元格自定义格式 　　①单击B16单元格，按组合键弹出“单元格格式”对话框。 　　②单击“数字”选项卡，在“分类”列表框中选择“自定义”。 　　③在“类型”文本框中输入 “#,##0.00”。 　　④单击“确定”按钮。 　　⑤采用类似的方法设置E16单元格的自定义格式为“0次/年”，I16单元格的自定义格式为“0件/次”。 　　Step 7 编制采购数量、平均存量等计算公式 　　①单击B2单元格，在编辑栏中输入以下公式，按键确认。 　　=$B$19/A2 　　②单击C2单元格，在编辑栏中输入以下公式，按键确认。 　　=B2/2 　　③单击D2单元格，在编辑栏中输入以下公式，按键确认。 　　=C2*$I$19 　　④单击E2单元格，在编辑栏中输入以下公式，按键确认。 　　=A2*$E$19 　　⑤单击F2单元格，在编辑栏中输入以下公式，按键确认。 　　=D2+E2 　　⑥选中B2:F2单元格区域，将光标移到F2单元格右下角，当光标变为形状时双击即可在B3:F13单元格区域快速地复制公式。 4.1.2 计算最小成本采购次数和采购量 　　Step 1 编制“最低采购成本”计算公式 　　单击B16单元格，在编辑栏中输入以下公式，按键确认。 　　=MIN(F2:F13) 　　 Step 2 编制采购批次计算公式 　　单击E16单元格，在编辑栏中输入以下公式，按键确认。 　　=INDEX(A2:A13,MATCH(B16,F2:F13,0)) 　　本公式的讲解请参见第102页。 　　 Step 3 编制“采购量”计算公式 　　单击I16单元格，在编辑栏中输入以下公式，按键确认。 　　=INDEX(B2:B13,MATCH(B16,F2:F13,0)) 　关键知识点讲解1 　　1.MIN函数 　　MIN函数是常用的数学函数，用于返回一组值中的最小值。 　　函数语法 　　MIN(number1,number2,...) 　　number1, number2,...是要从中找出最小值的1到30个数字参数。 　　函数说明 　　l 可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。 　　l 如果参数是数组或引用，MIN函数则仅使用其中的数字，空白单元格、逻辑值、文本或错误值将被忽略。如果逻辑值和文本字符串不能忽略，则应用MINA函数。 　　l 如果参数中不含数字，MIN函数则返回0。 　　函数简单示例 　　公式说明(结果) 　　=MIN(10,7,9,6,3)返回5个正数中的最小值3(3) 　　=MIN(-10,-9,-7,-6,-3)返回5个负数中的最小值-10(-10) 　　2.MATCH函数 　　MATCH函数是常用的查找函数之一，用于返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是LOOKUP函数。 　　函数语法 　　MATCH(lookup_value,lookup_array,match_type) 　　lookup_value为需要在数据表(lookup_array)中查找的数值。lookup_value 可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。 　　lookup_array为可能包含所要查找的数值的连续的单元格区域。lookup_array应为数组或数组引