Okay, ich habe diese Excel-Chart-Herausforderung am Freitag gepostet. Wie hast du das gemacht? Ich bin sicher, Ihre Lösung ist besser als meine. Dies war die Herausforderung einer Excel-Forumsfrage:

„So beseitigen Sie Ausreißer in Diagrammen

Beim Anzeigen einiger Diagramme muss ich manchmal einige Ausreißer ignorieren. Möchten Sie wissen, ob es möglich ist, auf die Ausreißerdaten oder das entsprechende x auf der x-Achse zu klicken, und das Diagramm wird ohne diese Ausreißerdaten aktualisiert. Ich weiß, dass ich zum Datensatz gehen und die Ausreißer entfernen kann, aber ich möchte es vereinfachen, indem ich es in der Grafik mache. Vielen Dank.“

Hier ist das Bild, das dieser Benutzer gepostet hat:

Der Graph hat 2 abgelegene Datenpunkte. 1 am 4. Januar und 1 am 7. Januar. Der Excel-Benutzer hat die Datenpunkte manuell gelöscht, und dann sehen Sie das endgültige Diagramm am unteren Rand des Bildes.

Wir müssen auch eine Trendlinie mit einer Trendlinienformel und einem R-Wert hinzufügen, dies kann jedoch ein manueller Schritt sein. Die Daten gehen vom 1. Januar bis zum 10. Januar des Jahres 2013. Also lasst uns mit diesen Annahmen gehen.Wie können wir also das endgültige Diagramm erstellen, indem wir die Datenpunkte vom 4. und 7. Januar entfernen, ohne diese Datenpunkte manuell zu löschen? Lass uns dazu kommen! Nachfolgend finden Sie eine kurze Aufschlüsselung, wie ich meine Lösung erstellt habe. Nachfolgend finden Sie eine detaillierte Schritt-für-Schritt-Anleitung dieser Excel-Lösung. Darunter sehen Sie eine Videodemonstration dieses Excel-Tipps. Schließlich finden Sie darunter eine Kopie meiner Tabelle, die Sie herunterladen können, um mit der Daten- und Diagrammtechnik zu planen.

Lassen Sie mich Ihnen zunächst zeigen, wie ein anderer Benutzer dieses Problem angegangen ist. Pete hatte einen genialen Weg, um dieses Problem zu umgehen. Er nimmt die Datenpunkte und erstellt eine Formel basierend auf der Steigung der Linie. Hier ist seine Lösung in seinen eigenen Worten:

Von Pete: „Ich habe mit deiner neuen Freitagsherausforderung herumgespielt und mir die Ergebnisse anders vorgestellt. Mein Diagramm zeigt die Originaldaten und die neuen Daten mit den entfernten Outlyern. Sie müssen das Blatt entsperren, um die Formeln anzuzeigen.

Grundsätzlich nahm ich die Originaldaten und verwendete die SLOPE-Funktion, um die Steigung der Daten zu ermitteln, und verglich sie dann mit einer theoretischen Linie basierend auf dem X-Wert unter Verwendung der algebraischen Formel für die Linien Y=MX+B. Mit dieser Formel und der berechneten Steigung konnte ich nach B lösen. Auf diese Weise kann der Benutzer auswählen, zu welchem Prozentsatz die Outlyers entfernt werden sollen. Ich musste 500% und 100% einschließen, damit der Benutzer die Originaldaten anzeigen kann, da der Datenpunkt „2“ 710% vom berechneten theoretischen Wert entfernt ist.“

Schauen Sie sich seine Lösung hier an: Petes Outlyer-Lösung

Hier ist meine grundlegende Lösung zum Erstellen einer Excel-Zeile, die keine Ausreißer anzeigt. Kommen Sie morgen wieder, um meine fortschrittliche Lösung zu sehen.

Die Aufschlüsselung

1) Diagrammdatenbereich erstellen

2) Bestimmen Sie eine Zelle als Ausreißertoleranzstufe

3) Ausreißerformel für Diagrammdatenbereich erstellen

4) Ausreißerdiagramm in Excel erstellen

5) Trendlinie zum Diagramm, Trendlinienformel und R-Quadrat-Wert hinzufügen

6) Glätten Sie die Linie und bereinigen Sie den Diagrammmüll

Schritt für Schritt

1) Erstellen Sie einen Diagrammdatenbereich

Okay, erstellen wir unseren ursprünglichen Datenbereich in den Zellen A1:B11. Erstellen Sie nun ein schnelles Liniendiagramm in Excel mit Markierungen, um zu sehen, wie es aussieht. Sieht fast genauso aus wie das erste Diagramm, das Sie in der ursprünglichen Frage gesehen haben.

Der einzige Unterschied ist, dass unsere Linie nicht geglättet ist. Der letzte Schritt in diesem Tutorial zeigt Ihnen, wie Sie diese Änderung vornehmen. Aber so würde das ursprüngliche Diagramm mit geglätteten Linien aussehen:

