Google Sheets : La recherche : comment accéder dynamiquement à des informations ?

1211 2

1- Pourquoi utiliser la recherche ?

Google Sheets permet d’automatiser des tâches (calcul, recherche, synthèse…) à grande échelle.

Dans la liste de courses ci-dessous, si je vous demande le prix de la lessive, vous me répondrez “8,00 €” : votre esprit a parcouru verticalement la liste des articles pour trouver la lessive, puis a trouvé le prix correspondant dans la colonne “Prix unitaire”. Félicitations, vous venez d’effectuer une recherche verticale.

Pourquoi automatiser une opération si facile ?

  • Afin de la réaliser de nombreuses fois
  • Parce que si la liste de courses contient 1000 lignes, l’opération devient moins facile

Dans ce tutoriel, nous verrons deux manières d’effectuer une recherche par formule. La première est plus facile à utiliser, et nous vous conseillons de pratiquer au quotidien cette première méthode avant d’apprendre la deuxième, plus avancée.

2- La recherche simple avec la formule : RechercheV

Ce schéma explique le fonctionnement de la formule RechercheV : recherche verticale. Cette fonction demande quatre paramètres :

  • En jaune A17 : quel est l’élément cherché ? Ici nous recherchons la lessive.
  • En violet A3:D13 : il faut ici sélectionner le tableau dans lequel nous cherchons la lessive. La recherche s’effectue toujours dans la première colonne du tableau, ici “Article”.
  • En bleu 4 : lorsque le mot “Lessive” est trouvé en parcourant la première colonne du tableau, il faut préciser dans quelle colonne se trouve le résultat qui nous intéresse. Ici c’est la colonne 4, celle du prix unitaire
  • En noir faux : pour faire simple, toujours indiquer “faux”.

Pour faire compliqué, mais n’hésitez pas à ignorer cette explication : si on écrit “vrai”, on indique à la formule que les données sont triées. Si l’article recherché n’existe pas (par exemple “Shampoing”) on peut obtenir, de manière imprévisible, un résultat erroné car obtenu par extrapolation.

Comme pour de nombreuses formules, la rechercheV peut être “étendue” pour rechercher sur chaque ligne un résultat :

  1. Lorsqu’une formule est étendue, toutes les plages de cellules (ici A17 et A3:D13) sont modifiées : la cellule du dessous aura donc des plages différentes (A18 et A4:D14). Pour A18 c’est souhaité, car le but est justement de chercher l’élément du dessous (brosse à dents). Pour le tableau source en revanche, la plage ne change pas. Il faut donc verrouiller cette plage avec le symbole dollar, comme ci-dessous. Le raccourci clavier “F4” permet d’ajouter les dollars facilement.
  1. Pour étendre la formule, sélectionnez le coin en bas à gauche de la première cellule et “tirez” vers le bas

3- La recherche avancée avec la formule : Query

La formule rechercheV présente deux limites :

  • Si l’élément cherché existe plusieurs fois dans le tableau, on n’obtient que le premier résultat, et on ne sait pas qu’il existe d’autres résultats
  • La colonne du tableau parmi laquelle on recherche doit être située à gauche de la colonne résultat. Il n’est pas toujours possible ou souhaitable de déplacer les colonnes pour les besoins d’une formule.

Dans l’exemple ci-dessous, on recherche “Supermarché”. On obtient comme résultat le prix “3,00€” soit le premier résultat, mais on ne sait pas qu’il y a d’autres résultats possibles. De même on est incapables de chercher les Articles qui correspondent à notre recherche car la colonne Article est située à gauche de notre colonne de recherche, Magasin.

Objectif Query : mettre en place l’équivalent d’un rechercheV, mais avec résultats multiples.

La formule Query possède deux arguments minimum :

= Query ( Tableau source  ;  “Requête SQL” )

Ceci n’est bien sûr pas un tutoriel sur les Query en général (pour cela, se reporter à un potentiel futur cours dédié, ou à ma formation Sheets avancé qui dure 2h30) mais à l’application particulière de la Query à ce besoin.

Le tableau source est bien sûr ici notre liste de courses.

Query signifie “requête” en anglais. Une requête est en fait un ordre adressé à l’ordinateur en langue non pas française ni anglaise, mais SQL. On peut le traduire littéralement :

SELECT A                              WHERE B=’Supermarché’

Affiche les valeurs de la colonne A, lorsque la colonne B est égale à ‘Supermarché’

On obtient bien les résultats : tous les articles du supermarché. Faites le test avec Poissonnerie à la place de supermarché. Attention, si la valeur de la colonne B est un nombre, pas d’apostrophes (WHERE B=’Supermarché’ mais WHERE B=5)

