TP MPI N° 05

Exploitation d'une série de mesures avec un tableur :  Correction

 Énoncé

I- But.

-  Exploitation avec un tableur d’une série de mesures. 

-  Utilisation d’un tableau de mesures, tracé de graphes et recherche d’un modèle.

II- Exemple de tableur : Excel.

1)- Présentation d’un tableur.

►  Un tableur est un logiciel performant qui permet :

-  De construire et de modifier des tableaux : ensemble de lignes et de colonnes.

-  De faire des calculs variés sur les lignes et les colonnes de ces tableaux.

-  De construire des graphiques à partir des données de ces tableaux.

-  etc.…..

►  C’est un outil très utilisé pour exploiter les résultats expérimentaux en Sciences physiques.

2)- Ouverture d’Excel.

a)- Présentation.

►  L’écran comprend :

-  La barre de titre (nom du logiciel avec le nom du fichier) : 1.

-  La barre de menu (Fichier, Édition,…..) : 2.

-  La barre des outils standard : 3.

-  La barre des outils mise en forme : 4.

-  La barre de formule : 5.

-  La barre d’état : 6.

b)- La feuille de calcul :

► La feuille de calcul est divisée en cellules, intersection de lignes et de colonnes.

-  Une feuille de calcul Excel contient 256 colonnes (A à IV) et 65536 lignes (1 à 65536).

-  Les colonnes sont repérées par des lettres : de A à Z pour les 26 premières et de combinaisons de deux lettres pour les autres.

-  Les lignes sont indiquées par des numéros de 1 à 65536.

-  Une cellule est caractérisée par sa colonne et sa ligne : la cellule C2.

c)- Les cellules.

► Lors de l’ouverture d’une nouvelle feuille de calcul, toutes les cellules sont vides.

► Une cellule peut contenir :

-  Une donnée numérique : la cellule A2 contient la valeur numérique 1.

-  Une formule, formée du signe égal suivi d’une expression littérale : la cellule C2 contient la formule : =A2+B2^2.

 Que représente cette formule ?

-  La cellule C2 contient le calcul suivant : à la valeur de la cellule A2, on ajoute le carré de la valeur de la cellule B2. 

-  La cellule C2 affiche le résultat du calcul. 

-  Un texte indiquant par exemple une grandeur et son unité.

► Remarque :

-  Quand la cellule contient une formule, celle-ci ne s’affiche que dans la barre de formule.

-  La cellule affiche le résultat du calcul correspondant. 

III- Premier exemple d’utilisation.

1)- Les données.

► Voici une partie de la fiche technique de l’essence de lavande produite par la GAEC LES Grandes marges Valensole.

-  Désignation : Lavande (Maillette)

-  Chromatogramme : Proportion relative des principaux constituants ( %).

2)- Rédaction du tableau.

a)- La ligne de titre.

-  Sélectionner la colonne B,  sur Format, Colonne, Largeur et taper 22 puis  sur O.K.

-  Sélectionner la colonne C, sur Format, Colonne, Largeur et taper 11 puis  sur O.K.

-  Sélectionner la colonne D, sur Format, Colonne, Largeur et taper 11 puis  sur O.K.

-  Sélectionner les cellules B2, C2 et D2 (se placer dans la cellule B2, faire un clic gauche, maintenir le clic et déplacer la souris vers la droite).

-  Appuyer sur l’icône  pour fusionner.

-  Taper le texte : ‘’Proportion relative des principaux constituants’’.

-  Police : Arial, 10, gras. Couleur de remplissage de la cellule : jaune clair.

-  Pour choisir la bordure.

-  Sélectionner la cellule B2, puis sur Format,

-  Cellule, Bordure, Couleur (bleue), Style  et Contour puis OK.

b)- Le tableau.

-  Dans la cellule B3 : taper ‘’Principaux constituants’’, en dessous Limonène et ainsi de suite.

-  Dans la cellule B17, taper ‘’Autres constituants’’.

-  Dans la cellule C3 : taper %  puis entrer les pourcentages des différents constituants.

c)- Mise en forme du tableau :

► Sélectionner la plage de cellules B3 : B17 (se placer dans la cellule B3, faire un clic gauche, maintenir le clic et déplacer la souris vers le bas).

-  sur Format, Cellule, alignement (horizontal : gauche, retrait 1 et vertical : centré).

-  sur Bordure, Couleur (bleue), Style , Contour et Intérieur,  puis  sur O.K..

-  Couleur de remplissage : brun.

► Sélectionner la plage de cellules C3 : C17

-  sur Format, Cellule, alignement (horizontal : centré et vertical : centré).

