Generička formula
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Sažetak
Da biste pronašli maksimalnu vrijednost u određenom mjesecu, možete upotrijebiti funkciju MAXIFS ili neku od ostalih alternativa u nastavku. U prikazanom primjeru formula u G5 je:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Gdje su prodaja (D5: D15), datumi (B5: B15) i vrijednosti (C5: C15) imenovani rasponi.
Obrazloženje
Funkcija MAXIFS može pronaći maksimalnu vrijednost u rasponu na temelju jednog ili više kriterija. U prikazanom primjeru koristimo MAXIFS za pronalaženje maksimalne vrijednosti prodaje temeljene u određenom mjesecu "zapisivanjem" datuma između prvog dana u mjesecu i zadnjeg dana u mjesecu. Prvi kriterij provjerava jesu li datumi veći ili jednaki prvom u mjesecu:
dates,">="&G4,dates // first of month
Napomena: pretpostavljamo da je datum u G4 datum "prvog u mjesecu".
Drugi kriterij provjerava jesu li datumi manji ili jednaki posljednjem u mjesecu, izračunato s funkcijom EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Kad oba kriterija vrate TRUE, datum je u danom mjesecu, a MAXIFS vraća maksimum vrijednosti datuma koji ispunjavaju kriterije.
Alternativne formule
Ako vaša verzija Excela ne uključuje funkciju MAXIFS, postoje druge mogućnosti. Jedna od mogućnosti je jednostavna formula niza koja se temelji na funkcijama MAX i IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Napomena: Ovo je formula niza i mora se unijeti sa control + shift + enter.
Druga mogućnost je uporaba funkcije AGGREGATE poput ove:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Obje opcije koriste jednostavniju metodu testiranja datuma na temelju funkcije TEKST koja koristi format broja za izdvajanje mjeseca i godine iz datuma. Funkcija TEXT ne može se koristiti na ovaj način unutar funkcije MAXIFS, jer MAXIFS zahtijeva raspon. Ovi su alternativni pristupi ovdje detaljnije objašnjeni.
Maksimalni klijent
Da bi se dohvatio klijent povezan s maksimalnom vrijednošću u danom mjesecu, formula u G6 je:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Ovo je formula niza i mora se unijeti sa control + shift + enter.
Ideja je ovdje da već znamo maksimalnu vrijednost u mjesecu (G5) i tu vrijednost možemo koristiti kao "ključ" za traženje klijenta. Napomena moramo ponoviti logiku koja se koristi za izoliranje vrijednosti u danom mjesecu kako bismo izbjegli pogrešno podudaranje u drugom mjesecu.
Kao i gore spomenute alternative, i ove formule koriste pojednostavljeni test za datume na temelju funkcije TEKST. Ovdje pročitajte više o INDEKSU i UMAKNICI s više kriterija.
Za cjelovitu formulu zamijenite G5 jednom od gore opisanih opcija formule za određivanje maksimalne vrijednosti u danom mjesecu.