Il reste trois problèmes à résoudre :

  • Le titre “Article” est de trop
  • ‘Supermarché’ est écrit directement dans la formule, alors qu’on souhaite le récupérer automatiquement dans la cellule A17 comme pour la rechercheV
  • Les résultats s’affichent en colonne au lieu de tenir dans une seule cellule
  1. Retirer le titre

Pour retirer le titre, il suffit de renommer la colonne A vide. Au lieu de ‘Article’ par défaut, on indique :

=> ‘’ (deux apostrophes qui se suivent, avec rien entre les deux). Pour renommer, on utilise le mot clef LABEL.

Les deux apostrophes ci-dessus ne contiennent rien, le résultat n’a donc plus de titre :

  1. Rendre automatique la récupération de l’article cherché

Avez-vous remarqué que la requête était un simple morceau de texte ? On appelle cela chaîne de caractères. Et une chaîne peut être écrite à la main entre guillemets, comme c’est le cas ici, mais aussi formée par formule.
Quelle est la partie de la requête qui doit changer selon la valeur en colonne A ? Le nom du magasin. Le reste ne change jamais.

On utilise  l’opérateur & pour coller plusieurs chaînes de caractères entre elles (le terme est concaténation). Ainsi si on colle la première partie (SELECT A WHERE B=’) avec le contenu de la cellule A17, puis qu’on les colle à nouveau à la dernière partie (‘ LABEL A ‘’), on rend automatique la récupération de l’article cherché :

  1. Assembler le résultat dans une cellule

Il est impossible d’étendre (faire glisser) la formule vers le bas si le résultat prend plusieurs cellules verticalement comme c’est le cas ici :

Sans oublier les $ à ajouter à la plage source $A$3:$D$13, on étend vers le bas. L’erreur #REF! est due au fait que le résultat qui comporte plusieurs cellules n’a pas la place de se développer.

Deux méthodes sont possibles :

  • Avec la formule transpose() nous pouvons développer horizontalement tous les résultats :
  • Avec la formule join ( “séparateur” ; résultat de la query ) nous assemblons dans une cellule tous les résultats séparés par le séparateur choisi :
  • On peut ensuite exploiter le résultat d’un join, en faisant l’inverse, c’est-à-dire en séparant les résultat : Split ( cellule ; “séparateur” )

4- Résumé & pour aller plus loin

  • RechercheV ( Élément cherché ; Tableau source ; N°colonne résultat ; Faux )
  • RechercheH ( Élément cherché ; Tableau source ; N°ligne résultat ; Faux )

Plus rarement utilisée, la recherche horizontale a un fonctionnement similaire à celui de la recherche verticale, mais de manière horizontale.

  • Query ( Tableau source  ;  “SELECT A WHERE B=’ ” & A17 & “ ‘ LABEL A ‘’ “ )
  • Query ( Tableau source  ;  “SELECT A WHERE B contains ’ ” & A17 & “ ‘ LABEL A ‘’ “ )

La variante contains permet de rechercher tous les éléments qui contiennent la valeur en A17, au lieu de seulement chercher ceux qui sont strictement égaux.

  • Join ( “Séparateur” ; Query(…) )
  • Split ( “Cellule contenant un résultat de Join()” ; “Séparateur” )
  • Transpose ( Tableau source ou query )

Cet article vous a plu ? N’hésitez pas à le partager sur les réseaux sociaux. Vous pouvez aussi rejoindre la communauté francophone Gsheets sur G+ avec votre compte G Suite (le service ne fonctionne plus depuis le 2/04/2019 avec les comptes Gmail).

Si vous avez trouvé une faute d’orthographe, veuillez nous en informer en sélectionnant le texte en question et en appuyant sur Ctrl + Entrée .

Notez cette information
[Total: 0 Average: 0]

2 comments

  1. Bon article, mais il est possible de passer plus simplement par la fonction FILTER plus simple à maîtriser, pour faire ce qui est proposé dans cet article avec la fonction QUERY (fonction très intéressante bien entendu, mais FILTER est plus pratique pour des cas “simples”).

  2. Bonjour Florent,
    Merci pour ce commentaire. En effet la fonction FILTER est plus simple, mais la fonction QUERY permet de répondre à tellement de besoin qu’elle peut remplacer de nombreuses autres fonctions. C’est le couteau suisse Google Sheets !
    Bonne journée,
    Thomas

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.

Share This

Rapport de faute d’orthographe

Le texte suivant sera envoyé à nos rédacteurs :