Im Artikel Wie man ein kurzes lineares Regressionsmodell in Excel erstellt, wurde nicht gezeigt, wie man eine Ellipse um die Daten einfügt, d. H. Eine faire Datengrenze erzeugt, welches Problem nun gelöst wurde. Für Neueinsteiger werden Schritte 1 bis 3 Schritt 10 hier wiederholt und dann beginnen die neuen Änderungsschritte mit Teil 3 Schritt 11 (so dass, wenn die Arbeit bereits gemäß dem vorherigen Artikel erfolgt ist, bitte bei Teil 3, Schritt 11, beginnen). Es wird neue Daten geben, die in Bezug auf die Varianz gestreut sind, um den Nutzen eines elliptischen Ansatzes besser zu veranschaulichen - dass neue Daten in dem Bild unten enthalten sind.
Erster Teil von Drei:
Das Tutorial
- 1 Öffnen Sie eine neue Arbeitsmappe in Excel und erstellen Sie 3 Arbeitsblätter: Daten, Diagramm und speichert. Speichern Sie die Arbeitsmappe als "Lineare Regression - Modifizierte Lektion" oder etwas Ähnliches in einem logischen Dateiordner.
- 2 Voreinstellungen festlegen: Öffnen Sie Einstellungen im Excel-Menü. Empfohlene Einstellungen: Setzen Sie General auf R1C1 Aus und zeigen Sie die 10 neuesten Dokumente an; Bearbeiten - Legen Sie alle Optionen für "Oben" fest, außer "Datum automatisch konvertieren". Anzahl der Dezimalstellen anzeigen = leer (für Ganzzahlen bevorzugt), Anzeige von Datumsangaben beibehalten und 30 für das 21. Jahrhundert einstellen; Ansicht - Zeige Formelleiste und Statusleiste, schwebe für Kommentare und alle Objekte, Zeige Gitternetzlinien und alle Kästchen unterhalb dieser automatisch oder markiert; Chart - Zeigt Chartnamen und Datenmarkierungen bei Hover an. Lassen Sie den Rest für jetzt deaktiviert; Berechnung - Automatisch und vor dem Speichern berechnen, max ändern .000.000.000.000.000,01 ohne Kommas als Ziel suchen ist viel getan und speichern externe Link-Werte und verwenden 1904 System; Fehlerprüfung - alle überprüfen; Speichern - Vorschaubild mit neuen Dateien speichern und Autorecover nach 5 Minuten speichern; Multifunktionsleiste - alle markiert außer Gruppentitel und Entwickler ausblenden.
- 3Wählen Sie zwischen der 1 und dem A in der oberen linken Ecke des Arbeitsblatts, um das gesamte Blatt auszuwählen, und richten Sie dann Ausrichtung der Zellen Ausrichtung Horizontal Center und Schriftgröße 9 oder 10 oder was immer Sie sich gerade ansehen möchten aus.
- 4 Geben Sie die Spaltenüberschriften in Zeile 1 ein: B1: X; C1: Y; D1: X. E1: Y F1: Y. Geben Sie die Spaltenüberschriften der Zeilen 2 ein: A2: Student; B2 SAT; C2: GPA; D2: VARIANZ; E2: Abweichung; F2: TREND, G2: Ändern.
- 5 Bearbeiten Gehe zu Zellbereich A3: A22, geben Sie 1 ein, und bearbeiten Sie die Füllreihenspalten Linearer Schrittwert 1 OK, Es gibt 20 Studenten, die den Trend ihrer abhängigen GPAs wissen wollen, wenn sie die neue unabhängige Prüfungsgruppennummer eingeben wahrscheinlich steigen, fallen oder in etwa gleich bleiben?
- 6 Geben Sie die Daten ein, um diese Frage zu beantworten. In x, y Paaren wie im Bild unten Schritt 22, oder wie folgt: für Schüler 1, X, Y = 935 für X oder SAT, 2.2 für Y oder GPA; Schüler 2 1260,3,1; dann 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 Wählen Sie Zelle A23 und geben Sie MEAN ein. Geben Sie die Formeln ohne Anführungszeichen in Zelle B23 "= DURCHSCHNITT (B3: B22)" ein und kopieren Sie sie und fügen Sie sie in Zelle C23 ein. Markieren Sie die Zelle C23 und geben Sie den Namen ein Geben Sie den Namen Y_Bar für die Zelle $ C $ 23 ein. Wählen Sie die Zelle B23 und den Namen einfügen Definieren Sie den Namen X_Bar für die Zelle $ B $ 23. Wählen Sie den Zellenbereich B23: C23 und Zellen formatieren Schriftfarbe Rot und Fett.
- 8 Bearbeiten Gehe zu Zellbereich D3: D22 und mit Zelle D3 die aktive und hervorgehobene Zelle, geben Sie ohne die Formel "= (B3-X_BAR) ^ 2" und Edit Fill Down ein. Bearbeiten Gehe zu Zellbereich E3: E22 und mit Zelle E3 die aktive und hervorgehobene Zelle, geben Sie ohne die Formel "= (C3-Y_BAR) ^ 2" und Edit Fill Down ein. Wählen Sie den Zellenbereich D3: D23, und formatieren Sie die Zellen. Anzahl Dezimalstellen 4.
- 9 Wählen Sie Zelle D23 und geben Sie ohne Anführungszeichen die Formel "= SUM (D3: D22) / (20-1)" ein und kopieren Sie sie und fügen Sie sie in Zelle E23 ein. Wählen Sie den Zellenbereich E3: E23 und formatieren Sie Zellen Nummer Dezimalstellen 6. Wählen Sie Zelle A24 und geben Sie VARIANCE ein und wählen Sie Zelle B24 und geben Sie die Formel "= D23" ein und kopieren Sie sie in Zelle C24. Wählen Sie den Zellbereich B24: D25 und formatieren Sie die Anzahl der Dezimalstellen.
- 10 Wählen Sie Zelle A25 und geben Sie STD DEV ein. (für Standardabweichung) und wählen Sie Zelle B25 und geben Sie ohne Anführungszeichen die Formel "= STDEVPA (B3: B22)" und kopieren Sie es und fügen Sie es in Zelle C25. Formatieren Sie die Anzahl der Dezimalzahlen für die Anzahl der Zellen 7 für den ausgewählten Zellenbereich B25: C25.
- 11 Markieren Sie die Zelle A27 und geben Sie FORECAST ein und wählen Sie Zelle B27 und geben Sie ohne die Formel "= FORECAST (1290, C4: C23, B4)" ein. Dies ist eine Matrixformel, so dass Sie CONTROL + SHIFT + ENTER drücken müssen Array-Formel mit Klammern. Das ist die Projektion des GPA eines Studenten bei einem SAT von 1290 und dem Rest der Bevölkerung oder Stichprobendaten. Er / Sie kann eine GPA von 3,170409192 erwarten, sobald Sie Zellen Number Number Decimal Places 9 formatieren (die später genau angepasst wird). Nicht, dass solch eine genaue GPA notwendig ist, aber der Punkt ist, die Formel zu beweisen, und das erfordert eine gewisse Genauigkeit.
- 12Bearbeiten Gehe zum Zellenbereich F3: F22 und gebe die Formel ohne Anführungszeichen "= TREND (C3: C22, B3: B22,, TRUE)" ein, was eine Matrixformel ist, also musst du STRG + UMSCHALT + EINGABE drücken, um das Array richtig einzugeben Formel mit Klammern. Dies ist die Projektion der GPAs der Schülerpopulation aufgrund ihrer früheren Leistung.
- 13 Bearbeiten Sie Gehe zu Zellbereich G3: G22 und mit G3 die aktive hervorgehobene Zelle, geben Sie die Formel w / o Anführungszeichen "= F3-C3" und bearbeiten Sie Fill Down. Formatieren Zellen Nummer Nummer Benutzerdefiniert +0.0; -0.0; +0.0.
Zweiter Teil von Drei:
Erläuterungsdiagramme, Diagramme, Fotos
- (abhängig von den obigen Tutorial-Daten)
- 1 Erstellen Sie das Diagramm. Wählen Sie den Zellenbereich B3: C23 und gehen Sie zur Multifunktionsleiste (oder führen Sie den Diagrammassistenten aus) und wählen Sie Diagramme, Alle, scrollen Sie nach unten zu Punktstreuung, Markierte Punktstreuung ... Dann Bearbeiten Ausschneiden oder Kopieren des neuen Diagramms in das Diagrammarbeitsblatt. Chart Layout Chart Titel - Titel über Chart und fülle es mit "Line Regression - SATs vs. GPAs" (ohne Anführungszeichen). Wählen Sie Gitterlinien Vertikale Gitterlinien Hauptgitterlinien markiert. Wählen Sie Achsentitel Titel der horizontalen Achse, Titel Titel unterhalb der Achse und bearbeiten Sie sie in "SAT Score" (ohne Anführungszeichen). Wählen Sie Achsentitel Titel der vertikalen Achse, Horizontaltitel und bearbeiten Sie sie in "G.P.A." (ohne Anführungszeichen). Per Konvention geht die Variable Independent x auf die untere horizontale Achse und die Variable Dependent y auf die linke vertikale Achse.
- 2 Suchen Sie den Datenmarker für den Mittelwert bei 1143.75,2.755, der "Serie 1 Punkt 1143.75" (1143.75, 2.8) anzeigt, wenn Sie darüber schweben oder darauf geklickt haben. Klicken Sie darauf und machen Sie Menü Format, Datenpunkt Marker Stil Automatisch Größe 9, dann Marker Fill Color Red.
- 3 Kehren Sie zum Datenarbeitsblatt zurück, um die Regressionslinie von Y '= mX + b herauszufinden, wobei m = die Steigung und b = der y-Achsenabschnitt ist. Betrachtet man die Diagrammdaten mit niedrigen Werten unter 10 auf der linken und Werten um 1000 auf der Unterseite, würde man eine sehr leichte Dezimalflanke und einen y-Achsenabschnitt nahe 0 erwarten. Skalierte Diagramme können manchmal täuschen, wenn sie abfallen.
- 4 Markieren Sie Zelle G1 und geben Sie Y ein. Markieren Sie Zelle H1 und geben Sie m ein und kopieren Sie H1 und fügen Sie sie über Befehl c in I1 ein, wählen Sie I1 und Befehl v. Wählen Sie Zelle H2 und geben Sie Numerator ein und wählen Sie Zelle I2 und geben Sie Nenner ein. Markieren Sie den Zellbereich H3: H22 und mit H3 als aktiv markierte Zelle, geben Sie ohne die Formel "= (B3-X_BAR) * (C3-Y_BAR)" ein. Bearbeiten Sie Ausfüllen. Wählen Sie den Zellbereich I3: I22 und mit I3 als aktiv markierte Zelle, geben Sie ohne die Formel "= (B3-X_BAR) ^ 2" ein. Bearbeiten Sie Ausfüllen. Wählen Sie die Spalten H und I und formatieren Sie die Anzahl der Zellen mit den Dezimalstellen 1.
- 5Markieren Sie die Zelle H23 und geben Sie die Formel ohne Anführungszeichen "= SUM (H3: H22)" und Zellen formatieren umrandet schwarz fett Umriss ein und kopieren Sie sie in I23.
- 6 Markieren Sie die Zelle H24 und geben Sie m ein und formatieren Sie die Schriftfarbe für die Schriftfarbe Rot. Kopieren Sie es in die Zelle H25 und geben Sie b in Zelle H25 ein. Wählen Sie Zelle I24 und geben Sie ohne Anführungszeichen die Formel "= H23 / I23", die die Steigung m ist, und wählen Sie dann Zelle I25 und geben Sie die Formel w / o Anführungszeichen "= Y_BAR-I24 * X_BAR", das heißt, die y-Abschnitt b = Y_Mean-m * X_Mean.
- 7 Kopieren Sie Zelle H25 und fügen Sie sie in Zelle I26 ein und geben Sie I26 Y '- mX + b ein. Markieren Sie Zelle H27 und geben Sie Forecast ein, und geben Sie dann die Formel in I27 ohne Anführungszeichen "= I24 * 1290 + I25" ein. Ihre Antwort sollte exakt der FORECAST-Antwort in B27 entsprechen, wenn Sie die Anzahl der Dezimalstellen der Zellen der Zahlennummer 9 haben.
- 8 Bearbeiten Sie die Kopierzelle I26 in den Zellenbereich H29: I29. In die Zelle H29 tritt die Linie X ein und in die Zelle I29 die Linie Y. Geben Sie 800 in die Zelle H30 und 1600 in die Zelle H31 ein. Wählen Sie Zelle I 27 und kopieren Sie ihre Formel in der Bearbeitungsleiste - kopieren Sie die Zelle nicht und fügen Sie sie ein - das wird nicht richtig funktionieren. Wählen Sie Zelle I30 und fügen Sie die gerade kopierte Formel in die Bearbeitungsleiste ein. Machen Sie das Gleiche für Zelle I31. Bearbeiten Sie die Formel der Zelle I31, um "= I24 * H31 + I25" zu lesen, drücken Sie die Eingabetaste und bearbeiten Sie dann die Formel der Zelle I30 in der Bearbeitungsleiste mit "= I24 * H30 + I25" und drücken Sie die Eingabetaste. Das Ergebnis von I30 sollte 1,7786108729206 lauten und das Ergebnis von I31 sollte 4,05093465957812 sein (was ich realisiere ist höher als ein 4,0 GPA, aber wir erstellen eine Regressionslinie, das ist also nicht so wichtig.
- 9 Aktivieren Sie das Diagramm-Arbeitsblatt und klicken Sie im Diagramm und im Menü auf Diagramm hinzufügen Daten und als Antwort auf die Bereichsabfrage gehen Sie zurück zum Arbeitsblatt Daten und wählen Sie den Zellbereich H30: I31. Jetzt kommt mein Fehler und ich muss die Serie bearbeiten. Keine große Sache. Wählen Sie den Datenmarker und bearbeiten Sie in der Bearbeitungsleiste die Reihe "= SERIES (, Sheet1! $ H $ 30: $ H $ 31, Sheet1! $ I $ 30: $ I $ 31,2)" und klicken Sie auf den Datenmarker bei 800,1.78 und make Line red und Weight 0.75 pt, dann Marker Fill Color Rot, dann Marker Style Round Dot Größe 5. Deine Daten und Diagramme sollten wie folgt aussehen:
- 10 Nun zu den Änderungen, bitte geben Sie bitte die neuen Student SAT-GPA gepaarten Daten ein:
- Geben Sie die Daten ein, um die Frage zu beantworten. In x, y Paaren, wie im Bild ganz oben in diesem Artikel gezeigt, oder wie folgt: für Schüler 1, X, Y in Spalten B und C = 990 für X oder SAT in B3, 2.2 für Y oder GPA IN C3; Schüler 2 1150,3.2; dann 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 Wählen Sie den Zellbereich A24: C24 und Zellen einfügen - Zellen nach unten verschieben. In Zelle A24 geben Sie den Mittelpunkt ein. In die Zelle B24 geben Sie die Formel ohne Anführungszeichen "= xCenter" ein und wählen Sie Zelle C24 und geben Sie ohne Anführungszeichen die Formel "= yCenter" ein. Markieren Sie die Zelle H32 und geben Sie den Namen ein. Definieren Sie den Namen xCenter in der Zelle $ H $ 32 und wählen Sie I32 und Name einfügen. Geben Sie Name yCenter in die Zelle $ I $ 32 ein. In H32 geben Sie die Formel ohne Anführungszeichen "= (H30 + H31) / 2" ein und in I32 geben Sie die Formel ohne Anführungszeichen "= (I30 + I31) / 2" ein.
- 12 Ziel ausführen Suche nach dem Wertebereich von Linie XY. Geben Sie in die Zelle J30 die Formel "= 2-I30" ein und führen Sie die Zielsatzsuche der Zielzelle J30 auf den Wert 0 durch, indem Sie den Wert in der Zelle: H30 ändern. H30 sollte ca. 920.690991; Gehe zu Zelle J31, gib die Formel "= 4-I30" ein und setze die Werkzeug-Zielsuchzelle J311 auf den Wert 0, indem du den Wert in der Zelle: H31 änderst. H31 sollte ca. 1212.61866
- 13 Die folgenden Informationen werden noch nicht verwendet. Es hat damit zu tun, dass wenn eine Linie zu einem Kreis hinzugefügt wird, das Ergebnis eine Ellipse ist. Wählen Sie die Zelle I33 und geben Sie den Radius ein und richten Sie den Mittelpunkt und die Unterstreichung aus. Markieren Sie die Zelle H34 und geben Sie Dist von Linie a ein, und wählen Sie Zelle I34 und geben Sie die Formel ohne Anführungszeichen ein "= SQRT ((H31-H32) ^ 2 + (I31-I32) ^ 2)" - das ist das lange Segment oder Linie a. Es sollte = ca. 291.9293847
- 14 Geben Sie in Zeile 1 die definierten Variablennamen der Ellipse ein. K1: Aj_X; L1: Aj_Y; M1: m_Slope_; N1: Streckung_x; O1: Stretch_y; P1: Abfangen
- 15 Geben Sie die folgenden Werte in Zeile 2 ein: K2: 1200; L2: 4,15; M2: "= m"; N2: 0.0024950665406049 (erreicht durch Zielsuche); O2: "= m-0,0005"; P2: "= b". Name einfügen Name m für Zelle I24 definieren und Namen einfügen Name für b für Zelle I25 definieren.
- 16Wählen Sie Zellbereich K1: P2 und Namen einfügen Namen in obere Zeile erstellen, OK.
- 17 Bearbeiten Gehe zum Zellenbereich K4: K54 und in die oberste Zelle die Formel "= -2 * PI ()" eingeben und dann Shift + Tab drücken, um zur unteren Zelle zu gehen und "= 2 * PI ()" einzugeben. Wählen Sie bei ausgewähltem Zellbereich K4: K54 Füllreihen bearbeiten, Spalten linear, übernehmen Sie den vorgeschlagenen Schrittwert oder drücken Sie Trend, wenn der vorgeschlagene Schrittwert falsch = 1, OK ist.
- 18Überspringen Sie eine Spalte und bearbeiten Sie Gehe zu Zellbereich M4: M54 und mit M4 die aktive Zelle, geben Sie die Formel w / o Anführungszeichen "= SIN (K4)" und Bearbeite Fill Down ein.
- 19Bearbeiten Gehe zu Zellbereich N4: N54 und mit N4 die aktive Zelle, geben Sie die Formel w / o Anführungszeichen "= m_Slope_ * SIN ((K4-1))" und Edit Fill Down.
- 20Bearbeiten Sie Gehe zu Zellbereich O4: O54 und mit O4 die aktive Zelle, geben Sie die Formel w / o Anführungszeichen "= (M4) / Stretch_x + Aj_X" und bearbeiten Sie Fill Down.
- 21Bearbeiten Gehe zu Zellbereich P4: P54 und mit P4 die aktive Zelle, geben Sie die Formel w / o Anführungszeichen "= (N4) / Stretch_y + AJ_Y + Intercept" und bearbeiten Sie Fill Down.
- 22 Erstellen Sie ein kleines Arbeitsblattdiagramm. Wählen Sie den Zellbereich O4: P54 und verwenden Sie den Diagrammassistenten oder das Multifunktionsleisten-Diagramm Alle / Andere, und scrollen Sie nach unten, um den Punkt Geglätteten Linienverlauf streuen und ein kleines Diagramm sollte über Ihren Daten erscheinen. Wichtig: Die x-Werte sollten zwischen 800 und 1600 liegen und die y-Werte zwischen 1,8 und 4,1. Wenn dies nicht der Fall ist, suchen Sie nach einem falsch platzierten Dezimalpunkt in den oberen Zeilen 2 definierter Variablen oder a Unterschied zwischen out Berechnungen von m und b. Sonst ist es irgendwo entlang der Linie ein Dateneingabefehler, der eine Spalte nach der anderen überprüft werden kann. Siehe auch den Warning Section unter Fehler.
- 23 Klicken Sie auf das Seriendiagramm des neuen kleinen Diagramms im Datenblatt und Befehl + c kopieren Sie es, dann gehen Sie zum Diagramm-Arbeitsblatt und klicken Sie in das Diagramm und Befehl + v fügen Sie es ein. Wenn es wie meines funktioniert, ist es FUBAR und muss nacheinander korrigiert werden.
- 24 Wenn die Regressionslinie weiterhin angezeigt wird, bearbeiten Sie ihre Reihe in der Formelleiste rechts neben der ersten Klammer, indem Sie vor dem Komma "Regressionslinie" in Anführungszeichen eingeben. Drücken Sie dann den Abwärtspfeil auf Ihrer Tastatur, um auf Series 1 zuzugreifen, und bearbeiten Sie Linie (keine Linie), Markenstil rot Größe 5, Markierung füllen - rot und bearbeiten Sie die Reihe in der Formelleiste nach der ersten linken Klammer mit Anführungszeichen , "SAT-GPA-Paare" vor dem ersten Komma.
- 25 Drücken Sie ein- oder zweimal die OK-Taste und dann die Eingabetaste und die Abwärtstaste, um auf die Serie 3 (die Ellipse) zuzugreifen. Auswahl des Diagrammlayout-Formats, Markierung der Füllung blau-grün, Markierungslinie automatisch mit geglätteter Linie aktiviert, OK.
- 26 Aktivieren Sie das Arbeitsblatt Daten und wählen Sie den Zellbereich B23: C24 und kopieren und aktivieren Sie das Arbeitsblatt Diagramm und Befehl + v fügen Sie den Bereich ein. Es funktioniert nicht richtig für mich, und ich muss es bearbeiten und eine zusätzliche Reihe löschen, abgesehen davon, Was Sie mit enden möchten, ist Serie, die in der Bearbeitungsleiste lesen, "= SERIES (" Mean and Midpoint ", Data! $ B $ 23: $ B $ 24, Daten! $ C $ 23: $ C $ 24,4) ", Keine Markierungslinie, Markierungsart Runder Punkt, Größe 9, Markierungsfüllung Dunkelblau oder Lila.
- 27Gehen Sie zu Einfügen Bild Word Art, um eine Überschrift für das Diagramm zu erstellen, die etwas Glanz hat.
- Ellipse zum Anpassen der Regressionsdaten 28
Erledigt!! Gut gemacht. In einem zukünftigen Artikel wird erläutert, wie die verschiedenen Sektoren der Ellipse analysiert werden können. Vorerst wurde die Umgebung des Datensatzes erreicht, was das Ziel war. Für diejenigen, die mit dieser Analyse beginnen möchten, ein Hinweis: Wenn Sie die Fläche eines geneigten Ellipsensektors berechnen: Schauen Sie so, als ob Sie sich in der Ebene befinden, in der sie nicht geneigt ist und nur eine normale Ellipse ist. Das ist der empfohlene Ansatz von meinem Mentor. Tipp # 2: Sehen Sie sich Ihre Daten an, um die Länge der Linie b zu finden. In den Tipps sind die Daten und die kleine Tabelle für die nicht angepasste schräge Ellipse enthalten, die Sie als Ausgangspunkt für Ihre Analysen verwenden können.
Teil drei von drei:
Hilfreiche Anleitung
- 1 Verwenden Sie Hilfsartikel, wenn Sie dieses Tutorial durcharbeiten:
- Eine Liste von Artikeln zu Excel, Geometrischer und / oder Trigonometrischer Kunst, Diagrammerstellung und algebraischer Formulierung finden Sie im Artikel Erstellen eines spiralförmigen Spinpartikel-Pfads oder einer Kettenform oder eines sphärischen Rahmens.
- Für weitere Diagramme und Diagramme, klicken Sie auf Kategorie: Microsoft Excel Bilder, Kategorie: Mathematik, Kategorie: Tabellenkalkulation oder Kategorie: Grafiken, um viele Excel Arbeitsblätter und Diagramme zu sehen, in denen Trigonometrie, Geometrie und Kalkül in Kunst umgewandelt wurden, oder klicken Sie einfach auf die Kategorie wie oben rechts auf der Seite oder links unten auf der Seite.
Facebook
Twitter
Google+