Fiche
Excel : additionner seulement les cellules visibles
SOUS.TOTAL avec 9 ou 109 : choisir le bon périmètre pour additionner une colonne filtrée et vérifier un total donné par une IA.
Mis à jour le
Pour additionner les valeurs d'une colonne en excluant les lignes filtrées et les lignes masquées manuellement, utilisez SOUS.TOTAL avec le code 109. Si les lignes masquées manuellement doivent rester dans le calcul, utilisez le code 9. Choisissez d'abord le périmètre que vous voulez mesurer.
Deux formules, deux intentions
Microsoft documente ces deux variantes pour une somme :
| Formule d'exemple | Lignes filtrées | Lignes masquées manuellement |
|---|---|---|
=SOUS.TOTAL(9;D2:D100) |
Exclues | Incluses |
=SOUS.TOTAL(109;D2:D100) |
Exclues | Exclues |
La plage doit correspondre à votre colonne de montants. Microsoft précise que ce comportement concerne les lignes : masquer des colonnes n'a pas le même effet sur une plage horizontale. La fonction ignore aussi les sous-totaux imbriqués produits par SOUS.TOTAL.
Tester avec un exemple connu
Prenons trois commandes fictives de 10 €, 20 € et 30 €. Sans exclusion, leur total est 60 €. Si vous filtrez pour conserver les deux dernières commandes, le total attendu est 50 €. Si vous masquez ensuite manuellement la ligne de 20 €, les deux formules répondent à des questions différentes : le code 9 conserve ce montant, le code 109 l'exclut.
Ce petit test permet de décider quelle formule correspond à votre travail. N'utilisez pas le résultat le plus proche de votre attente comme preuve que la formule est bonne : justifiez les lignes incluses.
Fixer la plage et la règle avant de partager
Le code 109 ne choisit pas les commandes à inclure selon leur statut : il applique le calcul aux lignes visibles de la référence fournie. Si une commande livrée se trouve hors de D2:D100, elle ne sera pas ajoutée au total parce que son statut correspond à votre intention. Vérifiez donc la dernière ligne couverte après chaque ajout de données.
Reprenez l’exemple : après le filtre, le code 9 donne 50 € ; après masquage manuel de la ligne de 20 €, il conserve 50 €, tandis que 109 donne 30 €. Ces résultats sont attendus pour ces opérations précises. Si vous remplacez ensuite le filtre, vous changez la population mesurée.
Si la règle « commandes livrées » vient d’une procédure interne, les fonctions de recherche documentaire d’ALMA peuvent servir à retrouver cette définition dans les documents importés. Il reste à traduire la règle en filtres ou en critères de calcul dans le classeur ; ce lien ne suppose pas une connexion automatique à Excel.
Contrôler les totaux déjà présents
Un total visible nul ne signifie pas que le filtre ne retient aucune ligne. Exemple fictif : une vente de 80 € et son avoir de −80 € donnent zéro. Pour compter les lignes visibles, Microsoft associe le code 103 à NBVAL : =SOUS.TOTAL(103;A2:A100) peut accompagner la somme si A contient un identifiant renseigné pour chaque ligne de données. Dans cet exemple, le comptage attendu est deux. Ce contrôle compte les cellules renseignées, pas les identifiants distincts ; utilisez la même étendue de lignes que pour les montants, sans en-tête ni ligne de total.
L’exclusion des SOUS.TOTAL imbriqués ne signifie pas que toute ligne nommée « Total » est ignorée. Une valeur saisie ou une autre formule de somme n’a pas automatiquement ce statut. Dans un exemple fictif, des ventes de 10 € et 20 € suivies d’un total saisi de 30 € peuvent être additionnées deux fois si la référence inclut les trois cellules. Séparez les lignes de données des lignes de présentation avant de calculer.
Comparer au chiffre d'un assistant
Avant de demander « combien avons-nous vendu ? », précisez la période, les statuts de commande et la devise. Notez les filtres actifs et les lignes masquées. Demandez à l'assistant de détailler son périmètre, puis comparez avec votre sous-total.
Si les chiffres diffèrent, commencez par la liste des lignes. Une réponse portant sur toutes les commandes ne se compare pas directement à une somme de commandes filtrées. Pour un périmètre exprimé par des critères explicites dans une formule, voyez SOMME.SI.ENS.
Garder un résultat explicable
Dans votre compte rendu, décrivez la population réellement retenue. Écrivez « total des commandes livrées sur la période » seulement si les filtres et les masquages correspondent exactement à cette règle. Si des lignes livrées sont masquées manuellement, précisez aussi leur exclusion ; « total visible » seul ne permet pas de la comprendre.
Conservez le fichier et la liste des critères avec votre calcul. Si vous partagez seulement un montant, le destinataire ne peut pas comprendre les exclusions. Pour faire expliquer la formule sans partager les commandes, utilisez les prompts de lecture d'une formule Excel, puis suivez la méthode de vérification d'une réponse Copilot.
Sources
Pages consultées pour écrire cet article :