-  sur Bordure, Couleur (bleue), Style , Contour et Intérieur,  puis  sur O.K..

-  Couleur de remplissage : turquoise clair.

d)- Un premier calcul.

► Se placer dans la cellule C18.

-  Calculer la somme des pourcentages en déduire le pourcentage des ‘’Autres constituants’’,

-  puis compléter le tableau.

-  Dans la cellule C18, il faut taper la formule : 

-  =SOMME(C4:C16). Dans la cellule C19 : =100-C18.

-  Recopier la valeur de la cellule C19 dans la cellule C17.

3)- Exploitation du tableau.

► Pour analyser les données du tableau, on peut faire une représentation graphique.

-  Dans le cas présent, on peut réaliser un histogramme.

a)- Histogramme.

► À l’aide de la souris (clic gauche), sélectionner la plage de cellules : B4 : C17.

-  sur l’icône , puis comme type de graphique choisir ,

-  Histogramme et comme sous-type de graphique, sélectionner le premier en haut à gauche.

-  sur Suivant, sélectionner série en colonnes et sur suivant.

► Titre du graphique : Histogramme.

-  Axe des abscisses (X) : Principaux constituants.

-  Axe des ordonnées (Y) : Pourcentages %.

-  sur Axes : cocher  Axes des abscisses (X) et cocher  automatique ; cocher Axe des ordonnées (Y).

-  sur  Quadrillage : cocher quadrillage principal pour l’axe des abscisses et l’axe des ordonnées.

-  sur  Légende : sélectionner afficher la légende et cocher : droite.

-  sur  Étiquettes de données : cocher : aucune.

-  sur  Tables de données : ne pas afficher la table de données :

-  sur Suivant et placer le graphique en tant qu’objet dans la feuil1 puis   sur Terminer.

► Sélectionner le graphe et augmenter sa hauteur.

-  sur l’axe des abscisses. Puis sur Alignement et saisir le point rouge avec la souris.

-  Le déplacer vers le haut pour obtenir un angle de 90 ° .

-  Puis sur Échelle, puis entrer les valeurs 1,1 et 1 puis  sur O.K.

-  sur la Zone de traçage et sélectionner la couleur : jaune clair puis  sur O.K.

-  sur la Zone de graphique et sélectionner la couleur : vert clair puis  sur O.K.

-  sur Quadrillage principal de l’axe des ordonnées :

sur O.K.

-  sur Quadrillage principal de l’axe des abscisses : idem.

Commenter l’histogramme obtenu.

-  Les constituants sont placés dans l'ordre du tableau initial.

-  Ils sont représentés par des rectangles de même couleur, de même largeur mais dont la hauteur est différente.  

-  La hauteur est proportionnelle au pourcentage.

-  La légende n’est pas très explicite.

-  On peut facilement remarquer que le constituant le plus abondant est l’acétate de linalyle, puis le linalol, etc.…..

-  Amélioration : Pour que l’histogramme soit plus clair, on peut affecter une couleur à chaque constituant.

-  sur un constituant, puis sur le même constituant et dans format de point de données sélectionner une couleur.

Quelles sont les remarques que l’on peut faire ?

-  L'histogramme est plus clair, la légende apparaît à droite du graphique. 

-  On peut repérer facilement les principaux constituants de l'essence de lavande.

-  Les constituants apparaissent dans l’ordre du tableau initial. 

-  On peut facilement remarquer que le constituant le plus abondant est l’acétate de linalyle, puis le linalol, etc.…..

b)- Il faut trier les données.

► Pour faire parler l’histogramme, il faut trier les données.

-  Première étude : Sélectionner la plage de cellules B4 : C17, puis sur Données,  puis :

sur O.K.

 Quelles sont les remarques que l’on peut faire ?

-  Les constituants apparaissent dans l’ordre alphabétique. 

-  La couleur d'un constituant dépend de sa position dans le tableau. 

-  Cet histogramme n'apporte rien de plus que le précédent .

-  Deuxième étude : Sélectionner la plage de cellules B4 : C17, puis sur Données,  puis :

 puis  sur O.K.

Quelles sont les remarques que l’on peut faire ?

-  Les constituants sont classés par pourcentage croissant. 

-  On peut donner facilement le constituant le plus abondant et le constituant le moins abondant (parmi les constituants donnés).

c)- Diagramme en secteurs.

-  Exercice : Faire un diagramme en secteurs (type 1 ou 2) pour les constituants dont le pourcentage est supérieur à 4 %.

-  Appliquer la méthode utilisée pour tracer l’histogramme.

-  Diagramme en secteurs obtenu avec les constituants du tableau initial : 

