精华内容
下载资源
问答
  • ![图片说明](https://img-ask.csdn.net/upload/202001/02/1577945994_526606.png) 主要是这两个数据一样的情况下
  • 在 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 的第一篇博客,见笑见笑。

    展开全文
  • 以4N行为例,转换结果如下,上传Excel。

    以4列N行为例,转换结果如下,上传Excel。excel应用实例
    在这里插入图片描述

    展开全文
  • 1.学习记录,简单可用很不错! 1、创建测试表, create table test_fee(userid number, ...2、插入测试数据 insert into test_fee values(1,1001,80); insert into test_fee values(1,1002,70); insert into test_fee

    https://zhidao.baidu.com/question/871893841426870532.html

    1.学习记录,简单可用很不错!

    1、创建测试表,

    create table test_fee(userid number, feeid number, fee number);

    2、插入测试数据

    insert into test_fee values(1,1001,80);

    insert into test_fee values(1,1002,70);

    insert into test_fee values(1,1003,90);

    insert into test_fee values(1,1004,60);

    insert into test_fee values(2,1001,99);

    insert into test_fee values(2,1002,66);

    insert into test_fee values(2,1001,55);

    3、查询表中所有记录,select t.*, rowid from test_fee t,

    在这里插入图片描述

    4、编写sql,按userid汇总,根据不同的feeid,进行行转列汇总,

    select userid,

       sum(case when feeid = 1001 then fee else 0 end) as fee_1001,
    
       sum(case when feeid = 1002 then fee else 0 end) as fee_1002,
    
       sum(case when feeid = 1003 then fee else 0 end) as fee_1003,
    
       sum(case when feeid = 1004 then fee else 0 end) as fee_1004
    

    from test_fee t

    group by userid
    在这里插入图片描述

    2.改写自己的sql进行应用记录

    SELECT
            * 
    FROM
            IA_A_SWLLRKTB 
    where CAST ( CXQ AS  int ) < 50
    ORDER BY
            ADCD,CAST ( CXQ AS  int ) ASC
    
    --多行多列转一行多列 
    SELECT DISTINCT a.CZZF,a.CZZW,a.ADCD,A.HECD,c.RVCD, WSCD,Q_5,Q_10, Q_20, Q_50,Q_100 from HSFX_CZZ a left JOIN
    SELECT DISTINCT a.CZZF,a.CZZW,a.ADCD,A.HECD,c.RVCD, WSCD,Q_5,Q_10, Q_20, Q_50,Q_100 from HSFX_CZZ a left JOIN
    (select ADCD, WSCD,
           sum(case when CXQ = 5 then Q else 0 end) as Q_5,
           sum(case when CXQ = 10 then Q else 0 end) as Q_10,
           sum(case when CXQ = 20 then Q else 0 end) as Q_20,
           sum(case when CXQ = 50 then Q else 0 end) as Q_50,
                             sum(case when CXQ = 100 then Q else 0 end) as Q_100
     from IA_A_SWLLRKTB t
    group by ADCD,WSCD) b
    on a.adcd = b.adcd
    left JOIN HSFX_RIVL c ON b.WSCD = c.BWSCD  COLLATE  Chinese_PRC_CS_AS
    

    实现前:在这里插入图片描述

    实现后:在这里插入图片描述

    展开全文
  • mysql将多行变成多列的方法:首先在原表中查询数据;然后将多行数据合并到同一列或不同列即可,如【select name ,group_concat(sore Separator ';') as score from stu group by name】。MySQL中的行列变换(视频教程...

    mysql将多行变成多列的方法:首先在原表中查询数据;然后将多行数据合并到同一列或不同列即可,如【select name ,group_concat(sore Separator ';') as score from stu group by name】。

    149c47afb45b201c7a2839973a397e74.png

    MySQL中的行列变换

    (视频教程推荐:mysql视频教程)

    mysql 行列转换 ,在项目中应用的极其频繁,尤其是一些金融项目里的报表。其中最为头痛的就是多行转多列,动态的列行转换。最近在研究这些行里转换,还是从最为简单的行列转换开始。

    首先示例数据原表如下:

    54de01d280b90990d4cb9fade1e44651.png

    【多行变一列】(合并后的数据在同一列上):select name ,group_concat(sore Separator ';') as score from stu group by name

    d7bfe7f612bbbf9e4ada65c2db053af8.png

    【多行变多列】(合并后的数据在不同列上):SELECT name ,

    MAX(CASE type WHEN '数学' THEN score ELSE 0 END ) math,

    MAX(CASE type WHEN '英语' THEN score ELSE 0 END ) English ,

    MAX(CASE type WHEN '语文' THEN score ELSE 0 END ) Chinese

    FROM stu

    GROUP BY name

    a0781c484161a10e6d91642525f5ecfc.png

    展开全文
  • 可以将原表中的数据理解为超市找中的不同产品在不同的日期的销售额,其实在真实的超市中有很都是这样做的,将每个月有多少个日期就写多少, 这样做的好处是可以通过二维的坐标快速找到产品在某个日期的销售额,...
  • 增加Excel的功能,也给我们在处理多样化的数据源的过程中,提供了很大的方便,不过我们也看到了,在之前写入数据的过程中,一次只能写入TXT的一行数据,如果我们恰好是希望将多行txt数据写入到Excel的一行呢?...
  • 多列单元格中的多个数据拆分为多行数据(explode方法扩展使用)1 业务需求2 发现问题3 解决问题实际操作 1 业务需求 在经过昨天梳理通过日期字段提取年月日、timedelta提取时分秒并进行小时汇总后,今天发现业务...
  • hive多行多列

    千次阅读 2019-06-09 18:46:10
    需要将多行数据,按照某个维度转换为一行。 转换前: 转换后: db_price : area_name 为 东北 的total_price_actual; db_qty : area_name 为 东北 的qty。 二、解决方案: 1、SQL select brand, max(case ...
  • 记录一个在oracle中多行转多列的函数,有一个项目近期到了收尾阶段,忽然客户说有一个成本分摊的统计表逻辑要调整,我一看需求瞬间头大了,其中比较麻烦的一段逻辑就是需要把单行的多列数据分割成多行,百度了一下...
  • 他们每一个都各自专长:SUM——单行、单列、多行多列,无条件求和;SUMIF——行、列,一个条件求和;SUMIFS——行、列,多条件求和。根据上表可知,常用的三个求和函数,均无法满足所有的求和要求。是否有一个函数,...
  • 这就要用到跨多行多列的表格,本节就开始讲解如何实现把多个单元格合并成一个单元格。网页学习网提示:html语言非常简单,不需要逻辑理解,而绝大部分朋友觉得它难以掌握,90%的原因在于英语单词不过关,所以每节...
  • 修改前:1列多行数据 修改后:合并成一 示例语句 1 2 3 4 5 6 7 8 9 10 11 select 类别, 名称=( stuff( (select','+名称fromTable_Awhere类别=A.类别forxmlpath...
  • 使用python办公自动化处理紫外数据-xlwt快速实现将一行转为多行多列
  • 背景 题目比较抽象,具体解释一下。 有这么一张表,里面是位客户在不同时间的不同状态。例如: 客户 时间 状态 小老鼠 20200428 高兴 小八戒 20200429 开心 ...这里多行数据比较混...
  • 有关多列条件求和对30列数据条件求和的快速SUMIF公式时间:2017-10-18作者:snow来源:Office之家今天和新手们分享有关条件求和的内容,高手请忽略。先来看一组销售数据,是某商场不同品牌电视机的三天销售记录:现在...
  • 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 ...
  • 使用GridView展示数据,经常会遇到合并单元格的情况。首先说明一下项目中合并单元格的要求,如下图所示,...从图中可以看到GridView一共有“等级”、“颜色”,“箱子”,“净重”,“规格”,“汇总”6,首先要要
  • 10-多行多列

    2021-02-06 15:36:05
    可以将原表中的数据理解为超市找中的不同产品在不同的日期的销售额,其实在真实的超市中有很都是这样做的,将每个月有多少个日期就写多少, 这样做的好处是可以通过二维的坐标快速找到产品在某个日期的销售额,...
  • // 有多少,就依次向上增加 var arr=["b","c","d","e","f","g","h","i","j","k","l","m"]; for(i=0; i;i++){ tdt = 0; $("span[id^=td_"+arr[i]+"]").each(function(){ tdt = tdt + parseInt($(this)....
  • '用途,将同客户合同号同样的模具号 的不同颜色合并到一个字段里合并前的数据'客合同 款号 颜色 数量'A 9001 黑色 100'A 9001 白色 200'A 9001 红色 300'''不用ADO 或DAO ,用查询,大家有什么比较好的办法 显示为:'...
  • SQL将一列多行数据合并

    千次阅读 2016-11-29 13:54:18
    l两个方法组合就可以将多行合并 具体的SQL: SELECT DISTINCT  VisitID ,  STUFF(( SELECT ',' + DiagDesc  FROM dbo.Pat_Diagnose  WHERE VisitID = a.VisitID  FOR  XML ...
  • 展开全部1、创建测试表,create table test_fee(userid number, feeid...2、插入测试数据insert into test_fee values(1,1001,80);insert into test_fee values(1,1002,70);insert into test_fee values(1,1003,90);...
  • excel表格多列数据中查找重复行数据

    千次阅读 2020-07-22 15:08:35
    1、做透视表统计 2、设置显示格式 3、取消分类汇总 4、拷贝到原数据后面 5、排序
  • 如何用pandas同时实现以下分类汇总:合并相同“名称”,“金额”求和,“利率”取均值,日期、类型、主体跟随“名称”合并。 尝试了一些办法,groupby似乎不能同时既求和又求均值&#...
  • 如上图,行数是不确定的
  • 但是sumproduct是在C:R十六中查找一个人的名字,把匹配到的行后面的U的金额相加,且存在一个问题,如果这十六中的某一行,出现个查找人的名字,那么这一行后面的数值会被累加次 很明显下面的数据想要...
  • 原表数据: 期望结果: 使用STUFF +FOR XML PATH即可实现以上效果 执行以下SQL: SELECT DISTINCT Name, STUFF((SELECT ',' + Course FROM Student WHERE Name = T.Name FOR XML PATH('')), 1, 1, '') AS ...
  • 第1行,辅助行,模拟了每个数据cell对应的号 第2行,辅助行,模拟了每个数据cell对应的行号 第3行,输出行 ,利用R1C1G格式,因为明显可以看出来,行号,号的变化时有规律的 =INDIRECT("r"&G2+2&"c...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,634
精华内容 4,253
关键字:

多行多列数据如何汇总