本文主要是介绍Excel 2010 VBA 入门 083 数据处理之按单列汇总数据，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

目录

示例

数据表

汇总表

代码

示例

如图所示，该表为某公司的销售数据。如何使用VBA按商品名汇总销售数量和收入？

数据表

汇总表

代码

Option Explicit

Sub 按单列汇总数据()
    Dim shtData As Worksheet
    Dim shtSum As Worksheet
    Dim rowSum As Long
    Dim rowData As Long
    Dim Rng As Range
    Dim sKey As String
    
    Set shtData = Sheets("sheet1")
    Set shtSum = Sheets("sheet2")
    
    rowSum = 2
    shtSum.Range("A2:C" & Rows.Count).Clear
    
    '获取汇总列字段不重复记录
    For rowData = 2 To shtData.Range("A1").CurrentRegion.Rows.Count
        sKey = shtData.Cells(rowData, "C").Value
        Set Rng = shtSum.Range("A:A").Find(sKey, lookat:=xlWhole)
        If Rng Is Nothing Then
            shtSum.Cells(rowSum, "A").Value = sKey
            rowSum = rowSum + 1
        End If
    Next rowData
    
    '汇总
    For rowSum = 2 To shtSum.Range("A1").CurrentRegion.Rows.Count
        sKey = shtSum.Cells(rowSum, "A").Value
        For rowData = 2 To shtData.Range("A1").CurrentRegion.Rows.Count
            If shtData.Cells(rowData, "C").Value = sKey Then
                shtSum.Cells(rowSum, "B").Value = shtSum.Cells(rowSum, "B").Value + shtData.Cells(rowData, "D").Value
                shtSum.Cells(rowSum, "C").Value = shtSum.Cells(rowSum, "C").Value + shtData.Cells(rowData, "E").Value
            End If
        Next rowData
    Next rowSum
End Sub

这篇关于Excel 2010 VBA 入门 083 数据处理之按单列汇总数据的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！