-  Diagramme en secteurs obtenu avec les constituants du tableau initial dont le pourcentage est supérieur à 4 %.

-  Diagramme en secteurs obtenu avec les constituants du tableau initial dont le pourcentage est supérieur à 4 %.(modèle éclaté)

 Quelles sont les remarques que l’on peut faire ?

-  On obtient un diagramme en ‘’camembert’’. 

-  En prenant moins de constituants, le graphe devient plus parlant et plus facile à étudier. 

-  Pour faire un diagramme en secteur, il ne faut pas trop de constituants.

-  Les aires des secteurs sont proportionnelles aux pourcentages.

-  Il faut changer la valeur du pourcentage de ''Autres '' pour que la somme fasse 100 %.

IV- Deuxième exemple d’utilisation.

1)- Le problème posé.

► Le but est de donner la représentation graphique d’une fonction mathématique : y = x ², avec – 8 ≤ x ≤ 8 . .

2)- Le tableau de valeurs.

-  Ouvrir une nouvelle feuille Excel.

-  Dans la cellule B3, taper ‘’x’’. En dessous entrer – 8, puis – 7.

-  Sélectionner les deux cellules B4 et B5.

-  Déplacer le pointeur de la souris dans le coin inférieur droit de la sélection jusqu’à l’obtention d’un plus noir (+).

-  Faire un clic gauche, maintenir et déplacer la souris vers le bas jusqu’à ce que la valeur 8 apparaisse. Relâcher.

-  Dans la cellule C3, taper ‘’y’’.

-  Dans la cellule C4 taper la formule : = B4^2.

-  Il faut dupliquer la formule vers le bas.

-  Sélectionner la cellule C4. Déplacer le pointeur de la souris dans le coin inférieur droit de la cellule jusqu’à ce qu’un plus noir (+) apparaisse.

-  Faire un clic gauche, maintenir et déplacer la souris vers le bas tant que nécessaire.

-  Puis relâcher.

-  Maintenant, on peut mettre en forme le tableau.

3)- Représentation graphique.

► On peut faire une représentation graphique adaptée à la série de valeurs.

-  À l’aide de la souris(clic gauche), sélectionner la plage de cellules :    B4:C20.

-  sur l’icône , puis comme type de graphique, choisir ,

-  Nuage de points et comme sous-type de graphique,

-  sélectionner le premier en haut à gauche.

-  sur Suivant, sélectionner série en colonnes et 8 sur suivant.

-  Titre du graphique : y = (x²).

-  Axe des abscisses (X) : x.

-  Axe des ordonnées (Y) : y.

-   sur Axes : cocher : axes des abscisses (X) et choisir : automatique puis cocher : axe des ordonnées (Y).

-  sur  Quadrillage : cocher quadrillage principal pour l’axe des abscisses et l’axe des ordonnées.

-  sur Suivant et sur Terminer.

-  Sélectionner le graphe et augmenter sa hauteur. Cliquer sur série1 et sur Suppr.

-  sur l’axe des abscisses. sur Motifs.

-  Cocher : Graduation principale : Extérieure ;

-  Graduation secondaire : intérieure ;

-  étiquette de graduation : En bas, puis  sur O.K.

-  sur l’axe des ordonnées. sur Motifs.

-  Cocher : Graduation principale : Extérieure ;

-  Graduation secondaire : intérieure ;

-  étiquette de graduation : En bas, puis  sur O.K.

-  sur la Zone de traçage et sélectionner la couleur : jaune clair puis  sur O.K.

-  sur la Zone de graphique et sélectionner la couleur : vert clair puis  sur O.K.

-  sur la série des données : 

     Format de la série de données : 

    Marques : cocher  Personnalisée ;

     Premier plan : bleu, arrière plan : bleu ciel ; Style +.

-  Sélectionner : Titre de l’axe des ordonnées, 

-  sur Format, titre de l’axe sélectionné,

-  sur Alignement puis régler l’angle sur 0 °, 

-  puis  sur O.K.

-  On peut déplacer les différents textes du graphique.

4)- Exploitation des valeurs.

► À partir du graphe, on peut retrouver la relation liant x et y.

-  sur graphique et sur Ajouter courbe de tendance.

-  Type : polynomiale ; Ordre : 2  (le logiciel recherche une fonction du type y = a.x² + b.x + c).

-  Options : cocher : Afficher l’équation sur le graphique et Afficher le coefficient de détermination (R²) sur le graphique, puis  sur O.K.

 Interpréter le résultat obtenu :

-  Excel donne l’équation de la représentation graphique obtenue grâce à une étude statistique.

