基于公眾客戶服務(wù)質(zhì)量考核指標(biāo)清單級海量數(shù)據(jù)一鍵式比對功能模塊開發(fā)與應(yīng)用

［溫強］

1 引言

廣東公司服務(wù)質(zhì)量管理系統(tǒng)（簡稱服管系統(tǒng)，下同）采用集中化建設(shè)與管控，接入多個IT 支撐系統(tǒng)，其服務(wù)質(zhì)量考核指標(biāo)詳細清單級海量數(shù)據(jù)用于集團與省級系統(tǒng)的公眾客戶服務(wù)質(zhì)量指標(biāo)統(tǒng)計考核、省級系統(tǒng)指標(biāo)統(tǒng)計考核、分析、監(jiān)控、預(yù)警等目標(biāo)系統(tǒng)應(yīng)用。由于服管系統(tǒng)接入源端系統(tǒng)眾多且分散，與源系統(tǒng)數(shù)據(jù)不一致會嚴重影響集團與省分公司指標(biāo)考核與完成情況、通報分析等，為確保數(shù)據(jù)準(zhǔn)確一致性，需對目標(biāo)與來源系統(tǒng)對千萬甚至億級的清單與字段等海量數(shù)據(jù)內(nèi)容進行一致性比對核查，原比對方法采用手工方式進行，不僅操作繁瑣復(fù)雜、工作量巨大、耗時長、出錯率高且重操作工作量更大，為此迫切需要開發(fā)數(shù)據(jù)自動化與智能化的比對功能模塊以代替人工操作，解決以上難題，提高比對工作效率，壓縮工作時間、減少人工成本與壓力，降低人工操作的出錯率，確保考核指標(biāo)數(shù)據(jù)的準(zhǔn)確性、有效性，更好地保障和促進集團與省分公司經(jīng)營指標(biāo)的完成。

本文以集團全業(yè)務(wù)服務(wù)質(zhì)量相關(guān)指標(biāo)數(shù)據(jù)比對模塊為例，介紹海量數(shù)據(jù)一鍵式比對功能模塊開發(fā)與應(yīng)用方法。

在日常數(shù)據(jù)比對工作中涉及部分項目如圖1 所示。

圖1 日常數(shù)據(jù)一致性比對項目

根據(jù)集團公司要求，每月要求通過手工上報集團電子運維系統(tǒng)全業(yè)務(wù)服務(wù)質(zhì)量管理模塊相關(guān)的公眾客戶服務(wù)質(zhì)量指標(biāo)數(shù)據(jù)（簡稱指標(biāo)數(shù)據(jù)，下同），模塊開發(fā)根據(jù)此過程分析功能需求、建立數(shù)據(jù)模型以及程序開發(fā)等。上報指標(biāo)數(shù)據(jù)來源于省內(nèi)集團指標(biāo)FTP 服務(wù)器與服管系統(tǒng)，其結(jié)構(gòu)如圖2 所示。

服管、源端系統(tǒng)及FTP 數(shù)據(jù)錯誤，將導(dǎo)致集團系統(tǒng)考核指標(biāo)不達標(biāo)或異常等問題。此外服管系統(tǒng)集成的相關(guān)數(shù)據(jù)應(yīng)用于每日/周/月/季度的網(wǎng)運線后端服務(wù)質(zhì)量相關(guān)通報統(tǒng)計分析，如服管及源端系統(tǒng)數(shù)據(jù)錯誤將嚴重影響通報準(zhǔn)確性與有效性。

發(fā)生目標(biāo)系統(tǒng)與服管、源端系統(tǒng)數(shù)據(jù)差異因素包括：服管、源端系統(tǒng)版本升級、數(shù)據(jù)結(jié)構(gòu)變更、系統(tǒng)軟件代碼更改、數(shù)據(jù)延遲提供等因素，為確保目標(biāo)與源端系統(tǒng)數(shù)據(jù)一致性，在圖2 所示各個環(huán)節(jié)均需要手工進行數(shù)據(jù)一致性比對核查，在出現(xiàn)差異問題時，需要由下至上逐層比對數(shù)據(jù)，快速定位排查手工比對無法發(fā)現(xiàn)的故障問題并加以及時解決。

