Lorsqu’il s’agit d’utiliser SUMIF/SUMIFS pour exclure les doublons dans Google Sheets, cela peut sembler important, mais en réalité, les doublons ne posent généralement pas de problème.
Ces fonctions sont généralement utilisées pour calculer la somme d’une colonne en fonction des occurrences de critères spécifiques dans une autre colonne.
Ces occurrences peuvent être considérées comme des doublons. Par conséquent, il n’y a pas de problème d’unicité ou de doublons lors de l’utilisation de SUMIF/SUMIFS de manière habituelle.
Avant d’expliquer comment utiliser SUMIF ou SUMIFS en excluant les doublons dans Google Sheets, il est essentiel de comprendre ce concept.
Il est important de comprendre ce que nous entendons par doublons dans le contexte de SUMIF/SUMIFS dans Google Sheets.
Doublons dans les fonctions SUMIF/SUMIFS : Ce qu’ils sont et comment les exclure
Prenons par exemple une situation où la colonne A contient des articles, la colonne B contient des quantités commandées, la colonne C contient les noms des clients et la colonne D contient les numéros de commande.
Prenons l’article « Black Sand 3/16 », qui a reçu quatre commandes de deux clients. Chaque client a commandé deux fois l’article. Cependant, en raison d’une pénurie de matériel, nous ne tiendrons compte que de leur première commande.
Le but de SUMIF/SUMIFS ici est donc d’obtenir le total de l’article « Black Sand 3/16 », sans doublons par rapport au client.
Utilisation de formules régulières
Nous utilisons généralement les formules suivantes lorsque nous voulons obtenir le total des commandes pour l’article « Black Sand 3/16 » :
-
Pour obtenir le total des commandes de l’article « Black Sand 3/16 » (indépendamment du client) :
=SUMIF(A2:A, "Black Sand 3/16", B2:B)// retourne 675=SUMIFS(B2:B, A2:A,"Black Sand 3/16")// retourne 675
-
Si nous voulons obtenir le total des commandes de l’article « Black Sand 3/16 » spécifiquement pour le « Client 1 » :
=SUMIFS(B2:B,A2:A,"Black Sand 3/16",C2:C,"Client 1")// retourne 225
Maintenant, plongeons dans la façon d’utiliser SUMIF ou SUMIFS tout en excluant les doublons dans Google Sheets.
Exclusion des doublons dans SUMIF/SUMIFS
Maintenant, je vais aborder le sujet de la gestion des doublons dans les calculs SUMIF/SUMIFS.
L’objectif est d’éliminer les occurrences multiples du critère en fonction d’une condition dans une autre colonne.
En d’autres termes, nous voulons supprimer les commandes multiples de « Black Sand 3/16 » du même client. En tenant compte de la structure de nos données d’exemple, nous devons prendre en compte les doublons dans les colonnes A et C.
Nous avons deux méthodes pour obtenir SUMIF/SUMIFS en excluant les doublons dans Google Sheets : une avec une colonne d’aide et une sans. Nous explorerons les deux solutions.
Remarque : Cela ne fonctionnera pas dans Excel car l’une des fonctions utilisées ici n’était pas disponible dans Excel lors de la dernière mise à jour de cet article.
Utilisation d’une colonne d’aide
Je suppose que les données d’exemple ci-dessus se trouvent dans Sheet1!A1:D.
Saisissez la formule SORTN suivante dans Sheet2!A1.
=SORTN(Sheet1!A2:D,9^9,2,Sheet1!A2:A&Sheet1!C2:C,1)
La fonction SORTN est utilisée pour éliminer les doublons. Elle renvoie les valeurs uniques en fonction de deux colonnes, qui sont les colonnes A et C.
Syntaxe de la fonction SORTN dans Google Sheets :
SORTN(plage, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, ...], [is_ascending2, ...])
Où :
- Sheet1!A2:D : C’est l’étendue des données à partir desquelles vous souhaitez supprimer les doublons.
- 9^9 : Ce nombre est utilisé comme un nombre arbitrairement grand pour s’assurer que toutes les lignes sont incluses après le traitement.
- 2 : Ce nombre display_ties_mode indique que vous voulez supprimer les doublons.
- Sheet1!A2:A&Sheet1!C2:C : Cette partie est la clé de la formule. Elle concatène les valeurs des colonnes A et C pour chaque ligne. Les répétitions de ces valeurs dans les lignes suivantes seront considérées comme des doublons.
- 1 : Cet argument spécifie l’ordre de tri, 1 indiquant un ordre croissant.
Maintenant, vous pouvez utiliser une formule SUMIF/SUMIFS régulière dans ces nouvelles données qui contiennent des enregistrements uniques par rapport aux articles et aux clients.
=SUMIF(A1:A,"Black Sand 3/16", B1:B)
=SUMIFS(B1:B, A1:A, "Black Sand 3/16")
Cela équivaudra à exclure les doublons dans les données d’origine lors de l’utilisation de SUMIF/SUMIFS.
Méthode de formule sans colonne d’aide
SUMIFS, tout comme SUMIF, ne fonctionnera pas avec des colonnes virtuelles dans la plage de somme. Par conséquent, il est évident que nous ne pouvons pas utiliser les données ci-dessus dans ces fonctions.
En alternative, nous pouvons utiliser la QUERY suivante :
=QUERY(SORTN(Sheet1!A2:D, 9^9, 2, Sheet1!A2:A&Sheet1!C2:C, 0), "select sum(Col2) where Col1='Black Sand 3/16' label sum(Col2)''")
Cette QUERY calcule la somme de la colonne 2 lorsque la valeur de la colonne 1 est égale à « Black Sand 3/16 ».
Donc, QUERY est une alternative à SUMIF/SUMIFS en excluant les doublons dans Google Sheets.
Conclusion
Vos besoins en matière d’exclusion des doublons pour SUMIF/SUMIFS peuvent différer de l’exemple discuté ci-dessus. Néanmoins, vous pouvez considérer cela comme un point de départ.
N’hésitez pas à décrire vos besoins spécifiques dans les commentaires ci-dessous. Je passerai personnellement en revue chaque commentaire pertinent.
Related: Mastering Unique Summing in Google Sheets Without SUMUNIQUEIFS
