Splošna formula
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Povzetek
Če želite poiskati največjo vrednost v določenem mesecu, lahko uporabite funkcijo MAXIFS ali eno od spodnjih možnosti. V prikazanem primeru je formula v G5:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Kjer so prodaja (D5: D15), datumi (B5: B15) in vrednosti (C5: C15) imenovani obsegi.
Pojasnilo
Funkcija MAXIFS lahko najde največjo vrednost v območju na podlagi enega ali več meril. V prikazanem primeru z MAXIFS poiščemo največjo prodajno vrednost, ki temelji na določenem mesecu, in sicer z datumi "postavljanja v oklepaje" med prvim dnem v mesecu in zadnjim dnem v mesecu. Prvo merilo preveri, ali so datumi daljši ali enaki prvemu v mesecu:
dates,">="&G4,dates // first of month
Opomba: predvidevamo, da je datum v G4 datum "prvega v mesecu".
Drugo merilo preverja, ali so datumi manjši ali enaki zadnjemu v mesecu, izračunano s funkcijo EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Ko oba merila vrneta TRUE, je datum v danem mesecu, MAXIFS pa vrne največjo vrednost datumov, ki izpolnjujejo merila.
Alternativne formule
Če vaša različica Excela ne vključuje funkcije MAXIFS, obstajajo druge možnosti. Ena od možnosti je preprosta formula matrike, ki temelji na funkcijah MAX in IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Opomba: To je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Druga možnost je uporaba funkcije AGGREGATE, kot je ta:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Obe možnosti zgoraj uporabljata enostavnejšo metodo testiranja datumov, ki temelji na funkciji TEXT, ki s številčno obliko izvleče mesec in leto iz datuma. Funkcije TEXT ni mogoče uporabiti tako znotraj funkcije MAXIFS, ker MAXIFS zahteva obseg. Ti alternativni pristopi so tukaj podrobneje razloženi.
Največja stranka
Za pridobitev odjemalca, povezanega z največjo vrednostjo v določenem mesecu, je formula v G6:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
To je formula matrike, ki jo je treba vnesti s tipko control + shift + enter.
Ideja tukaj je, da že poznamo največjo vrednost v mesecu (G5) in jo lahko uporabimo kot "ključ" za iskanje odjemalca. Upoštevajte, da moramo ponoviti logiko, uporabljeno za izolacijo vrednosti v danem mesecu, da se izognemo napačnemu ujemanju v drugem mesecu.
Tako kot zgoraj omenjene alternative tudi te formule uporabljajo poenostavljeni test za datume, ki temelji na funkciji TEXT. Tukaj preberite več o INDEKSU in UJEMANJU z več merili.
Za formulo vse v enem zamenjajte G5 z eno od zgornjih možnosti formule, da določite največjo vrednost v določenem mesecu.