-  Le coefficient de détermination permet de savoir si le modèle utilisé est en adéquation avec la représentation graphique obtenue.

-  Lorsque R² = 1, l’adéquation est parfaite.

-  Si R² ≈ 1, il y une dépendance statistique entre les variables x et y.

-  C’est souvent le cas en physique car on travaille avec des valeurs expérimentales.

-  Équation donnée par Excel :  y = x²  —  ( 3 × 10^{– 14} × x )   —  ( 9 × 10^{– 14} ).

-  Aux approximations près, Excel donne l'équation de la courbe tracée.

-  On peut considérer que les termes b et c sont négligeables devant le terme a.

-  En arrondissant, on retrouve la relation initiale :

y = x²  

-  Bien sur, dans le cas traité, l’adéquation ne peut-être que parfaite puisque les valeurs de y sont calculées en utilisant la relation. 

-  en physique, on travaille avec des valeurs expérimentales et c’est à partir des valeurs expérimentales que l‘on recherche une loi physique.

-  Grâce au tableur Excel, on peut trouver la relation qui lie deux grandeurs (si elle existe) en faisant une représentation graphique (nuage de points) et en recherchant la courbe de tendance la mieux adaptée à la représentation.

-  Le but est de trouver un modèle mathématique qui se rapproche le plus du modèle expérimental.

-  Le coefficient de détermination R² permet de savoir si le modèle choisi est bien en accord avec les résultats expérimentaux.

V- Troisième exemple : La loi d’Ohm.

Montage : .

1)- Données.

► Reprendre les mesures du TP MPI N° 04  Caractéristique d’un conducteur ohmique.

-  Tableau de valeurs.

2)- Tableau de valeurs.

-  Ouvrir une nouvelle feuille Excel.

-  Dans la cellule B3, taper ‘’ I ( mA )’’. En dessous, entrer les différentes valeurs de l’intensité du courant..

-  Dans la cellule C3, taper ‘’ U_AB (V)’’. En dessous, entrer les différentes valeurs de la tension. Respecter l’ordre des valeurs.

-  mettre en forme le tableau.

3)- Représentation graphique.

-  Tracer le graphique U_AB = f (I).

Quel modèle mathématique faut-il choisir ?  Tracer la courbe de tendance et en donner les caractéristiques.

-  Les points sont sensiblement alignés. 

-  On choisit comme type : linéaire et comme option : 

-  afficher l'équation sur le graphique et afficher le coefficient de détermination (R²) sur le graphique.

Quelle conclusion peut-on tirer ?

-  Il existe une relation linéaire entre U_AB et I. La grandeur U_AB est proportionnelle à la grandeur I. 

-  Il y a une dépendance statistique entre les variables U_AB et I.

Comme procéder pour obtenir la valeur du coefficient directeur de la courbe dans les unités du S.I ?

-  La grandeur a représente le coefficient de proportionnalité : U_AB = a. I.

-  En physique a = R qui est la résistance du conducteur ohmique (grandeur caractéristique). 

-  L’unité est le l’ohm :   .

-  Pour obtenir a  = R dans les unités du Système International, il faut exprimer l'intensité I en ampère. 

-  On peut ajouter une colonne et faire effectuer le calcul par Excel : 

-  Dans le cas présent, on tape la formule suivante dans la cellule F5 : =C5/1000

-  Puis on construit le graphe avec les séries des colonnes F et G.

-  Équation de la droite obtenue : U_AB = R. I .

-  Dans le cas qui nous intéresse : U_AB ≈ 33,3. I . 

-  Le coefficient de détermination :  R² ≈ 0,9995 ≈ 1. 

-  Il y a dépendance statistique linéaire entre les grandeurs U_AB et I.  

-  Valeur du coefficient directeur dans le S.I : a  ≈ 33,3 Ω ≈ R résistance du conducteur ohmique.

-  Ne pas confondre :  R² qui est le coefficient de détermination donné par Excel et R résistance du conducteur ohmique.

VI- Caractéristique d’une varistance.

1)- Objectif : tracer la caractéristique intensité – tension d’une varistance.

-  La varistance s’appelle aussi la V.D.R ( Volt Dépendant Résistor).

2)- Proposer un schéma du montage permettant d'étudier la varistance.

3)- Manipulation.

   Réaliser le montage et le faire vérifier.

4)- Mesures et exploitation des mesures.

a)-     Mesures : ne pas dépasser 8 V ou 60 mA.

-  Faire varier la tension U_AB aux bornes de la varistance

-  Pour chaque valeur de U_AB , relever la valeur de l'intensité I du courant qui traverse la varistance.

b)-     Tableau de mesures :

