TP de Tableur, 07.11.2013


Exercices

(Les solutions/commentaires seront là (peut-être).)

Les exercices d'aujourd'hui sont obligatoires, au sens : vous devez me les rendre par mail dans 7 jours. C'est-à-dire, AVANT le jeudi prochaîn. Mercredi minuit de 13.11 - 14.11, je ferme la boîte. Ces exercices seront évalués, et contribueront à votre note.

Ceci dit, jusqu'a lundi (inclus) je suis ouvert à recevoir vos QUESTIONS. Si vous trouvez qu'un sujet (qui demande un peu de rélexion, je déteste des exercices mécaniques, même si parfois ils sont inévitables...) vous fait grincer les dents, ou vous avez une erreur trop difficile à localiser et à corriger, n'hésitez pas à demander mon aide !


1. Régression linéaire

Ce terrible mot signifie ici simplement une estimation du comportement des données qui "doivent" suivre une règle théorique. Sur le graphique à gauche on voit un certain nombre de données, et il est naturel de conclure que ceci représente une valeur qui grandit approximativement linéairement avec la variable indépendante, avec des fluctuations.

Nous voulons trouver la "croissance théorique", une ligne droite qui passe de manière la plus exacte sur / à côté des points expérimentaux.

Importez et ouvrez le fichier regres.ods (chez moi : dans le dossier Feuilles). Construisez le diagramme comme celui à gauche.

Exercice. Premier exercice, assez mécanique, sert uniquement pour vous orienter. Insérez dans le diagramme la courbe de tendance (ligne droite). Trouver les paramètres $(A,B)$ de son équation $f(x) =A x + B$.

Exercice. Vous allez à présent construire cette ligne de tendance à la main.

La technique utilisée est celle de moindres carrés. Étant données les séries de données $x_i :\quad x_1, x_2, x_3, \ldots x_n$, et $y_i :\quad y_1, y_2, y_3,\ldots$, il faut proposer une droite $y(x)=Ax+B$, de manière à ce qui l'écart quadratique cumulé :

$\displaystyle{s=\sum_{i=1}^n (y(x_i) - y_i)^2}$
soit minimal. On cherche donc le minimum de la fonction $s(A,B) = \sum_{i=1}^n (A x_i + B - y_i)^2}$ en fonction de ses paramètres. S'ils sont tels, que l'on se trouve dans le minimum, les dérivées : $\partial s/\partial A$ et $\partial s/\partial B$ sont égales à zéro. Nous aurons deux équations à deux inconnues à résoudre :

$\displaystyle{A \sum x_i^2 + B \sum x_i = \sum x_i \cdot y_i}$

$\displaystyle{A \sum x_i + B \cdot n = \sum y_i}$

Or, on connaît la solution d'un tel système. Pour les équations en notation générale, avec la matrice principale $M$, et le vecteur constant $C$ :

$M_{00} A + M_{01} B = C_0 ; \qquad M_{10} A + M_{11} B = C_1$

nous aurons :

