Tutoriel Excel : comment créer une courbe d'étalonnage

Dans ce tutoriel, vous apprendrez comment utiliser Excel pour 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 « ORDONNEE.ORIGINE » pour utiliser l'équation d'étalonnage dans Excel.

En travaillant en laboratoire, il existe différentes façons de calculer la quantité d'un « analyte » présent dans un échantillon en le comparant aux normes. Vous pouvez pour cela utiliser un seul étalon externe, une courbe d'étalonnage, un étalon interne ou encore un ajout d'étalon. Apprendre à utiliser ces différentes méthodes de calcul est essentiel pour travailler en laboratoire et fait partie intégrante de tout programme de formation en laboratoire.

En outre, la plupart des mesures de la chimie analytique impliquent des fonctions d'étalonnage qui peuvent être décrites en utilisant une ligne droite. Les outils graphiques de Microsoft Excel offrent un excellent moyen de tracer les relations connues des paires ordonnées ou non ordonnées sur une courbe d'étalonnage. Cela peut être utile lorsque vous rédigez un rapport de laboratoire de chimie ou que vous programmez un facteur de correction dans un équipement.

Table des matières

Qu'est-ce qu'une courbe d'étalonnage ?

Comment créer une courbe d’étalonnage ?

Étape 1: préparer la source de données

Étape 2: créer une courbe d'étalonnage

Étape 3: ajouter une ligne de tendance

Étape 4: personnaliser le graphique d’étalonnage

Etape 5 : calculer l'équation de ligne et la statistique R au carré

Etape 6 : configurer des formules pour calculer rapidement des valeurs

Qu'est-ce qu'une courbe d'étalonnage ?

La prudence et les bonnes pratiques scientifiques exigent que les appareils de mesure soient étalonnés. C'est-à-dire que les mesures doivent être effectuées sur des échantillons avec des propriétés connues avant de mesurer des échantillons avec des propriétés inconnues. Considérons, par exemple, un thermomètre. Ce n'est pas parce qu'un thermomètre indique 77 degrés Fahrenheit que la température réelle dans la pièce est de 77 degrés Fahrenheit.

Dans le cas d'un thermomètre, prendre au moins deux mesures d'échantillons avec des valeurs connues, peut signifier plonger le thermomètre dans de l'eau glacée (0 degrés Celsius) et dans de l'eau bouillante (100 degrés Celsius).

Un étalonnage en ces deux points (points de congélation et d'ébullition de l'eau) d'un thermomètre aurait deux paires de données :

• A partir du moment où le thermomètre est placé dans de l'eau glacée (32 ° F ou 0 ° C).
• A partir du moment où le thermomètre est placé dans de l'eau bouillante (212 ° F ou 100 ° C).

Lorsque vous tracez ces deux paires de données en tant que points et tracez une ligne entre elles (la courbe d'étalonnage), et puis en supposant que la réponse du thermomètre est linéaire, vous pouvez choisir n'importe quel point sur la ligne qui correspond à la valeur affichée par le thermomètre afin de trouver la « vraie » température correspondante.

Vous pouvez créer un graphique des mesures d'étalonnage en traçant la valeur « connue » sur l'axe des y et la valeur « expérimentale » sur l'axe des x. Cela peut être fait manuellement (c'est-à-dire à la main sur du papier millimétré) ou à l'aide d'un programme graphique informatique, tel que Microsoft Excel. Ce denier possède des fonctionnalités qui permettent de tracer les paires de données dans un graphique, d'ajouter une ligne de tendance (courbe d'étalonnage) et d'afficher l'équation de la courbe d'étalonnage sur le graphique.

Comment créer une courbe d’étalonnage ?

Étape 1: Préparer la source de données

1. Ouvrez le classeur Excel contenant les valeurs « connues » à tracer sur une courbe d'étalonnage.
2. Organisez les données par des entrées paires (Une paire par ligne avec les valeurs dans les colonnes adjacentes).
3. Attribuez des en-têtes appropriés aux colonnes de données.
4. Triez par l'une des colonnes si les données nécessitent un ordre spécifique.

Pour cet exemple, je vais développer une courbe d'étalonnage à partir d'une série de dix paires de données. Chaque paire est composée d'une valeur X et d'une valeur Y.

• Les valeurs X seront les « normes », et représentent la concentration d'une solution chimique qu’on va mesurer à l'aide d'un instrument scientifique.
• Les valeurs Y seront les « réponses », et elles représenteraient la lecture de l'instrument fourni lors de la mesure de chaque solution chimique.

Étape 2: Créer une courbe d'étalonnage

