Vispārēja formula
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Kopsavilkums
Lai atrastu maksimālo vērtību attiecīgajā mēnesī, varat izmantot funkciju MAXIFS vai kādu no citām zemāk esošajām alternatīvām. Parādītajā piemērā formula G5 ir:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Kur pārdošana (D5: D15), datumi (B5: B15) un vērtības (C5: C15) ir nosaukti diapazoni.
Paskaidrojums
Funkcija MAXIFS var atrast maksimālo vērtību diapazonā, pamatojoties uz vienu vai vairākiem kritērijiem. Parādītajā piemērā mēs izmantojam MAXIFS, lai atrastu maksimālo pārdošanas vērtību, pamatojoties uz konkrēto mēnesi, "iekavās" norādot datumus starp mēneša pirmo dienu un mēneša pēdējo dienu. Pirmie kritēriji pārbauda, vai datumi ir lielāki vai vienādi ar mēneša pirmo:
dates,">="&G4,dates // first of month
Piezīme. Mēs pieņemam, ka G4 datums ir “pirmā mēneša” datums.
Otrais kritērijs pārbauda, vai datumi ir mazāki vai vienādi ar mēneša pēdējo, aprēķinot ar funkciju EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Kad abi kritēriji atgriež vērtību TRUE, datums ir attiecīgajā mēnesī, un MAXIFS atgriež kritērijiem atbilstošu datumu maksimālo vērtību.
Alternatīvās formulas
Ja jūsu Excel versijā nav iekļauta funkcija MAXIFS, ir arī citas iespējas. Viena iespēja ir vienkārša masīva formula, kuras pamatā ir MAX un IF funkcijas:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Piezīme: Šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter.
Vēl viena iespēja ir izmantot funkciju AGGREGATE šādi:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Abās iepriekš minētajās opcijās tiek izmantota vienkāršāka datumu testēšanas metode, kuras pamatā ir TEXT funkcija, kurā tiek izmantots ciparu formāts, lai mēnesi un gadu izvelk no datuma. Funkciju TEXT šādi nevar izmantot funkcijas MAXIFS iekšpusē, jo MAXIFS ir nepieciešams diapazons. Šīs alternatīvās pieejas ir sīkāk paskaidrotas šeit.
Maksimālais klients
Lai izgūtu klientu, kas ir saistīts ar maksimālo vērtību noteiktā mēnesī, formula G6 ir šāda:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter.
Ideja ir tāda, ka mēs jau zinām maksimālo vērtību mēnesī (G5), un mēs varam izmantot šo vērtību kā "atslēgu" klienta meklēšanai. Ņemiet vērā, ka mums jāatkārto loģika, kas izmantota vērtību izolēšanai attiecīgajā mēnesī, lai izvairītos no kļūdainas sakritības citā mēnesī.
Tāpat kā iepriekš minētās alternatīvas, arī šajā formulā datumiem tiek izmantots vienkāršots tests, pamatojoties uz funkciju TEXT. Lasiet vairāk par INDEX un MATCH ar vairākiem kritērijiem šeit.
Formulai “viss vienā” aizstājiet G5 ar vienu no iepriekš aprakstītajām formulas opcijām, lai noteiktu maksimālo vērtību attiecīgajā mēnesī.
