水平定向数据的条件最大/最小 [英] Conditional Max/Min on Horizontally oriented data
问题描述
右上方是指向我的数据集的子段的图像的链接.它以3列为一组，第一个是浓度，第二个是限定符，最后一个是MDL，并且最多可以进行95个样本(因此共有285列使手动输入变得不切实际).对于具有"u"的限定符的那些，我该如何计算浓度值的最大值或最小值?反之，没有限定符的，反之亦然?
Right, above is a link to an image of a sub-segment of my data set. It is oriented in sets of 3 columns, with the first being a concentration, the second a qualifier, and the last an MDL - and continues for up to 95 samples (so a total of 285 columns making manual entry impractical) . How can i calculate the max or min of the concentration values for those that have a qualifier of "u" or vice versa have no qualifier?
我什么都找不到，不幸的是我没有时间重新定向数据.有人有主意吗?
I can't figure out anything, and unfortunately i don't have the time to re-orient the data. Anybody have an idea?
推荐答案
也许会这样，
C7:J7中的8个公式是
The 8 formulas in C7:J7 are,
=AGGREGATE(15, 6, $A2:$AY2/(($A$1:$AY$1=C$6)*($B2:$Z2="U")), 1)
=AGGREGATE(15, 6, $C2:$BA2/(($C$1:$BA$1=D$6)*($B2:$AZ2="U")), 1)
=AGGREGATE(14, 6, $A2:$AY2/(($A$1:$AY$1=E$6)*($B2:$Z2="U")), 1)
=AGGREGATE(14, 6, $C2:$BA2/(($C$1:$BA$1=F$6)*($B2:$AZ2="U")), 1)
=AGGREGATE(15, 6, $A2:$AY2/(($A$1:$AY$1=G$6)*($B2:$Z2<>"U")), 1)
=AGGREGATE(15, 6, $C2:$BA2/(($C$1:$BA$1=H$6)*($B2:$AZ2<>"U")), 1)
=AGGREGATE(14, 6, $A2:$AY2/(($A$1:$AY$1=I$6)*($B2:$Z2<>"U")), 1)
=AGGREGATE(14, 6, $C2:$BA2/(($C$1:$BA$1=J$6)*($B2:$AZ2<>"U")), 1)
在包括或排除限定符时，它们都覆盖了最小值和最大值.
Those cover both minimum and maximum values when either including or excluding the qualifier.
Addendum: Excluding blank cells
还有一个条件可以检查 SIGN中功能.
One more condition to check the LEN of the values can be added. To change the length of the value into a divide-by-1 (unchanged) or divide-by-0 (#DIV/)! error) wrap the LEN in the SIGN function.
=AGGREGATE(15, 6, $A2:$AY2/(SIGN(LEN($A2:$AY2))*($A$1:$AY$1=C$6)*($B2:$Z2="U")), 1)
我保留了 仅将SMALL 子功能作为AGGREGATE的子功能14并将其向上处理为数组.
I'm retaining the SMALL sub-function as only AGGREGATE's sub-functions 14 and up process as an array.
这篇关于水平定向数据的条件最大/最小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
