Introduction
Vous en avez marre d’utiliser les fonctions RECHERCHEV & RECHERCHEH (voire RECHERCHEX pour les utilisateurs d’Office365) ou encore le combo de fonctions INDEX et EQUIV, ou bien vous vous y perdez dans l’utilisation de ces fonctions de Microsoft Excel ?
Et si je vous apprenais une astuce très simple utilisant les Noms définis comme alternative, ça vous intéresse ?
Spoiler de ce que vous allez apprendre à faire dans ce tutoriel Excel :
Intéressant comme formule, vous ne trouvez pas ?
On y va pour le guide détaillé ? Allez c’est parti !
Cette astuce, qui utilise donc les Noms définis, nécessite un peu de paramétrage, mais rien de bien méchant ou de chronophage, voyons cela étape par étape dans ce qui suit.
Quand les noms des plages sont des textes…
1. Sélectionner la plage entière des cellules, à partir de laquelle on veut faire notre recherche croisée (obtenir le résultat d’une cellule qui est croisement d’une certaine ligne et d’une certaine colonne).
2. Aller dans l’onglet Formules, puis dans la partie du ruban dénommée Noms définis, pour finalement cliquer sur la commande Depuis sélection (raccourci clavier Ctrl + Shift + F3).
3. Cette manipulation permet de créer des plages nommées automatiquement à partir de la sélection. Dans notre cas de figure nous voulons qu’une plage nommée soit créée pour chaque ligne et pour chaque colonne de la plage sélectionnée. Il faut donc s’assurer que dans la fenêtre qui s’affiche, les deux premières options soient cochées (Lignes du haut & Colonne de gauche). Cliquez sur OK.
4. Pour mieux comprendre ce qu’il s’est passé, allons voir dans l’onglet Formules, toujours dans la partie Noms définis, pour sélectionner cette fois le Gestionnaire de Noms (raccourci clavier CTRL + F3).
5. On constate alors qu’un nom a été défini pour chaque ligne (noms = cellules de la Colonne de gauche ) et pour chaque colonne (noms = cellules de la Ligne du haut) de la plage initialement sélectionnée. Parfait, c’est ce que nous voulions !
Ça y est, c’est déjà fini pour le paramétrage ! Alors, facile hein ? Il ne reste plus qu’une étape maintenant : Ecrire la recherche croisée , on y va :
6. Pour faire la recherche croisée, on écrit une formule comme n’importe quelle formule Excel en commençant par le symbole =, puis on commence à taper le nom de la ligne qui contient l’information que nous cherchons (le nom apparaît alors dans la liste où s’affichent habituellement les fonctions) : on le sélectionne (double-clic ou navigation avec les flèches du clavier + TAB), puis on ajoute un ESPACE avant de commencer à taper le nom de la colonne qui contient l’information que nous cherchons et on le sélectionne dans la liste qui s’affiche.
Dans les 2 exemples du GIF ci-dessous, on cherchait :
• L’EBITDA du Trimestre 1 ➡️ =Trim1 EBITDA_Trim ou =EBITDA_Trim Trim1
• L’EBIT du Trimestre 3 ➡️ =Trim3 EBIT_Trim ou =EBIT_Trim Trim3
Et voilà, on en a fini pour ce cas de figure ou la Colonne de gauche et la Ligne du haut contenaient des informations textuelles. Si c’est votre cas, je vous conseille juste de lire le dernier commentaire de l’article sur la mise en garde vis-à-vis de cette astuce avant de quitter ce tutoriel.
Quand les noms des plages sont des valeurs…
Pour l’heure, on va voir dans la suite ce qu’il faut faire quand notre Colonne de gauche et notre Ligne du haut contiennent des informations numériques (ça se complique un tout petit peu). Et comme la répétition est la base de l’apprentissage, on recommence tout depuis le début (ne râlez pas, c’est pour votre bien 😀) :
1. Allez, on sélectionne la plage entière des cellules, à partir de laquelle on veut faire notre recherche croisée.
2. On va dans l’onglet Formules, puis dans la partie Noms définis du ruban, pour cliquer sur la commande Depuis sélection (raccourci clavier Ctrl + Shift + F3).
3. Et là, on va déjà constater un petit changement par rapport à la première manipulation. Par défaut, on constate que l’option Lignes du haut est bien cochée, mais pas l’option Colonne de gauche. On va donc la cocher puisque l’on veut créer des noms à la fois pour chaque ligne et pour chaque colonne, et valider en cliquant sur OK.
4. Allons donc voir ce qu’il s’est passé dans le Gestionnaire de Noms, accessible via l’onglet Formules dans la partie Noms définis (ou via le raccourci clavier CTRL + F3).
5. Et là… C’est le drame ! Les plages nommées ont été créées pour les colonnes, mais pas pour les lignes ! Pourquoi ? En fait, Microsoft Excel n’accepte pas des noms définis qui sont des valeurs. Il faut transformer ces valeurs en textes pour que cela puisse fonctionner. ATTENTION : Modifier le Format de cellule de la colonne qui contient les valeurs pour attribuer le format Texte ne fonctionne pas ! En fait cette option ne change que l’affichage des cellules, mais pas leur contenu (oui, c’est très ambigu, je vous l’accorde…).
6. OK Jamy, mais alors, comment fait-on ? Et bien Fred, c’est très simple ! On va en fait créer une autre colonne dans laquelle on va transformer nos valeurs en textes, je vous propose deux solutions (il en existe d’autres, mais ce sont à mon sens les plus simples et efficaces) :
• Utilisation de la concaténation au sens d’Excel en utilisant le symbole & : On écrit la formule =[n’importe quelle chaine de texte précisée entre guillemets]&[référence de la cellule qui contient la valeur] ;
• Utilisation de la fonction TEXTE dont voici l’explication tirée du site officiel de Microsoft : =TEXTE(valeur à mettre en forme; “Code de format à appliquer”). Le code de format à appliquer dans notre cas de figure est “@” pour transformer en chaine de texte. La formule à écrire est donc =TEXTE([référence de la cellule qui contient la valeur];”@”) et le tour est joué.
7. Allez, comme on a créé deux colonnes avec les deux transformations différentes en texte, on va faire d’une pierre deux coups et définir toutes les plages nommées afférentes à ces deux cas de figure pour voir ce qu’il se passe (répétition des étapes 1-2-3). Et on remarque tout de suite que lors de la répétition de l’étape N°3, nos deux options Ligne du haut & Colonne de gauche sont à nouveau cochées par défaut (en fait Excel nous indique déjà par ce biais que cela va fonctionner).
8. Comme nous sommes consciencieux, nous allons tout de même vérifier (répétition de l’étape 4, vous vous souvenez du raccourci clavier j’espère ?! 😉 CTRL + F3.
9. Yes ! Jackpot ! Avez-vous remarqué une subtilité ? Pour les noms définis générés pour les lignes à partir de la colonne An_Texte2 (utilisation de la fonction TEXTE), Microsoft Excel a ajouté automatiquement un trait d’underscore devant les noms.
Pourquoi ? En fait, c’est ce que j’expliquais plus haut : Microsoft Excel n’accepte pas des noms définis qui sont des valeurs. Comme il n’était pas content, il a fait le travail correctement à notre place en ajoutant un caractère textuel devant le nom pour qu’il devienne une chaine de texte.
Il faut le savoir car pour la dernière étape c’est important !
10. Nous y voilà justement à cette dernière étape ! On rédige la recherche croisée en commençant par le symbole =, puis on commence à taper le nom de la ligne qui contient l’information que nous cherchons (et là, dans le cas de l’utilisation de la fonction TEXTE pour transformer nos informations numériques en informations textuelles, si on ne sait pas qu’un underscore a été ajouté devant le nom de notre année, on peut chercher longtemps…).
Vous comprenez alors pourquoi la remarque de l’étape 9 était importante) : on sélectionne ce nom (double-clic dans la liste ou navigation avec les flèches du clavier + TAB ), puis on ajoute un ESPACE avant de commencer à taper le nom de la colonne qui contient l’information que nous cherchons et on le sélectionne également dans la liste.
Dans les 2 exemples du GIF ci-dessous/ci-dessus, on cherchait :
• L’EBITDA de l’Année 2017 avec le texte transformé par concaténation ➡️ =a2017 EBITDA_An ou =EBITDA_An a2017 ;
• Le CA de l’Année 2019 avec le texte transformé via la fonction TEXTE ➡️ =_2019 CA_An ou =CA_An _2019.
Et cette fois-ci on a vraiment fait le tour complet de ce tutoriel à un point près…
Mise en garde vis-à-vis de cette astuce !
N’utilisez cette astuce que lorsque vous voulez faire une recherche croisée dans un petit set de données : disons maximum 20 colonnes par 20 lignes (donc bien souvent des sets de données de paramétrage ou d’informations clés) ! Pourquoi ? Et bien parce que déjà dans cette configuration, 40 plages nommées de 19 cellules chacune vont être créées, et il faut savoir qu’un nombre excessif de plages nommées, mais surtout des plages nommées d’une taille excessive peuvent ralentir la vitesse de traitement de Microsoft Excel et augmenter le poids de votre fichier, ce qui pourrait amener à un plantage.
Vous pouvez malgré tout essayer d’appliquer cette astuce à des sets de données volumineux si vous le souhaitez, pour tester, mais je vous conseille grandement de faire une sauvegarde de votre fichier en amont, et vous invite plutôt à favoriser les fonctions de recherche classiques telles que RECHERCHEV, RECHERCHEH, RECHERCHEX ou INDEX / EQUIV dans ce cas de figure, sincèrement.
Mot de la fin
Vous savez dorénavant tout de cette astuce !
Qu’en avez-vous pensé ? N’hésitez pas à vous exprimer en commentaires, cela m’aidera à améliorer le contenu du Blog, et donc à vous aider.
J’espère que cela vous aura été utile !
On se dit à très bientôt dans l’aventure !
Fabien @Exceldorado
Leave a Reply