So erstellen Sie eine lineare Kalibrierungskurve in Excel

Excel verfügt über integrierte Funktionen, mit denen Sie Ihre Kalibrierungsdaten anzeigen und eine Best-Fit-Linie berechnen können. Dies kann hilfreich sein, wenn Sie einen Chemielaborbericht schreiben oder einen Korrekturfaktor in ein Gerät programmieren.

In diesem Artikel sehen wir uns an, wie Sie mit Excel ein Diagramm erstellen, eine lineare Kalibrierungskurve zeichnen, die Formel der Kalibrierungskurve anzeigen und dann einfache Formeln mit den Funktionen SLOPE und INTERCEPT einrichten, um die Kalibrierungsgleichung in Excel zu verwenden.

Was ist eine Kalibrierungskurve und wie ist Excel bei der Erstellung nützlich?

Um eine Kalibrierung durchzuführen, vergleichen Sie die Messwerte eines Geräts (wie die Temperatur, die ein Thermometer anzeigt) mit bekannten Werten, die als Standards bezeichnet werden (wie Gefrier- und Siedepunkt von Wasser). Auf diese Weise können Sie eine Reihe von Datenpaaren erstellen, die Sie dann verwenden, um eine Kalibrierungskurve zu entwickeln.

Eine Zweipunkt-Kalibrierung eines Thermometers unter Verwendung des Gefrier- und Siedepunkts von Wasser würde zwei Datenpaare haben: eines ab dem Zeitpunkt, an dem das Thermometer in Eiswasser gelegt wird (32°F oder 0°C) und einer in kochendem Wasser (212°F oder 100°C). Wenn Sie diese beiden Datenpaare als Punkte darstellen und eine Linie dazwischen ziehen (die Kalibrierungskurve), dann können Sie unter der Annahme, dass das Thermometer linear ist, einen beliebigen Punkt auf der Linie auswählen, der dem vom Thermometer angezeigten Wert entspricht, und Sie die entsprechende „wahre“ Temperatur finden.

Die Linie füllt also im Wesentlichen die Informationen zwischen den beiden bekannten Punkten für Sie aus, damit Sie bei der Schätzung der tatsächlichen Temperatur bei 57,2 Grad des Thermometers einigermaßen sicher sein können, wenn Sie jedoch noch nie einen «Standard» gemessen haben, der dem entspricht diese Lesung.

Excel verfügt über Funktionen, mit denen Sie die Datenpaare grafisch in einem Diagramm darstellen, eine Trendlinie (Kalibrierungskurve) hinzufügen und die Gleichung der Kalibrierungskurve im Diagramm anzeigen können. Dies ist für eine visuelle Anzeige nützlich, aber Sie können die Formel der Linie auch mit den Funktionen SLOPE und INTERCEPT von Excel berechnen. Wenn Sie diese Werte in einfache Formeln eingeben, können Sie anhand jeder Messung automatisch den „wahren“ Wert berechnen.

Schauen wir uns ein Beispiel an

Für dieses Beispiel entwickeln wir eine Kalibrierkurve aus einer Reihe von zehn Datenpaaren, die jeweils aus einem X-Wert und einem Y-Wert bestehen. Die X-Werte werden unsere „Standards“ sein und sie können alles darstellen, von der Konzentration einer chemischen Lösung, die wir mit einem wissenschaftlichen Instrument messen, bis hin zur Eingabevariable eines Programms, das eine Kugelabschussmaschine steuert.

Die Y-Werte sind die «Antworten», und sie würden den Messwert des Instruments darstellen, das bei der Messung jeder chemischen Lösung bereitgestellt wird, oder die gemessene Entfernung, wie weit die Murmel von der Werfer aus mit jedem Eingabewert gelandet ist.

Nachdem wir die Kalibrierkurve grafisch dargestellt haben, verwenden wir die Funktionen SLOPE und INTERCEPT, um die Formel der Kalibrierlinie zu berechnen und die Konzentration einer „unbekannten“ chemischen Lösung anhand des Messwerts des Instruments zu bestimmen oder zu entscheiden, welche Eingaben wir dem Programm geben sollen, damit die Marmor landet in einer gewissen Entfernung vom Werfer.

Schritt eins: Erstellen Sie Ihr Diagramm

Unsere einfache Beispieltabelle besteht aus zwei Spalten: X-Wert und Y-Wert.

Beginnen wir mit der Auswahl der Daten, die im Diagramm dargestellt werden sollen.

