专栏首页完美ExcelExcel应用实践03:使用Excel进行个人计划执行记录与统计分析

Excel应用实践03:使用Excel进行个人计划执行记录与统计分析

学习Excel技术,关注微信公众号:

excelperfect

一转眼,2019年已至4月,自从年初立下flag后,便努力朝着实现它的方向奔跑。有些执行得很好,比如每天更新完美Excel微信公众号,坚持每天学习,而有些则还没有开始。是时候该督促自已全面开始了!于是,制作了一个简单的计划执行情况统计分析表,加上少量的VBA代码,以方便自已每周检视计划的执行情况,提醒自已哪些没做,要赶快补上。

下面,将创建过程与大家分享。有兴趣的朋友可以作为模板,或者与自已的实际情况相结合,稍作修改,用作自已的一个工具。

下图1所示为我使用的数据记录表,工作表名“个人计划执行记录”,每天晚上睡觉前或者第二天上午上班前,我会将一天的学习生活情况记录在此。

图1

在列G中,使用了“数据验证”功能(即原来的“数据有效性”),可以直接在列表中选择分类,如下图2所示。

图2

“数据验证”设置如下图3所示。

图3

其中,“category”是定义的名称,代表下图4所示工作表“计划执行统计”的单元格区域B7:B21。

图4

在图4中,单元格C4是统计的起始日期,命名为startDate;单元格D4是统计的结束日期,命名为endDate,这是我们在这个工作表中唯一要输入的两个数值。输入日期后,单击其右侧的“更新”按钮,自动统计这两个日期之间的相应数据。该按钮关联了下文所讲的用于实现自动统计的VBA程序。

单元格区域C7:D21是输出区域,通过VBA程序自动生成各分类上花费的时间和做的次数。

单元格区域B7:B21除作为上文介绍的分类下拉列表项来源外,还设置了条件格式,如下图5所示。当统计的次数不符合要求时,相应分类的字体会显示红色。

图5

在VBA代码中,使用了高级筛选功能。工作表“个人计划执行记录”的单元格区域J1:K2是条件区域,关联了工作表“计划执行统计”中输入的起始日期(startDate)和结束日期(endDate)。从单元格M1开始,放置符合筛选条件的数据,如下图6所示。

图6

代码将筛选出的数据与分类(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 = 0

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

If rng = cell Then

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

lngCount = lngCount + 1

End If

Next cell

rng.Offset(0, 2) = lngCount

Next rng

End Sub

代码中有很多“硬编码”,例如wksStat.Range("C7:D" & lngLastRow)和wksRecord.Range("S2:S" &lngFilterLastRow)中的单元格引用。如果工作表中的分类或列数据有增减,要作相应的修改。

代码的图片版如下:

结语:不必拘束于代码的优雅,也不必在意通用性,只要能够解决问题,快速实现自已的目的,适合自已就行,这就是VBA最大的好处。

本文分享自微信公众号 - 完美Excel(excelperfect)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-04-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Excel应用实践06:进行多条件统计

    这是在知乎上看到的一个问题,我试着用VBA来解决。欢迎大家就自已使用Excel中遇到的问题或想要的解决方案提问,我将尽力解答。

    fanjy
  • Excel应用实践04:分页单独打印Excel表中的数据

    在实际工作中,我们经常会遇到想将工作表中的数据(如下图1所示的“数据”工作表)导入到固定的表格(如下图2所示)中并打印。

    fanjy
  • Excel VBA解读(138): 自定义函数时使用字节数组实现更快的字符串处理

    如果有很多行,要查找每行字符串第一个大写字母的位置,则使用数组公式会花费不少时间。

    fanjy
  • Excel VBA解读(137): 让使用用户定义函数的数组公式更快

    Excel数组公式能够做很多令人惊讶的事情。除了在输入完后要按Ctrl+Shift+Enter组合键外,与普通公式一样。本文主要研究使用用户定义函数的数组公式。

    fanjy
  • Excel VBA解读(134): 使用Excel函数提高自定义函数的效率

    在上篇文章中,我们展示了自定义函数有效的方式是通过将单元格区域读取到Variant型数组来传递单元格区域数据。本文将介绍在自定义函数中最有效的方式是使用Exce...

    fanjy
  • Matlab加上VBA编程,表格就能画画了

    之前学习Matlab是为了参加一个数学建模的比赛,但是在慢慢的学习当中发现了matlab这款软件是真的有趣,真的非常有用,大家没事也可以去学习一下使用matla...

    FB客服
  • VBA实用小程序50: 在指定的单元格中插入指定的形状

    下面的自定义函数使用Shapes集合对象的AddShape方法及其参数,可以在指定的单元格中插入指定的形状。

    fanjy
  • Excel VBA解读(143): 在自定义函数中使用整列引用时,如何更有效率?

    Excel用户经常发现在公式中使用整列的引用很方便,这样可避免每次添加新数据时都必须调整公式。因此,当编写用户自定义函数时,可能会使用:

    fanjy
  • Excel VBA解读(135): 影响工作表公式中运用自定义函数效率的Bug及解决方法

    在前面的两篇文章中,我们通过简单地修改VBA代码来使自定义函数运行得更快。本文将聚焦于Excel中会影响到自定义函数的Bug,并探讨如何避免它们。

    fanjy

扫码关注云+社区

领取腾讯云代金券