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

    展开全文
  • Update 多行数据(将一张表中某列的值更新到另外一张表中)、 MERGE INTO USING、UPDATE SET FROM INNER join on WHERE 、UPDATE INNER JOIN ON SET WHERE”
    Oracle:
    MERGE INTO PERSONTE
    USING PERSON
    ON (PERSONTE.name = PERSON.name)
    WHEN MATCHED THEN
      UPDATE
         SET PERSONTE.sex = PERSON.sex
       WHERE PERSONTE.id = '1'
         AND PERSONTE.name IN ('q', 'w');
    
    MS SqlServer:
    UPDATE t2
       set t2.name = t1.name FROM t_test2 t2 INNER join t_test1 t1 ON t1.id = t2.id
     where t1.id = '3';
    
    MySQL:
    UPDATE test1 t2
    INNER JOIN test t1 ON t1.id = t2.id
    SET t2.NAME = t1.NAME
    WHERE
        t1.id = '1';

    --存在记录时,就更新(Update),不存在数据时,就插入(Insert)
    MERGE INTO table_name alias1 
    USING (table|view|sub_query) alias2
    ON (join condition) 
    WHEN MATCHED THEN 
        UPDATE table_name 
        SET col1 = col_val1, 
            col2     = col2_val 
    WHEN NOT MATCHED THEN 
        INSERT (column_list) VALUES (column_values); 


    --数据量小的时候可以使用子查询做两表关联操作;但数据量大的时候子查询效率太低(因为是单条比对)
    --比如:
    update person1 p1 set p1.p_name=(select p_name from person2 where p1.p_id=p2.p_id)  where p1.add_date>to_date('2014-09-01','yyyy-mm-dd')  
    
    --而使用MERGE INTO...USING 作两表关联操作(增、删、改)就效率非常高
    MERGE INTO person1 p1   
            USING (select p_id,p_name from person2 where use='952701' ) p2  
            ON ( p1.p_id=p2.p_id )      
            WHEN MATCHED THEN UPDATE SET p1.p_name = p2.p_name where p1.add_date>to_date('2014-09-01','yyyy-mm-dd')  
        commit;  



    展开全文
  • 1.选中需要分析的数据列 2.插入-数据透视表 3.名称想拖入行、求和项如值,下拉倒三角设置值字段为求和 4.结果如图

    1.选中需要分析的数据列
    在这里插入图片描述

    2.插入-数据透视表
    在这里插入图片描述在这里插入图片描述
    3.名称想拖入行、求和项如值,下拉倒三角设置值字段为求和
    在这里插入图片描述
    4.结果如图
    在这里插入图片描述

    展开全文
  • 示例数据:以按第一列升序排序,俩列值已分组去重 需求: 将左列相同的值对应的右列内容合并,如 127 b 则所需数据为 : 127 ab 127 a 实现公式为: ① IF(AND(A1=A2,A2=A3),B1&B2&B3,IF(A1=A2,B1&B2,...

    示例数据:以按第一列升序排序,俩列值已分组去重
    在这里插入图片描述
    需求: 将左列相同的值对应的右列内容合并,如
    127 b 则所需数据为 : 127 ab
    127 a
    实现公式为:

    ① IF(AND(A1=A2,A2=A3),B1&B2&B3,IF(A1=A2,B1&B2,B1))
    ② IF(AND(A1=A2,A2=A3),TEXTJOIN(",",TRUE,B1:B3),IF(A1=A2,TEXTJOIN(",",TRUE,B1:B2),B1))
    ③ TEXTJOIN("",TRUE,IF($A$1:$A$32=C2,$B$1:$B$32,""))   
    

    其中公式②是合并时使用逗号分隔
    其中此公式③ 需要使用数组公式计算,结果方正确(即输入完公式后,需按下CTRL + shift + 回车 退出公式编辑)

    • 公式①②解析如下
      在这里插入图片描述
      在这里插入图片描述

    and:
    在这里插入图片描述
    & : 拼接
    if:在这里插入图片描述
    先标出重复项,便于做完之后,检查数据有没有问题:
    选中第一列,然后选择
    数据 ——> 重复项 ——> 设置高亮重复项
    如下图:
    在这里插入图片描述
    使用公式填充数据
    在这里插入图片描述
    得到如下结果

    在这里插入图片描述

    在这里插入图片描述
    公式③解析图如下:
    原表数据如下
    在这里插入图片描述
    先复制A列至C处,然后选定C列,点击 "数据” --> “重复项” --> “删除重复项” ,如下图删除重复项
    (图中示例我未先复制列,清以文字为准)
    在这里插入图片描述
    选择编号,点击删除
    在这里插入图片描述
    在这里插入图片描述
    则得到C列如下
    在这里插入图片描述
    输入公式: TEXTJOIN("",TRUE,IF($A$1:$A$32=C2,$B$1:$B$32,""))
    图中公式行数是31,是由于原来没有增加表头,请以实际情况为准,此处因是32行
    在这里插入图片描述
    按下 CTRL + shift + Enter 后得到如下:
    在这里插入图片描述
    使用多行填充得到如下:
    在这里插入图片描述

    • 这里再分享一个合并同类单元格的案例
      原表数据如下:
      在这里插入图片描述
      首先在表格左侧插入一列空白列
      在这里插入图片描述
      然后选中表格,点击分类汇总,如下
      在这里插入图片描述
      在这里插入图片描述
      得到如下图:
      在这里插入图片描述
      点击左侧① 得到如下
      在这里插入图片描述
      在表格A3处输入任意内容,然后使用智能填充
      在这里插入图片描述
      得到如下
      在这里插入图片描述
      点击左侧的③得到如下
      在这里插入图片描述
      全选表格,按下 ctrl + g 打开定位
      在这里插入图片描述
      选择定位到空值
      在这里插入图片描述
      如图,然后在开始处,点击合并单元格选项
      在这里插入图片描述
      然后全选表格,点击分类汇总,删除分类汇总
      在这里插入图片描述
      在这里插入图片描述
      得到如下
      在这里插入图片描述
      选择第一列,点击格式刷,再点击第二列,则完成
      在这里插入图片描述
      在这里插入图片描述
      完成,最终效果如图显示
      在这里插入图片描述
    展开全文
  • //隐藏掉code列 hideHeaderCol(); //存放需要计算合计的汇总行集合 var area = []; $("#yourMind").find("tbody tr").each(function (i) { ... //通过指定标识,查找需要求和汇总数据 if (_tr.childre...
  • Shell下 文件行合并 和行内数据求和 *#!/bin/bash paste .dat > f0.dat awk '{for (i=1; i<= NF; i++) sum += $i;print sum > “output.log”; sum=0; } ’ f0.dat NF变量在awk里面的意思是当前记录的字段...
  • **以下是两个for循环的文件读取的方法,第二种就比较好的体现python的方便性要求:文件 data.txt 文件中有多行数据,打开文件,读取数据,并将其转化为列表。统计读取的数据,计算每一行的总和、平均值,在屏幕上输出...
  • 分两种情况: 一种是当前页的列求和 ...代码如下:(本段代码复制于博客:EasyUI datagrid表数据某列总和) <table id="dg"></table> <script> $(function () { $('#dg').datagrid({ ...
  • Hadoop Map/Reduce编程模型实现海量数据处理—数字求和,分别用不同的方法来使用Hadoop,进而了解Hadoop在数据处理的不同处理方式
  • 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 ...
  • DB2 中SQL分组求和

    2019-11-20 23:36:35
    小海同学最近在工作中遇到一个问题,那就是对数据库中的某个字段分组,插入一行数据对上面的一组的某个字段求和,和Excel中的分类汇总类似,我将其称之为“分组求和”。uno 员工编号,name 姓名,brncoBrncd 部门 ...
  • Stream流,求和计算

    千次阅读 2020-03-06 16:11:25
    简介: Stream 是对集合(Collection)对象功能的增强,它专注于对集合对象...本篇文章将分别使用传统的foreach 和 stream流 进行集合的求和汇总运算,废话不多说,直接上代码 1、创建测试的实体类 @Data @...
  • 微信扫码观看《财务人的Excel速成视频》excel累加求和在我们时间工作中非常常用。我们看下面的案例,B列是当日营业额,要求在C列计算出累加营业额。比如7月2日的累加营业额就是:7月1日和7月2日的营业额之和。...
  • 文章目录MySQL 数据表的增删改操作Column Attributes 列属性插入数据插入单行插入多行数据复制表格更新数据更新单行更新多行删除数据汇总数据Aggregate Function 聚合函数GROUP BY 语句HAVING 语句WITH ROLLUP 语句 ...
  • 自己建立了一个数据分析兴趣小组群,在分享...有个成员提出利用SQL语言来实现一个2万行数据的分类汇总,可是俺不会SQL,就想试试用Python能否实现。首先来看看数据吧(该数据没有脱敏,大家看看就好),数据包含了5...
  • 多行函数 (聚合函数):作用于多行,返回一个值。 --这里的1表示主键那一列 select count(1) from emp;---查询总数量 select sum(sal) from emp;---工资总和 select max(sal) from emp;---最大工资 select min(sal) ...
  • 看第一种解法,常规思路:先找出单个阶乘的函数表达式后再求和: 应该不难理解第一种解法,下面尝试第二种解法????:用列表推导式简化后求解递归 是不是觉得第一和第二其实是一样的?????下面????看第三种:直接...
  • 2、再打开数据菜单,点击分类汇总,在分类汇总窗口中,分类字段选择:科目名称,汇总方式:求和,选定汇总项:借方,确定。 3、最终结果如下图,按照科目名称进行了汇总,点击左上角的数字1、2、3,可以按不同的...
  • 笔者这次是第一次写东西,主要...需求:原数据格式:我们要变成下面的样子:(这里是做了分组和求和)解决需求:#数据是直接从数据库中查出来的,所以直接导入excel的数据,也可以直接连数据进行查询。import pandas a...
  • 上回:《SQL必知必会》(7,8):创建计算字段、使用数据处理...文章目录第九课:汇总数据9.1 聚合函数第十课:分组数据第十一课:使用子查询 第九课:汇总数据 9.1 聚合函数 第十课:分组数据 第十一课:使用子查询 ...
  • 文章目录7.1 pandas 常用数据类型7.1.1 一维数组与常用操作7.1.1.1 创建 Series7.1.1.2 修改指定索引对应的值7.1.1.3 对所有数据求绝对值7.1.1.4 对所有数据加 57.1.1.5 对每行索引加前缀7.1.1.6 对每行索引加后缀...
  • 编按:Excel处理数据有自己的游戏规则,尤其是数据源表规矩森严。想Excel运行快,操作顺,就必须遵守Excel的家规。在数据处理中,那种蔑视规则的人,忽视规则的人,注定要碰得头破血流,因为Excel不!留!情!在前几...
  • sql重复行求和

    千次阅读 2017-11-27 13:40:00
    有一表内容如下(多列且为数字): a b c IV-01-002-01 2 name IV-01-002-01 2 name IV-01-002-02 1 aaa IV-01-002-02 1 aaa IV-01-002-...
  • 要查看数据与选取数据,我们首先得了解python里数据存储的方式,然后才能进行数据查看、数据选择、数据清洗、数据分析、数据建模等。。 所以本文将分为: 数据结构 常用查看/选择数据函数 函数代码案例 ...
  • 原创 : 我们在做项目中有时候会遇到这种需求需要将name 为张三的hobby合并为如下: 当然你也可以通过mysql的 group_comcat将hobby合并成一列,那个很简单,这里不做介绍先建表,插入数据SET NAMES utf8mb4; SET FOREIGN...
  • pandas数据分析常用函数总结 | 下篇5... 数据提取6.1 单行索引6.2 区域索引6.2.1 用loc取连续的多行6.2.2 用loc取不连续的多行6.2.3 用loc取具体值6.2.4 用iloc取连续的多行6.2.5 用iloc取连续的多行和多列6.2.6 用i...
  • 在关系数据库中,使用数据分组可以取得表数据汇总信息。数据分组是通过分组函数,group by以及having等子句共同实现的。 分组函数: 是多行函数,作用于一组数据,并对一组数据返回一个值。 语法结构: SELECT ...
  • 文章目录第10章 创建计算字段10.1 计算字段10.2 拼接字段10.3 执行算术运算第11章 使用数据处理函数11.1 文本处理函数11.2 日期和时间处理函数11.3 文本处理函数 第10章 创建计算字段 10.1 计算字段 字段(field)...
  • 查询部分数据:df.head() 默认前5行 查询当前数据信息:df.info() 查询数据索引信息:df.index 查询数据列名:df.columns 查询数据值:df.values 数据处理 获取某列数据:df[“列名”] 将某列作为索引:df=df.set_...
  • Pandas基础 - 数据处理

    2020-11-11 21:26:52
    pandas 基础Pandas的数据处理DataFrame 的过滤DataFrame 的缺失值判断Series 与 ...df指整个表格,df[: 1] 指选择第一行,df[1:3] 选择多行,指索引1到索引3的2行。 df[2::2] 指从df里面选择索引为2开头,步长为2

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,872
精华内容 748
关键字:

多行数据汇总求和