Comment faire une courbe d'étalonnage linéaire dans Excel

Excel a des fonctions intégrées que vous pouvez utiliser pour afficher vos données d'étalonnage et calculer une ligne de meilleur ajustement. Cela peut être utile lorsque vous rédigez un rapport de laboratoire de chimie ou que vous programmez un facteur de correction sur un ordinateur..

Dans cet article, nous verrons comment utiliser Excel pour créer un graphique, tracer une courbe d'étalonnage linéaire, afficher la formule de la courbe d'étalonnage, puis configurer des formules simples avec les fonctions PENTE et INTERCEPTION pour utiliser l'équation d'étalonnage dans Excel.

Qu'est-ce qu'une courbe d'étalonnage et en quoi Excel est-il utile lors de la création d'une courbe d'étalonnage ??

Pour effectuer un étalonnage, comparer les lectures d'un appareil (comme la température indiquée sur un thermomètre) avec des valeurs connues appelées normes (comme les points de congélation et d'ébullition de l'eau). Cela vous permet de créer une série de paires de données que vous utiliserez ensuite pour développer une courbe d'étalonnage..

Un étalonnage en deux points d'un thermomètre utilisant les points de congélation et d'ébullition de l'eau aurait deux paires de données.: l'un des cas où le thermomètre est placé dans de l'eau glacée (32°Fo 0°C) et un dans l'eau bouillante (212°Fo 100°C). Lorsque vous tracez ces deux paires de données sous forme de points et tracez une ligne entre elles (la courbe d'étalonnage), en supposant que la réponse du thermomètre est linéaire, vous pouvez choisir n'importe quel point de la ligne qui correspond à la valeur affichée par le thermomètre, et vous pouviez trouver la température “Vrai” correspondant.

Donc, la ligne remplit essentiellement les informations entre les deux points connus afin que vous puissiez être raisonnablement confiant dans l'estimation de la température réelle lorsque le thermomètre lit 57.2 degrés, mais quand vous n’avez jamais mesuré un “la norme” ce qui correspond à cette lecture.

Excel a des fonctionnalités qui vous permettent de tracer les paires de données graphiquement sur un graphique, ajouter une ligne de tendance (courbe d'étalonnage) et afficher l'équation de la courbe d'étalonnage sur le graphique. Ceci est utile pour une présentation visuelle, mais vous pouvez également calculer la formule de la ligne en utilisant les fonctions PENTE et INTERCEPTION d'Excel. Lorsque vous entrez ces valeurs dans des formules simples, vous pouvez calculer automatiquement la valeur “vrai” basé sur n’importe quelle mesure.

Voyons un exemple

Pour cet exemple, nous développerons une courbe d'étalonnage à partir d'une série de dix paires de données, dont chacun se compose d'une valeur X et d'une valeur Y. Les valeurs X seront les nôtres “Normes” et pourrait représenter n’importe quoi, de la concentration d'une solution chimique que nous mesurons avec un instrument scientifique à la variable d'entrée d'un programme qui contrôle une machine à lancer des billes.

Les valeurs Y seront les valeurs “Réponses” et représenter la lecture fournie par l’instrument lors de la mesure de chaque solution chimique ou la distance mesurée de la distance à laquelle le marbre a atterri en utilisant chaque valeur d’entrée.

Après avoir tracé la courbe d'étalonnage, nous utiliserons les fonctions PENDING et INTERCEPTion pour calculer la formule de la ligne d’étalonnage et déterminer la concentration d’une solution chimique “étranger” en base a la lectura del instrumento o decidir qué entrada debemos dar al programa para que el el mármol aterriza a cierta distancia del lanzador.

Première étape: créez votre graphique

Notre feuille de calcul d'exemple simple se compose de deux colonnes: Valeur X et valeur Y.

Commençons par sélectionner les données à tracer sur le graphique.

Premier, seleccione las celdas de la columna ‘Valor X’.

Ahora presione la tecla Ctrl y luego haga clic en las celdas de la columna Y-Value.

Allez dans l'onglet “Insérer”.

Navegue hasta el menú “Graphique” y seleccione la primera opción en el menú desplegable “Dispersión”.

dispersión” largeur=”314″ hauteur=”250″ en charge =”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);” une erreur =”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);”>

