Excel regressziós elemzés (Tartalomjegyzék)
- Regressziós elemzés Excelben
- A regresszió magyarázata matematikailag
- Hogyan végezzünk lineáris regressziót Excelben?
- # 1 - Regressziós eszköz az Analysis ToolPak segítségével az Excelben
- # 2 - Regressziós elemzés a Scatterplot és a Trendline használatával az Excelben
Regressziós elemzés Excelben
A lineáris regresszió egy statisztikai technika, amely megvizsgálja a függő változó és egy vagy több független változó közötti lineáris kapcsolatot.
- Függő változó (más néven válasz / eredményváltozó): az Ön érdeklődésének változója, amelyet a független változó (k) ból rendelkezésre álló információk alapján meg akart jósolni.
- Független változó (más néven magyarázó / előrejelző változó): Az a változó (k), amelytől a válaszváltozó függ. Ami azt jelenti, hogy ezek azok a változók, amelyek segítségével a válaszváltozó előre jelezhető.
Lineáris kapcsolat: a független változó (k) ban bekövetkező változás megváltoztatja a függő változót.
Alapvetően kétféle lineáris kapcsolat is létezik.
- Pozitív lineáris kapcsolat: Ha a független változó növekszik, akkor a függő változó is növekszik.
- Negatív lineáris kapcsolat: Ha a független változó növekszik, a függő változó csökken.
Ez volt néhány előfeltétel, mielőtt az Excel regressziós elemzéséhez valóban továbblépne.
Két alapvető módszer van a lineáris regresszió végrehajtására az Excel alkalmazásban:
- Regressziós eszköz az Analysis ToolPakon keresztül
- Szórási diagram trendvonallal
Valójában van még egy módszer, amely kézi képletet használ a lineáris regresszió kiszámításához. De miért keresse meg, ha az Excel számításokat végez az Ön számára?
Ezért csak a fentiekben tárgyalt két módszerről fogunk beszélni.
Tegyük fel, hogy 10 személy magasságáról és súlyáról van adat. Ha ezt az információt diagramon ábrázolja, nézzük meg, mit ad.
Mint a fenti képernyőképe mutatja, a lineáris kapcsolat a magasságban és a súlyban található a grafikonon keresztül. Most ne sokat vegyen részt a grafikonban, mindazonáltal mélyen feltárjuk a cikk második részében.
A regresszió magyarázata matematikailag
A lineáris regresszió matematikai kifejezése az alábbiak szerint rendelkezik:
Y = aX + b + ε
Hol,
- Y egy függő változó vagy válaszváltozó.
- X jelentése független változó vagy prediktor.
- a a regressziós vonal meredeksége. Ami azt jelzi, hogy amikor X megváltozik, Y változás történik az „a” egységekkel.
- b elfogja. Ez az Y érték, ha X értéke nulla.
- ε a véletlenszerű hiba kifejezés. Azért fordul elő, mert az Y előrejelzett értéke soha nem lesz pontosan azonos az adott X tényleges értékével. Ez a hiba kifejezés, nem kell aggódnia. Mivel vannak olyan szoftverek, amelyek kiszámítják a hiba kifejezést az ön hátterében. Az Excel egyike annak a szoftvernek.
Ebben az esetben az egyenlet lesz,
Y = aX + b
Melyik lehet a következő:
Súly = a * Magasság + b
Megpróbáljuk kideríteni ezen a és b értékeket a fent tárgyalt módszerekkel.
Hogyan végezzünk lineáris regressziót Excelben?
A további cikk bemutatja az regressziós elemzés alapjait az Excelben, és bemutat néhány különféle módszert a lineáris regresszió végrehajtására Excelben.
Itt letöltheti ezt a regressziós elemzési Excel sablont - Regression Analysis Excel sablon# 1 - Regressziós eszköz az Analysis ToolPak segítségével az Excelben
Példánkban megpróbáljuk illeszteni a súlyértékek (amelyek függő változó) regresszióját a magassági értékek segítségével (ami független változó).
- Az Excel táblázatban kattintson az Adat elem elemre (az elemzési csoport alatt található) .
- Keresse meg a regressziót . Válassza ki és nyomja meg az OK gombot.
- Használja a következő bemeneteket a megnyíló Regression panelen.
- Y bemeneti tartomány : Válassza ki azokat a cellákat, amelyek tartalmazzák a függő változót (ebben a példában B1: B11)
- Input X Range : Válassza ki azokat a cellákat, amelyek tartalmazzák a független változót (ebben a példában A1: A11).
- Jelölje be a Címkék négyzetet, ha adatai oszlopnevekkel rendelkeznek (ebben a példában oszlopnevek vannak).
- A bizalom szintjét alapértelmezés szerint 95% -ra állítják be, amely a felhasználói igényeknek megfelelően megváltoztatható.
- A Kimeneti beállítások alatt testreszabhatja, hogy a regressziós elemzés kimenetét miként láthatja Excelben. Ebben az esetben ugyanazon a lapon szeretnénk látni a kimenetet. Ezért adott tartományt kell megadni.
- A Residuals opcióban opcionális bemenetek vannak, például Residuals, Residual plot, Standardized Residuals, Line Fit plot, amelyet igénye szerint választhat. Ebben az esetben jelölje be a Maradványok jelölőnégyzetet, hogy láthassuk a szóródást az előrejelzett és a tényleges értékek között.
- A Normál valószínűség opció alatt kiválaszthatja a Normál valószínűség diagramjait, amelyek segítségével ellenőrizheti a prediktorok normalitását. Kattintson az OK gombra .
- Az Excel másodpercek töredékével kiszámítja a regressziós elemzést az Ön számára.
Eddig könnyű volt, és nem olyan logikus. Ennek a kimenetnek az értelmezése és értékes betekintés készítése azonban trükkös feladat.
A teljes output egyik fontos része az R négyzet / igazított R négyzet az ÖSSZEFOGLALÓ KIMENET táblázat alatt. Amely információkat szolgáltat, hogy milyen jó modellünk. Ebben az esetben az R négyzet értéke 0, 9547. Amely azt magyarázza, hogy a modell pontossága 95, 47% (jó illeszkedés). Vagy más nyelven, az Y változóval kapcsolatos információk 95, 47% -kal magyarázhatók X változóval.
A teljes output másik fontos része az együtthatók táblázata. Az együtthatók értékeit adja meg, amelyek felhasználhatók a jövőbeli előrejelzések modelljének felépítéséhez.
Most a becslés regressziós egyenlete a következő lesz:
Súly = 0, 6746 * Magasság - 38, 45508 (A magasság lejtőértéke 0, 6746… és a lehallgatás –38, 45508…)
Megkapta, amit meghatározott? Ön definiált egy olyan funkciót, amelyben most meg kell adnia a Magasság értékét, és így megkapja a Súly értéket.
# 2 - Regressziós elemzés a Scatterplot és a Trendline használatával az Excelben
Most meglátjuk, hogy excel-ben hogyan illeszthetünk regressziós egyenletet maga a scatterplotba.
- Válassza ki a teljes két oszlopos adatot (a fejléceket is ideértve).
- Kattintson a Beszúrás elemre, és válassza az Scatter Plot a grafikonok alatt az alábbi képet.
- Lásd a kimeneti diagramot.
- Most ehhez a grafikonhoz legkevesebb négyzetű regressziós sorra van szükség. Ennek a sornak a hozzáadásához kattintson a jobb egérgombbal a grafikon bármelyik adatpontjára, és válassza a Trendline hozzáadása lehetőséget.
- Ez lehetővé teszi, hogy a legkevesebb négyzet alakú regressziós trendvonal legyen az alábbiak szerint.
- A Trendline formázása lehetőségnél jelölje be a Kijelző egyenlet a táblán négyzetet .
- Ez lehetővé teszi a legkevesebb négyzetű regressziós vonal egyenletének megjelenítését a grafikonon.
Ez az egyenlet, amellyel megjósolhatjuk az adott magassági értékkészlet súlyértékét.
Emlékezzen az Excel regressziós elemzésére
- A trendvonal elrendezését a Trendline formázása menüpont alatt meg lehet változtatni a szórt grafikonon.
- Mindig javasoljuk, hogy vizsgálja meg a maradék görbéket, miközben regressziós elemzést végez az Excel Data Analysis ToolPak segítségével. Ez jobban megérti a tényleges Y-értékek és a becsült X-értékek eloszlását.
- Az egyszerű lineáris regresszióhoz excelben nincs szükség ANOVA-ra és az igazított R négyzetre az ellenőrzéshez. Ezeket a funkciókat figyelembe lehet venni a többszörös lineáris regressziónál. Amely túllépi a cikk tárgyát.
Ajánlott cikkek
Ez egy útmutató az Excel regressziós elemzéséhez. Itt megvitatjuk, hogyan kell elvégezni a regressziós elemzést Excelben, az excel példákkal és a letölthető excel sablonnal együtt. Megnézheti más javasolt cikkeinket -
- Excel eszköz az adatok elemzéséhez
- Számítsa ki az ANOVA-t Excelben
- Hogyan lehet megtalálni az Excel mozgóátlagokat?
- Z TESZT Példák az Excelben