Excel 2010 : Comparer des listes de données grâce à la mise en forme conditionnelle
Date de publication : 26 octobre 2011.
Par
Pierre Fauconnier (http://fauconnier.developpez.com) (Blog)
Lors d'une formation que je donnais, les participantes m'ont informé qu'elles
traitaient souvent des listes. L'idée d'un exercice permettant de comparer des listes
via la mise en forme conditionnelle m'a amené à la rédaction de ce tutoriel.
Au travers de ce cours, je vous propose une méthode permettant de tester la présence
d'éléments d'une liste dans une autre.
I. Introduction
II. Rapide aperçu des possibilités de mise en forme conditionnelle
II-A. Définition
II-A-1. La MFC, version 2010
III. Conception du classeur
III-A. Création des tableaux
III-B. Mise en place des fenêtres
IV. Techniques de comparaison
IV-A. Recherche
IV-B. Dénombrement conditionnel
V. Mise en place de la mise en forme conditionnelle
VI. Conclusions
VII. Remerciements
I. Introduction
Il arrive régulièrement que nous devions comparer des listes de données,
pour mettre en évidence les éléments d'une liste qui (ne) se trouvent (pas)
dans une autre :
- articles d'un stock non vendus dans une période donnée ;
- intérimaires n'ayant pas presté pour une agence ;
- types d'incidents ou de non-conformités non rencontrés sur un site d'exploitation ;
- ...
La mise en forme conditionnelle se prête particulièrement à
ce genre d'analyse, car elle permet de mettre les données significatives
en évidence de manière visuelle.
Avec un peu d'astuce et la maîtrise de fonctions basiques d'Excel,
nous allons pouvoir concevoir un classeur qui peut s'adapter à beaucoup
de situations.
II. Rapide aperçu des possibilités de mise en forme conditionnelle
II-A. Définition
La Mise en Forme Conditionnelle (MFC) permet de
déterminer un format de cellule lorsqu'une condition est remplie.
Cette condition peut s'appliquer à la cellule qui supportera le format
conditionnel ainsi qu'à une autre cellule du classeur.
La condition peut également être déterminée par une formule conditionnelle,
c'est-à-dire une formule qui renvoie une valeur logique VRAI ou FAUX.
II-A-1. La MFC, version 2010
Par rapport aux versions antérieures, la MFC 2007/2010 offre principalement
quatre nouveautés:
- une ergonomie plus développée, présentant nativement des choix qu'il fallait "formuler" auparavant ;
- un remplissage dégradé de type graphique pour comparer rapidement les cellules d'une plage ;
- une mise en forme par icones qui permet de regrouper les données en trois, quatre ou cinq groupes ;
- une complexité accrue par la possibilité de fixer 64 formats conditionnels par cellule, en permettant le cumul des conditions.
Ergonomie plus développée: les MFC courantes ont été regroupées en deux catégories:
- les conditions sur la valeur de la cellule par rapport à une valeur pivot indépendante de la plage sur laquelle la MFC s'applique, et qui ne font donc varier le format que de la cellule elle-même ;
- les conditions qui s'appliquent sur "le poids" de la cellule par rapport à la plage sur laquelle s'applique la MFC, ce qui amène à ce que la variation d'une seule valeur redessine l'entiereté de la plage.
Un remplissage de type "graphique" permettant de visualiser rapidement le poids d'une valeur
dans une plage de cellules, que ce soit par pourcentage, par position par rapport au minimum et
maximum de la plage...
une mise en forme par icones, qui permet de diviser la plage de valeurs en
groupes répartis par pourcentages, centiles, plages de valeurs, ...
Bien entendu, Excel offre toujours la possibilité de créer des formules
conditionnelles, permettant une finesse et une complexité accrues dans la
détermination des conditions présidant à la détermination du format conditionnel.
III. Conception du classeur
Comme souvent en Excel, la bonne exécution du "cahier des charges" dépendra de la
conception du classeur.
Nous allons créer deux tableaux, chacun sur une feuille de classeur. De cette façon,
Excel ajoutera la mise en forme conditionnelle sur les nouvelles lignes de nos
tableaux, assurant ainsi une fiabilité de la mécanique mise en place.
III-A. Création des tableaux
III-B. Mise en place des fenêtres
IV. Techniques de comparaison
Nous pouvons rechercher la présence d'éléments dans une liste
essentiellement de deux manières :
- la recherche ;
- le dénombrement conditionnel.
Dans l'analyse qui va suivre, nous allons créer des formules
au sein de la feuille de calcul, voire même au sein du tableau.
Lorsque les mécanismes d'identification auront été assimilés, nous
verrons comment placer la mise en forme conditionnelle.
IV-A. Recherche
Nous pouvons détecter la présence ou l'absence d'éléments dans une
liste en effectuant une recherche de l'élément. Soit l'élément est trouvé
et donc, il existe, soit l'élément n'est pas trouvé et la recherche
renvoie une erreur #N/A.
IV-B. Dénombrement conditionnel
Le dénombrement conditionnel a ma préférence car il permet de
multiplier les critères de choix. La version 2010 offre en effet
la fonction NB.SI.ENS() qui permet jusqu'à 64 critères. Nous
pouvons en plus, sur base du fonctionnement de NB.SI.ENS(),
créer des formules matricielles lorsque les critères ne portent pas
sur les valeurs de certaines cellules, mais sur le résultat de calculs
appliqués aux valeurs des cellules.
Les techniques de dénombrement conditionnel ou matriciel permettent donc
d'ouvrir de nouveaux horizons d'analyse.
 |
Depuis la version 2007 et l'avènement des fonctions d'ensemble, je
considère que NB.SI() et SOMME.SI() sont devenues obsolètes et
ne doivent être conservées qu'à des fins de compatibilité avec les versions antérieures.
Je suivrai le même raisonnement avec MOYENNE.SI() qui, bien qu'apparue
avec la version 2007, est de facto obsolète et sera remplacée par la fonction
MOYENNE.SI.ENS() apparue en même temps.
|
V. Mise en place de la mise en forme conditionnelle
VI. Conclusions
VII. Remerciements
