专栏首页完美ExcelExcel应用实践06:进行多条件统计

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

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

问题:怎么用EXCEL多条件统计重复次数计数?如下图1所示,要统计每个试室都有什么专业(F),每个专业多少人,用左边的表生成右边的表(生成M、N、0列就行),数据大概4W多条,需要考虑效率。怎么能做出来?VBA,公式啥的行,不想用筛选。

图1

由于没有原始表格,我自已仿照做了一个示例数据表,如下图2所示。

图2

提问者只要求能够生成右边表格的右侧3列,图2中为列L、列M、列N,这样的话列I、列J、列K中的数据是应该都有了,这样相对来说更容易一些。由于我没有原始表格,所以我将右边表格的全部6列都使用VBA来生成。

分析图2左侧的数据表,统计每个试室有什么专业、每个专业有多少人,实际上就是求每个试室每个专业的不重复数。因此,我将场次、考场编码、试室、试室编码、报考专业连接起来,单独放置在列G中,将它们作为字典的键值,这样就得到了不重复的数据。在填充字典键的同时,统计重复的数据,作为键的元素值,从而得到了每个试室每个专业的人数。然后,再将字典键进行拆分,输入到右侧的场次、考场编码、试室、试室编码、报考专业对应单元格中,并将对应的元素值输入到报考人数对应的单元格,这样就得到了统计数据。

完整的VBA代码如下:

Sub StatisticsData()
    Dim lngLastRow As Long
    Dim rng As Range
    Dim myDict As Variant
    Dim myKey As Variant
    Dim str() As String
    Dim num As Long
    '获取数据最后一行
    lngLastRow = Range("A" &Rows.Count).End(xlUp).Row
    '将数据区域单元格中的场次|考场编码|试室|试室编码|报考专业数据组合
    '将组合后的数据临时存放在G列
    '每个单元格数据之间用空格分开
    '方便后面拆分
    For Each rng In Range("A2:A" & lngLastRow)
        With rng
            .Offset(0, 6) = .Offset(0, 0) +" " + _
                            .Offset(0, 1) +" " + _
                            .Offset(0, 2) +" " + _
                            .Offset(0, 3) +" " + _
                            .Offset(0, 5)
        End With
    Next rng
    '字典
    Set myDict =CreateObject("scripting.dictionary")
    '遍历列G中的数据并将其放置在字典中
    '字典中键值为不同的数据组合
    '字典中键对应的值为每种数据组合的数量,即专业报考人数
    For Each rng In Range("G2:G"& lngLastRow)
        With myDict
            If Not .exists(rng.Value) Then
                .Item(rng.Value) = 1
            Else
                .Item(rng.Value) =.Item(rng.Value) + 1
            End If
        End With
    Next rng
    '清除临时存放在列G中的数据
    Range("G2:G" &lngLastRow).Clear
    '获取字典键
    myKey = myDict.keys
    '遍历字典键
    For num = 0 To UBound(myKey)
        '拆分字典键中的字符
        '分别对应场次|考场编码|试室|试室编码|报考专业
        str = Split(myKey(num))
        '取出相应的值并放置在相应的单元格
        With Range("I2")
            .Offset(num, 0) = str(0)
            .Offset(num, 1) = str(1)
            .Offset(num, 2) = str(2)
            .Offset(num, 3) = str(3)
            .Offset(num, 4) = str(4)
            .Offset(num, 5) =myDict.Item(myKey(num))
        End With
    Next num
    '获取统计区域的数据最后一行
    lngLastRow = Range("I" &Rows.Count).End(xlUp).Row
    With Range("I1:N" &lngLastRow)
        '对统计区域的数据排序
        .Sort _
        Key1:="场次", Order1:=xlAscending, _
        Key2:="考场编码", Order2:=xlAscending, _
        Key3:="试室编码", Order3:=xlAscending, _
        Header:=xlYes
        '调整列宽
        .Columns.AutoFit
    End With
    Set myDict = Nothing
End Sub

代码中有相应的注释,可以参照理解。

下面是代码的图片版:

图3

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

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

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    一转眼,2019年已至4月,自从年初立下flag后,便努力朝着实现它的方向奔跑。有些执行得很好,比如每天更新完美Excel微信公众号,坚持每天学习,而有些则还没...

    fanjy

扫码关注云+社区

领取腾讯云代金券