運用Excel 回歸分析功能實現(xiàn)GB/T13234-2018《用能單位節(jié)能量計算方法》中的歸一化操作

顧淼波

揚州市節(jié)能監(jiān)察中心

2018年9月17日，國家市場監(jiān)督管理總局、中國國家標準化管理委員會聯(lián)合發(fā)布GB/T13234-2018《用能單位節(jié)能量計算方法》（以下簡稱《方法》），定于2019年4月1日正式實施?！斗椒ā份^前一版GB/T13234-2009《企業(yè)節(jié)能量計算方法》有大幅改動，其中最重要的創(chuàng)新是提出了“歸一化”的概念，即根據(jù)相關變量的變化關系，通過建立模型對能耗數(shù)據(jù)進行校準和修正。

1 歸一化的概念

1.1 原理

歸一化是指：為了達到滿足同等需求或達到相同目的的要求（例如：批量生產(chǎn)完全相同的產(chǎn)品），根據(jù)相關變量（例如：產(chǎn)量、效率等）的變化關系，對能源消耗數(shù)據(jù)進行修正的過程。

將能耗量實測值設為Y，相關變量實測值設為X，歸一化就是要確定兩者之間的變化關系Y=f（X），并以此關系為基礎，重新代入相關變量的條件值X′，算出能耗量校準值Y′。當基期能耗量或者報告期能耗量按照相同的條件值X′進行校準后，基期和報告期的能耗量就可以直接相減，從而得到節(jié)能量。

1.2 方法

歸一化的方法有三種，分別是后推校準法、前推校準法和參考條件校準法。

1）按照報告期條件進行歸一化，稱為后推校準法。

該方法以基期的能耗量實測值Y1和相關變量實測值X1為基礎，建立模型Y1=f（X1）。將報告期條件（即報告期相關變量實測值X2）代入上述模型，得到基期能耗量校準值Y1′。節(jié)能量由Y1′減去報告期能耗量實測值Y2得到。

2）按照基期條件進行歸一化，稱為前推校準法。

該方法與后推校準法相反，以報告期的能耗量實測值Y2和相關變量實測值X2為基礎，建立模型Y2=f（X2）。將基期條件（即基期相關變量實測值X1）代入上述模型，得到報告期能耗量校準值Y2′。節(jié)能量由基期能耗量實測值Y1減去Y2′得到。

3）按照參考條件進行歸一化，稱為參考條件校準法。

該方法顧名思義是以某個標準狀態(tài)為條件（例如：典型年產(chǎn)品產(chǎn)量），因此不受基期和報告期條件影響，適用于連續(xù)跟蹤節(jié)能量。

另外《方法》還特別強調(diào)：第一，通常情況下應采用后推校準法計算用能單位節(jié)能量。第二，當在多個報告期連續(xù)計算節(jié)能量時（例如：逐年開展重點用能單位“十三五”節(jié)能目標考核時），不應中途改變歸一化方法。

2 Excel回歸分析示例

2.1 選擇相關變量

根據(jù)常識可知，用能單位能耗大小與其產(chǎn)品產(chǎn)量有直接聯(lián)系。在實際工作中，我們常常選擇產(chǎn)品產(chǎn)量作為相關變量。為方便對比理解，本文借用《方法》附錄A中的案例進行操作演示。具體數(shù)據(jù)見表1和表2。

表1 基期產(chǎn)品產(chǎn)量與能耗數(shù)據(jù)

2.2 建立回歸模型

采用后推校準法。首先打開Excel（這里以2019版為例），導入表1數(shù)據(jù)。

如圖1 所示，框選單元格B2 到C13 的數(shù)據(jù)區(qū)域，點擊【插入】/【圖表】/【XY散點圖】。系統(tǒng)默認B列為X軸系列值，C列為Y軸系列值，自動生成散點圖。

圖1 散點圖

如圖2 所示，右鍵點擊圖中的散點，選擇【添加趨勢線】，在【趨勢線選項】中勾選【線性】、【顯示公式】、【顯示R2值】。由此得到了基期能耗校準模型Y=0.3538X+67.956。

