Formuła ogólna
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Podsumowanie
Aby uzyskać maksymalną wartość w zestawie danych na podstawie więcej niż jednego kryterium, możesz użyć formuły tablicowej opartej na funkcjach MAKS. I JEŻELI. W pokazanym przykładzie formuła w I6 to:
(=MAX(IF(color=G6,IF(item=H6,price))))
Przy kolorze „czerwonym” i pozycji „kapelusz” wynik wynosi 11,00 $
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą kombinacji Ctrl + Shift + Enter
Wyjaśnienie
W tym przykładzie zastosowano następujące nazwane zakresy: „kolor” = B6: B14, „pozycja” = C6: C14 i „cena” = E6: E14. Celem jest znalezienie maksymalnej ceny za dany kolor i przedmiot.
Ta formuła używa dwóch zagnieżdżonych funkcji JEŻELI, opakowanych wewnątrz wartości MAX, aby zwrócić maksymalną cenę z dwoma kryteriami. Rozpoczynając od testu logicznego pierwszej instrukcji JEŻELI, kolor = G6, wartości w nazwanym zakresie „kolor” (B6: B14) są porównywane z wartością w komórce G6, „czerwony”. Rezultatem jest tablica taka:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
W teście logicznym dla drugiej instrukcji JEŻELI, pozycja = H6, wartości z nazwanego elementu zakresu (C6: C14) są porównywane z wartością w komórce H6, „kapelusz”. Rezultatem jest tablica taka:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
„Wartość, jeśli prawda” dla drugiej instrukcji JEŻELI to nazwany zakres „ceny” (E6: E14), który jest tablicą taką jak ta:
(11;8;9;12;9;10;9;8;7)
Cena jest zwracana za każdy przedmiot w tym zakresie tylko wtedy, gdy wynik dwóch pierwszych tablic powyżej jest PRAWDA dla pozycji na odpowiednich pozycjach. W pokazanym przykładzie ostateczna tablica wewnątrz MAX wygląda następująco:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Zauważ, że jedynymi cenami, które „przetrwają”, są ceny w pozycji, w której kolor jest „czerwony”, a przedmiot to „kapelusz”.
Funkcja MAX zwraca następnie najwyższą cenę, automatycznie ignorując wartości FALSE.
Alternatywna składnia wykorzystująca logikę boolowską
Możesz również użyć następującej formuły tablicowej, która używa tylko jednej funkcji JEŻELI wraz z logiką boolowską:
(=MAX(IF((color=G6)*(item=H6),price)))
Zaletą tej składni jest to, że łatwiej jest dodawać dodatkowe kryteria bez dodawania dodatkowych zagnieżdżonych funkcji JEŻELI. Jeśli potrzebujesz logiki OR, użyj dodawania zamiast mnożenia między warunkami.
Z MAXIFS
Funkcja MAXIFS, wprowadzona w programie Excel 2016, służy do obliczania wartości maksymalnych na podstawie co najmniej jednego kryterium bez konieczności stosowania formuły tablicowej. W przypadku MAXIFS formuła w I6 to:
=MAXIFS(price,color,G6,item,H6)
Uwaga: MAXIFS automatycznie zignoruje puste komórki, które spełniają kryteria. Innymi słowy, MAXIFS nie będzie traktować pustych komórek, które spełniają kryteria, jako zero. Z drugiej strony MAXIFS zwróci zero (0), jeśli żadna komórka nie spełnia kryteriów.