-          Reproduire et compléter le tableau en réalisant une douzaine de mesures judicieusement réparties.

5)-  Exploitation des mesures

 Tracer la caractéristique intensité-tension de la varistance, c'est-à-dire : U_AB = f (I).

Quelles sont les caractéristiques de la courbe obtenue ?

-  On obtient une fonction croissante de la tension en fonction de l’intensité. 

-  Il n’existe pas de relation simple entre la tension U_AB aux bornes de la varistance et l’intensité I du courant qui la traverse.

-  La tension aux bornes de la varistance n'est pas proportionnelle à l'intensité du courant qui la traverse.

► Exploiter le graphe :

-  Existe-t-il une loi simple entre U_AB et I ?

-  On peut rechercher par tâtonnement s’il existe une loi simple entre U_AB et I .

-  Le but est de se ramener à l’étude d’une fonction linéaire et de tracer la droite moyenne.

-  On représente :  .

Quelles remarques peut-on faire ?

-  On remarque que les points sont sensiblement alignés et que le coefficient de détermination est proche de 1.

-  Il y a dépendance linéaire entre . On peut en déduire une loi simple entre .

Quelle relation existe-t-il entre U_AB et I  tant que la tension aux bornes de la varistance (ou l’intensité qui traverse la varistance) n’est pas trop élevée ?

-  La tension U_AB est proportionnelle à la racine carrée de l'intensité I.

Donner les caractéristiques de la courbe obtenue si U_AB  < 7 V ?  U_AB  < 6 V ? Conclusion.

-  On remarque que le coefficient de détermination   R²  se rapproche de la valeur 1 lorsque l'on diminue la plage d'utilisation de la varistance.

En déduire la relation liant U_AB et I  et le domaine de validité de cette relation.

 et

-  Il y a dépendance statistique linéaire entre les grandeurs .

VII- Étude d’une lampe.

1)- Objectif : on trace la caractéristique intensité tension d’une lampe à incandescence et on cherche une relation liant ces deux grandeurs.

2)- Proposer un schéma du montage permettant d'étudier la lampe.

3)- Manipulation.

Réaliser le montage et le faire vérifier.

4)- Mesures et exploitation des mesures.

a)-     Mesures : ne pas dépasser 100 mA.

-  Faire varier la tension U_AB aux bornes de la lampe.

-  Pour chaque valeur de U_AB , relever la valeur de l'intensité I du courant qui traverse la lampe.

b)-     Tableau de valeurs.

-          Reproduire et compléter le tableau en réalisant une douzaine de mesures judicieusement réparties.

b)-      Exploitation des mesures

Tracer la caractéristique intensité-tension de la lampe, c'est-à-dire : U_AB = f (I) à l’aide du tableur Excel.

Quelles sont les caractéristiques de la courbe obtenue ?

-  On obtient une fonction croissante de la tension en fonction de l’intensité. 

-  Il n’existe pas de relation simple entre la tension U_AB aux bornes de la varistance et l’intensité I du courant qui la traverse.

-  La tension aux bornes de la lampe n'est pas proportionnelle à l'intensité du courant qui la traverse.

Exploiter le graphe avec Excel.

-   sur graphique et sur Ajouter courbe de tendance.

-  Type : polynomiale ; Ordre : 2  (le logiciel recherche une fonction du type :

-   y = a.x² + b.x + c.

-  Options : cocher :

-  Afficher l’équation sur le graphique et Afficher le coefficient de détermination (R²) sur le graphique,

-  puis  sur O.K.

-  Résultat :

-  On peut refaire la représentation graphique en utilisant les unités du S.I.

-  Excel donne l’équation de la représentation graphique obtenue grâce à une étude statistique.

-  Le coefficient de détermination permet de savoir si le modèle utilisé est en adéquation avec la représentation graphique obtenue. 

-  Ici, avec le modèle choisi,  R² ≈ 0,9996 ≈ 1, il y a une dépendance statistique entre les variables I et U_AB.

-  On en déduit que dans le domaine de valeurs :

- 

Conclusions.

-  La tension aux bornes d’une lampe n’est pas proportionnelle à l’intensité du courant qui la traverse.

-  La loi n’est pas simple.

-  On peut tracer les variations de U_AB  en fonction de I² et faire une étude avec Excel.  

-  On peut considérer que :

U_AB  ≈ a I².

-  On remarque que si on élimine les premières valeurs (ou la lampe brille très peu), l’adéquation devient satisfaisante.

-  Si 4 V  ≤  U_AB  ≤ 12 V, alors :

U_AB  ≈ 1144 I² avec  R² ≈ 0,9998.