精华内容
下载资源
问答
  • 在 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 的第一篇博客,见笑见笑。

    展开全文
  • 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函数...

    工作场景

    有这样一份作业,统计财务部中,级别不为经理的各项社保扣款合计。

    由于这是多条件求和,所以要用到sumifs函数,又由于是多列求和,所以也要用到offset函数。

    在这里插入图片描述

    函数组合应用

    =SUM(SUMIFS(OFFSET(C1:C25,,{0,1,2,3}),B1:B25,J5,G1:G25,"<>经理"))
    

    这个应用的关键是offset函数。在这个函数组合应用中,C1:C25为reference列,{ }里面的数字,指的是以reference列为基准,向右偏移0列(自身)、1列(D列)、2列(E列)、3列(F列)。带{ }为数组条件,所以要在sumifs函数外面再套一层sum求和。

    结果

    在这里插入图片描述

    展开全文
  • pandas 满足多条件的行的某列求和

    千次阅读 2020-05-27 23:37:06
    import numpy as np ...# train_msg = train_msg.sort_values(by='user_id', ascending=True) # 按照特定排序,如果没有重新赋值,原数据不会改变 # train_msg_train = train_msg[train_msg.use.

    import numpy as np
    import pandas as pd
    import csv
    import random
    # train_msg = pd.read_csv('../../txCTR/train_msg.csv')
    # train_msg = train_msg.sort_values(by='user_id', ascending=True)  # 按照特定列排序,如果没有重新赋值,原数据不会改变
    # train_msg_train = train_msg[train_msg.user_id <= 100000]    # 按照单单个条件选择行
    # train_msg_test = train_msg[train_msg.user_id > 100000] 
    # train_msg_train.to_csv('../../txCTR/train_msg_train.csv',index=False,sep=',')
    # train_msg_test.to_csv('../../txCTR/train_msg_test.csv',index=False,sep=',')

    #下面一段代码
    # simulation = pd.read_csv('../../txCTR/test/try/simulation.csv')
    # simulation = simulation[(simulation.user_id == 1) & (simulation.industry == 1) & (simulation.category == 2)]  #里面()不能丢
    # print(simulation)
    # times = simulation['click_times'].sum()  
    # print(times)

    # 下面这几行代码极其高效地统计了各性别、年龄以及industry上面的分布,统计各类gender、age、industry组合出现的次数
    # train_msg = pd.read_csv('../../txCTR/train_msg.csv')
    # # train_msg = train_msg.sort_values(by='user_id', ascending=True)
    # count = train_msg.groupby(['gender', 'age', 'industry']).size().reset_index(name="Time")  # 此处如果不为统计列重命名,
    # # 则保存的csv文件只有最后一列,且没有列标题
    # print(count)
    # count.to_csv("../../txCTR/statistics/try/industry/gender_age_industry_count.csv", index=False, sep=',')

     

    展开全文
  • 多条件求和 经管学院女生的语文成绩之和 可以输入=sumifs(语文,学院,随机选择一个经管学院,性别,随机选择一个女) 或者=sumifs(语文,学院,“经管学院”,性别,“女”) 注意:不可以选最上面...

    在这里插入图片描述

    单条件求和

    计算经管学院数学成绩之和
    可以输入=sumif(学院列,“经管学院”,数学成绩列)

    在这里插入图片描述求环境学院英语成绩之和
    可以输入=sumif(学院列,随机点一个环境学院,英语列)
    在这里插入图片描述

    多条件求和

    经管学院女生的语文成绩之和
    可以输入=sumifs(语文列,学院列,随机选择一个经管学院,性别列,随机选择一个女)
    或者=sumifs(语文列,学院列,“经管学院”,性别列,“女”)
    注意:不可以选最上面的标题

    在这里插入图片描述

    条件求平均

    averageif和averageifs(其他不变)

    环境学院女生语文成绩的平均分
    可以输入=averageifs(语文列,学院列,随机一个环境学院,性别,女)
    在这里插入图片描述
    环境学院的语文平均成绩
    可以输入=average(学院列,“环境学院”,语文列)

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

    千次阅读 2019-05-17 16:47:36
    ** SUMPRODUCT多列求和的应用,具体看图 **
  • EXCEL-SUMIFS多条件求和(筛选效果)

    千次阅读 2017-03-10 08:48:03
    EXCEL-SUMIFS多条件求和(筛选效果)
  • 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")) 参考链接:...
  • 对复核条件的元组进行求和 2.求和列数为最后四 方案1:重新组织查询条件,查询所有数据,然后对获得的数据集合进行循环遍历将后四进行叠加。 方案2:重新组织查询条件,查询所有数据,让后利用Linq的Sum方法对...
  • 1、最简单粗暴的公式,个sumif求和 2、利用sumproduct 本意是在C、H、P、Q、R五中查找一个人的名字,把匹配到的行后面U的金额相加 但是sumproduct是在C:R十六中查找一个人的名字,把匹配到的行后面...
  • EXCEL:SUMIFS多条件求和2(筛选效果),含字符串转数组用法
  • excel中多条件判断求和

    千次阅读 2014-05-27 01:54:55
     而当需要判断多列数据的条件来对另一列数据进行求和的时候SUMIF就不行了,此时可以用SUMPRODUCT函数,即数组求和函数。利用布尔量的运算来表示多列条件的同时满足。 例如: SUMPRODUCT((F4:F53="男")*(G4:G53=...
  • 列是动态的 代码执行的时候才知道要对那些列求和。 不知道用linq怎么写 现在只能通过SqlQuery查询结果 使用System.Reflection.Emit 动态创建一个对应的类型装载结果 求教大神 有没有可能通过linq实现? ``` string ...
  • 不需要了解vba、公式,支持插入文件名、sheet名列,根据汉字生成汇总 使用时自动提示 工具地址 :http://www.excelfb.com/找到下图的工具 Excel自动汇总工具(根据汉字描述的要求格式进行汇总) ...
  • 1、需要同一个产品开票数量和入库数量求和 2、 ``` select '库存合同未开票情况', a.trackopcodename as '用户名', a.stockno as '采购合同号', p.goodscname as '产品名称', p.goodscode as '产品编码...
  • 推算日期: 1.相隔90天,日期遇到天数直接相加 = 一个新的日期 原因:日期的表示在Excel中也是数字,数字1为1900年1月1日,以此类推 2.间隔整数月份处理: ...sumif的能力扩充–用于处理多列循环
  • 日常工作中,我们经常要用到多条件求和,方法有多种,第一类:使用基本功能来实现。主要有:筛选、分类汇总、数据透视...主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助(将多条件变为单条件)、使用SUMPRODU
  • 数据源是A1:B10,案例需利用Sumif和Sumifs对A、B求总和。 Sumif和Sumifs语法和解释(符号"[ ]"内内容为可选)...参数解释:SUMIF(条件区域,条件,[需求和的单元格或区域]) Sumifs语法:SUMIFS(Sum_range, criter...
  • shell-awk 按列求和

    万次阅读 2017-08-14 16:51:08
    在Shell中,我们可以用awk实现按列求和的功能,非常简单。看下面的例子: 1.简单的按列求和 [linux@test /tmp]$ cat test 123.52 125.54 126.36 [linux@test /tmp]$ ...
  • 说,直接上干货! 简述:将L、M和N,相加放到O。(当然这里是相邻的)。同时,也可以求相隔几列的某些相加。 本文转自大数据躺过的坑博客园博客,原文链接:...
  • 越来越觉得sumproduct这个函数有用,过去用sum组函数,改起来复制起来都麻烦,sumif在条件多的时候也觉得不方便。现在改用sumproduct函数,就简单多了。查过sumproduct函数的使用方法,其解释为“求二个或二个以上...
  • 求和

    2020-03-04 14:45:35
    学习笔记:2019财务会计Excel实战特训营 隔行求和 隔行出现,并且周期一定 隔列求和 条件求和 1.sumif() 快速求和 小计行求和 表求和
  • iamlaosong文 SUMPRODUCT函数的含义为在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。按我的理解就是两个以上的数组乘积之和。例如,公式为:=SUMPRODUCT(A2:A4, B2:B4...1、条件求和条件区域与求...
  • 查询某张表中批物资的库存数,并且要求库存数>0 SELECT operation_warehouse,mat_id,mat_num,specification_type,mat_type,unit,sum(stock_num) as tt,sum(balance_num), sum(occupy_num) FROM `ws_mat_stock...
  • 【VB】table表列求和(机房问题)

    千次阅读 热门讨论 2015-07-17 19:17:26
    VB查询表列求和
  • sumif 根据条件求和

    2013-09-02 16:00:26
    例如,假设在含有数字的某一中,需要对大于 5 的数值求和。请使用以下公式: =SUMIF(B2:B25,">5") 在本例中,应用条件的值即要求和的值。如果需要,可以将条件应用于某个单元格区域,但却对另一个...
  • 条件求和向导法

    2009-06-26 13:47:00
    条件求和向导法 1、打开登记表,执行“工具加载宏”命令,打开“加载宏”对话框(如图5),选中“条件求和向导”选项,按下“确定”按钮,然后按提示操作加载“条件求和向导”功能。 注意:第一次使用这个功能时,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 43,439
精华内容 17,375
关键字:

多条件多列求和