Comment additionner des valeurs uniques en fonction de critères spécifiques dans Excel ?
Lorsque vous travaillez avec des jeux de données Excel — tels que des journaux de commandes, des registres financiers ou des résultats d’enquêtes —, il vous arrive fréquemment de devoir calculer la somme des valeurs uniques d’une colonne en appliquant des filtres ou des critères situés dans une autre colonne. Prenons l’exemple d’un tableau contenant deux colonnes : Nom et Commande. Si vous souhaitez additionner uniquement les valeurs uniques de la colonne Commande pour chaque Nom (en ignorant les doublons), comment y parvenir efficacement dans Excel ? Cette exigence est courante dans de nombreux contextes professionnels ou d’analyse de données, car une simple somme de tous les éléments correspondants produirait des résultats gonflés en raison des doublons.
La capture d’écran ci-dessous illustre un scénario typique : à partir d’une liste de noms et de leurs valeurs de commande correspondantes — incluant des doublons —, vous souhaitez résumer les données en additionnant, pour chaque nom, les valeurs uniques de commande.
Les défis habituels liés à cette tâche consistent à identifier les entrées uniques selon des critères précis, à s’assurer que seule la première occurrence soit prise en compte et à éviter les erreurs manuelles pouvant résulter du copier-coller de données filtrées. Plusieurs solutions pratiques dans Excel — notamment les formules matricielles, Kutools et Power Query — vous permettent de résoudre ce problème efficacement, chacune étant adaptée à des scénarios d’utilisation spécifiques.
- Additionner des valeurs uniques en fonction d’un ou plusieurs critères à l’aide de formules matricielles
- Additionner des valeurs uniques selon des critères à l’aide de la Fusion avancée des lignes de Kutools pour Excel
- Autres méthodes intégrées à Excel : utiliser Tableau croisé dynamique pour l’analyse de sommes uniques
<h4"> Additionner des valeurs uniques en fonction d’un ou plusieurs critères à l’aide de formules matricielles
Une approche à la fois efficace et flexible consiste à utiliser des formules matricielles, qui vous permettent de résumer uniquement les valeurs répondant à des critères spécifiques. Cette méthode s’avère particulièrement utile lorsque vous souhaitez que le calcul s’actualise automatiquement dès que les données ou les critères évoluent.
Pour additionner uniquement les valeurs uniques d’une colonne en fonction d’un filtre ou d’une condition située dans une autre colonne, vous pouvez appliquer la formule suivante :
1. Dans une cellule vide (par exemple,)E2), saisissez 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 valider la formule, vérifiez attentivement que :
- A2:A12 : la plage contenant les critères (ici, des noms).
- D2 : La cellule contenant votre condition cible (par exemple, un nom spécifique).
- B2:B12 : La plage de valeurs dont vous souhaitez faire la somme unique.
Vous pouvez ajuster ces plages en fonction de la structure réelle de vos données. Veillez toutefois à ce qu’elles aient toutes la même longueur pour éviter toute erreur de formule.
2. Pour activer cette formule matricielle, saisissez-la, puis appuyez simultanément sur Ctrl + Maj + Entrée. Des accolades apparaîtront automatiquement autour de la formule, confirmant qu’il s’agit bien d’une formule matricielle. Ensuite, faites glisser la poignée de recopie vers le bas pour étendre la formule à toutes les cellules correspondantes de votre colonne de synthèse — chaque élément obtient ainsi instantanément sa somme unique et exacte.
Conseil pratique : Si vous utilisez Excel 365 ou Excel 2021, les nouvelles fonctions de tableaux dynamiques telles que UNIQUE et SOMME.SI.ENS peuvent encore simplifier 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 davantage de critères : =SOMME(SOMME.SI.ENS(plage_somme; plage_critères1; UNIQUE(plage_critères1); [plage_critères2; critère2]; ...))
Précautions :
- N’oubliez pas d’utiliser la saisie de formule matricielle ()Ctrl + Maj + Entrée) si vous travaillez avec Excel 2019 ou une version antérieure. Avec Excel 365 ou Excel 2021, il vous suffit d’appuyer simplement sur Entrée pour les formules dynamiques.
- Si vos plages sont particulièrement volumineuses, l’approche matricielle peut ralentir les calculs. Il est alors préférable de filtrer vos données au préalable ou d’opter pour d’autres méthodes adaptées aux très grands jeux de données.
- Veillez attentivement aux espaces superflus et à la cohérence des types de données : un formatage incohérent du texte ou des nombres peut entraîner des erreurs d’incompatibilité.
Conseils: Si vous devez additionner toutes les valeurs uniques selon deux critères, vous pouvez utiliser l’extension suivante de la formule matricielle :
=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 repose sur le même principe, mais intègre un filtre supplémentaire issu de la colonne B(désormais comparée à)F2 comme condition secondaire) et additionne les valeurs uniques de la colonne C. Après avoir saisi cette formule dans la cellule de synthèse choisie, validez-la avec Ctrl + Maj + Entrée, puis appliquez-la aux autres lignes de synthèse si nécessaire.
Recommandation finale : Bien que les formules matricielles fournissent des résultats précis dans la plupart des cas, vérifiez toujours la présence de doublons masqués (notamment ceux comportant des espaces supplémentaires ou des différences de mise en forme du texte) et assurez-vous que vos zones de synthèse extraient bien des listes correctement filtrées.
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Additionner des valeurs uniques selon des critères à l’aide de la Fusion avancée des lignes de Kutools pour Excel
Kutools pour Excel rend l’addition des seules valeurs uniques selon une condition spécifique extrêmement simple grâce à sa fonction Fusion avancée des lignes ! En quelques clics seulement, elle regroupe intelligemment vos données et applique une logique de synthèse personnalisée — sans formule, sans complication, juste des résultats précis.
Étape 1 : Sélectionnez votre tableau de données
Sélectionnez l’intégralité du tableau, y compris les en-têtes.
Étape 2 : Accédez à Kutools > Contenu > Fusion avancée des lignes.
Étape 3 : Définissez la colonne de regroupement
Dans la boîte de dialogue qui s’ouvre, choisissez la colonne selon laquelle vous souhaitez effectuer le regroupement (par exemple, Fruit), puis définissez-la comme Clé primaire dans la section Opération.
Étape 4 : Définissez le champ pour la somme des valeurs uniques
Sélectionnez la colonne Ventes, puis choisissez le calcul souhaité (par exemple, Somme) dans la section Opération.
Conseil : Vous pouvez prévisualiser immédiatement le résultat combiné directement dans la boîte de dialogue.
Étape 5 : Cliquez sur OK. Le tableau est désormais regroupé par client, chaque groupe affichant la somme des montants uniques des produits.
Autres méthodes intégrées d’Excel : utilisez Tableau croisé dynamique pour l’analyse de somme unique
La fonction Tableau croisé dynamique d’Excel constitue une autre approche intégrée et puissante pour résumer des données selon des critères. Bien que les tableaux croisés dynamiques ne permettent pas, par défaut, de faire la somme des valeurs uniques, à partir d’Excel 2013, ils prennent en charge le calcul Nombre distinct, qui vous aide à analyser le nombre d’entrées uniques pour un champ donné. Même si cela ne calcule pas directement la somme des valeurs uniques, vous pouvez combiner Nombre distinct avec un ajustement manuel ou un champ calculé afin d’obtenir un résumé similaire.
Avantages : Les tableaux croisés dynamiques ne nécessitent ni mémorisation de formules ni codage VBA, et offrent une interface extrêmement flexible grâce à leur système intuitif de glisser-déposer. Parfaits pour les rapports périodiques, les analyses par groupe, les aperçus rapides ou le travail collaboratif entre équipes, ils excellent dans le résumé et l’analyse des données — mais ne sont pas conçus pour créer des formules destinées à des calculs ou une automatisation ultérieurs.
Voici comment utiliser un tableau croisé dynamique pour une analyse de somme unique :
- Sélectionnez votre plage de données (par exemple,)A1:B12, incluant les en-têtes), puis accédez à Insertion > Tableau croisé dynamique. Dans la boîte de dialogue, choisissez si vous souhaitez placer le tableau croisé dynamique dans une nouvelle feuille de calcul ou dans une feuille existante.
- Dans la liste des champs du tableau croisé dynamique, faites glisser Nom vers la zone Lignes, et Commande vers la zone Valeurs.
- Pour les entrées Commande situées dans la zone Valeurs, cliquez sur la flèche déroulante > Paramètres de valeur Paramètres de champ >, puis définissez sur Somme (affiche la somme totale des commandes, y compris les doublons).
Limites :
- La fonction Compte distinct n’est disponible qu’à partir d’Excel 2013 ; les versions antérieures exigent davantage de manipulations manuelles.
En résumé, même si le tableau croisé dynamique excelle pour les résumés interactifs et l’analyse, envisagez de le combiner avec des approches basées sur des formules ou du code VBA lorsque vous avez besoin de calculs précis portant sur la somme de valeurs uniques.
Articles connexes :
- Additionner plusieurs colonnes selon un critère unique dans Excel
- Dans Excel, il vous arrive souvent d’avoir à additionner plusieurs colonnes selon un seul critère. Par exemple, j’ai une plage de données comme illustrée ci-dessous, et je souhaite obtenir la somme totale des valeurs « KTE » sur les trois mois : janvier, février et mars.
- Recherche verticale (VLOOKUP) et somme des correspondances en lignes ou en colonnes dans Excel
- L’association de la fonction RECHERCHEV (VLOOKUP) et de la somme vous permet de localiser rapidement un critère précis tout en additionnant simultanément les valeurs correspondantes. Dans cet article, nous vous présentons deux méthodes pour effectuer une recherche verticale et additionner soit la première, soit l’ensemble des valeurs correspondantes, organisées en lignes ou en colonnes dans Excel.
- Additionner des valeurs selon le mois et l’année dans Excel
- Si vous disposez d’une plage de données où la colonne A contient des dates et la colonne B le nombre de commandes, vous devrez peut-être additionner ces valeurs en fonction du mois et de l’année indiqués dans une autre colonne. Ici, l’objectif est de calculer le total des commandes passées en janvier 2016 pour obtenir le résultat illustré ci-dessous. Cet article vous présente quelques astuces efficaces pour réaliser cette opération dans Excel.
- Additionner des valeurs selon un critère textuel dans Excel
- Dans Excel, avez-vous déjà essayé d’additionner des valeurs en fonction d’un critère textuel situé dans une autre colonne ? Par exemple, j’ai une plage de données dans une feuille de calcul, comme illustré ci-dessous. Je souhaite maintenant additionner tous les nombres de la colonne B dont les cellules correspondantes en colonne A contiennent « KTE ».
- Additionner des valeurs en fonction de la sélection de Liste déroulante dans Excel
- Comme illustré ci-dessous, vous disposez d’un tableau contenant une colonne Catégorie et une colonne Montant, et vous avez créé une liste déroulante via la validation des données Liste déroulante comprenant toutes les catégories. Lorsque vous sélectionnez une catégorie dans la Liste déroulante, vous souhaitez additionner toutes les valeurs correspondantes de la colonne B et afficher le résultat dans une cellule spécifique. Par exemple, en sélectionnant la catégorie « CC » dans la Liste déroulante, vous devez additionner les valeurs situées dans les cellules B5 et B8 pour obtenir le total 40+70=110. Comment y parvenir ? La méthode présentée dans cet article peut vous aider.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels