Tableur EXCEL/Exercices/Exercice EXCEL Données
Exercices : Liste d'étudiants
modifierAfin d'assimiler tout ce qui a été appris lors de la leçon, nous vous proposons divers exercices courts qui vous permettront de mettre en pratique la leçon.
Ces exercices reprendront les titres que nous considérons comme les plus importants dans Excel.
Les exercices sont indépendants. Cependant, vous devrez suivre ces instructions pour pouvoir réaliser les différents exercices :
- Créer un nouveau classeur que vous nommerez "Liste étudiants"
- Sur une feuille, créez 10 colonnes : Numéro étudiant, Nom complet, Age, Date de naissance, Ville, Niveau (classe), Moyenne Semestre 1, Moyenne Semestre 2, Moyenne Année, Statut.
- Remplissez ces colonnes d'une trentaine de lignes. Prenez soin d'indiquer au moins deux niveaux différents, et effectuez la fonction =MOYENNE pour la colonne Moyenne Année. Remplissez la colonne Statut avec "Admis" si l'étudiant a eu une moyenne annuelle >=10, et "Défaillant" si l'étudiant a eu une moyenne annuelle <10. (Vous pouvez utiliser la fonction =SI. Aidez vous avec la leçon Tableur EXCEL/Formules)
- Triez la colonne "Age" du plus jeune au plus agé, sans étendre la sélection. Que remarquez-vous ?
- Revenez au tableau initial en faisant "retour"
- Triez la colonne "Statut" afin que les Admis apparaissent en premier, en prenant en compte les données des colonnes adjacentes. Que remarquez-vous ?
- Coloriez les cellules des "Admis" en vert, et les cellules des "Défaillants" en rouge.
- Triez dans cet ordre d'importance :
- La colonne Niveau du plus haut au plus bas (De Z à A);
- La colonne Statut en fonction de la couleur : le vert apparaît en haut, le rouge en bas.
- La colonne Moyenne annuelle de la plus haute à la plus basse.
- Triez la colonne "Age" du plus jeune au plus agé, sans étendre la sélection. Que remarquez-vous ?
Sélectionnez la colonne Age. Dans l'onglet Données, cliquez sur trier.
Sélectionnez "Continuer avec la sélection en cours".
Choisissez la colonne Age, et le tri dans l’ordre croissant (du plus petit au plus grand)
Cliquez sur OK.
On remarque que seule la colonne Age a été modifiée. Les autres colonnes n'ont pas bougées, les données se sont donc mélangées. Cela est du au fait que nous avons choisit "Continuer avec la sélection en cours".
- Revenez au tableau initial en faisant "retour"
Cliquez sur la flèche retour ou faîtes le raccourci ctrl+Z autant de fois que nécessaire.
- Triez la colonne "Statut" afin que les Admis apparaissent en premier, en prenant en compte les données des colonnes adjacentes. Que remarquez-vous ?
Sélectionnez la colonne Statut. Dans l'onglet Données, cliquez sur trier.
Sélectionnez "Étendre la sélection"
Choisissez la colonne Statut, et le tri dans l’ordre croissant (de A à Z)
Cliquez sur OK.
On remarque que cette fois toutes les colonnes ont suivi le changement. C'est parce que nous avons étendu la sélection.
- Coloriez les cellules des Admis en vert, et les cellules de Défaillants en rouge.
Sélectionnez les cellules à colorier en rouge et choisir la couleur à l'aide du remplissage. Effectuez cette opération pour les deux couleurs (rouge et vert)
- Triez dans cet ordre d'importance :
- La colonne Niveau du plus haut au plus bas (De Z à A);
- La colonne Statut en fonction de la couleur : Le vert apparaît en haut, le rouge en bas.
- La colonne Moyenne annuelle de la plus haute à la plus basse.
Dans l'onglet Données, cliquez sur trier.
Sélectionnez "Étendre la sélection"
Dans la boite de dialogue du tri, cliquez deux fois sur "Ajouter un niveau".
Pour la première ligne, choisissez la colonne Niveau, et le tri dans l’ordre croissant (de A à Z)
Pour la deuxième ligne, choisissez la colonne Statut, le tri en fonction de la couleur de remplissage de la cellule, puis choisissez que la couleur verte apparaisse en haut.
Pour la troisième ligne, choisissez la colonne "Moyenne année", et le tri dans l’ordre décroissant (du plus grand au plus petit).
Cliquez sur OK.
- Appliquez le filtre automatique sur l’ensemble des données
- Affichez uniquement les étudiants dont le numéro d'étudiant est strictement supérieur à 50
- Affichez uniquement les étudiants dont l'âge est strictement supérieur à 25 et strictement inférieur à 30
- Affichez uniquement les étudiants dont la ville est Paris ou Bordeaux
- Affichez uniquement les étudiants dont la ville est Paris et qui ont moins de 22 ans
- Affichez uniquement les étudiants dont le numéro d'étudiant est strictement supérieur à 50
- Cliquez sur la flèche située à côté de l'en-tête Numéro Étudiant
- Dans filtre numérique, cliquez sur «Supérieur à» et renseignez 50
- Affichez uniquement les étudiants dont l'âge est strictement supérieur à 25 et strictement inférieur à 30
- Cliquez sur la flèche située à côté de l'en-tête Age
- Dans filtre numérique, cliquez sur «Entre» et renseignez 25 pour «est supérieur à» et 30 pour «est inférieur à»
- Affichez uniquement les étudiants dont la ville est Paris ou Bordeaux
- Cliquez sur la flèche située à côté de l'en-tête Ville
- Décochez «(Sélectionnez Tout)» dans la liste déroulante
- Cochez Paris et Bordeaux dans la liste déroulante
- Affichez uniquement les étudiants dont la ville est Paris et qui ont strictement moins de 22 ans
- Cliquez sur la flèche située à côté de l'en-tête Ville
- Décochez «(Sélectionnez Tout)» dans la liste déroulante
- Cochez Paris dans la liste déroulante
- Cliquez sur la flèche située à côté de l'en-tête Numéro Étudiant
- Dans filtre numérique, cliquez sur «Inférieur à» et renseignez 22
- Affichez uniquement les étudiants dont le numéro d'étudiant est strictement supérieur à 50
- Triez les "N°Étudiant" par ordre croissant
- Vous remarquez qu'un même numéro étudiant a pu être appliqué à plusieurs étudiants
- Sélectionnez toutes les données
- Cliquez sur "Suppression des données" et cochez uniquement "N°Étudiant". Que remarquez-vous?
- Corrigez ce que vous venez de faire, et maintenant supprimez les lignes en doublons dans la base de données. Que remarquez-vous?
Attention: un numéro étudiant peut avoir été attribué plusieurs fois pour des étudiants différents.
- Sélectionnez la première ligne de données et appliquez un filtre.
- Filtrez "N°Étudiant" par ordre croissant
- Dans l'onglet "DONNEES", cliquez sur "Suppression des données"
- Cliquer sur "Etendre la sélection" puis OK
- Cliquez sur le bouton "Déselectionnez tout" puis cochez uniquement "N°Étudiant" => vous remarquez que toutes les lignes comportant le même numéro étudiant ont été supprimées. Cependant, cette manipulation a aussi supprimée des étudiants du tableau
- Appuyez sur Ctrl+Z pour revenir en arrière
- Dans "Suppression de données", cochez uniquement la colonne "N°Étudiant" et "Prénom" (et "Nom" quand il y en a pour plus limiter des erreurs de faux doublons), puis OK
- Le message suivant "x valeurs en double trouvées et supprimées. Il reste y valeurs uniques."
- Cliquer sur OK et vérifier que la ligne en doublon a été supprimée.
Attention: Si un "N°Étudiant" est utilisé pour deux étudiants différents, cette manipulation ne modifie pas les codes. Il est nécessaire d'attribuer des nouveaux codes aux étudiants.
Le but de cet exercice est que tous les étudiants soient admis
- Ne gardez que dix étudiants
- Renommez la colonne de la moyenne annuelle en "Année". Pour cela, cliquez droit sur l'en-tête puis sélectionnez "Définir un nom"
- Les étudiants sont admis si la moyenne annuelle est supérieure ou égale à 10
- En utilisant la fonction "Valeur cible", modifiez les moyennes du semestre 1 (ou 2) pour que la moyenne de l'année soit égale à 10 (valeur minimale pour être admissible
- Dans la colonne "STATUT", insérez la formule de condition =si(Année>=10;"ADMIS";"")
- Cliquez sur "Analyse scénarios" puis "Valeur cible"
- Dans "Cellule à définir", entrez les coordonnées de la cellule qui sera égale à la valeur à atteindre (valeur cible)
- Dans "Valeur à atteindre", entrez le nombre 10 (valeur minimale pour être admissible)
- Dans "Cellule à modifier", entrez les coordonnées de la cellule qui sera ajustée automatiquement par Excel selon le résultat souhaité (la valeur cible)
- Dans la colonne Numéro Étudiant autorisez uniquement les numéros à 3 chiffres.
- Entrez un message de saisie expliquant que les numéros étudiants doivent contenir 3 chiffres pour être valide. Cliquez sur une cellule de la colonne Numéro Étudiant.
- Entrez un message d'erreur de type avertissement avec pour titre "Numéro étudiant" et pour message "Les données saisies ne sont pas valides car elles ne contiennent pas 3 chiffres".
- Entourez les données non valides
- Corrigez les données non valides
- Essayer d'entrer un numéro étudiant non valide
- Sélectionnez les cellules de la colonne Numéro Étudiant. Cliquez sur validation des données. Dans options sélectionnez autorisez : longueur du texte, données : égale à 3.
- Cliquez sur message de saisi, puis tapez votre titre et votre message.
- Cliquez sur alerte d'erreur. Choisissez style : avertissement. Tapez votre titre et votre message.
- Cliquez sur validation des données, entourer les données non valides.
- Modifier les données pour que tous les numéros soient de 3 chiffres.
- Tapez un numéro à 4 chiffres. Cliquez sur annuler
- Faîtes apparaître la moyenne de chaque Niveau pour le Semestre 1, le Semestre 2 et l'Année, grâce à un sous-total.
- Utilisez un sous-total imbriqué pour calculer le nombre d'individus dans chaque classe (niveau). Conseil : Utilisez la colonne Statut pour ce sous-total. Cela aidera à la lecture.
- Calculer le nombre d'Admis et de Défaillant pour chaque niveau, à l'aide d'un sous-total imbriqué.
A la fin de l'exercice, tous les sous-totaux doivent être visibles
- Faîtes apparaître la moyenne de chaque Niveau pour le Semestre 1, le Semestre 2 et l'Année, grâce à un sous-total.
Sous l'onglet données, cliquez sur Sous-Total.
Dans la boîte de dialogue, sélectionnez à chaque changement de Niveau, utiliser la fonction Moyenne, pour les trois colonnes : Moyenne Semestre1, Moyenne Semestre 2 et Moyenne Année.
Cliquez sur OK.
- Utilisez un sous-total imbriqué pour calculer le nombre d'individus dans chaque classe (niveau). Conseil : Utilisez la colonne Statut pour ce sous-total. Cela aidera à la lecture.
Sous l'onglet données, cliquez sur Sous-Total.
Dans la boîte de dialogue, sélectionnez à chaque changement de Niveau, utiliser la fonction Nombre, pour la colonne Statut.
Décocher "Remplacer les sous-totaux existants"
Cliquez sur OK.
- Calculer le nombre d'Admis et de Défaillant pour chaque niveau, à l'aide d'un sous-total imbriqué.
Sous l'onglet données, cliquez sur Sous-Total.
Dans la boîte de dialogue, sélectionnez à chaque changement de Statut, utiliser la fonction Nombre, pour la colonne Statut.
Décocher "Remplacer les sous-totaux existants"
Cliquez sur OK.
À la fin de cet exercice, vous devrez obtenir ceci :
