Všeobecný vzorec
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Zhrnutie
Ak chcete zistiť maximálnu hodnotu v danom mesiaci, môžete použiť funkciu MAXIFS alebo jednu z ďalších alternatív uvedených nižšie. V zobrazenom príklade je vzorec v G5:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
V prípade rozsahu predaja sú pomenované tržby (D5: D15), dátumy (B5: B15) a hodnoty (C5: C15).
Vysvetlenie
Funkcia MAXIFS dokáže nájsť maximálnu hodnotu v rozsahu na základe jedného alebo viacerých kritérií. V zobrazenom príklade používame MAXIFS na vyhľadanie maximálnej hodnoty predaja založenej v danom mesiaci na základe „bracketingu“ dátumov medzi prvým dňom mesiaca a posledným dňom mesiaca. Prvé kritérium kontroluje, či sú dátumy väčšie alebo rovnaké ako prvé v mesiaci:
dates,">="&G4,dates // first of month
Poznámka: Predpokladáme, že dátum v G4 je dátum „prvého mesiaca“.
Druhé kritérium kontroluje, či sú dátumy menšie alebo rovnaké ako posledný v mesiaci, vypočítané pomocou funkcie EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Keď obidve kritériá vrátia hodnotu TRUE, dátum je v danom mesiaci a funkcia MAXIFS vráti maximálnu hodnotu dátumov, ktoré zodpovedajú kritériám.
Alternatívne vzorce
Ak vaša verzia programu Excel neobsahuje funkciu MAXIFS, existujú aj ďalšie možnosti. Jednou z možností je jednoduchý vzorec poľa založený na funkciách MAX a IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Poznámka: Toto je vzorec poľa a musí sa zadávať pomocou klávesov control + shift + enter.
Ďalšou možnosťou je použiť funkciu AGGREGATE takto:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Obe vyššie uvedené možnosti používajú jednoduchšiu metódu testovania dátumov založenú na funkcii TEXT, ktorá pomocou číselného formátu extrahuje mesiac a rok z dátumu. Funkciu TEXT nie je možné takto použiť vo vnútri funkcie MAXIFS, pretože MAXIFS vyžaduje rozsah. Tieto alternatívne prístupy sú tu podrobnejšie vysvetlené.
Maximálny klient
Na získanie klienta spojeného s maximálnou hodnotou v danom mesiaci je vzorec v G6:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Toto je vzorec poľa a musí byť zadaný pomocou klávesov control + shift + enter.
Myšlienka tu je, že maximálnu hodnotu už poznáme za mesiac (G5) a môžeme ju použiť ako „kľúč“ na vyhľadanie klienta. Upozorňujeme, že musíme opakovať logiku použitú na izoláciu hodnôt v danom mesiaci, aby sa zabránilo nesprávnej zhode v inom mesiaci.
Rovnako ako vyššie uvedené alternatívy, aj tento vzorec používa zjednodušený test dátumov založený na funkcii TEXT. Prečítajte si viac informácií o indexoch INDEX a MATCH s viacerými kritériami tu.
Pre vzorec všetko v jednom nahraďte G5 jednou z možností vzorca vysvetlených vyššie na určenie maximálnej hodnoty v danom mesiaci.
