问题描述
在互联网上进行了深入搜索之后，我放弃了.
after a deep search on the internet i gave up.
我的简单"问题是:如何在公式中(最好在MATCH中)添加两个范围?
My "simple" question would be:How can I add two ranges in a formula, preferably in MATCH?
我要搜索A1:A7 + A9:A20之类的范围，因此不在我的范围内包括A8.
I want to search a range like A1:A7 + A9:A20 and thus not include A8 in my range.
这可能吗?请帮帮我
推荐答案
本来就不能，但是您可以尝试通过以下任一方法绕过它:
Natively you can't but you could try to bypass it with either:
排除单个单元格:
Exclude a single cell:
如果要从MATCH中排除某个单元格，可以像这样排除其特定的行号:
If you want to exclude a certain cell from a MATCH you can exclude it's certain row number like so:
=MATCH(1,(A1:A20="X")*(ROW(A1:A20)<>8),0)
或
=MATCH(1,INDEX((A1:A20="X")*(ROW(A1:A20)<>8),),0)
都是数组公式，但是只有第一个需要通过按住 + 来确认，然后按
Both are array formulas but only the first one needs to be confirmed by holding down + before pressing
排除一系列单元格:
Exclude a range of cells:
如果您要排除一定范围的行，例如8-12，那么我们不能使用AND或OR逻辑，因为它们不返回数组.但是，我们可以自己模仿它:
If you would want to exclude a range of rows, e.g. 8-12, then we cannot use the AND or OR logic as they don't return an array. However, we can mimic it ourselves like:
=MATCH(1,(A1:A20="x")*((ROW(A1:A20)<8)+(ROW(A1:A20)>12)),0)
或
=MATCH(1,INDEX((A1:A20="x")*((ROW(A1:A20)<8)+(ROW(A1:A20)>12)),),0)
同样，这两个都是数组公式，但是只有第一个需要通过按住 + 来确认，然后按
Where, again, both are array formulas but only the first one needs to be confirmed by holding down + before pressing
替代
Alternative
或者，您可以通过多种方式堆叠MATCH函数，但这是其中之一:
Alternatively, you could stack MATCH functions in a number of ways, but here is one of them:
=IFERROR(MATCH("x",A1:A7,0),MATCH("x",A13:A20,0))
- 专业人士:没有数组公式>快速！
- Con:将返回匹配项在第一个或第二个范围内的相对行位置.在此示例中，您只需在第二个结果中添加12.
祝你好运=)
这篇关于没有特定CELL的Excel MATCH范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！