Az Excel-diagramok adatainak simítása fix mozgóátlaggal akkor működik, ha van tapasztalatunk az adatokkal és azok változásaival kapcsolatban. De ha az adatok változnak vagy újak, akkor Ön vagy a vezetőség esetleg kísérletezni szeretne a simító átlag különböző számú periódusával.
Ebben a példában egy OFFSET képletet használunk egy dinamikus tartományon átívelő átlag létrehozására. Beírja, hogy hány hónapot szeretne használni a simítási időszakhoz, és az AVERAGE és az OFFSET kiszámítja, hogy hány cellán keresztül kell átlagolni.
Az AVERAGE dinamikussá tétele előtt kezdjük azzal, hogy megértjük, hogyan működik az OFFSET. A következő ábrán a G26-os cella tartalmazza a 20. sor adatainak simítására szolgáló képletet,
=AVERAGE(OFFSET(G20,0,0,1,-3))
OFFSET megadja, hogy az AVERAGE milyen tartományon keresztül simítsa az adatokat.
Ez a képlet egy köztes lépés, amely segít megérteni a dinamikus képlet működését. Az OFFSET függvény visszaad egy tartományt, amelyre az AVERAGE hatni fog. Az OFFSET attribútumai a következők,
OFFSET(referencia,sorok,oszlopok,,)
Ebben a példában a tartomány, amelyet az OFFSET kiszámít, G20-nál kezdődik, és nulla sor- vagy oszlopeltolással rendelkezik. Más szóval az OFFSET által kiszámított tartomány bal felső sarka csak G20. Nem lesz eltolva vagy eltolva sorokkal vagy oszlopokkal. A számított tartomány 1 sorral magasabb lesz, és magában foglalja az aktuális G26-os cellát és még kettőt balra (ez egy -3). Tehát ez a számított tartomány G20:E20 lesz.
Másolja ezt a képletet a G26-ban jobbra, és látni fogja, hogy átlagolja a felette lévő 20. sorban lévő adatcellát és a balra lévő két előző cellát.
Az alábbi trükk segítségével ez egy dinamikus és az Ön által választott tartományon átívelő átlag lesz. Mi van, ha a képletben a -3 helyére egy cellahivatkozást teszünk?
A lenti ábrán a G26-os cellában lévő képletet úgy módosítottuk, hogy az az E24-es cellában lévő számmal van megadva,
AVERAGE(OFFSET(G20,0,0,1,-$E$24))
$E$24 a zöld színnel árnyékolt cella. Az E24-es cellába beírt szám határozza meg az átlagoláshoz használt cellák szélességét.
Az E24-es cellába beírt érték megváltoztatja az adatok simításához használt tartományt.
Ezzel a ponttal egy pörgettyűvel vagy legördülő menüvel megadhatja a felhasználónak a választás lehetőségét, hogy milyen széles legyen a simító átlag. A választásuk eredményének az E24-es cellába kell kerülnie.
A simító Excel diagram adatainak letöltése fix vagy dinamikus simítással
Egy probléma van ezzel az elrendezéssel. Ha a felhasználó nagyobb számot ír be az E24-be, mint amennyi cella balra van, akkor az OFFSET az üres cellákat és a címkét is magában foglalja. Ez hibás átlagot fog adni. Ha az E24 olyan nagy, hogy az OFFSET kimarad a lapból, akkor az AVERAGE hibát produkál. Mi a teendő? Olyan megoldásra van szükségünk, amely nem csak a hibát állítja meg, mint az ISERROR, hanem olyat is, amely megállítja a helytelen válaszokat.
A következő ábrán a képlet a G26-os cellában van beállítva. A 25. sorban van egy számsorozat, amely az érvényes tartományok határértékeit jelenti balra. A G26-os cellában lévő új képlet a 25. sorra néz, és ha az E24-es cellában lévő szám nagyobb, mint a 25. sorban lévő határérték, akkor NA() keletkezik. Ez az új hibajavító képlet a G26-ban a következő,
=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))
Ha egy Excel-diagram NA()-ra hivatkozik egy cellában, akkor nem rajzol semmit. Ez megakadályozza, hogy a simítási vonal nullára essen.
Módosítsa a dinamikus simítási képletet a hibák elkerülése érdekében.
Problémák az adatok simításával és azok megkerülése
Most már hagyhatja, hogy a felhasználó bármilyen simítási időszakot kipróbáljon. Azonban (úgy tűnik, mindig van egy “azonban” vagy egy “de, ebben az esetben”) van egy probléma az adatok simításával. A simítás kiveszi a volatilitást, de ez azt is jelenti, hogy az irányváltozásokat, a fordulópontokat vagy az új trendeket csak a simítási időszak felénél fogjuk látni. Elmaradsz a görbétől.
A két világból a legjobb, ha súlyozott átlagú simító függvényt használsz. Így nagyobb súlyt adhatsz a rövid távú adatoknak, hogy lásd a trendeket, de a régebbi adatok alapján simíthatsz, hogy kiküszöbölj néhányat a volatilitásból. Úgy hangzik, mint egy jó eset egy másik cikkhez, Az Excel diagramadatok simítása súlyozott átlag használatával.
Töltse le a példafájlt az Excel diagramadatok simítására mozgó vagy dinamikus simítással
Kapcsolódó cikkek,
- Az Excel diagramadatok simítása mozgó átlaggal
- Az Excel diagramadatok simítása súlyozott átlag használatával
.