用Excel完成網(wǎng)頁數(shù)據(jù)的提取、整理和呈現(xiàn)

摘 要：電商企業(yè)每天都需要從賣家中心獲取數(shù)據(jù)，并進行整理和呈現(xiàn)。如果鼠標每次復(fù)制一個數(shù)據(jù)到Excel的相應(yīng)位置，則工作量較大；如果鼠標拖動出一個數(shù)據(jù)塊，并粘貼到Excel中，則需要從中提取所需數(shù)據(jù)。另外，通過帶顏色的上箭頭、下箭頭來呈現(xiàn)每天的變化，又如何自動生成箭頭，并改變其顏色，這些問題在本文中得到解答。

關(guān)鍵詞：電子商務(wù)；超鏈接拖選；offset（） row（） VBA

因為每天需要從賣家中心獲取數(shù)據(jù)，所以筆者對每個環(huán)節(jié)進行了優(yōu)化處理。下面就按照采集、萃取、呈現(xiàn)的順序，敘述一下操作和優(yōu)化的方法。

1 拖選超鏈接區(qū)域的文字

打開網(wǎng)頁【天貓商家】【我的工作臺】，如圖1所示。此時的區(qū)域都是超鏈接區(qū)域（超鏈接區(qū)域，鼠標樣式變?yōu)椤坝沂帧保?/p>

如果直接用鼠標拖動，不僅無法獲取一個區(qū)塊，反而打開了一張空網(wǎng)頁。正確的操作是：左手按下【Alt鍵】不放，右手拖動鼠標，即可得到一個區(qū)塊（如圖2所示）。

2 從大量數(shù)據(jù)中，抽取所需數(shù)據(jù)

先介紹2個函數(shù)offset（）和row（）

1）offset（單元格Cell，行變化量△row，列變化量△col）

函數(shù)offset（）是通過△row，△col對Cell進行修正，得到目標單元格。例如：在圖3的F1單元格中輸入：

=offset（A1，15，3）

則單元格F1的值為4.82996。

函數(shù)offset（A1， 15， 3），通過函數(shù)計算，得到單元格D16，而D16的值為4.82996。D16是如何得來的呢？

（1）從A1取出列值A(chǔ)，再加列的偏移量3，得到列值D；

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

2）row（）用于獲得當前單元格的行值。例如在G2單元格輸入：

=row（）

則單元格G2中的行值為2。

將圖4的某些數(shù)據(jù)讀出到圖5對應(yīng)單元格

由于數(shù)據(jù)量龐大，對圖4的數(shù)據(jù)采用“轉(zhuǎn)置”粘貼不方便，所以選擇了函數(shù)offset（）和row（）的組合完成數(shù)據(jù)提取任務(wù)。

通過研究數(shù)據(jù)排列，發(fā)現(xiàn)：4.82996、4.80267、4.88242，圖4中排在一行，而在圖5中變?yōu)榕旁谝涣小＜矗喊褕D4的單元格A3、B3、C3、……，讀出，再放到圖5對應(yīng)單元格D16、D17、D18、……，就完成任務(wù)。

把圖4的工作表改名為a。把工作表a的A3單元格中的4.82996讀取出來，再寫到圖5的工作表的D16單元格的公式為：

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

下面解釋一下這個公式：

（1）英文嘆號用來分隔工作表名稱和單元格名稱（由于需要跨工作表獲取值，所以要求數(shù)據(jù)源的工作表名稱不能省略）；

（2）$用于填充柄從D16向下拖動公式時，不允許A$3中的3變化；

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

（4）經(jīng)過offset（）函數(shù)求出的列值為A+0仍然為A，行值為3+row（）-16仍然為3。目標單元格為a！A$3（其值為4.82996）。

當我們拖動圖5中D16單元格的填充柄，到D17單元格時，公式仍然是：

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

唯一變化的是row（），變?yōu)?7，row（）-16=1，從而使A$3的列加1，目標單元格就變?yōu)閍！B3，通過圖4可以看出，B3的值為4.80267。通過圖5可以驗證：D17單元格的值正是4.80267。

3 將數(shù)據(jù)轉(zhuǎn)移到規(guī)定格式的表中，并通過不同顏色的箭頭呈現(xiàn)出來

在圖6的D3：D9，H3：H9算出當天值與月初值的差，再根據(jù)差的正負零，在E3：E9，I3：I9畫出方向箭頭。設(shè)計的公式如下：

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

如何處理箭頭的顏色問題呢？如果用菜單【開始】【樣式】【條件格式】，當把它拷貝到電子郵件的內(nèi)容里面時，顏色會丟失。所以這里制作了一個按鈕，用于調(diào)整顏色。制作按鈕的過程為：

菜單【文件】【選項】【自定義功能區(qū)】，在打開的【Excel選項】卡中，將【開發(fā)工具】打?qū)矗鐖D7所示（在圖片的右下角）。這時，菜單就會出現(xiàn)【開發(fā)工具】選項卡（如圖8所示）。

單擊如圖9所示菜單【開發(fā)工具】【控件】【插入】，單擊倒數(shù)第二行左一的按鈕，然后在工作表拖動，畫一個按鈕。雙擊這個按鈕，Excel系統(tǒng)將自動切換到“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-），男，漢族，山西太原，本科，廣東機電職業(yè)技術(shù)學(xué)院，副教授，工學(xué)碩士，電子商務(wù)。