Question

如何让一个单元格里的数去减另外一列中最近的大于0的数字

如：在A列中有随时变动的数字（如从A1到A15中，开始是A3是5、A7是8、其它都是0，后来A7变为了0，A5变为6），B列中是固定的数字（如B15是23，在C15里输入公式，让B15去减A列中最近的大于0的数，开始是B15-A7（B11对应的A15最近【向上查询】的大于0的单元格）=15，当变化后，就是B15-A5=17.能用公式解决吗？？？





Answer 1

公式设定范围是A1:A15，实际范围可根据需要调整。C15输入以下公式：

=B15-LOOKUP(1,0/(A$1:A$15>0),A$1:A$15)

或者用这公式也行，但这是数组公式，输入完成后不要直接回车，要按三键  CTRL+SHIFT+回车  结束

=B15-INDEX(A:A,MAX((A$1:A$15>0)*ROW($1:$15)))

追问

请问我用图上A列里的哪个公式计算出来的数字，怎么不正常啊？？？

如果是公式的最后一个有数字，是正常的，如果下边最后一个为空白时，就不正常了。

我的目的是想，当E列和F列对应的单元格里面为相等时才把G列的数字显示到A列里，但是当都为0时，就不用显示。然后C列里面就把B列的与A列最下面的大于0的数字进行计算。

请问我用图上A列里的哪个公式计算出来的数字，怎么不正常啊？？？

如果是公式的最后一个有数字，是正常的，如果下边最后一个为空白时，就不正常了。

我的目的是想，当E列和F列对应的单元格里面为相等时才把G列的数字显示到A列里，但是当都为0时，就不用显示。然后C列里面就把B列的与A列最下面的大于0的数字进行计算。

追答

把C1的公式改为这样就行：
=IF(B15="","",B15-LOOKUP(1,0/(--(0&A$1:A$15)>0),A$1:A$15))

这是因为A列的公式当不满足条件时，是返回一个空白值 "" ，这个空白值在EXCEL的内部运算中是作为一个文本字符处理，且是比任意一个数字都要大的，所以公式要求返回最后一个大于0时，如果这个这个文本字符又是位于最后一个单元中，那么必定是返回这个空值 "" 。 即上面原公的公式会最后变为 B15-"" ，这就导致错误值 #VALUE! 的产生。

其实也可以把A列的公式改为这样，A1公式下拉：
=IF((E1=F1)*F1>0,G1,0)

然后上面C列的公式不就不用修改了，但这样A列会产生0值。

追问

嗯！谢谢啦，原来就是我哪个为文本，我就说怎么总出错。我里面还有个把数字拆分的公式，如果里面前面没有数字时，就出现#VALUE! 我不想它出现这个，显示为空白或者0都可以，要数字型的，不要文本型的，不然又要出问题。麻烦帮我看看能不能解决。谢谢！！

追答

把B1的公式改为这样右拉再下拉就行了，返回的结果就是数字型的了：

=IF(LEN($A1)>=COLUMN(A1),--MID($A1,COLUMN(A1),1),"")

如果用的是EXCEL 2007及以上版本，公式精简为：

=IFERROR(--MID($A1,COLUMN(A1),1),"")

Answer 2

选中这一列，
菜单，格式，条件格式，单元格数值，大于 0 ，定义格式字体颜色红色，确定
再添加一个条件，单元格数值， 小于等于 0，定义格式字体颜色绿色，确定

追问

请问我用图上A列里的哪个公式计算出来的数字，怎么不正常啊？？？

如果是公式的最后一个有数字，是正常的，如果下边最后一个为空白时，就不正常了。

我的目的是想，当E列和F列对应的单元格里面为相等时才把G列的数字显示到A列里，但是当都为0时，就不用显示。然后C列里面就把B列的与A列最下面的大于0的数字进行计算。

Answer 3

公式是是C15中输入：=B14-MAX(A:A)