J’ai une formule matricielle pour obtenir le total inversé dans Google Sheets.
Lorsque vous avez vos données triées de la plus récente à la plus ancienne, vous trouverez cela très utile par rapport au total cumulé.
Prenons par exemple la production de blé en Australie de 2010 à 2019 (données provenant de Wikipedia).
Dans Google Sheets, nous pouvons organiser les données en deux colonnes comme indiqué ci-dessous.
Les colonnes A et B contiennent respectivement les années et les quantités de production de blé (en millions de tonnes métriques).
Ce qui est important ici, c’est que les données soient arrangées par ordre décroissant (de la plus récente à la plus ancienne) selon l’année dans la colonne A.
On peut voir les valeurs dans les colonnes de total cumulé C et D.
En quoi diffèrent-elles ?
Dans la colonne C, on peut voir le total cumulé calculé de haut en bas, tandis que dans la colonne D, le calcul est effectué de bas en haut.
À partir de la colonne D, on peut comprendre la production de blé cumulée jusqu’à cette année à partir de chaque ligne.
Comment calculer le total inversé dans Google Sheets comme indiqué dans la colonne D ci-dessus ?
Si vous préférez une formule non-matricielle, vous pouvez utiliser la formule suivante dans la cellule D2 et la faire glisser vers le bas jusqu’à la ligne 11.
=somme($B$2:$B)-somme(formulematricielle(n($B$1:B1)))
Mais j’ai une formule matricielle.
Formule matricielle pour le total inversé dans Google Sheets
Nous utiliserons ici une formule matricielle SOMME.SI.
Syntaxe :
formulematricielle(SOMME.SI(plage, critère, [plage_somme]))
Nous avons la plage_somme pour calculer la somme cumulative inverse, qui est B2:B. Que dire de la plage et du critère ?
Vous pouvez les trouver dans la formule ci-dessous.
Videz toute la colonne D car nous voulons insérer une formule matricielle dans la cellule D1, ce qui nécessite une colonne vide pour fonctionner sans l’erreur #REF.
La formule ci-dessous est pour la cellule D1.
={"Total inversé";formulematricielle(Si(B2:B="",,SOMME.SI(trier(ligne(A2:A),1,0),"<=("&trier(ligne(A2:A),1,0)&")",B2:B)))}
La formule matricielle ci-dessus renverra le total inversé dans Google Sheets pour la plage B2:B.
Elle a un avantage certain par rapport à sa contrepartie (non-matricielle).
Elle utilise une plage ouverte B2:B. Ainsi, elle renverra le total inversé dans toutes les lignes de la colonne D. Cependant, il doit y avoir des valeurs dans la colonne B. Les lignes vides seront ignorées.
Apprenez-en plus sur la plage et le critère utilisés dans la formule à partir de la partie d’explication ci-dessous.
Mise à jour : Cela fonctionnera également ={"Total inversé";formulematricielle(Si(B2:B="",,SOMME.SI(ligne(A2:A),"<=("&ligne(A2:A)&")",B2:B)))}. Mais l’explication sera basée sur la formule ci-dessus.
Explication de la formule
Supprimons la chaîne indésirable de la formule, c’est-à-dire l’en-tête « Total inversé », et rendons les plages fermées. Utilisons ensuite une colonne auxiliaire.
Ainsi, nous pouvons raccourcir la formule SOMME.SI et la rendre facilement lisible.
formulematricielle(si(B2:B11="",,SOMME.SI(C2:C11,"<=("&C2:C11&")",B2:B11)))
Nous pouvons diviser la formule en trois parties.
Partie 1
formulematricielle – Pour aider la fonction SOMME.SI à renvoyer un résultat matriciel. Le SI l’exige aussi.
formulematricielle(
Partie 2
SI – Pour limiter la sortie aux lignes contenant des valeurs dans la colonne B.
s'il vous plaît(B2:B11="",,
Partie 3
SOMME.SI – Pour renvoyer le total inversé dans Google Sheets.
SOMME.SI(C2:C11,"<=("&C2:C11&")",B2:B11)
Note : La plage de cellules C2:C11 est une plage auxiliaire temporaire pour l’explication de la formule. Veuillez vous référer à l’image ci-dessus. Dans la formule principale, nous avons utilisé trier(ligne(A2:A),1,0) à la place.
Cette formule nécessite une explication détaillée pour comprendre comment SOMME.SI renvoie le total inverse dans Google Sheets.
Testons la formule dans une ligne particulière, par exemple, dans la ligne n°5.
=SOMME.SI(C2:C11,"<=("&C5&")",B2:B11)
La formule renvoie la production totale de blé en Australie de 2010 à 2016 (cellules en surbrillance).
Parce que le critère, c’est-à-dire C2:C11<=C5, dans la colonne C correspond aux lignes en surbrillance.
Ressources :
- Running Count dans Google Sheets – Exemples de formules.
- Comment calculer le solde courant dans Google Sheets.
- Formule matricielle pour un total cumulatif conditionnel dans Google Sheets.
- Somme, nombre, somme cumulée de valeurs séparées par des virgules dans Google Sheets.
- Nombre cumulatif de valeurs distinctes dans Google Sheets (Comment faire).
- Solde cumulatif pour chaque paiement dans Google Sheets.
Cette astuce simple vous aidera à obtenir un total inversé dans Google Sheets. Utilisez cette formule matricielle pour gagner du temps et obtenir les résultats dont vous avez besoin. Si vous souhaitez en savoir plus sur les fonctionnalités avancées de Google Sheets, consultez Crawlan.com pour des astuces et des tutoriels supplémentaires. N’hésitez pas à partager cette astuce avec vos amis, car tout le monde peut en bénéficier !
