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

or

如何快速汇总Excel中的每一行或第n列?

众所周知,我们可以应用Sum函数来添加单元格列表,但有时我们需要为了某种目的而对每个其他单元格进行求和,并且Excel没有标准函数允许我们对每个第n个单元格求和。 在这种情况下,我们如何在Excel中对所有其他或第n行/列进行求和?

用公式计算每隔一行或第n行/列

使用用户定义函数计算每隔一行或第n行/列

用Kutools for Excel计算每隔或第n行/列的总和/平均值/计数


选择每隔一行或第n行/列,然后根据需要进行计算:

总结,平均或统计每隔一行或第n列, Kutools for Excel's 选择间隔行和列 功能可帮助您首先选择每隔一行或第n行,然后在状态栏底部进行计算。 了解更多... 免费下载。

DOC森 - 每隔一个-cell14

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



通过以下示例,我将向您展示如何应用总计每个其他单元格的公式。

1。 使用数组公式计算每一行或列的总和

在空白单元格中,请输入此公式: =SUM(IF(MOD(ROW($B$1:$B$15),2)=0,$B$1:$B$15,0)), 然后按 Ctrl + Shift + Enter 键,并且列B中的每个其他单元格值已经被求和。 查看屏幕截图:

DOC森 - 每隔一个-cell1  2 DOC森 - 每隔一个-cell2

备注:

1。 在上面的数组公式中,可以将数字2更改为3,4,5 ...,这意味着对每个3rd行,每个4th行,每个5行进行求和...

2。 如果你想对所有其他列求和,你可以输入下面的公式: =SUM(IF(MOD(COLUMN($A$1:$O$1),2)=0,$A$1:$O$1,0)),然后按 Ctrl + Shift + Enter 键来获得结果。 看截图:

DOC森 - 每隔一个-cell3

2。 使用公式来计算每隔一行或每列

这里有另一个公式可以帮助你计算工作表中的每个其他或第n个单元格。

在一个空单元格中,请输入此公式: =SUMPRODUCT((MOD(ROW($B$1:$B$15),3)=0)*($B$1:$B$15))。 然后按 输入 键,并且每个3rd单元格都已添加。 查看屏幕截图:

DOC森 - 每隔一个-cell4  2 DOC森 - 每隔一个-cell5

备注:

1。 在上面的公式中,可以将数字3更改为4,5,6 ...,这意味着将每个4th行,每个5th行,每个6th行进行求和...

2。 如果你想对所有其他列求和,你可以输入下面的公式: =SUMPRODUCT((MOD(COLUMN($A$1:$O$1),3)=0)*($A$1:$O$1)).

DOC森 - 每隔一个-cell6

如果您想了解更多有关这些配方的详细信息,请访问以下网站:

http://www.ozgrid.com/forum/showthread.php?t=26443

http://www.xl-central.com/sum-every-2nd-row.html

http://www.excel-easy.com/examples/sum-every-nth-row.html


以下用户定义函数还可以帮助您在Excel中添加每个其他或第n个单元格。

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

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

Function SumIntervalRows(WorkRng As Range, interval As Integer) As Double
'Update 20130907
Dim arr As Variant
Dim total As Double
total = 0
arr = WorkRng.Value
For i = interval To UBound(arr, 1) Step interval
    total = total + arr(i, 1)
Next
SumIntervalRows = total
End Function
Function SumIntervalCols(WorkRng As Range, interval As Integer) As Double
Dim arr As Variant
Dim total As Double
total = 0
arr = WorkRng.Value
For j = interval To UBound(arr, 2) Step interval
    total = total + arr(1, j)
Next
SumIntervalCols = total
End Function

3。 然后保存此代码,并根据需要将以下公式输入到空白单元格中,请参阅屏幕截图:

用于总结每第四行: = SumIntervalRows(B1:B15,4)

DOC森 - 每隔一个-cell7

用于总结每第四列: = SumIntervalCols(A1:O1,4)

DOC森 - 每隔一个-cell8

4。 然后按 输入 键,你会得到计算。 查看屏幕截图:

总计每四行:

DOC森 - 每隔一个-cell9

总结每四列:

DOC森 - 每隔一个-cell10

注意:您可以将数字4更改为任何其他数字,例如2,3,5 ...这意味着对每第二行,每三行,每五行或一列进行求和。


也许公式和用户定义的函数对于大多数人来说都有些困难,在这里我可以向你介绍一个更简单的方法来解决这个任务。

您订购的 Kutools for Excel选择间隔行和列,您可以先选择您需要的每个其他或第n个单元格,然后最后使用Sum函数对单元格进行求和,然后为选定单元格定义一个名称。

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

安装后 Kutools for Excel请按以下步骤操作:

1。 选择您想要对每隔一个或第n个单元求和的列。

2。 点击 Kutools > 选择 > 选择间隔行和列,看截图:

DOC森 - 每隔一个-cell11

3。 在 选择间隔行和列 对话框中,根据需要指定操作,请参阅截图:

DOC森 - 每隔一个-cell12

4。 点击 OK,并且每隔一行已经从第一行中选择,现在,您可以查看计算结果,如平均值,总和,计数值都显示在状态栏的底部。 看截图:

DOC森 - 每隔一个-cell13

备注:

1。 使用此功能,您可以设置数字2,3,4 ... in 间隔 选项,并且您将从步骤2中的范围的第一行开始,选择具有3,4,3行间隔的单元格。