本功能模塊結(jié)合利用電子表格的VBA、函數(shù)、外部數(shù)據(jù)庫等技術(shù)與功能，開發(fā)實現(xiàn)公眾客戶服務(wù)質(zhì)量指標(biāo)數(shù)據(jù)一鍵式自動比對功能，解決手工海量數(shù)據(jù)比對操作繁復(fù)、耗時長、易出錯等問題，并及時快速發(fā)現(xiàn)系統(tǒng)數(shù)據(jù)缺失、錯漏等異常故障問題與原因，該功能模塊的應(yīng)用極大縮短操作時間，提升工作效率與質(zhì)量，滿足高頻率的服務(wù)質(zhì)量統(tǒng)計分析通報數(shù)據(jù)一致性需求，探索建立一套高效精準(zhǔn)的數(shù)據(jù)一致性保障機制。

2 需求分析

集團公司網(wǎng)運部自2009 年起，在集團電子運維門戶系統(tǒng)建立全業(yè)務(wù)客戶服務(wù)質(zhì)量集中管理系統(tǒng)模塊，通過采集各省公眾客戶服務(wù)質(zhì)量相關(guān)指標(biāo)數(shù)據(jù)，并對數(shù)據(jù)進行統(tǒng)計分析。廣東省按照集團數(shù)據(jù)采集規(guī)范每月從廣東電信綜合調(diào)度系統(tǒng)采集數(shù)據(jù)，并由廣東公司服務(wù)質(zhì)量支撐及管理系統(tǒng)采集數(shù)據(jù)后進行統(tǒng)計分析，生成指標(biāo)數(shù)據(jù)并手工上報集團系統(tǒng)模塊，在上報過程需手工進行上報前后數(shù)據(jù)一致性比對核查，以確保上報指標(biāo)數(shù)據(jù)一致性。

目前集團系統(tǒng)采集方式采用各省通過集團全業(yè)務(wù)模塊上報方式進行，工作流程如下。

（1）數(shù)據(jù)下載及檢查

每月初從廣東省集團報數(shù)FTP 服務(wù)器下載全業(yè)務(wù)服務(wù)質(zhì)量指標(biāo)數(shù)據(jù)SF 與SG 文件，手工比對核查FTP 服務(wù)器與服管系統(tǒng)數(shù)據(jù)一致性，經(jīng)檢查核實數(shù)據(jù)準(zhǔn)確一致后，對文件進行分割操作。

（2）數(shù)據(jù)分割及文件大小檢查

分割完成后通過數(shù)據(jù)比對工具檢查文件數(shù)量、文件大小、記錄數(shù)量等與FTP 服務(wù)器文件一致。

（3）數(shù)據(jù)上傳

核查數(shù)據(jù)準(zhǔn)確后，按順序逐個將分割文件上報集團系統(tǒng)模塊。

（4）上傳后的數(shù)據(jù)比對

上報完成后，通過集團系統(tǒng)功能模塊進行相關(guān)數(shù)據(jù)比對核查，確保文件總數(shù)量、導(dǎo)入成功數(shù)量等關(guān)鍵數(shù)據(jù)與上報前文件準(zhǔn)確一致。如有差異，需從集團系統(tǒng)下載已上報文件，與上報前文件進行一致性數(shù)據(jù)比對核查，定位排查原因。

（5）異常問題處理

上報過程如因操作錯誤導(dǎo)致文件重復(fù)與錯誤、記錄數(shù)量異常等，需通過數(shù)據(jù)比對逐段核查定位異常原因，對于異常文件需重新下載數(shù)據(jù)與上報集團系統(tǒng)。

