用Excel完成网页数据的提取、整理和呈现

摘 要：电商企业每天都需要从卖家中心获取数据，并进行整理和呈现。如果鼠标每次复制一个数据到Excel的相应位置，则工作量较大；如果鼠标拖动出一个数据块，并粘贴到Excel中，则需要从中提取所需数据。另外，通过带颜色的上箭头、下箭头来呈现每天的变化，又如何自动生成箭头，并改变其颜色，这些问题在本文中得到解答。

关键词：电子商务；超链接拖选；offset（） row（） VBA

因为每天需要从卖家中心获取数据，所以笔者对每个环节进行了优化处理。下面就按照采集、萃取、呈现的顺序，叙述一下操作和优化的方法。

1 拖选超链接区域的文字

打开网页【天猫商家】【我的工作台】，如图1所示。此时的区域都是超链接区域（超链接区域，鼠标样式变为“右手”）。

如果直接用鼠标拖动，不仅无法获取一个区块，反而打开了一张空网页。正确的操作是：左手按下【Alt键】不放，右手拖动鼠标，即可得到一个区块（如图2所示）。

2 从大量数据中，抽取所需数据

先介绍2个函数offset（）和row（）

1）offset（单元格Cell，行变化量△row，列变化量△col）

函数offset（）是通过△row，△col对Cell进行修正，得到目标单元格。例如：在图3的F1单元格中输入：

=offset（A1，15，3）

则单元格F1的值为4.82996。

函数offset（A1， 15， 3），通过函数计算，得到单元格D16，而D16的值为4.82996。D16是如何得来的呢？

（1）从A1取出列值A，再加列的偏移量3，得到列值D；

（2）从A1中的行值1，再加行的偏移量15，得到行值16。

2）row（）用于获得当前单元格的行值。例如在G2单元格输入：

=row（）

则单元格G2中的行值为2。

将图4的某些数据读出到图5对应单元格

由于数据量庞大，对图4的数据采用“转置”粘贴不方便，所以选择了函数offset（）和row（）的组合完成数据提取任务。

通过研究数据排列，发现：4.82996、4.80267、4.88242，图4中排在一行，而在图5中变为排在一列。即：把图4的单元格A3、B3、C3、……，读出，再放到图5对应单元格D16、D17、D18、……，就完成任务。

把图4的工作表改名为a。把工作表a的A3单元格中的4.82996读取出来，再写到图5的工作表的D16单元格的公式为：

=offset（a！A$3，0，row（）-16）

下面解释一下这个公式：

（1）英文叹号用来分隔工作表名称和单元格名称（由于需要跨工作表获取值，所以要求数据源的工作表名称不能省略）；

（2）$用于填充柄从D16向下拖动公式时，不允许A$3中的3变化；

（3）在D16单元格中，row（）的值为16，所以row（）-16为0；

（4）经过offset（）函数求出的列值为A+0仍然为A，行值为3+row（）-16仍然为3。目标单元格为a！A$3（其值为4.82996）。

当我们拖动图5中D16单元格的填充柄，到D17单元格时，公式仍然是：

=offset（a！A$3，0，row（）-16）

唯一变化的是row（），变为17，row（）-16=1，从而使A$3的列加1，目标单元格就变为a！B3，通过图4可以看出，B3的值为4.80267。通过图5可以验证：D17单元格的值正是4.80267。

3 将数据转移到规定格式的表中，并通过不同颜色的箭头呈现出来

在图6的D3：D9，H3：H9算出当天值与月初值的差，再根据差的正负零，在E3：E9，I3：I9画出方向箭头。设计的公式如下：

=IF（D3<0，”↓”，IF（D3>0，”↑”，”-”））

如何处理箭头的颜色问题呢？如果用菜单【开始】【样式】【条件格式】，当把它拷贝到电子邮件的内容里面时，颜色会丢失。所以这里制作了一个按钮，用于调整颜色。制作按钮的过程为：

菜单【文件】【选项】【自定义功能区】，在打开的【Excel选项】卡中，将【开发工具】打对勾，如图7所示（在图片的右下角）。这时，菜单就会出现【开发工具】选项卡（如图8所示）。

单击如图9所示菜单【开发工具】【控件】【插入】，单击倒数第二行左一的按钮，然后在工作表拖动，画一个按钮。双击这个按钮，Excel系统将自动切换到“Microsoft Visual Basic for Applications”代码界面。

编写VBA代码

Private Sub CommandButton1_Click（）

For c = 5 To 9 Step 4

For r = 3 To 16

If Cells（r， c） = “↓” Then

Cells（r， c）.Select

Selection.Font.Color = vbGreen

ElseIf Cells（r， c） = “↑” Then

Cells（r， c）.Select

Selection.Font.Color = vbRed

Else：

Cells（r， c）.Select

Selection.Font.Color = vbBlack

End If

Next

Next

End Sub

作者簡介

曹党生（1964-），男，汉族，山西太原，本科，广东机电职业技术学院，副教授，工学硕士，电子商务。