使用Excel進行個人計劃執行記錄與統計分析

使用Excel進行個人計劃執行記錄與統計分析

下面，將創建過程與大家分享。有興趣的朋友可以作為模板，或者與自已的實際情況相結合，稍作修改，用作自已的一個工具。

下所示為我使用的數據記錄表，工作表名“個人計劃執行記錄”，每天晚上睡覺前或者第二天上午上班前，我會將一天的學習生活情況記錄在此。

在列G中，使用了“數據驗證”功能（即原來的“數據有效性”），可以直接在列表中選擇分類，如下所示。

“數據驗證”設置如下所示。

其中，“category”是定義的名稱，代表下所示工作表“計劃執行統計”的單元格區域B7:B21。

在中，單元格C4是統計的起始日期，命名為startDate；單元格D4是統計的結束日期，命名為endDate，這是我們在這個工作表中唯一要輸入的兩個數值。輸入日期后，單擊其右側的“更新”按鈕，自動統計這兩個日期之間的相應數據。該按鈕關聯了下文所的用于實現自動統計的VBA程序。

單元格區域C7:D21是輸出區域，通過VBA程序自動生成各分類上花費的時間和做的次數。

單元格區域B7:B21除作為上文介紹的分類下拉列表項來源外，還設置了條件格式，如下所示。當統計的次數不符合要求時，相應分類的字體會顯示紅色。

在VBA代碼中，使用了高級篩選功能。工作表“個人計劃執行記錄”的單元格區域J1:K2是條件區域，關聯了工作表“計劃執行統計”中輸入的起始日期（startDate）和結束日期（endDate）。從單元格M1開始，放置符合篩選條件的數據，如下所示。

代碼將篩選出的數據與分類（category）比較，計算相應分類上事項所花的時間及開展的次數，并輸入工作表“計劃執行統計”中的單元格區域C7:D21。

完整的代碼如下：

Sub planstatistics()

‘數據分析匯總工作簿變量

Dim wksStat As Worksheet

‘數據工作簿變量

Dim wksRecord As Worksheet

‘數據區域

Dim rngDatas As Range

‘篩選數據放置的區域

Dim rngFilterData As Range

‘篩選條件區域

Dim rngCriteria As Range

‘循環變量

Dim rng As Range

Dim cell As Range

‘數據區域的最后一行

Dim lngDataLastRow As Long

‘篩選的數據的最后一行

Dim lngFilterLastRow As Long

‘數據分析區域的最后一行

Dim lngLastRow As Long

‘計數變量

Dim lngCount As Long

‘設置工作簿變量

Set wksStat = Worksheets(“計劃執行統計”)

Set wksRecord = Worksheets(“個人計劃執行記錄”)

‘設置被篩選數據所在區域

lngDataLastRow =wksRecord.Range(“A” & Rows.Count).End(xlUp).Row

Set rngDatas =wksRecord.Range(“A1:G” & lngDataLastRow)

‘初始化篩選條件和篩選值放置的區域

With wksRecord

.Range(“J2″) =”>=” & [StartDate]

.Range(“K2″) =”<=” & [EndDate]

.Range(“M1:S” &Rows.Count).Clear

Set rngCriteria =.Range(“J1:K2”)

Set rngFilterData =.Range(“M1”)

End With

‘篩選數據

rngDatas.AdvancedFilterAction:=xlFilterCopy, _

CriteriaRange:=rngCriteria, _

CopyToRange:=rngFilterData

‘獲取篩選的數據

lngFilterLastRow =wksRecord.Range(“M” & Rows.Count).End(xlUp).Row

If lngFilterLastRow = 1 Then Exit Sub

‘清除統計表中已有數據

lngLastRow = wksStat.Range(“B”& Rows.Count).End(xlUp).Row

wksStat.Range(“C7:D” &lngLastRow).ClearContents

‘分析數據并將結果輸入到數據分析工作簿

For Each rng In [Category]

lngCount =

For Each cell InwksRecord.Range(“S2:S” & lngFilterLastRow)

If rng = cell Then

rng.Offset( , 1) =rng.Offset( , 1) + cell.Offset( , -2)

lngCount = lngCount + 1

End If

Next cell

rng.Offset( , 2) = lngCount

Next rng

End Sub

代碼中有很多“硬編碼”，例如wksStat.Range(“C7:D” & lngLastRow)和wksRecord.Range(“S2:S” &lngFilterLastRow)中的單元格引用。如果工作表中的分類或列數據有增減，要作相應的修改。

代碼的圖片版如下：

結語：不必拘束于代碼的優雅，也不必在意通用性，只要能夠解決問題，快速實現自已的目的，適合自已就行，這就是VBA最大的好處。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。