Wählen Sie zuerst die Zellen der Spalte ‘X-Wert’ aus.

Drücken Sie nun die Strg-Taste und klicken Sie dann auf die Zellen der Y-Wert-Spalte.

Gehen Sie zum Reiter „Einfügen“.

Navigieren Sie zum Menü „Charts“ und wählen Sie die erste Option im Dropdown-Menü „Scatter“.

Wählen Sie die Serie aus, indem Sie auf einen der blauen Punkte klicken. Nach der Auswahl werden die Punkte in Excel skizziert.

Klicken Sie mit der rechten Maustaste auf einen der Punkte und wählen Sie dann die Option „Trendlinie hinzufügen“.

Auf dem Diagramm wird eine gerade Linie angezeigt.

Auf der rechten Seite des Bildschirms erscheint das Menü „Trendlinie formatieren“. Aktivieren Sie die Kontrollkästchen neben «Gleichung im Diagramm anzeigen» und «R-Quadrat-Wert im Diagramm anzeigen». Der R-Quadrat-Wert ist eine Statistik, die Ihnen sagt, wie gut die Linie den Daten entspricht. Der beste R-Quadrat-Wert ist 1,000, was bedeutet, dass jeder Datenpunkt die Linie berührt. Wenn die Unterschiede zwischen den Datenpunkten und der Linie wachsen, sinkt der r-Quadrat-Wert, wobei 0,000 der niedrigste mögliche Wert ist.

Die Gleichung und die R-Quadrat-Statistik der Trendlinie werden auf dem Chart angezeigt. Beachten Sie, dass die Korrelation der Daten in unserem Beispiel mit einem R-Quadrat-Wert von 0,988 sehr gut ist.

Die Gleichung hat die Form „Y = Mx + B“, wobei M die Steigung und B der y-Achsen-Schnittpunkt der Geraden ist.

Nachdem die Kalibrierung nun abgeschlossen ist, können wir das Diagramm anpassen, indem wir den Titel bearbeiten und Achsentitel hinzufügen.

Um den Diagrammtitel zu ändern, klicken Sie darauf, um den Text auszuwählen.

Geben Sie nun einen neuen Titel ein, der das Diagramm beschreibt.

Um Titel zur X- und Y-Achse hinzuzufügen, navigieren Sie zuerst zu Diagrammtools > Design.

Navigieren Sie nun zu Achsentitel > Primäre Horizontale.

Um den Achsentitel umzubenennen, wählen Sie zuerst den Text aus und geben dann einen neuen Titel ein.

Gehen Sie nun zu Achsentitel> Primäre Vertikale.

Ein Achsentitel wird angezeigt.

Benennen Sie diesen Titel um, indem Sie den Text auswählen und einen neuen Titel eingeben.

Ihr Diagramm ist jetzt fertig.

Schritt 2: Berechnen Sie die Liniengleichung und die R-Quadrat-Statistik

Lassen Sie uns nun die Liniengleichung und die R-Quadrat-Statistik mit den in Excel integrierten Funktionen SLOPE, INTERCEPT und CORREL berechnen.

Zu unserem Blatt (in Zeile 14) haben wir Titel für diese drei Funktionen hinzugefügt. Wir führen die eigentlichen Berechnungen in den Zellen unter diesen Titeln durch.

Zuerst berechnen wir den SLOPE. Wählen Sie Zelle A15 aus.

Navigieren Sie zu Formeln > Weitere Funktionen > Statistisch > SLOPE.

Wählen Sie im Feld «Known_xs» die Zellen der X-Wert-Spalte aus oder geben Sie sie ein. Die Reihenfolge der Felder ‘Known_ys’ und ‘Known_xs’ ist in der SLOPE-Funktion von Bedeutung.

OK klicken.» Die endgültige Formel in der Bearbeitungsleiste sollte wie folgt aussehen:

=SLOPE(C3:C12,B3:B12)

Beachten Sie, dass der von der SLOPE-Funktion in Zelle A15 zurückgegebene Wert mit dem im Diagramm angezeigten Wert übereinstimmt.

Wählen Sie als Nächstes Zelle B15 aus und navigieren Sie dann zu Formeln > Weitere Funktionen > Statistik > INTERCEPT.

Wählen Sie die Zellen der X-Wert-Spalte für das Feld «Known_xs» aus oder geben Sie sie ein. Die Reihenfolge der Felder ‘Known_ys’ und ‘Known_xs’ spielt auch in der INTERCEPT-Funktion eine Rolle.

