Dans Google Doc Sheets, dans une plage triée, vous pouvez utiliser ma formule de tableau pour additionner la colonne B lorsque la valeur dans la colonne A change. Bien sûr, vous pouvez remplacer les colonnes A et B par d’autres colonnes de votre choix. Vous pouvez également utiliser des colonnes éloignées.
Je vais vous présenter deux formules de tableau qui additionnent une colonne lorsque la valeur dans une autre colonne change.
La formule de tableau régulière pour sommer la colonne B lorsque la valeur dans la colonne A change
La formule suivante calcule la somme des cellules jusqu’à ce qu’une valeur change dans une autre colonne. Il suffit d’entrer cette formule dans la cellule C2. Elle couvre les colonnes A et B entières.
=iferror(ArrayFormula(vlookup(row(A2:A),{if(len(A2:A),lookup(unique(A2:A),A2:A,row(A2:A))),if(len(A2:A),sumif(A2:A,unique(A2:A),B2:B))},2,0)))Explication de la formule
Plutôt que de donner une explication détaillée de la formule, je vous fournis la logique de la formule. Ce n’est pas obligatoire de la suivre.
En fait, la formule Maître #1 ci-dessus est une combinaison de formule qui agit comme une formule VLOOKUP.
Voici les arguments de la formule Vlookup utilisés dans la formule Maître #1.
search_key:
La formule row(A2:A), qui est la clé de recherche, renvoie les numéros de ligne à partir de 2, 3, 4, 5… en séquence. Lorsque vous testez cette formule ROW, vous devez l’envelopper dans la formule de tableau.
=arrayformula(row(A2:A))range:
Les formules soulignées en rouge et en vert sont la plage dans la fonction Vlookup. Elles forment une plage à deux colonnes car je les ai placées entre accolades dans la formule Maître #1. Pour mieux comprendre ce qu’elles renvoient, je les ai saisies dans les cellules F2 et H2.
F2:
=ArrayFormula(if(len(A2:A),lookup(unique(A2:A),A2:A,row(A2:A))))Cette formule renvoie le numéro de ligne des changements de valeur dans la colonne A.
H2:
=ArrayFormula(if(len(A2:A),sumif(A2:A,unique(A2:A),B2:B)))C’est une formule Sumif qui regroupe la colonne A et renvoie la somme. Ensemble, ces deux formules forment la plage pour Vlookup, virtuellement comme suit.
index:
L’index est le numéro de colonne dans la plage. J’ai utilisé 2 comme numéro d’index car la colonne 2 dans la plage (voir image #5 ci-dessus) contient la somme de chaque changement de valeur.
En enveloppant cette formule avec Iferror, je peux masquer l’erreur #N/A. De cette manière, nous pouvons utiliser une formule de tableau pour sommer la colonne B lorsque les valeurs dans la colonne A changent dans Google Sheets.
La solution Lambda pour sommer la colonne B lorsque la valeur change dans la colonne A
=ArrayFormula(ifna(lambda(x,y,byrow(x,lambda(r,if(row(r)=xmatch(r,x,0,-1)+1, sum(filter(y,x=r)),))))(A2:A,B2:B)))Nous pouvons utiliser la formule ci-dessus comme une alternative à notre formule Maître #1. Elle est assez facile à comprendre et permet de sommer la colonne B lorsque la valeur change dans la colonne A.
Cela fonctionne de la manière suivante :
Le FILTER filtre la colonne B si la valeur de la colonne A est A2 (critère). La fonction SUM la totalise. C’est la première valeur de changement de colonne à placer dans la cellule C4 comme indiqué sur l’image #4.
Pour trouver la ligne où la mettre, nous avons utilisé la fonction XMATCH, qui renvoie la dernière ligne de la clé de recherche (critère de filtre) dans A2. À l’aide de IF, nous plaçons ce total dans cette ligne.
Nous avons utilisé BYROW pour répéter le processus ci-dessus avec chaque critère dans FILTER (A2, A3, A4, …).
Le rôle de LAMBDA ici est de raccourcir la formule.
Voilà, maintenant vous savez comment sommer la colonne B lorsque les valeurs dans la colonne A changent dans Google Sheets.
Note:Vous pouvez également consulter mes autres articles liés à Google Sheets tels que « Google Sheets: Surligner les lignes lorsque la valeur change dans n’importe quelle colonne » et « Comment trouver la dernière ligne de chaque groupe dans Google Sheets ».
Pour plus de conseils et d’astuces sur Google Sheets, consultez Crawlan.com.
