Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Formel der Trendlinie mit festem Startpunkt

Formel der Trendlinie mit festem Startpunkt
28.03.2017 13:55:43
Joachim
Hallo an das Forum,
ich bitte um Hilfe bei folgender Fragestellung: ich habe Werte zur Ermittlung der Produktionsgeschwindigkeit von Fertigungsmaschinen aufgenommen und in einer Wertetabelle zusammengetragen, um mir daraus eine Ausgleichsgerade (Trendlinie) als Produktionsfunktion grafisch darstellen zu lassen. Die grafische Trendlinie biete eine für meinen Anwendungszweck fantastische Zusatzeinstellung, nämlich einen festen Schnittpunkt mit der y-Achse eingeben zu können. Das funktioniert ausgezeichnet und die Werte sind stimmig.
Nun benötige ich Steigung und Achsenabschnitt als Formel, um diese weiter verarbeiten zu können. Hier beginnt das Problem: STEIGUNG und ACHSENABSCHNITT liefern die korrekten Werte für eine Trendfunktion ohne Berücksichtigung dieses festen Startpunkts. Ich brauche aber die Funktionsbestandteile unter Berücksichtigung des festen Startpunktes.
Gibt es dazu einen Weg ?
Vielen Dank für eure Hilfe.
Gruß Joachim
Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel der Trendlinie mit festem Startpunkt
28.03.2017 14:21:45
Beverly
Hi Joachim,
handelt es sich bei der Trendlinie um eine Gerade? Also so richtig verstehe ich das Problem nicht: wenn du den Schnittpunkt vorgibst, erhältst doch ebenfalls eine Formel und diese sieht anders aus als wenn du den Schnittpunkt nicht vorgibst. Und aus dieser veränderten Formel kannst du doch dann die Steigung und den Achsenabschnitt ablesen.


Anzeige
AW: Formel der Trendlinie mit festem Startpunkt
28.03.2017 15:25:52
Joachim
Hallo Beverly,
ja, richtig, die Trendlinie ist eine Gerade und, auch richtig, die Formel sehe als Beschriftung in der Grafik. Was ich benötige, sind die dort angezeigten Werte für die Steigung und den Achsenabschnitt als ausgelesener Wert in einer Zelle, um die Formel anderweitig zu benutzen.
Beim Hinzufügen weiterer Wertepaare wird sich die Funktion nach und nach verändern. Durch den sich ständig aktualisierenden Wert der Formel würden sich nachlaufende Berechnungen, die auf der Funktion basieren, auch verändern.
Ich weiß halt nicht, wie ich Steigung und Achsenabschnitt dieser durch den festen Schnittpunkt "besonderen" Funktion auslesen kann.
Anzeige
AW: Formel der Trendlinie mit festem Startpunkt
28.03.2017 16:40:35
Beverly
Hi Joachim,
der Wert der vor x in der Formel steht ist der Anstieg und der Wert am Ende der Formel ist der Achsenabschnitt (entspricht vorgegebenem Schnittpunkt). Wenn du die Werte in einer Zelle benötigst, musst du halt die Formel mit VBA extrahieren - z.B. so:
Sub TrendlinienBerechnung()
Dim strInhalt As String
With Worksheets("Tabelle1")
With .ChartObjects("Diagramm 2").Chart
If .SeriesCollection(1).Trendlines.Count Then
If .SeriesCollection(1).Trendlines(1).Type = xlLinear Then
strInhalt = .SeriesCollection(1).Trendlines(1).DataLabel.Caption
End If
End If
End With
If strInhalt  "" Then
'Achtung, Zelladresse wo x-Wert steht anpassen!!
strInhalt = Application.Substitute(strInhalt, "x", "*A18")
.Range("A8").FormulaLocal = _
Application.Substitute(Application.Substitute(strInhalt, "y", ""), " ", "")
End If
End With
End Sub


