Comment additionner des valeurs uniques en fonction de critères dans Excel ?

Lorsque vous travaillez avec des jeux de données Excel, tels que des journaux de commandes, des dossiers financiers ou des résultats de sondages, vous pouvez souvent avoir besoin de calculer la somme des valeurs uniques d'une colonne basée sur des filtres ou des critères d'une autre colonne. Par exemple, considérons une table de données contenant deux colonnes : Nom et Commande. Si vous souhaitez calculer la somme des valeurs de Commande uniques pour chaque Nom (en ignorant les valeurs répétées), comment pouvez-vous le faire efficacement dans Excel ? C'est une exigence courante dans de nombreux scénarios commerciaux ou d'analyse de données où une simple sommation de tous les éléments correspondants donnerait des résultats gonflés en raison des doublons.

La capture d'écran d'exemple ci-dessous illustre un scénario typique : étant donné une liste de noms et leurs valeurs de commande correspondantes, y compris les doublons, vous souhaitez résumer les données en additionnant les valeurs de commande uniques pour chaque nom séparément.

Les défis courants dans cette tâche incluent l'identification des entrées uniques basées sur des critères spécifiques, en veillant à ce que seule la première occurrence soit comptée, et en évitant les erreurs manuelles qui peuvent survenir lors de la copie et du collage des données filtrées. Plusieurs approches pratiques dans Excel, y compris les formules matricielles, Kutools et Power Query, peuvent vous aider à résoudre ce problème, chacune adaptée à différents scénarios d'utilisation.

• Additionner des valeurs uniques en fonction d'un ou plusieurs critères avec des formules matricielles
• Additionner des valeurs uniques en fonction de critères en utilisant la fonctionnalité Avancée - Combiner des lignes de Kutools pour Excel
• Autres méthodes intégrées d'Excel : Utilisez le Tableau croisé dynamique pour l'analyse de sommes uniques

Une approche efficace et flexible consiste à utiliser des formules matricielles, qui vous permettent de résumer des valeurs uniques répondant à des critères spécifiques. Cela fonctionne particulièrement bien lorsque vous souhaitez que le calcul se mette à jour automatiquement lorsque les données ou les critères changent.

Pour ne faire la somme que des valeurs uniques dans une colonne selon le filtre ou la condition dans une autre colonne, vous pouvez appliquer la formule suivante :

1. Dans une cellule vide (par exemple, E2), entrez cette formule :

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))

Avant de confirmer la formule, vérifiez attentivement que :

• A2:A12 : La plage contenant les critères (noms dans ce cas).
• D2 : La cellule où se trouve votre condition cible (comme un nom spécifique).
• B2:B12 : La plage de valeurs que vous souhaitez additionner de manière unique.

Vous pouvez ajuster ces plages en fonction de la structure réelle de vos données. Assurez-vous que toutes les plages ont la même longueur pour éviter les erreurs de formule.

2. Pour activer cette formule matricielle, appuyez simultanément sur Ctrl + Maj + Entrée après l'avoir saisie. Des accolades apparaîtront autour de la formule, indiquant qu'il s'agit d'une formule matricielle. Ensuite, faites glisser la poignée de recopie vers le bas pour copier la formule pour chaque valeur correspondante dans votre colonne de résumé. De cette façon, chaque élément obtient automatiquement la somme unique correcte.

Astuce pratique : Si vous utilisez Excel 365 ou Excel 2021, de nouvelles fonctions de tableau dynamique comme UNIQUE et SOMME.SI peuvent simplifier davantage certains de ces calculs, mais la formule ci-dessus fonctionne de manière fiable sur de nombreuses versions d'Excel.

=SOMME.SI(A2:B12, UNIQUE(D2), B2:B12)

Gérer plus de critères : =SOMME(SOMME.SI(plage_somme, plage_critères1, UNIQUE(plage_critères1), [plage_critères2, critère2], ...)

Précautions :

• Assurez-vous d'utiliser la méthode de saisie de formule matricielle (Ctrl + Maj + Entrée) si vous êtes sur Excel 2019 ou version antérieure. Pour Excel 365/2021, une simple touche Entrée peut suffire pour les formules dynamiques.
• Si vos plages de données sont particulièrement volumineuses, l'approche matricielle peut devenir plus lente, alors envisagez de filtrer vos données d'abord ou d'utiliser d'autres méthodes pour les très grands ensembles de données.
• Surveillez attentivement les espaces supplémentaires ou la cohérence des types de données — des différences de formatage de texte ou de nombre peuvent provoquer des erreurs de non-correspondance.

Conseils : Si vous avez besoin d'additionner toutes les valeurs uniques basées sur deux critères, l'extension suivante de la formule matricielle peut être utilisée :

=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))

