精华内容
下载资源
问答
  • 在 Excel 中对多行多列进行条件求和

    万次阅读 2018-12-17 03:21:08
    在 Excel 中对多行多列进行条件求和问题由来源数据格式我的解决过程用 SUMPRODUCT 函数的失败过程分析错误解决问题用 SUMPRODUCT 解决问题我在 CSDN 的第一篇博客 问题由来 前几天,一名网友在微信群里求助,说有一...

    问题由来

    前几天,一名网友在微信群里求助,说有一个表格汇总数据不会算。经过两天努力,我帮他写了一个宏,顺利完成了任务。这个按字段汇总的宏我以后再说,今天只说验算的公式。
    由于源数据比较多,出于谨慎,我需要将宏算出来的汇总表进行验算,并且最好是用一个单元格用单个公式完成对源数据的加和运算,因此想编写一个公式把源数据表所有符合条件的数据加和,再对汇总表加和,比较两个和是否一致。

    源数据格式

    源数据是由上百个结构相同的数据块组成的数据块纵列。下面展示连续的两个数据块:
    两个数据块

    每个数据块的格式如下:

    1. 总共有21行、13列;
    2. 第1行是标题,第20、21行是需要被忽略的数据,从第2行到第19行是需要汇总的数据;
    3. 所有数据块的第二列是纵向标题,每块都一样,都是18个同样次序排列的字母;
    4. 每块数据块的第一行是横向标题,此部分每个数据块都不一样,且数量有多有少;
    5. 由此可以看出,需要求和每个数据块从第2行、第3列开始到第19行、第13列的数据块内的数据;

    为什么不能直接求和

    也许有人会说,直接求 C:M 的和不就行了?还真不行。每个数据块的第20行是一个合计行,不能被计算进总数内,所以不能直接求和。而这一个合计行的业务逻辑比较奇怪,我没看出这一行数据和主体数据块的关系,至少不是主体数据块的纵向求和,否则可以用求 C:M 的和然后除以 2 的方式来求所需要的总和。

    我的解决过程

    用 SUMPRODUCT 函数的失败过程

    这个公式需求看上去一点都不难,我不想用数组公式,用 SUMPRODUCT 函数看上去应该是一个非常理想的选择。于是我写了如下的公式:

    =SUMPRODUCT(C:M,((MOD(ROW(C:M)-1,21)+1)>1)*((MOD(ROW(C:M)-1,21)+1)<20)) ​​​​
    

    看上去很完美,用 C:M 指定汇总的列区域,用 ROW 求出单元格所在行的值后用两个 MOD 指出汇总的行区域。两个参数涉及区域都是 C:M,大小完全一样,符合 SUMPRODUCT 函数对各参数之间大小完全匹配的要求。
    但是,输入之后 Excel 提示我 #VALUE!

    分析错误

    问题到底出在什么地方?
    我先构造了一个小范围的公式,即只计算一个数据块,公式如下:

    =SUMPRODUCT(C1:M21,((MOD(ROW(C1:M21)-1,21)+1)>1)*((MOD(ROW(C1:M21)-1,21)+1)<20)) ​​​​
    

    然后用公式标签页的公式求值功能对公式的运算过程进行跟踪,发现问题出在 ROW 函数身上。
    我原以为,因为数组涉及到 C:M 多列,所以按 SUMPRODUCT 函数的工作流程,应该把涉及到的每个单元格都先用 ROW 求行值,再用 MOD 函数取余以确定其在每个数据块中的位置,就是说会生成一个21行×11列的数组。但跟踪下来完全不是这样。ROW 函数偷懒了。
    ROW 看到要计算的是范围是一个 C1:M21 的方块,并没有一个单元格一个单元格去老老实实地求行值,而是把 C1:M1,C2:M2……C21:M21 这21行作为21个计算单元,只计算了21次,得出了21个结果,而不是 21×11=231 个结果,这样当然就跟 C1:M21 不匹配了。

    解决问题

    用 SUMPRODUCT 似乎走进了死胡同,我只能回头考虑用 SUM 的数组公式。一试之下,发现 SUM 比 SUMPRODUCT 果然要聪明一点,能清楚地领会我的意思。我的公式如下:

    {=SUM(((MOD(ROW(C:C)-1,21)+1)>1)*((MOD(ROW(C:C)-1,21)+1)<20)*IF(ISNUMBER(C:M),C:M,))}
    

    用了基本和上面 SUMPRODUCT 里一样的条件设置,就把和求出来了,对行值的判断还仅仅用了 C 一列。不同的是多加了一个 ISNUMBER 的判断,因为 SUM 毕竟不是 SUMPRODUCT,遇到非数值数据不会自动转化为零。

    用 SUMPRODUCT 解决问题

    那用 SUMPRODUCT 真的不能写这个公式吗?非也,经过我脑洞大开的思索,我在原来的公式里多加了一个条件判断

    C1:M1=C1:M1
    

    公式于是变成了这样:

    =SUMPRODUCT(C:M,((MOD(ROW(C:C)-1,21)+1)>1)*((MOD(ROW(C:C)-1,21)+1)<20)*(C1:M1=C1:M1))
    

    看上去是完全的废话条件,因为一个数肯定等于自身。但这个可谓是神来之笔,因为这一个多加的条件强迫 SUMPRODUCT 把参数内的单元格认真对待,横向的每一个单元格兄弟都不能被忽略,不能被第一列单元格大哥的光芒所掩盖。这样,逗号两边的两个参数所代表的数组终于得到平等,可以共同参与运算了。

    我在 CSDN 的第一篇博客

    这是我在 CSDN 的第一篇博客,见笑见笑。

    展开全文
  • vba实现excel多行多列转多行一列

    千次阅读 2018-08-16 18:21:52
    选中部分数据区域(多行多列),例如: 转换之后: vba代码: Sub manyToOne() Dim TheRng, TempArr Dim i As Integer, j As Integer, elemCount As Long On Error GoTo line1 Sheets("Sheet2"...

    选中部分数据区域(多行多列),例如:

    转换之后:

    vba代码:

    Sub manyToOne()
       Dim TheRng, TempArr
       Dim i As Integer, j As Integer, elemCount As Long
       On Error GoTo line1
       Sheets("Sheet2").Range("a:a").ClearContents
       If Selection.Cells.Count = 1 Then
         Sheets("Sheet2").Range("a1") = Selection
       Else
         TheRng = Selection
         elemCount = UBound(TheRng, 1) * UBound(TheRng, 2)
         ReDim TempArr(1 To elemCount, 1 To 1)
         For i = 1 To UBound(TheRng, 2)
           For j = 1 To UBound(TheRng, 1)
             TempArr((i - 1) * UBound(TheRng, 1) + j, 1) = TheRng(j, i)
           Next
         Next
         Sheets("Sheet2").Range("a1:a" & elemCount) = TempArr
       End If
    line1:
    End Sub
    

     

    展开全文
  • 在求平均值时,既可一次只对一行一列或一个单元格区域求平均值,也可以一次对多行多列或多个单元格区域包括不连续的单元格区域批量快速求平均值;在批量求平均值时,需要用快捷键才能实现。一、Ex...

    Excel求平均值有两种方法,一种为用求平均值选项求,另一种为用求平均值快捷键求。用快捷键求平均值需要按四个键,并且需按住一个键,分别再按顺序按其它三个键,比求和快捷键多两个键,按起来没那么方便,但按熟后速度也不慢。

    在求平均值时,既可一次只对一行一列或一个单元格区域求平均值,也可以一次对多行多列或多个单元格区域包括不连续的单元格区域批量快速求平均值;在批量求平均值时,需要用快捷键才能实现。

    一、Excel求平均值方法一:用选项求

    (一)对行求平均值

    1、假如要求每个月各类服装销量的平均值。选中 F2 单元格,选择“公式”选项卡,单击屏幕左上角的“自动求和”下面的倒三角,在弹出的菜单中选择“平均值”,则求平均值公式 =AVERAGE(B2:H2) 自动填充到 F2 单元格中,按回车,求出1月各类服装的平均值;把鼠标移到F2 右下角的单元格填充柄上,按住左键,往下拖,则所经过单元格自动求出对应月份服装销量的平均值;操作过程步骤,如图1所示:

    29264f43fad8de1ed9e362d9f9784425.gif

    图1

    2、提示:如果往下拖过程中,所经过单元格没有变为相应月份的平均值,按 Ctrl + S 保存,就会立即更新为相应行的平均值。如果求平均值结果保留小数位数太多,可以按 Ctrl + 1(关闭中文输入法后按)打开“设置单元格格式”窗口,选择“数字”选项卡,再选择左边的“数值”,在右边“小数位数”输入要保留的小数位数单击“确定”即可。

    (二)对列求平均值

    假如要求每种服装1月到8月的销量平均值。选中 B10 单元格,选择“公式”选项卡,单击“自动求和”下面的倒三角图标,在弹出的菜单中选择“平均值”,则求平均值公式 =AVERAGE(B2:B9) 自动填充到 B10 中,按回车,求出“T恤”1月至8月的平均销量;把鼠标移到 B10 右下角的单元格填充柄上,按住左键,往右拖,则求出其余服装1月到8月的平均销量;操作过程步骤,如图2所示:

    88a786a98fa32988babc56bf0fd5b98b.gif

    图2

    二、Excel求平均值方法二:用快捷键快速求平均值

    (一)用快捷键 Alt + M + U + A 快速对行求平均值

    同样以求每个月各种服装的平均销量为例。选中 F2 单元格,按住 Alt,按一次 M,按一次 U,按一次 A,则求平均值公式 =AVERAGE(B2:E2) 自动输入,按回车,求出各种服装1月份的销量平均值;把鼠标移到 F2 右下角的单元格填充柄上,鼠标变为加号(+)后,双击左键,则自动求出1月到8月各种服装的平均销量;操作过程步骤,如图3所示:

    6e7b2bfdb76d2599d7394daecfe1f069.gif

    图3

    (二)用快捷键 Alt + M + U + A 快速对列求平均值

    同样以求每种服装1月到8月的平均销量为例。选中 B10 单元格,按住 Alt,按一次 M,按一次 U,按一次 A,则求平均值公式 =AVERAGE(B2:B9) 自动输入,按回车,求出“T恤”“1月至8月”的平均销量;把鼠标移到 B10 右下角的单元格填充柄上,鼠标变为加号(+)后,按住左键,往右拖,则求出其余服装1月至8月的平均销量;操作过程步骤,如图4所示:

    8a7bc9a7009ad28f30f92818749e629e.gif

    图4

    提示:对列求平均值不能通过双击单元格填充柄的办法来求剩余列的平均值,只能用向右拖的方法。

    三、Excel同时对多单元格求平均值

    (一)方法一:输入 + 框选

    假如要求第一季度服装的平均销量。选中 F2 单元格,输入公式 =average(,框选 B2:E4,接着输入右括号“)”,按回车,则求出第一季度服装销量的平均值,操作过程步骤,如图5所示:

    c8e029cb854754ec83fd9667e1efa9c9.gif

    图5

    (二)方法二:快捷键 + 框选

    假如要求第二季度服装的平均销量。选中 F5 单元格,按住 Alt,按顺序按 M、U、A,则自动输入公式 =AVERAGE(B5:E5),框选 B5:E7,框选区域自动代替公式中的 B5:E5,按回车,求出第二季度的平均销量,操作过程步骤,如图6所示:

    b15daa43cd141989e03658573706ad14.gif

    图6

    (三)同时对不连续单元格区域求平均值

    假如要求“1、2月与5、6月”服装销量的平均值。选中 F7 单元格,按住 Alt,按顺序按 M、U、A,自动输入公式 =AVERAGE(B7:E7),框选B2:E3,再输入逗号(,),框选 B6:E7,按回车,则求出所框选两个区域的平均销量,操作过程步骤,如图7所示:

    d77b3290b7c6ea470ae21a12a6d6ce4c.gif

    图7

    四、Excel批量求平均值

    (一)对列批量求平均值(一次求出每列的平均值)

    假如要求每种服装1月到8月的平均销量。框选 B2:E9,按住 Alt,按顺序按 M、U 和 A,则自动求出每种服装1月到8月的平均销量,操作过程步骤,如图8所示:

    7456c056244b742fc386b95d4c8688c7.gif

    图8

    (二)对行批量求平均值

    假如要求每个月每种服装的平均销量。框选 F2:F9,按住 Alt,依次按 M、U 和 A,则自动求出 1 到 8 月每种服装的平均销量,操作过程步骤,如图9所示:

    021adeceb99bde9c3e2f4366273d091c.gif

    图9

    提示:对列批量求平均值也可以只框选每列最后的单元格,按快捷键 Alt + M + U + A 就能求出各列的平均值。

    展开全文
  • SUMPRODUCT多列求和的应用

    千次阅读 2019-05-17 16:47:36
    ** SUMPRODUCT多列求和的应用,具体看图 **

    **

    SUMPRODUCT多列求和的应用,具体看图

    **

    在这里插入图片描述

    展开全文
  • MySQL对一行多列求和

    万次阅读 2016-07-11 13:20:47
    SUM函数的语法是:SELECT SUM(expression ) FROM tables WHERE predicates; 表达式可以是一个数值字段或公式。...SELECT 1+2+3……+N AS Total FROM 表 或者select SUM(group_type+group_num_d
  • 自动求和代码如下: protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowIndex >= 0) { sum += Convert.ToInt32(e.Row.Cells[4].Text);...
  • mysql多列求和

    千次阅读 2019-10-06 12:39:07
    table A select (a.a1+a.a2) from A a; 通过多列相加获取列的和值。 更新同理, update A a set a.a3 = (a.a1+a.a3); 转载于:https://www.cnblogs.com/bigbass/p/10220618.html...
  • <p>I want to SUM rows in MySQL table then merge and update. I'm trying to sum all the duplicate payments in a day. I will post my existing query but there are some limitations. ...
  • sql 多行求和

    2016-01-05 10:44:13
    数据通过表联查出来,现在要计算 5 6 7 这的和,还要保持多行数据 理想返回结果 行1 ......5.......18 行2 ......6.......18 行3 ......7.......18 使用SUM会将数据变为1行,不是想要的效果,求指点
  • excel sumifs()对多列求和

    千次阅读 2020-07-31 09:44:36
    如上表,我们要求班组为"甲(白)",产量"<>0"的F0,F1,F2,F3,F4的和。 公式:=SUM(SUMIFS(OFFSET(E:E,,{1,2,3,4,5}),B:B,"甲(白)",E:E,"<>0")) 参考链接:...
  • dataframe 多个列求和(很多列情况)

    千次阅读 2020-11-07 19:39:54
    你可以只需sum并设置paramaxis=1来对行进行求和,这将忽略无数字: In [91]: df = pd.DataFrame({'a': [1,2,3], 'b': [2,3,4], 'c':['dd','ee','ff'], 'd':[5,9,1]}) df['e'] = df.sum(axis=1) df Out[91]: a ...
  • (文章开头的资源为本次使用的SQL脚本文件)在对数据库的两列或多列进行求和时,其中如果有一列为如果存在值为null的行,那么求和后在对应行上求和字段的值也为null,下面两个SQL查询语句展示的就是这种情况: ...
  • <p>Let's say I have 5 different columns, <code>a, b, c, d, e, and I'm selecting multiple rows: <pre><code>$result = mysqli_query($conn,"SELECT a,b,c,d,e FROM posts WHERE submitter='$user'");...
  • 还有一些场景下需要计算分组的前几名,然后在进行求和的,这个我们也可以使用nlargest进行操作: 分组之后进行求和 使用这种方法会出现报错提示,这个因为在和索引都存在loaction_road,有重复,系统有警告,在...
  • shell 多行求和多行求平均值

    千次阅读 2014-08-01 15:01:50
    #合并多行的一然后相加 file="20140730_result.log" for domain in `cat domain.log`;do cat $file | grep $domain | awk '{ sum[$2] += $3; } END { for (i in sum) { print i," count_total "sum[i], "\t...
  • 说,直接上干货! 简述:将L、M和N,相加放到O。(当然这里是相邻的)。同时,也可以求相隔几列的某些相加。 转载于:...
  • MySql 动态多列多行

    2020-03-05 17:42:55
    目的:要将 prc_ben_type 列数据转为多列标题,并对其数据求和; set @acct_id = '001000001'; set @company_code = 'SZWX'; set @period_code = '201810'; set @city_codes = 'BJS,SHS,GUANGZS,SHENZS'; DROP ...
  • Excel之用Sumifs完成多条件多列求和

    千次阅读 2020-12-29 16:35:01
    由于这是多条件求和,所以要用到sumifs函数,又由于是多列求和,所以也要用到offset函数。 函数组合应用 =SUM(SUMIFS(OFFSET(C1:C25,,{0,1,2,3}),B1:B25,J5,G1:G25,"<>经理")) 这个应用的关键是offset函数...
  • 数据库表就是一个多行多列的表格。在创建表时,需要指定表的列数,以及列名称,列类型等信息。而不用指定表格的行数,行数是没有上限的。下面是tab_student表的结构: 当把表格创建好了之后,就可以向表格中添加...
  • 由于这里一个人只对应一科目的成绩,所有你用max和sum是一样的,如果一个人一个科目有个成绩,这时sum就是求和的意思了,而max就是取最大值,我就不在写案例了,自己注意下就ok 第二中展示(显示在同一行...
  • 查询结果变成7,第一列为名称,第二列为类别为A的Money求和,第三列为B的求和,这2个都属于标志1的 4,5也是分别对A,B的Money求和,属于标志2。标志3类似。 请问如何写查询语句,请高手指点,谢谢!
  • 本文介绍的是python中pandas.DataFrame对行与列求和及添加新行与列的相关资料,下面话不说,来看看详细的介绍吧。 方法如下: 导入模块: from pandas import DataFrame import pandas as pd import numpy as np ...
  • 大家都知道Excel有一个用函数∑自动求和功能,但却只能是选定一整行或一整列才行,如果我想把一整行中每间隔一个单元格的数字相加求和除了逐个选定每个单元格相加有什么方法? ...对奇数列求和:=SUM
  • (学习更翻到本文最后)目 录技巧1、单元格内强制换行技巧2、锁定标题行技巧3、打印标题行技巧4、查找重复值技巧5、删除重复值技巧6、快速输入对号√技巧7、万元显示技巧8、隐藏0值技巧9、隐藏单元格所有值。...
  • 需求将下列表格相同id的name拼接起来输出成一 id Name 1 peter 1 lily 2 john 转化后效果: , id Name 1 peter;lily 2 john; 实现方式使用 ...
  • ORACLE 求和多列

    千次阅读 2017-11-06 09:14:00
    SELECT SUM(列名),SUM(列名),SUM(列名),SUM(列名) FROM 表名 转载于:https://www.cnblogs.com/luvian/p/7791620.html
  • 注意点: 1. <el> 中 加:summary-method=”getSummaries” 2. <el> 中要加 prop // 房间号的合计去掉 getSummaries (param) { const { columns, data } = param const sums = [] columns.forEach((column, ...
  • 我测试时 当我的DataGrid 的行数不时候 一切正常,当行数一旦十多行的时候,有时候大概二十多行也会出错! 请问为什么?怎么改善。我的目的就是想得到某一的数据,之后求和或其他操作。[img=...
  • pandas一拆分成多行

    2020-12-09 16:08:44
     女神的需求其实很简单,就是将exp_job字段的期望岗位打散,然后求和计数,最后就可以制作岗位的词云图了。  李有为一看,立马就知道这个问题应该怎么解决了。 一:老方法(新方法可以直接看二) 1、去除多余的...
  • 一个文件,两列,多行。 第一是字母,第二是数字,同数据之间通过空格分割。 统计首字母相同的第二之和。 样例输入: A 5 B 6 OO 7 A 6 A 2 OO 2 输出: A:13 B:6 OO:9 面试官要求30分钟内实现,python,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,889
精华内容 3,955
关键字:

多行多列求和