Un graphique apparaîtra contenant les points de données pour les deux colonnes.

Sélectionnez la série en cliquant sur l'un des points bleus. Une fois sélectionné, Excel décrit les points à décrire.

Haga clic con el botón derecho en uno de los puntos y luego seleccione la opción “Ajouter une ligne de tendance”.

Une ligne droite apparaîtra sur le graphique.

Sur le côté droit de l'écran, le menu apparaîtra “Mettre en forme la ligne de tendance”. Marque las casillas junto a “Mostrar ecuación en el gráfico” et “Mostrar valor R-cuadrado en el gráfico”. La valeur R au carré est une statistique qui vous indique à quel point la ligne correspond aux données. La meilleure valeur de R au carré est 1.000, ce qui signifie que chaque point de données touche la ligne. Au fur et à mesure que les différences entre les points de données et la ligne augmentent, la valeur r au carré chute, étant 0.000 valeur la plus basse possible.

L'équation et la statistique R au carré de la ligne de tendance apparaîtront sur le graphique.. Notez que la corrélation des données est très bonne dans notre exemple, avec une valeur R au carré de 0,988.

L'équation a la forme « Y = Mx + B", où M est la pente et B est l'ordonnée à l'origine de la droite.

Maintenant que l'étalonnage est terminé, travaillons sur la personnalisation du graphique en éditant le titre et en ajoutant des titres d'axe.

Pour changer le titre du graphique, cliquez dessus pour sélectionner le texte.

Maintenant, écrivez un nouveau titre qui décrit le graphique.

Pour ajouter des titres à l'axe des x et à l'axe des y, accédez d'abord aux outils de graphique> Concevoir.

diseño” largeur=”650″ hauteur=”225″ en charge =”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);” une erreur =”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);”>

Cliquez sur le menu déroulant “Ajouter un élément de graphique”.

Maintenant, aller aux titres de l'axe> horizontale principale.

primaire horizontale” largeur=”650″ hauteur=”500″ en charge =”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);” une erreur =”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);”>

Un titre d'axe apparaîtra.

Pour renommer le titre de l'axe, sélectionnez d'abord le texte, puis écrivez un nouveau titre.

Maintenant, aller aux titres de l'axe> Verticale principale.

Un titre d'axe apparaîtra.

Renommez ce titre en sélectionnant le texte et en tapant un nouveau titre.

Votre graphique est maintenant terminé.

Deuxième étape: Calculer l'équation linéaire et la statistique R au carré

Calculons maintenant l'équation linéaire et la statistique R au carré à l'aide des fonctions SLOPE, INTERCEPTION et CORREL intégrés à Excel.

Vers notre fiche (en ligne 14) nous avons ajouté des titres pour ces trois fonctions. Nous effectuerons les calculs réels dans les cellules sous ces titres.

Premier, nous allons calculer la PENTE. Sélectionnez la cellule A15.

Aller aux formules> Plus de fonctionnalités> Statistiques> EN ATTENDANT.

Plus de fonctionnalités> Statistiques> EN ATTENDANT” largeur=”650″ hauteur=”435″ en charge =”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);” une erreur =”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);”>

La fenêtre des arguments de la fonction apparaîtra. ajuster le texte “Known_ys”, sélectionnez ou saisissez les cellules de la colonne Y-Value.

ajuster le texte “Known_xs”, sélectionnez ou tapez des cellules dans la colonne X-Value. L'ordre des champs ‘Known_ys’ y 'Connu_xs’ importer dans la fonction SLOPE.

Cliquez sur OK.” La formule finale dans la barre de formule doit ressembler à ceci:

=SLOPE(C3:C12,B3:B12)

Notez que la valeur renvoyée par la fonction SLOPE dans la cellule A15 correspond à la valeur affichée dans le graphique.

Ensuite, sélectionnez la cellule B15, puis accédez à Formules> Plus de fonctionnalités> Statistiques> INTERCEPTER.

Plus de fonctionnalités> Statistiques> INTERCEPTER” largeur=”650″ hauteur=”435″ en charge =”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);” une erreur =”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);”>

La fenêtre des arguments de la fonction apparaîtra. Sélectionnez ou tapez les cellules de la colonne Valeur Y du champ “Known_ys”.

