Objectifs
La séance de TD consacrée au logiciel Excel permet une approche de la notion de tableur, outil complémentaire et très courant des logiciels de traitement de textes et de gestion de fichiers. Les objectifs principaux qui guident le déroulement de l’atelier Excel sont les suivants :
iques
14 Dessiner, mettre en pages
15 Formules matricielles
Plan des exercices
Durée approximative : 1h ( de 1h à beaucoup plus... selon affinités)
1. CALCULS FINANCIERS
1.1. Compte bancaire
1.2. Intérêts composdepuis le début de l’année signifie : ajouter au nombre de jours écoulés, déjà calculé pour le mois précédent, le nombre de jours du mois en cours. Les formules employées pour ce calcul sont alors :
En fait la formule =C2+B2 introduite en C3 a la même structure pour les mois suivants. Les références B2 et C2 utilisées en C3 signifient
C2 : la cellule au dessus de C2
B2 : la cellule à gauche de C2
On dit alors que ces références B2 et C2 sont relatives à la cellule C3.
En C4, ces références sont B3 et C3, en C5 elles sont B4 et C4, etc.
Excel peut automatiquement étendre sur une plage de cellules une telle formule comportant des références relatives, par les commandes “Recopier…” du menu Édition :
- directement dans le menu Édition
- par des manipulations à la souris, en tirant sur toute une plage le cadre d'une cellule active :
recopie incrémentale, selon une progression arithmétique
ou recopie d'une formule
7.2. Exercice : recopies.
Sur une nouvelle feuille de calcul
a- Activer la cellule C8, y entrer la formule =C7+B8+1 (en cliquant sur C7, sur B8, en tapant +1), valider. On obtient 1.
b- Sélectionner la plage de cellules allant de C8 à C17, en partant de C8. La cellule C8 est active c’est à dire que sa référence apparaît en haut à gauche de l’écran, et qu’elle est entourée d’un rectangle gras. Dans le menu Édition choisir Recopier vers le bas (on obtient les valeurs 1 à 10). Observer les formules crées en C9, C10 etc. (en cliquant sur chacune des cellules) : la recopie a conservé la position relative des références.
c- Sélectionner la plage de cellules allant de C8 à F8, la cellule C8 devant être active. Dans le menu Édition choisir Recopier à droite (on obtient les valeurs 1 à 4).
d- Sélectionner la plage de cellules allant de C8 à B8, en partant de C8.
Choisir dans le menu Édition Recopier à gauche.
e— Sélectionner la plage de cellules allant de C8 à C2, en partant de C8. Puis choisir Recopier vers le haut. On obtient :
7.3. Exercice : erreurs ######### et #NOMBRE!
Calculer les factorielles des entiers :
1- en A1 placer 1, puis en A2 placer 2
2 - sélectionner A1 et A2, étendre la sélection sur 200 lignes : l’on crée ainsi la liste des entiers de 1 à 200
3- en B1 placer 1 ( parce que c'est 1! )
4- en B2 placer la formule =A2*B1 ( parce que 2! = 2 * 1! )
5- recopier la formule B2 vers le bas sur 199 lignes : les facorielles sont calculées
6- réduire la colonne B pour qu’elle ne contienne plus que 3 ou 4chiffres : des ### apparaissent à la place des nombres trop grands pour être affichés
7- élargir la colonne B :
- à partir de 15! la factorielle dépasse les entiers représentables sur le PC, la valeur est affichée en flottant
- à partir de 171!, la factorielle dépasse les flottants représentables sur le PC (10306), la valeur n’est plus calculable
7.4. Exercice : somme des entiers de 1 à 10
Sur une nouvelle feuille calculer la somme des 10 premiers entiers :
1- pour lister les 10 premiers entiers, placer en B1 la formule =A1+1 (cliquer sur A1…), puis la recopier à droite, sur 9 cellules ;
2- lister les sommes successives : placer en B2 la formule =A2+B1, puis la recopier à droite sur 9 cellules.
7.5 Exercice : multiplication par 8
Sur une nouvelle feuille établir la table de multiplication par 8 :
1- Placer en A1 la valeur 8
2- Placer en A2 la formule =A1 (en cliquant sur A1...), valider
3- Recopier vers le bas cette formule
4- Placer en B1 la valeur *, valider
5- Placer en C1 la valeur 1, valider
6- Placer en D1 la valeur =, valider
7- Placer en E1 la formule qui va donner le résultat de la multiplication 8*1
8- Pour la colonne B, recopier vers le bas la formule B1
9- Pour la colonne C, entrer en C2 la formule =C1+1, la recopier vers le bas
10- Pour les colonnes D et E, recopier vers le bas la première ligne. Pour cela sélectionner le plage allant de D1 à E10, puis recopier vers le bas. L'on peut ensuite ajuster la largeur des colonnes : placer le curseur sur le trait entre les titres de deuxcolonnes et faire glisser
Enfin modifier la valeur en A1 pour construire une autre table de multiplication.
8. RÉFÉRENCES ABSOLUES, NOMS
Les références relatives permettent la construction de grands tableaux par recopie des formules. Les références relatives sont donc proposées “en standard” par Excel, mais elles ne couvrent pas l’ensemble des besoins.
8.1. NÉCESSITE DES RÉFÉRENCES ABSOLUES : Exemple du calcul d'intérêts composés.
Clodimir a placé ses économies, 1000€, sur un livret d'épargne, rémunéré à 3,5% l'an. L'an prochain il aura donc 1000+1000x0,035 :
Cette somme de 1035€ obtenue en fin d'année 1 portera ses intérêts durant l'année 2. A la fin de cette année 2, il aura sur son livret le capital 1035€ plus les intérêts de l'année 1035x0,035. Et ainsi de suite...
année capital au début intérêts nouveau capital
de l'année en fin d'année
0
1000
1000x0,035=35 1000+35=1035
1 1035 1035x0,035 1035+1035x0,035
2 1035+1035x0,035 (1035+1035x0,035)x0,035 ...
…
Le calcul se faisant donc toujours avec les mêmes règles que pour la première année, il suffit de recopier vers le bas les formules établies pour cette première année, contenues sur la plage A5:B5. Ceci conduit à :
L'erreur vient de la référence au taux.
Dans la formule en B5, la référence à B1 signifie "4 cellules au dessus". La recopie vers le bas a conservé cette référence relative "4 cellules au dessus", ce qui correspond successivement à B2, B3, etc. La référence au taux ne doit donc pas être relative, elle doit être une référence absolue, invariante par recopie.
8.2. UNE RÉFÉRENCE ABSOLUE SE NOTE PAR DES SIGNES DOLLAR : $B$1
Pour obtenir cette notation à la création de la formule il faut insérer des $ dans la formule, directement en les frappant au clavier :
• lors de l'élaboration de la formule cliquer normalement sur la cellule à référencer : une référence relative (sans $) s'inscrit dans la formule;
• puis insérer les $ : $B$9..
Le tableau rectifié se présente maintenant sous la forme :
Exercice : Sur une nouvelle feuille effectuer le calcul décrit ci-dessus.
8.3. NOMMER UNE CELLULE
Mais il est encore plus judicieux de repérer la cellule où se trouve le taux d'intérêts par un nom :
Pour associer le nom "taux" à la cellule $B$1
- activer cette cellule
- dans le menu "Insérer" choisir la commande "Nom - Définir "
- la fenêtre de définition de noms apparaît, avec la référence $B$1 déjà en place; entrer le nom "taux" et valider
8.4. Exercice : nom.
Modifier le tableau créé en 8.3. pour utiliser un nom.
9. RÉFÉRENCES MIXTES
9.1.
Une RÉFÉRENCE ABSOLUE est INDÉPENDANTE DE LA POSITION DE LA CELLULE ACTIVE
Une RÉFÉRENCE RELATIVE est DÉPENDANTE DE LA POSITION DE LA CELLULE ACTIVE
Une référence à une cellule, comme "B1", comporte une référence à la ligne, ici 1, et une référence à la colonne, ici B. Ces deux références à la ligne et à la colonne peuvent être relatives ou absolues, indépendamment l'une de l'autre.
Ainsi B1 est une référence relative
$B$1 est une référence absolue, invariante par recopie
$B1 est une référence mixte : relative pour la ligne, absolue pour la colonne
B$1est une référence mixte : absolue pour la ligne, relative pour la colonne
Références relatives et références absolues sont des notions liées à la recopie des formules.
9.2. Exercice : références mixtes.
Manipuler des références mixtes :
Entrer la valeur 1 en tête des lignes 1 à 4;
En B1 placer une référence relative =A1*2; la recopier vers la droite et contrôler le résultat;
En B2 placer une référence absolue à la colonne et relative à la ligne; recopier;
En B3 placer une référence relative à la colonne et absolue à la ligne; recopier;
En B4 placer une référence absolue; recopier et contrôler.
9.3. Exercice : la table de Pythagore.
Soit à établir la table de Pythagore, donnant les produits des nombres :
Chaque cellule contient le produit du nombre en haut de la colonne par le nombre en tête de la ligne.
Essayer d'établir la table de Pythagore. Si la difficulté s'avère trop importante, utiliser les indications complémentaires données ci dessous.
Facultatif : Indications pour établir la table de Pythagore :
En B2 la formule comporte un produit du contenu de B1 par le contenu de A2.
Si l'on y place =B1*A2, la recopie à droite n'est pas possible : la référence à A2 doit être absolue quant à la colonne A. On écrit donc =B1*$A2. Vérifier alors que la recopie à droite donne un résultat correct.
Si en B2 l'on place =B1*$A2, la recopie vers le bas n'est pas possible : la référence à B1 doit être absolue quant à la ligne 1. On écrit donc =B$1*$A2. Vérifier que la recopie vers le bas donne un résultat correct.
On sélectionne alors la plage B2:F6 (la cellule B2 où est placée la formule étant active) et l'on recopie à la fois vers la bas et à droite :
NB: si en A1 on place une autre valeur que 0, le recalcul automatique permet d'obtenir d'autres portions de la table de Pythagore.
10. FORMATS
10.1. UN FORMAT MODIFIE LES VALEURS AFFICHÉES
Ce qui apparaît à l'écran n'est pas directement le contenu des cellules, mais plutôt une image de la valeur calculée “filtrée” par un format. Par exemple avec un format standard si on entre la valeur de π avec quelques décimales 3,1415926535897932384626433 Excel n'en affichera qu'un nombre limité, 15 par exemple, ce qui ne veut en aucun cas dire que la valeur mathématique de π n'a que 15 décimales : une valeur affichée est généralement une approximation.
Dans une cellule on peut placer une valeur ou placer une formule calculant une valeur. Un format précise la manière dont la valeur sera vue sur l'écran, mais n'affecte jamais la valeur.
10.2. DIFFÉRENTS FORMATS
- Le format Standard laisse Excel décider de la présentation.
- Les formats numériques comportent des signes 0 # et virgule pour préciser chiffre à chiffre la présentation à obtenir:
0 force l'affichage d'un chiffre,
# n'affiche le chiffre que s'il existe.
Un signe % indique que la valeur est un pourcentage.
Voici un tableau d'exemple de formats :
<
Ainsi ce qui est affiché à l'écran peut fréquemment être tronqué et même, dans des cas limites (formats "gag" et 2,718), n'avoir aucun rapport avec la valeur contenue dans la cellule.
Si la valeur formatée à affichée ne tient pas dans la largeur de la colonne, un signe ######## est affiché.
Les formats de date et d'heure précisent la forme désirée :
<
Pour définir le format d'une cellule, ou d'une plage de cellules, il faut
- sélectionner au préalable la ou les cellules,
- activer la commande "Cellule - Nombre" du menu "Format",
- introduire le format désiré, ou choisir l'un des formats pré définis.
10.3. Exercice : formats.
Utiliser des formats pour mieux présenter le tableau de l'exercice 7.1 sur les intérêts composés. En particulier présenter le taux avec un format de pourcentage :
10.4. ENCADREMENT, POLICE, JUSTIFICATION
Également dans le menu "Format" se trouvent des commandes pour améliorer la présentation de la plage de cellules qui a été sélectionnée au préalable :
* pour forcer la justification : à gauche, à droite, ou centrée ;
* pour définir le jeu de caractère utilisé, son corps, et son style ;
* pour encadrer, ou pour tracer des filets ;
* pour introduire un motif sur le fond de la cellule ;
* pour aérer le tableau. La taille des lignes et des colonnes peut aussi se modifier directement en faisant glisser les frontières, dans la ligne de titre de colonne ou la colonne de titre de lignes.
La commande Cellule du menu Format permet, à partir d'un texte débordant de la cellule
<
d'obtenir différentes mises en page :
— le renvoi à la ligne dans une même cellule (qui est possible manuellement en insérar des caractères Alt+F4) :
— une présentation non horizontale :
Il est possible de mettre mieux en valeur les résultats sur la feuille de calcul en modifiant
* le style , outils
* le corps : outils
* la justification : outils
* l'encadrement : outils
Exercice 9.2 :
Améliorer la présentation du tableau formaté précédemment en 9.1
NB : Excel propose de nombreux outils. Pour y accéder, il faut souvent personnaliser les barres d’outils.
10.5. DATES
Une date est enregistrée par Excel comme un “numéro de série” défini par le nombre de jours écoulés depuis le 1° janvier 1904. Seul un format approprié permet de la restituer à l'écran en forme humainement lisible…
<
Exercice :
Calculer le nombre de jours écoulé entre votre naissance et maintenant (la fonction =MAINTENANT() rend le numéro de série de l'instant où elle est calculée). Le transformer en heures, en secondes.
Calculer le jour de la semaine correspondant à votre date de naissance.
11. EXERCICE : le plein syouplait
On désire évaluer la consommation moyenne d’une automobile. Pour cela le conducteur a noté à chaque fois qu’il a fait le plein :
* la date,
* le volume de carburant acheté,
* le relevé du compteur kilométrique.
On pourra alors calculer, pour chaque plein :
* la distance parcourue,
* la consommation moyenne, exprimée en litres par 100 kilomètres,
* le kilométrage moyen parcouru chaque jour
NB: on présentera le tableau de la façon suivante (feuille de données) :
Exercice complémentaire :
Le plein est à chaque fois fait de façon approximative. Pour modérer ce biais, on calcule une moyenne mobile sur 3 pleins successifs.
(correction ci-dessous)
Consommation automobile — correction :
Les formules employées pourront être :
<
12. TRIER
Le document originespromo1A.xls
http://www.mines.u-nancy.fr/~tisseran/cours/Excel/TDExcel/originespromo1A.xls contient de passionnantes informations sur la vie des ex-taupins :
Sélectionner les données effectives, de la ligne 3 à la ligne 119 : pour sélectionner les lignes entières avec toutes leurs colonnes, cliquer sur le “2” en tête de la ligne 2 et glisser en maintenant le bouton enfoncé jusqu’au “128” de la ligne 128.
Puis dans le menu Données la commande Trier permet de modifier l'ordre de présentation des lignes sur la feuille.
On a cliqué la réponse « oui » à la question Ligne de titres, puis on a choisi le titre Option pour signaler que le tri par ligne sera fait suivant les valeurs croissantes de la colonne A.
Le résultat est un tri par option :
Un tri en colonne des villes présentera les données par ordre alphabétique de ville.
Nn tri avec trois clés est aussi possible pour classer les données par ville. A villes identiques par lycée. A lycées identiques par option :
12.1 Exercice : météo
Voici les températures relevées dans les principales villes de France le 1° janvier 1997 (source : Le Monde).
Ajaccio
15
8
Grenoble
1
-3
Pointe à Pître
29
11
Biarritz
4
-1
Lille
-9
-13
Rennes
-4
-11
Bordeaux
-3
-6
Limoges
1
-7
St Denis Réunion
29
23
Bourges
-6
-12
Lyon
-4
-7
St Etienne
-2
-10
Brest
-2
-7
Marseille
7
6
Strasbourg
-10
-17
Caen
-7
-12
Nancy
-10
-18
Toulouse
1
-1
Cherbourg
-2
-9
Nantes
-6
-11
Tours
-7
-11
Clermont-Fd
-2
-11
Paris
-8
-9
Nice
8
6
Dijon
-6
-10
Pau
2
-2
Fort de F.
29
20
Perpignan
7
3
(feuille de données)
On cherche à déterminer la ville de France ayant eu le plus grand écart de température entre le jour et la nuit.
1) Entrer dans un tableau les données pour la France. Il y aura trois colonnes : ville, minimum, maximum.
2) Dans une quatrième colonne, calculer l'écart de température pour chaque ville.
3) Sélectionner le tableau en entier, et trier selon le critère en colonne 1, en ordre décroissant.
13. GRAPHIQUES
13.1 GRAPHE D'UNE INTÉGRALE
Les graphiques sont un des points forts d'Excel. L'exercice proposé vise à la découverte de cet outil, sur un exemple simple de calcul numérique.
1— Tabuler la fonction entre 0 et a.
On prend un pas h, judicieusement choisi, placé en A1. Les valeurs de X sont mises en colonne B, les valeurs de f en colonne C. La valeur de a peut être mise en A2, et le nom "a" peut être utilisé (menu Sélectionner, ligne Définir un nom). L'opérateur puissance se note ^ (touche ^ puis espace).
2— Intégrer numériquement le fonction f(x) qui vient d'être tabulée.
On met en colonne D la valeur de l'intégrale, de 0 à nxh.
3— Tracer le graphe de la fonction et de son intégrale.
Pour ce premier essai de graphique, utiliser l'outil "assistant graphique". Cliquer sur son icône puis dessiner sur la feuille de calcul la zone de traçage du graphe.
13.2 Météo
En reprenant l’exercice 12.1 Météo :
4) Tracer l'histogramme 3D des minimum — maximum.
* Sélectionner les valeurs minimum - maximum. Activer l’outil graphique.
* Choisir le type de graphiques “ Histogramme 3D ”.
* Ne pas modifier la plage des données, déja fixée.
* Préciser les titres (du graphique, de chaque axe, de chaque série de données), si vous le jugez utile.
* Insérer le graphique sur la feuille.
5) Tracer le graphique radar des écarts.
13.3 Recensement 1999
Voici des résultats du recensement de 1999 concernant la Lorraine :
population
% de variation
REGION
2 308,1
+0,1
DÉPARTEMENTS
population
% de variation
AGGLOMÉRATIONS
population
% de variation
Moselle
1 022,8
+1,1
Nancy
330,5
+0,3
Meurthe et Moselle
712,8
+0,1
Metz
199,8
+3,5
Vosges
380,4
-0,5
Thionville
130,7
-1,3
Meuse
192,1
-2,1
Rombas
111,5
-0,5
Forbach
93,9
-4,9
Epinal
61,8
-0,5
Longwy
40,2
-2,6
(Source : Le Monde, 6/7/1999)
Quels graphiques élaborer pour visualiser au mieux l’importance et la croissance des grandes agglomérations ?
13.4 Camemberts et radars.
A quoi consacrez-vous vos loisirs ?
Etablissez-en le tableau chiffré, en nombre d’heures par semaine.
Par exemple :
Jeux vidéo
10
Jogging
6
Piscine
3
TV
8
Echecs
4
Drague
5
Musique
8
Tir
2
Puis dessiner les graphiques camembert et radar de la répartition des temps de loisir.
Ces graphiques pourront être inclus dans votre fiche d’activités jointe à votre CV.
13.5 La suprématie de la langue anglaise ?
Voici le nombre approximatif (février 1999) de personnes parlant chaque langue maternelle (en millions) :
Mandarin 726 Russe 158 Penjabi 60
Anglais 427 Japonais 124 Marathe 58
Espagnol 266 Alemand 124 Vietnamien 57
Hindi et Ourdou 233 Français 116 Tegulu 55
Arabe 181 Javanais 75 Turc 53
Portugais 165 Coréen 66 Tamoul 49
Bangali 162 Italien 65 Technocrate N.C.
Comment en faire un graphique ?
13.6 Un graphique peut être efficace pour faire dire ce que l'on veut aux statistiques.
Le Canard Enchaîné du 17/2/99 épingle Le Figaro qui démontre par des graphiques que les Etats Unis sont en bien meilleure santé économique que la France. Mais en y regardant de plus près, la démonstration est truquée !
14. DESSINER, METTRE EN PAGES
Excel possède des outils de dessin et de mise en page qui enrichissent et clarifient la présentation des tableaux.
Pour tracer des filets, la palette d’outils de bordures s’obtient en cliquant sur le bouton :
Pour tracer librement, cliquer sur l’outil dessin
donne accès à la barre d’outils de dessin :
Exercice : Tracer des filets dans le tableau météo (12.1)
Exercice : améliorer la présentation du graphique de loisirs (13.4) avec les outils de dessin.
15. FORMULES MATRICIELLES
Soit à faire une moyenne pondérée :
La formule en B3 sera évidemment =(B2*B1+C2*C1+D2*D1)/SOMME(B1:D1)
Si l'on rajoute un nombre important de valeur
la formule va devenir très pénible à construire.
L'emploi d'une formule matricielle, agissant en parallèle sur des plages de valeurs, va résoudre notre problème :
{=SOMME(B1:M1*B2:M2)/SOMME(B1:M1)}
Pour transformer une formule simple en formule matricielle, il suffit d'appuyer simultanément sur les touches MAJUSCULE CTRL ENTER au moment de valider. Voir l'aide en ligne d'Excel à ce propos.
Exercices proposés pour
l'APPRENTISSAGE DU TABLEUR Excel :
Sommaire
Les exercices étant indépendants les uns des autres peuvent être choisis dans n'importe quel ordre.
1. CALCULS FINANCIERS
1.1. Compte bancaire
Dans ce premier exercice simple et utile vous serez guidé pas à pas pour construire la feuille de calcul. Excel est parfaitement adapté à ce type de calcul de gestion.
Notions : fonctions SOMME et SI, mise en page, recopie de formules avec références relatives.
1.2. Intérêts composés : calcul itératif
Exercice simple d’itérations.
Notions : progression géométrique : références relatives, recopie.
1.3. Intérêts composés : calcul direct
Exercice simple.
Notions : noms, recopie.
2. CALCULS NUMÉRIQUES
2.1. Triangle de Pascal
Le calcul plus simple en Excel qu'en Pascal ou en C. De façon générale, la programmation du calcul des suites récurrentes se réalise bien avec Excel. Excel est aussi un outil de calcul numérique efficace pour certaines classes de problèmes (suites récurrentes, séries, équations différentielles,…).
Notion : recopie de références relatives dans un tableau.
2.2. Calcul de e par son développement en série
Calcul de suites récurrentes. Exercice facile.
2.3. Calcul de pi par la méthode de Viète
Calcul par une double suite récurrente, peu connue. Exercice simple.
2.4. Racine carrée par la méthode de Newton
Calcul d'une suite récurrente.
Notions : recopie de références relatives et absolues.
2.5. Calculs du nombre d'or
Plusieurs calculs ingénieux, menant tous au nombre d'or, vous sont proposés. Comme ils se ramènent à des suites récurrentes, l'exercice n'est pas difficile !
2.6. Moulin à nombres
Une manip toute simple.
3. SIMULATION
3.1. Joyeux anniversaires
36 personnes ont-elles toutes des dates d'anniversaire différentes ?
3.2. “Météo”
Un modèle markovien simpliste à expérimenter.
3.3. Impôts
A partir de l'explication du système d'imposition directe, créer la feuille de calcul permettant des simulations.
4. GRAPHIQUES
4.1. L’étalon monétaire international
Quelques données en vrac ? Un graphique et c'est plus clair !
4.2. Graphique scientifique : sinusoïde bruitée
Un graphique simple mais spectaculaire.
4.3. Suite de Syracuse
Exercice d'arithmétique expérimentale, aidé par l'outil graphique d'Excel.
4.4 Pyramide des âges
Exercice plus difficile de présentation d'un graphique.
5. BASES DE DONNÉES
5.1. Base de données
Excel possède des possibilités élaborées de traitement de bases de données. L'exercice vise à une première approche.
6. CALCUL ITÉRATIF : Valeur cible
6.1. Méthode de Newton pour le calcul d’une racine
6.2. Equations du nième degré
6.3. Un nombre est-il premier ?
6.4. Les isopérimètres d’Archimède
6. CALCUL NON NUMERIQUE
7.1. Le nombre de Kaprekar
Correction des exercices
1. EXERCICES DE CALCULS FINANCIERS
1.1. COMPTE BANCAIRE
Calculs financiers — Compte bancaire
Dans ce premier exercice simple et utile vous serez guidé pas à pas pour construire la feuille de calcul. Excel est parfaitement adapté à ce type de calcul de gestion.
Notions : fonctions SOMME et SI, mise en page, recopie de formules avec références relatives.
Afin de tenir à jour un compte bancaire, on se propose d'utiliser un tableur. Les différentes opérations sont mises dans un tableau, à raison d'une opération par ligne, avec leur date et leur montant. Ceci peut se présenter sous la forme suivante :
1/ entrée des opérations bancaires.
Dans un premier temps nous allons entrer les différentes opérations, leur date dans la colonne A et leur libellé dans la colonne B. Le montant de l'opération est placé en colonne C s'il s'agit d'une opération débitrice, en colonne D s'il s'agit d'une opération créditrice.
Les totaux seront calculés par une formule de sommation, comme nous le verrons dans l'étape suivante.
2/ calculs de débit et de crédit.
Plaçons nous dans la cellule correspondant au total du débit, en C16.
Une formule commence toujours par un signe =. Excel possède une fonction prédéfinie SOMME. Elle s'utilise comme toutes les autres fonctions, sous forme =SOMME(ref:ref). Pour entrer la formule nous pouvons frapper directement le texte "=SOMME(" ou bien cliquer sur l'icône de l'outil de sommation, puis sélectionner la plage de cellules à sommer C5:C15.
Pour le calcul du total du crédit, nous allons recopier cette formule (en C15), qui est en références relatives), à droite (en D15).
3/ calculs du solde final.
Nous avons maintenant les totaux des débits et crédits, il ne nous reste plus qu'à calculer le nouveau solde du compte. La formule de calcul du nouveau solde en ligne 17 fait la différence entre les totaux des opérations de débit et de crédit. Évidemment si le solde est positif il figure en colonne Crédit, et en colonne Débit s'il est négatif : dans ces cellules la valeur à afficher est conditionnée par son signe, nous utiliserons la fonction SI.
Ainsi en C17 nous avons =SI(C16>D16;C16-D16;"") et en D17 la formule complémentaire.
4/ libellé final.
En B17 nous plaçons le libellé "solde débiteur" ou "solde créditeur", suivant le cas. Quelle est donc la formule =SI(… à employer ?
5/ présentation.
Pour une meilleure lisibilité du tableau, nous pouvons préciser le format d'affichage des valeurs dans les colonnes débit et crédit, aussi mettre en gras le solde final et en italique le solde initial.
Enfin nous gagnons aussi en lisibilité à encadrer les plages de cellules pour mettre en valeur la structure du tableau.
6/ sauvegarde et impression.
Maintenant que nos calculs sont effectifs, nous désirons les conserver.
• Au préalable nous aurons mis en page la feuille de calcul : menu Fichier, ligne Mise en page.
• Sur disque nous sauvegardons la feuille de calcul, menu Fichier, ligne Enregistrer sous, en positionnant l'enregistrement sur la disquette de sauvegarde des documents.
• Sur papier nous imprimons le tableau : menu Fichier, ligne Imprimer.
7/ modifications.
Modifions une valeur et observons les totaux et solde. Ceux-ci sont automatiquement remis à jour. De même supprimons ou ajoutons une ligne d'opération bancaire (pour insérer : menu Fichier, ligne Insérer). Tout est alors recalculé.
1.2. INTÉRÊTS COMPOSÉS : CALCUL ITÉRATIF
Calculs financiers — intérêts composés : calcul itératif
Exercice simple d’itérations.
Notions : progression géométrique : références relatives, recopie.
Le club investissement d'une grande école vient de prendre son bénéfice. Et il est coquet ! Le club envisage de prêter cette somme à l'association des élèves pour financer un voyage promo sur la station spatiale. L’association rémunérera annuellement cette somme à un taux d’intérêts composés T.
Dans un emprunt les intérêts se calculent à l'échéance de chaque période, ici annuelle. Si le prêt est à t%, avec un capital c, les intérêts au bout d'un an se montent à c x T ; le montant dû par l’emprunteur est c + c x T
somme qui devient le nouveau capital prêté pour l'année suivante. Cannée = Cannée-1 ^ (1+T)
On demande d'établir le tableau de l'accroissement du montant dû) pour 12 ans.
1.3. INTÉRÊTS COMPOSÉS : CALCUL DIRECT
Calculs financiers — Intérêts composés : calcul direct
Exercice simple.
Notions : noms, recopie.
Dans un emprunt à mensualités constantes de C euros au taux de T% l'an, sur N mois, la mensualité se calcule directement par la formule suivante, où t=T/12 :
On demande d’imprimer le tableau des remboursements.
L’exponentiation se note par l’opérateur ^ (touche muette ^puis un espace). On placera la formule =C*t/(1-(1+t)^-N) et l’on définira les noms C, t et N (menu Sélection, commande Définir Nom) sur les cellules appropriées.
2. EXERCICES DE CALCUL NUMÉRIQUE
2.1. TRIANGLE DE PASCAL
Calculs numériques — Triangle de Pascal
Le calcul plus simple en Excel qu'en Pascal ou en C. De façon générale, la programmation du calcul des suites récurrentes se réalise bien avec Excel. Excel est aussi un outil de calcul numérique efficace pour certaines classes de problèmes (suites récurrentes, séries, équations différentielles,…).
Notion : recopie de références relatives dans un tableau.
Afficher les quinze premières lignes du triangle de Pascal.
Pour cela on utilise la formule récurrente traditionnelle
formulation mathématique : Cnp = Cn-1p-1 + Cn-1p
formulation Excel, en B2 : =A1+B1
Indications :
On place 1 en B1, valeur de C01. La formule en B2, qui calcule la valeur de C11, sera recopiée vers le bas et vers la droite dans un tableau 15x15. Enfin on adaptera au mieux la largeur des colonnes.
2.2. CALCUL DE e
Calculs numériques — Calcul de e par son développement en série
Calcul de suites récurrentes. Exercice facile.
Calculer la base de logarithmes népériens, considérée comme limite de la suite ∑1/n! quand n tend vers l'infini. Vérifier la rapidité de la convergence.
Indications : On peut placer en colonne A les valeurs de n, en colonne B les valeurs de 1/n!, en colonne C les sommes partielles.
2.3. CALCUL DE pi : formule de VIÈTE
Calculs numériques — Calcul de π par la méthode de Viète
Calcul par une double suite récurrente, peu connue. Exercice simple.
Lors des exercices sur l’éditeur d’équations de Word nous avons mis en page une feuille d’exercices sur la formule de Viète. Il s’agit maintenant de calculer π par la formule de Viète :
Soit
alors Un tend vers π quand n tend vers l'infini.
Indications : On tabule n dans la colonne A, U dans la colonne B et V dans la colonne C.
Correction en fin de chapitre
2.4. RACINE CARRÉE
Calculs numériques — Racine carrée par la méthode de Newton
Calcul d'une suite récurrente. Notions : recopie de références relatives et absolues.
Calculer la racine carrée d'un réel positif donné. Pour cela on utilise la suite de Newton :
qui converge vers
Indications : Si on place en A1 a=5 et en A2 U0=1 on a alors une table de la suite U en colonne A, en définissant en A3 la formule =(A2+$A$1/A2)/2 recopiée ensuite vers le bas.
Expérimenter la feuille de calcul pour différentes valeurs de a et de U0.
Correction en fin de chapitre
2.5. Le nombre d’or
Calculs numériques — Calculs du nombre d'or
Plusieurs calculs ingénieux, menant tous au nombre d'or, vous sont proposés. Comme ils se ramènent à des suites récurrentes, l'exercice n'est pas difficile !
1— Vers 1509 le moine franciscain Luca Pacioli s’est intéressé aux moeurs des lapins :
• un premier couple de lapins (première génération) donne naissance à un unique couple de lapins à la deuxième génération, puis de nouveau à un unique couple à la troisième génération;
• de même, le couple né à la deuxième génération donne naissance à un couple à la troisième génération et à un couple à la quatrième génération;
• le processus se poursuit indéfiniment.
On demande de tabuler la suite U représentant cet accroissement de population, dans le cas U0=1, U1=1.
On montrera que le rapport entre deux populations de générations successives tend vers une limite F=(1+racine de 5)/2
Puis on vérifiera la formule de calcul direct Un=aF^n + b(-1/F)^n avec a=F/racine de 5 et b=1/(F racine de 5)
(Par exemple on mettra en colonne B les indices n, en B la valeur de la suite Un, en C Un/Un-1, en D la valeur obtenue par calcul direct.)
On vérifiera que le rapport Un/Un-p tend vers Fp.
2— Vérifier que F est aussi défini par la fraction infinie
3— Vérifier que F est aussi défini par la racine infinie
4— Que vaut le produit ci-dessous ?
NB: F est appelé nombre d’or, et Luca Pacioli est plus connu sous le nom de Fibonacci.
Un site parlant d'art et du nombre d'or :
www.rtsq.qc.ca/aiguillart/projet/rech/artmath/no_or/intropas.htmCorrigé en fin de chapitre
2.6. Un moulin à nombres
Calculs numériques — Itérations sur des nombres
Une manip toute simple...
Le moulin à nombres est une feuille de calcul qui va "mouliner" un nombre de la manière suivante :
- d'abord multiplier par 2
- puis retrancher 7
- et diviser le résultat par le nombre initial augmenté de 1
enfin recommencer avec le quotient obtenu.
Préparez la feuille de calcul pour mouliner 2001 fois.
Entrez le nombre 2001. Quel résultat s'affiche ?
Et en partant d'un autre nombre ?
D'après Le Monde, "affaire de logique, problème n°200", du 5 décembre 2000
3. EXERCICES DE SIMULATION
3.1. JOYEUX ANNIVERSAIRE !
Simulation — Joyeux anniversaires
36 personnes ont-elles toutes des dates d'anniversaire différentes ?
Le hasard des concours a réuni 36 élèves - ingénieurs. Ont-ils tous des dates d’anniversaire différentes ? Nous allons expérimenter cette question :
1) mettre en colonne 36 nombres tirés au hasard, compris entre 1 et 365, représentant les jours d’anniversaire, =1+ENT(ALEA()*364,999)
2) les trier.
Le tri ne fionctionne pas, car les formules sont recalculées après le tri des valeurs. Les coller dans la colonne adjacente, collage spécial "valeurs".
3) calculer dans une autre colonne les différences de deux nombres successifs
4) calculer le minimum de ces différences
5) calculer la réponse :
“oui” si le minimum des différences n’est pas 0
“non” si le minimum des différences est 0
La formule à employer est de la forme =SI( condition ; “ oui ” ; “ non ” )
Une condition s’écrit avec les opérateurs de ralartion = < > <> <= >=
Refaire dix fois cet essai (Option - Mode de calcul - Calculer maintenant), pour se persuader que la probabilité que deux personnes parmi 36 aient le même anniversaire n’est pas aussi petite que le sens commun le voudrait.
3.2. “MÉTÉO”
Simulation — “Météo”
............... Un modèle markovien simpliste à expérimenter.
Le temps qu’il fait en Manichéie, cette charmante contrée de l’est, ne peut être que de deux sortes chaque jour : il fait beau, ou il fait mauvais.
1) Quand il fait beau, la probabilité qu’il fasse beau le lendemain est égale à 2/3
2) Quand il fait mauvais, la probabilité pour qu’il fasse mauvais le lendemain est de 1/2
Aujourd’hui il fait beau. Déterminer la probabilité pour qu’il fasse beau dans n jours.
Et à long terme, peut on faire une prédiction ?
Indications : Soit Pn la probabilité qu’il fasse beau dans n jours et Qn la probabilité qu’il y fasse mauvais.
On a : Pn + Qn = 1 Pn = 2/3 Pn-1 + 1/2 Qn-1 Qn = 1/3 Pn-1 + 1/2 Qn-1
(Ce processus stochastique est une chaîne de Markov stationnaire)
3.3. “IMPOTS”
Simulation — “Impôts directs”
............... A partir de l'explication du système d'imposition directe, créer la feuille de calcul permettant des simulations.
1. Albert Nome-Hyme, jeune Ingénieur Cvil des Mines, perçoit un salaire brut de 31 205 € annuels. L'impôt sur le revenu est calculé sur le salaire net imposable. Les charges seront estimées par les pourcentages figurant sur
http://www.pratique.fr/tableaux/daf3112a.htm. 1. Quel est le salaire brut mensuel ?
2. Quel est le salaire net imposable mensuel ?
3. Quel est le montant annuel des charges du salarié ? et de l'employeur ? en pourcentage du brut ?
4. Quel est le montant annuel de l'impôt ? Faire une feuille de calcul en suivant les directives de la "notice pour...", ci dessous. On prendra soi d'établir le calcul de l'impôt de la façon la plus simple possible, adaptée uniquement à ce cas particulier, sans chercher à résoudre le cas général. On pourra aussi chercher de la clarté sur le serveur gouvernemental
http://www.service-public.fr. 5. Vérifier sur
http://www2.finances.gouv.fr/calcul_impot/2001/indexs.htm 6. Quel est le salaire menuel effectivement disponible après impôt ?
2. Etablir une feuille de calcul d'impôt pour chacun des cas suivants. On se limitera aux seuls calculs strictement nécessaires à l'obtention du résultat.
1. Veult Jean, ingénieur (célib., exp 5 ans dans les réseaux, ATM, Java, wireless, sat.) perçoit un salaire de 53 300 € net imposable.
2. Il vit maritalement avec Aï Clou Lê Mahon, institutrice titulaire, qui a un salaire net de 11 150 €.
3. Ils se marient.
4. Ils ont un enfant.
5. Et s'ils avaient eu un enfant sans être mariés ?
6. Ils ont un deuxième enfant.
7. Puis trois enfants.
8. Combien d'enfants devraient-ils avoir pour ne plus payer l'impôt ?
3. Les bas revenus.
1. Est-ce qu'un célibataire touchant le SMIC paye des impôts sur le revenu ? Le montant du SMIC (43,72 F de l'heure en juillet 2001) peut être consulté à
http://vosdroits.service-public.fr/particuliers/F2300.xhtml?&n=Emploi,%20travail&l=N5&n=Droit%20du%20travail%20dans%20l'entreprise&l=N442&n=R%C3%A9mun%C3%A9ration%20et%20salaire&l=N474 2. A partir de quel salaire mensuel paye-t-on des impôts si l'on est célibataire ?
3. célibataire avec un enfant ?
4. couple marié sans enfant ?
5. avec un enfant ?
6. avec deux ?
4. L'ingénieux Charles-Antoine De La Tuilière dirige une start-up dans un incubateur d'entreprises. Il est marié, a un enfant en maternelle. Il a touché 56 317 € net en honoraires, 44 140 € net en salaires, est aux frais réels (14 460 €), a versé 1 300 € à une oeuvre reconnue d'utilité publique non alimentaire, a versé 110 € à l'association des anciens de son école, a donné 150 € aux restos du coeur, les dividendes de son portefeuille d'actions lui valent 69 493 € de crédit d'impôt , il a dépensé 6 000€ de cotisations syndicales (syndicat affilié au MEDEF), verse une pension alimentaire de 5 479€ à ses enfants naturels, a versé 18 264 € à son majordome, fait garder ses enfants pour 6 849 € par une nourice agréée, a financé le parti politique X pour 3 730€.
1. Que paye-t-il comme impôt ?
2. A-t-il épuisé toutes les dispositions légales pour réduire son impôt ? On pourra consulter les déducations de revenu imposable à
http://www.pratique.fr/argent/impot/irpp/daf1405.htm et les réductions d'impôt à
http://www.pratique.fr/argent/impot/irpp/daf14061.htm. 3. Sinon que lui conseiller ?
Source : Le Monde - supplément fiscalité - 26 février 2000
4. EXERCICES SUR LES GRAPHIQUES
4.1. L'ÉTALON MONÉTAIRE INTERNATIONAL
Graphique — L'étalon monétaire international
............... Quelques données en vrac ? Un graphique, et c'est beaucoup plus clair !
A partir des données de l'article ci dessous (Libé, 29/8/97), on demande de tracer le graphique de la valeur d'achat du temps de travail dans les pays cités comparée à la moyenne mondiale, avec la présentation la plus claire possible.
4.2. Un graphique scientifique : SINUSOÏDE BRUITÉE
Exercice facultatif :
Graphique scientifique : sinusoïde bruitée
Un graphique simple mais spectaculaire.
1— Dans une nouvelle feuille de calcul, tabulez la fonction sin(x) sur deux périodes : une colonne pour x, une colonne pour sin(x).
Ayant sélectionné les valeurs des deux colonnes, activez une feuille graphique. N'oubliez pas que la première colonne contient les abscisses, valeurs X d'un tracé XY.
Pour obtenir un tracé plein écran, cliquez sur la case de zoom, en haut à droite de la fenêtre.
2— Ajoutez un bruit de fond à la sinusoïde:
en colonne C calculez une fonction aléatoire =K*ALEA();
en F1 entrez la valeur de K, nommez la cellule K (menu Sélection)
en colonne D sommez sinusoïde et bruit
tracez le graphique.
3— Organisez votre écran pour avoir les deux fenêtres actives visibles simultanément.
Modifiez la valeur du coefficient K, en F1: observez la modification simultanée du graphique lié à la feuille de calcul.
Correction en fin de chapitre
4.3. La Suite de Syracuse
Exercice facultatif :
Graphiques — Suite de Syracuse
Exercice d'arithmétique expérimentale, aidé par l'outil graphique d'Excel.
L’on définit la suite N par
* Soit N0 entier positif.
* Si Ni est pair alors Ni+1=Ni/2, sinon Ni+1=3Ni+1
Il s'agit d'une itération sur un nombre de départ. Si le nombre est impair on le gonfle, s'il est pair on l'amorti. On observe qu’au bout d’un certain nombre d’itérations la suite boucle sur < 4 2 1 >
Au cours des itérations, le nombre va osciller en croissant et décroissant, atteignant par instants une hauteur surprenante, mais il finit toujours par finir sur le nombre 1
Tracer le graphe des valeurs successives obtenues pour différentes valeurs de départ. Essayer en particulier le nombre 27.
Pour en savoir plus :
http://perso.wanadoo.fr/yoda.guillaume4.4. PYRAMIDE DES ÂGES
Exercice facultatif :
Graphiques — Pyramide des âges
Exercice plus difficile de présentation d'un graphique.
Les graphiques d’Excel ne permettent pas de tracer directement une pyramide des âges.
Mais on peut ruser : lorsque l’on trace sur le même graphique en barres deux séries, l’une négative, l’autre positive, l’on obtient le même aspect que celui d’une pyramide des âges. Les âges sont en ordonnées. Il ne reste plus qu’à afficher en abscisses des valeurs positives.
5. EXERCICES DE BASES DE DONNÉES
5.1. BASE DE DONNÉES
Base de données Excel possède des possibilités élaborées de traitement de bases de données. L'exercice vise à une première approche. Attention : vous faites partie des données !
NB : Les exercices suivants s’appliquent à Excel 5. Avec Excel2000, si les fonctionnalités sont identiques, les manipulations sont sensiblement différentes : voir dans l’aide Excel les paragraphes concernant “ Gestion des listes ”, “ Tri de données ”, “ Recherche de lignes répondant à des critères spécifiques ”.
Créer une base de données.
Une base de données sous Excel se présente comme une feuille de calcul où les lignes contiennent des fiches, et les colonnes contiennent les différentes rubriques de ces fiches. La première ligne de la base définir les noms des rubriques, les lignes suivantes contiennent les données :
Pour créer une base de données, commencez par remplir la ligne des noms de rubriques, puis renseigner sur les lignes suivantes ces rubriques pour les différentes fiches.
Vous pouvez aussi reprendre les données existantes, par exemple le document origine promo 1A
contenant des indications sur les taupes d'origine des 1°A.
Ensuite sélectionner (c'est à dire noircir) la zone qui correspond à la base de données, en y incluant les noms de rubriques. Choisir dans le menu Données l'option Base de données. La base peut ensuite être sélectionnée directement par Atteindre dans le menu Sélection.
µe
الأحد 13 يونيو 2021, 15:01 من طرف 
موضوع: ECOLE DES MINES DE NANCY Séminaire de rentrée en 1°A 
