Liste de données - Manipulations et tris

TRIS : Excel propose des outils pour trier, afficher et imprimer de grandes listes de données.

• Les tris peuvent fonctionner sur un ou plusieurs champs.
• Les filtres permettent d'afficher à l'écran une partie de la liste, en la filtrant sur un ou plusieurs critères.

Données : Télécharger les données (Dans ce fichier, ne pas travailler sur la feuille "original")

Manipulations sur la liste

• Affichage d'une liste en figeant la première ligne pour la rendre toujours visible
• Déplacement dans une liste
• Sélection de lignes, de colonnes dans une liste
• Impression d'une liste en réduisant le nombre de pages,
• Impression d'une liste en répétant la première ligne sur chaque page.

TRIS : Sur le fichier téléchargé, mettre en place successivement les tris suivants :

1. Tri par service (ordre alphabétique)
2. Tri par date de naissance
3. Tri par salaire, 
4. Tri par sexe et par agence
5. Tri par agence, par service et salaire.

A retenir : Pour déplacer une colonne, il faut la sélectionner, puis la déplacer par glissade avec la touche MAJ enfoncée.

Liste de données - Filtres simples

FILTRES : Excel propose des outils pour filtrer de grandes listes de données. Le filtre est une sélection de lignes dans une liste, sur la base d'un ou plusieurs critères de filtrage.

Données : Télécharger les données (Dans ce fichier, ne pas travailler sur la feuille "original")

FILTRES :  Sur le fichier précédent, mettre en place les filtres suivants et comptabiliser le nombre de lignes de réponses.

1. Afficher toutes les femmes du service commercial de Lyon
2. Afficher toutes les personnes dont le nom commence par la lettre D
3. Afficher toutes les personnes des agences de Province
4. Afficher toutes les personnes gagnant moins de 1500€
5. Afficher toutes les personnes gagnant entre 1000 et 2000€
6. Afficher toutes les personnes des agences de Lyon ou de Paris
7. Afficher toutes les personnes nées en 1970
8. Afficher les femmes de Lyon et les hommes de Paris

Quelques défis sur les formules

Calculs de primes commerciales

Une équipe de 4 commerciaux (John, Max, Jo, Bill, Averell) réalisent respectivement 15000, 18000, 9500, 11000 et 12000 € de chifre d'affaire.

• DEFI 1  : prime pour les meilleurs : Leur directeur dispose d'une prime de 5000 €, qu'il souhaite distribuer aux meilleurs. Il entend par "meilleurs" ceux qui ont fait mieux ou aussi bien que la moyenne de l'équipe. Pour ces "meilleurs", il répartit la prime de 5000 € entre eux, de façon proportionnelle à leur performance, c'est à dire de façon proportionnelle à l'écart qu'ils ont chacun fait par rapport à la moyenne. Ainsi si l'un d'entre eux avait fait 200 €  de mieux que la moyenne et si son collègue avait fait 400 € de mieux, alors ce dernier aurait obtenu une prime double.

• Notez que si tous les membres de l'équipe réalisent le même CA, alors aucune prime ne sera attribuée à quiconque.

• A vous de créer une feuille Excel, de créer un tableau avec les données et de mettre en place les calculs qui donneront les montants  de prime pour tous les membres de l'équipe. Vous pouvez utiliser plusieurs colonnes de calculs intermédiaires si nécessaire pour trouver le résultat demandé.

Voir la solution (PDF)

• DEFI 2 : prime pour les DEUX meilleurs
  On reprend les mêmes commerciaux avec les mêmes chiffres d'affaire. Le directeur dispose toujours d'une prime de 5000 €, qu'il souhaite distribuer aux DEUX meilleurs. La répartition des 5000 € pour ces deux gagnants se fera proportionnellement à leur chiffre d'affaire respectif.
  La recherche des deux meilleurs devra être automatisée. Il n'est pas question de trier la liste sur le CA et ensuite de s'interesser aux deux premiers de la liste triée.

• Notez que si tous les membres de l'équipe réalisent le même CA, alors aucune prime ne sera attribuée à quiconque.

• La aussi, vous pouvez traiter ce problème en utilisant des colonnes intermédiaires.

Voir la solution (PDF)

Calculs de base

