如何使用EXCEL函数分解混合成本_数学_自然科学_信息

如何使用EXCEL函数分解混合成本为了解决线性回归方法成本分解中的复杂数学计算问题，可以借助EXCEL电子制表系统的相关功能和简单的操作来实现. 电脑. 以下是有关使用EXCEL函数分解混合成本的信息，希望对您有所帮助. 使用EXCEL函数分解混合成本，数量，成本和利润分析是公司研究成本，产销量和利润的依存关系和变化规律的重要方法. 但是，成本效益分析的前提是应用可变成本方法，根据成本的性质，将混合成本（总成本）分解为可变成本和固定成本两部分. 混合成本分解的方法通常包括技术计量方法，会计方法，高低点方法，散点图方法和线性回归方法. 线性回归方法基于多个时期的已知历史数据，在数学中使用最小二乘法最小化确定的直线和每个成本点之间的平方误差之和，并且分解结果最准确和科学；但其计算量大而复杂，尤其是多元回归分解，难以手工精确求解. 为了解决线性回归方法成本分解中的复杂数学计算问题，可以借助EXCEL电子表系统的相关功能通过计算机的简单操作来实现. 1.分解混合成本的相关功能EXCEL电子制表系统中的功能语法分为两部分: 功能名称和参数. 参数用括号括起来，并用逗号分隔. 参数可以是单元格范围，数组，函数，常量（逻辑类型，数字类型等）.

在分解混合成本时，主要使用线性回归函数LINEST，并辅以索引值INDEX和舍入ROUND函数. 1.线性回归函数LINEST.LINEST类底部统计分析函数，通常用于销量和成本预测. 如果用于分解混合成本，则此函数的功能为: 运算结果返回线性回归方程的参数，即，当一组混合成本称为的因变量的序列值时Y和与Xi的N组有关的自变量数量的序列值，该函数返回系数bi（i = 1，2 ... n单位变量成本）和常数a（固定成本或费用）回归方程. 多元回归方程模型为: y = b1x1 + b2X2……+ bnXn + a语法格式: LINEST（y序列值，x序列值，Const常数项不为零，并且不存在统计系数检验统计数据）. 其中: Const和Stats是逻辑变量，并且只有两个输入选项: TRUE和FALSE. 常量为TRUE或省略，正常计算a（固定成本）； Stats指定是否返回测试统计信息的值，如果Stats为TRUE，则LINEST返回相关的测试统计信息；否则，将a设置为0. 否则为FALSE或如果省略，则LINEST函数仅返回系数bi（单位可变成本）和常数a（固定成本）.

LINEST函数的计算结果是一个以数组形式反映的系数的序列表. 第二个包括测试统计信息. 每个系数的表达顺序严格. 请参阅下表并根据需要比较表中的值. 第一行bi是每个因子的单位可变成本，a是固定成本；第二行是每个可变因子的标准误差；第三行是相关系数r2和总成本y的标准误差. 第四行是统计值，Df是自由度，用于确定自变量和因变量之间的关系是否偶然发生，并确定模型的置信度；第五行SSreg和SSresid分别是平方的回归和和残差的平方和. 使用INDEX函数在表格中找到行和列的位置. 相关系数r的值范围在十个1和-1之间. 如果r = 0不相关，即业务量和总成本之间没有直接的依赖关系；如果r接近1，则相关程度越高；如果r = + 1，则表示业务量和成本保持正相关；如果r = -1，则意味着业务量和成本保持负相关. 因此，可以根据计算结果中的相关系数判断该因素是与混合成本有关还是与混合成本有关. 如果相关，则分解结果有效，否则无效. 2.索引值函数INDEX. 语法格式: INDEX（单元格区域或数组常数，行号，列号）；功能: 使用索引从单元格区域或数组中选择值.

此函数可用于在LINEST函数返回系数序列数组表中查找和选择所需数据的行和列位置. 3.舍入功能ROUND. 语法格式: ROUND（数字，小数位数）；功能: 将数字四舍五入到指定的小数位数. 由于LINEST函数的返回值是6个小数位，因此可以使用此函数指定保留的小数位数. 2.应用示例假设公司在前5个期间的电力成本与取暖天数，非生产煤气平方和3个发电度这三个因素有关. 电力成本与三个因素之间的数学关系模型应为: 电力成本y =日供热成本b1×供热天数X1十个燃气单位成本b2×燃气平方数x2十个发电单位成本b3×发电程度xa十固定成本的源数据如区域A2: E6中所示，目标结果数据将存储并显示在区域B7: E9中. 操作步骤如下: 1.选择任意工作表的A2: E7区域，输入已知的电费，加热天数，燃气数量，每个时期的发电程度，并设计格式;选择一个连续的单元格区域B7: E9，其大小很难放置线性回归方程的固定和可变系数a，b1，b2，b3和相关系数r2. 2.在[插入]菜单的[名称]命令下选择[定义]子命令，以设置因变量功率成本区域B2: B6定义为Y，由三个独立变量加热形成的连续区域天，燃气平方和发电度定义为X. 3.在单元格B8中输入用于计算a的公式= ROUND（INDEX（LINEEX（LINEST（YX，TRUE，TRUE），1，4），2）.

其中，与LINEST函数嵌套的INDEX函数的参数1和4是INDEX函数从LINEST函数返回的测试统计信息的系数表中的索引a的行号和列号；与INDEX嵌套的ROUND函数中的参数2表示将2保留为小数. 4.复制单元格B8的公式并将其粘贴到与b1，b2，b3和相关系数r2相对应的单元格C8，D8，E8，B9中，然后仅修改每个公式中LINEST函数从INDEX函数返回的测试统计信息在系数表中建立索引所需系数的行号和列号. 计算单位可变成本b1，b2，b3和相关系数r2的公式分别为= ROUND（INDEX（LINEEX（LINEST（Y，X，TRUE，TRUE），1，3），2）; = ROUND（INDEX（LINEST（Y，X，TRUE，TRUE），1，2），2）; = ROUND（INDEX（LINES（Y，X，TRUE，TRUE），1，1），2）; = ROUND （INDEX（LINEST（Y，X，TRUE，TRUE），3，1），2）. 输入公式后，计算结果将自动存储并显示在B7: E9单位区域中.

相关系数的平方是0.87. 在平方根之后，其值接近0.93. 结果表明，加热天数，燃气平方数和发电量与电力混合成本高度相关. 加热天数，燃气方数，发电度数和蒸汽混合成本之间的关系的数学模型表示为: y = 2282.27X1 + 0.08X2 + 0.31X3 + 190245.1使用以上方法分解了多种混合成本和费用，并与功能计算结果（目标数据）和源数据区域建立了自动链接关系. 当源数据发生更改时，将根据输入公式函数自动重新计算目标数据，以获得新的成本分解结果. 5

本文来自电脑杂谈，转载请注明本文网址：
https://www.pc-fly.com/a/shumachanpin/article-304452-1.html