Excel VBA 根据数据做汇总表格,最大值,最小值

最新推荐文章于 2024-03-25 13:38:32 发布

Hello_World_2021

最新推荐文章于 2024-03-25 13:38:32 发布

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： Excel VBA 文章标签： excel

本文链接：https://blog.youkuaiyun.com/Hello_World_2021/article/details/105513996

本文介绍如何使用Excel VBA自动化创建汇总表格，包括查找数据范围内的最大值和最小值。通过VBA编程，可以高效处理大量数据，提高工作效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


Sub sum_table()
'汇总每个sheet的结果,最大值,最小值之类的
    Dim arr, dic, colh, rowh
    Dim i%, j%, k%
    Dim rmax(), rmin(), rrange(), rmaxamongpulley(), rminamongpulley(), rmaxamongeachrun(), rminamongeachrun() As Double
    Dim coln, rown As Integer, n As Integer, m As Integer
     
    'Sheets.Add After:=Worksheets("content")
    'ActiveSheet.name = "sum"
     
    n = Worksheets.Count
    For k = 3 To n
         g = Worksheets("content").Cells(k - 2, 1).Value
         coln = Worksheets(g).UsedRange.Columns.Count
         coln = Worksheets(g).[IV1].End(xlToLeft).Column
         rown = Worksheets(g).UsedRange.Rows.Count
         
         colnsum = Worksheets("sum").[IV1].End(xlToLeft).Column + 1
         
         '取列头,并去重
         Set dic = CreateObject("scripting.dictionary")
         Worksheets(g).Activate
         arr = ActiveSheet.Range(Cells(2, 1), Cells(2, coln))
         For i = 1 To UBound(arr)
             For j = 1 To UBound(arr, 2)
                 dic(arr(i, j)) = ""
             Next j
         Next i
         rowh = Application.Transpose(dic.keys)
        
         Set dic = Nothing
         '取行头,并去重
         Set dic = CreateObject("scripting.dictionary")
         Worksheets(g).Activate
         arr = ActiveSheet.Range(Cells(3, 1), Cells(3, coln))
         For i = 1 To UBound(arr)
             For j = 1 To UBound(arr, 2)
                 dic(arr(i, j)) = ""
             Next j
         Next i
         colh = dic.keys
         
         Worksheets("sum").Activate
         Range(Cells(2, colnsum).Address).Resize(UBound(rowh), 1) = rowh
         Range(Cells(2