Utjämning av Excel-diagramdata med ett fast glidande medelvärde fungerar när du har erfarenhet av data och hur variabla de är. Men om data ändras eller är nya kanske du eller ledningen vill experimentera med ett annat antal perioder i utjämningsgenomsnittet.
I det här exemplet används en OFFSET-formel för att skapa ett genomsnitt över ett dynamiskt intervall. Du skriver in det antal månader som du vill använda som utjämningsperiod och AVERAGE och OFFSET beräknar hur många celler som medelvärdet ska beräknas över.
För att göra AVERAGE dynamiskt ska vi börja med att förstå hur OFFSET fungerar. I följande figur innehåller cell G26 formeln för utjämning av data i rad 20,
=AVERAGE(OFFSET(G20,0,0,1,-3))
OFFSET anger det område som AVERAGE ska jämna ut data över.
Den här formeln är ett mellansteg för att hjälpa dig att förstå hur den dynamiska formeln fungerar. Funktionen OFFSET returnerar ett område som AVERAGE kommer att agera på. OFFSETs attribut är,
OFFSET(referens,rader,kolumner,,)
I det här exemplet börjar intervallet som OFFSET beräknar vid G20 och det har noll rad- eller kolumnförskjutning. Med andra ord är det övre vänstra hörnet av det intervall som OFFSET beräknar bara G20. Det kommer inte att förskjutas eller förskjutas med några rader eller kolumner. Det beräknade intervallet kommer att vara 1 rad högt och inkludera den aktuella cellen G26 och ytterligare två till vänster (det är en -3). Så det beräknade intervallet kommer att vara G20:E20.
Kopiera formeln i G26 till höger och du ser att den beräknar ett medelvärde för datacellen ovanför i rad 20 och de två föregående cellerna till vänster.
Här är knepet som gör det här dynamiskt och gör det till ett medelvärde över ett intervall som du väljer. Vad händer om du ersätter en cellreferens för att -3 i formeln?
I figuren nedan har formeln i cell G26 ändrats så att den anges av siffran i cell E24,
AVERAGE(OFFSET(G20,0,0,1,-$E$24))
$E$24 är den grönskuggade cellen. Siffran du skriver i cell E24 bestämmer bredden på de celler som används för att jämna ut genomsnittet.
Inmatning av ett värde i E24 ändrar det intervall som används för att jämna ut data.
I det här läget kan du använda en spinnare eller en rullgardinsmeny för att ge användaren möjlighet att välja hur brett genomsnittet för utjämning ska vara. Resultatet av deras val ska gå till cell E24.
Hämta utjämning Excel Chart Data with Fixed or Dynamic Smoothing
Det finns ett problem med det här arrangemanget. Om användaren skriver in ett större tal i E24 än det finns celler till vänster kommer OFFSET att inkludera tomma celler och etiketten. Detta kommer att ge ett felaktigt medelvärde. Om E24 är så stor att OFFSET går utanför bladet ger AVERAGE ett fel. Vad ska man göra? Vi behöver en lösning som inte bara stoppar ett fel, som ISERROR, vi behöver en som stoppar felaktiga svar.
I nästa figur har formeln i cell G26 justerats. Rad 25 har en serie siffror som är gränserna för giltiga intervall till vänster. Den nya formeln i cell G26 tittar på rad 25 och om talet i cell E24 är större än gränsen i rad 25 produceras NA(). Den nya felkorrigerande formeln i G26 är,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))
När ett Excel-diagram hänvisar till NA() i en cell ritas ingenting. Det hindrar utjämningslinjen från att sjunka till noll.
Modifiera den dynamiska utjämningsformeln för att förhindra fel.
Problem med utjämning av data och hur man kringgår dem
Nu kan du låta din användare prova vilken utjämningsperiod som helst. Det finns dock (det verkar som om det alltid finns ett ”dock” eller ett ”men, i det här fallet”) ett problem med utjämning av data. Utjämning tar bort volatiliteten, men det innebär också att du inte kommer att se riktningsförändringar, vändpunkter eller nya trender förrän ungefär halvvägs genom utjämningsperioden. Du ligger bakom kurvan.
Det bästa av två världar är att använda en utjämningsfunktion för viktade medelvärden. På så sätt kan du ge mer vikt åt data på kort sikt så att du ser trender, men du kan jämna ut baserat på äldre data för att eliminera en del av volatiliteten. Låter som ett bra fall för en annan artikel, Smoothing Excel Chart Data Using a Weighted Average.
Ladda ner exempelfilen för utjämning av Excel diagramdata med rörlig eller dynamisk utjämning
Relaterade artiklar,
- Smoothing Excel Chart Data with Moving Average
- Smoothing Excel Chart Data Using a Weighted Average
.