Excel公式计算行中特定值之间的距离 [英] Excel formula in counting distance between a specific value in rows

问题描述

我正在处理无限数据，每次都会添加行的组合。我想要一个公式，计算第一下限到第二下限之间的行距离。请不要VBA我想要一个公式或数组公式。如果有人可以帮忙，我真的很感激。我一直试图找出几个月。

I am dealing with unlimited data that keeps adding a combination of rows every-time. I wanted a formula that would count the row distance between the First Lower limit to the Second Lower Limit. Please no VBA i want a formula only or array formula perhaps. I would truly appreciate it if someone could help. I have been trying to figure this out for months now.

示例：

推荐答案

这应该是正常的。粘贴在单元格I2中并作为数组公式提交[control-shift-enter]。然后下拉。

This should work. Paste in cell I2 and commit as an array-formula [control-shift-enter]. Then pull down.

=IF(LARGE(IF((A$1:F$1000=H2),ROW(A$1:A$1000),0),2)=0,0,LARGE(IF((A$1:F$1000=H2),ROW(A$1:A$1000),0),1)-LARGE(IF((A$1:F$1000=H2),ROW(A$1:A$1000),0),2))+1

这篇关于Excel公式计算行中特定值之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！