Dans cet article, nous allons explorer comment coder une formule de tableau pour un total cumulé conditionnel (somme cumulative conditionnelle) dans Google Sheets. Nous inclurons également un guide sur l’incorporation d’un total cumulé par groupe dans ce tutoriel.
Auparavant, pour le total cumulé de tableau (également appelé CUSUM), nous étions limités à l’utilisation de fonctions telles que SOMME.SI ou MMULT dans Google Sheets, tandis qu’Excel permettait l’utilisation de MMULT.
Cependant, jusqu’à récemment, la seule fonction adaptée à une formule de tableau pour le total cumulé conditionnel dans Google Sheets, selon ma compréhension, était MMULT. Maintenant, avec les solutions émergentes, les fonctions Lambda offrent des possibilités supplémentaires.
L’objectif principal de cet article est la formule de tableau pour le total cumulé conditionnel (CUSUM) dans Google Sheets.
J’ai présenté deux types d’exemples.
Dans un exemple, j’illustrerai le total cumulé par groupe, en indiquant des calculs CUSUM séparés pour chaque groupe ou valeur unique.
L’autre exemple montre comment exclure des valeurs spécifiques du total cumulé (CUSUM).
De plus, nous pouvons résoudre ces deux problèmes à l’aide des fonctions MMULT ou Lambda. Commençons par MMULT.
Total cumulé par groupe dans Google Sheets – MMULT
Fonction clé : Fonctionne avec des données triées et non triées.
Nous avons des données d’exemple dans les cellules A2:B, où A2:B2 contient les en-têtes. Pour accéder aux données d’exemple, veuillez reproduire ma feuille d’exemple en cliquant sur le bouton ci-dessous.
Créons la formule CUSUM conditionnelle dans la cellule C3, plus précisément dans la colonne « Total cumulé séparé pour chaque groupe ».
Il y a trois groupes ou valeurs uniques dans l’ensemble de données ci-dessus (reportez-vous à la colonne A) : Jan, Fév et Mar.
Bien que la colonne de groupe A soit triée, il convient de noter que même si les données ne sont pas triées par groupe comme indiqué ci-dessus, ma formule MMULT fonctionnera toujours. En d’autres termes, les noms des mois peuvent être dans n’importe quel ordre.
Formule :
=ARRAYFORMULA(IF(LEN(A3:A), MMULT( N(ROW(A3:A)>=TRANSPOSE(ROW(A3:A)))*N(A3:A=TRANSPOSE(A3:A)), N(B3:B) ),))
Explication de la formule
Remarque : La formule utilise les plages A3:A et B3:B (plages ouvertes). Nous testerons la formule dans A3:A10 et B3:B10 (plages fermées).
La formule de total cumulé conditionnel (par groupe) ci-dessus dans Google Sheets comprend trois parties.
Les deux premières parties forment la matrice 1, et la troisième partie forme la matrice 2.
Syntaxe MMULT : MMULT(matrice1, matrice2)
Les conditions doivent être appliquées dans la matrice 1. C’est ce que j’ai fait, et j’ai expliqué le processus ci-dessous.
PARTIE 1 (la première condition dans la formule de total cumulé)
Nous visons à calculer un total cumulé avec des conditions spécifiques. Le segment de formule suivant génère une matrice à cette fin.
N(ROW(A3:A10)>=TRANSPOSE(ROW(A3:A10)))
Cette formule PARTIE 1, lorsqu’elle est utilisée avec ArrayFormula, produit les valeurs de matrice suivantes.
Laissez-moi expliquer cette formule :
La fonction ROW(A3:A10) (en utilisant ArrayFormula) produit des nombres de séquence verticaux de 3 à 10.
Lorsque nous transposons cette formule en utilisant TRANSPOSE(ROW(A3:A10)) (avec ArrayFormula), les nombres sont renvoyés dans un arrangement horizontal.
La formule vérifie ensuite si chaque valeur dans la séquence de nombres verticaux est égale à sa valeur transposée (nombres de séquence horizontaux) et renvoie VRAI ou FAUX.
La fonction N convertit VRAI en 1 et FAUX en 0, comme illustré dans l’image ci-dessus.
PARTIE 2 (la deuxième condition dans la formule de total cumulé)
Nous visons à calculer un total cumulé par groupe, et cette partie nous aide à spécifier ce groupe.
N(A3:A10=TRANSPOSE(A3:A10))
Cette formule PARTIE 2, lorsqu’elle est utilisée avec ArrayFormula, produit les valeurs de matrice suivantes.
La formule teste si les valeurs dans A3:A10 sont égales aux valeurs transposées dans A3:A10 et renvoie VRAI ou FAUX. J’ai converti ces valeurs booléennes en 1 ou 0 (nombres) à l’aide de la fonction N.
Ainsi, Partie 1 * Partie 2 est égal à la matrice 1 dans le MMULT, ce qui est crucial pour obtenir le total cumulé conditionnel dans Google Sheets.
N(ROW(A3:A10)>=TRANSPOSE(ROW(A3:A10)))*N(A3:A10=TRANSPOSE(A3:A10))
PARTIE 3
La matrice 2 est la plage B3:B10 elle-même.
N(B3:B10)
Dans la matrice 2, la fonction N a pour but de ne pas convertir VRAI ou FAUX en 1 ou 0, car il n’y a pas de valeurs VRAI ou FAUX dans la plage.
Ici, elle convertit les cellules vides, le cas échéant, en 0 pour éviter les erreurs dans MMULT (puisque les paramètres 1 et 2 de MMULT nécessitent des valeurs numériques).
L’ensemble de la formule est enveloppé dans une combinaison IF et LEN. Elle vérifie si la longueur de la cellule correspondante dans la colonne A est supérieure à 0. Si c’est vrai, elle calcule le résultat MMULT ; sinon, elle renvoie un résultat vide.
Cela conclut l’explication du total cumulé par groupe, un type de total cumulé conditionnel dans Google Sheets.
Total cumulé par groupe dans Google Sheets – BYROW
Fonction clé : Fonctionne avec des données triées et non triées.
La formule MMULT ci-dessus peut entraîner des problèmes de performance avec de grands ensembles de données.
Voici une solution alternative pour un total cumulé par groupe dans Google Sheets en utilisant la fonction BYROW.
Insérez la formule suivante dans la cellule C3, qui se développera vers le bas :
=BYROW(A3:A, LAMBDA(r, IF(r="", ,SUM(FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r))))))
Remarque : N’hésitez pas à remplacer BYROW par MAP dans la formule ci-dessus.
Décomposition de la formule
FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r)) : La fonction FILTER filtre B3:B où A3:A est égal à r, et le numéro de ligne de A3:A est inférieur ou égal au numéro de ligne de r.
Ici, r représente la cellule actuelle dans chaque ligne de A3:A. Dans la première ligne, A3:A=r sera A3:A=A3, dans la deuxième ligne A3:A=r sera A3:A=A4, et ainsi de suite.
La même logique s’applique à ROW(A3:A) <= ROW(r). Ici aussi, r sera A3, A4, etc., pour chaque ligne. La fonction BYROW permet de parcourir chaque ligne de A3:A comme indiqué ci-dessus.
IF(r="", ,SUM(FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r)))) : La fonction IF retourne la somme des valeurs filtrées dans chaque ligne si r n’est pas vide.
Total cumulé conditionnel dans Google Sheets – MMULT
Commençons par de nouvelles données d’exemple dans la plage A2:B (vous pouvez les obtenir dans ma feuille d’exemple partagée ci-dessus).
Dans les données d’exemple (répartition mensuelle des dépenses), nous voulons appliquer une formule de tableau de total cumulé conditionnel dans la cellule C3.
Si nous insérons la formule de total cumulé standard dans la cellule C3 sans condition, elle renverrait une somme cumulative (CUSUM) incorrecte.
=ARRAYFORMULA(IF(LEN(A3:A), MMULT( IF(ROW(B3:B)>=TRANSPOSE(ROW(B3:B))=TRUE, 1, 0), N(B3:B) ),))
Remarque : Pour comprendre cette formule, vous pouvez vous référer aux explications pour la PARTIE 1 et la PARTIE 3 de notre exemple de formule de total cumulé par groupe. La Partie 1 correspond à la matrice 1, et la Partie 3 correspond à la matrice 2 dans MMULT.
Je souhaite exclure les lignes « Total » et la ligne de compte (2. Coût administratif) de la somme en cours (somme cumulative).
Ajout de conditions à la formule de tableau de total cumulé
Modification 1 (pour exclure les lignes « Total » et les sous-titres du résultat) :
Dans la formule fournie ci-dessus, remplacez :
IF(LEN(A3:A)
par :
IF((A3:A<>"Total")*(NOT(ISNUMBER(LEFT(A3:A,1)*1)))
Cette modification vérifie si A3:A ne contient pas la chaîne « total » et que le premier caractère de la chaîne n’est pas un nombre. Si le premier caractère est un nombre, cela signifie que la ligne contient un sous-titre que nous voulons exclure.
Modification 2 (pour exclure les valeurs de la ligne « Total » de la somme cumulative) :
Remplacez la matrice 2, qui est :
N(B3:B)
par :
IF(A3:A<>"Total", N(B3:B), 0)
Voici la formule tant attendue pour le total cumulé conditionnel à utiliser dans la cellule C3.
=ARRAYFORMULA(IF((A3:A<>"Total")*(NOT(ISNUMBER(LEFT(A3:A,1)*1))), MMULT( IF(ROW(B3:B)>=TRANSPOSE(ROW(B3:B))=TRUE, 1, 0), IF(A3:A<>"Total", N(B3:B), 0) ),))
Total cumulé conditionnel dans Google Sheets – SCAN
Ici aussi, l’objectif de cette alternative à MMULT est d’améliorer les performances.
Vous pouvez entrer la formule suivante dans la cellule C3 :
=ARRAYFORMULA(IF((A3:A="Total")+(B3:B=""), ,SCAN(0, A3:A, LAMBDA(a, v, IF(v="Total", a, OFFSET(v, 0,1 )+a)))))
Cette formule utilise la fonction SCAN dans Google Sheets pour des calculs itératifs. Voici l’explication :
- SCAN(0, A3:A, LAMBDA(a, v, …)) : La fonction SCAN itère sur les valeurs de la plage A3:A, en appliquant la fonction Lambda pour chaque élément. La fonction Lambda prend deux paramètres : a (accumulateur) et v (valeur actuelle).
- IF(v= »Total », a, OFFSET(v, 0, 1)+a) : Il s’agit de la logique principale de la fonction Lambda. Elle vérifie si la valeur actuelle (v) de la ligne est égale à « Total ». Si c’est vrai, elle renvoie l’accumulateur (a), ce qui permet de sauter la ligne « Total ». Si ce n’est pas le cas, elle ajoute la valeur actuelle (v) dans la colonne suivante (OFFSET(v, 0, 1)) à l’accumulateur (a).
Ainsi, la formule entière calcule efficacement un total cumulé, en excluant les lignes où la valeur de la colonne A est « Total ».
SCAN renvoie les valeurs dans toutes les lignes de la matrice même si elle n’utilise pas les valeurs de la ligne « Total » pour les calculs. Par conséquent, nous avons enveloppé la formule avec un test logique IF pour exclure les valeurs de ces lignes.
Voilà, vous pouvez maintenant ajouter d’autres conditions/critères à la somme en cours. Merci de nous avoir suivi, profitez-en !
Ressources
- Running Total with Monthly Reset in Google Sheets (Array Formula).
- Reset Running Total at Every Year Change in Google Sheets (SUMIF Based).
- Running Count of Multiple Values in a List in Google Sheets.
- Running Count in Google Sheets – Formula Examples.
- How to Calculate Running Balance in Google Sheets.
