Formuła ogólna
=MAX(INDEX(data,0,MATCH(column,header,0)))
Podsumowanie
Aby pobrać maksymalną wartość w zestawie danych, w którym kolumna jest zmienna, możesz użyć funkcji INDEKS i PODAJ.POZYCJĘ razem z funkcją MAX. W przedstawionym przykładzie formuła w J5 to:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
gdzie dane (B5: F15) i nagłówek (B4: F4) to nazwane zakresy.
Wyjaśnienie
Uwaga: Jeśli jesteś nowym użytkownikiem INDEKSU i PODAJANIA, zobacz: Jak używać INDEKSU i PODAJANIA
W standardowej konfiguracji funkcja INDEKS pobiera wartość z danego wiersza i kolumny. Na przykład, aby uzyskać wartość w wierszu 2 i kolumnie 3 w podanym zakresie:
=INDEX(range,2,3) // get value at row 2, column 3
Jednak INDEX ma specjalną sztuczkę - możliwość pobierania całych kolumn i wierszy. Składnia obejmuje podanie zera dla argumentu „inne”. Jeśli chcesz mieć całą kolumnę, podaj wiersz jako zero. Jeśli chcesz mieć cały wiersz, podaj kolumnę jako zero:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
W przedstawionym przykładzie chcemy znaleźć maksymalną wartość w danej kolumnie. Skrętem jest to, że kolumna musi być zmienna, aby można ją było łatwo zmienić. W F5 formuła to:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Pracując od wewnątrz, najpierw używamy funkcji MATCH, aby uzyskać „indeks” kolumny żądanej w komórce J4:
MATCH(J4,header,0) // get column index
Przy „zielonym” w J4 funkcja PODAJ.POZYCJĘ zwraca 3, ponieważ zielony jest trzecią wartością w nazwanym nagłówku zakresu . Po zwróceniu wyniku przez funkcję MATCH wzór można uprościć do tego:
=MAX(INDEX(data,0,3))
Gdy jako numer wiersza podano zero, INDEKS zwraca wszystkie wartości w kolumnie 3 danych zakresu nazwanego . Wynik jest zwracany do funkcji MAX w tablicy takiej jak ta:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
MAX zwraca wynik końcowy, 93.
Minimalna wartość
Aby uzyskać minimalną wartość z kolumną zmiennej, możesz po prostu zastąpić funkcję MAX funkcją MIN. Wzór w J6 to:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Z FILTREM
Do rozwiązania tego problemu można również użyć nowej funkcji FILTER, ponieważ FILTER może filtrować dane według wiersza lub kolumny. Sztuczka polega na skonstruowaniu logicznego filtru, który wykluczy inne kolumny. LICZ.JEŻELI działa dobrze w tym przypadku, ale musi być skonfigurowane „wstecz”, z J4 jako zakresem i nagłówkiem dla kryteriów:
=MAX(FILTER(data,COUNTIF(J4,header)))
Po uruchomieniu COUNTIF mamy:
=MAX(FILTER(data,(0,0,1,0,0)))
A FILTER dostarcza trzecią kolumnę do MAX, tak samo jak powyższa funkcja INDEKS.
Zamiast COUNTIF możesz użyć ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Funkcja MATCH jest ponownie ustawiana „wstecz”, dzięki czemu otrzymujemy tablicę z 5 wartościami, która posłuży jako filtr logiczny. Po wykonaniu ISNUMBER i MATCH mamy:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
I FILTER ponownie dostarcza trzecią kolumnę do MAX.