2) Bestimmen Sie eine Zelle als Ausreißertoleranzstufe

Okay, meine Lösung verwendet eine Tabellenkalkulationszelle, um die Toleranz für einen Ausreißer festzulegen. Daher habe ich Zelle D3 in der Tabelle als mein Toleranzniveau festgelegt. Wenn man sich die Daten ansieht, scheint es, dass ein Datenpunkt im Beispiel des Excel-Benutzers als Ausreißer bezeichnet wird, wenn sich die Punkte um 7 Einheiten unterscheiden.

Also habe ich in Zelle D3 einen Wert von 7 eingegeben:

Wir werden Zelle D3 im nächsten Schritt verwenden, wenn wir unsere Ausreißerformel erstellen.

3) Erstellen Sie eine Ausreißerformel für den Diagrammdatenbereich

Lassen Sie uns zuerst unsere Daten in Zelle E2 einfügen. Dies ist so einfach wie das Einfügen von an =A2 in Zelle E2.

Okay, das ist der Schritt, der alles zum Laufen bringt. Was wir tun müssen, ist den aktuellen Datenpunkt mit den Datenpunkten über und unter dem aktuellen zu vergleichen. Der Vergleich zeigt, ob der aktuelle Datenpunkt außerhalb des Toleranzniveaus liegt, das wir in Zelle D3 festgelegt haben. Im Wesentlichen subtrahieren wir den aktuellen Datenpunkt vom Datenpunkt eine Zelle darüber und prüfen, ob die Differenz größer ist als die Toleranz in Zelle D3. Wiederholen Sie diesen Schritt für die Zelle unterhalb des aktuellen Datenpunkts. Wenn beide Datenpunkte größer als das Toleranzniveau sind, setzen wir ein NA() in die aktuelle Zelle. Wenn beide Werte nicht außerhalb der Toleranz liegen, setzen wir den Wert des aktuellen Datenpunkts in diese Zelle. Da die Differenz negativ sein könnte, müssen wir diese Subtraktion in eine Absolutwertfunktion einschließen. Also in Zelle F2 setzen wir diese Formel:

