Si vous répondez aux deux critères suivants, il est facile de coder une formule de tableau pour réinitialiser le total cumulé à chaque changement d’année dans Google Sheets.
Quels sont ces deux critères ?
- Vous devez trier le tableau en fonction de la colonne de date par ordre croissant ou décroissant.
- Aucune cellule vide ne doit être présente dans la colonne de date.
Tout comme notre total cumulé conditionnel, nous pouvons également utiliser MMULT ici.
Mais, comme vous le savez peut-être, c’est une formule gourmande en ressources et peut se rompre lorsque les données sont importantes.
Pour réinitialiser le total cumulé à chaque changement d’année, nous pouvons utiliser une formule basée sur SUMIF dans Google Sheets. Cette formule semble être plus respectueuse des ressources.
Données d’exemple :
Les deux premières colonnes du tableau ci-dessous contiennent les données d’exemple, et la troisième colonne contient notre réponse de formule attendue.
| Date | Montant | Total cumulé qui redémarre à chaque changement d’année |
|---|---|---|
Codons maintenant la formule de tableau qui réinitialise le total cumulé à chaque changement d’année dans Google Sheets.
Réinitialisez le total cumulé à chaque changement d’année (Formule de tableau)
Les deux premières colonnes des données d’exemple se trouvent en A1:B, où A1 et B1 contiennent les étiquettes de champ Date et Montant.
Cela signifie que nous avons besoin du calcul à partir de la ligne n°2, donc la formule (de tableau) doit être placée dans la cellule C2.
Nous avons les données triées de A à Z, c’est-à-dire par ordre croissant. Nous pouvons donc utiliser la formule suivante dans la cellule C2 pour réinitialiser le total cumulé à chaque changement d’année dans A2:A.
Pour les dates triées de A à Z (Asc) :
=ArrayFormula(if(len(A2:A),sumif(row(B2:B), "<="&row(B2:B), B2:B)-sumif(year(A2:A), "<"&year(A2:A), B2:B),))Permettez-moi de répondre à deux questions rapides qui pourraient survenir dans l’esprit d’un utilisateur.
Est-ce que cela inclut les enregistrements futurs après la ligne n°9 ?
Oui ! Cela les inclura.
Pourquoi la formule renvoie-t-elle une erreur #REF ?
Si c’est le cas, sélectionnez C3:C et appuyez sur le bouton de suppression.
Parce que la formule utilisée pour réinitialiser le total cumulé à chaque changement d’année est une formule de tableau.
Elle nécessite des cellules vides sous la cellule C2 pour s’étendre vers le bas. Sinon, elle essaiera de remplacer les données existantes et peut provoquer l’erreur mentionnée ci-dessus.
Si les dates de la colonne A sont triées de Z à A (par ordre décroissant), la formule de réinitialisation du total cumulé à chaque changement d’année mentionnée ci-dessus ne fonctionnera pas correctement.
Vous devez apporter une modification à la formule, c’est-à-dire remplacer « < » par « > ». La voici !
Pour les dates triées de Z à A (Desc) :
=ArrayFormula(if(len(A2:A),sumif(row(B2:B), "<="&row(B2:B), B2:B)-sumif(year(A2:A), ">"&year(A2:A), B2:B),))Explication de la formule
Prenons en compte les données triées de A à Z et la formule pour l’explication. Cela vous aidera à comprendre l’autre formule, c’est-à-dire les données triées dans l’ordre Z-A également.
En fait, il y a deux parties principales dans la formule, les voici (surlignées en rouge et vert).
=ArrayFormula( if(len(A2:A), sumif(row(B2:B), "<="&row(B2:B), B2:B)- sumif(year(A2:A), "<"&year(A2:A), B2:B), ) )
Elles renvoient les totaux cumulés (CUSUM) de la colonne B de deux manières différentes. J’en viendrai à cela.
Partie 1 (se référer à la colonne E sur l’image ci-dessous) :
Sommer la colonne B si les numéros de ligne sont inférieurs ou égaux aux numéros de ligne. Elle renvoie donc la somme d’une séquence de nombres dans A2:A à chaque changement de ligne.
=ArrayFormula(sumif(row(B2:B), "<="&row(B2:B), B2:B))
Veuillez noter que nous avons besoin de la fonction ArrayFormula pour étendre le résultat. Nous devons donc l’utiliser avec chaque partie comme ci-dessus lors des tests.
Partie 2 (se référer à la colonne F sur l’image ci-dessous) :
Sommer la colonne B si les années sont inférieures aux années. Elle renvoie donc la somme d’une séquence de nombres dans B2:B à chaque changement d’année dans la colonne A.
=ArrayFormula(sumif(year(A2:A), "<"&year(A2:A), B2:B))
Partie 1 – Partie 2 = formule finale qui réinitialise le total cumulé en fonction du changement d’année dans la colonne de date.
Nous avons également utilisé if(len(A2:A) pour supprimer les valeurs des lignes vides après la ligne n°9.
Ce sont les formules de tableau respectueuses des ressources pour réinitialiser le total cumulé à chaque changement d’année dans Google Sheets.
Merci de rester avec nous. Profitez-en !
Ressources
- Reverse Running Total in Google Sheets (Formule de tableau).
- Running Count in Google Sheets – Exemples de formules.
- Comment calculer le solde cumulé dans Google Sheets.
- Valeurs maximales en cours d’exécution dans Google Sheets (Formule de tableau incluse).
- Trouver la valeur minimale en cours d’exécution dans Google Sheets.
- Calcul de la moyenne en cours d’exécution dans Google Sheets (Formule de tableau).
