> Le Site du Zéro - Informatique

> Cours

> Tutoriels

> Bureautique

> Microsoft Office

> Excel

> Analysez des données avec Excel

> Analyse des données et dynamisme du classeur

> Les listes

> Lecture du tutoriel

Les listes

Par

bat538 et Avatar

Etienne et Avatar

~Electro

Mise à jour : 05/09/2011

Difficulté : Facile Facile

9 728 visites depuis 7 jours, dont 166 sur ce chapitre classé 26/786

Les listes de données... vous les croyez enterrées ? Et bien non, elles reviennent à la charge ! :pirate:

Ce coup ci, c'est pour l'analyse et non pour la saisie. Au programme : listes déroulantes à gogo et quelques formules bien mastoc ! Le tout pour vous y retrouver, filtrer et impressionner la galerie !

Pour pouvoir vous entraîner avec moi durant ce chapitre, je vous propose un nouveau classeur à télécharger :

Télécharger le fichier liste.xlsx

Retour en haut

Sommaire du chapitre :

Les filtres, une puissance négligée
Mettre en place son filtre
Analyser sa liste avec la fonction SOMMEPROD
Q.C.M.

Retour en haut

Les filtres, une puissance négligée

Les filtres, appliqués à une liste, permettent de visionner certains éléments de cette liste en fonction d'autres.

Par exemple, vous avez un tableau qui contient les notes de 10 élèves dans 5 matières différentes. Grâce aux filtres, vous pourrez afficher uniquement les notes de tel élève, celles qui sont au-dessus de 10, etc.

Bref, elles font partie de ce que nous pourrions appeler les « notions avancées d'Excel ». Peu de personnes pensent à les utiliser : leur puissance en est négligée.

Prenons notre liste :

Vous voyez que c'est un véritable bazar ! Encore, ça va parce qu'il n'y a que 2 matières mais imaginez qu'on ait mis 35 élèves et 8 matières. o_O

Les filtres vont nous aider à faire un tri simple, efficace et à nous y retrouver.

Retour en haut

Mettre en place son filtre

Notre tableau est exploitable, on peut donc analyser les données qui s'y trouvent !

Sélectionnez toutes les cellules qui composent ce fameux tableau puis allez dans l'onglet « Insertion » et dans le cadre « Tableaux », cliquez sur le bouton « Tableau » :

Il vous ai ensuite demandé de sélectionner le tableau en question, attention à bien préciser que les en-têtes de votre tableau figurent dans votre sélection :

Voilà qui est fait.

À première vue, rien n'a changé mais penchez-vous sur les titres des colonnes :

Des listes déroulantes ! Ce sont elles qui vont filtrer vos données.

Déroulez par exemple la liste de la colonne « Note ». Si vous sélectionnez 8, vous aurez dans votre tableau toutes les lignes dont la note est 8, en l'occurrence Mathieu !

Lorsqu'un filtre est activé, c'est un mini-entonnoir qui est apparut à la place de la flèche et cette dernière est positionnée en bas à gauche de l'entonnoir :

Les filtres personnalisés

Ça y est, vous êtes heureux avec ces filtres mais saviez-vous que vous pouvez les personnaliser ?
Ah oui, non, c'est vrai, vous ne saviez pas.

Cliquez sur une des listes déroulantes et choisissez « Filtre numérique », puis « Filtre personnalisé... » dans la nouvelle liste qui vient d'apparaitre :

Une fenêtre s'ouvre alors :

À partir de là, vous pouvez faire ce que vous voulez !
Choisissez selon vos bons plaisirs dans les listes déroulantes, mettez des valeurs dans les champs... Je ne peux plus vous guider ici : c'est vous le patron.

Retour en haut

Analyser sa liste avec la fonction SOMMEPROD

Il est également possible d'analyser sa liste avec une fonction méconnue, aux explications généralement floues, mais d'une puissance exceptionnelle : « SOMMEPROD ».

Bon, tu nous dis du bien de cette fonction, mais on ne sait pas vraiment ce qu'elle a de si particulier juste en voyant son nom... et encore moins sans description !

J'y viens. Elle permet de comptabiliser des données en multipliant des matrices entre elles. :-°

Pour être clair, elle permet de compter le nombre d'entrées d'une liste selon des conditions mais aussi d'additionner des cellules d'une liste selon des conditions. Ce n'est toujours pas très clair ? Je vous donne la fonction et tout de suite un exemple, ça vous aidera surement à comprendre.

=SOMMEPROD((plage1="critère1")*(plage2="critère2")*...)

Alors c'est mieux ? On peut compter le nombre de ligne où la plage1 (colonne 1) est égale à critère1 et où la plage2 (colonne 2) est égale à critère2.

Nous allons utiliser un nouveau tableau (plus long) pour les exemples :

Pour me suivre durant cet exercice, vous pouvez télécharger ce nouveau tableau :

Télécharger le fichier sommeprod.xlsx

Notez néanmoins que travailler un peu votre saisie ne peut pas vous faire de mal.

Ce tableau représente les ventes de chaque vendeur d'un magasin sur les trois premiers mois de l'année. Il y a 4 vendeurs (Jean, Pierre, Paul, Jacques).

Comment faire pour savoir le nombre de ventes de Paul au mois de Mars ? En utilisant la fonction « SOMMEPROD ». pardi !

Pour cela il faut entrer la formule suivante :

