Excel MAXIF 函数还是仿真? [英] Excel MAXIF function or emulation?
问题描述
我在 excel 中有一个中等大小的数据集我希望从中提取 B 列中值的最大值,但那些仅对应于 A 列中满足某些条件的单元格的值.
I have a moderately sized dataset in excel from which I wish to extract the maximum value of the values in Column B, but those that correspond only to cells in Column A that satisfy certain criteria.
所需的功能类似于 SUMIF
或 COUNTIF
的功能,但两者都没有返回必要的数据.没有 MAXIF
函数;我该如何模仿?
The desired functionality is similar to that of SUMIF
or COUNTIF
, but neither of those return data that is necessary. There isn't a MAXIF
function; how do I emulate one?
推荐答案
您可以使用数组公式.在要计算最大值的单元格中输入:=Max(If([test],[if true],[if false]) 用测试替换方括号中的值,如果为真返回什么,如果为假返回什么.例如:
You can use an array formula.In the cell in which you want the max calculated enter: =Max(If([test],[if true],[if false]) where you replace the values in square brackets with the test, what to return if true and what to return if false. For example:
=MAX(IF(MOD(A2:A25,2)=0,A2:A25,0)
在这个公式中,如果值除以 2 没有余数,我将返回 A 列中的值.请注意,我在比较和值为 false 时使用了一系列单元格,而不是单个单元格.
In this formula I return the value in column A if the value divided by 2 has no remainder. Notice that I use a range of cells in my comparison and in the value if false rather than a single cell.
现在,在编辑单元格的同时,按 Ctrl+Shift+Enter(同时按住 Ctrl 键和 Shift,然后按 Enter).
Now, while still editing the cell, hit Ctrl+Shift+Enter (hold down the Ctrl key and the Shift together and then hit enter).
这将创建一个数组公式,该公式作用于范围内的每个值.
This creates an array formula that acts on each value in the range.
EDIT 顺便说一句,您是要以编程方式还是手动执行此操作?如果以编程方式,那么您使用的是什么环境?VBA?C#?
EDIT BTW, did you want to do this programmatically or manually? If programmatically, then what environment are you using? VBA? C#?
EDIT 如果通过 VBA,您需要像这样使用 FormulaArray 属性和 R1C1 引用:
EDIT If via VBA, you need to use the FormulaArray property and R1C1 references like so:
Range("A1").Select
Selection.FormulaArray = "=MAX(IF(MOD(R[1]C:R[24]C,2)=0,R[1]C:R[24]C,0))"
这篇关于Excel MAXIF 函数还是仿真?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!