基于公眾客戶服務質量考核指標清單級海量數據一鍵式比對功能模塊開發與應用

［溫強］

1 引言

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

本文以集團全業務服務質量相關指標數據比對模塊為例，介紹海量數據一鍵式比對功能模塊開發與應用方法。

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

圖1 日常數據一致性比對項目

根據集團公司要求，每月要求通過手工上報集團電子運維系統全業務服務質量管理模塊相關的公眾客戶服務質量指標數據（簡稱指標數據，下同），模塊開發根據此過程分析功能需求、建立數據模型以及程序開發等。上報指標數據來源于省內集團指標FTP 服務器與服管系統，其結構如圖2 所示。

服管、源端系統及FTP 數據錯誤，將導致集團系統考核指標不達標或異常等問題。此外服管系統集成的相關數據應用于每日/周/月/季度的網運線后端服務質量相關通報統計分析，如服管及源端系統數據錯誤將嚴重影響通報準確性與有效性。

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

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

2 需求分析

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

目前集團系統采集方式采用各省通過集團全業務模塊上報方式進行，工作流程如下。

（1）數據下載及檢查

每月初從廣東省集團報數FTP 服務器下載全業務服務質量指標數據SF 與SG 文件，手工比對核查FTP 服務器與服管系統數據一致性，經檢查核實數據準確一致后，對文件進行分割操作。

（2）數據分割及文件大小檢查

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

（3）數據上傳

核查數據準確后，按順序逐個將分割文件上報集團系統模塊。

（4）上傳后的數據比對

上報完成后，通過集團系統功能模塊進行相關數據比對核查，確保文件總數量、導入成功數量等關鍵數據與上報前文件準確一致。如有差異，需從集團系統下載已上報文件，與上報前文件進行一致性數據比對核查，定位排查原因。

（5）異常問題處理

上報過程如因操作錯誤導致文件重復與錯誤、記錄數量異常等，需通過數據比對逐段核查定位異常原因，對于異常文件需重新下載數據與上報集團系統。

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

圖3 模塊開發應用前后數據比對時間消耗比較

下面以步驟1 比對過程為例，說明程序設計思路、方法與實現技術。

3 程序開發與應用

3.1 程序開發應用的技術與特點

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

①開發速度快。

② VBA 支持調用Windows 底層API。

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

（2）用電子表格和數據庫管理數據，優點在于易用性，使用起來非常簡單。

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

3.2 建模算法與實現方式

公眾客戶有線寬帶業務退單率計算公式如下：

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

上述算法轉換為電子表格計算公式如下：

關于SUMPRODUCT 函數：

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

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

3.3 程序工作流程

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

圖4 程序工作流程圖

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

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

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

表1 數據比對結果表

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

3.4 關鍵技術功能與原理

3.4.1 電子表格數據庫連接功能

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

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

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

ACE 引擎是數據庫連接組件。

HDR 表示第一行是否是標題行。

若為YES，則第一行是標題行（即列名稱），不是數據；

若為NO，則第一行不是標題行，跟后面的行一樣，是數據。

3.4.2 電子表格文本導入功能

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

Sheet2.Select

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

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

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

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

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

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

3.4.4 函數功能

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

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

示例如下：

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

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

3.4.5 比對結果輸出程序

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

圖5 比對結果輸出流程圖

（1）選擇輸出結果與定義數組：

4 結語

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