提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何组合重复的行并在Excel中求和值?

DOC-结合,sum1 -2 DOC-结合,sum2

在Excel中,如果您有一系列包含重复条目的数据,并且现在要合并重复数据并在另一列中总结相应的值,则可能会遇到此问题,如以下屏幕截图所示。 你怎么能解决这个问题?

组合重复的行并使用Consolidate函数对值进行求和

组合重复的行并将值与VBA代码相加

合并重复的行,并将这些值与Kutools for Excel相加

将基于另一列中重复值的相应行与Kutools for Excel相结合


在另一列中组合重复行和总和/平均对应值

Kutools for Excel's 高级Combibe行 帮助您根据键列将多个重复行组合到一个记录中,并且还可以为其他列应用一些计算,如总和,平均值,计数等。 点击下载Kutools for Excel!


箭头蓝色右泡 组合重复的行并使用Consolidate函数对值进行求和


Consolidate是一个有用的工具,可以帮助我们在Excel中整合多个工作表或行,因此使用此功能,我们也可以根据重复项汇总多行。 请执行以下步骤:

1。 单击要在当前工作表中查找结果的单元格。

2。 去点击 数据 > 整合,看截图:

DOC-结合,sum3

3。 在 整合 对话框:

  • (1。)选择 总和 前往 功能 下拉列表;
  • (2。)单击 DOC-结合,sum5 按钮选择要合并的范围,然后单击 添加 按钮添加引用 所有参考 列表框;
  • (3。)检查 顶行 左栏 前往 在中使用标签 选项。 看截图:

DOC-结合,sum4

4。 完成设置后,单击 OK,并将这些副本进行合并和总结。 看截图:

DOC-结合,sum6

注意:如果范围没有标题行,则需要取消选中 顶行 来自 在中使用标签 选项。


箭头蓝色右泡 组合重复的行并将值与VBA代码相加

下面的VBA代码也可以帮助你合并工作表中的重复行,但同时原始数据将被销毁,你需要备份一份数据。

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块窗口.

VBA代码:合并重复的行并对值进行求和

Sub CombineRows()
'Update 20130829
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
    Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

3。 然后按 F5 键来运行此代码,并且您需要在弹出的提示框中选择要合并的范围。 看截图:

DOC-结合,sum7

4。 然后点击 OK,重复的行已经被组合并且值被加起来。

注意:如果你想使用这个代码,你最好制作一个文件的副本,以避免破坏数据,这个代码只适用于两列。


箭头蓝色右泡 合并重复的行,并将这些值与Kutools for Excel相加

在这里,我介绍一个方便的工具 - Kutools for Excel 为你,它的 先进的组合行 也可以很快的帮个忙解决这个问题。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制. 立即获取 .

安装后 Kutools for Excel请按照以下步骤进行:

1. 选择您需要的范围并点击 Kutools > 内容 > 先进的组合行。 看截图:

DOC-结合,sum7

2。 在 先进的组合行 对话框,检查 我的数据有标题 如果你的范围有标题,并选择你想合并重复和点击的列名称 首要的关键,看截图:

DOC-结合,sum7

3。 然后选择要汇总值的列名称,然后单击 计算 > 总和 或根据需要进行其他计算。 看截图:

DOC-结合,sum7

4。 点击 Ok 要关闭对话框,则可以看到重复项被合并,并将另一列中的相应数据合并在一起。 查看截图:

DOC-结合,sum1 -2 DOC-结合,sum2

点击此处了解更多关于高级合并行。


箭头蓝色右泡 将基于另一列中重复值的相应行与Kutools for Excel相结合

有时候,你想根据另一列中的重复值来组合行 先进的组合行 of Kutools for Excel 也可以为你做个帮忙,请做如下:

1。 选择要使用的数据范围,然后单击 Kutools > 内容 > 先进的组合行 使之成为可能 先进的组合行 对话框。

2。 在 先进的组合行 对话框中,单击要合并其他数据的列名称,然后单击 首要的关键,看截图:

DOC-结合,sum7

3. 然后单击您要合并数据的另一个列名称,然后单击 结合 要选择分隔符来分隔您的组合值,请参阅截图:

DOC-结合,sum7

4。 然后点击 Ok,A列中所有具有相同单元格的值已合并在一起,请参阅屏幕截图:

DOC-结合,sum1 -2 DOC-结合,sum2

箭头蓝色右泡 合并重复的行,并将这些值与Kutools for Excel相加

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

