Comment additionner des valeurs filtrées et/ou masquées dans Excel ?
Présentation du problème
Voici une question qui m’a été posée au boulot :
Regarde ce tableau. Toutes les ventes représentent 1010 euros. Mais si je masque certains pays, le total ne change pas. Comment avoir un total correct qui n’additionne pas les lignes filtrées et/ou masquées ?
La fonction SOUS.TOTAL()
La Réponse : en utilisant la fonction SOUS.TOTAL()
Cette fonction est disponible :
- sous Microsoft Excel = SOUS.TOTAL()
- sous Google Sheets = SUBTOTAL()
Prenons ce tableau en exemple :
Ici, nous n’avons ni filtre actif ni ligne masquée, les trois formules (en D12, D14 et D16) indiquent le même résultat.
Filtre actif
Ici, le filtre a été placé sur Espagne. Seules les lignes pour l’Espagne sont affichées.
La cellule D12, continue d’afficher le total de toutes les lignes, y compris celles que le filtre a écartées. Le résultat est faux car il ne tient pas compte du filtre.
La Cellule D14 affiche le bon résultat grâce au code de fonction 9 dont le rôle est de comptabiliser uniquement les lignes que le filtre a laissées visibles.
La cellule D16 affiche le même résultat puisque aucune ligne n’est masquée. Le rôle du code de fonction 109 est de respecter les filtres mais d’ignorer les lignes masquées. Ici nous n’avons aucune ligne masquée donc le résultat est identique à la cellule D14.
Filtre actif et ligne masquée
Ici, le filtre est toujours placé sur Espagne mais nous avons en plus masqué la ligne 6.
La cellule D12, continue d’afficher le total de toutes les lignes, y compris celles que le filtre a écartées et la ligne masquée. Le résultat ne tient compte ni du filtre ni de la ligne masquée.
La Cellule D14 n’affiche plus le bon résultat non plus car le code de fonction 9 tient compte du filtre mais pas des lignes masquées.
La cellule D16 est la seule qui affiche le bon résultat, grâce au code de fonction 109. Le rôle de ce code est d’indiquer à la fonction SOUS.TOTAL() de respecter à la fois le filtre ET les lignes masquées.
Syntaxe de la formule
Excel : SOUS.TOTAL(code_fonction; plage1; [plage2; …])
Google Sheets : SUBTOTAL(code_fonction; plage1; [plage2; …])
code_fonction : fonction à utiliser dans l’agrégation de sous-total.
plage1 : première plage à prendre en compte pour le calcul d’un sous-total.
plage2; … : plages supplémentaires à prendre en compte pour le calcul de sous-totaux.
Les codes de fonction
Voici les codes à utiliser pour tenir compte des filtres actifs pour chaque fonction. Pour ignorer aussi les valeurs cachées, ajoutez 10 au début des codes à un seul chiffre ou 1 au début des codes à deux chiffres, par exemple :
- 102 pour NB
- 110 pour VAR
- 1 est MOYENNE
- 2 est NB
- 3 est NBVAL
- 4 est MAX
- 5 est MIN
- 6 est PRODUIT
- 7 est ECARTYPE
- 8 est ECARTYPEP
- 9 est SOMME
- 10 est VAR
- 11 est VAR.P
Lien vers le fichier Google Sheets ➡️
CréditsLes captures animées sont réalisées avec le logiciel gratuit LICEcap que vous pouvez télécharger ici : https://www.cockos.com/licecap/
Tous les articles par catégorie
Envie de plus de lecture ? Tous les articles sont listés sur cette page par catégorie.