A lineáris regresszió modellezi a függő és független statisztikai adatváltozók közötti kapcsolatot. Egyszerűbben fogalmazva, a táblázat két táblázatoszlopa közötti trendet emelik ki. Például, ha beállít egy Excel táblázatot egy hónap x oszloppal, és minden hónaphoz rögzített egy adatkészletet a szomszédos y oszlopban, a lineáris regresszió kiemeli az x és y változók közötti trendet úgy, hogy trendvonalakat ad hozzá a táblázathoz. grafikonok. Így adhat hozzá lineáris regressziót az Excel grafikonokhoz.
Lineáris regressziós trendvonal hozzáadása a grafikonhoz
- Először nyisson meg egy üres Excel-táblázatot, válassza ki a D3 cellát, és adja meg a „Hónap” oszlopfejlécet, amely az x változó lesz.
- Ezután kattintson az E3 cellára, és írja be az „Y értéket” y változó oszlopfejléceként. Ez alapvetően egy táblázat, amely a január-május hónapokra rögzített adatértékek sorozatát tartalmazza.
- Írja be a hónapokat a D4–D8 cellákba, és a hozzájuk tartozó adatértékeket az E4–E8 cellákba, ahogy az a közvetlenül lenti pillanatképen látható.
Most beállíthat egy szórási grafikont ehhez a táblázathoz.
- Jelölje ki a táblázat összes celláját a kurzorral.
- Kattintson a Beszúrás fülre, és válassza ki Szétszór > Szórjon csak jelölőkkel a grafikon hozzáadásához a táblázathoz az alábbiak szerint. Alternatív megoldásként megnyomhatja az Alt + F1 gyorsbillentyűket oszlopdiagram beszúrásához.
- Ezután kattintson a jobb gombbal a diagramra, és válassza ki Diagramtípus módosítása >X Y (szórás) >Szórjon csak jelölőkkel.
Ezután hozzáadhatja a trendvonalat a szórási diagramhoz
- Jelölje ki az egyik adatpontot a szóródiagramon, és kattintson a jobb gombbal a helyi menü megnyitásához, amely tartalmaz egy Trendline hozzáadása választási lehetőség.
- Válassza ki Trendline hozzáadása a közvetlenül alatta lévő pillanatképen látható ablak megnyitásához. Az ablak öt lappal rendelkezik, amelyek különféle formázási lehetőségeket tartalmaznak a lineáris regressziós trendvonalak számára.
3. Kattintson a gombra Trendline opciók és onnan válassza ki a regresszió típusát. Választhat Exponenciális, Lineáris, Logaritmikus, Mozgóátlag, Erő és Polinom regressziós típusú opciók onnan.
4. Válassza ki Lineáris és kattintson Bezárás hogy hozzáadja a trendvonalat a grafikonhoz, ahogy az közvetlenül lent látható.
A fenti grafikonon látható vonalbeli regressziós trendvonal rávilágít arra, hogy az x és y változók között általános felfelé ívelő kapcsolat van, annak ellenére, hogy a diagramon néhány csökkenés tapasztalható. Vegye figyelembe, hogy a lineáris regressziós trendvonal nem fedi át a diagram egyik adatpontját sem, tehát nem azonos az egyes pontokat összekötő átlagos vonaldiagrammal.
A lineáris regressziós trendvonal formázása
A lineáris regressziós trendvonal formázása fontos eszköz az olvasható, világos grafikonok készítéséhez Excelben.
- A trendvonal formázásának megkezdéséhez kattintson rá jobb gombbal, és válassza ki Trendvonal formátuma.
- Ekkor újra megnyílik a Trendvonal formázása ablak, amelyből rákattinthat Vonal színe.
- Válassza ki Folytonos vonal és kattintson a Szín doboz megnyitásához egy paletta, amelyből választhat egy alternatív színt a trendvonalhoz.
- A vonalstílus testreszabásához kattintson a Vonalstílus fülre. Ezután módosíthatja a nyíl szélességét és konfigurálhatja a nyíl beállításait.
- megnyomni a Nyíl beállítások gombokkal nyilak adhatók a vonalhoz.
Esztétikai okokból effektusokat is hozzáadhat trendvonalához
- Kattintással adjon ragyogó hatást a trendvonalhoz Világít és Puha élek. Ekkor megnyílik az alábbi lap, amelyről a ikonra kattintva ragyogást adhat hozzá Előbeállítások gomb.
- Ezután válasszon egy ragyogási variációt az effektus kiválasztásához. Kattintson Szín az effektus alternatív színeinek kiválasztásához, és húzhatja a Méret és Átláthatóság sávok a trendvonal ragyogásának további konfigurálásához.
Értékek előrejelzése lineáris regresszióval
A trendvonal formázása után jövőbeli értékeket is előre jelezhet vele. Tegyük fel például, hogy három hónappal május után augusztusra kell előrejeleznie egy adatértéket, amely nem szerepel a táblázatunkban.
- kattintson a Trendline Options elemre, és írja be a „3”-at a Továbbítás szövegmezőbe.
- A lineáris regressziós trendvonal rávilágít arra, hogy az augusztusi érték valószínűleg valamivel 3500 felett lesz, amint az alább látható.
Minden lineáris regressziós trendvonalnak saját egyenlete és r négyzetértéke van, amelyet hozzáadhat a diagramhoz.
- Kattints a Jelenítse meg az egyenletet a diagramon jelölje be az egyenlet grafikonhoz való hozzáadásához. Ez az egyenlet tartalmaz egy meredekséget és egy metszéspont értéket.
- Az r négyzet értékének a grafikonhoz való hozzáadásához kattintson a gombra Az R-négyzet érték megjelenítése a diagramon jelölőnégyzetet. Ez hozzáadja az r négyzetét a közvetlenül az egyenlet alatti grafikonhoz, mint az alábbi pillanatképen.
- Húzza az egyenletet és a korrelációs mezőt, hogy megváltoztassa a helyzetét a szórásdiagramon.
A lineáris regressziós függvények
Az Excel lineáris regressziós függvényeket is tartalmaz, amelyekkel megtalálhatja az y és x adattömbök meredekségét, metszetét és r négyzetét.
- Válasszon ki egy cellát a táblázatból, amelyhez hozzáadni szeretné az egyik függvényt, majd nyomja meg a gombot Funkció beszúrása gomb. A lineáris regressziós függvények statisztikai jellegűek, ezért válasszon Statisztikai a kategória legördülő menüből.
- Ezután választhat RSQ, LEJTŐ vagy INTERCEPT hogy az alábbiak szerint nyissa meg a Funkcióablakukat.
Az RSQ, SLOPE és INTERCEPT ablakok nagyjából megegyeznek. Ezek közé tartoznak a Known_y és Known_x mezők, amelyek kiválasztásával hozzáadhatja az y és x változók értékét a táblázatból. Vegye figyelembe, hogy a celláknak csak számokat kell tartalmazniuk, ezért cserélje ki a hónapokat a táblázatban a megfelelő számokkal, például 1 a január, 2 a február stb. rendben az ablak bezárásához és a függvény hozzáadásához a táblázathoz.
Így most feldobhatja Excel táblázatos grafikonjait lineáris regressziós trendvonalakkal. Rávilágítanak a grafikonok adatpontjaira vonatkozó általános trendekre, és a regressziós egyenletekkel egyben praktikus előrejelző eszközök is.
Van tippje, trükkje vagy kérdése az Excel lineáris regressziós trendvonalaival kapcsolatban? Tudassa velünk az alábbi megjegyzés szakaszban.