一種基于EXCEL工作表的網絡管理數據的提取方法

金培欣

（北京信息科技大學，北京 10085）

1 引言

數據分析是政府和企事業單位日常辦公中的重要內容之一，而獲取進行數據分析的數據即數據提取是數據分析的基礎。由微軟公司開發的Microsoft Office 套件中的EXCEL具有強大的數據分析和處理功能，這主要是由于 Excel中提供了大量的工作表函數和可擴展 EXCEL與 Office應用的 VBA（Visual Basic for Application）。VBA對于沒有學習編程或編程基礎不好的辦公人員來說，存在著較大困難。而相對而言即使沒有編程的基礎，工作表函數則比較容易理解和應用。除此之外，經提取分析的數據可以進一步生成圖表，或將提取的數據直接用于生成圖表，能夠形象的展現出數據的變化規律和趨勢。因此與其它軟件相比，EXCEL在數據處理和分析方面具有自己獨特地優勢和地位。

2 EXCEL提取數據分析思路及函數

EXCEL表是一種二維表格。一般來說，單元格的命名采用樣式A1或采用R1C1。本文中采用的是A1樣式。這種樣式對單元格的引用方式是用數字來表示行，而用字母來表示列。一般用EXCEL表來保存記錄數據采用格式如圖1所示。一般用第一行各列作為字段，說明各列下記錄數據的含義；第二航的信息作為一個完整的記錄。這類似于數據庫中數據表的格式。數據的提取就以此形式為基礎進行分析。

EXCEL軟件比較容易入門，其應用范圍較廣。有時根據工作需要建立了如上描述的工作表記錄數據。在記錄數據的過程中，根據實際情況，記錄的數據時存在較大的隨機性。因此數據可能沒有規律性。當需要對某類數據進行分析時，給數據的提取帶來不便。根據辦公痕跡保留的要求，在數據分析時不能修改原始數據，需要建立新工作表來存放所提取的數據。EXCEL中提供了豐富的工作表函數，同時還可與數組公式同時使用，是數據的處理更加靈活方便。可以根據要提取數據建立如下的分析思路：

第一步：確定所要提取數據的所在數據區域范圍，可直接使用區域來表示，也還可以采用定義區域的方式來表示，這樣可以減少公式的長度。

第二步：根據提取數據的要求，確定所提取數據的特征，建立數據選擇的條件。此時可以利用比較運算符來實現提取數據的條件，也可以進一步采用數組公式進行計算。

第三步：根據建立數據選擇的條件，確定提取數據的行和列的位置。在滿足條件的基礎上，可以采用的 ROW()和COLUMN()函數來確定滿足條件的行號和列號。

第四步：利用索引函數，來提取所需數據或直接進行計算?？梢圆捎玫暮瘮涤蠭NDEX、OFFSET、VLOOKUP等。

3 應用案例分析

在日常網絡管理工作過程中，雖然一般常見的網絡管理功能都可以通過專用的網絡軟件來實現，但在實際管理過程中，需要對某一部分數據進一步分析，此時的管理軟件可能不能夠滿足需要。例如在網絡管理中，需要對某一時段的流量進行監控，分析出在此時間段中的各個用戶流量和較大流量的用戶。如果網絡管理軟件不能滿足需要，則需要借助其它軟件來實現，而 EXCEL則可以作為一種選擇。可以先通過管理軟件把需要分析的時間段的數據導入 EXCEL表中，再進一步進行分析。

用戶在使用網絡過程中，可能會根據需要多次登陸，也可能會一直使用網絡。用戶在某時間段中的使用網絡記錄存在較大的隨機性。因此分析某時間段的流量情況，根據EXCEL工作表函數的應用特點，完成上述工作需要分兩步進行：第一步要把各個用戶在該時間段的流量進行匯總；第二步在此基礎上，分析出該期間的前十名的最大流量記錄情況。這樣做可以追溯計算的詳細細節，容易糾錯。

第一步：根據導入到 EXCEL表中的數據格式，對各用戶在該時間段內的數據流量進行統計，數據的格式如圖2所示。

首先，分析數據記錄里不同用戶的情況。在此過程中需要分析提取不重復用戶數據的特征條件。中國人的名字可能存在重復，而用戶賬號是唯一，因此用戶賬號作為分析數據的特征條件，以此來確定所要提取數據的行和列的位置等，提取數據的公式如下：

定義：tiqu1=$B2∶$B100, tiqu2=$L2∶$L100，tiquall= $ A2∶$C100（定義的區間可以擴展，此處定義僅為解釋方便）。在A2中輸入：

