Remplacer les cellules vides par 0 dans un tableau croisé dynamique QUERY dans Google Sheets peut améliorer la lisibilité et l’interprétation des données. Cela peut être réalisé facilement en utilisant des fonctions modernes de Google Sheets telles que LET ou MAP.
Il est important de noter que vous n’avez pas besoin de comprendre pleinement la formule QUERY que vous avez utilisée pour appliquer cette astuce. Que vous ayez créé la formule vous-même ou non, cela n’a pas d’importance. Vous pouvez toujours remplacer les cellules vides par 0 dans le résultat du tableau croisé dynamique QUERY sans modifier directement la formule. Au lieu de cela, nous utiliserons un conteneur pour substituer les cellules vides par zéro.
Explorons un jeu de données d’exemple comprenant des colonnes de texte, de chiffres et de dates. Cela vous aidera à comprendre comment la formule gère les colonnes de texte ou de dates tout en remplaçant les cellules vides par 0.
Configuration de votre feuille d’exemple avec des données et une formule QUERY
Travaillons avec un jeu de données d’exemple minimal comprenant trois types de données: date, texte et nombre.
Dans les données d’exemple fournies (veuillez vous référer à l’image ci-dessous), la colonne A contient la date du paiement effectué, la colonne B contient l’article ou le type de dépense, et la colonne C contient les montants correspondants.
La formule QUERY suivante dans la cellule E1 génère un tableau croisé dynamique, organisant les dates de paiement dans les lignes, les éléments dans les colonnes et agrégeant les montants des paiements.
=QUERY(A1:C, "Select A, Sum(C) where A is not null group by A Pivot B", 1)
Remarquez les deux points verts (placés manuellement) dans la sortie du tableau croisé dynamique QUERY. Comment remplissons-nous ces cellules vides avec 0? Bien sûr, nous ne pouvons pas entrer 0 dans ces cellules car cela briserait la formule QUERY. Passons donc à nos solutions.
Voici une astuce pour remplacer les cellules vides par 0 dans un tableau croisé dynamique QUERY en utilisant la fonction MAP.
La fonction MAP est une fonction auxiliaire LAMBDA utile dans Google Sheets, qui nous permet de mapper chaque valeur d’un tableau vers une nouvelle valeur.
Dans ce contexte, le tableau est le résultat d’une formule QUERY, tel qu’un tableau croisé dynamique. En utilisant la fonction MAP, nous pouvons itérer à travers chaque valeur du tableau croisé dynamique et remplacer toutes les cellules vides par zéro.
Syntaxe:
MAP(array1, [array2, ...], lambda)
Formule générique:
=MAP(array1, lambda(valeur_de_cellule, SI(valeur_de_cellule="", 0, valeur_de_cellule)))
Il suffit de substituer array1 par votre formule QUERY spécifique.
Par exemple, dans mon cas, la formule pour remplacer les cellules vides par des valeurs 0 serait:
=MAP(QUERY(A1:C, "Select A, Sum(C) where A is not null group by A Pivot B", 1), lambda(valeur_de_cellule, SI(valeur_de_cellule="", 0, valeur_de_cellule)))
Remarque: La formule convertira les dates en valeurs de date. Par conséquent, sélectionnez les dates dans le tableau croisé dynamique et appliquez le format Nombre > Date.
Cette formule transforme efficacement le résultat QUERY spécifié, remplaçant toutes les cellules vides par 0 tout en laissant les autres valeurs inchangées.
Décortiquons la formule:
- QUERY(A1:C, « Select A, Sum(C) where A is not null group by A Pivot B », 1): cela sert de tableau ou de plage de valeurs que vous souhaitez manipuler.
- lambda: il s’agit d’une fonction définissant la transformation à appliquer à chaque élément du tableau.
- valeur_de_cellule: c’est un nom défini par l’utilisateur dans la fonction lambda, représentant chaque valeur de cellule dans le tableau.
- SI(valeur_de_cellule= » », 0, valeur_de_cellule): ce test logique SI est la fonction lambda. Il prend chaque valeur_de_cellule du tableau croisé dynamique QUERY en entrée et applique la logique suivante: SI(valeur_de_cellule= » », 0, valeur_de_cellule)
Une autre astuce est d’utiliser la fonction LET pour remplacer les cellules vides par 0 dans un tableau croisé dynamique QUERY.
La fonction LET facilite l’évaluation des expressions de formule en utilisant des arguments nommés déclarés.
Syntaxe:
LET(nom1, expression_valeur1, [nom2, ...], [expression_valeur2, ...], expression_formule)
Formule générique:
=LET(nom1, expression_valeur1, ArrayFormula(SI(nom1="", 0, nom1)))
Dans cette formule générique, remplacez expression_valeur1 par votre formule QUERY.
Par exemple, pour remplacer les cellules vides par zéro dans un tableau croisé dynamique QUERY, vous pouvez utiliser la formule suivante:
=LET(nom1, QUERY(A1:C, "Select A, Sum(C) where A is not null group by A Pivot B", 1), ArrayFormula(SI(nom1="", 0, nom1)))
Tout comme la formule MAP, ici aussi, vous devez formater les valeurs de date en dates dans le résultat du tableau croisé dynamique.
Décortiquons la formule:
- nom1: c’est le nom attribué à la formule QUERY (expression_valeur1). Vous pouvez choisir un nom plus significatif, comme « formule_query ».
- QUERY(A1:C, « SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B », 1): la formule (expression_valeur1) dont le résultat peut être référencé ultérieurement avec nom1 qui a été déclaré avant.
- ArrayFormula(SI(nom1= » », 0, nom1)): cette expression est évaluée à l’aide de la fonction LET. Elle vérifie si la valeur de nom1 est une chaîne vide. Si c’est le cas, elle renvoie 0; sinon, elle renvoie la valeur d’origine de nom1.
En conclusion, vous disposez maintenant de deux solutions pour remplacer les cellules vides par 0 dans un tableau croisé dynamique QUERY. N’hésitez pas à choisir l’une ou l’autre, car les deux formules sont simples et axées sur les performances. Il est important de noter que les deux formules convertiront les dates/les horodatages en valeurs de dates. Assurez-vous simplement d’appliquer le formatage approprié à partir du menu Format en sélectionnant ces valeurs de date.
BolaMarketing.com
