Excel : simuler une double recherche verticale
Troisème astuce sur Excel (Microsoft). Cette fois, on rentre vraiment dans la « bidouille ».
Situation : Vous avez d'un côté une liste de valeurs qu'il vous faut classer, dans l'exemple qui suit en catégories et sous-catégories, de l'autre un tableau, une matrice de correspondance entre des id de catégorie et leur valeur. Une sorte de simulation de base de données avec deux tables. Pour votre liste de valeur, vous connaissez leurs id mais souhaitez aussi afficher leur équivalent texte à l'aide de la matrice de correspondance. Exemple ci-dessous.
À gauche : la matrice de correspondance. À droite : votre liste avec les id.
Les id à droite sont établis en fonction de votre tableau. Exemples :
- Compétition de BMX, cela se range dans Discipline cycliste (id c. 2) > BMX (id s-c. 1)
- Circuits continentaux, cela se range dans Compétition cycliste (id c. 4), sans sous-catégorie
- Cyclisme en 2015 par mois, cela se range dans Chronologie du cyclisme (id c. 1) > Cyclisme par année et par mois (id s-c. 4)
Ces classifications, soit vous les déterminez à la main, soit vous les tirez d'un extract de base de données.
Méthode : Commencez par donner à votre matrice une vraie forme de base de données.
On commence par enlever la mise en forme : bordure, alignement et fusion.
Une fois les cellules défusionnées, il faut ensuite répéter les informations identiques à chaque ligne où il y en a besoin.
Servez-vous du double-clic sur le "+" dans le coin inférieur droit de votre sélection. La copie s'arrêtera jusqu'à la catégorie suivante. Répétez l'opération jusqu'à la dernière catégorie.
Créez un index temporaire : insérez une colonne à gauche de votre matrice. Dans mon exemple, ma plage de cellules avec la matrice est maintenant la suivante : B2:E31. Placez-vous sur la première ligne vide de votre nouvelle colonne (ici B3) et mettez-y la formule suivante :
=C3&D3&E3
Autrement dit, servez-vous de l'esperluette ("&") pour concaténer les colonnes suivantes : id catégorie, nom catégorie, id sous-catégorie. Ainsi, vous créez un identifiant unique à chaque ligne.
Les textes de l'index manquent de sens mais sont nécessaires pour la suite des opérations.
Retournez maintenant à votre tableau auquel vous voulez rattacher les valeurs textuelles des id. Pour vous repérer, nommez votre deuxième colonne « id catégorie » et votre troisième « id sous-catégorie ». Ajoutez une quatrième colonne « catégorie » et une cinquième « sous-catégorie ».
Ma plage de cellules avec le tableau à remplir est maintenant la suivante : J2:N10. Placez-vous sur la première ligne vide de votre colonne « catégorie » (ici M3) et mettez-y la formule suivante :
=RECHERCHEV(K3;$C$3:$D$31;2;0)
La fonction RECHERCHEV fonctionne avec les quatre arguments suivants : valeur cherchée, matrice de correspondance, index de la colonne, donnée binaire 0 ou 1 (valeur exacte ou valeur proche). Ici, on est encore dans une RECHERCHEV classique. La matrice de correspondance est un morceau du tableau que l'on a préparé préalablement, C3:D31 (ne pas oublier de le figer avec des $). En effet, il ne faut pas oublier que c'est la première colonne de la matrice qui sert à la recherche, du coup il ne faut pas inclure l'index ajouté en première colonne de matrice.
Il faut aussi savoir que si votre tableau contient plusieurs équivalences pour une seule valeur, la RECHERCHEV prendra la première. Dans le cas de la catégorie, peu importe car l'équivalence est toujours la même, mais pour retrouver la sous-catégorie, c'est justement la limite de la fonction, qui nous a conduit à cet article.
Pour notre deuxième RECHERCHEV, on va enfin se servir de l'index temporaire créé au départ. Placez-vous sur la première ligne vide de votre colonne « catégorie » (ici N3) et mettez-y la formule suivante :
RECHERCHEV(K3&M3&L3;$B$3:$F$31;5;0)
Décortiquons-la :
- K3&M3&L3 => on concatène dans la valeur cherchée les valeurs des colonnes suivantes : id catégorie, nom catégorie et id sous-catégorie. Exactement comme dans l'index temporaire !
- $B$3:$F$31 => cette fois on va chercher la matrice de correspondance dans sa globalité, en incluant l'index temporaire comme première colonne, autrement dit comme colonne de recherche.
- 5 => la matrice étant constituée de cinq colonnes, et que l'on cherche la dernière, on met la valeur "5".
- 0 => on veut la valeur exacte.
Comme vous pouvez le voir, on a la valeur "0" qui s'affiche lorsqu'il n'y a aucune sous-catégorie existante. Pour pallier ce problème d'affichage, deux solutions. Soit à la racine, c'est-à-dire dans la matrice de correspondance, vous entrez ="" dans toutes les cellules vides, ce qui a pour effet de remplacer les cellules vides par une chaîne de texte vide (ce qui n'a rien à voir !). Soit vous améliorez votre formule pour prendre le cas des sous-origines vides :
=SI(RECHERCHEV(K3&M3&L3;$B$3:$F$31;5;0)=0;"";RECHERCHEV(K3&M3&L3;$B$3:$F$31;5;0))
Conclusion sur cette méthode :
Premièrement il est possible que vous ne vous représentiez pas l'intérêt d'une telle combinaison de formules dans votre travail quotidien. Mais au cas où, gardez-la quelque part dans vos favoris : vous pourriez vous retrouver un jour avec une problématique similaire sans savoir quoi faire.
Deuxièmement, certains points peuvent ne pas sembler clairs, cet article s'appuyant sur plusieurs notions en Excel qui demandent un minimum de pratique. Pour vous aider davantage, je vous propose deux solutions : télécharger le document Excel ou me contacter (voir adresse en bas de page).