没有特定CELL的Excel MATCH范围 [英] Excel MATCH range without specific CELL

问题描述

在互联网上进行了深入搜索之后，我放弃了.

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)

都是数组公式，但是只有第一个需要通过按住 Ctrl + Shift 来确认，然后按 Enter

Both are array formulas but only the first one needs to be confirmed by holding down Ctrl+Shift before pressing Enter

排除一系列单元格:

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)

同样，这两个都是数组公式，但是只有第一个需要通过按住 Ctrl + Shift 来确认，然后按 Enter

Where, again, both are array formulas but only the first one needs to be confirmed by holding down Ctrl+Shift before pressing Enter

替代

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范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！