以上步驟均需要進行操作前后數(shù)據(jù)一致性與準(zhǔn)確性比對核查。以集團公眾客戶服務(wù)質(zhì)量考核指標(biāo)有線寬帶業(yè)務(wù)退單率為例，每月我省總業(yè)務(wù)開通工單SF 文件清單級單量約163 萬單，總有線帶寬業(yè)務(wù)申告工單量約45 萬單，有線寬帶業(yè)務(wù)退單量約4 萬，有線寬帶業(yè)務(wù)退單率為9%。SF 文件數(shù)量為21 個（分公司），總文件大小為567 MB，由于單次上報文件不能大于20 M（記錄數(shù)須低于5 萬條），因此需分割為小于20 M 的文件，分割后SF文件數(shù)量為40 個。按照以上5 個步驟的檢查，如果進行5 次以上數(shù)據(jù)操作前后一致性數(shù)據(jù)比對核查，核查單量接近千萬級單量，且由于單個文件分拆為20 M 以下的文件上傳，需檢查200 個文件以上，采用手工比對方式進行海量數(shù)據(jù)比對工作存在工作量龐大、操作繁瑣、耗時長、效率低下、易出錯等各種問題，且出錯后需重復(fù)以上1-5 步驟，導(dǎo)致出現(xiàn)諸如工作量像滾雪球一樣越來越大的困難與問題。針對這一難題利用電子表格相關(guān)技術(shù)開發(fā)了一鍵式數(shù)據(jù)比對工具程序，實現(xiàn)數(shù)據(jù)比對操作自動化，將兩天以上的手工操作時間縮短在幾分鐘甚至幾秒之內(nèi)，如圖3 所示。同時確保海量數(shù)據(jù)準(zhǔn)確性與一致性，提升工作效率與質(zhì)量。

圖3 模塊開發(fā)應(yīng)用前后數(shù)據(jù)比對時間消耗比較

下面以步驟1 比對過程為例，說明程序設(shè)計思路、方法與實現(xiàn)技術(shù)。

3 程序開發(fā)與應(yīng)用

3.1 程序開發(fā)應(yīng)用的技術(shù)與特點

（1）宏程序（簡稱VBA，下同）：

①開發(fā)速度快。

② VBA 支持調(diào)用Windows 底層API。

③VBA 是以電子表格為工作平臺的編程語言（宏程序）。

（2）用電子表格和數(shù)據(jù)庫管理數(shù)據(jù)，優(yōu)點在于易用性，使用起來非常簡單。

（3）函數(shù)作為電子表格處理數(shù)據(jù)的一個最重要手段，功能十分強大，在生活和工作實踐中可以有多種應(yīng)用，甚至可用電子表格來設(shè)計復(fù)雜的統(tǒng)計管理表格或者小型的數(shù)據(jù)庫系統(tǒng)。

3.2 建模算法與實現(xiàn)方式

公眾客戶有線寬帶業(yè)務(wù)退單率計算公式如下：

其中T為總有線寬帶業(yè)務(wù)退單量，Z為總有線寬帶業(yè)務(wù)派單量，T1 為退單率。k=1～X，X為文件數(shù)量，本例中為21（全省21 個分公司，每個分公司對應(yīng)1 個SF 文件，SF 文件分割后為40）。C為單個文件退單記錄數(shù)，其字段屬性滿足以下條件：“客戶類別”=“a”（公眾客戶）、“產(chǎn)品1”=1（帶寬型產(chǎn)品），且“最后一次退單時間”、“退單原因”、“退單時通知用戶時間”三個字段不為空；D 為單個文件有線寬帶業(yè)務(wù)派單量，其字段屬性滿足：“客戶類別”=“a”、“產(chǎn)品1”=1。

上述算法轉(zhuǎn)換為電子表格計算公式如下：

關(guān)于SUMPRODUCT 函數(shù)：

