采购成本分析表-创建数据变化表

采购成本分析表-创建数据变化表

4.1.1创建数据变化表

本案例首先创建采购成本和储存成本在不同批次下的数据变化表，然后利用公式计算最小成本、采购批次和采购量，接下来添加年采购量、年采购成本和单位存储成本滚动条窗体，最后制作存储成本和采购成本的散点图。

本案例分析中会使用到下面的公式：

采购数量=年采购量*年采购批次

平均存量=采购数量/2

存储成木=平均存量*单位存储成木

采购成本=年采购批次*采购成本

总成木=存储成木+采购成木

Step1 创建工作簿

启动Excel自动新建一个工作簿，然后保存并命名为“采购成本分析图.xls”。

Step2重命名工作表

双击Sheet1的工作表标签进入工作表重命名状态，输入“采购成本分析”后按键确认。

Step3删除多余的工作表

①选中后两个工作表.

②右键单击选定的工作表标签，从弹出的快捷菜单中选择“删除”。

Step4输入表格标题

如图所示输入表格各个字段的标题内容。

Step5输入表格数据

①在A2:A13单元格区域输入“年采购批次”。

②调整A列和H列列宽以完全显示文本。

Step6设置单元格自定义格式

①单山B16单元格，按纽合键弹山“单元格格式”对话框。

②单击“数宁”选项卡，在“分类”列表框中选择“自定义”。

③在“类型”文本框中输入“#.##0.00”。

④单击“确定”按纽。

⑤采用类似的方法设置E16单元格的自定义格式为”0"次年”“，I16单元格的自定义格式为”0"件次”“。

Step7编制采购数量、平均存虽等计算公式

①单击B2单元格，在编辑栏中输入以下公式，按键确认

=$B$19/A2

②单击C2单元格，在编辑栏中输入以下公式，按键确认。

=B2/2

③单击D2单元格，在编辑栏中输入以下公式，按键确认.

C2*$I$19

④单击E2单元格，在编辑栏中输入以下公式，按键确认。

=A2*SE$19

⑤单击F2单元格，在编辑栏中输入以下公式，按键确认。

=D2+E2

⑥选中B2：F2单元格区域，将光标移到F2单元格右下角，当光标变为”+“形状时双击即可在B3：F13单元格区域快速地复制公式

4.1.2计算最小成本采购次数和采购量

Step1编制“最低采购成木计算公式

单击B16单元格，在编辑栏中输入以下公式，按键确认：

=MN(F2:F13)

Step2编制采购批次计算公式

单击E16单元格，任编辑栏中输入以下公式，按键确认：

=INDEX(A2:A13,MATCH(B16,F2:F13,0))

Step3编制”采购量”计算公式

单击I16单元格，在编辑栏中输入以下公式，按键确认：

=INDEX(B2:B13,MATCH(B16,F2:F13,0))

关键知识点讲解1

1.MN函数

MN函数是常用的数学函数，用于返回一组值中的最小值。

函数语法

MIN(number1,number2,...)

numberl,.number2.,是要从中找出最小值的1到30个数字参数。

函数说明

（1）可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。

（2）如果参数是数组或引用，MN函数则仅使用其中的数字，空白单元格、逻辑值、文本或错误值将被忽略。如果逻辑值和文本字符串不能忽略，则应用MNA函数。

（3）如果参数中不含数字，MN函数则返回0。

函数简单示例

公式说明（结果）

=MN(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应为数组或数组引用。

match_type为数学-1、0或1。Match_type指明Microsoft Excel如何在lookup_array中查找lookup_value。

如果match_type为1，MATCH函数查找小于或等于lookup_value的最大数值。lookup_array必须按升序排列：-2、-l、0、I、2、A-Z、FALSE、TRUE。

如果match_type为0，MATCH函数查找等于lookup_value的第1个数值。lookup_array可以按任何顺序排列。

如果match_type为-1，MATCH函数查找大于或等于lookup value的最小数值。lookup_array必须按降序排列：TRUE、FALSE、Z-A、、2、1、0、-l、-2。

如果省略match_type，则假设为1。

函数说明

MATCH函数返回lookup_array中目标值的位置，而不是数值本身。例如MATCH("b","a","b","c",0)返回2，即“b”在数组"a”,"b","c"中的相应位置。

在找文本值时，MATCH函数不区分大小写字母。

如果MATCH函数查找不成功，则返回错误值#N/A。

如果match_type为0且lookup_value为文本，lookup value可以包含通配符、星号（*）和问号（？)。星号可以匹配任何字符序列，问号可以匹配单个字符。

