Excel
Excel comme outil de présentation, de calcul simple (avec action de recopie des
.... Au sujet des données suivantes proposant les sports favoris d'un groupe ...
part of the document
er la mise en forme d'un graphique
Créer des séquences utilisables par des élèves en utilisant un tableur
Construire un classeur dans lequel quelques cellules seulement sont modifiables
Simuler et dépouiller un phénomène aléatoire
Fonctionnalités du tableur exigibles en classe de première L
Source : 20/06/2001, Commission Ruget de réflexion sur l'enseignement des mathématiques.
Ce document propose de clarifier les compétences exigibles à propos des tableurs en classe de première L, dans la perspective d'un contrôle continu sur ordinateurs.
L'ouverture et l'enregistrement
Savoir :
- ouvrir un classeur, nouveau ou déjà enregistré dans un répertoire donné;
- choisir une nouvelle feuille de calcul ou reprendre une feuille éjà utilisée ;
- enregistrer son travail dans un répertoire donné son poste de travail.
La cellule
Savoir :
- localiser et désigner une cellule (par exemple , L2C4, A3, B1, (A,D),
) ;
- nommer une cellule ;
- utiliser les différents types de références à une cellule : références relatives, absolues, mixtes ;
- utiliser la référence à une plage de cellules, nommer une plage de cellules ;
- avant de saisir le contenu d'une cellule, reconnaître s'il s'agit d'un texte, d'un nombre, d'une formule, et le saisir dans une feuille de calcul ;
- interpréter la nature du contenu d'une cellule déjà saisie (ligne d'édition) ;
- régler le format d'une cellule ou d'une plage : date, heure, nombre, avec plus ou moins de décimales, pourcentages,
La présentation
Savoir :
- insérer une colonne ou une ligne ;
- régler la largeur d'une colonne ou la hauteur d'une ligne.
Les formules et les fonctions
Savoir :
- écrire dans une cellule une formule permettant le calcul de :
- sommes, produits, différences, quotients,
- puissances,
- racines carrées ;
- recopier une formule en utilisant les deux types de procédures : le "copier/coller" et les options du menu d'édition "copier/coller", collage spécial qui contribue à la compréhension des implicites sur le type de référence des cellules ;
- utiliser les fonctions qui permettent de calculer :
- la somme de valeurs d'une plage,
- la moyenne de valeurs d'une plage,
- l'écart-type de valeurs d'une plage,
- la médiane, le premier et le troisième quartiles d'une série statistiques ;
- trier une colonne de nombre par ordre croissant ou décroissant ;
- créer une table de nombres aléatoires ;
- exploiter un programme de simulation d'une expérience aléatoire en utilisant la fonction qui permet le recalcul des nombres aléatoires.
Les graphiques
Savoir :
- faire un graphique illustrant les données d'une zone en suivant un mode d'emploi donné ;
- changer les paramètres d'un graphiques (étiquettes, légende, grille, graduation des axes,
) en suivant un mode d'emploi donné.
Excel : de la calculatrice à la programmation
Le tableur comme calculatrice
Exercice 1 Exercice 2
Compléter le tableau suivant Compléter le tableau suivant
Excel comme outil de présentation, de calcul simple (avec action de recopie des formules) et de mise en forme :
Reproduire et compléter le tableau suivant (mais également disponible dans le fichier facture.xls)
Exercice 3
Reims, le***********Facture n°Montant HTMontant TTCQuantitéVidange moteur et niveau1Filtre à huile59,02**********************1Joint0,86**********************3Bidon Presti Diesel66**********************Contrôle eclairage2lave glace16,58**********************Pression pneusRemplacement filtre à gasoil et air1Filtre à air117,52**********************1Filtre à gasoil67,7**********************Main d'uvre1 MO128,75**********************0,8 MO139,55**********************Total HT**********************Tva19,60%***********Net à payer***********Exercice 4 Voici les températures relevées dans les principales villes de France le 1° janvier 1997 (source : Le Monde). (ces données sont disponibles dans le fichier meteo.xls)
Ajaccio158Grenoble1-3Pointe à Pître2911Biarritz4-1Lille-9-13Rennes-4-11Bordeaux-3-6Limoges1-7St Denis Réunion2923Bourges-6-12Lyon-4-7St Etienne-2-10Brest-2-7Marseille76Strasbourg-10-17Caen-7-12Nancy-10-18Toulouse1-1Cherbourg-2-9Nantes-6-11Tours-7-11Clermont-Fd-2-11Paris-8-9Nice86Dijon-6-10Pau2-2Fort de F.2920Perpignan73On 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.
Exercice 5
Reproduire la feuille de calcul ci-dessous (qui a été commencé dans alccol.xls) :
Les colonnes volume consommé et volume d'alcool seront automatiquement remplies suivant les valeurs données dans les autres colonnes.
De même les cellules quantité d'alcool consommé et taux d'alcoolémie seront calculées automatiquement. Pour cette dernière, le calcul provient de alcool/(masse*0,7).
Vous devrez adapter les formats d'affichage comme sur l'exemple et proposer un affichage final conditionnel :
Si le taux d'alcoolémie dépasse 0,5, il s'agit de l'afficher en blanc sur fond rouge
Si le taux est inférieur à 0,5, il devra être blanc sur fond vert.
Référencement absolu et relatif :
Exercice 6 Reproduire le tableau ci-après.
Vous devez proposer à l'utilisateur d'entrer un entier entre 1 et 200.
Vous devez construire une feuille de calcul permettant d'afficher si ce nombre est un nombre premier ou non.
Vous pouvez dans un second temps n'autoriser en cellule B1 que des entiers entre 1 et 200 (menu DONNEES puis VALIDATION).
Référencement mixte (semi absolu ou semi relatif) :
Exercice 7
Reproduire le tableau suivant permettant de visualiser les triplets pythagoriciens
Excel comme outil de représentation graphique de données :
Exercice 8
Au sujet des données suivantes proposant les sports favoris d'un groupe d'élèves, utiliser l'assistant graphique pour obtenu un diagramme en bâtons représentant ces données.
Basket5 Cyclisme3Football6Natation1Ski2Tennis2Autres5
Exercice 9
Le but de cet exercice est de construire un fichier Excel permettant de visualiser l'influence des coefficients a et b dans le tracé de la droite d'équation y = ax + b (on imagine, bien entendu cette méthode valable pour d'autres fonctions, le trinôme en tête).
Cette capture d'écran suivante est à reproduire autant que possible :
Une première version peut être créée sans les barres de défilement en bas à droite de la capture d'écran.
Les coefficients a et b sont donnés au clavier dans les cellules D4 à D5.
Les bornes de l'intervalle de représentation sont données au clavier dans les cellules C8 à C9.
Les abscisses en dépendent et parcourent l'intervalle formé par ces valeurs.
Les ordonnées sont alors automatiquement calculées.
Le graphique permet alors de visualiser la droite.
Afin de pouvoir visualiser les effets des paramètres a et b, il faut empêcher Excel de recalculer automatiquement les bornes de la fenêtre graphique. Il faut enlever cette capacité à chacun des deux axes du graphique (menu contextuel bouton de droite - sur les axes : format de l'axe).
Les paramètres a et b peuvent être créés de manière plus interactive :
Demandez à faire afficher la barre d'outils formulaire et créez deux barres de défilement. Affectez alors une valeur liée à ces barres dans des cellules proches de la barre associée.
Construisez alors les paramètres a et b de manière automatique par rapport aux valeurs créés par les barres de défilement. (Sur l'exemple, les barres de défilement créent des valeurs entre 0 et 100 puis les trois paramètres prennent des valeurs entre -5 et 5).
Avec nos élèves :
A) Tableau croisé et graphique dans l'espace
Extrait de : France métropolitaine, septembre 2003, exercice de spécialité TES
Une entreprise fabrique deux produits E et F en quantités respectives x et y exprimées en tonnes, pour lesquelles le coût de production z est donné par z = x² + 2y² - 6x 4y + 13 où z est exprimé en milliers d'euros avec x([0;7] et y([0;7].
La surface représentant ce coût est donnée dans le repère de l'espace situé sur la figure ci-dessous :
1) a) Placer sur cette surface le point A d'abscisse 4 et d'ordonnée 6.
b) Donner graphiquement un encadrement d'amplitude 10 de la côte du point A.
c) Vérifier par le calcul.
Construire la représentation graphique donnée par le schéma ci-dessus.
B) Simulation en statistiques
Chasseurs et Canards
Arthur Engel propose dans Les certitudes du hasard l'exercice suivant :
10 chasseurs tirent aléatoirement, mais sans échec, sur 10 canards sauvages.
Combien de canards survivent en moyenne ?
1) Proposez une feuille de calcul permettant de simuler une première fois cette situation.
Répéter à plusieurs reprises cette simulation pour en dégager une estimation de la moyenne.
2) Construire une macro permettant d'effectuer une simulation.
C) Suites Récurrentes
Pour trouver le 10ème terme de la suite ( un ) définie par u0 = 0 et un + 1= EQ \F(1;2)EQ \b(un + EQ \F(3;un + 1)), on place dans A1 la valeur 0.
Utilisation de Outils ( Options ( Calculs pour fixer le nombre d'itérations puis il suffit de faire une référence circulaire sur la cellule en écrivant en A1 :
=1/2*(A1+3/(A1+1))
D) Résolutions d'équation
Première Méthode : On transforme l'équation en suite récurrente
Exemple : Résoudre EQ \F(1;x + 1) = x revient à rechercher la limite éventuelle de un + 1 = EQ \F(1;un + 1) en prenant, par exemple, u0 = 0.
Seconde Méthode : Utilisation du solveur d'Excel sur l'équation ln(x² + 1) + sinx = 1
Il faut tout d'abord installer la macro complémentaire (dans Outils ( Macros Complémentaires) Solveur.
En A1, on donne par exemple 0
En A2, on donne =ln(A1^2+1)+sin(A1)
On applique en A2 le solveur préalablement installé, on fixe la variable en A1, la valeur à atteindre , ici 1 puis Résoudre.
E) Calcul matriciel
Utilisation de la séquence de touche CTRL + SHIFT + ENTREE pour valider sur toute une zone.
Exemple 24 du document d'accompagnement des programmes de TES
Deux villes X et Y totalisent une population d'un million d'habitants. La ville X est plus agréable maris la ville Y offre de meilleurs salaires. 20% des habitants de Y partent chaque année habiter X pour avoir un cadre de vie meilleur et 5% des habitants de X partent chaque année habiter Y pour augmenter leur niveau de vie.
1) Sachant qu'en l'année 0, un quart des habitants sont en X, calculer la population de X et de Y au bout de 1, 2, 5, 10 ans.
2) Que se passe-t-il si on suppose que 99% des habitants sont initialement en Y ou en X ? Que la population est également répartie entre les deux villes (500 000 habitants dans chaque ville en l'année 0) ?
Que constate-t-on ?
Remarque : on suppose que l'échange décrit est constant pendant un certain nombre d'années, et que dans chaque ville, le solde naissances-décès est nul.
F) sur le regroupement en classes (les données sont dans le fichier course.xls et on pourra construire plusieurs feuilles de calcul pour répondre aux questions suivantes)
On a réalisé un test de course à pied pour les 35 élèves d'une classe de troisième durant 20 minutes.
Les distances parcourues par les élèves pendant ce laps de temps sont données en kilomètres.
3,1 - 4,0 - 2,1 - 5,2 - 3,3 - 3,5 - 4,1 - 4,6 - 2,4 - 3,3 - 3,9 - 4,1 - 5,1 - 2,5 - 2,9 - 4,7 - 4,9 - 4,2 - 2,9 - 3,4 - 3,6 - 3,7 - 3,7 - 4,3 - 2,7 - 4,4 - 4,1 - 4,3 - 3,7 - 3,6 - 3,7 - 4,7 - 3,9 - 3,7 - 2,8.
1) Calculer la distance médiane parcourue par les élèves de la classe.
Calculer la distance moyenne parcourue.
2) On regroupe ces valeurs en classes d'amplitude 0,5, la première classe étant [2;2,5[. Tracer l'histogramme de cette série. Calculer la distance moyenne parcourue de la série ainsi regroupée.
3) On regroupe à présent ces valeurs en classes d'amplitude 1, la première classe étant [2;3[.
Répondre aux questions du 2).
4) Comparer les résultats obtenus.
G) (extraits d'une activité pour une classe de 4ème/3ème mais qui concerne également le lycée à partir de la seconde)
1ère Partie
Construire une feuille de calcul permettant la simulation d'un dé à 6 faces et dans le but d'étudier les fréquences d'apparition des différentes faces comme sur la capture d'écran suivante :
2ème Partie
Construire une feuille de calcul permettant la simulation de deux dés à 6 faces et dans le but d'étudier les fréquences d'apparition des sommes de ces deux dés comme sur la capture d'écran suivante :
H) Problème d'optimisation (activité pour une classe à partir de la troisième)
Problème : Pour réaliser un décor, on veut fabriquer le motif ci-dessous constitué d'aluminium et de verre coloré de même épaisseur 0,2 cm.
La masse volumique de l'aluminium est de 2,7 g/cm3 et celle du verre est 2,6g/cm3.
Ce décor doit être suspendu mais la charge ne peut dépasser 10 kg.
Quelle est la dimension maximale que l'on peut donner à x ?
La partie en verre coloré est en noir sur le schéma ci-contre.
A l'aide du tableau, résoudre ce problème :
Ouvrir une nouvelle feuille de calcul
Conseil : donner des titres aux colonnes utilisées.
Dans la première colonne, donner des valeurs pour x.
Affiner la recherche en donnant des valeurs plus précises à x.
Représenter graphiquement les masses en fonction des valeurs de x (nuage de points) pour
en visualiser la solution.
Quelle réponse donnez-vous au problème ?
Solution mathématisée
En vous aidant des formules, écrire la masse totale en fonction de x.
Ecrire l'inéquation puis la résoudre.
I) Problème de recherche et de logique pour une classe de troisième ; codage
Sans papier, sans calculatrice, uniquement avec un tableur, résoudre le problème suivant (proposé dans le Rallye d'Auvergne 2000) :
TOP SECRET
On numérote de 0 à 25 les lettres de l'alphabet dans l'ordre habituel. Ainsi, à chaque lettre on attribue un nombre entier x, 0( x ( 25, et inversement.
Pour coder un mot, on remplace chacune des lettres, numérotée x, par une lettre obtenue de la façon suivante :
1) On multiplie x par 29.
2) On soustrait au résultat obtenu un multiple de 26 permettant d'obtenir un nombre y compris
entre 0 et 25.
3) On code par la lette de numéro y.
Décoder le message
LYLM HM ZAHHUM JMIR KYHHM
Consignes :
Lancez Excel
Ouvrez un nouveau fichier que vous enregistrerez sous le nom codage.
En cellule A1 et C1 écrivez votre nom (pour identifier votre travail lors de l'impression)
Entrez vos formules
Rédigez brièvement ce que vous faites.
Enregistrez et demandez l'autorisation avant d'imprimer.
J) la machine de Sir Francis GALTON
Une bille, lâchée au niveau du goulet, bute successivement sur les différents plots, ce qui lui donne un trajet aléatoire qui, finalement, aboutit dans l'une des cases notées A, B, C, D, E.
Si nous introduisons un grand nombre de billes dans l'appareil nous obtenons une série statistique.
Réaliser une feuille de calcul comme sur la capture d'écran suivante permettant de simuler 100 réalisations et d'en faire une représentation graphique.
K) La suite de Syracuse
Cette suite est ainsi définie, par récurrence, par
u0 est un entier naturel non nul et un + 1 = EQ \B\LC\{( EQ \A\AL(EQ \F(un;2) si un est pair;3un + 1 si un est impair))
Conjecture : Il semble que un finisse par atteindre la valeur 1 et prenne alors, de façon périodique, le valeurs 4, 2, 1
Vérifier cette conjecture pour 1 ( n ( 100 sur le tableur.
Suites hésitantes : Déterminer le rang du premier terme égal à 1 avec u0 = 27 ; 54 ; 73 ; 97 ; 129 ; 171 ; 231 ; 703 ; 871.
Terme maximal atteint : Déterminer la valeur maximale de un pour 1 ( u0 ( 50.
Complément : La phrase conjecturée a été vérifiée pour toutes les valeurs de u0 jusqu'à 5,5.1014 mais n'a jamais été démontrée ni infirmée.
L) Votre première macro ou une feuille de calcul classique
1) Vous devez appliquer la simulation de votre choix dans une première feuille de calcul Exemples, création de 100 ou 1000 ou
simulations de :
la répartition de la fonction ALEA() dExcel sur l'un des 10 chiffres
la somme de deux dés à 6 faces
le tir de 3 fléchettes dans une cible et calcul des points obtenus sur les trois tirs.
Vous pouvez considérer les zones avec les probabilités de les atteindre proportionnelles à leur aire.
2) Construire dans une seconde feuille les calculs de paramètres statistiques sur cette simulation ; un tableau contenant le minimum, le premier décile, le premier quartile, la médiane, le troisième quartile, le neuvième décile et enfin le maximum de la série.
3) Construire une macro réalisant les instructions suivantes :
Reproduction du tableau précédent, et construction de la boîte à moustache comme sur la figure ci-dessous :
Il reste à terminer lenregistrement de la macro, supprimer cette dernière feuille et enregistrer le fichier excel. (Vous avez en effet enregistré la macro et non pas le résultat final ; la macro étant toujours disponible dans ce fichier).
Pour le graphique correspondant à cette boîte à moustache, tracez par l'intermédiaire d'une courbe du type nuage de points, le chemin D'1D"1D1Q1Q'1Q'3Q"3Me"Me'Q'1Q"1Q"3Q3D9D'9D"9 et il ne reste plus qu'à placer les deux points correspondant aux valeurs minimale et maximale de la série.
La présentation sera soignée par la gestion de la forme des axes du graphique.
application 1 : Adéquation à une loi équirépartie (fichier distance.xls)
Lorsqu'on lance n fois un dé, chacun sait que les distributions f1, f2,
, f6 des fréquences obtenues des six numéros fluctuent autour de la distribution théorique EQ \F(1;6), EQ \F(1;6),
, EQ \F(1;6).
Considérons le nombre, noté d 2(distance statistique), correspondant à n lancers de dé
d 2 = n EQ \I\SU(i = 1;6; EQ \b(fi - EQ \S\DOWN1(EQ \F(1;6))) EQ \S\UP15(2))
Une réalisation de 1 000 simulations de 120 lancers de dé a permis de trouver les résultats suivants pour les valeurs de d 2 dans le tableau ci-contre.
1) Représenter l'histogramme de la série.
2) Représenter la courbe cumulative des fréquences.
En déduire la médiane de la série, la valeur des déciles d1 et d9, la valeur des vingtiles v1 et v19. (on montre que ces valeurs ne changent pas pour un grand nombre de réalisations).
3) Représenter la boîte à moustaches ayant pour extrémités v1 et v19.
4) Nous admettons qu'un dé donné est irrégulier de façon significative au seuil de 5% lorsque, pour 120 lancers, sa distance statistique d 2 dépasse v19.
a) En lançant 120 fois un dé, nous avons obtenu la distribution des effectifs des six numéros (13, 18, 11, 21, 27, 30).
Ce dé est-il régulier ?
b) Recommencer avec un dé personnel (ici Excel) et conclure.
Application 2 : Durées des règnes des rois de France depuis Hugues Capet (fichier regnes.xls)
1) Calculer la médiane, les quartiles, les déciles à l'aide de la fonction tri d'Excel.
2) Représenter la série à l'aide d'une boîte à moustache, puis interpréter la position de la médiane dans la boîte.
Pour les pros
Exercice 12
Voici ce que fait Excel (par l'intermédiaire de l'éditeur Visual Basic) lorsqu'il enregistre une macro ou une fonction. Si vous souhaitez dans un premier temps comprendre ce qu'il s'écrit dans Visual Basic, regardez les dernières fonctions, à vous d'en créer d'autres
Les fonctions nommées Public Static sont des fonctions qui existent déjà (celles-ci proviennent de la macro complémentaire nommée Utilitaire d'Analyse).
Dans MACRO, demander VISUAL BASIC EDITOR et enfin INSERTION MODULE pour créer vos programmes et fonctions.
' moyenne pondérée
'Calcule la moyenne pondérée des notes avec les coefficients, et tiens compte des absents
Function MoyennePond(Coefficients, Notes)
Dim SommeCoef As Single, SommeNotes As Single, i As Integer
i = 1
For Each Coef In Coefficients
n = Notes(i)
If IsNumeric(n) 0 Then
If n 0 Then
SommeCoef = SommeCoef + Coef
SommeNotes = SommeNotes + n * Coef
End If
End If
i = i + 1
Next Coef
MoyennePond = SommeNotes / SommeCoef
End Function
'nombre de valeurs strictement inférieures à un seuil
Function NbInf(Plage, Seuil)
Nb = 0
For Each C In Plage
If C.Value < Seuil Then Nb = Nb + 1
Next C
NbInf = Nb
End Function'nombres de valeurs dans un intervalle ouvert à gauche et fermé à droite
Function EffectifClasse(Plage, BorneInf, BorneSup)
For Each C In Plage
If C >= BorneInf.Value And C < BorneSup.Value Then Nb = Nb + 1
Next C
NbClasse = Nb
End Function
'écrit un intervalle ouvert à gauche et fermé à droite
Function EcritIntervalle(BorneInf, BorneSup)
EcritIntervalle = "[" & CStr(BorneInf.Value) & ";" & CStr(BorneSup.Value) & "["
End Function
' moyenne classe
'Calcule la moyenne d'une série donnée en classes et effectifs et tiens compte des valeurs manquantes.
Function MoyenneClasses(Effectifs, ValeurCentrale)
Dim SommeCoef As Single, SommeNotes As Single, i As Integer
i = 1
For Each Coef In Effectifs
n = ValeurCentrale(i)
If IsNumeric(n) 0 Then
If n 0 Then
SommeCoef = SommeCoef + Coef
SommeNotes = SommeNotes + n * Coef
End If
End If
i = i + 1
Next Coef
MoyenneClasses = SommeNotes / SommeCoef
End Function
' primalité2 Macro
' met en gras les nombres premiers et en italique les autres
Sub primalité2()
For Each x In Selection
n = x.Value
d = 2
i = 1
Do While n > 1
Do While n Mod d = 0
n = n / d
i = i + 1
Loop
If d = 2 Then d = 3 Else d = d + 2
Loop
If i = 2 Then
x.Interior.ColorIndex = 4
x.Interior.Pattern = xlSolid
x.Interior.PatternColorIndex = xlAutomatic
Else
x.Interior.ColorIndex = 2
x.Interior.Pattern = xlSolid
x.Interior.PatternColorIndex = xlAutomatic
End If
Next x
End Sub
Public Static Function pgcd(a, b)
r = a Mod b
While r 0
a = b: b = r: r = a Mod b
Wend
pgcd = b
End FunctionPublic Static Function ppcm(a, b)
ppcm = a * b / pgcd(a, b)
End Function
Entrez dans le graphique afin de modifier taille, couleur, forme du graphique (camembert,
) et trouvez ainsi le moyen de construire des histogrammes.
EQ \F(x;2)
x
D'1
D1
D"1
Q1
Q'1
Q"1
Me'
Me"
Q'3
Q3
Q"3
D'9
D9
D"9
