新组词：EXCEL分解混合成本

2020年11月12日发(作者：辛程)
文档收集于互联网，已重新整理排版.word版本可编辑,有帮助欢迎下载支持.
EXCEL分解混合成本
量本利分析是企业研究成本、产销量与利润之间依存关系和 变化
规律的重要手段。但量本利分析的前提是应用变动成本法，按成本性
态将混合成本（全部成 本）分解为变动成本和固定成本两部分。混合
成本分解的方法通常有技术测定法、会计法、高低点法、散 布图法和
线性回归法。而线性回归法是根据已知若干期间历史数据，采用数学
中的最小二乘法， 使所确定的直线与各成本点之间误差平方和最小，
分解的结果最为精确、科学；但其运算工作量大且繁复 ，尤其是多元
回归分解，手工难以准确解算。为解决线性回归法成本分解中复杂的
数学计算问题 ，可借助于EXCEL电子制表系统的相关函数，通过计算
机进行简便的操作就可实现。
一、分解混合成本的相关函数
EXCEL电子制表系统中函数的语法分为函数名和参数两部分，参
数用圆括号括起来，之间以逗号隔开。参数可以为单元格区域、数组、
函数、常数（逻辑型、数 值型等）。分解混合成本时，主要采用线性回
归函数LINEST，辅以使用索引取值INDEX与四舍 五入ROUND函数。
1、线性回归函数类底统计分析函数，通常用于销
售量和成本预测 。若用于分解混合成本，该函数的功能为：运算结果
返回一线性回归方程的参数，即当已知一组混合成本 为Y因变量序列
值、N组Xi有关自变量因素的数量序列值时，函数返回回归方程的系
数bi（ i=1，2…n单位变动成本）和常数a（固定成本或费用）。多元
回归方程模型则为：y=b1x1＋ b2X2……＋bnXn＋a语法格式：LINEST
1文档来源为:从网络收集整理.word版本可 编辑.
文档收集于互联网，已重新整理排版.word版本可编辑,有帮助欢迎下载支持.
（y序列值，x序列值，Const常数项不为零否，Stats系数检验统计
量出现否）。
其中：Const、Stats均为逻辑变量，只有TRUE和FALSE两个输
入选项。 Const为TRUE或被省略，正常计算a（固定成本）；否则为
FALSE，a设置为指定是否返回 检验统计量的值，如果Stats
为TRUE，LINEST返回有关检验统计量；否则为FALSE或 被省略，LINEST
函数运算结果只返回系数bi（单位变动成本）和常数a（固定成本）。
LINEST函数计算结果是以数组方式反映的一个系数序列表，其中包括
检验统计量，各系数的表达次 序严格，参见下表，可根据需要从表中
对照取值。
第一行bi为各因素的单位变动成本， a为固定成本；第二行为各
自变量因素的标准误差值；第三行为相关系数r2与总成本y的标准误
差值；第四行为统计值、Df为自由度，分别用于判定自变量与因变量
间的关系式是否偶然出现和确定 该模型的置信度水平；第五行SSreg
与SSresid分别为回归平方和、残差平方和。用INDE X函数可从表中
进行行列位置定位取值。相关系数r的取值范围在十1与-1之间。若
r=0不 相关，即业务量与总成本无直接依存关系；若r越趋近于1，说
明相关程度越大；若r=＋1，表示业务 量与成本保持正比例相关；若
r=-1，表示业务量与成本保持负比例相关。因此可根据计算结果中的< br>相关系数，判断其因素或多因素与混合成本费用是否相关；若相关，
分解结果有效，否则无效。
2、索引取值函数INDEX.语法格式：INDEX（单元格区域或数组常
1文档来源为 :从网络收集整理.word版本可编辑.
文档收集于互联网，已重新整理排版.word版本可编辑,有帮助欢迎下载支持.
量，行序 号，列序号）；功能：使用索引从单元格区域或数组中选取值。
可用该函数在LINEST函数返回系数 序列数组表中根据所需数据所处
的行列位置定位选取。
3、四舍五入函数ROUND.语 法格式：ROUND（数字，小数位数）；
功能：将数字四舍五入到指定的小数位数。由于LINEST 函数的返回值
为6位小数，用此函数指定保留的小数位数。 二、应用实例
假定某企业 前5期的动力费用与取暖日数、非生产用煤气方数、
发电度数三因素具有相关性。动力费用与三因素的数 学关系模型应为：
动力费用y=日取暖费用b1×取暖日数X1十煤气单位成本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的公式=ROUND（INDEX（LINEST（ YX，
TRUE，TRUE），1，4），2）。其中，与LINEST函数嵌套的INDEX函数的< br>参数1和4，分别为INDEX函数从LINEST函数返回的检验统计量的系
1文档来源为:从 网络收集整理.word版本可编辑.
文档收集于互联网，已重新整理排版.word版本可编辑,有帮助欢迎下载支持.
数表中索 引a的行号与列号；与INDEX嵌套的ROUND函数中的参数2
意为保留2为小数。
4、将B8单元格的公式复制和粘贴在b1、b2、b3及相关系数r2
对应的单元植C8、D8、E8 、B9中，然后仅修改各公式中INDEX函数从
LINEST函数返回的检验统计量的系数表中索引所 需系数的行号与列
号。计算单位变动成本b1、b2、b3及相关系数r2的公式分别为=ROUND< br>（INDEX（LINEST（Y，X，TRUE，TRUE），1，3），2）；=ROUND（IND EX
（LINEST（Y，X，TRUE，TRUE），1，2），2）；=ROUND（INDEX（ LINES
（Y，X，TRUE，TRUE），1，1），2）；=ROUND（INDEX（LINE ST（Y，X，
TRUE，TRUE），3，1），2）。当公式输入完毕，计算结果则自动存放并显示于B7：E9单元区域。
相关系数平方为0.87，经开方后其值接近0.93，结果说 明取暖日
数、煤气方数、发电度数与动力混合费用高度相关。取暖日数、煤气
方数、发电度数与 蒸汽混合费用关系数学模型则表达为：
y=2282.27X1＋0.08X2＋0.31X3＋190245.1
用以上方法进行多元 混合成本费用分解，函数运算结果（目标数
据）和源数据区域建立了自动链接关系。当源数据变更时，目 标数据
将根据输入的公式函数自动重新计算得出新的成本费用分解结果。
1文档来源为:从网络收集整理.word版本可编辑.