函数简单示例

公式说明（结果）

3.INDEX函数

INDEX函数也是常用的查找函数之一，用于返回表或者区域中的值或对值的引用。INDEX函数有两种形式：数组形式和引用形式。

函数语法

(1)数组形式的函数语法

INDEX(array,row_num,column_num)

array是一个单元格区域或数组常量.。

如果数组中只包含一行或一列，则可不使用相应的row_num或column_num参数。

如果数组中包含多个行和列，但只使用了row_num或column_num，INDEX将返回数组中整行或整列的数组。

row_num用丁选择要从中返回值的数组中的行。如界省略row_num，则需要使用column_num。

column_num用于选择要从中返可值的数组中的列。如果省略column_num，则需要使用row_num。

函数说明

①如果同时使用了row_num和column_num参数，INDEX将返回row_num和column_num交叉处单元格中的值。

②如界将row_num或column_num设置为0(零)，INDEX函数将分别返回整列或整行的值数组。要将返回的值用做数组，则要在行的水平单元格区域和列的垂直单元格区域以数组公式的形式输入INDEX函数。

③row_num和column_num必须指向数组中的某一个单元格，否则INDEX返回#REF！错误值。

函数简单示例

公式说明（结果）

=NDEX({1,2,2;3,4,5},1,2)返回数组中第1行第2列的值(2)

=NDEX({"A","B","C";1,2,3},0,0)返回数组中第1行第1列的数值(A)

(2)引用形式的函数语法

INDEX(reference,row_num,column_num,area_num)

reference是对一个或多个单元格区域的引用。

如果要对引用输入一个非连续区域，则应使用括号将该引用括起来。

如果引用中的每个区域都只包含一行或一列，则可不使用相应的row_num或column_num参数。例如对于单行引用，可以使用

INDEX(reference,column_num)。

row_num是要从中返回引用的引用中的行编号。

column_num是要从中返回引用的引用中的列编号。

area_num用于选择要从中返回row_num和column_num的交叉点的引用区域。选择或输入的第1个区域的编号是1，第2个区域的编号是2，依次类推。如果省略area_num,INDEX函数将使用区域1。

函数说明

①在reference和area_num选择了特定的区域后，row_num和column_num将选择一个特定的单元格：row_numl是该区域中的第1行，column_numl是该区域中的第1列，依次类推。INDEX返回的引用将是row_num和column_num的交义点。

②如果将row_num或column_num设置为0(零)，INDEX将分别返回整列或整行的引用。

③row_num、column_num和area_num必须指向引用中的某一个单元格，否则INDEX返回#REF!错误值。如果省略了row_num和column_num，INDEX将返回由area_num指定的引用区域。

INDEX函数的结果是一个引用，在用于其他的公式时，其解释也是如此。根据使用的公式，INDEX函数的返回值可以用做引用或值。

函数简单示例

本下图所示的工作表中，B2:D4单元格区或中存放数据。B6、B7单元格应用INDEX函数查找数值所在的位皆。

公式说明（结果）

=INDEX(B2:D4,1,2)返B2:D4单元格区域中第1行第2列的值(3)

=INDEX(B2:C4,D2:D4),2,1,2)返回第2个单元格区域中第2行第1列的值(B)

本例公式说明

B16单元格的”最低采购成木”为：MIN(F2:F13)

函数返回F2:F13单元格区域中的最小值，即”取得最低采购成本“值。

E16单元格的“采购批次“公式为：=INDEX(A2:A13,MATCH(B16,F2:F13,0))

公式中第2个公式应用了MATCH函数，其各个参数值指定函数在F2：F13单元格区域中查询B16单元格中的“最低采购成本“，返回查找到的总成本的相对行号。

MATCH函数和INDEX函数经常一起使用，INDEX函数利用MATCH函数查找到的相对行号，返回A2:A13单元格区域中的相应值。

I16单元格的“采购量“公式为：=INDEX(B2:B13,MATCH(B16,F2:F13,0))

公式中第二个公式MATCH(B16,F2:F13,0)在F2:F13单元格区域中查询B16单元格中的”最低采购成本”，返回查找到的总成木的相对行号，然后用此值作为INDEX函数的第2个参数，返回B2:B13单元格区域中的相应值。

