Hoe maak je een kort lineair regressiemodel in Excel?
Selecteer celbereik H3:H22 en met H3 als de actieve gemarkeerde cel, voer zonder aanhalingstekens de formule "=(B3-X_BAR)*(C3-Y_BAR)" in.
Excel doet goed werk met statistieken, of ze laten een derde partij hun add-on schrijven, die gratis beschikbaar is. In dit artikel leert u hoe u een trend projecteert met behulp van Excel en lineaire regressieanalyse.
Deel 1 van 3: de tutorial
- 1Open een nieuwe werkmap in Excel en maak 3 werkbladen: Gegevens, Grafiek en Opslaan. Sla de werkmap op als Lineaire regressie - korte les, of iets dergelijks, in een logische bestandsmap.
- 2Voorkeuren instellen: Open Voorkeuren in het Excel-menu. Aanbevolen instellingen: stel Algemeen in op R1C1 Uit en toon de 10 meest recente documenten; Bewerken - zet alle Top-opties op aangevinkt behalve Datumsysteem automatisch converteren. Toon aantal decimalen = blanco (voor gehele getallen de voorkeur), behoud weergave van datums en stel 30 in voor 21e eeuw cutoff; Weergave - formulebalk en statusbalk weergeven, houd de muisaanwijzer voor opmerkingen en alle objecten, Toon rasterlijnen en alle vakken daaronder automatisch of aangevinkt; Grafiek - toon grafieknamen en gegevensmarkeringen bij de muisaanwijzer. Laat rust voorlopig niet aangevinkt; Berekening - Automatisch en calc voor opslaan, max verandering 0,000000,000000,01 zonder komma's, omdat het zoeken naar doelen veel wordt gedaan en externe linkwaarden opslaan en 1904-systeem gebruiken; Foutcontrole - controleer alles; Opslaan - voorbeeldafbeelding opslaan met nieuwe bestanden en Autorecover opslaan na 5 minuten; Lint - alles aangevinkt behalve Groepstitels en ontwikkelaar verbergen.
- 3Selecteer tussen de 1 en de A in de linkerbovenhoek van het werkblad om het hele blad te selecteren en opmaak van de cellen uitlijning horizontaal midden en lettergrootte 9 of 10, of wat u maar wilt.
- 4Voer de kolomkoppen in rij 1 in: B1: X; Cl: J; D1: X. E1: Y F1: Y. Voer de kolomkoppen van rijen 2: A2: Student; B2 SAT; C2: GPA; D2: WIJZIGING; E2: WIJZIGING; F2: TREND, G2: Wijziging.
- 5Bewerken ga naar celbereik a3: A22, voer 1 in en doe Bewerken Vul seriekolommen Lineaire stapwaarde 1 OK, er zijn 20 studenten die de trend van hun afhankelijke GPA's willen weten, gegeven de onafhankelijke variabele van de SAT-examenscore bij het invoeren van een nieuwe universiteit, dwz zullen hun cijfers waarschijnlijk stijgen, dalen of ongeveer hetzelfde blijven?
- 6Vul de gegevens in om deze vraag te beantwoorden. In {x,y} paren zoals weergegeven in onderstaande afbeelding Stap 22, of als volgt: voor leerling 1, {X,Y} = {935 voor X of SAT, 2,2 voor Y of GPA}; leerling 2 {12603,1}; dan {11052,6}; {13203,3}; {14503,8}; {9602,2; {13603,2}; {9002,2}; {10202,2}; {13803,6}; {9402,3}; {11902,8}; {10002,2}; {9452,3}; {9902,4}; {10002,2}; {10402,3}; {15703.9}; {15303,8}; {9802,4}.
- 7Selecteer cel a23 en voer MEAN in. Voer de formule zonder aanhalingstekens in cel B23" = GEMIDDELDE (B3: B22)" in en kopieer deze en plak deze in cel C23. Selecteer cel C23 en voeg naam in Definieer naam Y_Bar voor cel $C17€ Selecteer cel B23 en voeg naam in Definieer naam X_Bar voor cel $B17€ Selecteer celbereik B23:C23 en Format Cells Letterkleur Rood en Vet.
- 8Bewerken ga naar celbereik d3:d22 en met cel d3 de actieve en gemarkeerde cel, voer zonder aanhalingstekens de formule "=(b3-x_bar)^2" in en bewerk het invullen naar beneden. Bewerken Ga naar celbereik E3:E22 en met cel E3 de actieve en gemarkeerde cel, voer zonder aanhalingstekens de formule "=(C3-Y_BAR)^2" in en Bewerk Fill Down. Selecteer celbereik D3:D23 en voer Cellen op Aantal Aantal decimalen 4.
- 9Selecteer cel d23 en voer zonder aanhalingstekens de formule "=sum(d3:d22)/(20-1)" in en kopieer en plak deze in cel e23. Selecteer celbereik E3:E23 en voer Cellen op Aantal Aantal decimalen 6. Selecteer cel A24 en voer VARIANTIE in en selecteer cel B24 en voer de formule "= D23" in en kopieer deze en plak deze in cel C24. Selecteer celbereik B24:D25 en doe Cellen opmaken Nummer Nummer Decimale plaatsen 6.
- 10Selecteer cel a25 en voer STD DEV in. (voor Standaarddeviatie) en selecteer cel B25 en voer zonder aanhalingstekens de formule "=STDEVPA(B3:B22)" in en kopieer en plak deze in cel C25. Do Format Cells Number Number Decimaal Plaatsen 7 voor geselecteerd celbereik B25:C25.
- 11Selecteer cel a27 en voer VOORSPELLING in en selecteer cel b27 en voer zonder aanhalingstekens de formule "=stdevpa(b3:b22)" in, wat een matrixformule is, dus u moet op control+shift+enter drukken om de matrixformule correct tussen haakjes in te voeren. Dat is de projectie van de GPA van één student gegeven een SAT van 1290 en de rest van de populatie- of steekproefgegevens. Hij / zij kan een GPA van 3.170409192 verwachten zodra u Cells Number Number Decimal Places 9 formatteert (wat later exact wordt geëvenaard). Niet dat zo'n nauwkeurige GPA nodig is, maar het gaat erom de formule te bewijzen, en dat vereist enige nauwkeurigheid.
- 12Bewerken ga naar celbereik f3:f22 en voer de formule in zonder aanhalingstekens "=trend(c3:c22,b3:b22,,true)", wat een matrixformule is, dus u moet op control+shift+enter drukken om de matrix correct in te voeren formule tussen haakjes.dat is de projectie van de gpa's van de studentenpopulatie gegeven hun eerdere prestaties.
- 13Bewerken ga naar celbereik g3:g22 en met g3 de actieve gemarkeerde cel, voer de formule in zonder aanhalingstekens "=f3-c3" en bewerk naar beneden. Do Format Cells Number Number Custom +0,0;-0,0;+0,0.
Selecteer celbereik I3:I22 en met I3 als de actieve gemarkeerde cel, voer zonder aanhalingstekens de formule "=(B3-X_BAR)^2" in.
Deel 2 van 3: verklarende grafieken, diagrammen, foto's
- 1Maak de grafiek (afhankelijk van de bovenstaande zelfstudiegegevens). Selecteer celbereik B3:C23 en ga naar het lint (of doe de Grafiekwizard) en selecteer Grafieken, Alles, scrol omlaag naar Scatter, Marked Scatter.. Vervolgens Bewerken, knip of kopieer de nieuwe grafiek naar het werkblad Grafieken. Doe Chart Layout Chart Title - Title Above Chart en vul het in met "Line Regression - SATs vs. GPAs" (zonder aanhalingstekens). Selecteer Rasterlijnen Verticale rasterlijnen Grote rasterlijnen aangevinkt. Selecteer Axis Titles Horizontale Axis Title, Title Title Below Axis en bewerk in "SAT Score" (zonder aanhalingstekens). Selecteer as titels Verticale as titel, Horizontale titel en bewerken in "GPA" (zonder aanhalingstekens). Volgens afspraak gaat de variabele Independent x op de onderste horizontale as en de variabele Dependent y op de linker verticale as.
- 2Zoek de gegevensmarkering voor het gemiddelde op {1143,752.755} die "serie 1 punt 1143,75" (1143,75, 2,8) zal lezen wanneer u erover zweeft of erop hebt geklikt. Klik erop en doe menu Format, Data Point Marker Style Automatic Size 9, dan Marker Fill Color Red.
- 3Keer terug naar het gegevenswerkblad om de regressielijn van y' = mx + b te berekenen, waarbij m = de helling en b = het y-snijpunt. Kijkend naar de grafiekgegevens met lage waarden onder 10 aan de linkerkant en waarden rond 1000 aan de onderkant, zou je een zeer kleine decimale helling en een y-snijpunt dichtbij 0 verwachten. Geschaalde grafieken kunnen soms misleidend zijn wat betreft helling.
- 4Selecteer cel g1 en voer Y in. Selecteer cel H1 en voer m in en kopieer H1 en plak deze in I1 via opdracht c, selecteer I1 en opdracht v. Selecteer cel H2 en voer Teller in en selecteer cel I2 en voer de noemer in. Selecteer celbereik H3:H22 en met H3 als de actieve gemarkeerde cel, voer zonder aanhalingstekens de formule "=(B3-X_BAR)*(C3-Y_BAR)" in. Bewerken Vul naar beneden. Selecteer celbereik I3:I22 en met I3 als de actieve gemarkeerde cel, voer zonder aanhalingstekens de formule "=(B3-X_BAR)^2" in. Bewerken Vul naar beneden. Selecteer kolommen H en I en formatteer cellen Getal Getal Decimaal Plaatsen 1.
- 5Selecteer cel h23 en voer de formule in zonder aanhalingstekens "= sum (h3: h22)" en formatteer de cellen om de zwarte, vetgedrukte omtreklijn en kopieer deze naar i23.
- 6Selecteer cel h24 en voer m in en formatteer cellen letterkleur rood. Kopieer het naar cel H25 en voer b in cel H25 in. Selecteer cel I24 en voer zonder aanhalingstekens de formule "=H23/I23" in, wat de helling m is, en selecteer vervolgens cel I25 en voer de formule in zonder aanhalingstekens "=Y_BAR-I24*X_BAR", dat wil zeggen de y-snijpunt b = Y_Mean-m*X_Mean.
- 7Kopieer cel h25 en plak deze in cel i26 en voer in i26 y' - Mx + b in. Selecteer cel H27 en voer Prognose in, voer vervolgens de formule in I27 in zonder aanhalingstekens "= I24 * 1290 + I25". Uw antwoord moet exact gelijk zijn aan het VOORSPELLEN-antwoord in B27 als u eenmaal Cellen opmaken Aantal Aantal Decimale plaatsen 9 heeft.
- 8Bewerk kopieer cel i26 naar celbereik h29:i29. Voer in cel H29 Lijn X in en voer in cel I29 Lijn Y in. Voer 800 in cel H30 en 1600 in cel H31 in. Selecteer cel I 27 en kopieer de formule omhoog in de formulebalk - kopieer de cel niet en plak deze - dat zal niet goed werken. Selecteer cel I30 en plak in de bewerkingsformulebalk de formule die u zojuist hebt gekopieerd. Doe hetzelfde voor cel I31. Bewerk de formule van cel I31 om "=I24*H31+I25" te lezen en druk op enter en bewerk vervolgens de formule van cel I30 in de formulebalk om "=I24*H30+I25" te lezen en druk op enter. Het resultaat van I30 zou 1.7786108729206 moeten zijn en het resultaat van I31 zou 4.05093465957812 moeten zijn (waarvan ik besef dat dit hoger is dan een 4,0 GPA, maar we creëren een regressielijn, dus dat maakt niet zoveel uit).
- 9Activeer het diagram-werkblad en klik in het diagram en in het menu, doe diagram gegevens toevoegen en ga in reactie op de bereikquery terug naar het gegevenswerkblad en selecteer celbereik h30:i31. Nu komt de mijne er verkeerd uit en moet ik de serie editen. Geen probleem. Selecteer de gegevensmarkering en bewerk in de formulebalk de reeks om "=SERIES(,Sheet1!$H22€:$H23€,Sheet1!$I22€:$I230€)" te lezen en klik op de gegevensmarkering bij {8001,78} en maak Lijn rood en Gewicht 0,75 pt, dan Marker Vulkleur Rood, dan Marker Stijl Ronde Dot maat 5.
Bewerken ga naar celbereik d3:d22 en met cel d3 de actieve en gemarkeerde cel, voer zonder aanhalingstekens de formule "=(b3-x_bar)^2" in en bewerk het invullen naar beneden.
Deel 3 van 3: nuttige begeleiding
- 1Maak gebruik van hulpartikelen bij het doorlopen van deze zelfstudie:
- Zie het artikel Hoe maak je een spiraalvormig spin-deeltjespad of kettingvorm of bolvormige rand voor een lijst met artikelen met betrekking tot Excel, geometrische en/of trigonometrische kunst, grafieken/diagrammen en algebraïsche formulering.
- Voor meer kunstgrafieken en grafieken kunt u ook klikken op Categorie: microsoft Excel- afbeeldingen, Categorie:wiskunde, Categorie:spreadsheets of Categorie:afbeeldingen om veel Excel-werkbladen en -grafieken te bekijken waarin trigonometrie, geometrie en calculus zijn omgezet in kunst, of klik gewoon op de categorie zoals weergegeven in het witte gedeelte rechtsboven op deze pagina, of linksonder op de pagina.
Bewerken Ga naar celbereik E3:E22 en met cel E3 de actieve en gemarkeerde cel, voer zonder aanhalingstekens de formule "=(C3-Y_BAR)^2" in en Bewerk Fill Down.
- FOUTEN: Als u fouten of foutwaarden hebt, is het blad incompleet en heeft u verdere invoer of opzoektabellen nodig voor kritieke variabelen of heeft u ergens een fout gemaakt. Als de instructies zijn voltooid en er nog steeds fouten zijn, selecteert u de cel met de foutwaarde die het meest links en bovenaan staat. Zoek naar een typefout in een formule of ongeëvenaarde haakjes. Mogelijk is een gedefinieerde naam verkeerd - ze moeten precies zo in de formules worden ingevoerd als ze zijn gedefinieerd. Voer Naam in Definieer in om te controleren. Als je DIV/0! hebt, heb ik dat niet, dus zoek naar een variabele die op de een of andere manier misschien niet is ingevuld met een waarde. Wat u in ieder geval wilt doen, is de cel met de fout selecteren en na het controleren van al die typische fouten, Tools Auditing Trace Precedents en/of Trace Error uitvoeren.Als het herstellen van alle fouten uiterst links niet de rest van je fouten op je werkblad oplost, moet je het misschien op de moeilijke manier doen, van rechtsonder naar boven en dan naar links; dat is de langzame maar zekere manier om alle fouten te herstellen.
- Ook zullen fouten in uw grafiekgegevens hoogstwaarschijnlijk als nullen worden weergegeven. Dit kan acceptabel of zelfs wenselijk zijn. Als echter te veel lijnen (of curven) terugkeren naar 0, kan dit duiden op een logische fout in de gegevens - of te veel kleine waarden en dan is het misschien nodig om de grafiek opnieuw te schalen door de horizontale en verticale assen te inspecteren en ze in nul te veranderen in op het probleem. Plaats de muisaanwijzer op of klik op een gegevensmarkering op de serieplot en zoek vervolgens in de juiste kolom op waarde voor die waarde en identificeer de precedenten ervan.
- Microsoft Excel. Het bovenstaande is gedaan met MicroSoft® Excel® voor Mac 2011, v 14,3.1 en andere versies zijn compatibel, met uitzondering van Reflection, Shadow, Glow en andere recentere effecten-toevoegingen.
Vragen en antwoorden
- Wat zijn de eigenschappen van exponentiële curve?Een exponentiële kromme gaat door het punt P(0|1) en is asymptotisch volgens de X-as.