2。 通过上述步骤,您还可以根据需要对每一列或第n列进行求和。

如果您想了解更多关于此功能的信息,请点击 选择间隔行和列.

立即下载并免费试用Kutools for Excel!


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


相关文章:

如何仅汇总Excel中的过滤或可见单元格?

如何在Excel中求和绝对值?


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.
    Nilesh · 4 months ago
    The formula is not working for the column can anyone please help me to add odd column
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hi, Nilesh,
      If you want to sum the cell values in all odd row, the below formula may help you! (Please change the cell references to your need.)

      =SUMPRODUCT(B1:B15,MOD(ROW(B1:B15)+0,2))

      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Matt · 2 years ago
    Is there a way to add another criteria to this formula? I want to add every 9th row that is great than -40. I would appreciate your help. Thanks in advance!
  • To post as a guest, your comment is unpublished.
    ana oliveira · 2 years ago
    Used the visual basic programming – excellent!

    The other options worked fine except for row n.º 1.
  • To post as a guest, your comment is unpublished.
    dilip · 3 years ago
    sir
    I have used visual basic for SumIntervalCols function as stated above and saved the file as macro enable worksheet.
    when next time open the file the user define command is missing from drop down function menu
    Please help how to fix user define macro and can be used for other excel sheets.

    regards
    D KUMAR
  • To post as a guest, your comment is unpublished.
    Shem · 3 years ago
    This method does not work for me. I entered the 15 numbers in the column B (B1:B15) as in the example and copy the formula to a blank cell, the result is 0. If I change the if statement to IF(MOD(ROW($B$1:$B$15),2)=1, the result is 3795 that is sum(B1:B15). What is wrong?
  • To post as a guest, your comment is unpublished.
    Harry Flashman · 4 years ago
    This formula is easily modified to work with either ranges starting with odd or even numbered rows/columns. For example: =SUM(IF(MOD(ROW($B$1:$B$15),2)=0,$B$1:$B$15,0)) will sum only even numbered rows in the specified range, but =SUM(IF(MOD(ROW($B$1:$B$15),2)=1,$B$1:$B$15,0)) will sum only odd numbered row. This is because of the way the MOD function works. MOD(4,2) returns 0 because 2 divides into 4 with no remainders because 4 is an even number, but MOD(5,2) returns 1 because 2 into 5 leaves 1 remained.
  • To post as a guest, your comment is unpublished.
    hemanth · 4 years ago
    i am very interested now more about excel so please guide me.
    so, in will adding in last u use 4 why.
  • To post as a guest, your comment is unpublished.
    deka · 4 years ago
    sum of columns works as well, but you need to start from column A otherwise it returns a wrong result.
  • To post as a guest, your comment is unpublished.
    Anisha · 4 years ago
    HOPE THIS HELP SORT OUT THE ISSUE
  • To post as a guest, your comment is unpublished.
    Rich · 5 years ago
    This was very helpful. I was looking form something that would total Ro1 1 col 1, 4, 7, etc. Using the SumIntervalCols didn't work correctly so by just changing the For loop to: For j = 1 To UBound(arr, 2) Step interval gave me what I wanted, e.g. (F2:BH2,3). Now I wanted to average the totals in every third cell so I added the function:

    Function CountIntervalCols(WorkRng As Range, interval As Integer) As Double
    Dim arr As Variant
    Dim total As Double
    total = 0
    arr = WorkRng.Value
    For j = 1 To UBound(arr, 2) Step interval
    If (Not IsEmpty(arr(1, j)) And IsNumeric(arr(1, j))) Then
    If arr(1, j) > 0 Then
    total = total + 1
    End If
    End If
    Next
    CountIntervalCols = total
    End Function

    Then it was simple to create an AvgIntervalCols:

    Function AvgIntervalCols(WorkRng As Range, interval As Integer) As Double
    Dim totalsum As Double
    Dim totalcount As Double
    totalsum = SumIntervalCols(WorkRng, interval)
    totalcount = CountIntervalCols(WorkRng, interval)
    If totalcount = 0 Then
    totalsum = 0
    Else
    totalsum = totalsum / totalcount
    End If
    AvgIntervalCols = totalsum
    End Function

    Thanks!
  • To post as a guest, your comment is unpublished.
    Frank · 5 years ago
    This is a wonderful article! I didn't know that the MOD function can handle an array argument. I enjoyed the formula solution very much, it is simply brilliant!
  • To post as a guest, your comment is unpublished.
    Peter · 5 years ago
    Hi,

    this is helpful, but the formula is bonkers (at least if you try to apply it to columns) Column(REGION) returns the column index of the 1st column in the region. So the formula totally does not work for columns...

    But it is helpful to start you working in the right direction.
  • To post as a guest, your comment is unpublished.
    Kraems · 5 years ago
    Hi
    I tried it - but this formula
    =SUM(IF(MOD(ROW($B$1:$B$15),2)=0,$B$1:$B$15))
    functions only IF you start at an uneven row.Otherwise it gives a wrong result. Try it,insert a row before row one.
    ADVICE: instead insert the 2nd or 3rd row to sum (it is a variable, insert a reference Cell (B20) or so and insert there which every row you like to sum.
    Anyway, I wonder for what the heck you use such a formula???
    best regards
    kraems