In het artikel Hoe een kort lineair regressiemodel in Excel te maken, werd niet getoond hoe een ellips rondom de gegevens moest worden opgenomen, d.w.z. een eerlijke gegevensgrens creëren, welk probleem nu is opgelost. Voor nieuwkomers worden stap 1 tot en met deel 3 stap 10 hier herhaald en dan beginnen de nieuwe modificatiestappen met deel 3, stap 11 (zodat als het werk al volgens het vorige artikel is gedaan, begin bij deel 3 stap 11). Er zullen nieuwe gegevens zijn die meer verspreid zijn in termen van variantie, om beter het nut van een elliptische benadering te illustreren - dat nieuwe gegevens zijn opgenomen in de onderstaande afbeelding.

Deel een van de drie:
De zelfstudie

  1. 1 Open een nieuwe werkmap in Excel en maak 3 werkbladen: Gegevens, grafiek en opslaan. Sla de werkmap op als Lineaire regressie - aangepaste les of iets dergelijks in een logische bestandsmap.
  2. 2 Voorkeuren instellen: Open voorkeuren in het Excel-menu. Aanbevolen instellingen: stel Algemeen in op R1C1 Uit en toon de 10 meest recente documenten; Bewerken - stel alle Top-opties in om te selecteren, behalve Automatisch datumsysteem omzetten. Geef het aantal decimalen weer = blanco (als voorkeur voor gehele getallen), behoud weergave van datums en stel 30 in voor 21e eeuw cut-off; Beeld - toon formulebalk en statusbalk, plaats de muisaanwijzer voor opmerkingen en alle objecten, toon rasterlijnen en alle vakken onder dat automatisch of aangevinkt; Grafiek - toon kaartnamen en datamarkeringen bij zweven. Laat rust voor nu uit; Berekening - Automatisch en calc voor opslaan, max. 0000000000000,01 w / o komma's wijzigen als doelzoeken veel wordt gedaan en externe verbindingswaarden opslaan en het 1904-systeem gebruiken; Foutcontrole - alles controleren; Opslaan - sla voorbeeldafbeelding op met nieuwe bestanden en bewaar Autorecover na 5 minuten; Lint - alle aangevinkt behalve Groepstitels verbergen en Ontwikkelaar.
  3. 3Selecteer tussen 1 en A in de linkerbovenhoek van het werkblad om het volledige blad te selecteren en voer Cellen uitlijnen horizontaal midden en tekengrootte 9 of 10 uit, of wat u maar prettig vindt om te bekijken.
  4. 4 Voer de kolomkoppen in rij 1 in: B1: X; C1: Y; D1: X. E1: Y F1: Y. Voer de kolomkoppen van rijen 2 in: A2: Student; B2 SAT; C2: GPA; D2: VARIANCE; E2: VARIANCE; F2: TREND, G2: wijzigen.
  5. 5 Ga naar celbereik A3 bewerken: A22, voer 1 in en bewerk Kolommen in vullingsreeks Lineaire stapwaarde 1 OK, er zijn 20 studenten die de trend van hun afhankelijke GPA's willen kennen gezien de onafhankelijke variabele van de score van het SAT-examen bij het betreden van een nieuw college, dat wil zeggen hun cijfers waarschijnlijk stijgen, vallen of ongeveer hetzelfde blijven?
  6. 6 Voer de gegevens in om deze vraag te beantwoorden. In x, y paren zoals weergegeven in de afbeelding hieronder, stap 22, of als volgt: voor student 1, X, Y = 935 voor X of SAT, 2,2 voor Y of GPA; student 2 1260,3,1; dan 1105,2.6; 1320,3.3; 1450,3.8; 960,2.2; 1360,3.2; 900,2.2; 1020,2.2; 1380,3.6; 940,2.3; 1190,2.8; 1000,2.2; 945,2.3; 990,2.4; 1000,2.2; 1040,2.3; 1570,3.9; 1530,3.8; 980,2.4 .
  7. 7 Selecteer cel A23 en voer MEAN in. Voer de formules zonder aanhalingstekens in cel B23 "= GEMIDDELDE (B3: B22)" in en kopieer deze en plak deze in cel C23. Selecteer cel C23 en naam invoegen Definieer naam Y_Bar voor cel $ C $ 23. Selecteer cel B23 en naam invoegen Definieer naam X_Bar voor cel $ B $ 23. Selecteer celbereik B23: C23 en formaat cellen Lettertypekleur Rood en Vet.
  8. 8 Bewerken Ga naar celbereik D3: D22 en met cel D3 de actieve en verlichte cel, voer zonder citaten de formule in "= (B3-X_BAR) ^ 2" en Bewerk uitvullen. Bewerken Ga naar celbereik E3: E22 en met cel E3 de actieve en verlichte cel, voer zonder citaten de formule in "= (C3-Y_BAR) ^ 2" en Bewerk uitvullen. Selecteer celbereik D3: D23 en doe Cellen opmaken Codenummer Decimaal plaatsen 4.
  9. 9 Selecteer cel D23 en voer zonder citaten de formule "= SOM (D3: D22) / (20-1)" in en kopieer deze en plak deze in cel E23. Selecteer celbereik E3: E23 en doe Cellen opmaken Cijfernummer Decimale plaatsen 6. Selecteer cel A24 en voer VARIANCE 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 voer Cellen opmaken Codenummer Decimaal plaatsen 6.
  10. 10 Selecteer cel A25 en voer STD DEV in. (voor standaarddeviatie) en selecteer cel B25 en voer zonder citaten de formule "= STDEVPA (B3: B22)" in en kopieer en plak deze in cel C25. Voer Cellen op nummer van decimale plaatsen 7 voor geselecteerd celbereik B25: C25.
  11. 11 Selecteer cel A27 en voer FORECAST in en selecteer cel B27 en voer zonder citaten de formule "= FORECAST (1290, C4: C23, B4)" in, dit is een matrixformule, dus u moet op CONTROL + SHIFT + ENTER drukken om de matrixformule met haakjes. Dat is de projectie van de GPA van één student, gegeven een SAT van 1290 en de rest van de populatie of voorbeeldgegevens. Hij / zij kan een GPA van 3.170409192 verwachten als u Cijfers met getalcijfer decimale plaatsen 9 hebt geformatteerd (die later precies overeenkomen). Niet dat zo'n accurate GPA nodig is, maar het gaat erom de formule te bewijzen, en dat vereist enige nauwkeurigheid.
  12. 12Bewerk Ga naar celbereik F3: F22 en voer de formule zonder aanhalingstekens in "= TREND (C3: C22, B3: B22,, TRUE)" wat een matrixformule is, dus u moet op CONTROL + SHIFT + ENTER drukken om de array correct in te voeren formule met haakjes. Dat is de projectie van de GPA's van de Student Population gezien hun eerdere prestaties.
  13. 13 Ga naar celbereik G3: G22 bewerken en met G3 de actieve cel met een hoge belichtingswaarde, voer de formule zonder aanhalingstekens in "= F3-C3" en Bewerk uitvullen. Cellen opmaken Cellen Nummer Nummer Aangepast +0,0; -0,0; +0,0.

