问题描述
我有一个正在制作的电子表格，如下所示:
I have a spreadsheet that I am making that looks as follows:
Index Diff Exc Sym Sec Result Criteria Met
3.42 -2.07 0.86 0.92 1.83 1.95
-0.38 -2.93 0.87 0.23 -2.01 0.09
-2.67 -1.84 0.87 -2.49 -3.48 1.32
-0.65 -0.98 0.46 0.98 -2.01 0.00
-0.73 -2.79 -1.07 -2.15 -1.44 -0.10
0.15 2.33 -0.46 -0.66 3.17 0.38 0.38
0.90 -3.68 -0.72 -1.01 -1.36 1.69
0.68 -1.12 -0.36 0.73 -1.34 -0.29
-1.19 -1.70 -0.56 -1.31 1.45 0.49
-0.45 -0.69 -0.56 -1.22 0.00 -0.49
2.94 8.38 2.21 6.25 4.96 1.74
-1.04 7.36 2.59 3.00 2.17 2.97
1.21 1.73 3.05 1.48 3.56 0.77
-1.10 1.86 0.60 1.18 1.07 -0.49
-0.89 -3.19 -1.78 -2.24 -4.26 -0.81
-1.17 -3.44 0.11 -1.22 3.66 0.36
0.52 0.92 -1.02 0.38 1.96 -1.40 -1.40
-0.90 3.01 -0.86 0.62 0.97 -0.50 -0.50
2.78 1.46 0.00 0.47 1.95 0.84
Max Min
Index 2.00 -2.00
Diff 10.00 0.00
Exc 0.00 -10.00
Sym 10.00 -10.00
Sec 20.00 0.00
在标题索引下，Diff，Exc，Sym，Sec，Result是所有数据，在符合条件"列中，我有一个公式可以检查先前的标题是否在下面较小表格的最大和最小范围内，以及如果他们这样做，它将发布结果，如果它们都不都在最大和最小边界之内，则将其留为空白.我是通过使用以下公式来做到这一点的:
Under the headings Index, Diff, Exc, Sym, Sec, Result is all data, In the criteria met column i have a formula that checks if the prior headings fall within the Max and Min limits of the smaller table underneath, and if they do it posts the result, if they dont all fall within the Max and Min boundaries it leaves it blank. I did that by using this formula:
=IF(AND(A3<$B$24,A3>$C$24,B3<$B$25,B3>$C$25,C3<$B$26,C3>$C$26,D3<$B$27,D3>$C$27,E3<$B$28,E3>$C$28),F3,"")
复制满足条件的列.它可以很好地实现我希望实现的目标，但是随着此电子表格的增长以及我添加更多列，它似乎效率极低，并且容易出现很多人为错误.除了使用更有效的公式之外，是否有办法获得相同的结果?
copied down the criteria met column. It works perfectly fine for what I want it to achieve but as this spreadsheet grows and I add more columns it seems like it will be incredibly inefficient and prone to alot of human error. Is there a way to achieve the same results but by using a more efficient formula?
图片以供参考:
推荐答案
尝试以下数组公式:
=IF(SUM((A3:E3<=TRANSPOSE($B$24:$B$28))*(A3:E3>= TRANSPOSE($C$24:$C$28)))=COLUMNS(A3:E3),F3,"")
作为数组公式，退出编辑模式时必须通过Ctrl-Shift-Enter进行确认.正确完成后，Excel会自动在公式周围放置{}来表示数组公式.
Being an Array Formula it must be confirmed with Ctrl-Shift-Enter when exiting Edit mode. When done correctly Excel will automatically put {} around the formula to denote an array formula.
一个警告，要比较的列必须与最小/最大行的顺序相同.
One caveat, the Columns to be compared need to be in the same order as the min/max rows.
而且，正如几乎每个人都说过的，如果将Min Max转置为列中的行，则将减少使用CSE数组公式的需要.以下将起作用:
Also, as has been stated by nearly everyone if the Min Max were transposed to rows from column it would alleviate needing to use a CSE array formula. The following would work:
=IF(SUMPRODUCT((A5:E5>=$A$3:$E$3)*(A5:E5<=$A$2:$E$2))=COLUMNS(A5:E5),F5,"")
这篇关于Excel，针对多个IF ANDS的更有效公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！