一種基于EXCEL工作表的網(wǎng)絡(luò)管理數(shù)據(jù)的提取方法

金培欣

（北京信息科技大學(xué)，北京 10085）

1 引言

數(shù)據(jù)分析是政府和企事業(yè)單位日常辦公中的重要內(nèi)容之一，而獲取進(jìn)行數(shù)據(jù)分析的數(shù)據(jù)即數(shù)據(jù)提取是數(shù)據(jù)分析的基礎(chǔ)。由微軟公司開發(fā)的Microsoft Office 套件中的EXCEL具有強(qiáng)大的數(shù)據(jù)分析和處理功能，這主要是由于 Excel中提供了大量的工作表函數(shù)和可擴(kuò)展 EXCEL與 Office應(yīng)用的 VBA（Visual Basic for Application）。VBA對(duì)于沒有學(xué)習(xí)編程或編程基礎(chǔ)不好的辦公人員來說，存在著較大困難。而相對(duì)而言即使沒有編程的基礎(chǔ)，工作表函數(shù)則比較容易理解和應(yīng)用。除此之外，經(jīng)提取分析的數(shù)據(jù)可以進(jìn)一步生成圖表，或?qū)⑻崛〉臄?shù)據(jù)直接用于生成圖表，能夠形象的展現(xiàn)出數(shù)據(jù)的變化規(guī)律和趨勢(shì)。因此與其它軟件相比，EXCEL在數(shù)據(jù)處理和分析方面具有自己獨(dú)特地優(yōu)勢(shì)和地位。

2 EXCEL提取數(shù)據(jù)分析思路及函數(shù)

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

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

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

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

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

第四步：利用索引函數(shù)，來提取所需數(shù)據(jù)或直接進(jìn)行計(jì)算?？梢圆捎玫暮瘮?shù)有INDEX、OFFSET、VLOOKUP等。

3 應(yīng)用案例分析

在日常網(wǎng)絡(luò)管理工作過程中，雖然一般常見的網(wǎng)絡(luò)管理功能都可以通過專用的網(wǎng)絡(luò)軟件來實(shí)現(xiàn)，但在實(shí)際管理過程中，需要對(duì)某一部分?jǐn)?shù)據(jù)進(jìn)一步分析，此時(shí)的管理軟件可能不能夠滿足需要。例如在網(wǎng)絡(luò)管理中，需要對(duì)某一時(shí)段的流量進(jìn)行監(jiān)控，分析出在此時(shí)間段中的各個(gè)用戶流量和較大流量的用戶。如果網(wǎng)絡(luò)管理軟件不能滿足需要，則需要借助其它軟件來實(shí)現(xiàn)，而 EXCEL則可以作為一種選擇。可以先通過管理軟件把需要分析的時(shí)間段的數(shù)據(jù)導(dǎo)入 EXCEL表中，再進(jìn)一步進(jìn)行分析。

用戶在使用網(wǎng)絡(luò)過程中，可能會(huì)根據(jù)需要多次登陸，也可能會(huì)一直使用網(wǎng)絡(luò)。用戶在某時(shí)間段中的使用網(wǎng)絡(luò)記錄存在較大的隨機(jī)性。因此分析某時(shí)間段的流量情況，根據(jù)EXCEL工作表函數(shù)的應(yīng)用特點(diǎn)，完成上述工作需要分兩步進(jìn)行：第一步要把各個(gè)用戶在該時(shí)間段的流量進(jìn)行匯總；第二步在此基礎(chǔ)上，分析出該期間的前十名的最大流量記錄情況。這樣做可以追溯計(jì)算的詳細(xì)細(xì)節(jié)，容易糾錯(cuò)。

第一步：根據(jù)導(dǎo)入到 EXCEL表中的數(shù)據(jù)格式，對(duì)各用戶在該時(shí)間段內(nèi)的數(shù)據(jù)流量進(jìn)行統(tǒng)計(jì)，數(shù)據(jù)的格式如圖2所示。

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

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

=INDEX(tiquall,SMALL(IF(ROW(tiqu1)-1=MATCH(tiqu1,tiqu1,0),ROW(tiqu1)-1,"0"),ROW(B1)),COLUMN(A1))。按下Ctrl+shift+enter三個(gè)鍵，使公式進(jìn)入數(shù)組運(yùn)行狀態(tài)。

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

D列流量的數(shù)據(jù)提取，需要在上述用戶賬號(hào)提取的基礎(chǔ)上進(jìn)一步分析完成。在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三個(gè)鍵，使公式進(jìn)入數(shù)組運(yùn)行狀態(tài)。

其中SUMIF函數(shù)的第二參數(shù)是：

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

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

=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三個(gè)鍵，使公式進(jìn)入數(shù)組運(yùn)行狀態(tài)。

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

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

在上述運(yùn)算的基礎(chǔ)上，進(jìn)一步分析其中的前十位流量較大的記錄。數(shù)據(jù)的格式如圖2，可以在一張表中，也可以新建一個(gè)表。本文是在同一表中，放在上述提取數(shù)據(jù)區(qū)域的右側(cè)，如下圖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三個(gè)鍵，使公式進(jìn)入數(shù)組運(yùn)行狀態(tài)。使用填充句柄向右和向下填充，即可完成提取任務(wù)。其中，MATCH(LARGE($D$2∶$D$100,ROW(A1)),$D$2∶$D$100,0) 來 實(shí)現(xiàn)所需較大流量所在的行，而列在提取數(shù)據(jù)前后是是對(duì)應(yīng)的為COLUMN(A1)。由條件ROW(A1)＞10來控制只取前十位流量較大的記錄。

4 結(jié)語

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

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

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