Les tableurs ont simplifié la comptabilité et la gestion de projets. Avec un tableur, qu’il soit Excel ou Sheet, il est simple de créer une base de données et de faire parler les chiffres. Graphique, tableau croisé dynamique, ou calcul basique, nous allons pouvoir créer des tableaux de bord, des rapports ou même des factures et cela très simplement. S’il est simple d’ajouter le contenu de plusieurs cellules entre elles, encore faut-il trouver l’adresse de la cellule contenant la donnée que vous cherchez. Aujourd’hui je vous propose de chercher la colonne d’une valeur sur Google Sheet. 

Des données qui proviennent d’un copier coller 

En entreprise, il n’est pas rare d’avoir des outils métiers qui génèrent des données. Que cela soit un chiffre de ventes journalier ou des stocks produits, il est très souvent possible de les exporter au format Excel ou le cas échéant de faire un copier-coller des données. 

Une fois que nous avons les données, nous pouvons les coller dans un classeur Google Sheet pour faire notre dashboard et nos calculs. Souvent il est difficile de savoir à l’avance ou se trouve la colonne contenant un type de valeur, et si un jour elle se positionne sur une colonne, il se peut que le lendemain la valeur ne soit plus au même endroit. 

C’est avec cette problématique en tête que j’ai écrit cet article. Comment trouver la colonne  d’une valeur sur Google Sheet automatiquement ? La solution est simple, mais il faut la détailler pour la comprendre. 

Chercher la colonne d’une valeur sur Google Sheet

Pour vous expliquer comment trouver les coordonnées d’une valeur dans Google Sheet nous allons partir sur un exemple simple qui pourra être décliné à l’infini. C’est un tableau de 5 colonnes : Types/ dénomination / .. / Prix HT. Évidement mon tableau est minimaliste, mais il permet de comprendre facilement les fonctions.

Ce tableau est un simple copié-collé d’un outil interne. Aujourd’hui il n’y a que 5 colonnes, mais cela peut évoluer dans le temps et je n’ai pas envie de refaire mes fonctions à chaque changement d’emplacement de mes valeurs.

Pour l’exemple je veux trouver les coordonnées de la cellule contenant la valeur “Prix HT”. Évidemment dans mon tableau nous constatons qu’elle est en E1. Ce que je souhaite, c’est de trouver E1 automatiquement.

Ce que nous savons et qui n’évolue jamais.

La seule valeur qui n’évoluera jamais n’est autre que le numéro de la ligne d’en tête de mon tableau qui se trouve en ligne “1”, tout le reste peu changé à tout moment :

  • Le nombre de colonnes
  • La dénomination de ma colonne 

Equiv() affiche la position relative d’une donnée

La première chose à faire est donc de trouver la position relative d’un élément de place correspondant à une valeur indiquée. Pour cela nous allons utiliser la fonction =Equiv(). Voici comment elle se définie :

=EQUIV(clé_recherche; plage; [type_recherche])

Comme indiqué je veux trouver la position relative de la valeur “Prix HT” dans une plage donnée, sachant que ma valeur est présente sur la ligne 1.

  • Clé_recherche : “Prix HT” 
  • Plage : 1:1 car je vais chercher en  ligne 1
  • Type_recherche  : 0 (pour la valeur exacte)

La fonction s’écrira donc ainsi

=EQUIV("Prix HT";1:1;0)

Dans notre exemple, le résultat renvoyé sera “5” car notre valeur est présente dans la cinquième colonne de notre tableau.

Adresse() affiche la référence de la cellule

Maintenant que nous avons la position relative de notre valeur, il nous faut la transformer en référence de cellule. Pour cela nous allons faire appel à la fonction Adresse(), définie ainsi :

ADRESSE(ligne; colonne; [mode_relatif_absolu]; [utiliser_notation_a1]; [feuille])

Cette fonction est relativement simple, nous allons simplement lui indiquer la ligne et la colonne, où la colonne sera le résultat de notre fonction Equiv()

=Adresse(1;EQUIV("Prix HT";1:1;0))

Nous savons donc que la valeur “Prix HT” est positionnée dans la cellule $E$1

Indirect() pour utiliser une référence de cellule en chaine

À présent que nous connaissons la référence de la cellule nous pouvons utiliser cette référence dans une autre fonction. 

Attention, à ce stade, vous n’avez que la référence de la cellule, en aucun cas le contenu de la cellule. Pour que Google Sheet utilise le contenu de la cellule il faudra passer par la fonction Indirect(). La fonction indirect() affiche une référence de cellule spécifiée par une chaîne.

Il suffit d’encapsuler notre formule précédente dans la fonction Indirect()

=Indirect(Adresse(1;EQUIV("Prix HT";1:1;0)))

Le résultat ici ne sera plus $E$1 mais “Prix HT

Chercher la colonne d’une valeur sur Google Sheet

Amusons-nous à faire un moteur de recherche, avec filtre, dans un tableau avec les fonctions Equiv() et rechercheV(). Tout d’abord nous allons créer deux menus déroulants. L’un pour la dénomination du produit et l’autre pour la valeur à afficher que j’appellerai filtre.

Dans Données > Validation de Données > Liste créée à partir d’une plage > sélectionner la colonne dénomination > Puis fait enregistrer.

Créez également un menu déroulant avec les données d’entête de la ligne 1.

Dans Google Sheet la fonction validation de données supprime par défaut les valeurs en  doublons, très pratique pour réaliser un menu déroulant rapidement. 

Pour notre exemple le menu déroulant Dénomination se trouve en Q24 et le filtre en Q25. Notre tableau sera défini par de la colonne B à la colonne N

La fonction RECHERCHEV() est une recherche verticale. Elle permet de rechercher une clé dans la première colonne d’une plage, et affichage de la valeur d’une cellule spécifiée dans la ligne trouvée.

Voici comment se décompose la fonction  :

=RECHERCHEV( Valeur recherchée; la plage du tableau, le numéro de la colonne ou se trouve la donnée à afficher)

Ce qui donne dans notre exemple :

=RECHERCHEV(Q24;B:N;EQUIV(Q25;1:1;0)-1)

Le -1,ajouté à la formule, nous permet de décrémenter de 1 le résultat obtenu par la fonction Equiv(). En effet, notre colonne référence pour la  fonction recherche est la colonne B et non en colonne A comment pour la fonction équiv()

Dès à présent, il suffit de changer la valeur dans le menu déroulant filtre pour chercher dans une autre colonne. 

Google Sheet devient infini

Simple est très efficace. avec les fonctions Equiv(), adresse() et indirect, vous pourrez vous amuser à faire des fonctions qui ne souffriront pas du changement de position des données dans votre tableau. Ainsi vous pourrez concaténer des valeurs avec des positions relatives sans aucune difficulté. 

Mon conseil si vous débutez dans Google Sheet, décomposer vos fonctions avant de les réunir. Cela permet de vérifier le résultat à chaque étape de la construction de votre fonction.  

Aviez-vous déjà eu recours à ces fonctions pour trouver la position relative d’une valeur ? Avez-vous d’autres solutions plus simples que la mienne ? Faites-moi part de votre expérience dans la suite de cet article à travers vos commentaires.    

Shares:

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.