Comment exclure certaines cellules d’une colonne du calcul de la somme dans Excel ?
Lorsque vous travaillez avec des données dans Excel, il vous arrive fréquemment de devoir calculer la somme d’une plage tout en excluant délibérément certaines cellules — par exemple, parce qu’elles contiennent des valeurs aberrantes, des erreurs, des données non pertinentes ou des éléments spécifiques que vous souhaitez omettre du total. Comme le montre la capture d’écran ci-dessous, imaginez une colonne de chiffres dont vous souhaitez additionner tous les éléments, sauf ceux situés en A3 et A4. Cet article présente de façon exhaustive plusieurs méthodes pratiques pour faire la somme d’une plage dans Excel tout en ignorant une ou plusieurs cellules précises, afin de garantir des résultats exacts pour vos analyses financières, vos rapports ou votre gestion de stocks nécessitant des calculs ciblés.
- Exclure des cellules d’une colonne de la somme à l’aide d’une formule
- Code VBA – Faire la somme d’une plage par programmation, en ignorant/excluant des cellules spécifiques
- Formule Excel – Utiliser SOMME.SI/SOMME.SI.ENS pour inclure uniquement les valeurs qui ne correspondent pas aux critères d’exclusion
- Formule Excel – Utiliser la fonction FILTRE dans les versions récentes d’Excel pour filtrer les cellules exclues avant de faire la somme
Exclure des cellules d’une colonne de la somme à l’aide d’une formule
En intégrant une simple opération arithmétique dans votre formule SOMME, vous pouvez exclure directement les cellules indésirables de votre calcul. Cette méthode est idéale pour des calculs rapides lorsque le nombre d’éléments à exclure reste limité. Voici la marche à suivre :
1. Sélectionnez une cellule vide pour afficher le résultat de la somme, saisissez la formule suivante dans la barre de formule, puis appuyez sur Entréepour 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 l’intégralité de la plage, tandis que SOMME(A3:A4) soustrait les valeurs des cellules exclues. Cette méthode est particulièrement adaptée lorsque les cellules à exclure sont contiguës.
- Vous pouvez facilement combiner et soustraire plusieurs cellules d’exclusion, même lorsqu’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
- Lorsque les exclusions sont nombreuses ou dispersées, lister manuellement chaque cellule exclue allonge les formules et les rend plus difficiles à gérer.
- Attention aux références de cellules : si vos données ou votre plage évoluent, pensez à mettre à jour votre formule en conséquence pour éviter toute erreur.
Code VBA – Faire la somme d’une plage par programmation, en ignorant/excluant des cellules spécifiques
Dans les cas où vous avez de nombreuses exclusions ou devez répéter fréquemment cette opération, une macro VBA offre flexibilité et automatisation. Avec VBA, vous pouvez faire la somme d’une Plage limitée et exclure autant de cellules que nécessaire, qu’elles soient contiguës ou non, en les définissant par programmation. Cette méthode convient aux utilisateurs à l’aise avec l’environnement VBA et souhaitant rationaliser une logique d’exclusion plus complexe.
Précautions : Les macros VBA peuvent modifier votre classeur. Sauvegardez toujours votre travail avant d’exécuter un nouveau code. Les macros doivent être activées pour que le code ci-dessus fonctionne.
1. Accédez à Outils de développement > Visual Basic pour ouvrir l’éditeur VBA. Dans la fenêtre Projet, cliquez avec le bouton droit sur votre classeur, puis sélectionnez Insérer > Module, et 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 sur 
Exécuter dans la fenêtre VBA ou appuyez sur F5 pour lancer la macro. Une boîte de dialogue s’ouvrira pour vous demander de sélectionner d’abord la plage complète à additionner, puis les cellules à exclure (maintenez la touche Ctrl enfoncée pour en sélectionner plusieurs). Le résultat s’affichera immédiatement dans une boîte de message.
- Si vous sélectionnez des cellules par erreur, relancez simplement la macro. Le processus est interactif et s’adapte facilement à vos besoins.
- Les macros sont idéales pour les tâches récurrentes ou les exclusions complexes basées sur des critères.
Formule Excel – Utiliser SOMME.SI ou SOMME.SI.ENS pour inclure uniquement les valeurs qui ne correspondent pas aux critères d’exclusion
Pour des exclusions plus avancées ou basées sur une logique spécifique, utilisez les fonctions SOMME.SI ou SOMME.SI.ENS. Ces formules s’avèrent particulièrement efficaces lorsque vos exclusions reposent sur des valeurs précises, des critères définis ou une liste d’éléments à exclure.
Exemple – Exclure selon une valeur spécifique
1.Si vous souhaitez faire la somme de A2:A7 tout en excluant la valeur « 16 », saisissez la formule suivante dans votre cellule cible (par exemple, en B1) :
=SUMIF(A2:A7,"<>16")Cette formule additionne toutes les valeurs de la plage A2:A7, à l’exception de celles égales à 16.
2. Après avoir saisi la formule, appuyez sur Entrée. Vous pouvez copier ou adapter les plages et références de cellules selon vos besoins.
Exemple – Exclure toutes les cellules correspondant à la valeur d’une autre cellule
Supposons que la cellule C1 contienne la valeur que vous souhaitez exclure de la somme :
=SUMIF(A2:A7,"<>"&A3)Mettez C1 à jour selon vos besoins, et la formule exclura automatiquement toutes les valeurs correspondantes.
- Pour des critères d’exclusion multiples ou des règles plus complexes, envisagez d’utiliser SOMME.SI.ENS combiné à des colonnes auxiliaires ou à des tableaux. Toutefois, SOMME.SI et SOMME.SI.ENS donnent les meilleurs résultats lorsque les exclusions reposent sur des critères précis et cohérents, et non sur des positions de cellules arbitraires.
- Si votre plage contient du texte ou des cellules vides, SOMME.SI les ignore automatiquement ; assurez-vous que ce comportement correspond bien à vos attentes.
Formule Excel – Utiliser la fonction FILTRE (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 les versions ultérieures, la fonction FILTRE permet d’exclure de manière dynamique et flexible des cellules avant d’appliquer SOMME. Cette méthode est particulièrement utile pour les grands jeux de données ou lorsque les critères d’exclusion varient.
Exemple – Exclure des valeurs spécifiques (par ex., 16 et 13)
1.Saisissez la formule suivante dans votre cellule cible (par exemple, en B1) :
=SUM(FILTER(A2:A7,(A2:A7<>16)*(A2:A7<>13)))Cette formule additionne toutes les valeurs de A2:A7, à l’exception de celles égales à 16 et 13. La fonction FILTRE crée un tableau ne contenant que les cellules différentes de ces valeurs, puis SOMME les additionne.
2. Appuyez sur Entrée. Le calcul sera automatiquement mis à jour si les exclusions ou les données sources changent.
- Pour exclure dynamiquement des valeurs à partir d’une liste (par exemple, la liste d’exclusion se trouve en C2:C4) :
=SUM(FILTER(A2:A7,ISNA(MATCH(A2:A7,C2:C4,0))))Cette formule exclut automatiquement toute valeur de A2:A7 qui apparaît dans la plage C2:C4. Il vous suffit de mettre à jour votre liste d’exclusion dans la colonne C pour que les résultats soient instantanément actualisés.
- L’approche basée sur FILTRE est recommandée aux utilisateurs disposant des dernières versions d’Excel et souhaitant une logique d’exclusion dynamique et évolutive.
- Si vous obtenez une erreur #CALC!, vérifiez qu’au moins une valeur subsiste dans la plage après toutes les exclusions ; sinon, FILTRE renvoie une erreur.
En résumé, Excel propose plusieurs solutions pratiques pour faire la somme d’une plage tout en excluant des cellules ou des valeurs spécifiques. Les formules simples conviennent aux exclusions rapides et limitées, tandis que SOMME.SI/SOMME.SI.ENS et FILTRE prennent en charge des scénarios plus flexibles fondés sur des conditions. Le VBA est idéal lorsque les exclusions sont nombreuses, variées ou nécessitent une automatisation. Vérifiez toujours attentivement les références de cellules et les ajustements de formule lors de la modification de votre Données source. En cas d’erreur, examinez vos plages ou listes d’exclusion et essayez de réappliquer vos formules ou de relancer la macro.
Articles associés:
- 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 Valeur minimale dans une plage en excluant les valeurs nulles dans Excel ?
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