如何返回第一个大于0的值、第一个文本值、第一个数字？ / 开普饭

最近推送的几篇文章：

· 正 · 文 · 来 · 啦 ·

需求

有读者朋友提问：

这个问题拆分为三个小步骤：

1、先确定第一个大于0的数字的位置。确定位置一般用Match函数

2、然后用偏移函数INDEX或OFFSET取连续30个单元格组成的区域。

3、再用SUM对30个单元格求和。

公式

在3月份我们写的一篇文章中介绍了，如何用LOOKUP查找第一个、最后一个非空的值。

如何用公式返回第一个（最后一个）非空单元格的值，建议收藏备用

在本文中，我们要找到第一个符合条件值的位置（第几个），不能用上面链接中的公式。

我们知道，确定位置是用Match函数。比如要确定“偷懒的技术”在J4:J15单元格是第几个，用Match函数编制公式为：

=MATCH("偷懒的技术",J4:J15,0)

如果要确定第一个true的位置，公式为：

=MATCH(TRUE,J4:J15,0)

一些人为了减少公式的字符数，将其写成下面的公式，效果是等同的：

=MATCH(1=1,J4:J15,)

或者

=MATCH(1>0,J4:J15,)

解释：

不管是1=1 还是1>0，其计算结果均为TRUE。

在本文中，要得到第一个大于0的值的位置（第几个），其公式为：

=MATCH(TRUE,A3:A16>0,0)

解释：

公式中的A3:A16>0是将A3到A16单元格区域中的单元格逐个与0进行比较，看是否大于0。其计算结果为TRUE或FALSE。

我们选中公式中的A3:A16，按F9，

可以看到他是由TRUE或FALSE组成的一个列表。Match就是在这个列表中找TRUE。

然后，再用OFFSET函数往下偏移取1列，10行，共10个单元格（含其本身）：
=OFFSET(A3,MATCH(TRUE,A3:A16>0,0)-1,0,10,1)

再用SUM函数对其求和：

=SUM(OFFSET(A3,MATCH(TRUE,A3:A16>0,0)-1,0,10,1))

我们将此公式扩展引申一下：

返回列表中的第一个文本（含逻辑值），公式为：

=INDEX(A2:A17,MATCH(TRUE,A2:A17>9E+307,0))

返回列表中的第一个数字（含空白单元格），公式为：

=INDEX(A2:A17,MATCH(TRUE,A2:A17<9E+307,0))

解释：

9e307是科学计算法，也就是9*10^307，接近于Excel中最大的数字。

在Excel中大小比较机制为“数字<大写字母<小写字母<汉字"，也就是说所有的文本都大于所有的数字，只要是大于最大的数字那肯定就是文本了。

也可用LOOKUP函数来编制公式

返回第一个文本：

=LOOKUP(1,0/FREQUENCY(2,ISTEXT(A2:A17)+1),A2:A17)

返回第一个数字：

=LOOKUP(1,0/FREQUENCY(2,ISNUMBER(A2:A17)+1),A2:A17)

本文知识点

Match函数
9E307
大小比较机制

Excel畅销书推荐：

《“偷懒”的技术2：财务Excel表格轻松做》

《偷懒2》不是《偷懒1》的改版，两者内容是完全不同的，《偷懒2》主要介绍灵活如何用函数公式、功能技巧专业地设计表格。并将日常工作常用的公式设计成模型公式，要用的时候，直接比照套用就是了。

宗旨是：

表格不会做，照搬即可；

公式不会写，套用就行。

《“偷懒”的技术：打造财务Excel达人》

2017年当当网畅销榜Excel类第一名，办公类第二名，好评率99.8%，学Excel必选书籍！

【双口相声】偷懒1和偷懒2有啥区别？

👇滑动下面的列表查看更多

如何正确使用本公众号，学习Excel技巧，提高工作效率

【目录】本公众号2017年推送文章的分类导航

【目录】本公众号2018年推送文章的分类导航

怎样才算精能Excel？看完再也不敢在简历上写精通Excel了！

怎样才能让Excel运行得更快、从此告别卡慢等

强大到逆天的“快速填充”，不用公式提取字符、调换位置

你真的理解了相对引用？95%的人都错了，你呢？

按年、季、月、旬、周分段求和，这一篇文章总结完了

根据指定的条件，统计唯一值的个数，公式总结

行列交叉查询公式汇总及解释

如何按简称查找全称、如何反向模糊查找

财务工作经典Excel公式及解析

使用vlookup函数的常见错误及解决方法

深入讲解SUMIF&多表多列多条件求和

用sumif对超15位的代码条件求和居然出错了，原因是...

一张图表示实际VS半年及年度预算完成情况
 要做出别具一格的图表都要用到这个强大的功能...

超越图表大神的小技巧：在柱形图背后添加平均线

不等宽的堆积柱形图，这思路开脑洞...

如何用箭头标注指标的同比增减情况？

如何返回第一个大于0的值、第一个文本值、第一个数字？

相关推荐