SUMPRODUCT 函數(shù)用于計算給定的幾組數(shù)組中對應(yīng)元素的乘積之和，先對各組數(shù)字中對應(yīng)的數(shù)字進行乘法運算，然后再對乘積進行求和。SUMPRODUCT 函數(shù)格式：SUMPRODUCT（array1，[array2]，[array3]，…）

參數(shù)說明：array1（必選）：表示要參與計算的第 1 個數(shù)組。如果只有一個參數(shù)，那么SUMPRODUCT 函數(shù)直接返回該參數(shù)中的各元素之和。array2，array3，…（可選）：表示要參與計算的第 2～255 個數(shù)組。

3.3 程序工作流程

采用VBA 技術(shù)實現(xiàn)一鍵式數(shù)據(jù)導(dǎo)入功能，通過數(shù)據(jù)庫連接技術(shù)將服管統(tǒng)計結(jié)果以及全量詳單文件關(guān)鍵屬性導(dǎo)入電子表格中，然后通過3.2 的公式函數(shù)自動計算有線寬帶業(yè)務(wù)總申告量、退單量以及退單率，完成后與服管系統(tǒng)退單率報表數(shù)據(jù)進行一致性比對檢查，流程如圖4 所示，程序工作步驟如下。

圖4 程序工作流程圖

（1）按下組合鍵“CTRL+Y”，調(diào)用相應(yīng)宏程序，在打開窗口選取文件名“附件4：達標(biāo)情況指標(biāo)評估-綜調(diào).xls”（服管系統(tǒng)報表）。

（2）按下組合鍵“CTRL+J”，調(diào)用相應(yīng)宏程序，選取FTP 服務(wù)器下載的分公司SF 文本文件（注意路徑與第2 步驟選取路徑保持一致，否則數(shù)據(jù)不一致）。

（3）VBA 按照3.2 的算法計算單個分公司總帶寬型業(yè)務(wù)單量、退單量及退單率，并采用VLOOKUP 函數(shù)查找步驟1 的服管統(tǒng)計結(jié)果表相應(yīng)分公司對應(yīng)數(shù)據(jù)，通過IF函數(shù)比對兩者數(shù)據(jù)一致性，并將VBA 自動將比對結(jié)果填入比對結(jié)果表。如比對結(jié)果一致，則如表1，如果不一致，比對結(jié)果表中的“一致”更改為“不一致”。

表1 數(shù)據(jù)比對結(jié)果表

（4）完成后繼續(xù)下一個分公司數(shù)據(jù)比對。單個分公司比對時間在1 分鐘以內(nèi)，中小分公司幾秒就可以完成。進行全省一次比對工作可在幾分鐘甚至幾十秒之內(nèi)就可以完成。

3.4 關(guān)鍵技術(shù)功能與原理

3.4.1 電子表格數(shù)據(jù)庫連接功能

通過電子表格數(shù)據(jù)連接功能，建立與包括SQL SERVER、ACCESS、電子表格在內(nèi)的各種類型數(shù)據(jù)庫內(nèi)部連接，實現(xiàn)批量數(shù)據(jù)記錄的讀取與更新等功能，結(jié)合函數(shù)與宏程序功能，簡單靈活實現(xiàn)大型數(shù)據(jù)庫所具備的復(fù)雜存儲過程與功能。

電子表格的OLEDB 連接串的格式示例：

OLEDB；Provider=Microsoft.ACE.OLEDB.12.0；Password=""；UserID=Admin；Data Source=“文件路徑及文件名”；Mode=Share Deny Write；Extended Properties="HDR=YES；"

ACE 引擎是數(shù)據(jù)庫連接組件。

HDR 表示第一行是否是標(biāo)題行。

若為YES，則第一行是標(biāo)題行（即列名稱），不是數(shù)據(jù)；

若為NO，則第一行不是標(biāo)題行，跟后面的行一樣，是數(shù)據(jù)。

3.4.2 電子表格文本導(dǎo)入功能

