EXCEL分解混合成本(1)

1、EXCEL剖析混淆本錢(1)量本利闡發(fā)是企業(yè)研究本錢、產(chǎn)銷量與利潤之間依存干系和變革紀(jì)律的緊張本領(lǐng)。但量本利闡發(fā)的條件是應(yīng)用變更本錢法，按成天性態(tài)將混淆本錢全部本錢剖析為變更本錢和結(jié)實本錢兩部門?；煜商炻毥獾囊I(lǐng)通常有技能測定法、管帳法、凹凸點法、分布圖法和線性回歸法。而線性回歸法是按照多少期間汗青數(shù)據(jù)，接納數(shù)學(xué)中的最小二乘法，使所確定的直線與各本錢點之間偏向平方和最小，剖析的效果最為正確、科學(xué)；但其運算事情量大且繁復(fù)，尤其是多元回歸剖析，手工難以正確解算。為辦理線性回歸法成天職解中龐大的數(shù)學(xué)盤算題目，可借助于EXEL電子制表體系的相干函數(shù)，通過盤算機(jī)舉行輕便的操縱就可實現(xiàn)。一、剖析混淆本錢

2、的相干函數(shù)EXEL電子制表體系中函數(shù)的語法分為函數(shù)名和參數(shù)兩部門，參數(shù)用圓括號括起來，之間以逗號離隔。參數(shù)可以為單位格地區(qū)、數(shù)組、函數(shù)、常數(shù)邏輯型、數(shù)值型等。剖析混淆本錢時，重要接納線性回歸函數(shù)LINEST，輔以利用索引取值INDEX與四舍五入RUND函數(shù)。1、線性回歸函數(shù)LINEST.LINEST類底統(tǒng)計闡發(fā)函數(shù)，通常用于販賣量和本錢猜測。假設(shè)用于剖析混淆本錢，該函數(shù)的成效為：運算效果返回一線性回歸方程的參數(shù)，即當(dāng)一組混淆本錢為Y因變量序列值、N組Xi有關(guān)自變量因素的數(shù)目序列值時，函數(shù)返回回歸方程的系數(shù)bii=1，2n單位變更本錢和常數(shù)a結(jié)實本錢或用度。多元回歸方程模子那么為：y=b1x1b

3、2X2bnXna語法格式：LINESTy序列值，x序列值，nst常數(shù)項不為零否，Stats系數(shù)查驗統(tǒng)計量出現(xiàn)否。此中：nst、Stats均為邏輯變量，只有TRUE和FALSE兩個輸入選項。nst為TRUE或被省略，正常盤算a結(jié)實本錢；不然為FALSE，a設(shè)置為0.Stats指定是否返回查驗統(tǒng)計量的值，假設(shè)Stats為TRUE，LINEST返回有關(guān)查驗統(tǒng)計量；不然為FALSE或被省略，LINEST函數(shù)運算效果只返回系數(shù)bi單位變更本錢和常數(shù)a結(jié)實本錢。LINEST函數(shù)盤算效果是以數(shù)組方法反響的一個系數(shù)序列表，此中包羅查驗統(tǒng)計量，各系數(shù)的表達(dá)序次嚴(yán)酷，拜見下表，可按照必要從表中比較取值。第一行bi

4、為各因素的單位變更本錢，a為結(jié)實本錢；第二舉動各自變量因素的尺度偏向值；第三舉動相干系數(shù)r2與總本錢y的尺度偏向值；第四舉動統(tǒng)計值、Df為自由度，別離用于斷定自變量與因變量間的干系式是否偶爾出現(xiàn)和確定該模子的置信度程度；第五行SSreg與SSresid別離為回歸平方和、殘差平方和。用INDEX函數(shù)可從表中舉行行列位置定位取值。相干系數(shù)r的取值范疇在十1與-1之間。假設(shè)r=0不相干，即業(yè)務(wù)量與總本錢無直接依存干系；假設(shè)r越趨近于1，說明相干程度越大；假設(shè)r=1，表現(xiàn)業(yè)務(wù)量與本錢保持正比例相干；假設(shè)r=-1，表現(xiàn)業(yè)務(wù)量與本錢保持負(fù)比例相干。因此可按照盤算效果中的相干系數(shù)，斷定其因素或多因素與混淆本

5、錢用度是否相干；假設(shè)相干，剖析效果有用，不然無效。2、索引取值函數(shù)INDEX.語法格式：INDEX單位格地區(qū)或數(shù)組常量，行序號，列序號；成效：利用索引從單位格地區(qū)或數(shù)組中拔取值?？捎迷摵瘮?shù)在LINEST函數(shù)返回系數(shù)序列數(shù)組表中按照所需數(shù)據(jù)所處的行列位置定位選齲3、四舍五入函數(shù)RUND.語法格式：RUND數(shù)字，小數(shù)位數(shù)；成效：將數(shù)字四舍五入到指定的小數(shù)位數(shù)。由于LINEST函數(shù)的返回值為6位小數(shù)，用此函數(shù)指定保存的小數(shù)位數(shù)。二、應(yīng)用實例假定某企業(yè)前5期的動力用度與取暖和日數(shù)、非消費用煤氣方數(shù)、發(fā)電度數(shù)三因素具有相干性。動力用度與三因素的數(shù)學(xué)干系模子應(yīng)為：動力用度y=日取暖和用度b1取暖和日數(shù)X1

6、十煤氣單位本錢b2煤氣方數(shù)x2十發(fā)電單位本錢b3發(fā)電度數(shù)xa十結(jié)實本錢a源數(shù)據(jù)資料如圖的A2：E6地區(qū)，目的效果數(shù)據(jù)將被存放和表現(xiàn)于B7：E9地區(qū)。操縱步調(diào)如下：1、選定任一事情表的A2：E7地區(qū)輸入各期動力用度、取暖和日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)，并舉行格式方案；選擇一連續(xù)單位格地區(qū)B7：E9，其巨細(xì)難備安排線性回歸方程的結(jié)實及變更系數(shù)a、b1、b2、b3及相干系數(shù)r2.2、選用插入菜單上名字下令下的界說子下令，將因變量動力用度地點的地區(qū)B2：B6界說為Y，將三個自變量取暖和日數(shù)、煤氣方數(shù)及發(fā)電度數(shù)所形成的連續(xù)地區(qū)界說為X.3、在單位格B8中輸入盤算a的公式=RUNDINDEXLINESTYX，

7、TRUE，TRUE，1，4，2。此中，與LINEST函數(shù)嵌套的INDEX函數(shù)的參數(shù)1和4，別離為INDEX函數(shù)從LINEST函數(shù)返回的查驗統(tǒng)計量的系數(shù)表中索引a的行號與列號；與INDEX嵌套的RUND函數(shù)中的參數(shù)2意為保存2為小數(shù)。4、將B8單位格的公式復(fù)制和粘貼在b1、b2、b3及相干系數(shù)r2對應(yīng)的單位植8、D8、E8、B9中，然后僅修改各公式中INDEX函數(shù)從LINEST函數(shù)返回的查驗統(tǒng)計量的系數(shù)表中索引所需系數(shù)的行號與列號。盤算單位變更本錢b1、b2、b3及相干系數(shù)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。當(dāng)公式輸入完畢，盤算效果那么主動存放并表現(xiàn)于B7：E9單位地區(qū)。相干系數(shù)平方為0.87，經(jīng)開方后其值靠近0.93，效果說明取暖和日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)與動力混淆用度高度相干。取暖和日數(shù)、煤氣方數(shù)、發(fā)電度數(shù)與蒸汽混淆用度干系數(shù)學(xué)模子那