OK klicken.» Die endgültige Formel in der Bearbeitungsleiste sollte wie folgt aussehen:

=INTERCEPT(C3:C12,B3:B12)

Beachten Sie, dass der von der Funktion INTERCEPT zurückgegebene Wert mit dem im Diagramm angezeigten y-Achsenabschnitt übereinstimmt.

Wählen Sie als Nächstes Zelle C15 aus und navigieren Sie zu Formeln > Weitere Funktionen > Statistik > KORREL.

Wählen Sie oder Geben Sie den anderen der beiden Zellbereiche für das Feld „Array2“ ein.

OK klicken.» Die Formel sollte in der Formelleiste so aussehen:

=CORREL(B3:B12,C3:C12)

Beachten Sie, dass der von der CORREL-Funktion zurückgegebene Wert nicht mit dem „r-squared“-Wert im Diagramm übereinstimmt. Die CORREL-Funktion gibt „R“ zurück, also müssen wir es quadrieren, um „R-Quadrat“ zu berechnen.

Klicken Sie in die Funktionsleiste und fügen Sie am Ende der Formel „^2“ hinzu, um den von der CORREL-Funktion zurückgegebenen Wert zu quadrieren. Die fertige Formel sollte nun so aussehen:

=CORREL(B3:B12,C3:C12)^2

Drücken Sie Enter.

Nach dem Ändern der Formel entspricht der „R-Quadrat“-Wert nun dem im Diagramm angezeigten Wert.

Schritt 3: Einrichten von Formeln zur schnellen Berechnung von Werten

Jetzt können wir diese Werte in einfachen Formeln verwenden, um die Konzentration dieser „unbekannten“ Lösung zu bestimmen oder welche Eingabe wir in den Code eingeben müssen, damit die Murmel eine bestimmte Distanz fliegt.

In diesen Schritten werden die Formeln erstellt, die Sie benötigen, um einen X-Wert oder einen Y-Wert eingeben zu können und den entsprechenden Wert basierend auf der Kalibrierungskurve zu erhalten.

Die Gleichung der Best-Fit-Linie hat die Form „Y-Wert = SLOPE * X-Wert + INTERCEPT“, so dass die Auflösung nach dem „Y-Wert“ durch Multiplizieren des X-Werts und SLOPE erfolgt und dann Hinzufügen des INTERCEPT.

Als Beispiel geben wir Null als X-Wert ein. Der zurückgegebene Y-Wert sollte gleich dem INTERCEPT der Linie der besten Anpassung sein. Es stimmt überein, sodass wir wissen, dass die Formel richtig funktioniert.

Das Auflösen nach dem X-Wert basierend auf einem Y-Wert erfolgt durch Subtrahieren des INTERCEPT vom Y-Wert und Dividieren des Ergebnisses durch die SLOPE:

X-value=(Y-value-INTERCEPT)/SLOPE

Als Beispiel haben wir den INTERCEPT als Y-Wert verwendet. Der zurückgegebene X-Wert sollte gleich Null sein, aber der zurückgegebene Wert ist 3,14934E-06. Der zurückgegebene Wert ist nicht null, da wir das INTERCEPT-Ergebnis bei der Eingabe des Werts versehentlich abgeschnitten haben. Die Formel funktioniert jedoch korrekt, da das Ergebnis der Formel 0,00000314934 ist, was im Wesentlichen null ist.

Sie können einen beliebigen X-Wert in die erste Zelle mit dickem Rand eingeben und Excel berechnet den entsprechenden Y-Wert automatisch.

Die Eingabe eines beliebigen Y-Werts in die zweite Zelle mit dickem Rand ergibt den entsprechenden X-Wert. Diese Formel ist, was Sie verwenden würden, um die Konzentration dieser Lösung zu berechnen oder welche Eingabe erforderlich ist, um die Murmel über eine bestimmte Entfernung zu schleudern.

In diesem Fall zeigt das Instrument „5“ an, sodass die Kalibrierung eine Konzentration von 4,94 vorschlagen würde, oder wir möchten, dass die Murmel fünf Entfernungseinheiten zurücklegt, sodass die Kalibrierung vorschlägt, dass wir 4,94 als Eingabevariable für das Programm eingeben, das den Murmelwerfer steuert. Aufgrund des hohen R-Quadrat-Wertes in diesem Beispiel können wir diesen Ergebnissen einigermaßen sicher sein.