精华内容
下载资源
问答
  • 今天介绍一种几乎没人知道的多表多条件筛选求和公式,为了大家更好理解,下面结合案例介绍。数据源包含三张工作表,分别是北京、上海、广州,首先看一下北京工作表如下图所示。每张工作表中数据量不一定相同,但结构...

    点击右上角“关注”,每天免费获取干货教程

    职场办公中什么问题都有可能遇到,比如多表求和、筛选求和、跨表汇总......当这几种问题同时混合在一起时,你还能顺利解决吗?

    今天介绍一种几乎没人知道的多表多条件筛选求和公式,为了大家更好理解,下面结合案例介绍。

    865892ee12937b4a4d7ec623d3d4f8d3.png

    数据源包含三张工作表,分别是北京、上海、广州,首先看一下北京工作表如下图所示。

    4943fd4f0726273611c79671059d6e79.png

    每张工作表中数据量不一定相同,但结构一致,金额都在C列。上海工作表如下图所示。

    9e35468d5aff64cc40d0d2693b2a67de.png

    最后看一下广州工作表。

    62d35c884fed98b17777c1fcfcd3e83f.png

    当写好公式以后,无论后续的几张工作表如何更改筛选条件,都可以仅对显示出来的筛选结果进行多表求和,动图演示如下图所示。

    9a3cbb38c9f8a890299bf14520da4daf.gif

    有兴趣的同学可以自己尝试写下公式,再看下面给出的解决方案。

    解决方案:

    先分别按照上面动图演示的过程,把每张工作表的筛选条件截图展示给大家,最后再看公式原理解析,会更容易理解一些。

    比如在北京工作表筛选商品1,如下图所示。

    bfb86c5c28a5641e835e408c0001989e.png

    比如在上海工作表筛选商品2,如下图所示。

    f5d042284269ed80f31a7e537d6a57fc.png

    比如在广州工作表筛选商品3,如下图所示。

    508017564cba56e72d8e74294f03b008.png

    最后使用跨多工作表筛选求和公式如下:

    =SUM(SUBTOTAL(109,INDIRECT({"北京","上海","广州"}&"!c:c")))

    公式特写示意图如下:

    52bdecd0cf26f04fef5ceca0364fe2aa.png

    公式原理解析:

    先借助INDIRECT函数实现跨工作表引用,再使用SUBTOTAL函数的109参数实现仅对筛选结果求和,最后将分别对多工作表计算出来的结果{12,210,800}传递给SUM函数进行最终求和计算,SUM({12,210,800})得到想要的结果1022。

    由于所有函数都支持自动更新,所以当筛选条件变更时,公式结果自适应更新。

    这些常用的经典excel函数公式技巧可以帮你在关键时刻解决困扰,有心的人赶快收藏起来吧。

    希望这篇文章能帮到你!怕记不住可以发到朋友圈自己标记

    想系统提升Excel应用技能的同学,推荐下面这套超清视频专栏↓

    122872ee409cf96bb2ee18c2f5a34d2d.png
    展开全文
  • 日常工作中,我们经常要用到...主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODU

    http://ranran3299.blog.163.com/blog/static/38322561201191651023682/

    日常工作中,我们经常要用到多条件求和,方法有多种,第一类:使用基本功能来实现。主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel2007及以上的版本),方法千差万别、效果各有千秋。本人更喜欢用SUMPRODUCT函数。由于Excel帮助对SUMPRODUCT函数的解释太简短了,与SUMPRODUCT函数的作用相比实在不匹配,为了更好地把握该函数,特将其整理如下。
      一、 基本用法
      在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
      语法:
      SUMPRODUCT(array1,array2,array3, ...)
      Array1, array2, array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。
      公式:=SUMPRODUCT(A2:B4, C2:D4) 公式解释:两个数组的所有元素对应相乘,然后把乘积相加,即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。计算结果为156
      二、 扩展用法
      1、 使用SUMPRODUCT进行多条件计数
      语法:
      =SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))
      作用:
      统计同时满足条件1、条件2到条件n的记录的个数。
      实例:
      =SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称"))
      公式解释:
      统计性别为男性且职称为中级职称的职工的人数
      2、 使用SUMPRODUCT进行多条件求和
      语法:
      =SUMPRODUCT((条件1)*(条件2)* (条件3) *…(条件n)*某区域)
      作用:
      汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。
      实例:
      =SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*C2:C10)
      公式解释:
      统计性别为男性且职称为中级职称的职工的工资总和(假设C列为工资)
      三、 留意事项
      1、数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。
      2、SUMPRODUCT函数将非数值型的数组元素作为 0 处理。
      3、在SUMPRODUCT中,2003及以下版本不支持整列(行)引用,必须指明范围,不可在SUMPRODUCT函数使用A:A、B:B,Excel2007及以上版本可以整列(列)引用,但并不建议如此使用,公式计算速度慢。
      4、SUMPRODUCT函数不支持“*”和“?”通配符
      SUMPRODUCT函数不能象SUMIF、COUNTIF等函数一样使用“*”和“?”等通配符,要实现此功能可以用变通的方法,如使用LEFT、RIGHT、ISNUMBER(FIND())或ISNUMBER(SEARCH())等函数来实现通配符的功能。如:
      =SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*(LEFT(D2:D10,1)="龙")*C2:C10)
      =SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*((ISNUMBER(FIND("龙逸凡",D2:D10)))*C2:C10))
      注:以上公式假设D列为职工姓名。ISNUMBER(FIND())、ISNUMBER(SEARCH())作用是实现“*”的通配功能,只是前者区分大小写,后者不区分大小写。
      5、SUMPRODUCT函数多条件求和时使用“,”和“*”的区别:当拟求和的区域中无文本时两者无区别,当有文本时,使用“*”时会出错,返回错误值 #VALUE!,而使用“,”时SUMPRODUCT函数会将非数值型的数组元素作为 0 处理,故不会报错。 也就是说:
      公式1:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*C2:C10)
      公式2:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称"),C2:C10)
      当C2:C10中全为数值时,两者计算结果一样,当C2:C10中有文本时公式1会返回错误值 #VALUE!,而公式2会返回忽略文本以后的结果。
      四、 网友们的出色实例
      1、求指定区域的奇数列的数值之和
      =SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)
      2、求指定区域的偶数行的数值之和
      =SUMPRODUCT(((MOD(ROW(A1:A22),2))-1)*A1:A22)*(-1)
      3、求指定行中列号能被4整除的列的数值之和
      =SUMPRODUCT((MOD(COLUMN(A1:P1),4)=0)*A1:P1)
      4、.求某数值列前三名分数之和
      =SUMPRODUCT(LARGE(B1:B16,ROW(1:3)))
      5、统计指定区域不重复记录的个数
      =SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15))

    详情查看:excel表格的基本操作
           http://hi.baidu.com/xueexcel

    用Excel制造人为表

    Excel基础.doc

    EXCEL使用技巧大全3

    Excel数组公式从入门到精通之入门篇

    Excel办公应用常见技巧方法(集合一)

    EXCEL使用技巧大全

    Excel 2007轻松进行多条件求和

    Excel技能

    Excel2007快捷键大全(参考)

    Excel中取得合并单元格相关信息

     

    展开全文
  • 他们的格式都相近:部门,姓名,型号等等 现在是希望在“结果”这个工作表中对五个工作表的值进行汇总,而且要能按照部门和姓名进行筛选 这个问题可以通过以下公式来解决 =SUMPRODUCT((T(INDIRECT("'"&...

    有网友给我来信,提到下面这样一个问题,希望我能设法帮助解决

    工作簿中有好几个表格,例如下面的1,2,3,4,5。他们的格式都相近:部门,姓名,型号等等

    image

    现在是希望在“结果”这个工作表中对五个工作表的值进行汇总,而且要能按照部门和姓名进行筛选

    image

    这个问题可以通过以下公式来解决

    image

    =SUMPRODUCT((T(INDIRECT("'"&{1,2,3,4,5}&"'!A"&ROW($2:$5)))=$A2)*N(INDIRECT("'"&{1,2,3,4,5}&"'!C"&ROW($2:$5)))*(T(INDIRECT("'"&{1,2,3,4,5}&"'!B"&ROW($2:$5)))=$B2))

     

    好多年不怎么深入去研究Excel了,现在Excel的水准也只跟一般用户差不多,写这个公式也费了不少脑筋大汗

    转载于:https://www.cnblogs.com/chenxizhang/archive/2010/04/26/1720907.html

    展开全文
  • EXCEL函数公式

    热门讨论 2010-03-16 03:26:38
    如何求多条件的平均值 想求出第三大之数值 【查询和查找引用】 查找顺序公式 怎样实现精确查询 查找及引用 查找函数的应用 怎么能方便的判断某个单元格中包含多少个指定的字符?如何用查找函数 日期查找的问题 如何...
  • 如何求多条件的平均值 想求出第三大之数值 【查询和查找引用】 查找顺序公式 怎样实现精确查询 查找及引用 查找函数的应用 怎么能方便的判断某个单元格中包含多少个指定的字符?如何用查找函数 日期查找的问题 如何...
  •  7.5 求和公式  7.5.1 对范围内的所有单元格求和 7.5.2 计算累计和 7.5.3 求n个最大值的和 7.5.4 使用单个条件求和 7.5.5 只对负值求和 7.5.6 根据范围Difference的值求和 7.5.7 基于文本的比较结果求和 ...
  •  7.5 求和公式  7.5.1 对范围内的所有单元格求和 7.5.2 计算累计和 7.5.3 求n个最大值的和 7.5.4 使用单个条件求和 7.5.5 只对负值求和 7.5.6 根据范围Difference的值求和 7.5.7 基于文本的比较结果求和 ...
  • Excel使用攻略(1)

    2019-07-29 19:20:00
    Excel使用攻略(1)1.SUM函数(数学)基本语法:算例:累计求和SUM函数的数组公式2.IF函数(逻辑)基本语法:算例:累计求和3.VLOOKUP函数(筛选)基本语法:3.1基础单条件查找3.2反向查找3.3多条件查询3.4查询返回...

    1.SUM函数(数学)

    基本语法:

    =SUM(A1:A10)----------------------------单区域
    =SUM(A1:A10,B2:B5)-------------------多区域

    算例:

    累计求和

    在这里插入图片描述

    SUM函数的数组公式

    在这里插入图片描述
    在使用数组公式的时候,需要按Ctrl+Shift+Enter键,不能直接按Enter键

    2.IF函数(逻辑)

    基本语法:

    =IF(logical,value_if_true,value_if_false)

    算例:

    累计求和

    3.VLOOKUP函数(筛选)

    基本语法:

    =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
                              查找值         查找范围      返回第几列  匹配方式(0精确匹配,1模糊匹配)

    通俗版:=VLOOKUP(用谁找,去哪里找,找到了返回什么,怎么着)

    3.1基础单条件查找

    使用单个检索关键字,并且检索关键字在选择区域的第1列,直接使用普通公示就可以解决。

    3.2反向查找

    检索关键字必须在查找区域的第1列,反向查找的检索关键字不在查找区域的第1列,可以使用虚拟数组公式IF来做一个调换。
    在这里插入图片描述反向查找的固定公式用法:
    =VLOOKUP(检索关键字,IF({1,0},检索关键字所在列,查找值所在列),2,0)
    :所有使用了数组的公式,不能直接回车,需要使用Ctrl+Shift+Enter,否则会出错。

    3.3多条件查询

    可以利用&将字段拼接起来,并且利用IF数组公式构建出一个虚拟的区域
    多条件查找多条件查找的固定公式用法:=VLOOKUP(关键字1&关键字2,IF({1,0},序列1&序列2,查找值所在列),2,0)。

    3.4查询返回多列

    借助另外一个能返回单元格引用列数的辅助函数——column函数:
    =column(待查询单元格/区域)
    查询返回多列返回多列的固定公式用法:
    =VLOOKUP(混合引用关键字,查找范围,COLUMN(xx),0)
    返回第几列就开始引用第几列的单元格即可

    4.SUMIF函数(条件计数)

    n.其它

    绝对引用

    按F4可对选中的单元格进行引用形式的切换。
    (复制的公式具有相对引用)

    数组公式

    是Excel公式在以数组为参数时的一种应用
    数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。数组的元素可多达6500个。

    展开全文
  • 主要有:筛选、分类汇zhi总、数据透视表、多条件求和向dao导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODUCT函数、使用SUMIFS函数...
  • │ │ 技巧242 按多条件筛选记录.xls │ │ 技巧243 对数据进行排序.xls │ │ 技巧244 多关键字排名应用.xls │ │ 技巧245 返回单列中的唯一值列表.xls │ │ 技巧246 返回双列中的唯一值列表.xls │ │ 技巧...
  • 88. 快速在个单元格中输入相同公式 50 89. 同时在个单元格中输入相同内容 50 90. 快速输入日期和时间 51 91. 将复制的单元格安全地插入到现有单元格之间 51 92. 在EXCEL中不丢掉列标题的显示 52 93. 查看与日期...
  • 88. 快速在个单元格中输入相同公式 50 89. 同时在个单元格中输入相同内容 50 90. 快速输入日期和时间 51 91. 将复制的单元格安全地插入到现有单元格之间 51 92. 在EXCEL中不丢掉列标题的显示 52 93. 查看与日期...
  • Excel技巧大全

    2015-05-06 07:21:37
    44. 快速在个单元格中输入相同公式 26 45. 同时在个单元格中输入相同内容 26 46. 快速输入日期和时间 26 47. 将复制的单元格安全地插入到现有单元格之间 26 48. 在Excel中不丢掉列标题的显示 26 49. 查看与日期...
  • 44. 快速在个单元格中输入相同公式 26 45. 同时在个单元格中输入相同内容 26 46. 快速输入日期和时间 26 47. 将复制的单元格安全地插入到现有单元格之间 26 48. 在EXCEL中不丢掉列标题的显示 26 49. 查看与日期...
  • 筛选唯一值】:以选择区域左边一列为条件进行唯一值筛选,输入1则删除重复值所在行,输入2则隐藏重复值所在行 【相同项与不同项】:提取两列数据的共同项和不同项,也可以对两列数据的不同项或者相同项进行标示 ...
  • Excel百宝箱8.0

    2011-06-07 21:32:17
    【sumifcol】【AVER】【hesum】【NOWW】【SFZ】【批注】【合并】【取数】【唯一值】【消除空值】【颜色求和】【颜色计数】【工作表】【数字】【分割取数】【共有项】不同项】【【公式】【计算】【公式长度】【大写】...
  • 筛选唯一值】以选择区域左边一列为条件进行唯一值筛选,输入1则删除重复值所在行,输入2则隐藏重复值所在行 【建文件目录】对指定文件夹中的文件建立目录,且生成链接,单击可以打开对应的文件 【建工作表目录】对...
  •  6.4.5 使用单个R1C1公式替换个A1公式  6.4.6 记住列字母对应的列号  6.5 条件格式必须使用R1C1引用  6.5.1 通过用户界面设置条件格式  6.5.2 使用VBA设置条件格式  6.5.3 找出G列中最大的值  6.6 ...
  • 34.在个工作表中查找一个范围内符合某个指定条件的项目对应指定范围加总求和 35.返回 Column 英文字 36.查找指定列名的列数 37.文字格式的时间(分:秒)转化为数字格式(秒) 38.将"hh:mm:ss"格式的时分秒数转换成秒数...
  • Excel新增工具集

    2011-12-20 09:30:27
    7、个工作簿中的所有工作表或指定工作表相同位置(区域)求和:打开(复制)一个空白的汇总表,表格格式与要汇总的表相同,凡鼠标选定单元格连续或不连续区域将对所有表格做累加计算。 8、当前工作表中每行记录都...
  • 实例181 对数据进行多条件排序 实例182 对统计结果进行排序 实例183 按姓氏笔画排序 实例184 按姓氏拼音排序 实例185 按仓库分组统计图书库存(多列) 实例186 多表分组统计 实例187 使用compute 实例188 使用compute ...
  • 【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体字...
  • 【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体...
  • EXCEL集成工具箱V6.0

    2010-09-11 01:44:37
    【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体...
  • 【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体...
  • 【综合计算显示】 综合计算并显示选中区域存储格的最大值、最小值、平均值、求和、存储格个数、筛选状态下的的加总求和,以及显示选区包含的数字、字符、汉字的个数等信息。 【GB2转BIG5】 将选中区域存储格的简体...
  • 函数作用:在个工作表中查找一个范围内符合某个指定条件的项目对应指定范围加总求和..........................59 '35.函数作用:返回 Column 英文字.......................60 '36.函数作用:查找指定列名的列数.......

空空如也

空空如也

1 2
收藏数 32
精华内容 12
关键字:

多条件筛选求和公式