Tweede deel van de drie:
Verklarende grafieken, diagrammen, foto's

  • (afhankelijk van de tutorial gegevens hierboven)
  1. 1 Maak de grafiek. Selecteer celbereik B3: C23 en ga naar het lint (of doe Kaartwizard) en selecteer Kaarten, Alles, scroll naar beneden naar Scatter, Gemarkeerde spreiding ... Bewerk vervolgens knip of kopieer het nieuwe diagram naar het werkblad Diagrammen. Doe Grafiekindeling Grafiektitel - Titel boven de grafiek en vul deze in met "Regressie - SATs versus GPA's" (zonder aanhalingstekens). Selecteer rasterlijnen Verticale rasterlijnen Grote rasterlijnen aangevinkt. Selecteer Axis Titles Titel horizontale as, Titel titel onder Axis en bewerk in "SAT Score" (zonder aanhalingstekens). Selecteer As Titels Verticale As Titel, Horizontale Titel en bewerk in "G.P.A." (zonder citaten). Volgens afspraak gaat de variabele Independent x op de onderste horizontale as en de afhankelijke y-variabele op de linker verticale as.
  2. 2 Zoek de gegevensmarkering voor de gemiddelde op 1143.75.2.755 die "Reeks 1 punt 1143.75" (1143.75, 2.8) zal lezen wanneer u eroverheen zweeft of erop hebt geklikt. Klik erop en voer het menu Opmaak, Gegevenspuntmarkeerstijl Automatisch formaat 9 uit en dan Markeervulkleur Rood.
  3. 3 Keer terug naar het Data-werkblad om de regressielijn van Y '= mX + b te berekenen, waarbij m = de helling en b = het y-snijpunt. Kijkend naar de kaartgegevens met lage waarden onder 10 aan de linkerkant en waarden rond 1000 aan de onderkant, zou men een zeer lichte decimale helling en een y-snijpunt in de buurt van 0 verwachten. Schaaldiagrammen kunnen soms een misleiding zijn met betrekking tot helling.
  4. 4 Selecteer cel G1 en voer Y in. Selecteer cel H1 en voer m in en kopieer H1 en plak deze op I1 via commando c, selecteer I1 en voer v in. Selecteer cel H2 en voer Teller in en selecteer cel I2 en voer Ondominatie in. Selecteer celbereik H3: H22 en voer met H3 als de actieve gemarkeerde cel zonder aanhalingstekens de formule "= (B3-X_BAR) * (C3-Y_BAR)" in. Bewerk Fill Down. Selecteer celbereik I3: I22 en met I3 als de actieve gemarkeerde cel, voer zonder citaten de formule "= (B3-X_BAR) ^ 2 in". Bewerk Fill Down. Selecteer de kolommen H en I en noteer Cellen Nummer Number Decimal Places 1.
  5. 5Selecteer cel H23 en voer de formule zonder aanhalingstekens in "= SOM (H3: H22)" en noteer Cellen rand zwart vet omtrek en kopieer deze naar I23.
  6. 6 Selecteer cel H24 en voer m in en voer Formaat Cellen lettertypekleur rood in. Kopieer het naar cel H25 en voer b in cel H25 in. Selecteer cel I24 en voer zonder citaten de formule "= H23 / I23" in, wat de helling m is, en selecteer vervolgens cel I25 en voer de formule zonder aanhalingstekens "= Y_BAR-I24 * X_BAR" in, dat wil zeggen, de y-snijpunt b = Y_Mean-m * X_Mean.
  7. 7 Kopieer cel H25 en plak deze in cel I26 en ga naar I26 Y '- mX + b. Selecteer cel H27 en voer Prognose in en voer vervolgens de formule in I27 zonder aanhalingstekens "= I24 * 1290 + I25" in. Uw antwoord moet exact gelijk zijn aan het ANTWOORDWOORD in B27 als u eenmaal Cellen heeft Number Number Decimal Places 9.
  8. 8 Bewerk de kopiecel I26 tot celbereik H29: I29. In cel H29 voer je 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 naar boven in de formulebalk - kopieer de cel niet en plak deze niet - wat niet goed werkt. Selecteer cel I30 en plak in de bewerkingsformulebalk de formule die u zojuist hebt gekopieerd. Doe hetzelfde voor cel I31. Bewerk cel I31's formule om "= I24 * H31 + I25" te lezen en druk op enter en bewerk de formule van cel I30 in de formulebalk om "= I24 * H30 + I25" te lezen en druk op enter. Het resultaat van I30'S zou 1.7786108729206 moeten zijn en het resultaat van I31 zou 4.05093465957812 moeten zijn (waarvan ik me realiseer dat het hoger is dan 4.0 GPA, maar we creëren een regressielijn, dus dat maakt niet zoveel uit.
  9. 9 Activeer het diagramwerkblad en klik in het diagram en maak in het menu Diagram Gegevens toevoegen en ga in reactie op de bereikquery terug naar het werkblad Gegevens en selecteer celbereik H30: I31. Nu komt de mijne verkeerd uit en moet ik de serie bewerken. Niet een groot probleem. Selecteer de gegevensmarkering en bewerk in de formulebalk de reeks om "= SERIES (, Blad1! $ H $ 30: $ H $ 31, Blad1! $ I $ 30: $ I $ 31,2)" te lezen en klik op de gegevensmarkering op 800,1.78 en maak lijn rood en weeg 0.75 pt, markeer vullingkleur rood en markeerstijl rond puntgrootte 5. Uw gegevens en grafiek moeten er als volgt uitzien:
  10. 10 Nu voor de wijzigingen, Voer eerst de nieuwe student-SAT-GPA gepaarde gegevens in:
    • Voer de gegevens in om de vraag te beantwoorden. In x, y paren zoals weergegeven in de afbeelding helemaal bovenaan dit artikel, of als volgt: voor student 1, X, Y in kolommen B en C = 990 voor X of SAT in B3, 2,2 voor Y of GPA IN C3; student 2 1150,3.2; dan 1080,2.6; 1100,3.3; 1280,3.8; 990,2.2; 1110,3.2; 920, 2.0; 1000,2.2; 1200,3.6; 1000, 2.2; 1200, 3.6; 1000, 2.1; 1150, 2.8; 1070, 2.2; 1120, 2.1; 1250, 2.4; 1550,3.9; 1480,3.8; 1010, 2.0.
  11. 11 Selecteer celbereik A24: C24 en Voeg cellen in - verschuif cellen naar beneden. Ga in cel A24 naar Midpoint. Voer in cel B24 de formule zonder aanhalingstekens "= xCenter" in en selecteer cel C24 en voer de waarde w / o in met de formule "= yCenter". Selecteer cel H32 en Naam invoegen Definieer naam xCenter in cel $ H $ 32 en selecteer I32 en Naam invoegen definieer Naam yCenter in cel $ I $ 32. Voer in H32 de formule zonder aanhalingstekens in "= (H30 + H31) / 2" en voer in I32 de formule zonder aanhalingstekens in "= (I30 + I31) / 2".
  12. 12 Voer doel zoeken uit op waardebereik van lijn XY. Voer in cel J30 de formule in, "= 2-I30" en voer Tools Doel zoeken in Stel cel J30 in op waarde 0 door de waarde in cel te wijzigen: H30. H30 zou ongeveer moeten worden. 920.690991; ga naar cel J31 voer de formule in, "= 4-I30" en doe Tools Doel Zoek cel J311 in op waarde 0, door de waarde in cel te wijzigen: H31. H31 zou ongeveer moeten worden. 1.212,61866
  13. 13 De volgende informatie wordt nog niet gebruikt. Het heeft te maken met het feit dat wanneer een regel wordt toegevoegd aan een cirkel het resultaat een ellips is. Selecteer cel I33 en voer de straal in en lijn het midden en onderstreep uit. Selecteer cel H34 en voer Dist of Line a in en selecteer cel I34 en voer de formule zonder aanhalingstekens in "= SQRT ((H31-H32) ^ 2 + (I31-I32) ^ 2)" - dat is het lange segment of regel a. Het zou = ongeveer moeten zijn. 291.9293847
  14. 14 Voer in rij 1 de gedefinieerde variabele namen van Ellipse in. K1: Aj_X; L1: Aj_Y; M1: m_Slope_; N1: Stretch_x; O1: Stretch_y; P1: onderscheppen
  15. 15 Voer de volgende waarden in rij 2 in: K2: 1200; L2: 4,15; M2: "= m"; N2: 0,0024950665406049 (bereikt door doelzoeken); O2: "= m-0,0005"; P2: "= b". Naam invoegen Definieer Naam m voor cel I24 en Naam invoegen Definieer naam voor b voor cel I25.
  16. 16Selecteer celbereik K1: P2 en Voeg namen in creëer namen in bovenste rij, OK.
  17. 17 Bewerk Ga naar celbereik K4: K54 en voer in de bovenste cel de formule in "= -2 * PI ()" en druk vervolgens op het tabblad shift + om naar de onderste cel te gaan en voer "= 2 * PI ()" in. Met celbereik K4: K54 geselecteerd, wijzigt u de Fill-reeks, voegt u lineaire kolommen toe, accepteert u de voorgestelde stapwaarde of raakt u Trend aan als de voorgestelde stapwaarde ten onrechte = 1, OK.
  18. 18Sla een kolom over en bewerk Ga naar celbereik M4: M54 en voer met M4 de actieve cel de formule zonder aanhalingstekens in "= SIN (K4)" en Bewerk uitvullen.
  19. 19Bewerken Ga naar celbereik N4: N54 en met N4 de actieve cel, voer de formule zonder aanhalingstekens in "= m_Slope_ * SIN ((K4-1))" en Bewerk uitvullen.
  20. 20Bewerken Ga naar celbereik O4: O54 en met O4 de actieve cel, voer de formule zonder aanhalingstekens in "= (M4) / Stretch_x + Aj_X" en Bewerk uitvullen.
  21. 21Bewerken Ga naar celbereik P4: P54 en met P4 de actieve cel, voer de formule zonder aanhalingstekens in "= (N4) / Stretch_y + Aj_Y + Onderscheppen" en Bewerk uitvullen.
  22. 22 Maak een klein werkbladschema. Selecteer celbereik O4: P54 en gebruik de Kaartwizard of het lint door alle / andere kaarten te schuiven en scroll naar beneden naar Scatter Smoothed Line Scatter en er verschijnt een kleine grafiek boven uw gegevens. Belangrijk: de x-waarden moeten tussen 800 en 1600 liggen en de y-waarden moeten tussen ongeveer 1,8 en 4,1 liggen. Als dit niet het geval is, zoekt u naar een misplaatst decimaalteken in de bovenste rijen 2 van gedefinieerde variabelen of een verschil tussen uitberekeningen van m en b. Anders is het een fout bij het invoeren van gegevens ergens langs de lijn, om een ​​kolom tegelijk te controleren. Zie ook het gedeelte Waarschuwingen hieronder voor fouten.
  23. 23 Klik op de reeksplanning van de nieuwe kleine grafiek op het gegevensblad en Command + c kopieer deze, ga vervolgens naar het diagramwerkblad en klik in het diagram en Command + v plak het. Als het werkt zoals het mijne, is het FUBAR en moet het een reeks tegelijk worden gecorrigeerd.
  24. 24 Als de regressielijn nog steeds wordt weergegeven, bewerkt u de reeks in de formulebalk aan de rechterkant van het eerste haakje door met aanhalingstekens 'Regressielijn' vóór de komma te typen. Druk vervolgens op de pijl-omlaag op uw toetsenbord om toegang te krijgen tot Reeks 1 en voer Lijnlay-out voor grafieken bewerken (geen lijn), Markeerstijl rood formaat 5, Markeringsvulling - Rood uit en bewerk deze in de reeks in de formulebalk voorbij het eerste linkerhaakje met aanhalingstekens , "SAT-GPA paren" vóór de eerste komma.
  25. 25 Druk één of twee keer op OK en vervolgens op de Enter-toets en de Omlaag-toets om toegang te krijgen tot Series 3, de Ellipse. Doe Diagram Opmaak Indelingsselectie, Marker Fill Blauw-groen, Markeringslijn Automatisch met Smoothed Line aangevinkt, OK.
  26. 26 Activeer het Data-werkblad en selecteer celbereik B23: C24 en kopieer en activeer het Chart-werkblad en Command + v plak het bereik in. Het werkt niet goed voor mij en ik moet het bewerken en een extra serie verwijderen. Wat u uiteindelijk wilt, is een reeks die in de formulebalk staat: "= SERIES (" Mean and Midpoint ", Data! $ B $ 23: $ B $ 24, Data! $ C $ 23: $ C $ 24,4) ", No Marker Line, Marker Style Round Dot, Size 9, Marker Fill Dark Blue or Purple.
  27. 27Ga naar afbeelding Word Art invoegen om een ​​titel voor de grafiek te maken die enige glans heeft.
  28. Overschakelen naar regressiegegevens 28
    Gedaan!    ! Goed werk. In een volgend artikel kan worden onderzocht hoe de verschillende sectoren van de ellips kunnen worden geanalyseerd. Voorlopig is de omgeving van de dataset bereikt, wat het doel was. Voor degenen die graag aan deze analyse willen beginnen, een hint: bij het berekenen van het gebied van een schuine ellipssector: kijk, doe net alsof je in het vlak bent waar het niet schuin staat en slechts een normale ellips is. Dat is de aanbevolen aanpak van mijn mentor. Tip # 2: kijk naar je gegevens om de lengte van regel b te vinden. In Tips zijn de gegevens en de kleine grafiek voor de niet-aangepaste gehelde ellips opgenomen die u als startpunt in uw analyses kunt gebruiken.

Derde deel van de drie:
Nuttige begeleiding

  1. 1 Maak gebruik van helper-artikelen bij het doorlopen van deze tutorial:
    • Zie het artikel Hoe maak je een Spirallic Spin Particle Path of Kettingvorm of Sferische rand voor een lijst van artikelen met betrekking tot Excel, Geometrische en / of trigonometrische kunst, grafieken / diagrammen en algebraïsche formulering.
    • Voor meer kunstgrafieken en grafieken, wilt u misschien ook klikken op Categorie: Microsoft Excel-afbeeldingen, Categorie: Wiskunde, Categorie: Spreadsheets of Categorie: afbeeldingen om veel Excel-werkbladen en -grafieken weer te geven waarbij trigonometrie, meetkunde en calculus zijn omgezet in kunst, of klik eenvoudig op de categorie zoals verschijnt in het witte gedeelte rechtsboven op deze pagina, of linksonder op de pagina.