Cette formule est similaire en principe, mais supporte un filtre supplémentaire de la colonne B (maintenant comparé avec F2 comme condition secondaire), et additionne les valeurs uniques de la colonne C. Après avoir saisi cette formule dans la cellule de résumé sélectionnée, utilisez Ctrl + Maj + Entrée pour la confirmer, puis appliquez-la aux autres lignes de résumé au besoin.

Résumé : Bien que les formules matricielles fournissent des résultats précis dans la plupart des scénarios, vérifiez toujours deux fois la présence de doublons cachés (tels que ceux avec des espaces supplémentaires ou des différences de formatage de texte) et assurez-vous que vos zones de résumé tirent des listes filtrées avec précision.

Découvrez la magie d'Excel avec Kutools AI

• Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
• Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
• Codage VBA : Écrivez et implémentez du code VBA sans effort.
• Interprétation des formules : Comprenez facilement des formules complexes.
• Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.

Améliorez vos capacités Excel avec des outils alimentés par l'IA. Téléchargez maintenant et découvrez une efficacité sans précédent !

Additionner des valeurs uniques en fonction de critères en utilisant la fonctionnalité Avancée - Combiner des lignes de Kutools pour Excel

La fonctionnalité Avancée - Combiner des lignes de Kutools pour Excel rend l'addition des seules valeurs uniques basées sur une condition spécifique sans effort ! Avec quelques clics seulement, elle regroupe intelligemment vos données et applique une logique de résumé personnalisée — pas de formules, pas de complications, juste des résultats précis.

Étape 1 : Sélectionnez votre table de données

Mettez en surbrillance la table complète, y compris les en-têtes.

Étape 2 : Allez dans Kutools > Texte > Avancée - Combiner des lignes.

Étape 3 : Définissez la colonne de regroupement

Dans la fenêtre contextuelle, choisissez la colonne par laquelle vous souhaitez regrouper (par ex., Fruit), définissez-la comme Clé principale dans la section Opération.

Étape 4 : Définissez le champ pour la somme unique

Sélectionnez la colonne Ventes, définissez le calcul souhaité (par ex., Somme) dans la section Opération.

Astuce : Vous pouvez prévisualiser le résultat combiné dans la boîte de dialogue.

Étape 5 : Cliquez sur OK. La table est maintenant regroupée par client, chacun ayant la somme des montants de produits uniques.

??Kutools pour Excel propose plus de 300 fonctionnalités puissantes pour décupler votre productivité — et vous pouvez les essayer gratuitement pendant 30 jours !

Autres méthodes intégrées d'Excel : Utilisez le Tableau croisé dynamique pour l'analyse de sommes uniques

La fonctionnalité Tableau croisé dynamique d'Excel offre une autre approche intégrée puissante pour résumer des données basées sur des critères. Bien que les Tableaux croisés dynamiques ne fassent pas directement la somme des valeurs uniques par défaut, post-Excel2013, ils prennent en charge le calcul du Compte distinct, ce qui vous aide à analyser le nombre d'entrées uniques pour un champ spécifié. Bien que cela ne calcule pas directement la somme des valeurs uniques, vous pouvez utiliser le Compte distinct avec un ajustement manuel ou un champ calculé pour faciliter un résumé similaire.

Avantages : Les Tableaux croisés dynamiques ne nécessitent aucune mémorisation de formules ni codage VBA, et offrent des interfaces de glisser-déposer hautement flexibles. Adaptés pour les rapports périodiques, l'analyse de groupe, les aperçus rapides ou lorsque vous collaborez à travers des équipes. Cependant, ils sont mieux adaptés pour le résumé et l'analyse, et non pour construire des formules pour des calculs ou automatisations ultérieures.

Voici comment utiliser un Tableau croisé dynamique pour l'analyse de sommes uniques :

1. Sélectionnez votre plage de données (par exemple, A1:B12 y compris les en-têtes), et allez dans Insérer > Tableau croisé dynamique. Dans la boîte de dialogue, choisissez si vous voulez placer le Tableau croisé dynamique dans une nouvelle feuille de calcul ou dans une existante.
2. Dans la Liste des champs du Tableau croisé dynamique, faites glisser Nom dans la zone Lignes, et Commande dans la zone Valeurs.
3. Pour les entrées de Commande dans la zone Valeurs, cliquez sur la flèche déroulante > Paramètres du champ de valeur > définissez sur Somme (montre la somme totale des commandes, y compris les doublons).

Limitations :

• La fonction Compte distinct n'est disponible que dans Excel2013 ou version ultérieure ; les versions antérieures nécessitent un travail manuel plus important.

En résumé, bien que le Tableau croisé dynamique soit excellent pour le résumé interactif et l'analyse, envisagez de le coupler avec des approches basées sur des formules ou VBA si vous avez besoin de véritables calculs de sommes uniques.

Plus d'articles relatifs :