Si les valeurs sont des dates ou des nombres dans une colonne et qu’elles sont triées, il est facile de rechercher une valeur dans cette colonne et de les sommer jusqu’à cette ligne dans une autre colonne sur Google Sheets. Pour ce faire, nous pouvons nous appuyer sur plusieurs formules.
Les formules incluent Sumif, Sumproduct, Sum+Filter, Sum+IF ou Query. Nous aborderons cela plus tard.
Les choses seront différentes dans les scénarios suivants :
- Valeurs dupliquées dans la colonne de recherche.
- Données non triées dans la colonne.
Je vais essayer de dissiper tous vos doutes concernant la recherche d’une valeur dans une colonne et la sommation jusqu’à cette (trouvée) ligne dans une autre colonne sur Google Sheets.
Pour l’instant, oublions les doublons. Laissez-moi vous donner des exemples de la sommation d’une colonne jusqu’à la ligne où la valeur de recherche est trouvée.
Remarque : Si vous avez des doutes sur l’une des fonctions mentionnées dans ce tutoriel, veuillez consulter mon guide des fonctions Google Sheets.
Recherche et sommation en fonction d’une colonne de valeurs uniques dans Google Sheets
Colonne de valeurs de date ou de nombre (triée par ordre croissant)
Dans l’exemple suivant, je veux rechercher la date du 05-Jan-2020 dans la colonne B, puis sommer jusqu’à la ligne n° 6 (C2:C6) car ce sera la ligne « trouvée ».
Dans ce cas, nous pouvons utiliser l’une des formules suivantes. Dans toutes les formules, j’ai inclus deux conditions. Elles sont :
- Les dates dans B2:B doivent être inférieures ou égales à 05-Jan-2020.
- Les cellules de la plage B2:B ne doivent pas être vides. Si elles sont vides, omettez les cellules correspondantes de C2:C.
- Si vous souhaitez inclure la ligne vide, supprimez simplement le deuxième critère B2:B<> » » de toutes les formules. Dans Query, c’est différent. Supprimez la partie « B is not null » de la formule.
- Somme + Filtre:
=sum(filter(C2:C,B2:B<=date(2020,1,5),B2:B<>"")) - Somme + SI:
=sum(ArrayFormula(if((B2:B<=date(2020,1,5))*(B2:B<>"")=1,C2:C))) - Requête:
=query(B1:C,"Select Sum(C) where B<=Date'2020-1-5' and B is not null label Sum(C)''") - Somme des produits:
=sumproduct((B2:B<=date(2020,1,5))*(B2:B<>"")=1,C2:C) - Somme.si: `=sumif(B2:B10, »<= »&date(2020,1,5),C2:C10)
Dans ces 5 formules ci-dessus, qui recherchent une date/valeur numérique dans la colonne B et somment la colonne C jusqu’à cette ligne, j’ai utilisé comme référence directe le critère.
Pour plus de flexibilité, c’est-à-dire pour pouvoir facilement modifier le critère sans avoir à modifier la formule, nous pouvons les utiliser sous forme de référence de cellule. Cela varie en fonction des fonctions.
Supposons que le critère 05-Jan-2020 se trouve dans la cellule D1. Alors, les formules ci-dessus seraient utilisées comme suit :
=sum(filter(C2:C,B2:B<=D1,B2:B<>""))
=sum(ArrayFormula(if((B2:B<=D1)*(B2:B<>"")=1,C2:C)))
=query(B1:C,"Select Sum(C) where B<=date'"&TEXT(D1,"yyyy-mm-dd")&"' and B is not null")
=sumproduct((B2:B<=D1)*(B2:B<>"")=1,C2:C)
`=sumif(B2:B10, »<= »&D1,C2:C10)
En parcourant les formules de recherche ci-dessus et la sommation jusqu’à cette ligne, vous pouvez comprendre que la Query est totalement différente dans l’utilisation des critères.
Vous devez entrer les critères selon un schéma spécifique, et cela varie en fonction des critères.
Pour les critères de date utilisés dans Query, veuillez vous référer à mon guide – Comment utiliser les critères de date dans la fonction Query de Google Sheets. Pour apprendre l’utilisation correcte de tous les types de critères dans Query, veuillez consulter mon guide suivant – Exemples d’utilisation des littéraux dans Query de Google Sheets.
Colonne de valeurs de date ou de nombre (triée par ordre décroissant)
Si les dates sont triées dans la colonne B par ordre décroissant, la partie du critère des formules ci-dessus doit être modifiée.
Les changements sont minimes. Il suffit de remplacer <= par >=. Voici les formules après les modifications pour rechercher une valeur dans une colonne et sommer jusqu’à cette ligne à partir d’une autre colonne :
=sum(filter(C2:C,B2:B>=D1,B2:B<>""))
=sum(ArrayFormula(if((B2:B>=D1)*(B2:B<>"")=1,C2:C)))
=query(B1:C,"Select Sum(C) where B>=date'"&TEXT(D1,"yyyy-mm-dd")&"' and B is not null label sum(C)''")
=sumproduct((B2:B>=D1)*(B2:B<>"")=1,C2:C)
`=sumif(B2:B10, »>= »&D1,C2:C10)
Colonne de valeurs de chaînes de caractères
Et qu’en est-il des valeurs de chaîne de caractères dans la colonne B ?
Toutes les formules ci-dessus, à l’exception de Query, fonctionneront également dans une colonne de chaînes de caractères. La différence entre les formules triées/non triées s’appliquera également ici.
Voici un exemple. Je ne répète pas comment utiliser ces formules lorsque les valeurs sont triées par ordre décroissant, car les modifications concernent l’utilisation des opérateurs <= et >=.
[Insérer l’image ici]
Recherche d’une valeur et sommation jusqu’à cette ligne indépendamment de l’ordre de tri et du type de valeur
Toutes les formules ci-dessus de recherche et de sommation jusqu’à cette ligne ont été testées dans une colonne triée et avec des types de données spécifiques tels que les nombres, les chaînes de caractères ou les dates.
Je veux une formule plus générale pour rechercher une valeur et sommer jusqu’à cette ligne en raison des raisons énumérées ci-dessous.
La recherche dans une colonne contient :
- Des valeurs de types de données mélangés.
- Des valeurs alphanumériques.
- Une incertitude quant à la tri ou à l’absence de tri des données.
Dans de tels scénarios, nous ne pouvons pas utiliser les formules précédentes. Voici une formule « toutes conditions météorologiques » à utiliser, qui fonctionnera avec des données triées et non triées. Également avec des valeurs de colonnes de dates, de nombres et de chaînes de caractères.
Formule de recherche et somme toutes conditions météorologiques
=sum(array_constrain(C2:C,match(D1,B2:B,0),1))
[Insérer l’image ici]
Cette formule nécessite une correspondance exacte (non sensible à la casse) de la clé de recherche dans la colonne de recherche. Parce que les données ne sont pas triées.
Puisque les opérateurs de comparaison sont utilisés avec le critère, les formules précédentes dans une plage triée correspondent à la valeur inférieure ou égale à la valeur de clé de recherche.
De même, dans une plage triée, nous pouvons ajuster la formule « toutes conditions météorologiques » ci-dessus pour la correspondance la plus proche. Comment faire ?
Dans la partie Match de la formule, c’est-à-dire match(D1,B2:B,0), 0 représente l’ordre de tri (non trié/correspondance exacte). Remplacez-le par 1 pour des données triées par ordre croissant et utilisez -1 pour un ordre décroissant.
Pour exclure les lignes vides (si une cellule dans la colonne B est vide), utilisez la version suivante de la formule « toutes conditions météorologiques » :
=sum(array_constrain(filter(C2:C,B2:B<>""),match(D1,filter(B2:B,B2:B<>""),0),1))
Le point de la formule Match ci-dessus s’applique également ici.
En résumé, cette formule est suffisante pour rechercher une valeur et sommer jusqu’à cette ligne dans Google Sheets. Applicable à une plage triée ou non triée dans une colonne de valeur unique.
Recherche et sommation en fonction d’une colonne de valeurs dupliquées dans Google Sheets
Sommation jusqu’à la première occurrence de la valeur de recherche
Ici, nous pouvons utiliser la formule précédente.
Sommation jusqu’à la dernière occurrence de la valeur de recherche
Pour rechercher la dernière occurrence d’une valeur et sommer jusqu’à cette ligne dans une autre colonne sur Google Sheets, nous pouvons utiliser nos précédentes formules dans une plage triée.
Évitez la Query et utilisez 1 ou -1 dans la formule « toutes conditions météorologiques » en fonction de l’ordre de tri des données. Mais aucune des formules ci-dessus ne fonctionnera dans une plage non triée.
Voici les formules pour rechercher la dernière occurrence d’une valeur dans une plage non triée et sommer jusqu’à cette ligne:
[Insérer l’image ici]
Exclure la ligne vide :
=Query(indirect("B2:"&ArrayFormula(cell("address",lookup(2,1/{B2:B=D1},C2:C)))),"Select Sum(C) where B is not null label Sum(C)''")
Inclure la ligne vide:
=Query(indirect("B2:"&ArrayFormula(cell("address",lookup(2,1/{B2:B=D1},C2:C)))),"Select Sum(C) label Sum(C)''")
Et voilà ! Merci de votre attention, profitez-en bien !
P.S : Pour plus d’articles intéressants sur Google Sheets et d’autres sujets passionnants du monde numérique, consultez Crawlan.com.
