搜索
156财务管理必会Excel应用100例办公室之·第11章成本分析成本分析中有很多常用的表，本章将会看到其中一些。上一章我们讲了本量利分析，本量利分析要求将企业成本划分为变动和固定成本，在成本项目中很多项目既有固定成分，又有变动成分，所以需要将其分解。本章要点：1.趋势线的使用2.RAND函数、MIN函数、MATCH函数如何用汉字名称代替单元格地址？材料成本汇总表效果图采购成本分析表效果图157成本分析CHAPTER11·11.1 成本分解表如何准确将成本中的固定成分和翻去成分分离是做好本量利分析的关键，一些成本项目的性质比较明确，可以直接划分为固定成本或变动成本，但是有的则比较模糊。比如电费，虽然电费与产量有关，但是产量为零时电费却并不为零，这说明电费中既有变动成分，又有固定成分。成本分解表效果图步骤01新建工作表新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。这个工作表中包括了产量和电费的数据。步骤02生成散点图选中C2:D14区域，使用上一章介绍的方法插入一个散点图，这次我们要选择的是一个没有线的默认散点图。定义名字的方法有两种：一种是选定单元格区域后在“名字框”直接输入名字，另一种是选定想要命名的单元格区域，再选择“插入”\“名字”\“定义”，在“当前工作簿中名字”对话框内键人名字即可。158财务管理必会Excel应用100例办公室之·步骤03对散点图进行调整对于生成的散点图我们进行一些小的调整，以便最终效果更加便于查看。首先我们可以删除图例，因为现在只有一个系列。另外，我们还可以修改一下散点图的标题。步骤04设置轴标题如图所示，从“布局”选项卡中选择“坐标轴标题”下的横坐标及纵坐标项目可以添加轴标题，并设置相应的格式等。添加后可以直接对轴标题进行编辑。如何在公式中快速输入不连续的单元格地址？159成本分析CHAPTER11·步骤05给散点添加趋势线右键单击散点图中的任意一个散点，从弹出的快捷菜单中选择“添加趋势线”。步骤06趋势线的设置在弹出的对话框中，选择“线性”，并勾选“显示公式”。步骤07分解成本确定后生成最终的散点图。可以看到，在散点图上出现了线性趋势线和一个二元一次方程。方程中的截距代表固定成本，即505.4；斜率代表单位变动成本，即1.9942。 知识点：趋势线Excel的图表中，散点图、折线图、面积图、条形图、柱形图、股份图和气泡图中都可以添加趋势线，通常适合使用趋势线的的图表有两类，一是成对的数字数据，即典型的散点图中使用的数据；二是基于时间的数据，比如折线图、散点图、面积图等使用的数据。可按住Ctrl键，进行不连续区域的选取。区域选定后选择“插入”→“名字”→“定义”，将此区域命名，如Group1，然后在公式中使用这个区域名，如“＝SUM（Group1）”。160财务管理必会Excel应用100例办公室之·另外，除了线性趋势线，Excel还提供了非线性趋势线：*对数：当数据增加或减少的变化速率非常大，然后很快变得平缓时使用。*多项式：数据规则波动时使用，可以根据数据的波动规律制定多项式的阶数。*乘幂：数据按照固定的速率增加时使用，此时数据不能为零值或负数。*指数：数据以递增或者递减的趋势变化时使用，数据同样不能为零或负数。*移动平均：不是真正的趋势线，它是原数据按照指定的项数不高平均值。使用移动平均时要设定移动平均的项数。在勾选“显示公式”后，图形上会显示线性或非线性的趋势线对应的公式。在勾选“显示R平方值”后，图形上会显示模型的拟合系数。一般情况下，拟合系数越大，趋势线和原数据的拟合程度越好；反之则越差。11.2 材料成本汇总表在材料消耗环节既要统计同一产品不同材料的消耗量，又要计算不同产品对同一材料的消耗量。如何利用材料消耗明细账快速准确地制作出两个统计口径下的材料消耗汇总表是很重要的工作。材料成本汇总表效果图如何定义局部名字？161成本分析CHAPTER11·步骤01新建工作表新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的字段标题、数据，并设置格式等。步骤02生成材料金额在D3输入公式：=INT(RAND()*1000)，并复制公式到D4:D32区域。知识点：RAND函数RAND函数返回大于等于0及小于1的均匀分布随机实数，每次计算工作表时都将返回一个新的随机实数。函数语法RAND()函数说明如果要使用函数RAND生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按F9，将公式永久性地改为随机数。在默认情况下，工作薄中的所有名字都是全局的。其实，可以定义局部名字，使之只对某个工作表有效，方法是将名字命名为“工作表名！名字”的形式即可。162财务管理必会Excel应用100例办公室之·在本例中，使用公式：=INT(RAND()*1000)，即是生成一个大于0及小于1000的整数。如果要生成一个规定范围内的随机数，可以使用公式：=RAND()*(上限-下限)+下限步骤03开始生成数据透视表选择B2:D32中的任意单元格，然后在“插入”选项卡中选择“数据透视表”里的“数据透视表”，开始生成数据透视表。步骤04使用向导进行设置这时会弹出一个对话框，本例中不必改动任何选项。在本例中数据来源就是我们的工作表，而数据透视表生成于新的工作表中。 步骤05设置布局在这一步我们要在新生成的工作表中设置数据透视表的布局形式，比较简单的方式就是直接在窗口中拖动字段名称到相应的区域。步骤06生成数据透视表通过上面的一系列设置，我们得到一张数据透视表，表中显示了材料和产品的汇总数据。如何命名常数？163成本分析CHAPTER11·步骤07增加数据透视图有了数据透视表，但我们希望更加直观地查看数据，所以我们可以增加一张数据透视表，方法跟之前生成柱形图的方法是一样的。步骤08生成数据透视图通过上面一个简单的步骤，我们就得到了一张数据透视图，不过我们还需要对图形做一些细节的调整。在某个工作表中经常需用利率4.9％来计算利息，可以在“当前工作薄的名字”框内输入“利率”，在“引用位置”框中输入“＝0.049”，按“确定”按钮。164财务管理必会Excel应用100例办公室之·步骤09设置数据标签现在我们要对柱形图的数据标签进行设置，以方便查看。方法前面已经讲过，在“布局”选项卡中选择“数据标签”下的“其他数据标签选项”。步骤10显示系列名称在弹出的对话框中，我们选择显示系列名称，这样柱形图上就会直观地显示材料的类型。步骤11完成设置做完以上的步骤，我们就得到了最终的结果。实际上，本节的例子除了使用数据透视表，我们也可以使用公式来得到相同的结果，这里我们就不再赘述了，有兴趣的朋友可以自己研究一下。如何给工作簿扩容？165成本分析CHAPTER11·知识点：数据透视表的数据源使用数据透视表可以汇总、分析、浏览和提供摘要数据。使用数据透视图可以在数据透视表中可视化此摘要数据，并且可以方便地查看比较、模式和趋势。数据透视表和数据透视图都能使您做出有关企业中关键数据的决策。在创建数据透视表时，可使用多种源数据类型。使用工作表数据可以将Excel工作表中的数据作为报表的数据来源。数据应为列表格式，第一行包含列标签，其余行包含相同列中的类似项，并且数据区域中没有空白的行或列。Excel将列标签作为报表的字段名称。使用外部数据源要汇总和分析Excel的外部数据，则可从包括数据库、OLAP多维数据集和文本文件的外部数据源上检索数据。使用其他数据透视表每次在新建数据透视表或数据透视图时，Excel均将报表数据的副本存储在内存中，并将其保存为工作簿文件的一部分。这样每张新的报表均需要额外的内存和磁盘空间。但是，如果将现有数据透视表作为同一个工作簿中的新报表的源数据，则两张报表就可以共享同一个数据副本。因为可以重新使用存储区，所以就会缩小工作簿文件，减少内存中的数据。如果要将某个数据透视表用作其他报表的源数据，则两个报表必须位于同一工作簿中。如果源数据透视表位于另一工作簿中，则需要将源报表复制到要新建报表的工作簿位置。不同工作簿中的数据透视表和数据透视图是独立的，它们在内存和工作簿文件中都有各自的数据副本。在刷新新报表中的数据时，Excel也会更新源报表中的数据，反之亦然。如果对某个报表中的项进行分组或取消分组，那么也将同时影响两个报表。如果在某个报表中创建了计算字段或计算项，则也将同时影响两个报表。更改现有报表的源数据更改源数据将导致用于分析的数据也发生变化。例如，您可能希望方便地从测试数据库切换到生产数据库。可以通过刷新报表，使用与原始数据连接信息类似的新数据来更新数据透视表。选取“工具”→“选项”命令，选择“常规”项，在“新工作薄内的工作表数”对话栏用上下箭头改变打开新工作表数。一个工作薄最多可以有255张工作表，系统默认值为6。166财务管理必会Excel应用100例办公室之·11.3 采购成本分析表材料的成本是生产成本的重要组成部分，而材料的成本除了价格因素外，还有一项很重要的因素，就是采购成本。采购成本通常由两项组成，一是采购环节发生的费用；二是材料存储时的发生的费用。这是两个互相制约的因素，每批采购量大，采购次数少，可以减少年采购成本，但是存储费用的增加；反之亦然。因此确定采购量和存储量之间的关系也是一项很重要的工作，通过“采购成本分析”可以帮助企业设置科学合理的采购量和采购次数，从而为降低企业采购成本提供可靠依据。采购成本分析表效果图我们先创建采购成本和存储成本在不同批次下的数据表，再利用公式计算最小成本、采购批次和采购量。然后添加年采购量、年采购成本和单位存储成本滚动条，最后制作存储成本和采购成本的散点图。首先，我们还是要来看一下本节要使用的公式：采购当量＝年采购量/年采购批次平均存量＝采购数量/2存储成本＝平均存量×单位存储成本采购成本＝年采购批次×采购成本总成本＝存储成本＋采购成本步骤01新建工作表新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的字段标题、数据，并设置格式等。步骤02输入公式在B2到F2依次输入下列公式：=$B$19/A2如何减少重复劳动？167成本分析CHAPTER11·=B2/2=C2*$I$19=A2*$E$19=D2+E2 步骤03复制公式选中B2:F2，移动光标到F2右下角，双击即可复制上述公式至B3:F13区域。步骤04最低采购成本公式在B16单元格输入公式：=MIN(F2:F13)。 步骤05采购批次公式在E16单元格输入公式：=INDEX(A2:A13,MATCH(B16,F2:F13,0))。为了减少重复劳动，我们可以把一些常用到的操作定义成宏。其方法是：选取“工具”菜单中的“宏”命令，执行“记录新宏”，记录好后按“停止”按钮即可。也可以用VBA编程定义宏。168财务管理必会Excel应用100例办公室之·步骤06采购量公式在I16单元格输入公式：=INDEX(B2:B13,MATCH(B16,F2:F13,0))。关于INDEX函数的使用我们在前面的相关章节已经介绍过。知识点：MIN函数返回一组值中的最小值。函数语法MIN(number1,number2,...)Number1,number2,...是要从中查找最小值的1到255个数字。函数说明参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数中不含数字，则函数MIN返回0。如果参数为错误值或为不能转换为数字的文本，将会导致错误。怎样直接打开文本文件获得所需数据？169成本分析CHAPTER11·MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。函数语法MATCH(lookup_value,looku