Google Sheets : utilisez la méthode de l’oignon dans vos fonctions

Vous souhaitez aborder des formules complexes dans Google Sheets ?

Utilisez la méthode de l’oignon !

Des formules complexes? La méthode de l’oignon? 

Imaginez que les formules complexes dans Google Sheets ressemblent beaucoup à des oignons, chacune des couches représentant une fonction qui vient s’ajouter à celle de base..

Et les oignons vous font parfois pleurer. 

C’est pourquoi, cette méthode d’approche des  formules complexes est intéressante. Si vous construisez des formules complexes, je vous préconise cette méthode qui consiste à construire  vos formules en une série d’étapes et où chaque étape exécute une nouvelle fonction.

Dans l’exemple suivant, vous allez voir comment découper étape par étape, et ajouter des couches à notre formule de base, afin d’en extraire correctement les données voulues.

Exercice: Construction d’une formule complexe avec la méthode de l’oignon

Examinons les données d’importation du tableau de cette page Wikipedia sur les plus grandes villes en fonction de leur population .

Tableau de données de pays Wikipedia

Étape 1

Ouvrez une nouvelle feuille Google (astuce : tapez simplement Sheets.new dans la fenêtre de votre navigateur).

Étape 2

Dans cette étape nous allons créer une fonction IMPORTHTML, nous permettant de récupérer la table des villes à partir d’une page du site Wikipedia. Url wikipedia
Dans la cellule A2:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2)

Étape 3

Nous utiliserons  la fonction INDEX pour saisir uniquement la colonne de population.

Selon notre méthode, nous insérons cette étape suivante dans une nouvelle cellule, la cellule J2, à droite des données existantes, dans cet exemple:

=INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4)

J’ai mis en évidence la nouvelle étape en rouge soit  l’ajout de la fonction INDEX.

En regardant de près la fonction INDEX, vous verrez que nous avons laissé l’argument ‘data’ vide, à savoir:

INDEX(data, ,4) afin d’afficher  la colonne entière.

Étape 4

A cette étape la colonne de population nous renvoie des données brutes, que nous ne voudrions pas afficher, nous allons donc utiliser les Expressions Régulières, afin de régler ce problème.
En informatique, une expression régulière est une chaîne de caractères, qui décrit, selon une syntaxe précise, un ensemble de chaînes de caractères possibles.

A l’aide de la fonction REGEXEXTRACT, nous n’allons extraire que les chiffres et les « , » des données, avant les citations entre parenthèses en rajoutant une couche de plus à notre formule de base :

=REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+")

Si vous avez cette erreur,  # N / A… 🤔, pas d’inquiétudes nous allons résoudre ce problème à l’étape suivante.

Étape 5

Transformez ceci avec un petit ArrayFormula et obtenez les valeurs de la colonne de population sans les chiffres entres crochets superflus !

=ArrayFormula(REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+"))

Vous avez cependant encore 1 problème à résoudre:
Vous devez  encore corriger l’erreur de l’en-tête de colonne # N / A 

Étape 6

Utilisez la fonction IFERROR pour corriger cette erreur embarrassante # N / A en haut de l’en-tête de notre colonne et remplacez le #N/A par le mot «Population»:

=ArrayFormula(IFERROR(REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+")*1,"Population"))

SUPER, nous avons maintenant notre colonne de population en chiffres et notre entête correct .

Étape 7

Choisissez les autres colonnes que vous voulez afficher, en encapsulant la fonction IMPORTHTML avec une fonction QUERY .

Notez que vous devez utiliser la notation Col1 plutôt que la lettre de colonne dans votre instruction Select, car vous imbriquez une autre fonction en tant que source de données dans la fonction QUERY.

Il est préférable d’ailleurs d’utiliser, la fonction QUERY plutôt que la fonction INDEX car nous voulons renvoyer plusieurs colonnes cette fois, ce que la fonction INDEX ne peut pas faire.

=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),"SELECT Col2, Col8",1)

Étape 8

Tout ce qui reste à faire est de joindre ces deux plages en utilisant la notation des accolades, comme ceci:

={
QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),"SELECT Col2,Col8",1),
ArrayFormula(IFERROR(REGEXEXTRACT(INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_cities_proper_by_population","table",2),,4),"[0-9,]+") * 1,"Population"))
}

Et voilà c’est terminé !!!

En conclusion

La méthode de l’oignon  vous permet de créer des formules complexes de manière plus rapide.

Et si vous êtes confronté à devoir déchiffrer  une formule impossible, suivez cette méthode. Décortiquez  les couches une par une, afin d’en comprendre le fonctionnement.

Vous serez étonné de la rapidité avec laquelle votre compréhension des formules complexes sera plus aisée. Vous rencontrerez et comprendrez de nouvelles fonctions dont vous n’aviez jamais entendu parler auparavant. De plus, vous découvrirez toutes sortes d’astuces secrètes avec les formules existantes.

Qui sait, vous pourriez même pleurer des larmes de joie …