4.1.3添加滚动条窗体

Step1添”年采购量”的滚动条窗休

①在“窗休”工具栏中单击“滚动条”按钮。

②当光标变为”十”形状时在A21单元格位置绘制一个滚动条。

Step2设置”年采购量”滚动条窗依的格式

①右键刚绘制好的滚动条弹出“设置对象格式”对话框。

②在”设置对象格式”对话框巾单击“控制"选项卡。

③在“最小值”文本框中输入“1000”，在“最大值“文本框中输入“3000”，在“步长"文本框中输入”200”。

④单击“单元格链接”文本框右侧的按纽，在弹出的区域选择框中用鼠标选中B19单元格。

⑤单击“确定”按钮即可完成第1个滚动条格式的设定。

Step3添加和设置年采购成本的滚动条窗体

①使用同样的方法，在D21单元格位置绘制一个滚动条窗体。

②在“最小值文本框中输入“200”，在“最大值”文本框中输入“600”，在“步长”文木框中输入“100”。

③单击“单元格链接”文本框右侧的按钮，在弹出的区域选择框中用鼠标选中E19单元格。

Step4添加和设置单位储存成本的滚动条窗体

①使用同样的方法，在H21单元格位置绘制一个滚动条窗体。

②在“最小值文本框中输入”4”，在“最大值”文本框中输入“12”，在“步长”文木框中输入“1”。

③单击“单元格链接”文本框右侧的按钮，在弹出的区域选择框中用鼠标选中I19单元格。

4.1.4绘制和编辑折线图

Step1选择图表类型

①选中D1:E13单元格区域。

②插入“折线图”，然后“轴标签（分类）”在弹出的区域选择框中用鼠标选中A2:A13单元格区域。

③调整表格位置和字体、背景等格式。

4.1.5采购成本变动分析

在完成“年采购量”、“采购成本”和“单位储存成本”滚动条的添加，以及“存储成本”和“采购成本”折线图的绘制之后，接下来可以分析“年采购量”、“采购成本”、“单位储存成本”和“最低采购成本”、“采购批次”、“采购量”以及折线图之间的动态变化关系。

Step1采购量的变动影响分析

①单击“年采购量”下方的滚动条，，按住鼠标左键不放向右移动增大“年采购量”，“存储成本”增大而“采购成本”不变，因此折线图上的“存储成本”线发生变动而“采购成本”线保持不变。

②“存储成本”增大因而“总成本”增加，此时B16、E16和I16单元格中的“最低采购成本”、“采购批次”和“采购量”也会相增大。

Step2采购成本的变动影响分析

①单击“采购成本”下方的滚动条，，按住鼠标左键不放向右移动增大“采购成本”，“采购成本”增大而“存储成本”不变，因此折线图上的“采购成本”线发生变动而“存储成本”线保持不变。

②“采购成本”增大因而“总成本”增加，此时B16和I16单元格中的“最低采购成本”和“采购量”也会相增大，而E16“采购批次”则会减少。

Step3单位储存成本的变动影响分析

①单击“单位储存成本”下方的滚动条，，按住鼠标左键不放向右移动增大“单位储存成本”，“采购成本”不变，因此折线图上的“储存成本”线发生变动而“采购成本”线保持不变。

②“储存成本”增大因而“总成本”增加，此时B16和E16单元格中的“最低采购成本”和“采购批次”也会相增大，而I16“采购量”则会减少。

案例背景和关键技术点分析

材料成本大小直接关系到企业的生产成本，在材料成本中除采购价格因素外还有一项非常重要的因素，那就是采购成本。采购成本的构成有两项，一是采购环节发生的费用，二是储存材料的费用。单批采购量较大时，年采购次数会降低，这样虽然可以减少年采购成本，但是储存成本则会随之增加，因为企业要设置更大的储存场地和更多的保管人员。单批采购量减小时，储存场地占用会减少，保管人员可减少，储存成本会降低，但是由于单次采购量减少了，必然使得年采购次数增加，采购成本则随之加大。

因此如何确定采购量和储存量之间的关系是每个企业都应该关注的问题。通过“采购成本分析”可以帮助企业设置利料学合理的采购量和采购次数，从而为企业降低采购环节成本提供可靠的依据。

关键技术点

要实现本案例中的功能，读者应当学握以下的Excel技术点。

函数的应用：MIN函数、MATCH函数和INDEX函数New!

附件：采购成本分析表