Formula generică
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
rezumat
Pentru a găsi valoarea maximă într-o lună dată, puteți utiliza funcția MAXIFS sau una dintre celelalte alternative de mai jos. În exemplul prezentat, formula din G5 este:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
În cazul în care vânzările (D5: D15), datele (B5: B15) și valorile (C5: C15) sunt denumite intervale.
Explicaţie
Funcția MAXIFS poate găsi valoarea maximă într-un interval bazat pe unul sau mai multe criterii. În exemplul prezentat, folosim MAXIFS pentru a găsi valoarea maximă a vânzărilor bazată într-o lună dată prin date de „parantezare” între prima zi a lunii și ultima zi a lunii. Primele criterii verifică dacă datele sunt mai mari sau egale cu prima din lună:
dates,">="&G4,dates // first of month
Notă: presupunem că data din G4 este o dată „prima lună”.
Al doilea criteriu verifică dacă datele sunt mai mici sau egale cu ultima lună, calculate cu funcția EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Când ambele criterii returnează TRUE, data este în luna dată, iar MAXIFS returnează valoarea maximă a datelor care îndeplinesc criteriile.
Formule alternative
Dacă versiunea dvs. de Excel nu include funcția MAXIFS, există și alte opțiuni. O opțiune este o formulă simplă de matrice bazată pe funcțiile MAX și IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Notă: Aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
O altă opțiune este de a utiliza funcția AGGREGATE astfel:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Ambele opțiuni de mai sus utilizează o metodă mai simplă de testare a datelor bazată pe funcția TEXT, care utilizează un format numeric pentru a extrage luna și anul dintr-o dată. Funcția TEXT nu poate fi utilizată astfel în interiorul funcției MAXIFS, deoarece MAXIFS necesită un interval. Aceste abordări alternative sunt explicate mai detaliat aici.
Clientul maxim
Pentru a recupera clientul asociat cu valoarea maximă într-o lună dată, formula din G6 este:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Ideea aici este că știm deja valoarea maximă într-o lună (G5) și putem folosi acea valoare ca „cheie” pentru a căuta clientul. Rețineți că trebuie să repetăm logica utilizată pentru a izola valorile în luna dată, pentru a evita o potrivire falsă într-o lună diferită.
La fel ca alternativele menționate mai sus, această formulă folosește și un test simplificat pentru date bazat pe funcția TEXT. Citiți mai multe despre INDEX și MATCH cu mai multe criterii aici.
Pentru o formulă all-in-one, înlocuiți G5 cu una dintre opțiunile de formulă explicate mai sus pentru a determina valoarea maximă într-o lună dată.
