Est-il possible d’obtenir des barres horizontales sur les côtés gauche et droit d’une ligne verticale (virtuelle) dans un graphique à barres SPARKLINE dans Google Sheets (côté gauche pour les valeurs négatives et côté droit pour les valeurs positives) ?
Oui ! Cet article explique comment utiliser la fonction SPARKLINE pour créer un graphique à barres positives et négatives dans les cellules de Google Sheets.
Formulaire non-array SPARKLINE pour créer un graphique à barres positives et négatives dans Google Sheets
En trois étapes, nous pouvons coder un graphique à barres SPARKLINE avec des barres positives et négatives dans Google Sheets.
Il est possible que nous ayons besoin d’une étape supplémentaire pour convertir la formule codée en une formule array, qui se développe vers le bas.
La dernière étape est possible grâce à la disponibilité des fonctions LAMBDA dans Google Sheets.
La fonction SPARKLINE a deux arguments : les données et les options.
Syntaxe : SPARKLINE(data, [options])
Dans ce petit graphique intégré, la largeur des barres horizontales sera proportionnelle aux valeurs absolues qu’elles représentent.
En d’autres termes, la fonction utilise les valeurs absolues de la plage de cellules spécifiée.
Ainsi, les valeurs négatives et positives auront le même impact sur le graphique à barres.
Formule de l’étape 1 (dans B2, copiée vers le bas) :
=ArrayFormula(SPARKLINE(A2,{"charttype","bar";"max",max(abs($A$2:$A))}))
Vous avez uniquement la possibilité de spécifier deux couleurs (au choix) en fonction de leurs signes, par exemple le rouge pour les valeurs négatives et le vert pour les valeurs positives.
Formule de l’étape 2 (dans C2, copiée vers le bas) :
=ArrayFormula(SPARKLINE(A2,{"charttype","bar";"max",max(abs($A$2:$A));"color1",if(A2>0,"green","red")}))
Comment déplacer les barres horizontales sur les côtés gauche et droit d’une barre verticale virtuelle en fonction des points de données positifs et négatifs ?
Formule de l’étape 3 (dans D2, copiée vers le bas) :
=sparkline({if(A2>0,min($A$2:$A),A2-min($A$2:$A)),A2},{"charttype","bar";"max",max(0,$A$2:$A)-min(0,$A$2:$A);"color1","white";"color2",if(A2>0,"green","red")})
Explication de la formule de l’étape 3
La formule SPARKLINE ci-dessus crée des barres positives et négatives proportionnelles aux valeurs qu’elles représentent.
Les valeurs négatives et positives seront alignées sur les côtés gauche et droit d’une ligne (virtuelle) verticale.
Contrairement aux formules des étapes 1 et 2, la formule de l’étape 3 renvoie un graphique à barres bicolore – blanc, rouge/vert.
C’est la partie clé de la formule SPARKLINE pour les barres positives et négatives dans Google Sheets.
Points de données SPARKLINE pour les barres positives et négatives :
{if(A2>0,min($A$2:$A),A2-min($A$2:$A)),A2}
Blanc – La partie logique cyan-bleu-gris des données renvoie le point de données 1 pour la barre blanche.
Rouge/vert – La partie verte claire cyan des données renvoie le point de données 2 pour la barre rouge/verte.
Pour comprendre ce que la formule retourne dans chaque ligne, veuillez consulter le tableau ci-dessous.
Options Max SPARKLINE pour les barres positives et négatives :
La valeur maximale le long de l’axe horizontal de tous les graphiques à barres sera de 20, c’est-à-dire max(0,$A$2:$A)-min(0,$A$2:$A).
Tableau #1
Référence de cellule et valeur (A2:A13) – Point de données 1 (Blanc) – Point de données 2 (Rouge/Vert) – Valeur maximale
Note : La formule SPARKLINE traitera toutes les valeurs des points de données 1 et 2 comme des valeurs absolues.
Si les valeurs en A2 sont inférieures à 0, le total des points de données 1 (blanc) et 2 (rouge) sera de 16, ce qui est égal à la valeur minimale absolue dans la plage A2:A13. Cela s’applique à A3, A4, etc.
Si les valeurs en A2 sont supérieures à 0, le total des points de données 1 (blanc) et 2 (vert) sera de 16+ (blanc=16, le reste sera vert). Cela s’applique également à A3, A4, etc.
Le max (20) dans la partie des options aligne les barres.
De cette manière, nous pouvons utiliser la fonction SPARKLINE pour créer des graphiques à barres positives et négatives dans les cellules de Google Sheets.
Formule array SPARKLINE et fonctionnommée pour le graphique à barres positives et négatives
Formule array :
Convertissons la formule de l’étape 3 en une formule array.
Vous pouvez utiliser la fonction BYROW ou MAP pour cela. J’utilise la seconde ici.
Formule générique :
=map(A2:A,lambda(r,non_array_formula))
Remplacez non_array_formula par la formule de l’étape 3 et toutes les références de cellules A2 dans cette formule par le nom r.
Formule array SPARKLINE pour le graphique à barres positives et négatives :
=map(A2:A,lambda(r,sparkline({if(r>0,min($A$2:$A),r-min($A$2:$A)),r},{"charttype","bar";"max",max(0,$A$2:$A)-min(0,$A$2:$A);"color1","white";"color2",if(r>0,"green","red")})))
Cette formule de graphique à barres positives et négatives SPARKLINE se propagera vers le bas. Donc, videz d’abord la plage B2:B, puis saisissez-la dans la cellule B2.
Fonction nommée personnalisée
J’ai déjà expliqué les étapes pour créer une fonction nommée dans Google Sheets.
Nous pouvons copier la formule de tableau SPARKLINE positive et négative ci-dessus et la convertir en une fonction nommée SPARKLINE_NEGATIVE_BAR().
Syntaxe : SPARKLINE_NEGATIVE_BAR(plage)
Selon nos données d’exemple ci-dessus, la formule SPARKLINE_NEGATIVE_BAR personnalisée sera la suivante :
=SPARKLINE_NEGATIVE_BAR(A2:A)
Assurez-vous d’avoir vidé la colonne pour permettre l’expansion de la formule vers le bas.
Ressources :