1. Mettez en surbrillance toutes les données à utiliser pour la courbe d'étalonnage.
2. Cliquez sur l'en-tête en haut de la colonne de gauche des données (étiquetées « x » dans l'exemple).
3. Maintenez le bouton de la souris enfoncé et faites glisser le pointeur de la souris vers la dernière cellule de la colonne de gauche (étiquetée « y » dans l'exemple).
4. Relâchez le bouton de la souris.

5. Cliquez sur l'onglet « Insertion » puis sur l'icône « Insérer un graphique en nuages de point (X,Y) ou en bulles » pour afficher la galerie de variations du diagramme de dispersion.

6. Cliquez sur l'option « Nuages de points » pour générer un diagramme de dispersion avec un marqueur représentant chaque paire de données.

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

Étape 3: Ajouter une ligne de tendance

Pour ajouter une ligne de tendance au graphique en nuages de points, suivez les étapes ci-dessous :

1. Sélectionnez la série en cliquant sur l'un des points bleus. Une fois sélectionnée, Excel décrit les points qui seront soulignés.

2. Cliquez avec le bouton droit sur l'un des points, puis sélectionnez l'option « Ajouter une courbe de tendance » dans le menu contextuel. Ainsi, une ligne droite apparaîtra sur le graphique.

Lorsque vous ajoutez la ligne de tendance au graphique, la boîte de dialogue « Formater la ligne de tendance » apparaît à droite de l'écran dans Excel.

3. Cochez les cases à côté de « Afficher l'équation sur le graphique » et « Afficher le coefficient de détermination (R² ) sur le graphique » :

La valeur R au carré est une statistique qui vous indique à quel point la ligne correspond aux données. La meilleure valeur R au carré est de 1000, 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 au carré diminue, 0,000 étant la 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 cet exemple, avec une valeur R au carré de 0,988.

L'équation est sous la forme « Y = A * x + B », où A est la pente et B est l'ordonnée à l'origine de la droite.

Étape 4: Personnaliser le graphique d’étalonnage

Maintenant que l'étalonnage est terminé, essayons de personnaliser le graphique en modifiant le titre et en ajoutant des titres d'axe.

Pour modifier l'apparence ou choisir un format différent pour le graphique, utilisez les options affichées dans le sous-onglet « Création » de l’onglet « Outils de graphique » qui s'affiche lorsqu'un graphique est sélectionné et actif.

1. Pour changer le titre du graphique, cliquez dessus pour sélectionner le texte.
2. Tapez maintenant un nouveau titre qui décrit le graphique.

3. Pour ajouter des titres à l'axe x et à l'axe y, accédez d'abord à « Outils de graphique » ® « Création ».
4. Cliquez sur le menu déroulant « Ajouter un élément de graphique ».
5. Maintenant, accédez à « Titres des axes » et choisissez « Horizontal principal ». Un titre d'axe apparaîtra.

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

7. Maintenant, dirigez-vous vers « Titres des axes » et sélectionnez « Vertical principal ». Un titre d'axe apparaîtra.

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

Une fois terminé, votre graphique d'étalonnage devrait ressembler à ce qui suit.

Quelques commentaires explicatifs sont nécessaires ici :

• La droite la mieux ajustée (la ligne de régression de y sur x) est calculée en supposant que toutes les erreurs sont dans les valeurs mesurées (y), et pas dans les valeurs de concentration (x).
• Cette ligne de régression minimise la distance dans la direction entre la ligne et les points individuels, et passe par le centre de gravité des données (valeurs moyennes x et y).
• R est une mesure de corrélation, pas de linéarité. En conséquence, cela peut être trompeur. Vous devez toujours regarder le graphique avec la ligne droite la mieux adaptée plutôt que de vous fier uniquement à R pour évaluer la linéarité.

Après avoir représenté graphiquement la courbe d'étalonnage, vous pouvez utiliser les fonctions « PENTE » et « ORDONNEE.ORIGINE » pour calculer la formule de la ligne d'étalonnage et déterminer la concentration d'une solution chimique « inconnue » en fonction de la lecture de l'instrument.

Etape 5 : Calculer l'équation de ligne et la statistique R au carré

Calculons maintenant l'équation de ligne et la statistique R au carré à l'aide des fonctions intégrées « PENTE », « ORDONNEE.ORIGINE » et « COEFFICIENT.CORRELATION » d'Excel.

Dans la feuille de calcul de cet exemple (ligne 13), j’ai ajouté des titres pour ces trois fonctions. Je vais effectuer les calculs réels dans les cellules sous ces titres.

Commençons par calculer la PENTE :

1. Sélectionnez la cellule A14.
2. Accédez à l’onglet « Formules ».
3. Dans le groupe « Bibliothèque de fonctions » cliquez sur « Plus de fonctions ».

4. Sélectionnez « Statistiques » dans le menu déroulant, puis sélectionnez la fonction « PENTE » dans la liste des fonctions proposées.

5. La fenêtre « Arguments de fonction » apparaît.
6. Dans le champ « Y_connus », sélectionnez ou saisissez les cellules de la colonne « Y ».
7. Dans le champ « X_connus », sélectionnez ou saisissez les cellules de la colonne « X ». L'ordre des champs « Y_connus » et « X_connus » est important dans la fonction PENTE.

8. Cliquez sur OK.

La formule finale dans la barre de formule doit ressembler à ceci :   =PENTE(B2:B11; A2:A11)

Notez que la valeur renvoyée par la fonction « PENTE » dans la cellule A14 correspond à la valeur affichée sur le graphique.

9. Ensuite, sélectionnez la cellule B14, puis accédez à l’onglet « Formules ».
10. Cliquez à nouveau sur « Plus de fonctions ».
11. Choisissez le type « Statistiques » et sélectionnez la fonction « ORDONNEE.ORIGINE ».

12. La fenêtre « Arguments de fonction » apparaît. Sélectionnez ou saisissez les cellules de la colonne Y pour le champ « Y_connus ».
13. Sélectionnez ou saisissez les cellules de la colonne X pour le champ « X_connus ». L'ordre des champs « Y_connus » et « X_connus » est également important dans la fonction ORDONNEE.ORIGINE.

14. Cliquez sur OK.

 La formule finale dans la barre de formule doit ressembler à ceci :   =ORDONNEE.ORIGINE(B2:B11; A2:A11)

Notez que la valeur renvoyée par la fonction ORDONNEE.ORIGINE correspond à l'ordonnée à l'origine affichée dans le graphique.

15. Sélectionnez la cellule C14 et accédez à « Formules »  ->  « Plus de fonctions » -> « Statistiques » -> « COEFFICIENT.CORRELATION ».

16. La fenêtre « Arguments de fonction » apparaît.
17. Sélectionnez ou saisissez l'une des deux plages de cellules pour le champ « Matrice1 ». Contrairement à PENTE et ORDONNEE.ORIGINE, l'ordre n'affecte pas le résultat de la fonction COEFFICIENT.CORRELATION.
18. Sélectionnez ou saisissez l'autre plage de cellules pour le champ « Matrice2 ».

19. Cliquez sur OK.

Dans la barre de formule, la formule doit ressembler à ceci :   =COEFFICIENT.CORRELATION(A2:A11;B2:B11)

Notez que la valeur renvoyée par la fonction COEFFICIENT.CORRELATION ne correspond pas à la valeur « R² » sur le graphique. La fonction COEFFICIENT.CORRELATION renvoie «R». Nous devons donc la mettre au carré pour calculer «R au carré».

20. Cliquez à l'intérieur de la barre de fonctions et ajoutez « ^ 2 » à la fin de la formule pour cadrer la valeur renvoyée par la fonction COEFFICIENT.CORRELATION.

La formule devrait maintenant ressembler à ceci :  =COEFFICIENT.CORRELATION(A2:A11;B2:B11)^ 2

21. Appuyez sur Entrée.

Après avoir changé la formule, la valeur « R au carré » correspond maintenant à celle affichée dans le graphique.

Etape 6 : Configurer des formules pour calculer rapidement des valeurs

Maintenant, vous pouvez utiliser ces valeurs dans des formules simples pour déterminer la concentration de cette solution « inconnue ».

Ces étapes établiront les formules requises 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 au meilleur ajustement se présente sous la forme « Y = PENTE *  X + ORDONNEE.ORIGINE ». Donc, la résolution de la valeur de « Y » se fait en effectuant le produit de la valeur X et la « PENTE », puis en ajoutant « ORDONNEE.ORIGINE ».

Je vais mettre, par exemple, zéro comme valeur de X. La valeur de Y retournée doit être égale à l'ORDONNEE.ORIGINE de la ligne au meilleur ajustement. C’est correct, donc la formule fonctionne correctement !

• La résolution de la valeur de X basée sur une valeur de Y se fait en soustrayant la valeur de « ORDONNEE.ORIGINE » de la valeur de Y et en divisant le résultat par la valeur de la PENTE :

X=(Y – ORDONNEE.ORIGINE)/PENTE

Dans cet exemple, j’ai utilisé la valeur de « ORDONNEE.ORIGINE » comme valeur de Y. La valeur de X renvoyée doit être égale à zéro, mais la valeur renvoyée est 3,14934E-06. La formule fonctionne correctement car le résultat est 0,00000314934, ce qui est essentiellement zéro. Vous pouvez obtenir ce résultat en changeant le format de la cellule « B21 ». Pour ce faire, accédez à l’onglet « Accueil », puis dans le groupe « Nombre » modifiez le type « Standard » en « Nombre ».

Vous pouvez entrer la valeur de X que vous souhaitez dans la première cellule (B17) et Excel calculera automatiquement la valeur de Y correspondante puis l’affichera dans la cellule B18.

La saisie d'une valeur pour Y dans la deuxième cellule (B20) donnera la valeur de X correspondante dans la cellule B21. Cette formule est celle que vous utiliserez pour calculer la concentration de cette solution.

Télécharger le fichier Excel de ce tutoriel