Bendroji formulė
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Santrauka
Norėdami rasti maksimalią tam tikro mėnesio vertę, galite naudoti funkciją MAXIFS arba vieną iš kitų žemiau pateiktų alternatyvų. Parodytame pavyzdyje formulė G5 yra:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Kur pardavimai (D5: D15), datos (B5: B15) ir vertės (C5: C15) įvardijami kaip diapazonai.
Paaiškinimas
Funkcija MAXIFS gali rasti didžiausią diapazono vertę pagal vieną ar daugiau kriterijų. Parodytame pavyzdyje mes naudojame MAXIFS, kad surastume maksimalią pardavimo vertę, pagrįstą tam tikru mėnesiu, „skliausteliuose“ nurodant datas nuo pirmos mėnesio dienos iki paskutinės mėnesio dienos. Pirmaisiais kriterijais tikrinama, ar datos yra didesnės ar lygios mėnesio pirmajai:
dates,">="&G4,dates // first of month
Pastaba: mes manome, kad G4 data yra „pirmo mėnesio“ data.
Antruoju kriterijumi tikrinama, ar datos yra mažesnės arba lygios paskutinei mėnesio daliai, apskaičiuotai naudojant funkciją EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Kai abu kriterijai pateikia TRUE, data yra nurodytą mėnesį, o MAXIFS pateikia maksimalią dienų, kurios atitinka kriterijus, vertę.
Alternatyvios formulės
Jei jūsų „Excel“ versijoje nėra funkcijos MAXIFS, yra ir kitų parinkčių. Viena iš parinkčių yra paprasta masyvo formulė, pagrįsta MAX ir IF funkcijomis:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Pastaba: Tai yra masyvo formulė, kurią reikia įvesti su klavišais + Shift + Enter.
Kita galimybė yra naudoti tokią funkciją AGGREGATE:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Abiejose aukščiau pateiktose parinktyse naudojamas paprastesnis datų tikrinimo metodas, pagrįstas funkcija TEKSTAS, kuris naudoja skaičių formatą, kad iš mėnesio ir metų išgautų datą. Funkcija TEXT negali būti naudojama MAXIFS funkcijoje, nes MAXIFS reikia diapazono. Šie alternatyvūs metodai yra išsamiau paaiškinti čia.
Maksimalus klientas
Norėdami gauti klientą, susietą su maksimalia tam tikro mėnesio verte, G6 formulė yra:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Idėja yra ta, kad mes jau žinome maksimalią vertę per mėnesį (G5) ir galime naudoti šią vertę kaip „raktą“ klientui ieškoti. Atminkite, kad turime pakartoti logiką, naudojamą tam tikro mėnesio reikšmėms išskirti, kad būtų išvengta klaidingo atitikimo kitą mėnesį.
Kaip ir pirmiau minėtos alternatyvos, šiose formulėse taip pat naudojamas supaprastintas datų testas, pagrįstas funkcija TEXT. Skaitykite daugiau apie INDEX ir MATCH pagal kelis kriterijus čia.
Formulėje „viskas viename“ pakeiskite G5 viena iš aukščiau paaiškintų formulių parinkčių, kad nustatytumėte maksimalią tam tikro mėnesio vertę.
