Glatning af Excel-diagramdata med et fast glidende gennemsnit fungerer, når du har erfaring med dataene, og hvor variable de er. Men hvis dataene ændrer sig eller er nye, vil du eller ledelsen måske gerne eksperimentere med et andet antal perioder i udglattende gennemsnit.
I dette eksempel bruges en OFFSET-formel til at oprette et gennemsnit på tværs af et dynamisk område. Du indtaster det antal måneder, du vil bruge som udjævningsperiode, og AVERAGE og OFFSET beregner, hvor mange celler gennemsnittet skal gå på tværs.
Hvor vi gør AVERAGE dynamisk, skal vi starte med at forstå, hvordan OFFSET fungerer. I følgende figur indeholder celle G26 formlen til udjævning af dataene i række 20,
=AVERAGE(OFFSET(G20,0,0,0,1,-3))
OFFSET angiver det område, som AVERAGE skal udjævne dataene over.
Denne formel er et mellemtrin, der skal hjælpe dig med at forstå, hvordan den dynamiske formel fungerer. Funktionen OFFSET returnerer et område, som AVERAGE vil virke på. OFFSET’s attributter er,
OFFSET(reference,rows,columns,,)
I dette eksempel starter det område, som OFFSET beregner, ved G20, og det har nul række- eller kolonneforskydning. Med andre ord er det øverste venstre hjørne af det område, som OFFSET beregner, kun G20. Det vil ikke blive forskudt eller forskudt med nogen rækker eller kolonner. Det beregnede område vil være 1 række højt og omfatte den aktuelle celle G26 og to mere til venstre (det er en -3). Så dette beregnede område vil være G20:E20.
Kopier denne formel i G26 til højre, og du vil se, at den beregner gennemsnittet af datacellen over den i række 20 og de to foregående celler til venstre.
Så, her er tricket, der gør dette til et dynamisk og gennemsnit på tværs af et område, du vælger. Hvad hvis du erstatter en cellehenvisning for at -3 i formlen?
I nedenstående figur er formlen i celle G26 blevet ændret, så den er specificeret af tallet i celle E24,
AVERAGE(OFFSET(G20,0,0,0,1,-$E$24))
$E$24 er den grønt skraverede celle. Det tal, du skriver i celle E24, bestemmer bredden af de celler, der bruges til at udjævne gennemsnittet på tværs.
Indtastning af en værdi i E24 ændrer det område, der bruges til at udjævne dataene.
På dette tidspunkt kan du bruge en spinner eller drop-down menu til at give brugeren et valg af, hvor bredt udjævningsgennemsnittet skal være. Resultatet af deres valg skal gå i celle E24.
Download udglatning Excel-diagramdata med fast eller dynamisk udglatning
Der er ét problem med dette arrangement. Hvis brugeren indtaster et større tal i E24, end der er celler til venstre, vil OFFSET omfatte tomme celler og etiketten. Dette vil give et forkert gennemsnit. Hvis E24 er så stor, at OFFSET går ud over arket, så giver AVERAGE en fejl. Hvad skal man gøre? Vi har brug for en løsning, der ikke bare stopper en fejl, som ISERROR, vi har brug for en løsning, der stopper forkerte svar.
I denne næste figur er formlen i celle G26 blevet justeret. Række 25 har en række tal, der er grænserne for gyldige intervaller til venstre. Den nye formel i celle G26 ser på række 25, og hvis tallet i celle E24 er større end grænsen i række 25, så produceres NA(). Denne nye fejlkorrigerende formel i G26 er,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))
Når et Excel-diagram henviser til NA() i en celle, tegner det intet. Det forhindrer udglatningslinjen i at falde til nul.
Modificer den dynamiske udglatningsformel for at forhindre fejl.
Problemer med udglatning af data og hvordan du kommer udenom dem
Nu kan du lade din bruger prøve en hvilken som helst udglatningsperiode, de ønsker. Der er dog (det virker som om der altid er et “dog” eller et “men, i dette tilfælde”) et problem med udglatning af data. Udglatning fjerner volatiliteten, men det betyder også, at du ikke vil se ændringer i retning, bøjningspunkter eller nye tendenser før omkring halvvejs gennem udglatningsperioden. Du er bagud i forhold til kurven.
Det bedste af begge verdener er at bruge en vægtet gennemsnitlig udglatningsfunktion. På den måde kan du give mere vægt til data på kort sigt, så du ser tendenser, men du kan glatte baseret på ældre data for at fjerne noget af volatiliteten. Det lyder som en god sag for en anden artikel, Udglatning af Excel-diagramdata ved hjælp af et vægtet gennemsnit.
Download eksempelfilen til udglatning af Excel-diagramdata med glidende eller dynamisk udglatning
Relaterede artikler,
- Glatning af Excel-diagramdata med glidende gennemsnit
- Glatning af Excel-diagramdata ved hjælp af et vægtet gennemsnit