表2 報告期產(chǎn)品產(chǎn)量與能耗數(shù)據(jù)表

圖2

2.3 檢驗模型有效性

模型是否可用，還要經(jīng)過有效性檢驗，通常看三個值：判定系數(shù)（R2值）、t統(tǒng)計量和F統(tǒng)計量。

R2值用于說明回歸趨勢線對數(shù)據(jù)的擬合程度，越接近1.0，擬合度越好，反之，越接近0，擬合度越差。上述案例中R2=0.994 7，充分說明X、Y高度相關。

統(tǒng)計量是檢驗自變量的系數(shù)是否顯著為零，F(xiàn)統(tǒng)計量是檢驗所有自變量系數(shù)是否顯著為零，前者對應具體自變量，后者對應整個回歸方程。

如圖3 所示，框選單元格B2 到C13 的數(shù)據(jù)區(qū)域，點擊【數(shù)據(jù)】/【數(shù)據(jù)分析】/【回歸】，設置【置信度】為95%（t檢驗和F檢驗結(jié)果小于0.05即表示顯著為0）。初次使用，未顯示【數(shù)據(jù)分析】按鈕的，可點選【文件】/【選項】/【加載項】/【分析工具庫】加載該功能。

圖3 回歸分析

如圖4所示，F(xiàn)檢驗是顯著的（Significance F為1.06×10-12），變量X 的t 檢驗也是顯著的（P-value為1.06×10-12）。這兩個結(jié)果完全一致，是因為本模型為一元回歸方程，只有一個自變量X。

圖4 模型有效性檢驗

2.4 校準能耗并計算節(jié)能量

模型檢驗通過，即可計算節(jié)能量。具體過程見表3。

表3 節(jié)能量計算表

2.5 從“特例”看建模計算的優(yōu)勢

《方法》附錄B節(jié)能量計算的特例，提出“只有當用能單位能源消耗與產(chǎn)品產(chǎn)量或產(chǎn)值（或增加值）成正比例關系時，用后推法進行歸一化，節(jié)能量計算公式簡化為”：

節(jié)能量=（基期產(chǎn)品單耗-報告期產(chǎn)品單耗）×報告期產(chǎn)品產(chǎn)量

此簡化版的公式，是目前節(jié)能量審核工作中最常用的單耗法。

正比例關系，指能耗與產(chǎn)量滿足Y＝kX（k 為常數(shù)，k≠0）。而滿足了此關系，即表示產(chǎn)品單耗保持恒定，不隨生產(chǎn)負荷變化。在現(xiàn)實中幾乎不會發(fā)生，因此《方法》將其歸為特例，目的是推廣建模計算，限制和替代傳統(tǒng)的單耗法。

傳統(tǒng)單耗法是基于“產(chǎn)量與能耗成正比例關系”此不夠嚴謹?shù)乃季S，且在計算節(jié)能量時通常只采用基期合計產(chǎn)量、基期合計能耗、報告期合計產(chǎn)量、報告期合計能耗4 個數(shù)據(jù)，忽略了月度數(shù)據(jù)之間的變化規(guī)律。本文示例的建模計算，充分分析了全年12 組數(shù)據(jù)。其最終擬合方程Y=0.3538X+67.956 反映出，用能單位存在部分與產(chǎn)量變化無關的固定能耗，而這一情況也明顯更加符合客觀實際。

3 結(jié)語

《方法》的發(fā)布實施，為節(jié)能量審核認定工作啟發(fā)了新思路、提出了新要求。Excel 作為最常用的辦公軟件之一，其自帶的回歸分析功能可簡便快捷地實現(xiàn)“歸一化”操作。通過本文的介紹，能夠有助于廣大節(jié)能量審核人員，更好地理解《方法》相關理念、熟練地應用Excel相關功能，促進節(jié)能減排事業(yè)繁榮發(fā)展。