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

    展开全文
  • 求和的方法是: cell = row.createCell(i);//建完表之后,创建需要列号 i为第几列,row为第几行 String colString = CellReference.convertNumToColString(i); //将当前行长度转成ABC列 String sumstring = "SUM(...

    求和的方法是:

    cell = row.createCell(i);//建完表之后,创建需要列号 i为第几列,row为第几行
    String colString = CellReference.convertNumToColString(i);  //将当前行长度转成ABC列
    String sumstring = "SUM(" + colString + "2:" + colString + rowLength + ")";//求和公式
    //该求和公式的意思就是:sum(第几列+第几个 : 第几列+需要累计到的第几个单元格位置)
    sheet.getRow(rowLength).getCell(i).setCellFormula(sumstring);
    //不需要给该指定的单元格赋值,写入上面行,导出时自动会合计

    提醒:合计行需要指定样式为数值样式,如果是文本样式,无法自动合计

    HSSFCellStyle tableStyleCenter = workbook.createCellStyle();
    
    tableStyleCenter.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));
    //上面行的意思是设置该行样式为整数的数值

    如果多行需要用到求和公式,可参考以下方法:

    if (columnLength > 2) {
        for (int i = 2; i < columnLength; i++) {
            cell = row.createCell(i);//设置公式前,一定要先建立表格
            String colString = CellReference.convertNumToColString(i);  //长度转成ABC列
            String sumstring = "SUM(" + colString + "2:" + colString + rowLength + ")";//求和公式
            sheet.getRow(rowLength).getCell(i).setCellFormula(sumstring);
        }
    }


     

    展开全文
  • 例举 : A1行是 123 A2行是 321 A3行是 456 A4行是 789我是加的是A1+A3得出的和还有加的是A2+A4得出的和因为要A1+A3一直加到A601,我用很笨的方式像这样子一个个加过来,所以EXCEL公式太复杂,加不出来,我只能在...
    例举 : A1行是 123   A2行是 321  A3行是 456  A4行是 789
    我是加的是A1+A3得出的和
    还有加的是A2+A4得出的和
    因为要A1+A3一直加到A601,我用很笨的方式像这样子一个个加过来,所以EXCEL说公式太复杂,加不出来,我只能在自己敲计算器加。
    请求大家看看有没有简单的公式可以让偶数、奇数分开求和。

    =sumproduct(mod(row(a1:a601),2)*a1:a601)

    =sumproduct((1-mod(row(a1:a601),2))*a1:a601)

    奇数行相加:
    =SUM(IF(MOD(ROW(A1:A601),2)=1,A1:A601))
    上式为数组公式,按ctrl+shift+enter三键结束。

    偶数行相加:
    =SUM(IF(MOD(ROW(A1:A601),2)=0,A1:A601))
    上式也为数组公式,按ctrl+shift+enter三键结束。
    展开全文
  • 他们每一个都各自专长:SUM——单行、单列、多行、多列,无条件求和;SUMIF——行、列,一个条件求和;SUMIFS——行、列,多条件求和。根据上表可知,常用的三个求和函数,均无法满足所有的求和要求。是否有一个函数,...

    在OFFICE官方函数列表中,常用的求和函数有三个:SUM,SUMIF,SUMIFS。

    他们每一个都各自专长:

    SUM——单行、单列、多行、多列,无条件求和;

    SUMIF——行、列,一个条件求和;

    SUMIFS——行、列,多条件求和。

    157335982_1_20190326095757896

    根据上表可知,常用的三个求和函数,均无法满足所有的求和要求。

    是否有一个函数,可以满足所有的求和要求?

    目前OFFICE最新版本为OFFICE 2019,尽管有新增函数,但并没有包含与求和有关的项目。

    157335982_2_2019032609575821

    那么有没有函数组合,满足多条件、多行或多列求和呢?

    曾经在《如何快速解决多条件汇总难题——Excel中的三个简单方法》中,提到过数学集合的概念,并将其应用于多条件求和。

    既然集合可用于多条件,那么也将其用于多行或多列求和。

    实际上多行或多列,在Excel中称之为"区域"。因此只要能够使函数组合识别到数据"区域",就可以进行计算。

    与区域计算相关的函数常用的有两个,一个是SUMPRODUCT,一个是OFFSET。

    下面就以统计最近一届奥运奖牌榜中国的金牌及银牌的总数为案例分别说明两个方法。

    157335982_3_20190326095758177

    方法一:SUMPRODUCT数组计算实现区域多条件求和

    157335982_4_20190326095758287

    观察SUMPRODUCT的参数是数据区域1、2……255。因此,可直接进行多区域计算,而计算条件,可作为区域参数即可,两者用符号"*"连接。

    根据奖牌榜统计要求,包含一个求和区域及两个条件:

    求和区域为"金牌"E、"银牌"F两列;

    条件1是"最近一届",也就是在年度A列选择最大的年份,这里使用MAX函数即可找到;

    条件2是国家为"中国"。

    将以上条件分别填入函数SUMPRODUCT中,公式为:

    SUMPRODUCT((E2:F10)*(A2:A10=MAX(A2:A10))*(D2:D10="中国"))=44

    157335982_5_20190326095758396表哥TIPS:

    SUMPRODUCT看似很好用,但由于参数个数的限制,最多不能超过255个,使用时需要注意。

    方法二: OFFSET确定求和区域,加上SUMIFS多条件的特点。

    157335982_6_20190326095758506

    根据Excel官方说明中,OFFSET既可以返回一个单元格,也可以返回一片数据区域。其中前三个参数是必需项目。表哥Tips:

    若第2、3两个参数为0,则可为空,但逗号不可省略。

    同样以统计最近一届咱国家收获金牌和银牌数为例。这次我们使用

    OFFSET来定义区域。

    157335982_7_20190326095803677

    由于所选区域为E、F两行,所以要用到数学中集合的概念。公式为:

    OFFSET(E2:E10,,{0,1})

    157335982_8_20190326095803959

    公式中第三个参数的含义是,向右移动的列数。OFFSET中引入了集合{0,1},则可理解为此公式包含两个区域:OFFSET(E2:E10,,0)和OFFSET(E2:E10,,0)。

    确定了求和数据区域后,

    结合SUMIFS函数的多条件的特点,再公式最外层加上SUM以进行集合运算。组合公式为:

    SUM(SUMIFS(OFFSET(E2:E10,,{0,1}),A2:A10,MAX(A2:A10),D2:D10,"中国"))

    157335982_9_20190326095804131

    157335982_9_20190326095804131表哥Tips:

    (1) 注意确认OFFSET返回区域是否在系统界内,超出系统极限值,会报错;

    (2) SUMIFS的最多条件数是127,注意设置条件数在此范围内。

    以上就是多行多列多条件求和的两种常用方法。

    你学会了吗?

    展开全文
  • 微信扫码观看《财务人的Excel速成视频》1返回业绩最高季度 下面的Excel表是每个员工1-4季度的业绩。现在需要在F列统计每个人业绩最高的季度。F2单元格公式为:=LOOKUP(1,0/(MAX(B2:E2)=B2:E2),$B$1:$E$1)公式解析:...
  • 示例数据:以按第一列...实现公式为: ① 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("
  • 大家在日常工作中经常会遇到多列数据乘积后求和的问题,如图1所示,计算“数列1x数列2”后的总和,甚至需要计算“数列1x数列2x数列3”后的总和。图1我们一般会采用常规方法:先计算A2xB2xC2、A3xB3xC3……,在计算...
  • excel sumifs()对多列求和

    千次阅读 2020-07-31 09:44:36
    如上表,我们要求班组为"甲(白)...公式:=SUM(SUMIFS(OFFSET(E:E,,{1,2,3,4,5}),B:B,"甲(白)",E:E,"<>0")) 参考链接:https://zhidao.baidu.com/question/423955275403435652.html?qbl=relate_question_1 ...
  • 今天ytkah得到一份数据,要求进行统计分析,由于是原始数据,还没处理过,数据量有点大,如下图所示(Excel每隔两行自动求和),每天的数字由两项组成,男生的人数、消费值和女生的人数和消费值,数字都在变,要统计...
  • 隔行求和公式设置 隔列将相同项目进行求和 隔行或隔列加总 请问如何在一百行内做隔行相加 如何将间隔一定的列的数据相加呢 隔列求和(A、B列) 隔列求和的公式 隔列求和 关于隔行、隔列求和的问题 EXCEL中求两列的...
  • 如下图,是某实验仪器导出的数据,现在我们想要对B列数据每隔4行求和。即求B2:B5之和,然后求B6:B9数据之和…… 如下图,在C2单元格输入公式=SUM(OFFSET(B$2,(ROW(A1)-1)*4,,4)),即可计算B2:B5数据之和 ...
  • 如何求满足条件的行及列的和(支持合并单元格)---Excel 多条件行、条件列对应的和公式 地址:http://www.excelfb.com/ 点击:Excel自动写公式工具(根据汉字描述写公式,支持合并单元格公式) 点击:汇总满足...
  • excel中sumifs公式多列、或者区域求和1、首先在格中输入:=sumifs——回车——点击上面的【插入】标志:fx。2、求和区域于求和计算的数据区域选择要计算的数据区域。3、区域1:第一个条件判断所处的区域选择条件所处...
  • EXCEL表格中有很多项,如何对指定的部分项目进行求和?1、打开Excel表格好要求和的数据。2、列好数,输入公式=英语 语文 数学(指)。3、输入公式==E5 H5 I5后,回车就可以得到项的求和数据了。4、得到指定项求和数据...
  • 微信扫码观看《财务人的Excel速成视频》excel累加求和在我们时间工作中非常常用。我们看下面的案例,B列是当日营业额,要求在C列计算出累加营业额。比如7月2日的累加营业额就是:7月1日和7月2日的营业额之和。...
  • 学完知识兔Excel教程后,就是在工作学习中实战多练,必须多练,活学活用!不用等于没学!知识兔上有大量视频教程课程,都是精品中的精品,一套优秀课程教解锁一门技能,轻轻松松上车学习啦。重点来啦!怎么领取?...
  • 本篇文章是从海量excel技巧中,精选出的最贴近实用的技巧,共36个,重新配图配文。希望对同学们能有帮助。(学习更多翻到本文最后)目 录技巧1、单元格内强制换行技巧2、锁定标题行技巧3、打印标题行技巧4、查找重复...
  • 篇一:Excel电子表格中的函数怎么计算乘法公式Excel电子表格中的函数怎么计算乘法公式(1)在需要显示结果的单元格点一下,输入=输入要计算的单元格例如:在c3中输入“=”再输入“a3*b3” ,敲回车(不包括引号“”,...
  • 方法一、使用“+”加号求和这种方法是最简单也是最常用的一种方法,适合新手菜鸟朋友,直接在E3单元格输入“=”,然后光标点击需要...方法二、利用Excel中自带求和公式选中某行或者某列需要进行求和的数据后,点击【...
  • EXCEL函数公式

    热门讨论 2010-03-16 03:26:38
    隔行求和公式设置 隔列将相同项目进行求和 隔行或隔列加总 请问如何在一百行内做隔行相加 如何将间隔一定的列的数据相加呢 隔列求和(A、B列) 隔列求和的公式 隔列求和 关于隔行、隔列求和的问题 EXCEL中求两列的...
  • Excel常用公式与函数总结(Excel 2010)

    千次阅读 2019-02-10 21:53:56
     在Excel中输入函数时是不区分大小写的,无论是大写还是小写,结束函数编辑时,Excel会自动转化为大写。对于函数中的参数,当参数是文本时,要用双引号引起来,单纯的数字不需要加双引号,除非这个数字是文本性数字...
  • 减法:IMSUB--为减数(一般减法,直接用-符号,举例:=A1-B1)如图:EXCEL如何设置整列的有加法有减法公式可以用SUM 函数公式。1、以excel2010版本为例,首先excel表格,输入如图所示的金额数据;2、现需要把该列金额...
  • 多个合并单元格求和

    2019-10-26 11:46:27
    上图表格中如何计算每位学员的总分?图中共4位学员,求和的话很简单,一个个相加就可以实现了. ...这里我们运用一个公式来实现它:=sum()-sum() 第一步:鼠标选中D2-D17列(如下图) 第二步:在编辑栏输入 =SUM(C2:$C$1...
  • 本文转载自公众号:Excel完美,原创作者:佛山小老鼠,本文著作权归原创作者所有,...想要的效果:按姓名和月份对数量进行求和一、方法1:sumifs实现这个函数要2007版本及以及版本才有这个函数公式=SUMIFS($C:$C,$A:$...
  • 例:用公式计算单元格背景色为浅蓝色的数字之和 步骤一: Office 2003 Insert->Name->Define,Names in workbook输入getColor或getBGColor等标识词汇,Refers to输入公式=GET.CELL(63,$A1),这里数字...
  • 使用公式时碰到了一个问题,那就是公式明明已经编辑好了,但是在单元格里不生效,直接把公式显示出来了,网上资料说有4种原因,但是我4种都不是,是第5种原因,如下图: 这种情况是由于单元格格式不是常规导致的,...
  • Excel中可能我们碰到的最多的就是数据求和处理,对于绝大多数人来讲,学习Excel...场景1:单一行数正常数据求和问题:求出每个产品上半年销售总量函数=SUM(B2:G2)场景2:多行多列数据求和问题:求出上半年所有产品的...
  • 我们日常学习和工作中都会或多或少接触到数据,这些数据相对比较简单,不需要使用复杂的分析工具来处理,使用的最多的是Excel表格。我们使用复制和粘贴功能是最多的,但Excel表格还有更多更好的功能等待着被挖掘,...
  • 日常工作中,我们经常要用到多条件求和,方法有多种,第一类:使用基本功能来实现。主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用...
  • 我们已经在前面详细解释了Excel求和和查找. 如何使用差异公式. 那么,我们如何使用公式将一些数据相乘呢?如何快速方便地计算结果?下面的单词联盟将逐步教您如何使用Excel乘法公式!让我们简短地开始吧!首先,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 665
精华内容 266
关键字:

多行求和公式excel