Formula generică
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
rezumat
Pentru a obține valoarea maximă într-un set de date pe baza mai multor criterii, puteți utiliza o formulă matrice bazată pe funcțiile MAX și IF. În exemplul prezentat, formula din I6 este:
(=MAX(IF(color=G6,IF(item=H6,price))))
Cu o culoare „roșu” și elementul „pălărie”, rezultatul este de 11,00 USD
Notă: Aceasta este o formulă matrice și trebuie introdusă folosind Ctrl + Shift + introdus
Explicaţie
Acest exemplu folosește următoarele intervale denumite: „culoare” = B6: B14, „articol” = C6: C14 și „preț” = E6: E14. Scopul este de a găsi prețul maxim pentru o anumită culoare și articol.
Această formulă utilizează două funcții IF imbricate, înfășurate în MAX pentru a returna prețul maxim cu două criterii. Începând cu un test logic al primei instrucțiuni IF, culoare = G6, valorile din intervalul numit „culoare” (B6: B14) sunt verificate în raport cu valoarea din celula G6, „roșu”. Rezultatul este o matrice ca aceasta:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
În testul logic pentru cea de-a doua instrucțiune IF, item = H6, valorile din articolul de interval denumit (C6: C14) sunt verificate în raport cu valoarea din celula H6, „hat”. Rezultatul este o matrice ca aceasta:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
„Valoarea dacă este adevărată” pentru a doua instrucțiune IF este intervalul denumit „prețuri” (E6: E14), care este o matrice ca aceasta:
(11;8;9;12;9;10;9;8;7)
Un preț este returnat pentru fiecare articol din acest interval numai atunci când rezultatul primelor două tablouri de mai sus este ADEVĂRAT pentru articolele aflate în poziții corespunzătoare. În exemplul prezentat, matricea finală din MAX arată astfel:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Rețineți că singurele prețuri care „supraviețuiesc” sunt cele aflate într-o poziție în care culoarea este „roșie” și elementul este „pălărie”.
Funcția MAX returnează apoi cel mai mare preț, ignorând automat valorile FALSE.
Sintaxă alternativă folosind logica booleană
De asemenea, puteți utiliza următoarea formulă matrice, care utilizează o singură funcție IF împreună cu logica booleană:
(=MAX(IF((color=G6)*(item=H6),price)))
Avantajul acestei sintaxi este că este mai ușor să adăugați criterii suplimentare fără a adăuga funcții IF imbricate suplimentare. Dacă aveți nevoie de logică SAU, utilizați adunarea în loc de multiplicarea între condiții.
Cu MAXIFS
Funcția MAXIFS, introdusă în Excel 2016, este concepută pentru a calcula maximele pe baza unuia sau mai multor criterii fără a fi nevoie de o formulă matrice. Cu MAXIFS, formula din I6 este:
=MAXIFS(price,color,G6,item,H6)
Notă: MAXIFS va ignora automat celulele goale care îndeplinesc criteriile. Cu alte cuvinte, MAXIFS nu va trata celulele goale care îndeplinesc criteriile ca zero. Pe de altă parte, MAXIFS va returna zero (0) dacă nicio celulă nu corespunde criteriilor.
