TP de Tableur, 14.11.2013


Je n'ai pas oublié que vous auriez dû m'envoyer les exercices de la semaine dernière. Tout sera compté.


Exercices

Permutations aléatoires

la génération des nombres aléatoires de toute sorte (entiers, réels...) est très utile pour la simulation, pour les analyses statiqtiques, et plusieurs algorithmes. Les tableurs et les langages de programmation disposent de procédures qui servent à ça, par exemple Calc dispose de ALEA() dont chaque appel engendre un nombre réel entre 0 et 1. (Et Calc à chaque modification de la feuille engendre un nouveau). ALEA.ENTRE.BORNES(min;max) produit les nombres entiers entre min et max (inclus). (Rien à voir avec les fonctions Calc avec ces noms !)

Par ex. ALEA.ENTRE.BORNES(0;20) dans 10 cellules peut produire : 9, 0, 13, 2, 12, 14, 14, 4, 2, 8. Il n'y a pas d'ordre, mais les nombres "au pif" peuvent se répéter. Mais supposons que nous devons engendrer 21 nombres aléatoires entre 0 et 20 sans répétitions, par ex. 11 2 20 4 0 14 8 7 16 9 13 5 18 1 10 6 19 12 3 17 15.

Réaliser ce simple exercice.

Vous devez tester la qualité de cette solution (qui théoriquement n'est pas parfaite !...) Le test est assez misérable, mais doit suffire.

Variante

Au lieu de permuter à chaque fois la séquence 0, 1, ... 20, on permute la précédente, déjà permutée. Si l'algorithme est bon, peu importe la permutation d'origine. Comparer les résultats.

Tel que présenté ci-dessus, l'algorithme est manuellement fatigant : créer à la main 2×50 colonnes, lancer 50 fois le tri, ... bref, exécrable. Vous voyez que les langages de programmation sont considérablement plus sérieux que tous ces tableurs, au moins dans ce contexte.

Mais, peut-on optimiser le procédé? Si quelqu'un invente une bonne méthode, il aura un bonus supplémentaire.


Variantes, encore

Vous devez constater qu'une permutation n'est pas seulement une configuration, mais aussi une transformation. Concrètement, une permutation, disons,
6 19 10 4 7 0 15 17 2 14 8 16 13 5 20 1 3 11 9 12 18
peut être interprétée de deux manières, comme :

Implémenter l'enchaînement de ces transformations. Stocker la permutation-transformation dans une colonne à part, et ailleurs la colonne d'origine, ordonnée. Depuis cette séquence 0 1 2 ... construire celle ci-dessus : 6 19 10 ..., ensuite transformer le résultat, transformer le résultat suivant, etc. Est-ce qu'au bout d'une quinzaine [mieux, après 25, 15 peut être trop peu] de transformations on note quelques régularités?

Quelle variante est plus facile à faire en tableur, la directe ou l'inverse?

Essayez de faire le suivant, c'est la transformation inverse. La seconde colonne est la permutation-transformation :

0   6   15  1   19  12  13  5   0   6   15  1   19
1   19  12  13  5   0   6   15  1   19  12  13  5
2   10  8   2   10  8   2   10  8   2   10  8   2
3   4   7   17  11  16  3   4   7   17  11  16  3
4   7   17  11  16  3   4   7   17  11  16  3   4
5   0   6   15  1   19  12  13  5   0   6   15  1
6   15  1   19  12  13  5   0   6   15  1   19  12
7   17  11  16  3   4   7   17  11  16  3   4   7
8   2   10  8   2   10  8   2   10  8   2   10  8
9   14  20  18  9   14  20  18  9   14  20  18  9
10  8   2   10  8   2   10  8   2   10  8   2   10
11  16  3   4   7   17  11  16  3   4   7   17  11
12  13  5   0   6   15  1   19  12  13  5   0   6
13  5   0   6   15  1   19  12  13  5   0   6   15
14  20  18  9   14  20  18  9   14  20  18  9   14
15  1   19  12  13  5   0   6   15  1   19  12  13
16  3   4   7   17  11  16  3   4   7   17  11  16
17  11  16  3   4   7   17  11  16  3   4   7   17
18  9   14  20  18  9   14  20  18  9   14  20  18
19  12  13  5   0   6   15  1   19  12  13  5   0
20  18  9   14  20  18  9   14  20  18  9   14  20

Les ingrédients utiles sont :

Le tableau peut/doit être placé n'importe où, par exemple à partir de la ligne 3 du tableur, et la colonne D. Supposons que nous devons trouver l'élément sur la ligne 4 du tableau, colonne 5 (si la première est zéro) : là où la solution est 3.

Bien sûr, vous pouvez utiliser des expressions, genre ...LIGNE()+3;... dans les expressions. L'adressage de la colonne précédente est relatif : H si l'actuelle est I. Par contre, l'adressage de la colonne avec la transformation, doit être absolu.


Mois lunaire

Dans le dossier Feuilles, vous trouverez le document Lune13. Une partie de votre tâche est de compléter la première colonne, mais avec UNE FORMULE ; vous n'avez pas le droit de copier les données, seulement insérer une formule et la copier.

Ensuite vous devez formater conditionnellement les lignes selon ce modèle pdf. (Les deux premières lignes, avec le texte fixe sont formatées à la main).

La partie principale, calculatoire de l'exercice est de trouver – étant donnée une date quelconque, par ex. aujourd'hui, ou 8/04/13, etc. insérée dans une cellule, la date de la prochaine pleine lune (c'est très utile à ceux parmi vous qui ont la chance d'être loups-garous).

Ceci demande un peu de calcul, faites cela doucement, étape par étape. En cours vous avez vu la fonction RECHERCHEV. Ici il est plus utile d'utiliser une fonction similaire : EQUIV(val,plage), qui cherche une valeur (par ex. la date en question) dans une plage, dans la première colonne de cette plage. Le résultat retourné est le numéro de ligne de la valeur trouvée, ou – ce qui est notre cas – la ligne immédiatement au dessus, avec la valeur inférieure plus proche, à condition que les valeurs dans la plage soient triées, ce qui est le cas.

Ce qui nous intéresse ce n'est pas la ligne précédente, mais la suivante. Comment l'obtenir? Et, ATTENTION ! La fonction retourne le numéro de ligne à partir du début de la plage, non pas du tableur, donc, si, comme dans notre cas, le tableau commence à la ligne 3, il faut faire un petit calcul scolaire.

Ceci n'est pas la fin, car le résultat n'est pas forcément la pleine lune, pour 08/04/13, on obtient 10/04, la nouvelle lune. Il faut alors réfléchir un peu, vérifier le contenu de la colonne A, si c'est "Pleine lune", c'est bon, sinon ajouter 1, 2 ou 3 au numéro de la ligne, selon le cas : une expression SI(nlin="Pleine lune";0;SI(...)) etc., composite. Pas trop compliqué, mais demande un raisonnement discipliné. (Bien sûr, nlin correspond à la valeur calculée par EQUIV, plus la correction mentionnée ci-dessus).

Retour