Ecel-kaavion datan tasoittaminen kiinteällä liukuvalla keskiarvolla toimii, kun sinulla on kokemusta datasta ja sen vaihtelevuudesta. Mutta jos data muuttuu tai se on uutta, sinä tai johto voi haluta kokeilla eri jaksojen lukumäärää tasoituskeskiarvossa.
Tässä esimerkissä OFFSET-kaavaa käytetään dynaamisen keskiarvon luomiseen. Kirjoitat kuukausien lukumäärän, jota haluat käyttää tasoitusjaksona, ja AVERAGE ja OFFSET laskevat, kuinka monen solun yli keskiarvo lasketaan.
Ennen kuin teet AVERAGEsta dynaamisen, aloitetaan ymmärtämällä, miten OFFSET toimii. Seuraavassa kuvassa solu G26 sisältää kaavan, jolla tasoitetaan rivin 20 tiedot,
=AVERAGE(OFFSET(G20,0,0,1,-3))
OFFSET määrittää alueen, jonka yli AVERAGE tasoittaa tiedot.
Tämä kaava on välivaihe, joka auttaa ymmärtämään, miten dynaaminen kaava toimii. OFFSET-funktio palauttaa alueen, johon AVERAGE vaikuttaa. OFFSET:n ominaisuudet ovat,
OFFSET(viite,rivit,sarakkeet,,)
Tässä esimerkissä OFFSET:n laskema alue alkaa G20:stä ja siinä on nolla rivi- tai sarakeoffsetia. Toisin sanoen OFFSETin laskeman alueen vasen yläkulma on vain G20. Sitä ei siirretä tai siirretä millään rivillä tai sarakkeella. Laskettu alue on yhden rivin korkuinen ja sisältää nykyisen solun G26 ja kaksi muuta solua vasemmalle (se on -3). Tämä laskettu alue on siis G20:E20.
Kopioi tuo kaava G26:ssa oikealle ja näet, että se keskiarvoistaa sen yläpuolella rivillä 20 olevan datasolun ja kaksi edellistä solua vasemmalla.
Tässä on siis temppu, joka tekee tästä dynaamisen ja keskiarvon valitsemallasi alueella. Entä jos korvaat kaavassa tuon -3:n soluviittauksella?
Oheisessa kuvassa solussa G26 olevaa kaavaa on muutettu niin, että määritetään solussa E24 oleva luku,
AVERAGE(OFFSET(G20,0,0,1,-$E$24))
$E$24 on vihreällä tummennettu solu. Soluun E24 kirjoittamasi luku määrittää solujen leveyden, jonka yli käytetään keskiarvoa.
Syöttämällä arvon soluun E24 muutat tietojen tasoittamiseen käytettävää aluetta.
Tässä vaiheessa voit käyttää spinneriä tai pudotusvalikkoa antaaksesi käyttäjälle mahdollisuuden valita, kuinka leveä tasoituskeskiarvosta halutaan tehdä. Heidän valintansa tuloksen pitäisi mennä soluun E24.
Lataa tasoitus Excel-kaavion tiedot kiinteällä tai dynaamisella tasoituksella
Tässä järjestelyssä on yksi ongelma. Jos käyttäjä syöttää E24:ään suuremman luvun kuin sen vasemmalla puolella on soluja, OFFSET sisältää tyhjät solut ja etiketin. Tämä antaa väärän keskiarvon. Jos E24 on niin suuri, että OFFSET poistuu arkilta, AVERAGE tuottaa virheen. Mitä on tehtävä? Tarvitsemme ratkaisun, joka ei vain pysäytä virhettä, kuten ISERROR, vaan joka pysäyttää virheelliset vastaukset.
Tässä seuraavassa kuvassa solun G26 kaavaa on korjattu. Rivillä 25 on sarja numeroita, jotka ovat voimassa olevien alueiden rajoja vasemmalla. Solussa G26 oleva uusi kaava tarkastelee riviä 25, ja jos solussa E24 oleva luku on suurempi kuin rivin 25 raja, tuloksena on NA(). Tämä uusi virheitä korjaava kaava solussa G26 on,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))
Kun Excel-kaavio viittaa solussa NA(), se ei piirrä mitään. Tämä estää tasoitusviivaa laskemasta nollaan.
Muokkaa dynaamisen tasoituksen kaavaa virheiden välttämiseksi.
Problems with Smoothing Data and How to Get A Go Around Them
Nyt voit antaa käyttäjän kokeilla mitä tahansa tasoitusjaksoa. Kuitenkin (näyttää siltä, että aina on ”kuitenkin” tai ”mutta, tässä tapauksessa”) datan tasoittamisessa on ongelma. Tasoittaminen poistaa volatiliteetin, mutta se tarkoittaa myös sitä, että et näe suunnanmuutoksia, käännekohtia tai uusia trendejä ennen kuin tasoitusjakson puoliväliin mennessä. Olet jäljessä käyrästä.
Kummastakin maailmasta paras on käyttää painotetun keskiarvon tasoitusfunktiota. Näin voit antaa enemmän painoarvoa lähiajan tiedoille, jotta näet trendejä, mutta voit tasoittaa vanhempien tietojen perusteella poistaaksesi osan volatiliteetista. Kuulostaa hyvältä tapaukselta toiselle artikkelille, Excel-kaaviodatan tasoittaminen painotetun keskiarvon avulla.
Lataa esimerkkitiedosto Excel-kaaviodatan tasoittamisesta liukuvalla tai dynaamisella tasoituksella
Seuraavat artikkelit,
- Ecel-kaaviodatan tasoittaminen liukuvalla keskiarvolla
- Ecel-kaaviodatan tasoittaminen painotetun keskiarvon avulla
.