云南個(gè)舊大屯錫礦-用excel服務(wù)器實(shí)現(xiàn)生產(chǎn)統(tǒng)計(jì)報(bào)表管理信息系統(tǒng)
[摘 要] 本文介紹應(yīng)用Excel服務(wù)器管理軟件設(shè)計(jì)礦山生產(chǎn)統(tǒng)計(jì)報(bào)表管理信息系統(tǒng)的實(shí)現(xiàn)方法，并通過介紹其中一套關(guān)聯(lián)模板來闡明系統(tǒng)設(shè)計(jì)的基本思路和技巧。
[關(guān)鍵詞]信息化、生產(chǎn)統(tǒng)計(jì)報(bào)表、Excel服務(wù)器
一、引言
隨著松礦信息化建設(shè)步伐不斷加快，局部網(wǎng)覆蓋面已經(jīng)延伸到了各坑口、股室、工區(qū)，但在生產(chǎn)統(tǒng)計(jì)報(bào)表的業(yè)務(wù)流程上，目前仍處于各計(jì)算機(jī)獨(dú)立工作，統(tǒng)計(jì)報(bào)表繁瑣。統(tǒng)計(jì)日、月、季度、年度等報(bào)表需要大量的人力和時(shí)間來處理數(shù)據(jù)，數(shù)據(jù)采集較難，報(bào)表統(tǒng)計(jì)時(shí)間不及時(shí)，復(fù)制粘貼容易出錯(cuò)，查詢工作量很大。Excel服務(wù)器(BC Excel Server 2008)是一個(gè)面向最終用戶的管理軟件、系統(tǒng)設(shè)計(jì)工具與運(yùn)行平臺，她將電子表格軟件 Microsoft Office Excel和大型數(shù)據(jù)庫管理系統(tǒng)MS SQL Server集成為一個(gè)網(wǎng)絡(luò)數(shù)據(jù)業(yè)務(wù)協(xié)同工作環(huán)境。在這個(gè)平臺上，用戶可以充分發(fā)揮Excel的應(yīng)用水平，通過設(shè)計(jì)模板、定義工作流、定義表間公式等簡易直觀的操作，實(shí)現(xiàn)管理意圖，輕松、快速構(gòu)建能夠適應(yīng)變化的管理信息系統(tǒng)。
二、系統(tǒng)設(shè)計(jì)基本思路
2.1安裝與配置
安裝Excel服務(wù)器軟件時(shí)，需要選定網(wǎng)絡(luò)中的一臺計(jì)算機(jī)安裝服務(wù)端程序和數(shù)據(jù)庫，在服務(wù)器上需要安裝Microsoft Office Excel和數(shù)據(jù)庫。目前Excel服務(wù)器支持的數(shù)據(jù)庫產(chǎn)品是MS SQL Server2000、MSDE2000、MS SQL Server 2005、MS SQL Server 2008?？蛻舳酥恍枰惭bMicrosoft Office Excel和客戶端安裝包。服務(wù)端的基礎(chǔ)配置十分簡單，進(jìn)入管理控制臺根據(jù)向?qū)Ъ纯膳渲猛瓿伞？蛻舳瞬恍枰魏闻渲茫灰诘卿浄?wù)器處輸入服務(wù)端IP地址（或計(jì)算機(jī)名）即可。圖一為服務(wù)器控制臺的基本配置，其中包括以下幾個(gè)部分：
權(quán)限管理類工作：設(shè)置部門、角色、用戶。
建立規(guī)范類工作：定義自動產(chǎn)生編號的規(guī)則，建立下拉列表、樹型選擇及列表選擇等。
系統(tǒng)管理類工作：備份/恢復(fù)數(shù)據(jù)庫，建立與其它應(yīng)用系統(tǒng)（如財(cái)務(wù)軟件、ERP系統(tǒng)等）的數(shù)據(jù)庫的集成，查看系統(tǒng)日志，監(jiān)控與管理用戶的連接等。
(圖一)
2.2理解統(tǒng)計(jì)報(bào)表在Excel服務(wù)器中實(shí)現(xiàn)統(tǒng)計(jì)匯總的過程
建立一個(gè)信息系統(tǒng)所要完成的四個(gè)方面：建立部門、角色、用戶等基本信息；建立基礎(chǔ)數(shù)據(jù)；管理業(yè)務(wù)數(shù)據(jù)；完成統(tǒng)計(jì)、匯總、查詢。在本系統(tǒng)中主要建立了涉及生產(chǎn)統(tǒng)計(jì)報(bào)表的部門、用戶，并分別給各用戶賦予相應(yīng)的填報(bào)員、一級匯總、二級匯總、礦領(lǐng)導(dǎo)四種角色。應(yīng)用樹型選擇功能來建立填報(bào)單位的下拉選擇列表。圖二為一份統(tǒng)計(jì)報(bào)表實(shí)現(xiàn)填報(bào)、匯總分析的基本過程。
（圖二）
2.3理解業(yè)務(wù)流程、各模版的數(shù)據(jù)來源以及模版間的關(guān)聯(lián)
松礦的生產(chǎn)統(tǒng)計(jì)業(yè)務(wù)目前主要以探礦、采礦、運(yùn)輸、生產(chǎn)計(jì)劃、勞動出勤五個(gè)部分展開，其主要流程為：工區(qū)或股室（部分原始數(shù)據(jù)）、股室（部分原始數(shù)據(jù)）、坑口（工區(qū)、本坑口分類統(tǒng)計(jì)匯總）、礦部（各坑口、松樹腳分礦、大屯錫礦、全礦分類統(tǒng)計(jì)匯總）逐級上報(bào)分類統(tǒng)計(jì)匯總，并最后形成報(bào)表。圖三為松礦統(tǒng)計(jì)業(yè)務(wù)的各類報(bào)表名稱、數(shù)據(jù)來源、表間關(guān)聯(lián)及報(bào)表周期。
（圖三）
2.4模板設(shè)計(jì)
用Excel服務(wù)器構(gòu)建的生產(chǎn)統(tǒng)計(jì)報(bào)表管理信息系統(tǒng)，是由一個(gè)個(gè)的模板組成。構(gòu)建系統(tǒng)的過程，就是制作“模板”的過程。模板就是一個(gè)空白的表樣。依照模板填充了具體的數(shù)據(jù)之后的表格，稱為“表單”。簡單點(diǎn)講，模版=表樣+填寫規(guī)定，填寫規(guī)定正是Excel服務(wù)器的魅力所在，她包括數(shù)據(jù)項(xiàng)定義、數(shù)據(jù)規(guī)范、工作流、表間公式等。本文主要分析圖三中類型為“探礦”的4張報(bào)表從基礎(chǔ)數(shù)據(jù)表到分類匯總表的數(shù)據(jù)提取過程。下面逐一介紹。
2.4.1坑道進(jìn)尺驗(yàn)收月報(bào)表：定義主表，單位名稱、日期為單一數(shù)據(jù)項(xiàng)。單位名稱填寫規(guī)范選擇自定義的填報(bào)單位形成下拉列表。在模板打印區(qū)域外定義一項(xiàng)“模板主鍵”的單一數(shù)據(jù)項(xiàng)，用YEAR(I2)&MONTH(I2)&D2函數(shù)連接單位和日期并將其設(shè)置為該模板主鍵，然后隱藏該列，這是設(shè)計(jì)該模板的一個(gè)技巧，其目的是每月只能填報(bào)一份。定義明細(xì)表，中段、隊(duì)組等定義為重復(fù)數(shù)據(jù)項(xiàng)。圖四為該模板部分最終效果圖。
（圖四）
2.4.2探礦作業(yè)月報(bào)表：設(shè)計(jì)該模板時(shí)，定義數(shù)據(jù)項(xiàng)與坑道進(jìn)尺驗(yàn)收月報(bào)表類似，但由于該模板的部分?jǐn)?shù)據(jù)需要根據(jù)條件提取坑道進(jìn)尺驗(yàn)收月報(bào)表中的數(shù)據(jù)，所以就用到了表間公式，在該模板中多加一列“序號”數(shù)據(jù)項(xiàng)的技巧，其目的是使提取1表中的數(shù)據(jù)不會錯(cuò)行，公式如下：
提取<坑道進(jìn)尺驗(yàn)收月報(bào)表_明細(xì),坑道進(jìn)尺驗(yàn)收月報(bào)表_主表>中滿足下列條件的數(shù)據(jù):
<數(shù)據(jù)篩選:坑道進(jìn)尺驗(yàn)收月報(bào)表_主表.單位名稱=本報(bào)表.Sheet1:單位名稱
并且年份值(坑道進(jìn)尺驗(yàn)收月報(bào)表_主表.日期) =年份值(本報(bào)表.Sheet1:日期)
并且月份值(坑道進(jìn)尺驗(yàn)收月報(bào)表_主表.日期) =月份值(本報(bào)表.Sheet1:日期)>
按以下方式填入<探礦作業(yè)月報(bào)表_明細(xì)>:
坑道進(jìn)尺驗(yàn)收月報(bào)表_明細(xì).序號 --->(填入值) 序號(升序)
坑道進(jìn)尺驗(yàn)收月報(bào)表_明細(xì).隊(duì)組 --->(填入值) 隊(duì)別
坑道進(jìn)尺驗(yàn)收月報(bào)表_明細(xì).中段 --->(填入值) 中段
坑道進(jìn)尺驗(yàn)收月報(bào)表_明細(xì).工程名稱 --->(填入值) 工作面名稱
坑道進(jìn)尺驗(yàn)收月報(bào)表_明細(xì).規(guī)格 --->(填入值) 實(shí)際驗(yàn)收規(guī)格
坑道進(jìn)尺驗(yàn)收月報(bào)表_明細(xì).合格進(jìn)尺 --->(填入值) 實(shí)際驗(yàn)收原規(guī)格進(jìn)尺
圖五為該模板填報(bào)提取數(shù)據(jù)后的部分最終效果圖。
（圖五）
2.4.3探礦掘進(jìn)進(jìn)尺分類表: 此模板屬于該信息系統(tǒng)中比較復(fù)雜的一張，因?yàn)樵撃０迳婕暗椒诸悈R總和上月累計(jì)兩個(gè)難點(diǎn)。設(shè)計(jì)者在設(shè)計(jì)該模板時(shí)明細(xì)表采用了交叉表設(shè)計(jì)方式，并引用了一行（第4行）和多列（B列和各單位上月累計(jì)列）輔助，然后將其隱藏，目的是根據(jù)多條件匹配查找提取2表數(shù)據(jù)。主表與2表類似，只是多加了“提取上月日期”的一個(gè)數(shù)據(jù)項(xiàng)，目的是根據(jù)此日期提取上月累計(jì)。表間公式采用了三條，分別為提取當(dāng)月數(shù)據(jù)、提取上月日期、提取上月累計(jì)。其中在表間公式中還引用了表間關(guān)聯(lián)：單位對應(yīng)明細(xì)表。目的是根據(jù)選擇上級單位提取該單位下所有子單位符合條件的數(shù)據(jù)。圖六為模版部分效果圖，圖七為單位對應(yīng)明細(xì)表。三條表間公式分別如下：
提取本月數(shù)據(jù)：
提取<單位對應(yīng)_明細(xì),探礦作業(yè)月報(bào)表_明細(xì),探礦作業(yè)月報(bào)表_主表>中滿足下列條件的數(shù)據(jù):
<表間關(guān)聯(lián):單位對應(yīng)_明細(xì).工區(qū)=探礦作業(yè)月報(bào)表_主表.單位名稱>
<數(shù)據(jù)篩選:本報(bào)表.Sheet1:單位名稱=單位對應(yīng)_明細(xì).坑口
并且年份值(本報(bào)表.Sheet1:日期) =年份值(探礦作業(yè)月報(bào)表_主表.日期)
并且月份值(本報(bào)表.Sheet1:日期) =月份值(探礦作業(yè)月報(bào)表_主表.日期)>
按以下方式填入<探礦掘進(jìn)進(jìn)尺分類表_明細(xì)>:
單位對應(yīng)_明細(xì).工區(qū) --->(按行(列)匹配) 坑名
′本月′ --->(按行(列)匹配) 統(tǒng)計(jì)類型
探礦作業(yè)月報(bào)表_明細(xì).工程分類 --->(按行(列)匹配) 工程類型
探礦作業(yè)月報(bào)表_明細(xì).礦種 --->(按行(列)匹配) 礦種類
探礦作業(yè)月報(bào)表_明細(xì).規(guī)格大小 --->(按行(列)匹配) 規(guī)格
合計(jì)(探礦作業(yè)月報(bào)表_明細(xì).實(shí)際驗(yàn)收原規(guī)格進(jìn)尺) --->(填入值) 數(shù)據(jù)
提取上次日期：
提取<探礦掘進(jìn)進(jìn)尺分類表_主表>中滿足下列條件的數(shù)據(jù):
<數(shù)據(jù)篩選:年份值(本報(bào)表.Sheet1:日期) = 年份值(探礦掘進(jìn)進(jìn)尺分類表_主表.日期)>
按以下方式填入<探礦掘進(jìn)進(jìn)尺分類表_主表>:
最晚(探礦掘進(jìn)進(jìn)尺分類表_主表.日期) --->(填入值) 上次日期
提取上次累計(jì)：
提取<探礦掘進(jìn)進(jìn)尺分類表_明細(xì),探礦掘進(jìn)進(jìn)尺分類表_主表>中滿足下列條件的數(shù)據(jù):
<數(shù)據(jù)篩選:年份值(探礦掘進(jìn)進(jìn)尺分類表_主表.日期) =年份值(本報(bào)表.Sheet1:上次日期)
并且月份值(探礦掘進(jìn)進(jìn)尺分類表_主表.日期) =月份值(本報(bào)表.Sheet1:上次日期)
并且探礦掘進(jìn)進(jìn)尺分類表_主表.單位名稱=本報(bào)表.Sheet1:單位名稱
并且探礦掘進(jìn)進(jìn)尺分類表_明細(xì).統(tǒng)計(jì)類型=′累計(jì)′
并且 探礦掘進(jìn)進(jìn)尺分類表_明細(xì).坑名=本報(bào)表.Sheet1:坑名>
按以下方式填入<探礦掘進(jìn)進(jìn)尺分類表_明細(xì)>:
探礦掘進(jìn)進(jìn)尺分類表_明細(xì).坑名 --->(按行(列)匹配) 坑名
′上月累計(jì)′ --->(按行(列)匹配) 統(tǒng)計(jì)類型
探礦掘進(jìn)進(jìn)尺分類表_明細(xì).工程類型 --->(按行(列)匹配) 工程類型
探礦掘進(jìn)進(jìn)尺分類表_明細(xì).礦種類 --->(按行(列)匹配) 礦種類
探礦掘進(jìn)進(jìn)尺分類表_明細(xì).規(guī)格 --->(按行(列)匹配) 規(guī)格
合計(jì)(探礦掘進(jìn)進(jìn)尺分類表_明細(xì).數(shù)據(jù)) --->(填入值) 數(shù)據(jù)
（圖六）
(圖七)
2.4.4探礦作業(yè)整理匯總表：根據(jù)業(yè)務(wù)流程分析，該模板是按不同礦種分類，然后根據(jù)探別和項(xiàng)目來匯總所需各類數(shù)據(jù)。所以在設(shè)計(jì)該模板時(shí)，最重要的就是在表間公式中與單位對應(yīng)明細(xì)表關(guān)聯(lián)，這樣就可以根據(jù)不同單位來分別匯總符合條件的數(shù)據(jù)了。其定義主表和明細(xì)表與2表類似，圖八為模板部分效果圖。因?yàn)橛醒趸V、硫化礦、鉛礦3種礦種，根據(jù)單位明細(xì)表，每種礦種有4種不同匯總方式，所以此模板共12條表間公式，以下給出根據(jù)氧化礦匯總某坑口符合條件的表間公式，其余礦種類似。
提取氧化礦數(shù)據(jù)_按坑口：
提取<單位對應(yīng)_明細(xì),探礦作業(yè)月報(bào)表_明細(xì),探礦作業(yè)月報(bào)表_主表>中滿足下列條件的數(shù)據(jù):
<表間關(guān)聯(lián):探礦作業(yè)月報(bào)表_主表.單位名稱=單位對應(yīng)_明細(xì).工區(qū)>
<數(shù)據(jù)篩選:單位對應(yīng)_明細(xì).坑口 =本報(bào)表.Sheet1:制表單位
并且探礦作業(yè)月報(bào)表_明細(xì).礦種= ′氧′
并且年份值(探礦作業(yè)月報(bào)表_主表.日期) =年份值(本報(bào)表.Sheet1:日期)
并且月份值(探礦作業(yè)月報(bào)表_主表.日期) =月份值(本報(bào)表.Sheet1:日期)>
按以下方式填入<探礦作業(yè)整理匯總表_明細(xì)>:
探礦作業(yè)月報(bào)表_明細(xì).探別 --->(按行(列)匹配) 探別
探礦作業(yè)月報(bào)表_明細(xì).項(xiàng)目 --->(按行(列)匹配) 項(xiàng)目
合計(jì)(探礦作業(yè)月報(bào)表_明細(xì).機(jī)臺數(shù)臺班) --->(填入值) 氧化礦機(jī)頭臺數(shù)
合計(jì)(探礦作業(yè)月報(bào)表_明細(xì).實(shí)際驗(yàn)收原規(guī)格進(jìn)尺) --->(填入值) 氧化礦進(jìn)尺
合計(jì)(探礦作業(yè)月報(bào)表_明細(xì).實(shí)際驗(yàn)收折合體積) --->(填入值) 氧化礦體積
合計(jì)(探礦作業(yè)月報(bào)表_明細(xì).耗用人工工班運(yùn)輸) --->(填入值) 氧化礦耗工總數(shù)工班運(yùn)輸
合計(jì)(探礦作業(yè)月報(bào)表_明細(xì).耗用人工工班修理) --->(填入值) 氧化礦耗工總數(shù)工班修理
到此為止，生產(chǎn)統(tǒng)計(jì)報(bào)表系統(tǒng)中探礦部分的全部模板就設(shè)計(jì)完成了。其他幾個(gè)采礦、運(yùn)輸、出勤類型的模板與探礦的模板設(shè)計(jì)思路基本相同，不同的只是各模板的格式和提取數(shù)據(jù)源不同而已。這樣，一個(gè)生產(chǎn)統(tǒng)計(jì)管理報(bào)表系統(tǒng)的核心就基本設(shè)計(jì)完成了。后期就是比較簡單的報(bào)表美化設(shè)計(jì)工作。經(jīng)設(shè)計(jì)者組織測試，各模板提取數(shù)據(jù)完全準(zhǔn)確無誤。
三、結(jié)束語
該系統(tǒng)的設(shè)計(jì)過程完全由主要業(yè)務(wù)人員自主參與設(shè)計(jì)，系統(tǒng)的設(shè)計(jì)需求隨時(shí)可以根據(jù)業(yè)務(wù)流程的不斷變化而隨時(shí)改變。這樣既提高業(yè)務(wù)人員的業(yè)務(wù)素質(zhì)，又減少系統(tǒng)投入的風(fēng)險(xiǎn)。由于系統(tǒng)設(shè)計(jì)者不是專業(yè)統(tǒng)計(jì)業(yè)務(wù)人員，在流程設(shè)計(jì)中難免有不足之處，請給予批評指正。
[參考資料]
[1] Excel服務(wù)器認(rèn)證工程師培訓(xùn)教程
[2]<< Excel數(shù)據(jù)處理與分析實(shí)戰(zhàn)技巧精粹>>人民郵電出版社，2008-8-1，Excel Home編著
