Excel 公式查找表中包含值的最右边的列

Question

我在 Excel 电子表格中有一些数据结构如下：

    A B C D E F

1   1 1 2 x 2 3
2   1 1 1 2 2 3
3   3 3 3 3 4 4

我正在尝试制定一个 Excel 公式，该公式将为我提供此表中最右列的索引，该表中有一个与特定值匹配的单元格。

在此示例中，包含值“1”的最右边的列位于 C 列中。对于“2”，它将是 E。该列索引就是我所追求的。

我使用列字母是为了与 Excel 一致，但最好使用数字列索引。

我已经尝试了一些其他解决方案来解决在线发现的类似 Excel 问题，但它们的行为并不完全正确。

Answer 1

如果你想使用辅助列，你可以把这个公式放在 G1 中

{=MAX((COLUMN(A1:F1)*(A1:F1=2)))}

输入的数组中。向下填充至G3。在 G4 中，输入

=MAX(G1:G3)

然后对每个数字重复。如果您不需要辅助列，您可以编写一个像这样的 UDF，

Public Function MaxColumn(rInput As Range, vValue As Variant) As Long

    Dim rFound As Range

    Set rFound = rInput.Find(vValue, rInput.Cells(1), xlValues, xlWhole, xlByColumns, xlPrevious)

    If Not rFound Is Nothing Then MaxColumn = rFound.Column

End Function

您可以这样调用

=maxcolumn(A1:F3,2)

Answer 2

这是一种用公式来实现的方法。我将展示如何使用几个不同的公式来展示逻辑步骤，然后将它们组合成一个大公式。

首先，每列使用一个公式来查看目标值是否在该列中。例如，在 A 列中：

=COUNTIF(A1:A100,Goal)
=COUNTIF(B1:B100,Goal)
...
(where Goal can be a hardcoded search value,
 or a named range where you type your query)

然后，将 IF 语句添加到这些公式中，将其转换为列号。如果列中存在查询，则显示列号，否则显示零。

=IF(COUNTIF(A1:A100,Goal)>0, 1, 0)
=IF(COUNTIF(B1:B100,Goal)>0, 2, 0)
...

最后，添加一个公式以从先前的公式中获取最大列数。这将等于最右边包含您的查询值的列。

=MAX( IF(COUNTIF(A1:A100,Goal)>0, 1, 0), IF(COUNTIF(B1:B100,Goal)>0, 2, 0), ...)

Answer 3

怎么样：

Function FindCol(ToFind)
Dim r As Range
Dim rfind As Range
Dim rfound As Range
Set r = ActiveSheet.UsedRange

For i = r.Columns.Count To 1 Step -1
    Set rfind = r.Columns(i)
    Set rfound = rfind.Find(ToFind)
    If Not rfound Is Nothing Then
        Result = rfound.Column
        Exit For
    End If
Next

FindCol = Result

End Function

Answer 4

这是使用新的 LAMBDA 和 BYROW 函数的解决方案。

BYROW 查看设定范围内的每一行，并返回目标编号所在的最右侧的列索引号。 IFERROR 用于捕获目标编号不存在的任何错误，MAX 用于返回最大编号/最右侧索引列。

=LET(
goal, $H$1,
range, A1:F3,
MAX(IFERROR(BYROW(range,LAMBDA(x,XMATCH(TRUE,INDEX(x=goal,),0,-1))),0)))

完整教程在这里