Sélectionnez ou tapez les cellules de la colonne Valeur X du champ “Known_xs”. L'ordre des champs ‘Known_ys’ y 'Connu_xs’ également important dans la fonction INTERCEPT.

Cliquez sur OK.” La formule finale dans la barre de formule doit ressembler à ceci:

=INTERCEPT(C3:C12,B3:B12)

Notez que la valeur renvoyée par la fonction INTERCEPT correspond à l'ordonnée à l'origine indiquée dans le graphique.

Ensuite, sélectionnez la cellule C15 et accédez à Formules> Plus de fonctionnalités> Statistiques> CORREL.

Plus de fonctionnalités> Statistiques> CORREL” largeur=”650″ hauteur=”435″ en charge =”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);” une erreur =”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(cette);”>

La fenêtre des arguments de la fonction apparaîtra. Sélectionnez ou tapez l’une des deux plages de cellules pour le champ “Tableau1”. Contrairement à SLOPE et INTERCEPT, l'ordre n'affecte pas le résultat de la fonction CORREL.

Sélectionnez ou tapez l’autre des deux plages de cellules pour le champ “Tableau2”.

Cliquez sur OK.” la formule doit ressembler à ceci dans la barre de formule:

=CORREL(B3:B12,C3:C12)

Notez que la valeur renvoyée par la fonction correl ne correspond pas à la valeur “r-carré” dans le graphique. La fonction CORREL renvoie “R”, il faut donc l’élever au carré pour calculer “R-carré”.

Cliquez à l'intérieur de la barre de fonction et ajoutez "^ 2" à la fin de la formule pour mettre au carré la valeur renvoyée par la fonction CORREL. La formule complète devrait maintenant ressembler à ceci:

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

Appuyez sur Entrée.

Après avoir changé la formule, la valeur de “R-carré” correspond maintenant à celui montré dans le graphique.

Troisième étape: Configurer des formules pour calculer des valeurs rapidement

Nous pouvons maintenant utiliser ces valeurs dans des formules simples pour déterminer la concentration de cette solution "inconnue" ou quelle entrée nous devons entrer dans le code pour que la bille parcoure une certaine distance..

Ces étapes configureront les formules nécessaires pour que vous puissiez entrer une valeur X ou une valeur Y et obtenir la valeur correspondante en fonction de la courbe d'étalonnage..

L’équation de la ligne la mieux ajustée a la forme “Valeur Y = PENTE * Valeur X + INTERCEPTER”, donc la résolution de l' “Valeur Y” se fait en multipliant la valeur X et SLOPE puis en ajoutant l’INTERCEPT.

Exemple, nous mettons zéro comme valeur X. La valeur Y renvoyée doit être égale à l'INTERCEPTION de la ligne de meilleur ajustement. Coïncider, donc nous savons que la formule fonctionne correctement.

La résolution de la valeur X basée sur une valeur Y se fait en soustrayant l'INTERCEPTION de la valeur Y et en divisant le résultat par la PENTE:

Valeur X=(Y-value-INTERCEPT)/PENTE

Exemple, nous utilisons INTERCEPT comme valeur AND. La valeur X renvoyée doit être égale à zéro, mais la valeur de retour est 3.14934E-06. La valeur renvoyée n'est pas nulle car nous tronquons par inadvertance le résultat INTERCEPT lors de l'écriture de la valeur. Cependant, la formule fonctionne correctement car le résultat de la formule est 0.00000314934, qui est essentiellement nul.

Vous pouvez entrer n'importe quelle valeur X dans la première cellule de bordure épaisse et Excel calculera automatiquement la valeur Y correspondante.

En entrant n'importe quelle valeur Y dans la deuxième cellule de bordure épaisse, la valeur X correspondante sera obtenue. Cette formule est ce que vous utiliseriez pour calculer la concentration de cette solution ou quelle entrée est nécessaire pour lancer la bille à une certaine distance.

Dans ce cas, l’instrument lit “5”, donc l'étalonnage suggérerait une concentration de 4.94 ou nous voulons que la bille parcoure cinq unités de distance, donc le calibrage suggère que nous saisissions 4.94 comme variable d'entrée pour le programme qui contrôle le lanceur de billes. Nous pouvons avoir une confiance raisonnable dans ces résultats en raison de la valeur élevée de R au carré dans cet exemple.