用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-），男，漢族，山西太原，本科，廣東機電職業技術學院，副教授，工學碩士，電子商務。