求前3个非空数字之和,你还能想出其他的方法吗?

小伙伴们,大家好。今天要分享的是一个条件求和的问题。先来看下数据源和求和的结果。如下图所示,A到G列是数据源,可以看做一个成绩表。现在要求的是前3个非空数字的和,结果在H列。比如苏妍春,前3个非空的数字分别是62,39,75,它们的和是176。

在H2单元格输入公式=SUMPRODUCT((SMALL((B2:G2="")/1%+COLUMN(B:G),{1;2;3})=COLUMN(B:G))*B2:G2),不用三键,向下填充,完成。

说下大概的思路:首先判断是否为空,不为空的返回对应的列号,为空的返回一个比较大的列号(100再加上列号)。然后从这些列号中提取最小的3个值就是前3个非空数字对应的列号,并且让它们形成一维纵向数组。接着再用它和这个区域的所有列号进行相等的比较,也就是一维纵向数组和一维横向数组的运算,最后再乘以对应的数字区域,然后求和就得到了前3个非空数字之和。

以苏妍春为例说明,=(B2:G2="")/1%+COLUMN(B:G)这部分返回的结果如下图第10行红色框所示,非空的单元格返回对应的列号,空单元格返回较大的列号。

=SMALL((B2:G2="")/1%+COLUMN(B:G),{1;2;3})这部分是从上一步的结果中取出3个最小的值,分别是2,4,5,结果如下图红色框所示。

=SMALL((B2:G2="")/1%+COLUMN(B:G),{1;2;3})=COLUMN(B:G)这部分就是用上一步small的结果和COLUMN(B:G)进行相等的比较,产生一个二维数组,返回的结果如下图③所示。①是small的结果,②是COLUMN(B:G)的结果。

=(SMALL((B2:G2="")/1%+COLUMN(B:G),{1;2;3})=COLUMN(B:G))*B2:G2这部分就是用上一步的结果和B2:G2相乘,得到的结果如下图红色框所示,这样就得到了前3个非空的数字,最后用sumproduct求和就可以了,而且用sumproduct不用按三键。

好了,今天的问题就分享到此,你也可以想想其他的方法,欢迎在留言区写出答案。

文件链接:

https://pan.baidu.com/s/1vW8QUE3kfvw-w7Nin53xAA

提取码:j94r

(0)

相关推荐

  • 合并单元格表格转换

    前言 左边表格有合并单元格,如何能转换成右边的格式? 1方法一:INDEX+COLUMN F1=INDEX($A:$A,2*COLUMN(A1)) 右拉公式到L1 注释: 2*COLUMN(A1) 构 ...

  • 如何用公式实现自动填入满足相应条件的数字?

    Q:这是一名知乎网友提出的问题,如下图1所示,在列O中自动填写N班对应的日期. 图1 A:想了半天,没有想到简单的公式.使用数组公式找到N对应的日期数不难,但是如何将找到的多个日期数连在一起却难倒了我 ...

  • 进行排序和筛选的数组公式

    数组公式可以用于动态筛选数据区域,其中的一个关键步骤是使用数组公式去排序一些数字. 使用数组公式排序数字 如下图1所示,列A中的单元格区域A1:A10放置了一系列无序的数字,要求在列B中以升序排列该区 ...

  • 求包含文本内容的单元格中的数字之和

    单元格中的数据包含文本和数字(如图1),如何使用公式求出该单元格中的数字之和? 先不看答案,自已动手试一试. 公式思路 先将文本屏蔽,然后求数字之和. 公式 数组公式如下: =SUM(IFERROR( ...

  • 如何求出字符串中出现的所有数字之和?

    Q:如何使用公式返回文本字符串中所有数字之和?例如,对于文本字符串"I am 24years old and my Dad is 43",应用公式后,结果为13,即2+4+4+3. ...

  • 如何求出字符串中出现的所有数字之和

    Q:如何使用公式返回文本字符串中所有数字之和?例如,对于文本字符串"I am 24years old and my Dad is 43",应用公式后,结果为13,即2+4+4+3. ...

  • 问与答117:如何求出字符串中出现的所有数字之和?

    excelperfect Q:如何使用公式返回文本字符串中所有数字之和?例如,对于文本字符串"I am 24years old and my Dad is 43",应用公式后,结果 ...

  • ​LeetCode刷题实战129:求根到叶子节点数字之和

    算法的重要性,我就不多说了吧,想去大厂,就必须要经过基础知识和业务逻辑面试+算法面试.所以,为了提高大家的算法能力,这个公众号后续每天带大家做一道算法题,题目就从LeetCode上面选 ! 今天和大家 ...

  • 16.七年级:任何相邻数字之和都是16,求w和x是多少?找出规律

    欢迎您来到方老师数学课堂,请点击上方蓝色字体,关注方老师数学课堂.所有的视频内容,全部免费,请大家放心关注,放心订阅. 这题,属于一元一次方程培优找规律的题型.大家先在草稿本上认真地做一遍,然后再看后 ...

  • 为何被3整除的数,其各个位上的数字之和能被3整除?

    很多小学生(5年级及以上学生)都知道,被3整除的数,他们各个位上的数字之和能被3整除.这是判断一个数是否是3的倍数的一个基本的准则,但是,当我问,为什么的时候,很多同学就不知道了. 我们都知道,记住一 ...

  • “前是而今非”不是走回头路

    "前是而今非"不是走回头路 --<胜市正言>沪续八十五 公众微信号<商坛论衡> 从管理拐点的存在可以看出,任何一个管理理念,无论它过去如何正确,在历史的拐点 ...

  • 如何计算两列数字之和?

    今天介绍一下,最简单的sum函数功效.下面是小编为大家精心整理的关于如何计算两列数字之和?希望能够帮助到你们. 方法/步骤 1单列之和---菜单法 点击开始"菜单"- " ...