$D = M_{00} \cdot M_{11} - M_{01} \cdot M_{10}$ (c'est le déterminant principal de ce système)

$A = (C_0 M_{11} - C_1 M_{01})/D$

$B = (C_1 M_{00} - C_0 M_{10})/D$

Construisez sur votre feuille du tableur des cellules avec les sommes correspondantes, et trouvez les solutions de ces équations. Comparez avec la courbe de tendance. Est-ce que la correspondance est bonne? Sinon, il est toujours possible que je me suis trompé dans les équations... Alors, corrigez-les.

Ayant les paramètres, tracez la courbe de tendance calculée par vous à côté de la droite automatique, avec une couleur différente.

Conseil général : NOMMEZ VOS VARIABLES ! Utilisez des noms comme "x", "sxy", etc., et non pas directement les identifiants des cellules. En TP j'ai constaté 4 cas d'erreur provoquées uniquement par des fautes d'adressage.

Usage du Solveur. Bonus

Une autre technique à maîtriser (ce qui ne vous exonère pas des calculs ci-dessus...) est la solution des équations linéaires grâce au Solveur, un outil de programmation linéaire incorporé dans Calc. (Dans OpenOffice il existe un module non-linéaire aussi, mais en Libre Office il ne marche actuellement pas).

Testez le solveur. Lisez le tutoriel, et programmez les équations du problème ci-dessus. Ceci demandera un peu de patience de votre part, et est considéré comme un exercice - bonus. Mais le Solveur pourra vous être utile plus tard.


2. Interpolation

Regardez encore une fois les données dans regres.ods. Vous avez 25 valeurs des x, entre 0 et 2.4, et 25 valeurs y correspondantes. On peut poser la question : quelle est la valeur y pour x, disons, égal à 0.716 ?

Ici il ne s'agit pas de faire la "prévision théorique" (la valeur sur la droite de tendance), mais d'interpoler les valeurs voisines. Votre "programme" codé dans quelques cellules, doit - à partir de la valeur de x, récupérer les valeurs d'y en bas et en haut (correspondant aux x en bas et en haut : 0.7 et 0.8), et de trouver la réponse. Pour notre exemple, avec x=0.716, la réponse est 0.5570807373.

Commencez par la réponse à la question comment trouver les lignes "entre lesquelles" se trouve la valeur de x.

En utilisant les formules que vous avez découvertes, interpoler les données pour x entre 1.3 et 1.7 avec le pas 0.02 .

Certains avaient des problèmes concernant l'interpolation. J'explique la méthode. Il s'agit de trouver $y$ en fonction de $x$, sachant que le point se trouve sur la droite spécifiée par $x_0, x_1; y_0, y_1$. Or, les deux angles marqués sur le dessin sont identiques, donc :
$(y-y_0)/(x-x_0) = (y_1-y_0)/(x_1-x_0)$. Cela doit vous suffire pour trouver $y$ si $x$ et toute autre donnée sont connues.

La question est comment à partir d'un $x$ quelconque, trouver "ses" $x_0$ et $x_1$, la valeur "avant" et "après" dans la liste de données. Il faut regarder la colonne des données et se rendre compte qu'il s'agit de l'arrondi avec la précision 0.1 : ARRONDI.INF(10*x)/10 . (La même chose avec SUP). Ensuite, comment localiser les lignes où se trouvent ces nombres. On peut lancer la procédure de recherche (faites-le, si vous vous sentez fort), ou constater que la ligne vaut $10*x+2$, pour 0.7, c'est la ligne 9.


3. Recherche

Cette section est consacrée à la recherche des valeurs, numériques ou textuels, dans une feuille du calcul. Les dispositifs de recherche sont donc d'une très grande importance. Vous avez la commande de chercher et remplacer dans le menu, comme dans n'importe quel éditeur visuel. Mais nous voulons utiliser le tableur lui-même, comme une machine de recherche programmable. On commence par un exercice très simple.

Importez et ouvrez le document rechv.ods. Ceci est une "micro-base de données" avec 2 tables. Une table est une collection de valeurs composites ("records"), par ex. des individus, chacun possédant un nom, et une voiture d'une certaine puissance fiscale, et ce record occupe une ligne. La dernière colonne c'est le tarif de remboursement de leurs frais, qui est resté vide.

En toute indépendance de la première table, la seconde établit une relation entre CV et le tarif/km.

Exercice. Complétez cette première table. Bien sûr, pas à la main ! Les cases jaunes doivent contenir l'appel de la fonction recherchev, parametrée par le critère de recherche (le CV), la plage (matrice) où on stocke les données cherchables ; ici c'est le contenu de l'autre table, avec les CV et les tarifs, ensuite c'est la colonne où on récupère le résultat (les données correspondant aux critères de recherche, c'est toujours la première colonne de la plage). Finalement le dernier paramètre (appelons-le : mode) qui vaut 1 ou zéro (vrai ou faux) précise le "protocole" de la recherche, exacte en vrac (si zéro), ou peut-être inexacte dans une colonne triée dans l'ordre croissant.

Encore une fois, l'explication avec d'autres mots. Cette fonction

le dernier paramètre demande une explication supplémentaire. Si le mode est vrai (ou 1) ou omis, la colonne gauche de la plage doit être triée (numériquement ou alphabétiquement). Si on trouve la valeur cherché, avec mode=Vrai, on retourne la réponse correspondante. Sinon, la ligne au dessus est prise comme cible ; s'il n'y a plus rien, Calc retourne une "valeur-erreur" #N/D. Dans l'exemple toutes les valeurs sont bonnes ; si CV de Rubens était 13, cela aurait marché aussi. Par contre, la valeur 2 déclencherait une erreur.

Si le paramètre mode est faux, le tri n'est pas nécessaire, mais la correspondance exacte est exigée, sinon c'est #N/D.

Exercice. Relancez la recherche, avec mode=0. Dans la colonne F, dans les lignes concernées placez la formule qui laisse la case vide si tout va bien, et écrit "Calamité !" si la cellule correspondante de la colle D est erronée. Utiliser la fonction =ESTNA(refval), qui répond "Vrai", si la référence contient un #N/D (#N/A était en anglais...); Faux sinon.

Ajoutez à cela un peu de formatage conditionnel : le texte "Calamité" doit être jaune sur le fond rouge. Vous devez déclencher le formatage conditionnel non pas basé sur une valeur, mais sur une formule.

Retour