一、簡單演示
本例是從某些表格中收集滿足一定條件的數據進行彙總的應用。
二、關鍵對象/方法的分析
1. Workbooks.Open(FileName)
打開一個工作簿。如果打開失敗,嘗試 FileName 加上完整的路徑。
2. Set ws0 = ActiveSheet
Set wbTarget = Workbooks.Open(fpath & "" & cfile)
Set wsT = wbTarget.Sheets(1)
非常簡單的代碼。當我們在打開多個工作簿時,首先考慮用變量賦予工作表,這是簡化代碼,減少出錯的好習慣。
三、執行思路及過程分析
【1】需求分析
收集到語文、數學、英語 3 科的成績表,需要完成:
(1)彙總到一張表格中去;
(2)對每科成績進行排序,並找出前 5 名;
(3)計算每個人的總分,並進行總分排序,找出前 5 名。
收到的成績表有如下問題:
- 表格的名字不統一
- 表格的樣式不統一
但有如下共性:
- 表格的名字含有本科目的名字
- 記錄成績的子表都是第一個子表
- 表格中都會有 2 列,1 列 學號,1列 成績,並且第 1 行都是表頭
【2】完成數據收集的表格樣式
左邊是按學號順序,收集所有學生的成績;右邊是各個科目和總分進行排序的結果展示。
【3】獲取成績表的名字
由於宏在執行時,會打開多個工作簿,所以,定義當前工作簿,方便後面的成績錄入。
fpath 是存放收集到的各科成績表的目錄。
rmax 是本表 A 行最大的行號,這個決定了需要錄入成績的數量。
由於 3 個科目,錄入或者排序,都有很多共性,所以用一個 For 循環,對於非共性的地方,我們進行變量定義,比如 KeMu,我們分別對 i 等於 1~3 時,對應 3 個不同科目的名字。
cfile,通過 Dir 和通配符 *,獲取到每個科目的成績表名字。獲取到以後,進入 If 語句。
【4】錄入成績
在 WE003 那期,我們採用不打開目標表格的方式,對數據進行提取,但那種情況,僅限於目標表格的格式固定的,並且不需要用一些公式進行數據匹配的時候。
像現在這種情況,打開目標表格進行數據的提取,會更加簡便和有效率。
打開成績表以後,通過 Match 函數,獲取成績表中 學號 和 成績 的列號,然後獲取這張表格的最大行列號。再用 Match 函數,獲取對應科目成績記錄在本表中的列號。有了這些數據,就可以用 Vlookup 函數進行數據的提取。
這個公式,用了很多變量,而且還是 R1CI 格式的,看起來很難懂,但其實我們看看它輸出的結果,其實就是一個非常普通的 Vlookup 函數。
編寫宏的時候,我們先是手動把一個公式寫出來,再把公式中的定義了變量的地方,用變量進行替換就可以了。
【5】進行單科成績排序
首先,科目排序列獲取到科目成績以及學生的名字。
然後,用 Sort 函數,注意排序的範圍,只選兩列。
排序後,選擇成績列,定義一個條件格式,把前 5 的成績高亮出來。這個條件格式怎麼寫,不需要太過關注,要用的時候,我們使用 錄製新宏 的功能,可以很容易獲取到。
【6】計算總分,並排序
總分的計算,用一個 SUM 函數就可以了。
總分的排序,其實跟每個單科排序一樣的,最後,總分排序的顏色,換一個跟單科不一樣的,顯得更加明顯一些。