Есть ли способ получить максимальное значение диапазона значений, используя стандартные функции Excel?

Больше информации

  • Я хочу найти максимальное значение в диапазоне ячеек. A1: =max(A2:A10)
  • Однако я не хочу находить максимум значения в самих ячейках, а скорее связанный с ним набор значений, полученный путем выполнения vlookup для этих ячеек. то есть B2: =vlookup(A2,MyLookupName,2,false) (скопируйте формулу B2 до B10). A1: =max(B2:B10) .
  • Вместо того, чтобы делать это за 2 шага (т.е. выполнить vlookup в столбце B, а затем сделать максимум по сравнению с ячейкой A1), я бы предпочел иметь одну формулу, которая применяет оба шага в одном, в той же ячейке. т.е. A1: {=max(vlookup(A2:A10,MyLookupName,2,false))}

Однако эта последняя формула не работает, поскольку функция VLOOKUP принимает только одну ячейку для параметра Lookup_Value ; если диапазон пройден, он берет верхнюю левую ячейку из этого диапазона.

VBA Solution

Чтобы лучше проиллюстрировать функциональность, к которой я стремлюсь, я кодировал функцию рабочего листа VBA, которая будет делать то, что мне нужно; Однако я пытаюсь избежать пользовательских макросов.

Public Function MaxVLookup(ByRef Lookup_Array As Range, ByRef Table_Array As Range, Col_Index_Num As Long) As Variant
    Dim v As Variant, temp As Variant, max As Variant
    max = Null
    For Each v In Lookup_Array
        temp = WorksheetFunction.VLookup(v, Table_Array, Col_Index_Num, False)
        If IsNull(max) Then
            max = temp
        Else
            If max < temp Then
                max = temp
            End If
        End If
    Next
    MaxVLookup = max
End Function

Пример данных

2 ответа2

1

Если значения в A находятся в справочной области только один раз, тогда вы можете использовать SUMUIF(), заключенный в MAX(), в качестве формулы массива:

=MAX(SUMIF(D:D,A1:A10,E:E))

Будучи формулой массива, она должна быть подтверждена с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, тогда Excel поместит {} вокруг формулы.


Если они кратны, и вы хотите только первое, как VLOOKUP, используйте LOOKUP():

=MAX(IFERROR(LOOKUP(A1:A10,D1:D13,E1:E13),-1E+99))

Опять же, это формула массива.

Будучи формулой массива, она должна быть подтверждена с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, тогда Excel поместит {} вокруг формулы.

Скриншот на примере

Скриншот на основе примера

0

NB: я нашел альтернативное решение моей проблемы. Поскольку мне нужно было только сопоставить каждое значение с числом, я смог подделать VLookup с помощью Match.

то есть A1: {=max(6-match(A2:A10,MyLookupName,0))} .

6- - просто изменить значение, возвращаемое соответствием (то есть положение элемента, который я ищу в списке значений MyLookupName ), на значение, которое я сопоставляю.

Демо Скриншот

Решение с ценностями:

Решение с ценностями

Решение с формулами:

Решение с формулами


NB: Я не помечал это как ответ, так как, хотя он решил мою проблему, он не решает все проблемы этого типа / обходит ответ на вопрос, который я задал.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .