Obecný vzorec
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
souhrn
Chcete-li zjistit maximální hodnotu v daném měsíci, můžete použít funkci MAXIFS nebo některou z dalších alternativ níže. V zobrazeném příkladu je vzorec v G5:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Kde prodeje (D5: D15), data (B5: B15) a hodnoty (C5: C15) jsou pojmenovány rozsahy.
Vysvětlení
Funkce MAXIFS může najít maximální hodnotu v rozsahu na základě jednoho nebo více kritérií. V zobrazeném příkladu používáme MAXIFS k vyhledání maximální hodnoty prodeje založené v daném měsíci podle „bracketingových“ dat mezi prvním dnem měsíce a posledním dnem měsíce. První kritérium kontroluje, zda jsou data větší nebo rovna prvnímu v měsíci:
dates,">="&G4,dates // first of month
Poznámka: Předpokládáme, že datum v G4 je datum „prvního měsíce“.
Druhé kritérium ověří, zda jsou data menší nebo rovna poslednímu měsíci, počítáno pomocí funkce EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Když obě kritéria vrátí TRUE, datum je v daném měsíci a MAXIFS vrátí maximum hodnoty dat, která splňují kritéria.
Alternativní vzorce
Pokud vaše verze aplikace Excel neobsahuje funkci MAXIFS, existují další možnosti. Jednou z možností je jednoduchý vzorec pole založený na funkcích MAX a IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Poznámka: Toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Další možností je použít funkci AGGREGATE takto:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Obě výše uvedené možnosti používají jednodušší metodu testování dat založenou na funkci TEXT, která používá číselný formát k extrakci měsíce a roku z data. Funkci TEXT nelze takto použít uvnitř funkce MAXIFS, protože MAXIFS vyžaduje rozsah. Tyto alternativní přístupy jsou vysvětleny podrobněji zde.
Maximální klient
Chcete-li načíst klienta spojeného s maximální hodnotou v daném měsíci, vzorec v G6 je:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Toto je vzorec pole a musí být zadán pomocí kláves Control + Shift + Enter.
Myšlenka je, že již známe maximální hodnotu za měsíc (G5) a můžeme ji použít jako „klíč“ k vyhledání klienta. Všimněte si, že musíme opakovat logiku použitou k izolaci hodnot v daném měsíci, abychom se vyhnuli falešné shodě v jiném měsíci.
Stejně jako výše uvedené alternativy používá tento vzorec také zjednodušený test pro data založený na funkci TEXT. Přečtěte si více o INDEXU a ZÁPASU s více kritérii zde.
U vzorce vše v jednom nahraďte G5 jednou z výše popsaných možností vzorce pro stanovení maximální hodnoty v daném měsíci.
