Comment filtrer des données en excluant la ligne de total inférieure dans Excel ?
Dans le traitement quotidien des données avec Excel, il est courant de calculer une ligne de total au bas de votre ensemble de données pour résumer des chiffres clés tels que les totaux, les moyennes, les décomptes et autres agrégations. Cependant, lorsque vous utilisez la fonction de filtre d'Excel, la ligne de total est souvent soumise aux mêmes filtres que le reste de vos données, ce qui signifie qu'elle peut être masquée lorsque des filtres sont appliqués. Dans de nombreux scénarios – en particulier dans les rapports de synthèse, les états financiers ou l'analyse de données en cours – vous pouvez avoir besoin que la ligne de total reste toujours visible au bas de vos données, quelles que soient les filtres appliqués au reste de votre feuille de calcul, comme illustré dans la capture d'écran ci-dessous.
Cet article vous guidera à travers plusieurs méthodes pratiques pour garantir que la ligne de total inférieure reste toujours visible lors du filtrage. Chaque méthode convient à différents scénarios d'utilisation et préférences utilisateur. L'article couvre non seulement les fonctionnalités natives d'Excel, mais inclut également des approches basées sur des formules et des solutions VBA programmables, offrant un éventail d'options selon vos besoins.
➤ Filtrer des données en excluant la ligne de total inférieure en créant un tableau
➤ Filtrer des données en excluant la ligne de total inférieure en insérant une formule
➤ Filtrer des données en excluant la ligne de total inférieure en utilisant du code VBA
Filtrer des données en excluant la ligne de total inférieure en créant un tableau
La manière la plus simple de garder votre ligne de total visible tout en filtrant est de convertir vos données en un Tableau Excel officiel. Les Tableaux Excel disposent d'une fonction de ligne de total native intelligemment conçue pour rester visible au bas de vos données même lorsque vous filtrez d'autres lignes. Voici les étapes détaillées, les scénarios courants et les remarques pour cette méthode :
1. Si vos données incluent déjà une ligne de total calculée manuellement (c'est-à-dire une ligne avec des formules de total non intégrées dans un tableau), supprimez temporairement cette ligne pour éviter toute duplication.
2. Sélectionnez la plage de vos données réelles (en excluant la ligne de total créée manuellement). Ensuite, allez dans Insertion > Tableau. Dans la boîte de dialogue Créer un tableau, assurez-vous que l'option Mon tableau a des en-têtes est cochée si vos données incluent des en-têtes de colonne. Revisez la plage de données pour confirmer que toutes vos lignes sont incluses.
3. Après avoir cliqué sur OK, vos données seront formatées sous forme de Tableau Excel. Tandis que le tableau est sélectionné, allez dans l'onglet Conception (ou Design de Tableau dans certaines versions). Dans le groupe Options de Style de Tableau, cochez la case ligne de total. Excel ajoutera automatiquement une nouvelle ligne de total au bas.
4. Vous pouvez maintenant utiliser les flèches de filtre sur n'importe quelle colonne pour filtrer vos données selon vos besoins. La ligne de Total apparaîtra toujours comme la dernière ligne du tableau, quelles que soient les filtres appliqués.
Scénarios applicables : Cette méthode est idéale pour les ensembles de données fréquemment mis à jour, partagés entre équipes ou utilisés dans des rapports récurrents – en particulier lorsque vous bénéficiez des fonctionnalités natives des tableaux comme les lignes bicolores, les références structurées et le formatage automatique.
Avantages :
- Intégration transparente avec les fonctionnalités natives d'Excel
- Pas besoin de mettre à jour les formules lorsque les données augmentent ou diminuent
- La ligne de total reste dynamique et mise à jour automatiquement
Limitations :
- Les formules personnalisées dans la ligne de total doivent être reconfigurées après conversion en Tableau
- Les grands ensembles de données peuvent connaître un léger ralentissement de performance en raison du formatage en temps réel
- Assurez-vous de supprimer toute ligne de total créée manuellement avant de convertir en Tableau, sinon vous risquez de voir des totaux en double.
- Effacez les filtres avant d'ajouter ou de supprimer des données pour maintenir la structure du tableau cohérente.
Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Filtrer des données en excluant la ligne de total inférieure en insérant une formule
Si vous ne voulez pas utiliser un Tableau Excel, vous pouvez conserver une ligne de total manuelle au bas de vos données en appliquant la formule SOUS.TOTAL. La fonction SOUS.TOTAL est spécialement conçue pour fonctionner dynamiquement avec les filtres Excel – son calcul reflète uniquement les lignes visibles (filtrées), mais la formule elle-même ne sera ni masquée ni exclue lors du filtrage. Cette approche est flexible et fonctionne bien avec des plages de données classiques.
1. Dans la première cellule de votre ligne de total prévue, directement sous votre tableau de données, saisissez la formule suivante (par exemple, si vos valeurs sont dans la colonne B de la ligne 2 à 13) :
=SUBTOTAL(9, B2:B13)Remplacez B2:B13 par la plage correspondant à la colonne et aux lignes réelles de vos données. Le chiffre 9 dans la formule spécifie la fonction SOMME ; vous pouvez utiliser différents numéros de fonction selon les besoins (par exemple, 1 pour MOYENNE, 2 pour NB, etc.).
2. Appuyez sur Entrée pour confirmer. La ligne de total affichera immédiatement le total calculé basé sur les lignes visibles (non filtrées). Lorsque vous appliquez des filtres en utilisant les boutons de filtre natifs d'Excel, la ligne de total reste visible au bas et montre toujours le sous-total actuel uniquement pour les lignes visibles.
SOUS.TOTAL si vos données augmentent ou diminuent. Cette formule ne s'étendra pas automatiquement à moins que vous n'utilisiez une fonction de plage dynamique comme DECALER ou INDEX.Résumé : La formule SOUS.TOTAL prend en charge une variété de fonctions d'agrégation (comme MOYENNE, NB, etc.), la rendant flexible pour les lignes de synthèse utilisées dans les workflows financiers, d'inventaire et de reporting.
Filtrer des données en excluant la ligne de total inférieure en utilisant du code VBA
Pour les utilisateurs travaillant avec de grands ensembles de données, ou ceux cherchant une approche automatisée pour garantir que la ligne de total inférieure est toujours affichée (et jamais masquée, quelles que soient les configurations de filtre), vous pouvez utiliser VBA (Visual Basic for Applications) pour gérer le filtrage de manière programmable. Cette solution est particulièrement utile dans les classeurs où la plage de données ou le placement de la ligne de total change dynamiquement, ou lorsque vous souhaitez imposer une cohérence pour tous les utilisateurs.
Cet exemple VBA fonctionne en vérifiant la dernière ligne de votre plage de données et en la forçant à rester visible, même après avoir appliqué des filtres. Il garantit que la ligne de total est toujours affichée et peut être réutilisée en attribuant la macro à un bouton.
Étapes pour utiliser cette solution VBA :
1. Dans Excel, accédez à l'onglet Développeur. Si vous ne voyez pas cet onglet, vous devrez peut-être l'activer depuis les Options Excel en cochant "Développeur" dans la section Personnaliser le Ruban.
2. Cliquez sur Visual Basic pour ouvrir l'éditeur VBA. Dans la nouvelle fenêtre, cliquez sur Insertion > Module, puis collez le code suivant dans le nouveau module :
Sub AlwaysShowTotalRow()
Dim ws As Worksheet
Dim lastRow As Long
On Error Resume Next
Set ws = ActiveSheet
' Get last used row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Ensure total row is visible before applying filter
ws.Rows(lastRow).Hidden = False
' If filtering is active, reapply filter
If ws.FilterMode Then
ws.ShowAllData
End If
' Make sure total row remains visible
ws.Rows(lastRow).Hidden = False
End Sub3. Après avoir entré le code, fermez l'éditeur VBA. Dans Excel, appuyez sur Alt + F8, sélectionnez ToujoursAfficherLigneTotal, et cliquez sur Exécuter. La macro garantira que la dernière ligne (votre ligne de total) soit visible même après l'application des filtres.
• La macro suppose que la ligne de total est la dernière ligne utilisée dans la colonne A. Vous pouvez ajuster la logique si vos données incluent des lignes vides ou plusieurs résumés.
• Envisagez d'attribuer cette macro à un bouton pour une réutilisation facile si vos données changent fréquemment.
• Si votre classeur contient plusieurs sections, utilisez une plage nommée ou un marqueur de colonne spécifique pour localiser la bonne ligne de total de manière plus fiable.
Avantages : Automatise la visibilité de la ligne de total indépendamment des actions de l'utilisateur ou du filtrage. Idéal pour les tableaux de bord, les rapports ou les classeurs partagés.
Limitations : Nécessite un classeur activé pour les macros et des permissions appropriées pour les macros. Peut nécessiter une personnalisation pour des dispositions de feuille plus complexes.
Résumé : Cette approche VBA est recommandée pour les environnements automatisés où le contrôle manuel des lignes de total est peu fiable ou inconvenant. Elle aide à maintenir la visibilité des données de synthèse dans tous les cas d'utilisation.
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