精华内容
下载资源
问答
  • 他们的格式都相近:部门,姓名,型号等等 现在是希望在“结果”这个工作表中对五个工作表的值进行汇总,而且要能按照部门和姓名进行筛选 这个问题可以通过以下公式来解决 =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

    展开全文
  • 主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel...
    日常工作中,我们经常要用到多条件求和,方法有多种,第一类:使用基本功能来实现。主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel2007及以上的版本),方法千差万别、效果各有千秋。本人更喜欢用SUMPRODUCT函数。由于Excel帮助对SUMPRODUCT函数的解释太简短了,与SUMPRODUCT函数的作用相比实在不匹配,为了更好地掌握该函数,特将其整理如下。

    龙逸凡注:欢迎转贴,但请注明作者及出处。

    一、         基本用法

    在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

    语法:

    SUMPRODUCT(array1,array2,array3, ...)

    Array1, array2, array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。

    公式:=SUMPRODUCT(A2:B4, C2:D4)

            A        B        C        D
    1        Array 1        Array 1        Array 2        Array 2
    2        3        4        2        7
    3        8        6        6        7
    4        1        9        5        3
                                   

    公式解释:两个数组的所有元素对应相乘,然后把乘积相加,即 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))

    展开全文
  • Dynamics CRM 之汇总字段

    2019-10-01 04:32:26
    筛选器:对相关实体的记录进行条件判断筛选。 聚合:可以选择聚合函数对某个值进行聚合操作。 例如:一个客户有条资产记录,对该客户的总资产求和。 优点:运行速度快,至少比写定时任务和插件汇总...

    用插件汇总数据,速度很慢,导数据的时候更慢!那就用汇总字段- -

    新建个汇总字段,字段类型选择汇总。点击编辑进入逻辑编辑

    相关实体:对当前实体或者相关联的实体的字段值进行判断筛选。

    筛选器:对相关实体的记录进行条件判断筛选。

    聚合:可以选择聚合函数对某个值进行聚合操作。

    例如:一个客户有多条资产记录,对该客户的总资产求和。

    优点:运行速度快,至少比写定时任务和插件汇总快的多,想实时查看可以点击字段后面的刷新。

    缺点:汇总不是实时汇总,1个小时自动汇总一次,不具有实时性。

    转载于:https://www.cnblogs.com/SilverWolf/p/6669967.html

    展开全文
  • DQL语言学习汇总

    2020-07-25 21:37:00
    where 筛选条件 order by 排序列表【ASC/DESC】 二、特点 ASC:升序,如果不写默认升序 DESC:降序 排序列表支持单个字段,个字段,函数,表达式,别名 order by的位置一般放在查询语句的最后(limit语句除外) ...

    排序查询
    一、语法
    select 查询字段
    from 表
    where 筛选条件
    order by 排序列表【ASC/DESC】

    二、特点

    1. ASC:升序,如果不写默认升序
      DESC:降序
    2. 排序列表支持单个字段,多个字段,函数,表达式,别名
    3. order by的位置一般放在查询语句的最后(limit语句除外)

    常见函数
    调用: select 函数名(实参列表)
    单行函数:
    1.字符函数
    concat 拼接字符
    substr 截取子串
    upper/lower 变大写/小写
    replace 替换
    length 获取字节长度
    lpad 左填充
    rpad 右填充
    instr 获取子串第一次出现的索引

    2.数学函数
    ceil 向上取整
    floor 向下取整
    round 四舍五入
    mod 取模
    truncate 截断
    rand 获取随机数,返回0-1之间的小数

    3.日期函数
    now 返回当前系统日期及时间
    year/month/day 年/月/日
    date_format 将日期转换为字符
    curdate 返回当前系统日期
    curtime 返回当前时间
    str_to_date将字符转换成日期
    datediff 返回两个日期相差的天数
    monthname 以英文形式返回月

    4.其他函数
    version 当前数据库服务器版本
    database 当前打开的数据库
    user 当前用户
    password(‘字符’) 返回该字符的加密形式
    md5(‘字符’) 返回该字符的加密形式

    5.流程控制函数
    if(条件表达式,表达式1,表达式2) 如果条件成立,返回表达式1,否则返回表达式2
    case 情况1
    case 变量或表达式或字段
    when 产量1 then 值1
    when 产量2 then 值2

    else 值n
    end

    case 情况2
    case
    when 条件1 then 值1
    when 条件2 then 值2

    else 值n

    分组函数
    max 最大值
    min 最小值
    sum 和
    avg 平均值
    count 计算个数

    特点

    1. sum/avg 一般用于处理数值型 max/min/count 可以处理任何数据类型 以上分组函数都忽略null
    2. 都可以搭配distinct使用,实现去重的统计
      select sum(distinct 字段) from 表
    3. 注意count的用法:count(*)/count(1)/count(‘字段’)
    展开全文
  • EXCEL函数公式集

    热门讨论 2010-03-16 03:26:38
    如何求多条件的平均值 想求出第三大之数值 【查询和查找引用】 查找顺序公式 怎样实现精确查询 查找及引用 查找函数的应用 怎么能方便的判断某个单元格中包含多少个指定的字符?如何用查找函数 日期查找的问题 如何...
  • MySQL知识汇总(三)

    2020-06-18 01:27:17
    MySQL之DQL语言(数据查询语言)(二)表查询等值连接查询非等值连接join连接内连接外连接常见函数子查询分页查询分组查询案例(结合分组函数)总结尾言 上篇博文给大家介绍了DQL语言的部分知识点,这篇博文给...
  • 聚合函数:sum,count,max,avg等,一般作用于条记录上。通过group by可以将数据对属于一组的数据起作用。SELECT region, SUM(population), SUM(area)FROM bbc GROUP BY regionhaving子句,汇总之后再筛选。作用...
  • 7.4.2 统计满足条件的单元格数量 7.4.3 使用AND筛选条件 7.4.4 使用OR条件 7.4.5 综合使用AND和OR筛选条件 7.4.6 出现频率最高的项的计数 7.4.7 确定特定文本的出现频率 7.4.8 统计唯一值的数目 7.4.9...
  • 7.4.2 统计满足条件的单元格数量 7.4.3 使用AND筛选条件 7.4.4 使用OR条件 7.4.5 综合使用AND和OR筛选条件 7.4.6 出现频率最高的项的计数 7.4.7 确定特定文本的出现频率 7.4.8 统计唯一值的数目 7.4.9...
  • excel sample

    千次阅读 2014-11-13 14:28:14
    excel条件筛选的三种方法 EXCEL分类汇总的用法_百度知道 Excel用函数公式筛选数据的方法 在公式中定义和使用名称 - Excel 快速入门:创建数据透视表 - Excel Excel:[2]vlookup双条件查询实例_百度经验 ...
  • SQL查询入门(下篇)

    2012-09-18 16:42:00
    引言 在前两篇文章中,对于单表查询和表...对数据进行汇总前,还可以按照特定的列(column)将数据进行分组(Group by)再汇总,然后按照再次给定的条件进行筛选(Having). 聚合函数将多行数据进行汇总的概念可以简...
  • 多条件组合查询资料 • 利用INDEX函数结合MATCH函数进行向左查找 • 实现根据学员成绩查询等级 • 确定工资单中最后一名员工的位置 • 根据工资表生成工资条 • 利用CHOOSE函数重新生成内存数组 • 利用查找函数...
  • 引言  在前两篇文章中,对于单表查询和表查询的概念做出了详细的介绍,在本篇文章...对数据进行汇总前,还可以按照特定的列(column)将数据进行分组(Group by)再汇总,然后按照再次给定的条件进行筛选(Having).
  • 第一节、常用条件统计函数的应用 通配符在条件统计函数中的应用 Sumifs、Countifs、Average复杂条件统计 Round函数 第二节、逻辑函数 If条件函数的嵌套应用 And、Or函数与If的组合应用 第三节、查找引用函数的使用 ...
  • 技巧133 设置筛选条件的相互关系 技巧134 空与非空条件设置的方法 技巧135 精确匹配的筛选条件 技巧136 高级筛选中通配符的运用 技巧137 使用公式自定义筛选条件 技巧138 复杂条件下的高级筛选 技巧...
  • 2、where子句基于指定的条件对记录行进行筛选;3、group by子句将数据划分为个分组;4、使用聚集函数进行计算;5、使用having子句筛选分组; 在select 语句中可以使用group by 子句将行划分成较小的组,然后,...
  • 63.Tableau中的排序与筛选器_实例演示条件筛选器 64.Tableau中的排序与筛选器_实例演示顶部筛选器 65.Tableau中的排序与筛选器_实例演示通配符筛选器 66.Tableau中的排序与筛选器_小结 67.Tableau中的参数_如何创建...
  • select语句的执行顺序与优化

    千次阅读 2009-11-07 00:02:00
    2、where子句基于指定的条件对记录行进行筛选;3、group by子句将数据划分为个分组;4、使用聚集函数进行计算;5、使用having子句筛选分组; 在select 语句中可以使用group by 子句将行划分成较小的组,然后,...
  • 情况下,用户都需要进行一些汇总操作,比如统计整个公司的人数或者统计每一个部门的人数等。 聚合函数 AVG(【DISTINCT】 expr) 返回expr的平均值 COUNT(【DISTINCT】 expr)返回expr的非NULL值的数目 MIN...

空空如也

空空如也

1 2 3 4 5 ... 8
收藏数 144
精华内容 57
关键字:

多条件筛选汇总函数