Формула Excel для поиска крайнего правого столбца, содержащего значение в таблице

У меня есть некоторые данные, структурированные таким образом в электронной таблице 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, найденных в Интернете, но они не совсем корректны.


excel formula excel-formula
person joelpt    schedule 24.01.2011    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Вот способ сделать это с помощью формул. Я покажу, как это сделать с помощью нескольких разных формул, чтобы показать шаги логики, а затем объединю их в одну большую формулу.

Во-первых, используйте одну формулу для каждого столбца, чтобы узнать, находится ли целевое значение в столбце. Например, в столбце 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), ...)
person Steven    schedule 24.01.2011

arrow_upward
3
arrow_downward

Если вы хотите использовать вспомогательные столбцы, вы можете поместить эту формулу в 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)
person Dick Kusleika    schedule 24.01.2011

arrow_upward
2
arrow_downward

Как насчет:

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
person Fionnuala    schedule 24.01.2011