Ce que vous allez découvrir
- Exercice: Construction d'une formule complexe avec la méthode de l'oignon
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 …
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets- Tutos connexes
- Plus de l'auteur
Les formules françaises sont un peu différentes
Cet avis vous a été utile ?
Question annexe, j’ai plusieurs page html avec la même forme. Peuxt-on faire un IMPORTHTML sur plusieurs URL ?
Cet avis vous a été utile ?