Comment exclure des cellules d'une colonne de la somme dans Excel ?
Lorsque vous travaillez avec des données dans Excel, vous pouvez souvent avoir besoin de calculer la somme d'une plage tout en sautant intentionnellement certaines cellules – par exemple, si ces cellules contiennent des valeurs aberrantes, des erreurs, des données non pertinentes ou des valeurs spécifiques que vous souhaitez exclure d'un agrégat. Comme le montre la capture d'écran ci-dessous, imaginez que vous avez une colonne de nombres mais que vous souhaitez faire la somme de toutes les valeurs, sauf celles des cellules A3 et A4. Cet article démontrera de manière exhaustive diverses méthodes pratiques pour additionner une plage dans Excel tout en excluant une ou plusieurs cellules spécifiées, vous aidant ainsi à obtenir précisément les résultats souhaités pour des tâches d'analyse financière, de reporting ou de gestion des stocks nécessitant des calculs sélectifs.
- Exclure des cellules d'une colonne de la somme avec une formule
- Code VBA - Additionner une plage par programmation, en sautant/excluant des cellules spécifiées
- Formule Excel - Utiliser SOMME.SI/SOMME.SI.ENS pour inclure uniquement les valeurs ne correspondant pas aux critères d'exclusion
- Formule Excel - Utiliser la fonction FILTRER dans les versions plus récentes d'Excel pour filtrer les cellules exclues avant de faire la somme
Exclure des cellules d'une colonne de la somme avec une formule
En utilisant une simple arithmétique dans la formule SOMME, vous pouvez directement exclure des cellules indésirables dans votre calcul. Cette approche convient pour des calculs rapides lorsque vous avez un petit nombre d'exclusions à gérer. Suivez ces étapes :
1. Sélectionnez une cellule vide pour afficher le résultat de la somme, puis entrez la formule suivante dans la Barre de formule, puis appuyez sur Entrée pour calculer la somme tout en excluant des cellules spécifiques. Par exemple :
=SOMME(A2:A7)-SOMME(A3:A4)
Explication et conseils :
- La formule SOMME(A2:A7) calcule toute la plage, tandis que SOMME(A3:A4) soustrait les valeurs des cellules exclues. Cela fonctionne mieux lorsque les cellules à exclure sont continues.
- Vous pouvez facilement mélanger et soustraire plusieurs cellules d'exclusion si elles ne sont pas adjacentes. Par exemple, pour exclure A3 et A6 de la plage, ajustez la formule comme suit :
=SOMME(A2:A7)-A3-A6
- Si les exclusions sont dispersées ou nombreuses, lister manuellement chaque cellule exclue peut rendre les formules plus longues et plus difficiles à gérer.
- Soyez prudent avec les références de cellules : si vos données ou votre plage change, mettez à jour la formule en conséquence pour éviter les erreurs.
Code VBA - Additionner une plage par programmation, en sautant/excluant des cellules spécifiées
Pour les scénarios où vous avez de nombreuses exclusions ou devez répéter le processus souvent, utiliser une macro VBA offre flexibilité et automatisation. Avec VBA, vous pouvez additionner une plage spécifiée et exclure n'importe quel nombre de cellules, qu'elles soient continues ou discontinues, en les définissant par programmation. Cette méthode convient aux utilisateurs à l'aise avec l'environnement VBA et cherchant à rationaliser des logiques d'exclusion plus complexes.
Précautions : Les macros VBA peuvent modifier votre classeur. Enregistrez toujours votre travail avant d'exécuter un nouveau code. Les macros doivent être activées pour que ce qui précède fonctionne.
1. Allez dans Outils Développeur > Visual Basic pour ouvrir l'éditeur VBA. Dans la fenêtre Projet, faites un clic droit sur votre classeur, sélectionnez Insérer > Module, puis collez le code suivant dans le module :
Sub SumWithExclusions()
Dim sumRange As Range
Dim excludeCells As Range
Dim cell As Range
Dim result As Double
Dim xTitleId
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set sumRange = Application.InputBox("Select the range to sum", xTitleId, Type:=8)
Set excludeCells = Application.InputBox("Select cells to exclude (use Ctrl+Click to select multiple)", xTitleId, Type:=8)
result = 0
If Not sumRange Is Nothing Then
For Each cell In sumRange
If Not Application.Intersect(cell, excludeCells) Is Nothing Then
' Skip excluded cells
Else
result = result + cell.Value
End If
Next
MsgBox "The sum excluding specified cells is: " & result, vbInformation
Else
MsgBox "No range selected.", vbExclamation
End If
End Sub2. Cliquez 
Exécuter dans la fenêtre VBA ou appuyez sur F5 pour exécuter la macro. Une boîte de dialogue vous invitera à sélectionner la plage complète à additionner, puis à sélectionner les cellules à exclure (maintenez Ctrl pour sélectionner plusieurs). La macro affichera le résultat dans une boîte de message.
- Si vous sélectionnez des cellules par erreur, relancez la macro. Le processus est interactif et flexible.
- Les macros sont idéales pour les tâches répétitives ou les exclusions basées sur des critères complexes.
Formule Excel - Utiliser SOMME.SI ou SOMME.SI.ENS pour inclure uniquement les valeurs ne correspondant pas aux critères d'exclusion
Pour des exclusions plus avancées ou basées sur des règles logiques, vous pouvez utiliser les fonctions SOMME.SI ou SOMME.SI.ENS. Ces formules fonctionnent bien lorsque vos exclusions sont basées sur une valeur, des critères, ou lorsque vous disposez d'une liste de valeurs à éviter.
Exemple - Exclusion basée sur une valeur spécifique
1. Si vous voulez additionner A2:A7 mais exclure la valeur '16', entrez la formule suivante dans votre cellule cible (par exemple, dans la cellule B1) :
=SUMIF(A2:A7,"<>16")Cette formule additionne toutes les valeurs dans la plage A2:A7 sauf celles égales à 16.
2. Après avoir tapé la formule, appuyez sur Entrée. Vous pouvez copier ou ajuster les références de plage/cellule selon vos besoins.
Exemple - Exclure toutes les cellules correspondant à une valeur de cellule
Supposons que la cellule C1 contienne la valeur que vous souhaitez exclure de la somme :
=SUMIF(A2:A7,"<>"&A3)Mettez à jour C1 au besoin, et la formule exclura dynamiquement toutes les valeurs correspondantes.
- Pour plusieurs critères d'exclusion ou des règles plus complexes, envisagez d'utiliser SOMME.SI.ENS en combinaison avec des colonnes d'aide ou des tableaux. Cependant, SOMME.SI/SOMME.SI.ENS fonctionne mieux lorsque les exclusions sont basées sur des critères spécifiques et cohérents, et non sur des positions arbitraires de cellules.
- Si votre plage contient du texte ou des cellules vides, SOMME.SI les ignore automatiquement ; assurez-vous que ce comportement est celui souhaité.
Formule Excel - Utiliser la fonction FILTRER (dans les nouvelles versions d'Excel) pour filtrer les cellules exclues avant de faire la somme
Si vous utilisez Excel pour Microsoft 365 ou Excel 2021 et versions ultérieures, la fonction FILTRER permet une exclusion dynamique et flexible des cellules avant d'appliquer SOMME. C'est particulièrement utile pour les grands ensembles de données ou les critères d'exclusion variables.
Exemple - Exclure des valeurs spécifiques (par exemple, 16 et 13)
1. Entrez la formule suivante dans votre cellule cible (par exemple, B1) :
=SUM(FILTER(A2:A7,(A2:A7<>16)*(A2:A7<>13)))Cela additionne toutes les valeurs dans A2:A7, sauf celles égales à 16 et 13. La fonction FILTRER crée un tableau qui n'inclut que les cellules non égales à ces valeurs, et ensuite SOMME les additionne.
2. Appuyez sur Entrée. Le calcul se mettra à jour dynamiquement si les exclusions ou les données sources changent.
- Pour exclure dynamiquement des valeurs en fonction d'une liste (par exemple, la liste d'exclusion est dans C2:C4) :
=SUM(FILTER(A2:A7,ISNA(MATCH(A2:A7,C2:C4,0))))Cette formule exclut toute valeur dans A2:A7 qui correspond à n'importe quelle valeur dans C2:C4. Mettez simplement à jour votre liste d'exclusion dans la colonne C, et le résultat de la formule se mettra à jour automatiquement.
- L'approche basée sur FILTRER est recommandée pour les utilisateurs travaillant avec les dernières versions d'Excel et recherchant une logique d'exclusion dynamique et scalable.
- Si vous recevez une erreur #CALC!, vérifiez qu'il reste au moins une valeur dans la plage après toutes les exclusions ; sinon, FILTRER renvoie une erreur.
En résumé, Excel propose plusieurs solutions pratiques pour additionner une plage tout en excluant des cellules ou des valeurs spécifiques. Des formules simples conviennent pour des exclusions rapides et petites, tandis que SOMME.SI/SOMME.SI.ENS et FILTRER prennent en charge des scénarios plus flexibles basés sur des conditions. VBA est idéal lorsque les exclusions sont nombreuses, variées ou nécessitent une automatisation. Vérifiez toujours deux fois les références de cellules et les ajustements de formules lorsque vous modifiez vos données sources. Si vous rencontrez des erreurs, vérifiez les plages ou les listes d'exclusion et essayez de réappliquer vos formules ou de relancer la macro.
Articles connexes :
- Comment exclure certaines cellules ou zones de l'impression dans Excel ?
- Comment exclure des valeurs d'une liste par rapport à une autre dans Excel ?
- Comment trouver la valeur minimale dans une plage en excluant la valeur zéro dans Excel ?
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels