展开

关键词

SUMPRODUCT实现AVERAGEIF

每天一行 如果我们要求四川省的日均销售额 那就是SUM(四川销售额)/COUNT(四川出现天数) 可以写SUMIF+COUNTIF解决 也可以写AVERAGEIF解决(如果有的话) 然后其实也可以用Sumproduct 解决 写法是 变复杂了 =SUMPRODUCT((B2:B14=E2)*(C2:C14))/SUMPRODUCT(N(B2:B14=E2)) 以下是Sumproduct的补充内容 : 解释一下这个函数吧 Sumproduct是矩阵相乘函数 什么是矩阵相乘呢 举个例子 在有两列数据,长度一致 A1:A5和B1:B5 =SUMPRODUCT(A1:A5,B1:B5) 函数的意思是 1*2+3*4+5*

9710

函数|多条件求和——SUMPRODUCT函数

今天跟大家分享一个在多条件求和方面特别厉害的函数——SUMPRODUCT。 ▼ 也许大家对sum函数都很熟悉,知道它强大的求和功能。 但是如果学会使用sumproduct函数的话,分分钟做完,不用在乎有多少条数据记录。 大家可以看下以下场景: ? 如果是用sumproduct函数来求的话,会是什么情况呢? ? ? sumproduct函数语法详解: SUMPRODUCT(array1,[array2],[array3],[array4],…) 里面每一个arrayi代表一个数组,而且它支持的多个数组相乘。 当然,遇到需要三个 参数相乘求和的情况,sumproduct依然能够轻松搞定。 ?

