核算鎂質球團礦結算清單的方法

王軍

[摘 要]利用電子表格對鎂質球團礦結算清單進行核算，是物資稽核系統對大宗原燃料核算的補充，其過程是煩瑣的。本文將通過事例展現核算過程的思路。為脫服務器核準與質量異議產生時發生復檢結果的快速預結算，提供了可行辦法。

[關鍵詞]結算清單；核算；電子表格

doi：10.3969/j.issn.1673-0194.2016.12.008

[中圖分類號][TF-9] [文獻標識碼]A [文章編號]1673-0194（2016）12-00-03

以承鋼為例，通常鋼鐵企業的物資稽核系統的最后結果是以結算清單的方式，一式N份打印出單，經審核后提供給供應商作為開具發票的憑證，并由承鋼開出承兌匯票。結算清單是關鍵，但由于結算過程復雜，又因媒體是紙質介質，給稽核人員審核及供應商核算帶來了很大不便。將結算過程數據化，結算清單以電子表格方式輔助驗算結算過程是本文探討的目的。

1 承鋼物資稽核系統簡介

物資稽核系統是公司大宗原燃料結算平臺，通過與ERP系統集成，抽取訂單、入庫信息；通過與質量系統集成，抽取質檢信息，將采購合同條款轉換為結算預設規則，實現大宗原燃料的自動結算。主要模塊有：合同模板的生成與審批，合同條款的生成與審批，價格的錄入與修改及審批。原燃料結算與清單打印。效果：實現了合金類（錳鐵、硅鐵、鋁線等）、原料類（普通鐵精礦、普通球團礦、釩鈦鐵精礦、石灰、螢石等）、燃料類（冶金焦炭、主焦煤、肥煤、噴吹無煙煤等）系統稽核計算；結算方式有不含稅到廠價，鐵運物料包干價，包干篩分等方式的自動結算，成為結算業務的支撐、追溯、監控平臺。

2 物資稽核結算清單的驗算基礎

2.1 合同條款的反映

承鋼與某礦業公司簽訂的“低品鎂質球團礦買賣合同”中規定了如下內容：A產品名稱、數量及供貨時間；B 質量要求：理化指標要求符合 Q/CB 005-2015；C 質量與價格結算辦法（按實際化驗結果計算）；D運輸方式；E 驗收方式；F結算方式等項內容。

其中質量內容以質量要素要求為基點，按照區間范圍分段扣款。

各要素基本評價范圍及扣款方法如下：

Tfe 59.5≤TFe<60.5 每降1個品位，減20元/噸

58.5≤TFe<59.5 每降1個品位，減40元/噸

57≤TFe<58.5 每降1個品位，減60元/噸

TFe<57 拒付

Al2O3 1.7%

S 0.08%

MgO 2.7%≤MgO<3% 每降低0.1%減2元/噸，

2.5%≤MgO<2.7% 每降低0.1%減3元/噸

2%≤MgO<2.5% 每降低0.1%減6元/噸

MgO<2% 每降低0.1%減12元/噸

價格：到需方廠區指定地點不含稅干基交貨基價521.367元/噸（含運費、各項雜費等）。價格隨行就市，價格和條款以河鋼集團有限公司價格管理委員會下發的價格文件為準。

2.2 價格變化的體現

價格由于遂行就市受集團公司指導，在同一訂單內由于季節，市場行情等諸多因素變化，訂單價格也相應變化。具體的訂單價格如圖1所示。

3 具體的結驗算方法

以下是結驗算的主要步驟。

（1）汲取按接收批次的原燃料的到貨入庫信息包括物料屬性、發貨日期、到貨日期、入庫日期、檢驗批號、檢斤量等）。

（2）按檢驗批號抽取該批檢驗的各化學元素指標。

（3）按照到貨日期，抽取相對應的合同價格信息。

（4）按照檢驗批次的實際質量要素，對比相應的合同條款圖譜計算該要素的實際單位扣款從而決定結算單價。

（5）質量要素按照范圍判定不合格產品，從而決定結算單價。如：TFe當小于57%時按300元/噸結算或視情況拒付。篩分指數大于5%時按300元/噸結算。

（6）按照結算價格計算結算貨款。形成結算清單后，通過系統讀取電子表格模板，輸出電子表格清單。其內容包括工作表（到貨信息、核算信息、合同條款、價格變化、核算基本信息、校驗結果信息）合同的具體條款通過解釋，數字化過程后形成合同條款表格（見表1）。

4 驗算的目的、范圍的設定及主要過程

驗算的目的是將各要素的具體扣款要素值、扣款額度，以及對應的日期范圍、值的區間范圍，展示各結算人員或供應商。將結算過程透明化。

合同單價。通常在電子表格輸出過程中，鎖定了該訂單的價格信息，并輸出到價格變化（Sheet）中，只需鎖定到貨時間落定的日期范圍即可得到其對應的合同價格。

利用Excel中Index與Match函數鎖定到貨批對應的合同價格信息。

Index函數用于返回表或區域中的值或值的引用，而Match函數用于查找一個值所在的位置，兩者結合可以幫助人們進行更靈活的查找。

在結算條款的定義中根據不同的化學元素及其質量區間，有增量方式、常量方式及不合品的計價方式，還有扣水標準等。

4.1 合同價格的抽取

L11=LOOKUP（2，1/（（到貨信息！D11>=價格變化！$E$2：$E$19）*

（ 到貨信息！D11<=價格變化！$F$2：$F$19）），價格變化！$H$2：$H$19）

