Developpez.com - Excel
X

Choisissez d'abord la catégorieensuite la rubrique :


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.

       Version PDF (Miroir)   Version hors-ligne (Miroir)
Viadeo Twitter Facebook Share on Google+        



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 :

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:

Ergonomie plus développée: les MFC courantes ont été regroupées en deux catégories:

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 :

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.

info 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



               Version PDF (Miroir)   Version hors-ligne (Miroir)

Valid XHTML 1.0 TransitionalValid CSS!

Responsables bénévoles de la rubrique Excel : Pierre Fauconnier - Arkham46 -