Comment trouver les cellules communes à plusieurs plages avec l'opérateur d'INTERSECTION d'Excel

Aujourd’hui, nous allons découvrir une des fonctionnalités méconnues d’Excel, il s’agit de l’opérateur d’INTERSECTION. Ce dernier permet d’effectuer des traitements (tels que des calculs, récupération de coordonnées, …) sur des cellules situées à l’intersection de plusieurs ensembles de cellules. À la fin de ce tutoriel, nous verrons également comment procéder de manière très simple pour sélectionner utiliser les plages de cellules dont souhaitons extraire l’intersection directement dans des menus déroulants !

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

1. L’opérateur d’INTERSECTION

Bien peu utilisé, l’opérateur d’intersection permet de récupérer les cellules communes à deux plages de cellules de manière très rapide, sans avoir besoin d’utiliser de formule complexe.

Pour l’utiliser, rien de plus simple, il suffit en effet de taper un espace entre les plages de cellules comparées :

Par exemple si nous souhaitons connaître le montant des ventes réalisées en Allemagne durant le mois d’avril, la formule sera la suivante :

 =C7:C20 A11:D11

2. Effectuer une somme de cellules retournées par l’INTERSECTION

Attention, l’opérateur d’INTERSECTION saisi de manière aussi simple ne permet par contre de récupérer la valeur contenue à l’intersection des plages de cellules que lorsque cette intersection n’est composée que d’une seule cellule ! En d’autres termes, si les plages de cellules utilisées pour effectuer le calcul possèdent plusieurs cellules en commun, alors Excel renverra irrémédiablement une erreur de type #VALEUR :

Pour régler ce problème, il convient d’utiliser une formule SOMME() pour effectuer par exemple la somme des valeurs des cellules se trouvant à l’intersection de ces plages :

 =SOMME(A8:D10 C7:C20)

3. La magie des plages nommées !

Pour le moment, ce que nous venons de voir ne paraît pas encore extraordinaire ! En effet, il est encore beaucoup simple et rapide de sélectionner directement les cellules contenant les résultats pour effectuer les calculs :

Mais vous devez bien vous douter, qu’Excel est capable de beaucoup mieux ! En effet, pour révéler toute la puissance de l’opérateur INTERSECTION, nous allons maintenant utiliser les plages nommées.

Et vu que nous souhaitons réaliser nos calculs le plus rapidement possible, nous allons laisser Excel se charger de la création de ces noms de cellules automatiquement pour nous :

• Commençons par sélectionner les cellules du tableau (pour cela sélectionnons une des cellules, puis appuyons simplement sur les touches [Ctrl]+[*]),

• Puis dans le menu Formules du ruban, nous cliquons sur le bouton Depuis sélection (dans le groupe Noms définis) :

• Puis dans la fenêtre qui s’affiche, nous cochons simplement les deux premières lignes (Ligne du haut, et Colonne de gauche) et nous validons la création des noms en appuyant sur le bouton [OK] :

Cela a pour effet de créer automatiquement les plages nommées en fonction des titres de colonnes et de ligne :

À présent, nous pouvons insérer de nouvelles formules très simplement, en écrivant de manière casi rédactionnelle les champs à utiliser :

 =SOMME((Janvier;Février;Mars) Allemagne)

Note : Comme vous pouvez le voir sur la formule ci-dessus, nous avons utilisé un opérateur que nous connaissons tous sans savoir qu’il en est un, il s’agit de l’opérateur UNION (le point-virgule « ; ») qui permet d’unir des plages de cellules entre-elles (cliquez-ici pour tout savoir sur les différents opérateurs).

Nous pouvons également insérer de nouvelles plages nommées pour séparer les données en trimestres :

Ce qui nous permet de simplifier grandement la formule que nous venons de créer :

 =SOMME(Trimestre1  Allemagne)

4. Sélection de cellules dans un menu déroulant

À présent, nous allons vouloir simplifier la sélection de cellules en combinant l’opérateur d’Intersection que nous venons de voir avec l’utilisation de menu déroulants directement intégrés dans les cellules.

La création de menu déroulant peut se faire de trois manières différentes, comme nous avons eu l’occasion de le voir dans un article précédent (que vous trouverez en suivant ce lien). Ici, nous allons utiliser la méthode la plus courante, c’est-à-dire la création de validation de données sous la forme de liste.

Pour cela, commençons par sélectionner la cellule de destination (G18), puis nous allons nous rendre dans le menu Données, afin de cliquer sur le bouton Validation des données (au sein du groupe Outils de données) :

Dans la fenêtre qui s’affiche à l’écran :

• Nous choisissons d’autoriser la saisie de données contenues dans une liste (menu Autoriser),
• Nous vérifions que la case Liste déroulante dans la cellule est effectivement cochée,
• Dans la zone d’adresse Source, nous sélectionnons la plage des cellules contenant les mois de l’année,
• Enfin, nous validons en appuyant sur le bouton [OK] :

Puis, nous répétons l’opération pour insérer les trois noms de pays dans la cellule située juste en dessous :

Ensuite, il ne nous reste plus qu’à récupérer la cellule qui se trouve être située juste à l’intersection des plages de cellules dont les noms sont sélectionnés dans ces menus déroulants.

Bien évidemment, il ne suffit pas de sélectionner ces cellules en utilisant l’opérateur d’Intersection. Cela aurait pour effet de chercher une cellule commune aux deux cellules concernées, ce qui n’est pas possible et renverra ainsi une erreur #NUL ! :

La solution pour récupérer les coordonnées d’une plage de cellule à partir du nom de celles-ci est d’utiliser la formule INDIRECT(), qui permet de récupérer une référence à partir d’une simple chaîne de caractères :