Anzeige
AW: Formel der Trendlinie mit festem Startpunkt
30.03.2017 18:08:59
Joachim
Hallo Karin,
vielen Dank dafür. Jetzt benötige ich etwas zeit, denn VBA gehört nicht gerade zu meinen Kernkompetenzen ;-)
Beste Grüße
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Trendlinie in Excel mit festem Startpunkt erstellen


Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Trage die Werte, die du analysieren möchtest, in eine Excel-Tabelle ein. Achte darauf, dass die Daten gut strukturiert sind.

  2. Diagramm erstellen: Markiere die Daten und gehe zu Einfügen > Diagramme > Punkt (X,Y) oder Linien und wähle das gewünschte Diagramm aus.

  3. Trendlinie hinzufügen: Klicke mit der rechten Maustaste auf die Datenreihe im Diagramm und wähle Trendlinie hinzufügen. Wähle den Typ der Trendlinie (z.B. Linear) aus.

  4. Startpunkt festlegen: Um einen festen Schnittpunkt mit der y-Achse zu definieren, gehe zu den Trendlinie Optionen und aktiviere die Option Schnittpunkt festlegen. Gebe den gewünschten y-Achsenabschnitt ein.

  5. Formel anzeigen: Aktiviere die Option, um die Trendlinie zu beschriften. Dies zeigt die Gleichung im Diagramm an.

  6. Werte in Zelle extrahieren: Um die Steigung und den Achsenabschnitt in Zellen zu verwenden, benötigst du VBA. Verwende das folgende Skript, um die Werte automatisch in die Zellen zu übertragen:

    Sub TrendlinienBerechnung()
    Dim strInhalt As String
    With Worksheets("Tabelle1")
        With .ChartObjects("Diagramm 2").Chart
            If .SeriesCollection(1).Trendlines.Count Then
                If .SeriesCollection(1).Trendlines(1).Type = xlLinear Then
                    strInhalt = .SeriesCollection(1).Trendlines(1).DataLabel.Caption
                End If
            End If
        End With
        If strInhalt <> "" Then
            strInhalt = Application.Substitute(strInhalt, "x", "*A18") ' Zelladresse anpassen
            .Range("A8").FormulaLocal = Application.Substitute(Application.Substitute(strInhalt, "y", ""), " ", "")
        End If
    End With
    End Sub

Häufige Fehler und Lösungen

  • Fehler beim Hinzufügen der Trendlinie: Stelle sicher, dass die Daten korrekt formatiert sind. Wenn keine Trendlinie angezeigt wird, überprüfe die Datenreihen und deren Struktur.

  • Formel wird nicht angezeigt: Überprüfe die Optionen zur Anzeige der Trendlinie im Diagramm. Manchmal wird die Gleichung nicht automatisch aktiviert.

  • VBA funktioniert nicht: Achte darauf, dass Makros in Excel aktiviert sind. Gehe zu den Excel-Optionen und aktiviere die Makros.


Alternative Methoden

Wenn du die Trendlinie nicht über VBA auslesen möchtest, kannst du auch die Formel manuell in einer Zelle eingeben, basierend auf den Werten, die du in der Grafik siehst. Zum Beispiel:

  • Steigung: Nutze die Formel =STEIGUNG(Bereich_y; Bereich_x).

  • Achsenabschnitt: Berechne den Wert mit =Y-Achsenabschnitt - (Steigung * X-Achsenabschnitt).


Praktische Beispiele

Angenommen, du hast folgende Werte für die Produktionsgeschwindigkeit:

X-Werte Y-Werte
1 2
2 3
3 5
  1. Erstelle ein Diagramm mit diesen Werten.
  2. Füge eine Trendlinie hinzu und setze den Schnittpunkt auf (0,2).
  3. Verwende das VBA-Skript, um die Steigung und den Achsenabschnitt in Zellen zu extrahieren.

Tipps für Profis

  • Nutze die Trendlinie-Optionen, um weitere Anpassungen vorzunehmen, wie z.B. die Anzeige von R²-Werten zur Überprüfung der Anpassungsgüte.

  • Experimentiere mit verschiedenen Typen von Trendlinien (z.B. exponentiell, logarithmisch), je nach den Anforderungen deiner Analyse.

  • Wenn du regelmäßig mit Trendlinien arbeitest, erstelle eine Vorlage, die bereits deine Standardwerte und Formate enthält.


FAQ: Häufige Fragen

1. Wie kann ich die Steigung einer Trendlinie in Excel berechnen?
Verwende die Formel =STEIGUNG(Bereich_y; Bereich_x), um die Steigung zu berechnen.

2. Kann ich die Trendlinie durch den Ursprung legen?
Ja, setze einfach den y-Achsenabschnitt auf 0, wenn du die Trendlinie erstellst.

3. Wie kann ich die Trendlinie in einer Zelle anzeigen?
Verwende VBA, um die Gleichung der Trendlinie in eine Zelle zu extrahieren, wie im obigen Beispiel gezeigt.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige