Excel反向匹配 [英] Excel Reverse Match

问题描述

我试图找到一种在Excel中进行反向匹配的方法。我有一列TRUE / FALSE语句，我需要找到一个方法来在这一列中找到匹配和反向匹配，以找到 TRUE 语句之间的差异两个方向。

I'm trying to find a way to do a reverse match in Excel. I have a column of TRUE/FALSE statements and I need to find a way to find both a match and a reverse match within this one column to find the difference between TRUE statements in both directions.

True
False
False
False
True
False
False
True

在这种情况下，从中间True语句正常的 MATCH 函数下降将返回一个4.我正在尝试找到一个额外的匹配函数，向上看到 TRUE 语句并返回值5

In this scenario from the perspective of the middle True statement the normal MATCH function going down would return a number of 4. I am trying to find an additional match function that would look upward to the TRUE statement and return a value of 5

推荐答案

快速而脏：

A1：A7 ：TRUE / FALSE

A8 ：数组公式： {= LARGE（IF（A1：A7 = TRUE; ROW（A1：A7）;）; 1）}

A1:A7: TRUE/FALSE
A8: Array formula: {=LARGE(IF(A1:A7=TRUE;ROW(A1:A7);"");1)}

结果将是从下面最接近的 TRUE 的行号。
公式通过Shift-Ctrl-Enter在公式窗口中插入。弯曲的括号由Excel插入，而不是由用户插入。

The result will be the row number of the nearest TRUE from below. The formula is inserted by Shift-Ctrl-Enter in a formula window. Curled brackets are inserted by Excel, not by a user.

如果您需要从最后一个 TRUE转换为TRUE ：
{= LARGE（IF（A1：A7 = TRUE; ROW（A1：A7）;）; 2）}

If you need the shift from the last TRUE: {=LARGE(IF(A1:A7=TRUE;ROW(A1:A7);"");2)}

更改公式中的最后一个数字会将您从下面移动到第n个 TRUE 。

Changing the last number in the formula moves you to the n-th TRUE from below.

用 SMALL 替换 LARGE 将会从上面给出行号。因此，可以通过组合公式来找到相关组合之间的转移。

Replacing LARGE with SMALL will give the row numbers from above. So it would possible to find the shift between relevant combinations by combining the formulae.

这篇关于Excel反向匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！