寻找最小值的最大值 [英] Finding the maximum of minimum values

问题描述

我想计算大于 0 的电子表格(特别是 Google 表格)中每一行的最小值的最大值.我希望这是有道理的.我的数据是:

I would like to calculate the maximum value of the minimum values of each row in a spreadsheet (Google Sheets, specifically) that is greater than 0. I hope that makes sense. My data is:

0    6   7   8   1          
0   12  21  22  21          
0   10      18  24
0    7   9   1  17          
0   16  16  20              

所以，我想要一个可以生成的 ArrayFormula:

So, I want an ArrayFormula of some sort that will generate:

 1
12
10
 1
16

然后我可以获得最大值.我已经阅读并体验到明显的解决方案不起作用，即:

Of which I could then get the maximum. I've read and experienced that the obvious solution doesn't work, which is:

=max(ArrayFormula(min(if(A:Z>0,A:Z,"")))

原因是它的 ArrayFormula(min(... 部分返回整个数据集的最小值大于 0，这里是 1.如果没有好的解决方法，我'我会考虑以某种方式重新排列我的数据，或者在单独的单元格中计算最小值，但我真的希望有一个一步解决方案.

The reason being the ArrayFormula(min(... part of it returns the minimum of the whole data set that's greater than 0, which is 1 here. If there's no nice workaround, I'll consider rearranging my data somehow, or calculating minima in separate cells, but I'm really hoping for a 1-step solution.

推荐答案

max ArrayFunction 不存在，所以唯一的方法是解决方法.假设您在 A1:C5 范围内有一些数字.结果范围是 D1:D5:

max ArrayFunction doesn't exist, so the only way is workaround. Suppose, you have some numbers in range A1:C5. the resulting range is D1:D5:

您只需要单元格D1中的单个公式:

All you need is single formula in cell D1:

=QUERY(QUERY({ArrayFormula(REGEXEXTRACT(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(row(A1:C5)&"*"&A1:C5&"/")),"/")),"\d+")),ArrayFormula(REGEXEXTRACT(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(row(A1:C5)&"*"&A1:C5&"/")),"/")),"[*](\d+)")*1)},"select Col1, min(Col2) where Col2 <>0 group by Col1 label min(Col2) ''"),"select Col2")

小心，它可能运行缓慢.

Be careful, it can work slow.

公式的主要部分是查询:

The main part of formula is query:

select Col1, min(Col2) where Col2 <>0 group by Col1...

改变它以获得不同的结果(最大值、最小值、总和、平均值、一些计算等等)

change it to get different results (max, min, sum, avg, some calculatrions and so on)

如果有人想探索公式，这里是工作表 分步解决方案.

If someone want to explore the formula, here's worksheet with step-by step solution.

这篇关于寻找最小值的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！