根据重复值将多行组合成一行


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    raven blaze · 1 months ago
    Sub MergeSameCells()
    Application.DisplayAlerts = False

    Dim rng As Range

    MergeCells:

    For Each rng In Selection
    If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then
    Range(rng, rng.Offset(1, 0)).Merge
    GoTo MergeCells
    End If
    Next

    End Sub
  • To post as a guest, your comment is unpublished.
    Alex Lesyk · 5 months ago
    An absolute mess of an explantation. Thanks for the effort but it did nothing to help.
  • To post as a guest, your comment is unpublished.
    andreaB · 8 months ago
    LOVE IT!!! YOUR SAVE MY LIFE!!
  • To post as a guest, your comment is unpublished.
    sarah · 10 months ago
    Ifsum=(columns include,start point row,sum column)
    Example ifsum=(A:D,B:2,D:D)
    WAY EASIER!
  • To post as a guest, your comment is unpublished.
    chinna raju · 1 years ago
    Hi Am chinnaraju

    can u please assist for this. Any one?

    =VLOOKUP(M5,E:F,2,)


    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    chinna raju · 1 years ago
    Hi,
    Can you please explain or share the Formula for below
    A5 A6 A7
    i want answer for KTO as Transpose Rice Biryani Prawns

    Left coloumn Right Coloumn
    KTO Rice
    Office Tab Curd Rice
    KTO Biryani
    Ranjith Chiken
    KTO Prawns

    Thanks,
    Chinnaraju
    9849212552
  • To post as a guest, your comment is unpublished.
    Chinnaraju · 1 years ago
    Hi,
    A5 A6 A7
    i want answer for KTO as Transpose Rice Biryani Prawns

    Left coloumn Right Coloumn
    KTO Rice
    Office Tab Curd Rice
    KTO Biryani
    Ranjith Chiken
    KTO Prawns

    Thanks,
    Chinnaraju
    9849212552
  • To post as a guest, your comment is unpublished.
    Chris · 1 years ago
    what if I want to do a customer math equation with the duplicates instead of sum? For example I want my duplicates to calculate overall electrical resistance.
  • To post as a guest, your comment is unpublished.
    Joshua · 2 years ago
    You guys are awesome! You have saved me so much productivity. Cant thank you guys enough
  • To post as a guest, your comment is unpublished.
    Raghu · 2 years ago
    How to make VBA code to add totals for 12 columns
  • To post as a guest, your comment is unpublished.
    Mostafa · 2 years ago
    Really Thank you your explain realy helpful
  • To post as a guest, your comment is unpublished.
    Ahmed SAmir · 2 years ago
    as usual perfect Ideas and Projects , thank you all :-)
  • To post as a guest, your comment is unpublished.
    Neelesh Sonekar · 3 years ago
    Dear Sir

    What if i have 3 Cloumns ?

    Node1 Node2 Length
    R1 J1 30 J1=113
    J1 J2 29
    J1 J3 54
    J3 J4 47
    J3 J5 27
    J5 J6 19
    J5 J7 17
    J7 J8 10
    J7 J9 56
    J9 J10 96
    J9 J11 29
    J11 J12 34
    J12 J13 10
    J12 J14 49

    Can i use this for 3 columns.
  • To post as a guest, your comment is unpublished.
    Remo · 3 years ago
    This consolidate tool is so useful.
    Many thanks for sharing the info :)
  • To post as a guest, your comment is unpublished.
    Ben · 3 years ago
    Seems like a good spot for a pivot table.
  • To post as a guest, your comment is unpublished.
    Jon · 3 years ago
    How do you make that column where the sums are to update automatically when you change the original data?
  • To post as a guest, your comment is unpublished.
    Maria · 3 years ago
    Could do find hwo to to exactly the same thing but in Google Docs? I really need it!
  • To post as a guest, your comment is unpublished.
    Sha · 4 years ago
    Thank you so much for great tips. Fantastic.
  • To post as a guest, your comment is unpublished.
    Lars · 4 years ago
    The VBA macro works well, but how should I change it if I want to delete the entire row rather than just clearing contents? I have other columns I want to keep linked to that data.
  • To post as a guest, your comment is unpublished.
    stan · 4 years ago
    VBA code does not work. Combines first column but 2nd column data is all gone
  • To post as a guest, your comment is unpublished.
    stan · 4 years ago
    VBA code breaks when more rows added, not really sure why
  • To post as a guest, your comment is unpublished.
    ND · 4 years ago
    Worked fantastically! Thanx! :-)
  • To post as a guest, your comment is unpublished.
    B · 5 years ago
    Couldn't get this to work for more than one column of data - any tips?
  • To post as a guest, your comment is unpublished.
    Ed · 5 years ago
    Your first example is pretty good but it is missing one step. Prior to clicking OK the cursor needs to be in a blank area for the consolidation to export. If you have not selected a blank cell area after adding the range you will get a "source overlaps destination" error.