EXCEL分解混合成本(1)

1、EXCEL剖析混淆本钱(1)量本利阐发是企业研究本钱、产销量与利润之间依存干系和变革纪律的紧张本领。但量本利阐发的条件是应用变更本钱法，按成天性态将混淆本钱全部本钱剖析为变更本钱和结实本钱两部门。混淆成天职解的要领通常有技能测定法、管帐法、凹凸点法、分布图法和线性回归法。而线性回归法是按照多少期间汗青数据，接纳数学中的最小二乘法，使所确定的直线与各本钱点之间偏向平方和最小，剖析的效果最为正确、科学；但其运算事情量大且繁复，尤其是多元回归剖析，手工难以正确解算。为办理线性回归法成天职解中庞大的数学盘算题目，可借助于EXEL电子制表体系的相干函数，通过盘算机举行轻便的操纵就可实现。一、剖析混淆本钱

2、的相干函数EXEL电子制表体系中函数的语法分为函数名和参数两部门，参数用圆括号括起来，之间以逗号离隔。参数可以为单位格地区、数组、函数、常数逻辑型、数值型等。剖析混淆本钱时，重要接纳线性回归函数LINEST，辅以利用索引取值INDEX与四舍五入RUND函数。1、线性回归函数LINEST.LINEST类底统计阐发函数，通常用于贩卖量和本钱猜测。假设用于剖析混淆本钱，该函数的成效为：运算效果返回一线性回归方程的参数，即当一组混淆本钱为Y因变量序列值、N组Xi有关自变量因素的数目序列值时，函数返回回归方程的系数bii=1，2n单位变更本钱和常数a结实本钱或用度。多元回归方程模子那么为：y=b1x1b

3、2X2bnXna语法格式：LINESTy序列值，x序列值，nst常数项不为零否，Stats系数查验统计量出现否。此中：nst、Stats均为逻辑变量，只有TRUE和FALSE两个输入选项。nst为TRUE或被省略，正常盘算a结实本钱；不然为FALSE，a设置为0.Stats指定是否返回查验统计量的值，假设Stats为TRUE，LINEST返回有关查验统计量；不然为FALSE或被省略，LINEST函数运算效果只返回系数bi单位变更本钱和常数a结实本钱。LINEST函数盘算效果是以数组方法反响的一个系数序列表，此中包罗查验统计量，各系数的表达序次严酷，拜见下表，可按照必要从表中比较取值。第一行bi

4、为各因素的单位变更本钱，a为结实本钱；第二举动各自变量因素的尺度偏向值；第三举动相干系数r2与总本钱y的尺度偏向值；第四举动统计值、Df为自由度，别离用于断定自变量与因变量间的干系式是否偶尔出现和确定该模子的置信度程度；第五行SSreg与SSresid别离为回归平方和、残差平方和。用INDEX函数可从表中举行行列位置定位取值。相干系数r的取值范畴在十1与-1之间。假设r=0不相干，即业务量与总本钱无直接依存干系；假设r越趋近于1，说明相干程度越大；假设r=1，表现业务量与本钱保持正比例相干；假设r=-1，表现业务量与本钱保持负比例相干。因此可按照盘算效果中的相干系数，断定其因素或多因素与混淆本

5、钱用度是否相干；假设相干，剖析效果有用，不然无效。2、索引取值函数INDEX.语法格式：INDEX单位格地区或数组常量，行序号，列序号；成效：利用索引从单位格地区或数组中拔取值。可用该函数在LINEST函数返回系数序列数组表中按照所需数据所处的行列位置定位选龋3、四舍五入函数RUND.语法格式：RUND数字，小数位数；成效：将数字四舍五入到指定的小数位数。由于LINEST函数的返回值为6位小数，用此函数指定保存的小数位数。二、应用实例假定某企业前5期的动力用度与取暖和日数、非消费用煤气方数、发电度数三因素具有相干性。动力用度与三因素的数学干系模子应为：动力用度y=日取暖和用度b1取暖和日数X1

6、十煤气单位本钱b2煤气方数x2十发电单位本钱b3发电度数xa十结实本钱a源数据资料如图的A2：E6地区，目的效果数据将被存放和表现于B7：E9地区。操纵步调如下：1、选定任一事情表的A2：E7地区输入各期动力用度、取暖和日数、煤气方数、发电度数，并举行格式方案；选择一连续单位格地区B7：E9，其巨细难备安排线性回归方程的结实及变更系数a、b1、b2、b3及相干系数r2.2、选用插入菜单上名字下令下的界说子下令，将因变量动力用度地点的地区B2：B6界说为Y，将三个自变量取暖和日数、煤气方数及发电度数所形成的连续地区界说为X.3、在单位格B8中输入盘算a的公式=RUNDINDEXLINESTYX，

7、TRUE，TRUE，1，4，2。此中，与LINEST函数嵌套的INDEX函数的参数1和4，别离为INDEX函数从LINEST函数返回的查验统计量的系数表中索引a的行号与列号；与INDEX嵌套的RUND函数中的参数2意为保存2为小数。4、将B8单位格的公式复制和粘贴在b1、b2、b3及相干系数r2对应的单位植8、D8、E8、B9中，然后仅修改各公式中INDEX函数从LINEST函数返回的查验统计量的系数表中索引所需系数的行号与列号。盘算单位变更本钱b1、b2、b3及相干系数r2的公式别离为=RUNDINDEXLINESTY，X，TRUE，TRUE，1，3，2；=RUNDINDEXLINESTY，X，TRUE，TRUE，1，2，2；=RUNDINDEXLINESY，X，TRUE，TRUE，1，1，2；=RUNDINDEXLINESTY，X，TRUE，TRUE，3，1，2。当公式输入完毕，盘算效果那么主动存放并表现于B7：E9单位地区。相干系数平方为0.87，经开方后其值靠近0.93，效果说明取暖和日数、煤气方数、发电度数与动力混淆用度高度相干。取暖和日数、煤气方数、发电度数与蒸汽混淆用度干系数学模子那