一种基于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()函数来确定满足条件的行号和列号。

第四步：利用索引函数，来提取所需数据或直接进行计算。可以采用的函数有INDEX、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.