I artiklen Sådan oprettes en kort lineær regressionsmodel i Excel, blev det ikke vist, hvordan man indbefatter en ellipse, der omgiver dataene, dvs. skabe en retfærdig datagrind, hvilket problem nu er blevet løst. For nybegyndere, vil trin 1 til del 3 trin 10 blive gentaget her, og så begynder de nye modifikationstrin med del 3 trin 11 (så hvis arbejdet allerede er gjort i henhold til den foregående artikel, skal du starte på del 3 trin 11). Der vil være nye data, der er mere spredt ud fra varians, for bedre at illustrere brugen af ​​en elliptisk tilgang - at nye data er inkluderet i billedet nedenfor.

Første del af tre:
Tutorial

  1. 1 Åbn en ny projektmappe i Excel og lav 3 regneark: Data, diagram og gemmer. Gem arbejdshandlen som en lineær regressionsmodificeret lektion eller noget lignende i en logisk filmappe.
  2. 2 Indstil præferencer: Åbn indstillinger i Excel-menuen. Anbefalede indstillinger: Indstil Generelt til R1C1 Fra og Vis de 10 seneste dokumenter; Rediger - Indstil alle de øverste indstillinger, der skal markeres, undtagen automatisk konverter dato system. Vis antal decimaler = tomt (for heltal foretrukne), Bevar visning af datoer og sæt 30 for 21st century cutoff; View - vis Formula Bar og Status Bar, svæv for kommentarer og alle objekter, Vis gridlinjer og alle felter under den pågældende auto eller markeret; Diagram - vis diagramnavne og data markører på svømmeren. Lad hvile ukontrolleret for nu; Beregning - Automatisk og beregning før gemme, maks. Ændring. 000,000,000,000,01 uden kommaer som målsøgning gøres meget og gemmer eksterne linkværdier og bruger 1904 system; Fejl kontrol - check alle; Gem - Gem forhåndsvisningsbillede med nye filer og Gem Autorecover efter 5 minutter; Bånd - alt markeret undtagen Skjul gruppetitler og Udvikler.
  3. 3Vælg mellem 1 og A i øverste venstre hjørne af regnearket for at vælge hele arket og gøre Format Cells Alignment Horizontal Center og Skriftstørrelse 9 eller 10, eller hvad du er komfortabel med visning.
  4. 4 Indtast kolonneoverskrifterne i række 1: B1: X; C1: Y; D1: X. E1: Y F1: Y. Indtast kolonneoverskrifterne i rækker 2: A2: Student; B2 SAT; C2: GPA; D2: VARIANCE; E2: VARIANCE; F2: TREND, G2: Skift.
  5. 5 Rediger Gå til celleområde A3: A22, indtast 1 og gør Rediger Fill Series Columns Lineær Trin Værdi 1 OK, Der er 20 studerende, der ønsker at kende Trenden af ​​deres afhængige GPA'er, der får den uafhængige variabel af SAT Exam score når de går ind i et nyt kollegium, dvs. er deres karakterer sandsynligvis stige, falde eller forblive omkring det samme?
  6. 6 Indtast dataene for at besvare dette spørgsmål. I x, y par som vist på billedet under Trin 22, eller som følger: for elev 1, X, Y = 935 for X eller SAT, 2.2 for Y eller GPA; studerende 2 1260,3,1; derefter 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 Vælg celle A23 og indtast MEAN. Indtast formlerne uden citerer i celle B23 "= AVERAGE (B3: B22)" og kopier det og indsæt det i celle C23. Vælg celle C23 og Indsæt navn Definer navn Y_Bar for celle $ C $ 23. Vælg celle B23 og Indsæt navn Definer navn X_Bar for celle $ B $ 23. Vælg celleområde B23: C23 og Format celler Font farve Rød og fed.
  8. 8 Rediger Gå til celleinterval D3: D22 og med celle D3 den aktive og højlysede celle, skriv uden citerer formlen "= (B3-X_BAR) ^ 2" og Rediger udfyld. Rediger Gå til celleinterval E3: E22 og med celle E3 den aktive og højlysede celle, angiv uden citerer formlen "= (C3-Y_BAR) ^ 2" og Rediger udfyld. Vælg celleområde D3: D23 og formater celler Nummer Antal Decimale steder 4.
  9. 9 Vælg celle D23 og indtast w / o citerer formlen "= SUM (D3: D22) / (20-1)" og kopier den og indsæt den i celle E23. Vælg celleområde E3: E23 og formater celler Nummer Antal Decimale steder 6. Vælg celle A24 og indtast VARIANCE og vælg celle B24 og indtast formlen "= D23" og kopier den og indsæt den i celle C24. Vælg celleområde B24: D25 og formater celler Nummer Antal Decimale steder 6.
  10. 10 Vælg celle A25 og indtast STD DEV. (for standardafvigelse) og vælg celle B25 og indtast w / o citerer formlen "= STDEVPA (B3: B22)" og kopier og indsæt den i celle C25. Gør Format Celler Nummer Antal Decimale Steder 7 for valgt celleområde B25: C25.
  11. 11 Vælg celle A27 og indtast FORECAST og vælg celle B27 og indtast uden citationstegn formlen "= FORECAST (1290, C4: C23, B4)", som er en matrixformel, så du skal trykke på CONTROL + SHIFT + ENTER for at indtaste korrekt matrixformel med parentes. Det er projektionen af ​​en elevs GPA givet et SAT på 1290 og resten af ​​befolkningen eller prøve data. Han / hun må forvente en GPA på 3.170409192, når du formaterer celle nummer nummer decimale steder 9 (som matches senere, nøjagtigt). Ikke at sådan en nøjagtig GPA er nødvendig, men det er meningen at bevise formlen, og det kræver en vis nøjagtighed.
  12. 12Rediger Gå til celleområde F3: F22 og indtast formlen uden citationstegn "= TREND (C3: C22, B3: B22,, TRUE)", som er en matrixformel, så du skal trykke på CONTROL + SHIFT + ENTER for at indtaste ordningen korrekt formel med parenteser.Det er projiceringen af ​​studenternes befolkning's GPA'er givet deres tidligere præstationer.
  13. 13 Rediger Gå til celleområde G3: G22 og med G3 den aktive højlysede celle, indtast formularen uden citationstegn "= F3-C3" og Rediger udfyld. Gør formatceller nummernummer Tilpasset +0,0; -0,0; +0,0.

