Comment compter / additionner les cellules barrées dans Excel ?
Dans Excel, il est courant d'appliquer un formatage barré à certaines cellules afin de marquer des données comme obsolètes, non pertinentes ou invalides. Ce repère visuel peut grandement améliorer la gestion des données et vous aider à vous concentrer sur les informations importantes lors de l'analyse. Cependant, lorsqu'il s'agit d'effectuer des calculs - tels que le décompte ou la somme - vous pourriez vouloir séparer les résultats des cellules barrées de celles qui ne le sont pas. Comprendre comment gérer ces scénarios peut rationaliser votre flux de travail et conduire à des analyses plus précises. Cet article propose plusieurs méthodes pratiques pour compter et additionner des cellules avec ou sans formatage barré dans Excel, en utilisant des fonctions VBA personnalisables qui offrent une flexibilité pour divers besoins de gestion des données.
Compter les cellules barrées avec VBA dans Excel
Compter sans les cellules barrées avec VBA dans Excel
Somme excluant les cellules barrées avec VBA dans Excel
Compter ou Additionner des Cellules Barrées en Utilisant Kutools pour Excel
Compter les cellules barrées dans Excel
Si vous devez déterminer le nombre total de cellules qui ont été formatées avec un texte barré dans une plage spécifique, cela ne peut pas être réalisé directement avec les fonctions intégrées d'Excel, car il n'existe aucune formule qui reconnaisse les formats de texte comme le barré. Par conséquent, utiliser une fonction définie par l'utilisateur (UDF) en VBA devient une solution pratique. Cette approche est particulièrement utile lors de la gestion des listes de tâches, des mises à jour d'inventaire, ou tout autre scénario où le barré est utilisé pour indiquer des points de données terminés, invalides ou obsolètes.
1. Maintenez enfoncées les touches ALT + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Dans la fenêtre VBA, cliquez sur Insertion > Module pour créer un nouveau module, puis collez le code suivant dans la fenêtre du Module.
Code VBA : Compter les cellules barrées
Public Function CountStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If pRng.Font.Strikethrough Then
xOut = xOut + 1
End If
Next
CountStrike = xOut
End Function
3. Après avoir entré le code, enregistrez votre classeur (pour conserver la macro), puis fermez l'éditeur VBA et revenez à votre feuille de calcul. Dans n'importe quelle cellule vide où vous souhaitez que le résultat apparaisse, entrez la formule suivante (par exemple, pour vérifier les cellules barrées dans la plage A2:B14) : =CountStrike(A2:B14)
Astuce : Vous pouvez modifier A2:B14 par n'importe quelle autre plage que vous souhaitez vérifier.
4. Appuyez sur la touche Entrée pour exécuter la formule, et Excel affichera immédiatement le nombre de toutes les cellules barrées dans la plage spécifiée. Cela vous permet de suivre efficacement la progression ou de supprimer les entrées obsolètes si nécessaire. Voir capture d'écran :
Précaution : Pour vous assurer que ce code VBA reste disponible pour une utilisation future, vous devez enregistrer le fichier au format .xlsm (Classeur Excel prenant en charge les macros).
Compter sans les cellules barrées dans Excel
Il existe des cas où vous pourriez avoir besoin de compter uniquement les cellules qui n'ont pas le formatage barré dans une plage donnée. Par exemple, dans les tableaux de suivi de projet ou les données de vente, vous souhaitez souvent compter uniquement les entrées actives/valides (c'est-à-dire celles sans barre). Les fonctions COUNT ou COUNTA par défaut d'Excel ignorent le formatage du texte, donc une solution VBA adaptée offre un contrôle précis sur ces besoins.
1. Appuyez sur ALT + F11 pour lancer la fenêtre Microsoft Visual Basic pour Applications.
2. Allez dans Insertion > Module pour ajouter un nouveau module de code. Collez le code suivant dans cette fenêtre de module.
Code VBA : Compter sans les cellules barrées
Public Function CountNoStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If Not pRng.Font.Strikethrough Then
xOut = xOut + 1
End If
Next
CountNoStrike = xOut
End Function
3. Enregistrez votre classeur prenant en charge les macros, revenez à la feuille de calcul, et dans une cellule vide, entrez la formule (par exemple, pour A2:B14) : =countnostrike(A2:B14) Ensuite, appuyez sur Entrée pour obtenir le résultat. Excel comptera uniquement les cellules de la plage sélectionnée qui n'ont pas le formatage barré.
Remarque : Dans l'exemple ci-dessus, A2:B14 est la plage de cellules évaluée. Ajustez la référence de plage selon les besoins pour votre feuille de calcul. Soyez également conscient que les cellules fusionnées ou les formats spéciaux peuvent parfois affecter les résultats - double-vérifiez que votre plage contient uniquement les cellules prévues.
Somme excluant les cellules barrées dans Excel
Lorsque vous traitez des données numériques où certaines entrées ne sont plus actives ou doivent être ignorées (c'est-à-dire avec un formatage barré), il est souvent essentiel de n'additionner que les nombres valides. Cela est courant dans les feuilles de budget, la gestion des stocks ou le suivi des tâches, où les entrées barrées représentent des articles obsolètes ou terminés qui doivent être exclus des totaux. Puisque la fonction SOMME intégrée d'Excel ne fait pas la distinction entre les cellules formatées et non formatées, une Fonction Définie par l'Utilisateur (VBA) est nécessaire pour cette opération.
1. Utilisez ALT + F11 pour ouvrir l'éditeur Microsoft Visual Basic pour Applications.
2. Cliquez sur Insertion > Module pour ajouter un module, et collez le code ci-dessous dans cette fenêtre de module.
Code VBA : Somme excluant les cellules barrées
Public Function ExcStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If Not pRng.Font.Strikethrough Then
xOut = xOut + pRng.Value
End If
Next
ExcStrike = xOut
End Function
3. Enregistrez et fermez l'éditeur VBA. Dans votre feuille de calcul cible, choisissez une cellule vide et entrez la formule suivante (par exemple, pour B2:B14) : =excstrike(B2:B14) Appuyez sur Entrée, et Excel additionnera uniquement les cellules de votre plage spécifiée qui n'ont pas de formatage barré, fournissant instantanément une somme qui exclut les valeurs obsolètes.
Remarque : Ajustez B2:B14 à votre plage de données réelle. Cette fonction ignore les cellules barrées indépendamment de leur valeur. Si vous obtenez des résultats inattendus, assurez-vous que toutes les cellules contiennent des entrées numériques et le format correct.
Astuce pratique : Si vous avez besoin d'actualiser la somme après avoir modifié le statut barré d'une cellule, recalculez la feuille de calcul en appuyant sur F9.
Compter ou Additionner des Cellules Barrées en Utilisant Kutools pour Excel
Si vous souhaitez compter ou additionner uniquement les cellules qui ont un format barré, Excel ne propose pas de méthode intégrée pour ce faire - mais Kutools pour Excel facilite la tâche grâce à sa fonction Filtre spécial.
1. Sélectionnez la Colonne
Cliquez sur la colonne qui contient les cellules avec un formatage barré.
2. Ouvrez le Filtre Spécial
Allez dans l'onglet Kutools Plus, puis cliquez sur Filtre Spécial > Filtrer les cellules barrées.
3. Confirmez le Filtre
Une boîte de dialogue apparaîtra, montrant combien de cellules correspondent à la condition. Cliquez sur OK pour continuer.
4. Consultez les Résultats
Les cellules avec un format barré sont maintenant filtrées et visibles. Sélectionnez la plage filtrée - vous verrez instantanément la Somme, la Moyenne et le Décompte dans la barre d'état d'Excel en bas.
✨ Pourquoi utiliser Kutools ?
Kutools pour Excel propose plus de 300 fonctionnalités puissantes et faciles à utiliser comme celle-ci - vous aidant à gagner du temps et à augmenter votre productivité sans écrire de formules complexes ou de code VBA.
Que vous filtriez par format, nettoyiez des données, combiniez des feuilles ou fassiez des modifications en série, Kutools simplifie votre travail en quelques secondes.
Essayez Kutools pour Excel gratuitement pendant 30 jours et découvrez ce que vous avez manqué !
Articles connexes :
Comment additionner / compter les nombres en gras dans une plage de cellules dans Excel ?
Comment compter et additionner des cellules en fonction de la couleur d'arrière-plan dans Excel ?
Comment compter / additionner des cellules en fonction des couleurs de police 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