Je vais vous révéler une astuce incroyable pour soustraire la valeur précédente d’une valeur donnée dans Google Sheets. Vous allez adorer cette méthode rapide et efficace !
L’avantage de la fonction MAP
La fonction lambda MAP est ma préférée parmi toutes les autres fonctions d’aide lambda (LHF) pour coder une formule de soustraction de la valeur précédente (ligne supérieure) à la valeur de la ligne actuelle dans Google Sheets. Contrairement aux autres LHF comme SCAN ou BYROW, MAP nous permet d’utiliser plusieurs tableaux. Cela s’avère très utile lorsque nous souhaitons soustraire la valeur de la ligne actuelle de la valeur de la ligne précédente en fonction d’une colonne de regroupement.
Soustraire la valeur précédente de la valeur actuelle
Commençons par des données à une colonne, ce qui signifie que le regroupement n’est pas nécessaire. Dans ce cas, nous pouvons utiliser l’une des LHF mentionnées précédemment. C’est parti !
Voici une capture d’écran montrant les données réparties sur plusieurs colonnes. Les données d’exemple se trouvent dans les colonnes B et C. Les colonnes E et G contiennent les résultats de deux formules lambda.
Nous allons commencer avec les données de la colonne C et le résultat dans la colonne G. Je souhaite obtenir une formule de tableau pour soustraire la valeur C2 de C3 (=C3-C2), la valeur C4 de C3 (=C4-C3), et ainsi de suite. Voici le résultat dans G2:G11, où j’ai utilisé la formule de tableau suivante dans la cellule G2.
={0;map(C3:C11,lambda(r,r-indirect("C"&row(r)-1)))}Cette formule soustrait la valeur précédente de la valeur actuelle, ligne par ligne. N’hésitez pas à remplacer la fonction MAP par BYROW. Le reste de la formule, y compris LAMBDA, reste le même.
Si vous préférez utiliser SCAN, utilisez la formule suivante :
={0;scan(,C3:C11,lambda(a,r,r-indirect("C"&row(r)-1)))}Comment ouvrir la plage de C3:C11 à C3:C?
La solution consiste à trouver la position relative de la dernière cellule non vide de la colonne C. La formule suivante vous permet de le faire dans Google Sheets :
=ArrayFormula(MATCH(2,1/(C3:C<>"")),1)Utilisez ensuite INDEX pour décaler ces lignes autant de fois que nécessaire. Remplacez donc C3:C11 par C3:index(C3:C,last_non_blank_cell). Vous pouvez supprimer la formule ArrayFormula, car elle n’est pas nécessaire avec INDEX.
Voici donc la formule pour soustraire la valeur précédente de la valeur actuelle dans une plage ouverte dans Google Sheets :
={0;map(C3:index(C3:C,match(2,1/(C3:C<>"")),1)),lambda(r,r-indirect("C"&row(r)-1)))}Soustraire la valeur de la ligne précédente par groupe
En plus de la colonne « Montant », nous allons également prendre en compte la colonne « ID ». Le but est de soustraire la valeur de la ligne précédente par groupe.
Veuillez consulter la plage B2:C11 et le résultat dans E2:E11 dans la capture d’écran ci-dessus. Nous avons besoin d’un tableau trié. Dans l’exemple ci-dessus, j’ai trié le tableau (plage) par ID, la colonne de regroupement.
Voici la formule E2 pour soustraire la valeur de la ligne précédente par groupe dans Google Sheets :
={0;map(B3:B11,C3:C11,lambda(_,r,if(countif(B2:_,_)=1,0,r-indirect("C"&row(r)-1))))}En quoi cette formule diffère-t-elle de la formule_1 précédente ? Jetez un coup d’œil à la partie mise en évidence. Voici la logique derrière la soustraction de la valeur actuelle de la cellule supérieure par groupe :
if(running_count_of_occurrence_of_group_column_values=1,0,formula_1)Nous pouvons réaliser tout cela dans une seule formule MAP. C’est précisément ce que fait la formule E2. La partie mise en évidence en rouge renvoie le décompte continu des occurrences des IDs. La partie countif(B2:,) est spécifiquement destinée à renvoyer le décompte continu des occurrences des IDs.
Et voilà ! Vous avez maintenant tous les secrets pour soustraire la valeur précédente de la valeur actuelle dans Google Sheets. N’hésitez pas à visiter Crawlan.com pour plus de conseils sur Google Sheets !