Excel est un tableur, c'est à dire un logiciel pouvant faire des calculs sur les données saisies par l'utilisateur.


Dans cet article, nous allons voir comment Excel réalise les 4 opérations, la somme d'une plage de cellules, la moyenne, la recherche du minimum ou  du maximum d'une plage de cellules, la fonction SI, qui permet dans une cellule d'afficher l'un ou l'autre résultat suivant qu'une condition est vraie ou non, et enfin l'utilisation du $ dans une formule pour figer des cellules lors de la recopie de cette formule.

Ce sont ces opérations de base qui sont détaillées dans l'exercice qui suit.

• Faire l'exercice :  télécharger le fichier Excel de données
• Voir l'exercice terminé : télécharger le fichier Excel de résultats
• Disposer d'un mémento : télécharger la fiche de cours en pdf
• Savoir comment manipuler : visualiser la vidéo ci-dessous :

Différence entre modèle "Courbe et "Nuage de points"

Parfois le choix du modèle d'un graphique est loin d'être anodin, et peut entraîner une représentation complètement fausse des données. C'est ce qu'on va voir dans cet article.

Exercice

• Dans une feuille Excel, saisir le tableau de données suivant :

Noter qu'on ne dispose pas d'informations pour l'année 2007.

• Représenter alors graphiquement ce tableau, par deux graphiques distincts et séparés, qui afficheront chacun un ensemble de segments de droite reliant les différents points (année; CA). Vous ferez ainsi :
• un graphique de type "Courbes"
• un graphique de type "Nuage de points"
• La question est de savoir parmi les deux affirmations suivantes, laquelle est juste ... ?
1. D'après le graphique "Courbe", le PDG annonce que le CA chute depuis 2006, mais que depuis 2008 la chute est moins prononcée. C'est de bonne augure !   :-) :-) :-)
2. D'après le graphique "Nuage de points", le PDG indique que le CA chute depuis 2006 et que la chute a continué au même rythme jusqu'en 2009. On est mal parti !  :-(  :-( 

Quel est votre avis et votre argumentation ?

• RETENIR !  Le seul  type de graphique d'EXCEL qui permet de représenter les étiquettes comme un axe ordonné numérique est le graphique de type ... ... ... ...

Graphique de type RADAR

Ce type de graphique est trés utile pour apprécier d'un coup d'oeil la performance d'un produit. C'est en particulier utilisé dans les dossiers techniques de la Fnac pour faire des comparaisons de produits hi-tech.

Appareil PHOTO
Panasonic - Lumix DMC - G2  
    

 Panasonic - Lumix GH2


Plus l'enveloppe du graphique est large, meilleur est le produit !

Exercice à faire

• Dans une feuille Excel, représenter à l'aide d'un graphique radar, les données suivantes :

Autofocus = 3,2
Sensibilité = 3,3
Respect des couleurs = 4,5
Optique = 5
Définition = 2,5
Rapidité = 2

Vous devriez obtenir un graphique comme celui-ci :

Mise en forme conditionnelle

Cette fonction très efficace permet de mettre en valeur des informations particulières dans une feuille de calculs Excel.

Les mises en forme conditionnelles peuvent être très sophistiquées dès lors que vous maîtrisez les formules de calcul d'Excel, et l'utilisation des dollars dans les formules.

Données : Télécharger les données - Fichier résultat

Exercice : Dans le fichier téléchargé, répondre aux questions numérotées suivantes. Pour bien vous organiser, créer une nouvelle feuille pour chaque nouvelle question. Cette feuille sera une copie de la feuille "3255". Vous renommerez chacune des nouvelles feuilles avec un N° correspondant à la question à traiter.

Rappels :

• Copier une feuille : Utiliser CTRL Cliqué / glissé sur l'onglet de la feuille à copier
• Sélectionner une colonne, sans aller jusqu'en bas de la feuille !! Cliquer sur la cellule du haut et faire CTRL MAJ Flêche basse.

Pour commencer : Mettre en surbrillance :

1. Créer une copie de la feuille 3255 en une feuille que vous renommerez "1". Mettre en surbrillance les salaires supérieurs à 3000.
2. Dans une feuille nommée "2", mettre en surbrillance les salaires supérieurs ou égaux à 2500.

Continuer à créer une feuille pour chaque question et répondre aux questions suivantes.

3. Les dates de l'année 1970
4. Les salaires supérieurs à la moyenne des salaires
5. Les salaires inférieurs ou égaux à la moyenne des salaires
6. Utiliser un jeu de 3 icônes pour caractériser les salaires par rapport aux deux seuils de 1500 et 3000. Vous êtes libre sur l'appréciation des bornes.
7. Avec un fond de couleur, les 3 salaires les plus élevés.
8. Les salaires supérieurs ou égaux à une valeur saisie dans une cellule externe au tableau. La cellule externe sera par exemple la cellule K1, dans laquelle vous écrirez une valeur numérique qui servira de seuil à votre mise en forme conditionnelle.
9. Dans cette question, il n'y a pas de nouvelle feuille à créer.
   Cette question demande de changer des mises en forme que vous avez faites. Pour cela, il vous faudra sélectionner les cellules dont vous souhaitez changer la mise en forme, et aller dans le menu "Gérer les règles". Dans l'écran qui s'affiche, sélectionner la règle et cliquer sur le bouton "Modifier la règle".
• Revenir à la feuille "1" et modifier la mise en forme pour que cette fois ci cela concerne les salaires supérieurs à 3500.
• Revenir à la feuille "7", et changer franchement la couleur de mise en forme des salaires les plus élevés.
10. A nouveau sur une nouvelle feuille que vous nommerez "10", mettre en surbrillance avec un fond de couleur, les salaires extrèmes, c'est à dire le plus petit salaire et le plus grand salaire. Cette question est posée pour montrer que l'on peut enchaîner plusieurs mises en forme sur une même sélection.
11. Avec un fond de couleur, les salaires qui ne sont pas compris entre 2000 et 3000.

Ca se complique un peu : Mettre en surbrillance :

12. La colonne des noms correspondant aux femmes
    Il faut commencer par sélectionner les noms. Cette question ne se résoud pas par une régle prédéfinie.
    Donc, vous devez faire "Mise en forme cond."/ Nouvelle règle / Utiliser une formule ....
    Pour trouver la réponse, posez-vous la question suivante .: "Quelle est la condition que doit vérifier la cellule "Archambaud" pour qu'elle soit colorisée ? C'est cette condition qu'il faut écrire dans la zone de saisie.
13. Les lignes entières correspondant aux personnes de Lyon
    * Dans le cas de "lignes entières", il faut, au départ, sélectionner tout le tableau à l'exception de la première ligne contenant les en-têtes de colonne.
    * Le test concerne la cellule de l'agence.
    * Le résultat de la mise en forme est visible ci-dessous.

14. Les lignes entières des femmes gagnant au moins 2000 €
    => Indication : Dans le cas de deux conditions simultanées, il faut utiliser la fonction ET() qui s'écrit comme suit :          =ET(condition1;condition2)
15. La colonne des noms correspondant aux femmes de Lyon
16. Les lignes entières des femmes de Paris travaillant en comptabilité
    => Dans cette question, on passe à 3 conditions simultanées. Il faut savoir que dans la fonction ET(), on peut écrire jusqu'à 30 conditions.

Ca chauffe ! : Avec des formules variées ... et plutôt difficiles !

17. Les lignes des personnes nées en 1970
    * Indication : Pour l'écriture du test, utiliser la fonction ANNEE(). Cette fonction renvoie l'année de la date que vous lui fournissez. A vous de comparer cette année à 1970.
    Pour comprendre la fonction ANNEE, placer vous dans la cellule J2 et saisir la formule =ANNEE(E2). Cela vous montre comment fonctionne cette fonction.
18. Les lignes des personnes ayant leur anniversaire aujourd'hui. Il faut donc comparer les dates de naissance de la feuille Excel avec la date du jour où vous faites l'exercice.
    * La fonction aujourdhui() renvoie la date du jour. Notez qu'il n'y a pas d'apostrophes, et qu'il faut écrire les deux parenthèses bien qu'elles soient vides!
    * Pour résoudre la question, il vous faudra éventuellement modifier les donnéees pour que certaines dates de naissance de la feuille Excel aient les mêmes jour et date que la date à laquelle vous faites l'exercice.
    * La fonction MOIS(...) renvoie le mois d'une date que vous lui indiquez. Donc l'expression MOIS(AUJOURDHUI()) renvoie le mois de la date du jour.
    * En utilisant à bon escient les fonctions ET, AUJOURDHUI, MOIS, JOUR, vous devriez y arriver.
19. Les personne du service informatique de l'agence de Marseille et ayant au moins 50 ans.
    * La difficulté est due à l'écriture du test sur la date.
    Si vous écrivez une expression du type E2=1/12/1960, alors Excel ne voit pas que vous avez écris une date. Dans sa logique, il compare E2 au résultat du calcul de 1 divisé par 12 et divisé ensuite par 1960. Il faut savoir que pour écrire une date dans un test, et pour que celle-ci soit comprise, alors le test doit être écrit comme suit : E2=DATE(1960;12;1). La fonction DATE(  ;  ;  ) reconstruit une date à partir des trois informations année, mois et jour.

La fonction SI

La fonction incontournable d'EXCEL !!!

Entre les formes simples et les formes complexes imbriquant plusieurs Si avec des conditions à base de formules, cette fonction permet des applications sans fin.

Tranquille : Présentation de la fonction SI()

• Télécharger les données
• Voir la fiche de cours

Ca chauffe : Calculs et fonctions dans un SI()

• Télécharger les données
• Voir la fiche de cours

Tableaux croisés dynamiques

Vous en avez surement (beaucoup) entendu parler, voici les tableaux croisés dynamiques. Ils sont très utiles pour analyser des listes de données, et en extraire des tableaux synthétiques.

Données : Il faut donc disposer d'une liste de données à analyser : Télécharger les données. (Dans ce fichier, ne pas travailler sur la feuille "original").

Présentation : Voici deux exemples de tableaux qu'on peut tirer de ces données, ce qui est une bonne introduction à l'usage des TCD. En cours, vous allez voir comment créer ces tableaux.
() NOUVEAU : Vous pouvez voir la VIDEO qui présente les tableaux croisés dynamiques.

Exercice : Voici une liste de questions à traiter, questions concernant le fichier de données précédemment téléchargé.

Les graphiques - Cas "Fournisseurs d'accès"

Dans une feuille Excel, réaliser l'exercice suivant, qui vous amènera dans un premier temps à créer un tableau de données et de calculs, et dans un deuxième temps à créer deux graphiques à partir des données précédemment saisies et calculées.

Cliquer sur l'image pour l' AGRANDIR !

Les graphiques - Excel ne voit pas les étiquettes

Un graphique simple peut perturber Excel ! Vous allez voir dans l'exercice qui suit, que Excel ne sait pas trouver les étiquettes ! Il va falloir l'aider.

• Avant de commencer l'exercice, un petit rappel sur la terminologie d'Excel

C'est parti pour l'exercice qui suit ... qui vous amènera à devoir modifier les données sélectionnées, en utilisant l'onglet "Création" / "Sélectionner les données"

• Dans une feuille Excel, créer le tableau suivant :

•  et à partir de ce tableau, créer le graphique suivant :

En fait : Lorsque la sélection initiale comporte deux plages de valeurs numériques, Excel considère qu'il y a deux graphiques à faire au lieu d'un seul, et il considère ainsi que chacune des deux plages est une plage de valeurs. Pour lui, aucune étiquette n'a été indiquée, et du coup Excel invente les étiquettes 1, 2, 3, etc. C'est à nous de modifier le graphique créé par Excel (Onglet "Création" / "Sélectionner les données"), de suppprimer une série de données et de modifier la plage d'étiquettes, en venant sélectionner manuellement la plage adéquate.

Les graphiques - Présentation

Nous abordons ici les bases de la création de graphiques avec Excel :

• Le vocabulaire : Excel parle d'étiquettes et de valeurs
• La sélection des données servant à créer le graphique
• La modification des éléments du graphique une fois celui-ci constitué
• Le changement des min et max des axes
• La gestion des quadrillages primaire et secondaire
• L'affichage des différents titres (du graphique, des axes)
• La mise en forme en couleur
• La modification des données une fois le graphique constitué

Exercice à faire

• Télécharger le fichier avec les données et les consignes

Ci-dessous, la réalisation de l'exercice, en deux vidéos.

Vidéo 1 - Premier graphique

Vidéo 2 - Graphiques 2, 3 et 4