使用電子表格文本導(dǎo)入功能，導(dǎo)入TXT、CSV 等類型的文本文件，選擇與設(shè)置好所需字段，導(dǎo)入子表“數(shù)據(jù)源”，添加文本連接。在VBA 中執(zhí)行以下語句，可以選擇導(dǎo)入所需比對的SF 文本文件：

Sheet2.Select

ActiveWorkbook.Connections（"文本連接名稱"）.Refresh

3.4.3 電子表格定義名稱功能運用

不同分公司運行程序進行數(shù)據(jù)一致性比對后，結(jié)果需輸出在電子表格不同單元格，利用電子表格名稱功能，分別將表中21 個單元格定義為21 個名稱，名稱格式為“分公司名稱”+“數(shù)據(jù)比對結(jié)果”，這樣就可以在VBA 程序運行時可以采用循環(huán)過程，將對應(yīng)分公司比對結(jié)果回填在相應(yīng)的單元格，在完成21 個分公司數(shù)據(jù)比對檢查后輸出如3.3 所示的結(jié)果表。關(guān)于名稱的使用方法。

（1）把一個區(qū)域定義為名稱，引用這個區(qū)域時，可直接使用名稱。

（2）把一個公式定義為名稱時，重復(fù)使用這個公式時，可直接使用名稱。

（3）使用定義名稱，可打破函數(shù)30 個參數(shù)的限制。（4）宏表函數(shù)需要定義為名稱，才能使用。

3.4.4 函數(shù)功能

（1）通過3.2 計算單個分公司總有線寬帶業(yè)務(wù)派單量Z、有線寬帶業(yè)務(wù)退單T 及退單率T1 后，需與服管系統(tǒng)相應(yīng)分公司的數(shù)據(jù)進行比對檢查。

（2）采用VLOOKUP 函數(shù)查找對應(yīng)分公司Z1、T1、T11，采用IF 函數(shù)與（1）計算結(jié)果相減，如果3 個減數(shù)均為0 則結(jié)果一致，否則不一致，并在固定單元格單元輸出相應(yīng)文本。

示例如下：

Z1=VLOOKUP（分公司名稱，CHOOSE（{1，2}，服管指標(biāo)統(tǒng)計表!$A$8:$A$29，服管指標(biāo)統(tǒng)計表!$H$8:$H$29），2，0），T1、T11的查找方法與此相同。CHOOSE 函數(shù)用于從一組數(shù)據(jù)中選擇特定一個數(shù)據(jù)進行輸出顯示。

比對結(jié)果運用IF 函數(shù)實現(xiàn)：IF（AND（E6-E8=0，G6-G8=0，I6-I8=0），B6&"服管與FTP 數(shù)據(jù)一致"，B6&"服管與FTP 數(shù)據(jù)不一致"），其中單元格E6、E8 分別表示變量Z、Z1，單元格G6、G8 分別表示T、T1，I6、I8 分別表示T1、T11。

3.4.5 比對結(jié)果輸出程序

流程圖如圖5 所示，過程如下：

圖5 比對結(jié)果輸出流程圖

（1）選擇輸出結(jié)果與定義數(shù)組：

4 結(jié)語

電子表格在數(shù)據(jù)管理、統(tǒng)計分析等方面具有功能強大，技術(shù)先進，應(yīng)用方便等特點，結(jié)合外部數(shù)據(jù)庫可以方便快捷地完成海量數(shù)據(jù)的高效整理、計算、匯總、查詢、分析等處理。加上VBA、函數(shù)綜合運用則如虎添翼,能夠發(fā)揮其更大的威力，可設(shè)計復(fù)雜條件下的數(shù)據(jù)統(tǒng)計分析應(yīng)用管理程序和數(shù)據(jù)庫管理系統(tǒng)等，高效實現(xiàn)繁瑣復(fù)雜以及各種個性化的應(yīng)用需求。