=WENN(UND(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Hier ist eine Aufschlüsselung der Formel:

Zuerst werden wir mit einer IF-Formel beginnen, also gehen Sie zu Zelle F2 und geben Sie Folgendes ein:

=IF(

weil wir einen Wert vergleichen müssen und dann basierend auf diesem Wert einen von 2 Werten in diese Zelle einfügen

Wie oben beschrieben, müssen Sie nun den aktuellen Datenpunkt mit der obigen Zelle vergleichen und die Zelle darunter. Und wenn beide Vergleiche außerhalb der Toleranz liegen, möchten wir den Datenpunkt nicht anzeigen. Da wir es also mit einem „BOTH“ -Vergleich zu tun haben, sollten wir die UND-Funktion verwenden, also geben wir das in next ein:

=IF(AND(

Wie ich bereits sagte, wird manchmal das Subtrahieren der Datenpunkte den Endwert negativ machen. Und ein negativer Wert liegt IMMER unter unserer Toleranz, daher müssen wir sicherstellen, dass unsere Differenz immer in positiven Einheiten liegt, da unser Toleranzniveau in positiven Einheiten liegt. Um etwas immer positiv zu machen, müssen wir unseren Vergleich in eine Absolutwertfunktion einschließen, also geben wir als nächstes die ABS-Funktion ein:

=IF(AND(ABS(

Jetzt machen wir tatsächlich unseren 1. Vergleichswert. Dazu subtrahieren wir einfach den aktuellen Wert mit dem Wert über dem aktuellen Datenpunkt und prüfen, ob er größer als unser Toleranzniveau in Zelle D3 ist. Geben Sie also Folgendes ein:

=IF(AND()(B2-B1)>$D$3

Beachten Sie, dass ich die D3- und absolute Referenz erstellt habe. Wenn Sie nicht wissen, was eine absolute Referenz ist, lesen Sie diesen Beitrag:

Bezugnahme auf Bereiche in Formeln für Ihre Excel-Dashboard-Vorlagen

Das ist der erste Teil unserer UND-Funktion und die und-Funktion wird durch ein Komma getrennt, dann werden Sie den nächsten Vergleich durchführen. Geben wir also den nächsten Vergleich nach einem Komma ein. Der nächste Vergleich besteht darin, unseren aktuellen Datenpunkt mit dem nächsten Datenpunkt zu vergleichen. Also lasst uns das eingeben:

=IF(AND(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3

Da dies unser letzter Vergleich ist, über den wir uns Sorgen machen müssen, können Sie die UND-Funktion mit einer rechten Klammer beenden. Jetzt können wir ein Komma einfügen und bestimmen, was zu tun ist, wenn beide AND-Kriterien wahr sind. Wenn in unserem Fall beide Unterschiede der Datenpunkte größer als die Toleranz sind, müssen wir dort eine NA() -Funktion einfügen. Geben wir also diesen IF TRUE Wert ein:

=IF(AND(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA()

Wenn Sie mehr über die Funktion NA() erfahren möchten, lesen Sie diesen Beitrag:

So blenden Sie ein Null-Kreisdiagramm-Slice oder ein gestapeltes Säulendiagramm aus Abschnitt

Jetzt sind wir bereit für den endgültigen Wert, wenn die UND-Funktion NICHT WAHR ist. Setzen Sie also ein Komma ein und geben Sie den Wert IF FALSE ein. In diesem Fall möchten wir den tatsächlichen Datenpunktwert unserer Datenzelle B2 eingeben. Geben Sie also ein Komma ein und dann B2)

=IF(AND(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Drücken Sie nun die Eingabetaste und Ihr Wert in Zelle F2 sollte folgendermaßen aussehen:

Nun, da wir unsere Formeln an Ort und Stelle haben, können wir sie bis zu unserem letzten Datenpunkt in den Spalten A und B kopieren. Kopieren Sie diese Formel nach unten und es wird so aussehen:

Beachten Sie, dass Datenpunkte für 1/4 1/7 zeigt nun einen Wert von #N/A. Und die anderen Werte sind die gleichen wie in unserem ursprünglichen Datensatz in Spalte B. Das ist SO WUNDERBAR! Was für ein cooler Excel-Tipp, um Ausreißer aus unserem Trend zu entfernen.

Jetzt sind wir alle bereit, unser Excel-Ausreißerdiagramm zu erstellen.

4) Ausreißerdiagramm in Excel erstellen

Erstellen wir also unser Diagramm, indem wir den Zellbereich von E1:F11 hervorheben. Gehen Sie dann zum Menüband Einfügen und wählen Sie über die Schaltfläche Linie ein Liniendiagramm mit Markierungen aus:

Ihr Diagramm sollte jetzt folgendermaßen aussehen:

Es sieht ganz anders aus als das mit den Ausreißern:

5) Trendlinie zum Diagramm, Trendlinienformel und R-Quadrat-Wert hinzufügen

Jetzt, wo wir unser Diagramm haben, brauchen wir nur noch ein paar letzte Dinge. Fügen wir unserem Chart eine Trendlinie hinzu. Klicken Sie dazu auf das Diagramm und dann auf die Diagrammlinie. Gehen Sie von dort zum Menüband Layout und wählen Sie die Schaltfläche Trendlinie und dann Lineare Trendlinie:

Wählen Sie dann im Dialogfeld Trendlinie formatieren „Linear“ und „Gleichung im Diagramm anzeigen“ und „R-Quadrat-Wert im Diagramm anzeigen“:

Ihr Diagramm wird jetzt so aussehen:

6) Glätten Sie die Linie und bereinigen Sie den Diagrammmüll

Jetzt müssen wir nur noch das Diagramm bereinigen und wir sind fertig! Wählen Sie zuerst Ihr Diagramm und dann die Legende aus und drücken Sie die Entf-Taste. Ihr Diagramm sollte nun folgendermaßen aussehen:

Jetzt müssen wir ein geglättetes Liniendiagramm erstellen. Wählen Sie dazu das Diagramm und dann die Linie im Diagramm aus. Drücken Sie von dort aus STRG + 1, um das Dialogfeld Serie formatieren aufzurufen. Wählen Sie von dort aus die Linienstile aus dem linken Menü und wählen Sie Geglättete Linie aus den Linienstiloptionen:

Ihr endgültiges Diagramm sollte jetzt folgendermaßen aussehen:

Kein großer Unterschied, aber wenn Ihre Daten größere Änderungen aufweisen, sehen sie noch glatter aus

.

Vergleichen wir es nun mit der ursprünglichen Anfrage. Es sieht fast genau so aus, wie wir es wollten. Sie werden jedoch sehen, dass die Datenpunkte vom 3. bis zum 5. Januar verbunden und nicht wie im Originalmuster unterbrochen sind. Sam für Datenpunkte zwischen 1/6 und 1/8. Ich bin mir nicht sicher, wie wichtig es ist, diese Linien NICHT zu verbinden, aber es ist nicht möglich, wie Excel Liniendiagramme erstellt, wenn Sie eine Datenlücke haben, die eine Formel verwendet.

Kommen Sie jedoch morgen zurück, wo ich Ihnen zeigen werde, wie Sie ein Liniendiagramm mithilfe von Formeln eine Lücke anzeigen lassen.

Video-Tutorial

Schauen Sie sich diese Videodemonstration zum Erstellen eines Excel-Liniendiagramms an, das Ausreißer ignoriert: