Comment additionner uniquement les cellules visibles en fonction de critères dans Excel ?
Dans Excel, les utilisateurs peuvent généralement additionner des cellules en fonction de critères spécifiques à l'aide de la fonction SOMME.SI. Cependant, lorsqu'il s'agit de données filtrées, appliquer simplement SOMME.SI inclura à la fois les cellules visibles et masquées dans le calcul. Cela entraîne souvent des résultats incorrects si vous devez additionner uniquement les cellules visibles (c'est-à-dire non filtrées) qui correspondent à des critères particuliers, comme illustré dans la capture d'écran ci-dessous.
Additionner uniquement les cellules visibles basées sur un ou plusieurs critères avec une formule
Additionner uniquement les cellules visibles basées sur des critères en utilisant du code VBA
Il est courant dans les flux de travail de reporting quotidien et d'analyse de données de devoir agréger avec précision des données dans des tableaux filtrés, par exemple lors du calcul des montants de ventes pour un produit ou une catégorie particulière après avoir appliqué certains filtres. Faire cela incorrectement peut entraîner des totaux incluant des données que vous n'aviez pas l'intention d'inclure, il est donc important d'utiliser des techniques qui additionnent uniquement les données visibles que vous voyez à 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 éventuelles limitations. Vous pouvez sélectionner une solution qui correspond le mieux à la taille de votre feuille de calcul, à la structure de vos données et à vos habitudes opérationnelles. Des étapes détaillées pour chaque solution sont fournies ci-dessous, ainsi que des explications des erreurs potentielles et des moyens d'optimiser le processus de calcul pour des résultats plus fiables.
Additionner uniquement les cellules visibles basées sur un ou plusieurs critères avec une colonne d'aide
L'une des approches les plus intuitives et stables pour additionner des cellules visibles basées sur des critères spécifiques consiste à utiliser une colonne d'aide qui renvoie des valeurs uniquement pour les lignes visibles, puis à exploiter la fonction SOMME.SI avec vos conditions souhaitées. Cette méthode est particulièrement efficace si votre ensemble de données est fréquemment filtré de diverses manières ou si vous devez configurer des calculs que vos collègues peuvent facilement comprendre ou modifier.
Avantages : Simple à mettre en place ; toute la logique et les calculs restent visibles dans la feuille de calcul ; idéal pour les petits et moyens tableaux ; robuste lorsqu'il est nécessaire d'ajuster ou d'auditer les formules.
Limitations : Crée des colonnes supplémentaires ; peut nécessiter des mises à jour des formules si la disposition des lignes change ; une utilisation extensive peut devenir encombrante pour les très grands ensembles de données.
Par exemple, pour additionner uniquement les valeurs des commandes pour le produit "Hoodie" dans une plage filtrée :
1. Entrez ou copiez la formule suivante dans une colonne vide à côté de votre ensemble de données (par exemple, dans la cellule E2, en supposant que D soit votre colonne de valeurs) :
Faites glisser la poignée de recopie vers le bas pour remplir cette formule dans toutes les lignes de votre plage de données. Cette formule renverra la valeur de la colonne D si la ligne est visible et 0 si la ligne est masquée par un filtre.
2. Après avoir généré les valeurs d'aide dans la colonne E, utilisez une fonction SOMME.SI pour additionner uniquement les valeurs visibles en fonction de vos critères. Par exemple, pour additionner pour "Hoodie" dans la colonne A :
Vous pouvez ajouter plus de critères en étendant les arguments de SOMME.SI.ENS dans le format =SOMME.SI.ENS(plage_somme, plage_critères1, critères1, [plage_critères2, critères2], [plage_critères3, critères3], ...). Vérifiez toujours vos plages pour assurer un alignement correct et des résultats attendus.
Attention : Si vous réorganisez, insérez ou supprimez des lignes après avoir configuré vos formules, vérifiez deux fois que toutes les références correspondent encore à votre structure de données. Parfois, des erreurs peuvent résulter de plages mal alignées ou de l'oubli de mettre à jour vos cellules de critères.
Additionner uniquement les cellules visibles basées sur des critères avec une formule
Si vous préférez une solution basée sur une formule qui ne nécessite pas l'ajout de colonnes d'aide, vous pouvez utiliser une combinaison des fonctions SOMMEPROD, SOUS.TOTAL, DECALER, LIGNE et MIN pour additionner les cellules visibles selon des critères spécifiques. Cette approche convient aux utilisateurs expérimentés d'Excel familiarisés avec les formules matricielles, et est particulièrement utile lorsque vous souhaitez garder votre feuille propre sans colonnes supplémentaires.
Avantages : Pas besoin de colonnes supplémentaires dans la feuille de calcul ; flexible et dynamique ; la formule se met à jour instantanément lorsque vous filtrez ou modifiez les critères.
Limitations : Les formules peuvent être complexes à lire ou à déboguer, surtout pour ceux qui ne sont pas familiers avec les fonctions matricielles ; les performances peuvent ralentir dans les très grands tableaux.
Copiez ou entrez la formule suivante dans une cellule vide (par exemple, pour additionner les cellules visibles pour "Hoodie" dans A2:A12, avec les valeurs réelles dans D2:D12, et les critères dans A17) :
Après avoir entré la formule, appuyez sur Entrée pour obtenir le résultat souhaité, comme indiqué ci-dessous :
Attention : Cette approche est sensible aux plages spécifiées - des plages incohérentes ou chevauchantes peuvent déclencher des erreurs ou des résultats inattendus. Testez les cas limites, surtout lorsque le filtrage modifie le nombre ou la position des lignes visibles.
Additionner uniquement les cellules visibles basées sur des critères en utilisant du code VBA
Pour les utilisateurs avancés, l'utilisation de VBA offre une solution flexible pour additionner uniquement les cellules visibles selon des critères spécifiques, en particulier lors de la gestion de scénarios complexes ou de grands ensembles de données où les formules standard peuvent souffrir de goulets d'étranglement de performance ou lorsque les critères incluent une logique multi-conditions difficile à exprimer dans une seule formule. VBA peut itérer à travers chaque ligne visible, tester vos conditions et calculer la somme efficacement. Cela est particulièrement adapté aux tâches de reporting répétitives ou lors de l'automatisation des calculs de synthèse.
Avantages : Peut facilement gérer de grands ensembles de données, plusieurs ou critères dynamiques et une logique complexe ; le processus s'exécute rapidement même avec des milliers de lignes ; réduit le risque d'erreurs dues aux modifications manuelles des formules.
Limitations : Nécessite l'activation des macros ; certains utilisateurs peuvent ne pas être familiers avec VBA ou ne pas avoir les permissions adéquates ; les modifications nécessitent un accès à l'Éditeur de Macro. Effectuez toujours une sauvegarde avant d'exécuter du VBA sur des ensembles de données importants.
1. Pour commencer, ouvrez l'Éditeur VBA en cliquant sur Outils de Développeur > Visual Basic. Dans la fenêtre qui apparaît, allez dans Insertion > Module, et 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 exécuter le code. Une boîte de dialogue vous invitera à sélectionner à la fois la plage de critères (tels que vos noms de produits), la plage de valeurs à additionner et la valeur que vous souhaitez comme filtre (par exemple, "Hoodie"). La macro additionnera uniquement les lignes visibles où vos critères sont remplis et affichera le résultat dans un message contextuel.
Conseils pratiques : Utilisez ce code VBA lorsque vous devez souvent recalculer vos sommes après avoir modifié vos données ou vos filtres. Vous pouvez développer davantage le code VBA pour fonctionner avec plusieurs critères en ajoutant plus d'invitations de saisie ou de conditions logiques.
Dépannage : Assurez-vous toujours que les plages que vous sélectionnez pour les critères et les valeurs ont le même nombre de lignes et appartiennent aux mêmes colonnes que vos données filtrées. Si le code signale une erreur ou ne renvoie pas la somme attendue, revérifiez vos paramètres de filtre et votre sélection active.
Suggestions de synthèse : Pour une analyse de données nécessitant des calculs répétés uniquement sur les cellules visibles, enregistrer cette macro dans votre Classeur de Macros Personnel peut accélérer vos rapports quotidiens. Si une boîte de dialogue n'apparaît pas, vérifiez vos paramètres de macro et vos permissions de sécurité.
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