64040
  • 广告
    关闭

    开发者专享福利,1988元优惠券限量发放

    带你体验博客、网盘相册搭建部署、视频渲染、模型训练及语音、文字识别等热门场景。云服务器低至65元/年,GPU15元起

  • 您找到你想要的搜索结果了吗?
    是的
    没有找到

    Excel公式技巧28: SUMPRODUCT函数基本原理详解

    SUMPRODUCT函数返回相应的数组或区域乘积的和。其语法为: SUMPRODUCT(数组1, [数组2], [数组3], …) 其中: 1. 数组1,必需,指定想要相乘并相加的数组数据。 2. 图1 公式1: =SUMPRODUCT({1;2;3}) 得到结果6,即对数组元素求和: =1+2+3 公式2: =SUMPRODUCT(C4:C6) 对指定单元格区域求和,得到结果6, 公式3: =SUMPRODUCT 然而,使用SUMPRODUCT函数可直接求出结果: =SUMPRODUCT(D4:D8,E4:E8) SUMPRODUCT函数是怎么做的呢?看下图3: ? 图3 可以清楚地看出,SUMPRODUCT函数先使数组中相对应的元素相乘,然后将这些乘积相加,得到最终结果。 以上是SUMPRODUCT函数最基础的用法。 应用技巧 下面,我们使用SUMPRODUCT函数来实现条件求和。

    61220

    精通Excel数组公式011:令人惊叹的SUMPRODUCT函数

    本文主要探讨什么时候使用SUMPRODUCT函数更有效,而什么时候应该使用SUMIFS函数代替SUMPRODUCT函数。 下面是关于SUMPRODUCT函数使用的一些重要说明: 1. SUMPRODUCT函数将非数字数据视为0。 4. SUMPRODUCT函数的数组参数可以处理数组操作以及由数组操作生成的结果数组,而无需按Ctrl+Shift+Enter键。 5. 此时,仅使用了SUMPRODUCT函数的SUM部分。 7. 在Excel 2003或更早版本中,SUMPRODUCT函数可用于的多条件求和和计数。 8. ,并且比SUMPRODUCT函数更有效率。 =SUMPRODUCT(--(A2:A5=C2)) 转换为: =SUMPRODUCT(--({TRUE;FALSE;TRUE;FALSE})) 转换为: =SUMPRODUCT(-({-1;0;-1;0

    1.2K10

    Excel公式练习70: 求最近一次活动的日期

    公式2:使用MAX/SUMPRODUCT函数 =SUMPRODUCT(MAX((C3:F3)*(C4:F4="x"))) 由于日期在Excel中是以数字形式存储的,因此可以将它们与TRUE/FALSE值组成的数组相乘 ,上述公式可转换为: =SUMPRODUCT(MAX({41091,41092,41093,41094}*{TRUE,TRUE,FALSE,FALSE})) 可转换为: =SUMPRODUCT(MAX( 我们使用了SUMPRODUCT函数强制进行数组运算,如果不使用SUMPRODUCT函数,则在输入公式: =MAX((C3:F3)*(C4:F4="x")) 需按Ctrl+Shift+Enter组合键。

    43410

    python吊打Excel?屁!那是你不会用!

    相信大家总能在朋友圈刷到下图这种教育广告,python很强,但总是这么贬低Excel就没必要了吧。 直到我看见了下面的回复,笑喷! ? [1] 而python就是给你一堆原材料,水泥、砖头、钢筋等等,你的工作就是自己要从头摘,一步步把高楼给建造出来。 Excel虽然人人都会,但如果想要用来数据分析,要掌握的函数操作还真不少。 9)sumproduct实战演示 =SUMPRODUCT(G:G,J:J) =SUMPRODUCT((H:H="2018")*1,G:G,J:J) =SUMPRODUCT((H2:H7326="2018 ")*G2:G7326*J2:J7326) =SUMPRODUCT((H:H="2019")*1,(D:D="上海")*1,G:G,J:J) =SUMPRODUCT((H:H="2019")*1,(D: 参考文章 [1] 李启方《会用Excel,真的需要再学Python吗?》

    47350

    Excel基础入门——常用特殊函数用法详解(八)

    /sumproduct+countif函数的使用方法。 因此,SUMPRODUCT函数是把指定几个数组元素对应相乘然后返回乘积之和。 公式=SUMPRODUCT (array1, [array2], [array3], ...) 3、多条件计数 公式=SUMPRODUCT((范围1=条件1)*(范围2=条件2)......) 例如,我们要求客户年龄为28岁且购买数量等于2的客户人数,使用SUMPRODUCT函数进行多条件计数。 三、SUMPRODUCT+COUNTIF 基本用法:对指定数据列进行排名。

    21410

    这个示例太典型,忍不住再拿出来秀一下

    图1 下面是我首先想到的公式1: =SUMPRODUCT(MID(B3,ROW(INDIRECT("1:" &LEN(B3))),1)*1) 在单元格C3中输入后,下拉至单元格C10。 将上述结果乘以1转换为数字,最后使用SUMPRODUCT函数求和。 也可以使用公式2: =SUMPRODUCT(MID(B3,ROW(OFFSET(A1,,,LEN(B3))),1)+0) 公式中,使用的是: ROW(OFFSET(A1,,,LEN(B3))) 得到数组 : {1;2;3;4;5;6;7;8;9} 加上0,将MID函数得到的数字文本转换为数字,让SUMPRODUCT函数对它们进行求和。 还可以使用公式3: =SUMPRODUCT(MID(B3,ROW(A1:INDEX(A:A,LEN(B3))),1)+0) 公式中,使用的是: ROW(A1:INDEX(A:A,LEN(B3))) 得到数组

    14520

    Excel公式技巧82:查找指定值所在的单元格

    图1 我们可以使用SUMPRODUCT函数与ROW函数和COLUMN函数配合,分别获取该数所在的行和列,然后使用ADDRESS函数来获取单元格地址。 公式为: =ADDRESS(SUMPRODUCT((B2:M7=MAX(B2:M7))*ROW(B2:B7)),SUMPRODUCT((B2:M7=MAX(B2:M7))*COLUMN(B2:M2)), 公式: =INDIRECT(ADDRESS(SUMPRODUCT((B2:M7=MAX(B2:M7))*ROW(B2:B7)),1)) 返回“商品D”,即该数值对应的商品名。 公式: =INDIRECT(ADDRESS(1,SUMPRODUCT((B2:M7=MAX(B2:M7))*COLUMN(B1:M1)))) 返回“六月”,即该数值对应的月份。 小结:本示例技巧关键在于理解公式中SUMPRODUCT函数部分的技巧。

    3.5K30

    Excel公式练习71: 求最近一次活动的日期(续)

    公式2:使用MAX/SUMPRODUCT函数 =SUMPRODUCT(MAX((B13:B20=F12)*(C13:C20))) 这个公式由于日期在Excel中是以数字形式存储的,因此可以将它们与TRUE /FALSE值组成的数组相乘,上述公式可转换为: =SUMPRODUCT(MAX({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}*{41091;41091 ;41091;41092;41092;41093;41094;41094})) 可转换为: =SUMPRODUCT(MAX({41091;0;0;41092;0;0;0;0})) 得到: 41092 即该日期对应的序数 我们使用了SUMPRODUCT函数强制进行数组运算,如果不使用SUMPRODUCT函数,则在输入公式: =MAX((B13:B20=F12)*(C13:C20)) 需按Ctrl+Shift+Enter组合键

    46720

    精通Excel数组公式013:什么时候真正需要数组公式?

    示例1:条件是文本时的日期统计,使用TEXT和SUMPRODUCT还是COUNTIFS? 如下图1所示,条件区域是单元格E8中的年和F8中的月。 图3:多个SUMPRODUCT和TEXT函数的公式,更长的计算时间。 注意,数组运算中使用不同函数的计算速度也不同。本示例中,使用MONTH和YEAR函数组合比使用TEXT函数的速度更快。 示例2:条件与日期序列不匹配情况下的年销售额求和,使用SUMPRODUCT,SUMIFS还是SUMIF? 如下图4所示,条件单元格中的数据与日期序列号不匹配,要求该年的销售额之和。 ? 你可以使用COUNTIF或COUNTIFS函数替代SUMPRODUCT和TEXT函数的公式,也可以使用IF函数替代IFERROR函数的公式。这两种情形都会缩短公式计算时间。

    33720

    excel中的 sumif 和 countif 函数分析详解

    E4=SUMPRODUCT((C2:C10>E2)*(C2:C10<F2)) E4的公式使用了sumproduct的计数功能,即对满足两个条件的区域进行计数。 F10=SUMPRODUCT((C2:C10>E7)*(C2:C10<F7)*C2:C10) F10与E10类似,不过这里使用的是sumproduct函数的求和功能,故可以省去数组公式{} ------

    42550

    Excel公式练习62: 判断回文

    公式 假设单词或句子在单元格A1中,那么可以使用公式: =IF( SUMPRODUCT( ( MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)= MID(A1,LEN(A1)- +1,1) 即: {"m";"a";"d";"a";"m"}={"m";"a";"d";"a";"m"} 得到: {TRUE;TRUE;TRUE;TRUE;TRUE} 将上述结果代入公式: =IF( SUMPRODUCT 得到: =IF( SUMPRODUCT( ({TRUE;TRUE;TRUE;TRUE;TRUE})+ 0 ) = LEN(A1), "这个是回文", "No!") 转换为: =IF( SUMPRODUCT( {1;1;1;1;1} ) =LEN(A1), "这个是回文", "No!") 转换为: =IF( 5 = LEN(A1), "这个是回文","No!") 转换为: =IF( SUMPRODUCT({0;0;1;0;0}) =LEN(A1), "这个是回文", "No!") 转换为: =IF(1 = 5, "这个是回文","No!")

    43010

    Excel公式练习79: 多个OR条件计数

    解决方案 可以使用SUMPRODUCT函数与ISNUMBER/MATCH函数的组合来编写公式求得结果。 在单元格F9中的公式为: =SUMPRODUCT(ISNUMBER(MATCH(项目,{"T恤衫","连帽衫"},0))*1) 公式中的MATCH函数在“项目”列中查找“T恤衫”或“连帽衫”,返回由数字 函数求和得到满足条件的项目数: 7 在单元格G9中的公式为: =SUMPRODUCT(ISNUMBER(MATCH(项目,{"T恤衫","连帽衫"},0))*ISNUMBER(MATCH(颜色,{"红色 依此类推,在单元格H9中的公式为: =SUMPRODUCT(ISNUMBER(MATCH(项目,{"T恤衫","连帽衫"},0))*ISNUMBER(MATCH(颜色,{"红色","蓝色","绿色"}, 0))*ISNUMBER(MATCH(城市,{"北京","上海"},0))) 上述公式中,花括号内的数组是硬编码值,我们可以使用单元格区域来替换: =SUMPRODUCT(ISNUMBER(MATCH(

    35920

    Excel公式技巧56:获取最大值最小值所在的单元格地址

    在单元格D7中输入公式: =ADDRESS(INDEX(ROW(A1:A4),SUMPRODUCT((A1:D4=C7)*(ROW(A1:D4)))),INDEX(COLUMN(A1:D1),SUMPRODUCT FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE} 将上述中间数组代入: SUMPRODUCT ((A1:D4=C7)*(ROW(A1:D4))) 得到: SUMPRODUCT({FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,FALSE;FALSE,FALSE, FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}*{1,1,1,1;2,2,2,2;3,3,3,3;4,4,4,4}) 得到: SUMPRODUCT({0,0,0,0;0,2,0,0 ((A1:D4=C8)*(ROW(A1:D4)))),INDEX(COLUMN(A1:D1),SUMPRODUCT((A1:D4=C8)*(COLUMN(A1:D4)))),1,1) 得到结果: A2

    2.3K53

    Excel公式技巧25: 使SUMIFSCOUNTIFS函数内的间接列引用变化

    例如公式: =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"),INDIRECT("'"&Sheets&"'! B:B"),"Y")) 其中,“Sheets”是定义的名称,引用位置为: ={"Sheet1","Sheet2","Sheet3","Sheet4"} 像前面一样,我们希望向右拖拉时,公式变化为: =SUMPRODUCT B:B"),"Y")) =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!E:E"),INDIRECT("'"&Sheets&"'! 仍然可以使用OFFSET函数: =SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&Sheets&"'!

    48320

    一大波常用函数公式,值得收藏!

    COUNTIFS函数的语法换成普通话的意思大致是: =COUNTIFS(区域1,条件1, 区域2,条件2…区域N,条件N) 除此之外,还可以使用SUMPRODUCT函数计算多条件计数: =SUMPRODUCTSUMPRODUCT函数多条件计数的语法,换成普通话的意思大致是: =SUMPRODUCT((区域1=条件1)*(区域2=条件2)* (区域N=条件N)) 15、多条件求和: 前面的内容中,咱们说过多条件求和的 SUMIFS函数,如果使用SUMPRODUCT函数多条件求和,语法为: =SUMPRODUCT((区域1=条件1)*(区域2=条件2)* 求和区域) ?

    31740

    Excel公式技巧41: 跨多工作表统计数据

    使用公式: =SUMPRODUCT(COUNTIF(INDIRECT("'"& Sheets & "'!" & "A1:E10"),B9)) 即可得到结果。 ? 图6 我们可以看到,上述公式可以解析为: =SUMPRODUCT(COUNTIF(INDIRECT({"'表一'!A1:E10";"'表二'!A1:E10";"'表三'! A1:E10"}),B9)) 分别计算单元格B9中的值在每个工作表指定区域出现的次数,公式转换为: =SUMPRODUCT({5;12;3}) 得到结果20。 图7 这样,就可以直接使用公式: =SUMPRODUCT(COUNTIF(INDIRECT("'"& Sheets2 & "'!"

    4.3K30

    问与答85: 如何统计汇总筛选过的列表数据?

    在单元格D2中输入公式: =SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),--(C7:C13=B2)) 传递给 SUMPRODUCT函数的是生成的两个中间数组:一个是代表所有有效筛选的数据的列表,另一个是代表所有与条件匹配的未筛选的数据的列表,两个数组的乘积将是一个包含与条件匹配的筛选的数据的数组。 对于公式中的: --(C7:C13=B2) 将单元格区域C7:C13中的数值与单元格B2中的数值比较,得到数组: {0;1;0;1;0;1;0} 将上述生成的中间数组传递给SUMPRODUCT函数,这样 : 对于没有筛选的数据,公式转换为: =SUMPRODUCT({1;1;0;1;1;1;1}, {0;1;0;1;0;1;0}) =SUMPRODUCT({1*0;1*1; 0*0; 1*1; 1*0; 1*1; 1*0}) =3 对于应用了筛选的数据,公式转换为: =SUMPRODUCT({1;0;0;1;0;0;0}, {0;1;0;1;0;1;0}) =SUMPRODUCT({1*0;0*1;

    55120

    Excel中如何实现多条件求和?

    在Excel2007以下,多条件求和通常使用sumproduct函数,而2010及以上,带了sumifs多条件求和函数,使用都非常简单。 分别举例如下: 一、sumproduct 即通过多个条件的相乘实现多条件的判断,如下图所示: 二、sumifs 即通过罗列多个条件直接完成多条件的判断,如下图所示: 相对于sumproduct来说, 2、重点函数专项训练 包括SumIf、SumProduct、Vlookup等等 3、常用组合函数重点训练 如IF和VLOOKUP、LARGE和ROW等等函数的结合使用 在线M函数快查及系列文章链接(

    90930

    扫码关注腾讯云开发者

    领取腾讯云代金券