=INDEX(tiquall,SMALL(IF(ROW(tiqu1)-1=MATCH(tiqu1,tiqu1,0),ROW(tiqu1)-1,"0"),ROW(B1)),COLUMN(A1))。按下Ctrl+shift+enter三個鍵，使公式進入數組運行狀態。

其中，ROW(tiqu1)-1=MATCH(tiqu1,tiqu1,0) 為提取數據的條件特征，當滿足時把所在的行記錄下來為ROW(tiqu1)-1。根據所提取數據前后所在列的對應關系，列設為COLUMN(A1)。通過SMALL函數對所提取數據的行數進行由小到大的排序，獲得完整的需要的提取數據的行數，以數組的形式表示。最后利用引用函數 INDEX根據所確定的行和列，進行數據的提取，利用填充句柄向下完成用戶編號的提取。同樣適用填充句柄完成B、C列用戶賬號和姓名的提取。

D列流量的數據提取，需要在上述用戶賬號提取的基礎上進一步分析完成。在D2中輸入：

=SUMIF(tiqu1,INDEX(tiquall,SMALL(IF(ROW(tiqu1)-1=MATCH(tiqu1, tiqu1,0),ROW(tiqu1)-1,"0"),ROW(B1)),

COLUMN($A1)),tiqu2))。按下Ctrl+shift+enter三個鍵，使公式進入數組運行狀態。

其中SUMIF函數的第二參數是：

INDEX($A∶$C,SMALL(IF(ROW(tiqu1)-1=MATCH(tiqu1,tiqu1,0),ROW(tiqu1)-1,"0"),ROW(B1)),COLUMN($A1))。這樣保證計算結果與前面所提取數據的一致性。

為了方便公式的使用與利用填充句柄，可以把上述的兩個公式統一起來，具體公式如下：

=IF(SUM(1/COUNTIF(tiqu1,tiqu1))＞=ROW(A1),IF((A$1＜＞"流量"),INDEX(tiquall,SMALL(IF(ROW(tiqu1)-1=MATC

H(tiqu1,tiqu1,0),ROW(tiqu1)-1,"0"),ROW(B1)),COLU MN(A1)),SUMIF(tiqu1,INDEX(tiquall,SMALL(IF(ROW(tiqu1)-1=MATCH(tiqu1,tiqu1,0),ROW(tiqu1)-1,"0"),ROW(B1)),COLUMN($A$1)),tiqu2)),"end")。按下Ctrl+shift+enter三個鍵，使公式進入數組運行狀態。

其中 SUM(1/COUNTIF(tiqu1,tiqu1))＞=ROW(A1)，可以控制總的提取數據的量。而條件(A$1＜＞"流量")來控制上述兩個公式在不同單元格發揮作用。

第二步：提取流量較大的前十名記錄

在上述運算的基礎上，進一步分析其中的前十位流量較大的記錄。數據的格式如圖2，可以在一張表中，也可以新建一個表。本文是在同一表中，放在上述提取數據區域的右側，如下圖3所示。在I2中輸入：

=IF(ROW(A1)＞10,"",INDEX($A$2∶$D$100,MATCH(LARGE($D$2∶$D$100,ROW(A1)),$D$2∶$D$100,0),COLUMN(A1)))。按下Ctrl+shift+enter三個鍵，使公式進入數組運行狀態。使用填充句柄向右和向下填充，即可完成提取任務。其中，MATCH(LARGE($D$2∶$D$100,ROW(A1)),$D$2∶$D$100,0) 來 實現所需較大流量所在的行，而列在提取數據前后是是對應的為COLUMN(A1)。由條件ROW(A1)＞10來控制只取前十位流量較大的記錄。

4 結語

數據提取是基于 EXCEL豐富的工作表函數與數組公式完成的。上述數據提取分為兩步進行的，主要是基于工作表函數的應用特點和公式長度的問題。如果上述提取數據一步完成，則需要的計算公式較長，這樣在輸入或修改公式過程中不易控制，容易出錯。此外數據提取過程中，所定義的的數據的區域范圍較小，可以根據需要進一步擴展。本文數據提取公式在應用中具有一定的通用性，也可根據需要采用其它的函數進行相應的改變。

[1] 雪之舫工作室.EXCEL應用案例詳解[M].北京:中國鐵道出版社,2004.

[2] Reed Jacobson.中文版Microsoft Office 2000專家手冊[M].北京:人民郵電出版社出版社,2000.