Comment additionner uniquement les cellules visibles répondant à des critères spécifiques dans Excel ?
Dans Excel, les utilisateurs peuvent généralement additionner des cellules selon des critères spécifiques à l’aide de la fonction SOMME.SI.ENS. Toutefois, lorsqu’ils travaillent avec des données filtrées, l’utilisation classique de SOMME.SI.ENS inclut à la fois les cellules visibles et celles masquées par le filtre dans le calcul. Cela conduit souvent à des résultats erronés si l’objectif est d’additionner uniquement les cellules visibles — c’est-à-dire non filtrées — qui répondent à certains critères, comme le montre la capture d’écran ci-dessous.
Additionner uniquement les cellules visibles selon un ou plusieurs critères à l’aide d’une formule
Additionner uniquement les cellules visibles selon des critères à l’aide d’un code VBA
Il est courant, dans les rapports quotidiens et les flux de travail Analyse des données, de devoir agréger précisément des données dans des tableaux filtrés, par exemple pour calculer les montants des ventes d’un produit ou d’une catégorie spécifique après application de filtres. Une mauvaise méthode peut entraîner des totaux incluant des données non souhaitées ; il est donc essentiel d’utiliser des techniques qui n’additionnent que les données visibles à l’écran.
Cet article présente plusieurs méthodes pratiques adaptées à différents scénarios et niveaux de compétence, chacune ayant ses avantages et ses limites potentielles. Vous pouvez choisir la solution la mieux adaptée à la taille de votre feuille de calcul, à la structure de vos données et à vos habitudes opérationnelles. Des étapes détaillées sont fournies ci-dessous pour chaque solution, accompagnées d’explications sur les erreurs possibles et des conseils pour optimiser le processus de calcul afin d’obtenir des résultats plus fiables.
Additionner uniquement les cellules visibles selon un ou plusieurs critères à l’aide d’une colonne auxiliaire
L’une des approches les plus intuitives et stables pour additionner des cellules visibles selon des critères spécifiques consiste à utiliser une colonne auxiliaire qui renvoie une valeur uniquement pour les lignes visibles, puis à appliquer la fonction SOMME.SI.ENS avec vos conditions souhaitées. Cette méthode s’avère particulièrement efficace si votre jeu de données est fréquemment filtré de diverses manières ou si vous devez mettre en place des calculs clairs et facilement modifiables par vos collègues.
Avantages : Facile à mettre en place ; toute la logique et les calculs restent visibles dans la feuille de calcul ; idéal pour les petits et moyens tableaux ; particulièrement robuste pour ajuster ou vérifier les formules.
Limites : Crée des colonnes supplémentaires ; peut nécessiter de mettre à jour les formules si la disposition des lignes change ; une utilisation intensive peut vite devenir fastidieuse avec des jeux de données très volumineux.
Par exemple, pour additionner uniquement les valeurs des commandes du produit « Hoodie » dans un(e) Plage de filtrage :
1. Saisissez ou copiez la formule suivante dans une colonne vide adjacente à votre jeu de données (par exemple, dans la cellule E2, en supposant que la colonne D contienne vos valeurs) :
Faites glisser la poignée de recopie vers le bas pour étendre cette formule à toutes les lignes de votre Plage de données. Cette formule renvoie la valeur de la colonne D si la ligne est visible, et 0 si elle est masquée par un filtre.
2. Après avoir généré les valeurs auxiliaires dans la colonne E, utilisez la fonction SOMME.SI.ENS pour n’additionner que les valeurs visibles répondant à vos critères. Par exemple, pour additionner les valeurs correspondant à « Hoodie » dans la colonne A :
Vous pouvez ajouter davantage de critères en étendant les arguments de la fonction SOMME.SI.ENS selon le format suivant :=SOMME.SI.ENS(plage_somme; plage_critère1; critère1; [plage_critère2; critère2]; [plage_critère3; critère3]; ...). Veillez toujours à ce que vos plages soient parfaitement alignées pour obtenir des résultats fiables.
Attention : si vous réorganisez, insérez ou supprimez des lignes après avoir configuré vos formules, vérifiez soigneusement que toutes les références correspondent toujours à la structure actuelle de vos données. Certaines erreurs peuvent en effet résulter de plages mal alignées ou de l’oubli de mise à jour des cellules de critères.
Additionner uniquement les cellules visibles selon des critères à l’aide d’une formule
Si vous préférez une solution fondée sur une formule et ne nécessitant aucune colonne auxiliaire, combinez les fonctions SOMMEPROD, SOUS.TOTAL, DECALER, LIGNE et MIN pour additionner uniquement les cellules visibles selon vos critères. Cette méthode s’adresse principalement aux utilisateurs expérimentés d’Excel à l’aise avec les formules matricielles, et s’avère particulièrement utile lorsque vous souhaitez conserver une feuille épurée, sans colonnes supplémentaires.
Avantages : Aucune colonne supplémentaire requise ; solution flexible et dynamique ; la formule se met à jour instantanément dès que vous appliquez un filtre ou modifiez les critères.
Limites : Les formules peuvent être complexes à lire ou à déboguer, surtout pour ceux qui ne maîtrisent pas les fonctions matricielles ; elles peuvent aussi ralentir les performances sur des tableaux très volumineux.
Copiez ou saisissez la formule suivante dans une cellule vide (par exemple, pour additionner les cellules visibles correspondant à « Hoodie » dans A2:A12, avec Valeur réelle dans D2:D12, et le critère dans A17) :
Après avoir saisi la formule, appuyez sur Entréepour obtenir le résultat souhaité, comme illustré ci-dessous :
Attention : cette approche est sensible aux plages spécifiées — des plages incompatibles ou chevauchantes peuvent provoquer des erreurs ou des résultats inattendus. Testez les cas limites, en particulier lorsque le filtrage modifie le nombre ou la position des lignes visibles.
Additionner uniquement les cellules visibles selon des critères à l’aide d’un code VBA
Pour les utilisateurs avancés, le recours au VBA constitue une solution flexible permettant d’additionner uniquement les cellules visibles selon des critères précis — notamment dans des scénarios complexes ou avec de grands jeux de données, où les formules standard peuvent souffrir de problèmes de performance, ou lorsque les critères reposent sur une logique multi-conditions difficile à exprimer en une seule formule. Grâce au VBA, il est possible de parcourir chaque ligne visible, d’évaluer vos conditions et de calculer la somme de manière efficace. Cette approche se révèle particulièrement adaptée aux tâches de reporting répétitives ou à l’automatisation de calculs de synthèse.
Avantages : Gère facilement de grands jeux de données, des critères multiples ou dynamiques et des logiques complexes ; le traitement s’exécute rapidement, même avec des milliers de lignes ; réduit les risques d’erreurs liées aux modifications manuelles de formules.
Limites : Nécessite l’activation des macros ; certains utilisateurs peuvent ne pas maîtriser le VBA ou ne pas disposer des autorisations requises ; toute modification exige un accès à l’éditeur de macros. Sauvegardez toujours vos données importantes avant d’exécuter du code VBA.
1. Pour commencer, ouvrez l’Éditeur VBA en cliquant sur Outils de développement > Visual Basic. Dans la fenêtre qui s’ouvre, accédez à Insertion > Module, puis collez le code suivant dans le nouveau module :
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2. Cliquez sur le bouton 
« Exécuter » (ou appuyez sur)F5) pour lancer le code. Une boîte de dialogue s’ouvrira alors pour vous permettre de sélectionner à la fois la plage de critères (par exemple, vos noms de produits), la plage des valeurs à additionner et la valeur à utiliser comme filtre (par exemple, « Hoodie »). La macro additionnera uniquement les lignes visibles correspondant à vos critères et affichera le résultat dans un message contextuel.
Conseils pratiques :Utilisez ce code VBA lorsque vous devez recalculer fréquemment vos totaux après avoir modifié vos données ou ajusté vos filtres. Vous pouvez également étendre ce code pour gérer plusieurs critères en ajoutant davantage d’invites ou de conditions logiques.
Dépannage : Veillez toujours à ce que les plages sélectionnées pour les critères et les valeurs comportent le même nombre de lignes et correspondent exactement aux colonnes de vos données filtrées. Si le code signale une erreur ou ne renvoie pas la somme attendue, revérifiez attentivement vos paramètres de filtre ainsi que votre sélection active.
Recommandations récapitulatives : Pour les analyses de données nécessitant des calculs répétés portant uniquement sur les cellules visibles, enregistrer cette macro dans votre classeur personnel de macros peut accélérer la création de vos rapports quotidiens. Si aucune boîte de dialogue n’apparaît, vérifiez vos paramètres de macro et vos autorisations de sécurité.
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