L12=LOOKUP（2，1/（（到貨信息！D12>=價格變化！$E$2：$E$19）*

（ 到貨信息！D12<=價格變化！$F$2：$F$19）），價格變化！$H$2：$H$19）

解釋：抽取到貨日期在價格變化標簽內的日期范圍內相對應的價格。

4.2 要素增量方式的提取

MgO（O11）

O11=IFERROR（INDEX（合同條款！$N$2：$N$41，MATCH（1，（合同條款！$B$2：$B$41=到貨信息！$O$8）*（合同條款！$S$2：$S$41<=到貨信息！$D11）*（合同條款！$T$2：$T$41>=到貨信息！$D11）*（合同條款！$J$2：$J$41>=到貨信息！O11）*（合同條款！$I$2：$I$41<=到貨信息！O11）*（合同條款！$P$2：$P$41=1），0），1），0）

O12==IFERROR（INDEX（合同條款！$N$2：$N$41，MATCH（1，（合同條款！$B$2：$B$41=到貨信息！$O$8）*（合同條款！$S$2：$S$41<=到貨信息！$D12）*（合同條款！$T$2：$T$41>=到貨信息！$D12）*（合同條款！$J$2：$J$41>=到貨信息！O12）*（合同條款！$I$2：$I$41<=到貨信息！O12）*（合同條款！$P$2：$P$41=1），0），1），0）

解釋：抽取合同條款中相應的扣款金額。

具體的描述是：抽取合同條款中扣款為增量方式的，查找到貨的化學要素在合同條款中定義的位置，并且到貨日期滿足在合同條款中定義的范圍。實際到貨的質量指標在合同條款的劃分區間內的加款或扣款金額。不滿足條件視為不扣款即值為零。

通常正常條件下是不扣款的。

Excel的數據抽取有多種方法，這里采用的是定位與匹配相結合的抽取方式。現將基本原理予以展現。基本的方式為邏輯乘法，其組合與順序不分先后。

如O11中的公式進行解釋。

通過Index與Match結合，定位到具體的行的位置。具體的查找過程如下在絕對位置B2至B41的范圍內查找到貨信息標簽頁內絕對定位O8值為MgO的行號。

A1行信息=（合同條款！$B$2：$B$41=到貨信息！$O$8）= （合同條款！$B$2：$B$41=MgO）={ 6，7，8，9，10，11，12，13，14，15，16}

第一條到貨時間（2015-12-1 3：58：32）匹配的行號

A2行信息=（合同條款！$S$2：$S$41<=到貨信息！$D11）*（合

同條款！$T$2：$T$41>=到貨信息！$D11）=（合同條款！$S$2：

$S$41<=2015-12-1 3：58：32）*（合同條款！$T$2：$T$41>=2015

-12-1 3：58：32）={2，3，12，13，14，15，24，25，26，27，31，36，39，40}*{2，3，4，5，12，13，14，15，17，24，25，26，27，31，32，33，34，35，36，39，40}={2，3，12，13，14，15， 24，25，26，27， 31，36，39，40}

A1行信息* A2行信息={ 12，13，14，15，16}到貨信息第一條質量指標O11值為2.91匹配的行號

A3行信息=（合同條款！$J$2：$J$41>=到貨信息！O11）*（合同

條款！$I$2：$I$41<=到貨信息！O11）=（合同條款！$J$2：$J$41>=2.91）*

（合同條款！$I$2：$I$41<=2.91）={8，12，16，18，27，30}

A1行信息* A2行信息* A3行信息={ 12，13，14，15，16}*{8，12，16，18，27，30}={12}

合同條款中扣款類型為增量的匹配的行號

A4行信息=（合同條款！$P$2：$P$41=1）

={1，2，4，6，7，8，9，10，11，12，13，14，15，……………}

A1行信息* A2行信息* A3行信息* A4行信息={12}*{1，2，4，6，7，8，9，10，11，12，13，14，15，……………}={12}

至此通過Match函數的完全匹配模式利用邏輯乘法將多種條件進行了邏輯與操作定位出扣款指標所在的行號。O11 為合同條款標簽頁內N列12行的值為（0.001）。

4.3 核算信息的計算

以MgO為例 計算公式如下（單元格 O11）

=ABS（（到貨信息！O11-核算信息基準！O11））*核算信息減價！O11*（IFERROR（1/核算信息增分值！O11，0））

P（單元格 P11）的計算公式如下：

=ABS（（到貨信息！P11-核算信息基準！P11））*核算信息減價！P11*（IFERROR（1/核算信息增分值！P11，0））

AL2O3（單元格 M11）的計算公式如下：

=ABS（（到貨信息！M11-核算信息基準！M11））*核算信息減價！M11*（IFERROR（1/核算信息增分值！M11，0））

以此類推，計算出全部質量要素的單位扣款信息，合計計算出結算價格。

結算價格（Y11單元格）

=IF（MAX（核算信息常量值！M11：X11）>0，MAX（核算信息常量值！M11：X11），L11+SUM（M11：X11）+SUM（核算信息常量值！M11：X11））

5 結 語

本文給出了合同價格的抽取方法及合同條款中基礎信息的定位方法，計算各個檢驗化學元素的扣款項，最終計算出結算價格。通過對比結算清單的數據。可以對鎂質球團礦結算清單進行核算。在程序化輸出電子表格時，依照上述步驟，分別以標簽方式，對到貨信息、合同條款、價格變化、核算信息基準、核算信息及校驗信息等輸出，使結算過程透明化，便于稽核人員審核及供應商核算。