Excel propose des outils intégrés pour analyser vos données d’étalonnage et déterminer la droite d’ajustement optimale. Ceci s’avère très utile, que ce soit pour la rédaction d’un rapport de laboratoire en chimie ou pour la programmation d’un facteur de correction dans un équipement.
Dans cet article, nous allons explorer comment utiliser Excel pour générer un graphique, tracer une courbe d’étalonnage linéaire, afficher la formule de cette courbe et configurer des formules simples à l’aide des fonctions PENTE et ORDONNEE.A.L’ORIGINE, afin d’exploiter l’équation d’étalonnage dans Excel.
Comprendre la courbe d’étalonnage et le rôle d’Excel
L’étalonnage consiste à comparer les mesures d’un appareil (par exemple, la température indiquée par un thermomètre) avec des valeurs de référence, appelées étalons (tels que les points de congélation et d’ébullition de l’eau). Cette approche permet de créer un ensemble de couples de données qui serviront ensuite à établir 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, produirait deux couples de données : l’un obtenu lorsque le thermomètre est plongé dans de l’eau glacée (0 °C ou 32 °F) et l’autre dans de l’eau bouillante (100 °C ou 212 °F). En traçant ces deux couples de données sur un graphique et en traçant une ligne entre eux (la courbe d’étalonnage), en supposant une réponse linéaire du thermomètre, vous pouvez déterminer la température « réelle » correspondant à n’importe quelle valeur affichée par le thermomètre en vous référant à la courbe.
Ainsi, la courbe estime les informations entre les deux points connus, vous permettant d’évaluer avec une certaine assurance la température réelle même si le thermomètre affiche une lecture de 57,2 degrés et que vous n’avez pas de point de référence correspondant.
Excel offre des fonctions pour représenter graphiquement les couples de données, ajouter une courbe de tendance (courbe d’étalonnage) et afficher l’équation de cette courbe sur le graphique. Cela est pratique pour une visualisation, mais vous pouvez également calculer la formule de la droite avec les fonctions PENTE et ORDONNEE.A.L’ORIGINE. En intégrant ces valeurs dans des formules simples, vous pourrez calculer automatiquement la valeur « réelle » en fonction de n’importe quelle mesure.
Étude d’un exemple concret
Dans cet exemple, nous allons établir une courbe d’étalonnage à partir d’une série de dix couples de données, comprenant chacun une valeur X et une valeur Y. Les valeurs X seront nos « étalons ». Elles pourraient représenter la concentration d’une solution chimique mesurée avec un instrument scientifique ou la variable d’entrée d’un programme qui contrôle une machine de lancement de billes.
Les valeurs Y, quant à elles, seront les « réponses », correspondant à la lecture de l’instrument pour chaque solution chimique ou à la distance parcourue par la bille en fonction de chaque valeur d’entrée du programme.
Une fois la courbe d’étalonnage tracée, nous utiliserons les fonctions PENTE et ORDONNEE.A.L’ORIGINE pour calculer l’équation de la droite d’étalonnage et déterminer la concentration d’une solution chimique « inconnue » à partir de la lecture de l’instrument. Nous pourrons également déterminer l’entrée à donner au programme pour que la bille atteigne une distance spécifique.
Première étape : Création du graphique
Notre feuille de calcul d’exemple se compose de deux colonnes : « Valeur X » et « Valeur Y ».
Commençons par sélectionner les données à utiliser pour le graphique.
Sélectionnez d’abord les cellules de la colonne « Valeur X ».
Maintenez la touche Ctrl enfoncée, puis cliquez sur les cellules de la colonne « Valeur Y ».
Allez dans l’onglet « Insertion ».
Dans le menu « Graphiques », sélectionnez la première option du menu déroulant « Nuage de points ».
Sélectionnez la série de données en cliquant sur l’un des points bleus. Excel mettra en évidence les points ainsi sélectionnés.
Faites un clic droit sur l’un des points, puis sélectionnez l’option « Ajouter une courbe de tendance ».
Une ligne droite apparaîtra sur le graphique.
Sur le côté droit de l’écran, le menu « Format de la courbe de tendance » s’ouvrira. Cochez les cases « Afficher l’équation sur le graphique » et « Afficher le coefficient de détermination (R²) sur le graphique ». Le R² est une statistique qui indique la qualité de l’ajustement de la droite aux données. La meilleure valeur est 1,000, signifiant que tous les points se trouvent sur la droite. Plus les points s’écartent de la droite, plus le R² diminue, jusqu’à 0,000, qui est la valeur la plus basse.
L’équation et le coefficient de détermination R² de la courbe de tendance s’afficheront sur le graphique. Dans notre exemple, la corrélation des données est très bonne, avec un R² de 0,988.
L’équation est sous la forme « Y = Mx + B », où M représente la pente et B l’ordonnée à l’origine de la droite.
Maintenant que l’étalonnage est terminé, personnalisons le graphique en modifiant le titre et en ajoutant des titres aux axes.
Pour changer le titre du graphique, cliquez dessus pour sélectionner le texte.
Saisissez un nouveau titre qui décrit le graphique.
Pour ajouter des titres à l’axe des abscisses et à l’axe des ordonnées, accédez d’abord à « Outils de graphique > Création ».
Ensuite, allez dans « Titres des axes > Horizontal principal ».
Pour renommer le titre de l’axe, sélectionnez le texte, puis saisissez un nouveau titre.
Allez maintenant dans « Titres des axes > Vertical principal ».
Un titre d’axe s’affichera.
Renommez ce titre en sélectionnant le texte et en saisissant un nouveau titre.
Votre graphique est maintenant terminé.
Deuxième étape : Calcul de l’équation linéaire et du R²
Calculons maintenant l’équation de la droite et le R² en utilisant les fonctions intégrées PENTE, ORDONNEE.A.L’ORIGINE et COEFFICIENT.CORRELATION d’Excel.
Nous avons ajouté des titres pour ces trois fonctions sur notre feuille (ligne 14). Les calculs réels seront effectués dans les cellules sous ces titres.
Nous allons d’abord calculer la PENTE. Sélectionnez la cellule A15.
Allez dans « Formules > Plus de fonctions > Statistiques > PENTE ».
Dans le champ « X_connus », sélectionnez ou entrez les cellules de la colonne « Valeur X ». L’ordre des champs « Y_connus » et « X_connus » est crucial dans la fonction PENTE.
Cliquez sur OK. La formule finale dans la barre de formule devrait ressembler à ceci:
=PENTE(C3:C12;B3:B12)
Notez que la valeur renvoyée par la fonction PENTE dans la cellule A15 correspond à celle affichée sur le graphique.
Sélectionnez maintenant la cellule B15, puis allez dans « Formules > Plus de fonctions > Statistiques > ORDONNEE.A.L’ORIGINE ».
Sélectionnez ou entrez les cellules de la colonne « Valeur X » pour le champ « X_connus ». L’ordre des champs « Y_connus » et « X_connus » est également important dans la fonction ORDONNEE.A.L’ORIGINE.
Cliquez sur OK. La formule finale dans la barre de formule devrait être:
=ORDONNEE.A.L’ORIGINE(C3:C12;B3:B12)
Notez que la valeur renvoyée par la fonction ORDONNEE.A.L’ORIGINE correspond à l’ordonnée à l’origine affichée dans le graphique.
Sélectionnez ensuite la cellule C15 et allez dans « Formules > Plus de fonctions > Statistiques > COEFFICIENT.CORRELATION ».
Sélectionnez ou entrez l’autre plage de cellules pour le champ « Matrice2 ».
Cliquez sur OK. La formule dans la barre de formule devrait être:
=COEFFICIENT.CORRELATION(B3:B12;C3:C12)
Notez que la valeur renvoyée par la fonction COEFFICIENT.CORRELATION ne correspond pas au R² affiché sur le graphique. La fonction COEFFICIENT.CORRELATION renvoie « R », il faut donc la mettre au carré pour obtenir le « R² ».
Cliquez dans la barre de formules et ajoutez « ^2 » à la fin de la formule pour mettre au carré la valeur renvoyée par la fonction COEFFICIENT.CORRELATION. La formule complétée devrait maintenant être:
=COEFFICIENT.CORRELATION(B3:B12;C3:C12)^2
Appuyez sur Entrée.
Après avoir modifié la formule, la valeur du « R² » correspond maintenant à celle affichée dans le graphique.
Troisième étape : Configuration de formules pour des calculs rapides
Nous pouvons maintenant utiliser ces valeurs dans des formules simples afin de déterminer rapidement la concentration de cette solution « inconnue » ou l’entrée à saisir dans le code pour que la bille vole à une certaine distance.
Ces étapes vont configurer les formules nécessaires pour pouvoir saisir une valeur X ou une valeur Y et obtenir la valeur correspondante en fonction de la courbe d’étalonnage.
<img decoding= »async » loading= »lazy » class= »alignnone wp-image-399921 size-full » src= »https://toptips.fr/wp-content/uploads/2020/12/1609408238_566_Comment-faire-une-courbe-detalonnage-lineaire-dans-Excel.png » alt= »entrez une valeur X ou une valeur Y et obtenez la valeur correspondante » width= »650″ height= »350″ onload= »