Excel计算Y列中最小的X列，忽略零
我正在尝试计算运行中最佳片段的总和。例如，每个公里给出这样的列表： 5:40 6:00 5:45 5:55 6:21 6：30
我试图收集2km/3km/4km等的最佳段，并希望一个简单的代码来完成。目前，我正在使用该公式
=Min(If(B1=0,9:9:9,sum(A1:B1),If(C1=0,9:9:9,sum(B1:C1))
，但这一直到50公里，这意味着一个很长的公式，然后我必须在3公里处重复略有不同，然后是4公里，然后是5公里等。肯定必须有一种方法 生成每个N列的总和列的数组，然后迭代到它忽略0时找到最小值？ 我现在可以手动做，但是如果我想超过50公里，该怎么办？我可能想在将来合并自行车/汽车驱动器以进行一些数据分析，因此我认为现在最好找到理想的公式。
这是令人沮丧的，因为我可以编码它，并且我想避免使用VBA，并坚持使用Excel中的配方。
I'm trying to calculate the sum of best segments in a run. For example, each Km gives a list as such:
5:40 6:00 5:45 5:55 6:21 6 :30
I'm trying to gather the best segments of 2km/3km/4km etc and would like a simple code to do it. At the moment, I'm using the formula
=Min(If(B1=0,9:9:9,sum(A1:B1),If(C1=0,9:9:9,sum(B1:C1))
but this goes all the way to 50km, meaning a very long formulae that I then have to repeat slightly differently at 3km, then 4km, then 5km etc. Surely there must me a way of
generating an array of summed columns of every n column, then iterating over that to find the min while ignoring values of 0?
I can do it manually for now, but what if I want to go over 50km? I might want to incorporate bike rides/car drives in the future just for some data analysis so I figured it best finding an ideal formulae now.
It's frustrating as I could code it and I want to avoid VBA ideally and stick to formulae in Excel.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是一个案件的草稿，只有2公里组的零零。我认为最简单的方法最初是添加几个辅助行，其中包含运行总数（以及以后的使用计数），并使用这样的公式成对减去它们：
但是，如果您可以访问Excel 365的最新添加就像扫描一样，您可以在没有辅助行的情况下进行。
中投入了几个零
这是一个更现实的场景，在
请注意，连续七个以上的腿不包含零的连续七个以上的运行，9、10等。只需锻炼到0即可。
如前所述，您可以通过使用扫描来分配助手行，但并非每个人都可以访问它，因此我将单独添加：
Here is a draft of the case where there aren't any zeroes just for groups of 2Km. I decided the simplest approach initially was to add a couple of helper rows containing the running total of times (and for later use counts) and use a formula like this to subtract them in pairs:
but if you have access to recent additions to Excel 365 like Scan you can do it without helper rows.
Here is a more realistic scenario with a couple of zeroes thrown in
Note that there are no runs of more than seven consecutive legs that don't contain a zero so 8, 9, 10 etc. just work out to 0.
As mentioned you could dispense with the helper rows by using Scan, but not everyone has access to this so I will add it separately:
汤姆工作！我也在途中学到了一些东西，我从未想到的是，将索引方法与序列和列一起使用。我以前从未听说过LET命令，我已经可以看到这将在将来对一些较大的计算中真正有所帮助。
非常感谢您，我想向您展示现在的样子。第3087行是我的旧公式，第3088行是使用新公式的相同数据的副本，因为您可以看到我已经获得了完全相同的结果，因此很明显它可以完美地工作，并且可以轻松地复制。
Tom that worked! I learnt a few things on the way too and using the indexing method alongside sequence and columns is something I had not thought of. I'd never heard of the LET command before and I can already see that this is going to really help with some of the bigger calculations in the future.
Thank you so much, I'd like to show you how it now looks. Row 3087 is my old formula, row 3088 is a copy of the same data using the new formula, as you can see I've gotten exactly the same results so it's clear that it works perfectly and it is can be easily duplicated.
