从EXCEL档快速建立SAS资料档的秘笈.doc

EXCEL 學習三部曲 筆者在計中教EXCEL軟體課程十多年，從EXCEL 95到最新的EXCEL 2010版本，深刻體認到EXCEL實在是資料處理上必備的技能。但如果問每個人「你的EXCEL程度如何？」這樣的問題，得到的答案幾乎是模糊的。因此在這裡提出兩道大家常面對的問題，讓各位測測看自己的EXCEL程度到底屬於初級、中級、高級的哪一級。 第一題：利用EXCEL印製報表很是方便，讓每一頁都可印出標題是初級者可辦到的，但是EXCEL列印的列數常是由系統依篇幅自動計算，所以常會有一頁報表31或33筆紀錄的情況，並不利於統計紀錄筆數。如果你可達成每頁只列印30筆就要一定要跳頁的規定，就算是屬於EXCEL的中級程度。又如果可以寫一個小小的巨集程式，做到每頁只列印30筆，而且換不同單位部門時也要跳頁，讓不同的單位部門分開印在不同頁上，這樣你就可以算是具有EXCEL高級的程度了。 第二題：利用EXCEL來排名次也很簡單???以國中基測為例，總分相同的同學還要比作文分數定高下，如果先以總分為主鍵從高到低排序，再以作文分數為第二鍵從高到低排序，然後以填滿數列的方式拉出1、2、3…名次就好了，這是EXCEL初學者常會做的事，但是會發生同樣總分的人，名次不相同的情況。學過進階EXCEL函數的同學可能會知道要使用RANK函數，讓同樣總分的人名次也相同，以下的名次則累計。但是以國中基測為例，總分相同的同學還要比作文分數定出高下，這時RANK函數就沒辦法做到了，我們必須學習寫一個小小的巨集程式，來做到這種排名的規定。只要以此種規定排名，再按照各校招生人數來篩選學生，使位在邊緣的同學只要是名次相同的就一律進榜。總分相同但作文分數較低的同學就要被刷掉。 筆者上課時因為時間的關係，往往只能教到初級、中級的程度，但有不少同學對於撰寫EXCEL巨集很有興趣，因此想說在這裡紀錄下來，讓稍具程式設計基礎的同學可以舉一反三，加強進一步解決問題的能力。 第一題：利用EXCEL印製報表 利用EXCEL印製報表很方便，讓每一頁都可印出標題是初級者可辦到的，我們以EXCEL 2010為例，只要如下圖從「版面配置--列印標題--版面設定」的工作表頁籤去設標題列為第一和第二列($1:$2)即可。 但是EXCEL列印的列數常是由系統依篇幅自動計算，所以常會有一頁報表31或33筆紀錄的情況，並不利於統計紀錄筆數。如我們所舉的例子，每頁印出26筆資料，但我們想印出規定每頁為25筆資料的報表。 其實這也很簡單，可利用錄製巨集的方式達到，先將游標定位在第25筆資料之後，即第26筆資料處，從「檢視--巨集--錄製巨集」，先選「以相對位置錄製」，再執行「錄製巨集」。 填入所要取的巨集名稱和快速鍵後，就可以開始錄製巨集。 ， 錄製的動作只有兩個，在「版面配置--分頁符號--插入分頁」後，再將游標定位在第50筆資料之後，即可「停止錄製」。 接著從「檢視巨集」去找到這個巨集，按下「編輯」，即可轉到VBA編輯視窗。 EXCEL會自動產生這兩個動作的指令，接著我們要作的只是依需要在指令外圍加上一個迴圈，看迴圈需作多少次。再從巨集視窗去「執行」此巨集即可。 茲將程式碼說明如下，「」符號表示之後的文字是註解，可不必打在程式裡： Sub 巨集PAGE25() 我們錄置巨集時所取的巨集名稱 快速鍵: Ctrl+p For i = 1 To 10 讓這兩個動作重複執行10次 在目前作用儲存格所在的位置上面插入分頁線 ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell 再將作用儲存格以相對參照移到25列之後 並選取整列 ActiveCell.Offset(25, 0).Rows(1:1).EntireRow.Select Next End Sub 以下可看到執行此巨集後，報表列印的結果每頁固定是25筆。 做到這裡表示你已經有中級程度了。但是從上面報表我們發現金華國中的後段和龍門國中的前段資料竟然在同一頁。所以我們希望寫一個小小的巨集程式，做到每頁只列印30筆，而且換不同學校或單位時也要跳頁從第一列開始印，讓不同的學校或單位分開印在不同頁上。如下列兩頁所示。 我們只要根據學校或單位先行排序，在剛才的VB編輯視窗撰寫以下的巨集程式，並以同樣的方式執行巨集，就可以做到了。程式說明如下： Sub PAGESchool() Dim i, count As Integer i是作用儲存格所在的列數 count是指每頁要印幾筆 Dim school, oldshool As String 分別存放目前這筆和上一筆學校的字串變數 count = 1 每頁25筆 從1起算 i = 3 第一次出現校名是B3