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

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

    展开全文
  • 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求和。

    结果

    在这里插入图片描述

    展开全文
  • 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")) 参考链接:...

    如上表,我们要求班组为"甲(白)",产量"<>0"的F0,F1,F2,F3,F4的和。

    公式:=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

    https://zhidao.baidu.com/question/1497728456744827379.html?sort=11&rn=5&pn=0#wgt-answers

    展开全文
  • 说,直接上干货! 简述:将L、M和N,相加放到O。(当然这里是相邻的)。同时,也可以求相隔几列的某些相加。 本文转自大数据躺过的坑博客园博客,原文链接:...
  • (学习更翻到本文最后)目 录技巧1、单元格内强制换行技巧2、锁定标题行技巧3、打印标题行技巧4、查找重复值技巧5、删除重复值技巧6、快速输入对号√技巧7、万元显示技巧8、隐藏0值技巧9、隐藏单元格所有值。...
  • 我们看下面的案例,B是当日营业额,要求在C计算出累加营业额。比如7月2日的累加营业额就是:7月1日和7月2日的营业额之和。以此类推,累计求和。excel累计求和,可以套用这样的格式:=SUM(开始单元格:结束单元格)...
  • 今天被提问,SUMIF的统计结果不正确,数据如下: 使用SUMIF时,公式如下: ...经过百度搜索,发现公式SUMIF条件区域行列数必须与求和区域一致 修改公式为:SUMPRODUCT((H5:H8=">200k")*(E5:G8)) 统计结果
  • 现在需要在F统计每个人业绩最高的季度。F2单元格公式为:=LOOKUP(1,0/(MAX(B2:E2)=B2:E2),$B$1:$E$1)公式解析:通过MAX函数返回1-4季度最大值,然后通过LOOKUP函数结合二分法统计最大值对应季度。关于LOOKUP函数的...
  • Excel“自动求和”批处理功能(转) 在表格中,许多时候大家需要对行或列进行求和运算,如果同时有多行多列需要求和,该怎样实现求和运算的简化操作呢?以下,我们以图1所示表格为例,使用最为常见的软件,为大家...
  • 数据合并计算,顾名思义,就是能将区域的数据合并,可以是跨表,合并之后进行加减乘除计算,这就是本文为大家讲解的WPS表格的数据合并计算功能。下面以图文的形式将步骤演示下,感兴趣的朋友可以借此机会学习下...
  • access dsum条件 DSUM和Excel表:具有条件的总和 (DSUM and Excel Tables: Sum With Multiple Criteria) If you need to get a total in Excel, based on criteria, there are a few different ways that you ...
  • Excel基本技巧

    2019-09-25 21:17:25
    3、多行多列 2、常用快捷键 ctrl 选中多个不重复的区域 shift 开始,结尾,shift键 选中开始、结尾及中间的区域 ctrl+enter 批量输入数据 alt+= 快速多组求和 选中求和的单元格,运用快捷键 ...
  • 技巧 26 :删除空白行 选取A列区域 - ctrl+g打开定位窗口 - 空值 - 删除整行 技巧27:表格只能填写不能修改 操作步骤 步骤1:按ctrl键,选取所有黄色的区域,然后按ctrl+1(数字1)打开“单元格格式”窗口,在...
  • 由于不必须按顺序存储,链表在插入的时候可以达到O(1)的复杂度,比另一种线性表顺序表快得,但是查找一个节点或者访问特定编号的节点则需要O(n)的时间,而线性表和顺序表相应的时间复杂度分别是O(logn)和O(1)。...
  • 超硬核!小白读了这篇文章,就能在算法圈混了

    万次阅读 多人点赞 2021-03-29 10:21:58
    1.2位运算 关于位运算有很天秀的技巧,这里举一个例子。 给定一个非空整数数组,除了某个元素只出现一次以外,其余每个元素均出现两次。找出那个只出现了一次的元素。 说明:你的算法应该具有线性时间复杂度。 你...
  • 【DBGridEh(增强型表格组件)功能详解】 DBGridEh组件无论在外观上还是功能上...● 为多列标题设定共同的父标题行. ● 表格底部(Footer) 区显示求和、计数和其它统计信息. ● 自动调整组件宽度与客户区域等宽.
  • 2021-01-15

    2021-01-15 18:32:30
    创建表格,插入与删除一行一列或多行多行,一次移动一行一列或多行多列,拆分与合并单元格,单元格内换行,表格求和与求平均值是Excel表格的基本操作;除此之外,Excel表格的基本操作还包括调整行高列宽、单元格样式...
  • DBGridEh(增强型表格组件)功能详解

    千次阅读 2005-05-03 14:41:00
    ● 为多列标题设定共同的父标题行.● 表格底部(Footer) 区显示求和、计数和其它统计信息.● 自动调整组件宽度与客户区域等宽.● 设置标题行、数据行的高度.● 超长的标题行、数据行文本自动折行处理
  • 一键对多行多列求和 6. 效率小贴士:快速对区域小计汇总金额 第十一章:财务人员必会函数之逻辑判断函数 1. 基础工资表:职工类别自动判断录入 2. IF函数多层嵌套使用 3. 利用IF函数进行区间判断调整基本工资 4. ...
  • 从真实世界中获取数字图像有很方法,比如数码相机、扫描仪、CT或者磁共振成像。无论哪种方法,我们(人类)看到的是图像,而让数字设备来“看“的时候,则是在记录图像中的每一个点的数值。 比如上面的图像,在标...
  • 通过数据库导出交易数据时,经常是一些没有经过处理的交易原始数据格式,如下图: 然而我们希望DBA能够哪怕写一条语句把记录上述记录写成如下的格式: 那么如果DBA不能满足...列表区域选中A1:B14,条件区域...
  • EXCEL基础篇(二)

    2019-09-26 19:41:45
    本章主要内容 一、单元格操作 二、插入批注 三、自动求和 四、填充序列 五、查找、替换 六、对齐方式 ...b、插入单元行/ ...c、插入多行单元行/ 选中多行---右击插入----即可插入多行/ ...
  • 这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单表数据的统计比多表统计的速度完全是两个概念....数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更的语句,
  • 查询行时需要用到 loc 方法,比如查询日期为 2018-01-01 时,使用以下代码: df.loc['2018-01-01'] 2.1.3 检索多行多列数据 当需要选择多列数据时,需要在列表中传递一个列表,例如: 当查询多行时就得使用列表中...
  • 几百个工作薄、几千个工作表,要汇总?怎么办?复制、粘贴……?搞死人,而且易出错!表合并,一键帮你搞定! ——Mergebooks.dll
  • HBase作为数据库最经常被人诟病的特性包括:无法轻易建立“二级索引”,难以执行求和、计数、排序等操作。比如,在旧版本(&lt;0.92)的Hbase中,统计数据表的总行数,需要使用Counter方法,执行一次MapReduce ...

空空如也

空空如也

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

多行多列区域多条件求和