Del to af tre:
Forklarende diagrammer, diagrammer, billeder

  • (afhængig af vejledningsdata ovenfor)
  1. 1 Opret diagrammet. Vælg celleområde B3: C23 og gå til båndet (eller lav diagramguiden), og vælg Diagrammer, Alle, rul ned til Scatter, Marked Scatter ... Derefter Rediger skære eller kopi det nye diagram til diagramarket. Gør Diagram Layout Diagram Titel - Titel ovenfor Diagram og udfyld det med "Line Regression - SATs vs GPAs" (uden citater). Vælg Gridlines Vertikale Gridlines Major Gridlines tjekket. Vælg Axis Titles Horizontal Axis Title, Titel Titel Under Axis og Rediger i "SAT Score" (uden citater). Vælg Akseltitler Lodret Akse Titel, Horisontalt Titel og Rediger i "G.P.A." (uden citater). Ved konventionen går den uafhængige x-variabel på den nederste vandrette akse, og afhængig y-variablen går til venstre lodret akse.
  2. 2 Find datapunktoren for gennemsnittet på 1143.75,2.755, som vil læse "Serie 1 punkt 1143.75" (1143.75, 2.8), når du svæver over det eller har klikket på det. Klik på det og gør menu Format, Data Point Marker Style Automatisk størrelse 9, og marker Marker Fill Color Red.
  3. 3 Gå tilbage til Data-regnearket for at finde ud af regressionslinjen for Y '= mX + b, hvor m = hældningen og b = y-afsnit. Når man ser på diagramdataene med lave værdier under 10 til venstre og værdier omkring 1000 på bunden, ville man forvente en meget lille decimalhældning og en y-intercept tæt på 0. Skalede diagrammer kan bedrage i tilfælde af hældning nogle gange.
  4. 4 Vælg celle G1 og indtast Y. Vælg celle H1 og indtast m og kopier H1 og indsæt den i I1 via kommando c, vælg I1, og kommando v. Vælg celle H2 og indtast Numerator og vælg celle I2 og indtast Nøgler. Vælg celleinterval H3: H22 og med H3 som den aktive fremhævede celle, indtast uden citerer formlen "= (B3-X_BAR) * (C3-Y_BAR)". Rediger Fyld ned. Vælg celleinterval I3: I22 og med I3 som den aktive fremhævede celle, skriv uden citerer formlen "= (B3-X_BAR) ^ 2". Rediger Fyld ned. Vælg kolonner H og I og Format Cells Nummer Antal Decimale Steder 1.
  5. 5Vælg celle H23 og indtast formlen m / o citerer "= SUM (H3: H22)" og Format Celler Border Black bold Outline og kopier den til I23.
  6. 6 Vælg celle H24 og indtast m og lav Format Cells Font Color Red. Kopier det til celle H25 og indtast b i celle H25. Vælg celle I24 og indtast w / o citerer formlen "= H23 / I23", som er hældningen m, og vælg derefter celle I25 og indtast formlen uden citationstegn "= Y_BAR-I24 * X_BAR", det vil sige y-afsnit b = Y_Mean-m * X_Mean.
  7. 7 Kopier celle H25 og indsæt den i celle I26 og indtast I26 Y '- mX + b. Vælg celle H27 og indtast prognose, og indtast derefter formlen i I27 uden citationstegn "= I24 * 1290 + I25". Dit svar skal nøjagtigt svare til FORECAST svaret i B27, når du har Format Cells Number Number Decimal Places 9.
  8. 8 Rediger kopieringscelle I26 til celleinterval H29: I29. Ind i celle H29 indtast linie X og ind i celle I29 indtaste linie Y. Indtast 800 i celle H30 og 1600 i celle H31. Vælg celle I 27 og kopier dens formel op i formellelinjen - kopier ikke cellen og indsæt den - det virker ikke korrekt. Vælg celle I30 og indsæt i formel Formula Bar den formel, du lige har kopieret. Gør det samme for celle I31. Rediger celle I31s formel for at læse "= I24 * H31 + I25" og tryk på enter og rediger derefter celle I30s formel i formellinjen for at læse "= I24 * H30 + I25" og tryk enter. I30'S resultat skal være 1.7786108729206 og I31s resultat skal være 4.05093465957812 (hvilket jeg ved er højere end en 4,0 GPA, men vi skaber en regressionslinje, så det betyder ikke noget så meget.
  9. 9 Aktivér diagrammets regneark og klik på diagrammet og i menuen, lav diagram Tilføj data og svar på rækkefølge forespørgslen, gå tilbage til Data regnearket og vælg celleområde H30: I31. Nu kommer min ud forkert, og jeg er nødt til at redigere serien. Ikke noget særligt. Vælg data markøren og i formellelinjen, rediger serien for at læse "= SERIES (, Sheet1! $ H $ 30: $ H $ 31, Sheet1! $ I $ 30: $ I $ 31,2)" og klik på datapunktet på 800,1.78 og lav Line rød og Vægt 0.75 pt, derefter Marker Fill Color Red og derefter Marker Style Round punktstørrelse 5. Dine data og diagram skal se sådan ud:
  10. 10 Nu for ændringerne, Først skal du indtaste de nye paratiske SAT-GPA parrede data:
    • Indtast dataene for at besvare spørgsmålet. I x, y par som vist på billedet øverst i denne artikel eller som følger: for elev 1, X, Y i kolonne B og C = 990 for X eller SAT i B3, 2.2 for Y eller GPA IN C3; studerende 2 1150,3,2; derefter 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 Vælg celleområde A24: C24 og Insert Celler - Skiftceller ned. Ind i celle A24 indtast Midpoint. I celle B24 indtastes formlen uden citationstegn "= xCenter" og vælg celle C24 og input w / o citerer formlen "= yCenter". Vælg celle H32 og Indsæt navn Definer navn xCenter til celle $ H $ 32, og vælg I32 og Indsæt navn definer Name yCenter til celle $ I $ 32. I H32 indtastes formlen m / o citater "= (H30 + H31) / 2" og ind i I32 indtaste formlen m / o citater "= (I30 + I31) / 2".
  12. 12 Udfør mål Søgning på værdiområde af Linje XY. I cellen J30 indtastes formlen, "= 2-I30" og gør Værktøjer Målsøgning Indstil celle J30 til værdi 0 ved at ændre værdien i celle: H30. H30 skal blive ca. 920.690991; Gå til celle J31 Indtast formlen, "= 4-I30" og gør Værktøjer Målsøgning Indstil celle J311, til værdi 0, ved at ændre værdien i celle: H31. H31 skal blive ca. 1212,61866
  13. 13 Følgende oplysninger er ikke brugt endnu. Det har at gøre med, at når en linje tilføjes til en cirkel, er resultatet en ellipse. Vælg celle I33 og indtast radius og juster center og understreger. Vælg celle H34 og indtast Dist of Line a, og vælg celle I34 og indtast formlen uden citationstegn "= SQRT ((H31-H32) ^ 2 + (I31-I32) ^ 2)" - det er det lange segment eller linje a. Det skal = ca. 291.9293847
  14. 14 I række 1 skal du indtaste Ellipse's Defined Variable navne. K1: Aj_X; L1: Aj_Y; M1: m_Slope_; N1: Stretch_x; O1: Stretch_y; P1: Intercept
  15. 15 Indtast følgende værdier i række 2: K2: 1200; L2: 4,15; M2: "= m"; N2: 0,0024950665406049 (opnået ved målsøgning); O2: "= m-0,0005"; P2: "= b". Indsæt Navn Definer Navn m for Celle I24, og Indsæt Navn Definer navn for b for Celle I25.
  16. 16Vælg celleområde K1: P2 og Indsæt navne Opret navne i øverste række, OK.
  17. 17 Rediger Gå til celleområde K4: K54 og ind i den øverste celle indtast formlen "= -2 * PI ()" og tryk derefter på skift + fanen for at gå til den nederste celle og indtast "= 2 * PI ()". Med celleinterval K4: K54 valgt, gør Edit Fill Series, kolonner lineære, accepter den foreslåede trinværdi eller hit Trend, hvis den foreslåede trinværdi fejlagtigt = 1, OK.
  18. 18Spring en kolonne over og Rediger Gå til celleområde M4: M54 og med M4 den aktive celle, skriv formlen uden citationstegn "= SIN (K4)" og Rediger udfyld.
  19. 19Rediger Gå til celleområde N4: N54 og med N4 den aktive celle, indtast formlen m / o citater "= m_Slope_ * SIN ((K4-1))" og Rediger Fyld ned.
  20. 20Rediger Gå til celleområde O4: O54 og med O4 den aktive celle, indtast formlen uden citationstegn "= (M4) / Stretch_x + Aj_X" og Rediger udfyld.
  21. 21Rediger Gå til celleområde P4: P54 og med P4 den aktive celle, skriv formlen uden citationstegn "= (N4) / Stretch_y + Aj_Y + Intercept" og Rediger udfyld.
  22. 22 Lav et lille regneark. Vælg celleområde O4: P54 og brug diagramguiden eller Ribbon do Charts All / Other og rul ned til Scatter Smoothed Line Scatter, og et lille diagram skal vises over dine data. Vigtigt: X-værdierne bør variere fra 800 til 1600, og y-værdierne skal ligge fra ca. 1,8 til 4,1. Hvis dette ikke er tilfældet, kig efter et misplaceret decimaltal i de øverste rækker 2 af definerede variabler eller en forskel mellem udregninger af m og b. Ellers er det en dataindtastningsfejl et sted langs linjen, der skal kontrolleres en kolonne ad gangen. Se også advarselssektionen nedenfor på fejl.
  23. 23 Klik på seriens plot af det nye lille diagram på databladet og kommandoen + c kopier det, og gå til diagrammets regneark og klik på diagrammet og kommandoen + v indsæt det. Hvis det virker som min, er det FUBAR og skal rettes en serie ad gangen.
  24. 24 Hvis regressionslinjen stadig vises, skal du redigere sin serie i formellelinjen til højre for den første parentes ved at indtaste med citater "Regression Line" før kommaet. Tryk derefter på pil ned på tastaturet for at få adgang til Serie 1 og redigér Linje (Ingen linje), Marker Style rød størrelse 5, Marker Fill - Rød og rediger ind i serien i formellelinjen forbi den første venstre parentes med citater , "SAT-GPA Par" før det første komma.
  25. 25 Tryk OK og derefter Enter nøglen og Nede nøglen en eller to gange for at få adgang til Serie 3, som er Ellipse. Vælg Valg af diagramlayoutformat, Marker Fill Blå-Grøn, Marker linje Automatisk med Glat linje kontrolleret, OK.
  26. 26 Aktiver dataarket og vælg celleområde B23: C24 og kopier og aktiver diagramarket og Kommand + v indsæt rækkevidden i. Det virker ikke rigtigt for mig, og jeg skal redigere det og slette en ekstra serie udover, hvad du vil ende med er serier, der læses i formellelinjen, "= SERIES (" Middel og Midpoint ", Data! $ B $ 23: $ B $ 24, Data! $ C $ 23: $ C $ 24,4) ", Ingen markørlinje, Marker Style Round Dot, Size 9, Marker Fill Mørkeblå eller Lilla.
  27. 27Gå til Indsæt billedordskunst for at oprette et overskrift til diagrammet, der har noget glans til det.
  28. Ellipse til at tilpasse regressionsdata 28
    Færdig!    ! Godt job. I en fremtidig artikel kan man analysere de forskellige sektorer af ellipse. For nu er omgivelserne af datasættet opnået, hvilket var målet. For dem, der er ivrige efter at komme i gang med denne analyse, et tip: Når man beregner området af en skrå ellipsesektor: Se, foregiv du er i flyet, hvor den ikke er skrå og er bare en normal ellipse. Det er den anbefalede tilgang fra min mentor. Tip # 2: Se på dine data for at finde længden af ​​linje b. I Tips indgår dataene og det lille diagram for den ujusterede skrå ellipse, som du kan bruge som udgangspunkt i dine analyser.

Del tre af tre:
Nyttig vejledning

  1. 1 Gør brug af hjælperartikler, når du fortsætter gennem denne vejledning:
    • Se artiklen Sådan oprettes en Spirallic Spin Particle Path eller Halskæde Form eller Sfærisk Border for en liste over artikler relateret til Excel, Geometrisk og / eller Trigonometrisk Kunst, Kortlægning / Diagrammering og Algebraisk Formulering.
    • For flere kunstdiagrammer og grafer kan du også klikke på Kategori: Microsoft Excel Imagery, Kategori: Matematik, Kategori: Regneark eller Kategori: Grafik for at se mange Excel regneark og diagrammer, hvor Trigonometri, Geometri og Calculus er blevet omdannet til Kunst, eller klik blot på kategorien som vist i øverste højre hvide del af denne side eller nederst til venstre på siden.