=SOMMEPROD((A2:A31="Paul")*(B2:B31="Mars"))

On obtient bien 3 ! Et oui Paul a fait 3 ventes au mois de mars. Maintenant on cherche à savoir combien d'argent a rapporté Paul au mois de Mars. Il suffit de multiplier par la colonne "Montant" de cette manière :

=SOMMEPROD((A2:A31="Paul")*(B2:B31="Mars")*(C2:C31))

Tada ! On obtient donc 2230. En effet la fonction a effectué le calcul suivant : 840+660+730=2230. C'est top non ? On peut faire plein de combinaisons avec cette formule ! Mais attention il y a quelques règles à respecter :

Toutes les plages doivent avoir la même taille et aucune colonne ne peut être prise entièrement en entrant (A:A). Cela dit, on peut la sélectionner en faisant (A1:A65535).

Je vous propose d'autres exemples pour bien comprendre et voir un peu ce que l'on peut faire avec cette fonction.

Exemple 1

Il est possible de compter le nombre de ventes réalisées par Jean (on peut aussi réaliser cette opération avec la fonction NB.SI) :

=SOMMEPROD((A2:A31="Jean")*1)

On obtient ainsi : 8. Cet exemple n'est pas le meilleur pour montrer la puissance de la fonction mais elle montre qu'on n'est pas obligé d'avoir beaucoup de paramètres compliqués.

Exemple 2

Il est aussi possible de compter le nombre de ventes supérieures à 600€ au mois de Janvier :

=SOMMEPROD((B2:B31="Janvier")*(C2:C31>600))

On obtient ainsi : 2. On peut ainsi combiner les conditions pour prendre les valeurs comprises entre 200 et 600 par exemple.

Exemple 3

Enfin, dernier exemple, nous pouvons totaliser la somme accumulée grâce à Pierre aux mois de Janvier et Mars :

=SOMMEPROD((A2:A31="Pierre")*((B2:B31="Janvier")+(B2:B31="Mars"))*(C2:C31))

On obtient ainsi : 2760.

Je viens donc de vous montrer la puissance de cette fonction, qui peut s'avérer très utile dans des longues listes présentant beaucoup de critères.

Retour en haut

Q.C.M.

Nativement, le filtre se fait selon :

Ce qu'on lui demande ;
Le contenu de la colonne ;

Un filtre personnalisé est disponible. Cette fonctionnalité très puissante est néanmoins peut recommandée d'utilisation aux débutants

Le filtre personnalisé est plutôt simple d'utilisation, aucun problème à ce niveau là.
Si vous avez lu ce chapitre, au bout de quelque temps d'utilisation, vous saurez vous en servir.

Quelle est la fonction vue dans ce chapitre ?

SOUPROD ;
SOMMEPROD ;
SURPROD ;
SOUSPROD ;

Statistiques de réponses au QCM

Retour en haut

Voilà pour les listes de données et leurs filtres.
Je vous conseille de bien appréhender ce chapitre : nous ne pourrons pas nous passer des données filtrées dans le chapitre suivant ! :-°

Retour en haut

Retour en haut

11 commentaires pour "Les listes"

Note moyenne : 3.67 / 4 (100 votes)

Pseudo	Commentaire
h4x@n	# Posté le 31/10/2010 à 20:59:48
Avis : Très bon Ville : Le péage de roussillon Pays : France métropolitaine	Même si j'ai l'impression d'avoir tout compris, j'ai trouvé ce tuto beaucoup moins compréhensible que les précédents . Je pense qu'il a de quoi être amélioré, en tout cas c'est mon avis. Bon sur ceux je n'ai pas le temps de me lamenter devant la moindre difficulté, je continue ! Vous pouvez retrouver tous mes autres projets sur mon site en cliquant sur l'image ci-dessus.
Gothor	# Posté le 01/01/2011 à 01:38:45
Badou badou ouapa	Bonjour, j'utilise Office 2010 et chez moi, la fonction SOMMEPROD ne fonctionne pas tout à fait de la même manière. Ainsi, pour avoir le total de leur vente, je n'ai pas dû faire: =SOMMEPROD(($B$2:$B$32=F3)($C$2:$C$32=G3)($D$2:$D$32)) mais =SOMMEPROD(($B$2:$B$32=F3)*($C$2:$C$32=G3);($D$2:$D$32)) Ce n'est pas grand chose, mais c'est différent... (d'ailleurs, je ne sais pas pourquoi une multiplication d'un côté et un point-virgule après...
thefone	# Posté le 17/02/2011 à 10:22:44
	bonjour, Je ne vois pas l'intérêt des formulaires. peut-être que je n'ai pas bien compris comment m'en servir, mais je trouve la saisie des données plus longue que si je le faisais manuellement. pourriez vous me dire ce qu'elle a de bien?
patlang	# Posté le 04/06/2011 à 01:28:03
	Impossible de télécharger le fichier .xls d'Excel. Je travaille sur notebook windows 7. Quelqu'un voudrait bien m'aider?
Etienne	# Posté le 04/06/2011 à 06:52:25
Have you mooed today ? Groupe : Anciens	Citation : patlang Impossible de télécharger le fichier .xls d'Excel. Je travaille sur notebook windows 7. Quelqu'un voudrait bien m'aider? Déjà ce sont des .xlsx qui sont proposés en téléchargement. Je viens de (re)tester et il n'y a aucun problème. Quelle est l'erreur que tu rencontres ?