Si l'on applique des filtres (critères) à une base de données, les lignes ne correspondant pas aux critères sont masquées (voir les "trous" présents dans la numérotation des lignes à ce moment-là). Si l'on créé des formules portant sur une plage dans la base de données, les lignes masqués dans la sélection sont prises en compte par ces formules et renvoyant ainsi un résultat ne correspondant pas aux résultats attendus. Je vous propose de voir plusieurs solutions pour ignorer les lignes masquées de la base de données.
Base de données de départ pour l'exemple

Nous allons appliquer un filtre portant sur les villes et ne garder que DIJON, masquant les lignes 3, 6 et 7.
Résultats dans la barre d'état

Ouf ! Nous avons les bons résultats.
Remarques :
- L'icône Filtre en C1 (1) et l'information "Mode Filtre" dans la barre d'état.
- Les n° de ligne en couleur Cyan et l'absence des lignes 3, 6 et 7 correspondantes aux lignes pour la ville TALANT.
Utilisation d'une fonction standard comme la fonction SOMME

Aïe ! La présence des lignes masquées a provoqué :
- Un résultat faux : Le résultat en E9 (1) est faux car la formule prend en compte la ligne 3 (1+3+5 = 9 et non 10 comme affiché).
- Une erreur dans la définition de la formule elle-même : Les lignes masquées 6 et 7 (4) n'ont pas étés prises en compte quand l'utilisateur a fait le cliquer-glisser pour sélectionner la plage à utiliser par la fonction SOMME (de même si la ligne E2 avait été masquée la sélection aurait été E3:E5).
Remarques :
- Excel alerte l'utilisateur sur cette erreur (5).
- L'icône "Cellule visible" (Alt + ; ) n'est pas utilisable ici.
Utiliser la fonction SOUS.TOTAL

Remarques :
- Si le tableau est en forme de tableau structuré, l'option "Ligne total" permet d'accéder facilement à la fonction SOUS.TOTAL.
- Dans un tableau Excel "classique", si un filtre est appliqué au tableau, en utilisant l'icône ∑, Excel substitue la classique fonction SOMME par la fonction SOUS.TOTAL.
- Cette fonction permet d'ignorer les lignes masquées pour une petite liste de fonctions classiques chacune désignées par un code :
- SOMME (109), MOYENNE (101), MAX (104), MIN (105), ECARTYPE (107), VAR (110),
- NBVAL (103) ici c'est Nombre et NB (102) ici c'est Chiffres 🤪,
- Dans l'interface des tableaux structurés :
- Le choix "Autres fonctions" n'a aucun sens ! Cela ouvre l'assistant fonction pour insérer n'importe quelle fonction d'Excel sans lien avec la fonction SOUS.TOTAL.
- Il manque aussi les fonctions PRODUIT (106), ECARTYPEP (108), VAR.P (111) qui sont pourtant utilisables.
- Il existe aussi une série de code de 1 à 11 correspondants aux mêmes fonctions que les codes 101 à 111 et semblant donner le même résultat dans l'exemple ! 🤔.
- L'aide indique que les code 1 à 11 prennent en compte les lignes masquées manuellement. Il faut comprendre masquer par la commande Masquer ou par la commande Grouper et non par la commande Filtre (ou par un Segment) !
- Cas particulier, si un l'outil Filtre est activé quelque part dans la feuille alors il n'y a pas de différence entre ces codes. Simple 😰 !
- A l'origine il n'y avait que les code de 1 à 11. C'était utilisé par l'outil Sous Total (ruban Données) qui permet de générer une interface de masquage de lignes/colonnes et des lignes/colonnes de synthèses. Dans ces lignes/colonnes (de synthèses), on va trouver les fonctions SOUS.TOTAL avec les codes 1 à 11 permettant d'avoir les valeurs calculées même si les lignes de données "subordonnées/correspondantes" sont masquées par l'utilisateur.
Utiliser la fonction AGREGAT
=AGREGAT(12; 5; E2:E7)
Remarques :
- Le 1er argument est le code de la fonction à utiliser, le 2ème pour indiquer ce que la fonction doit ignorer (5 > les lignes filtrés).
- On retrouve les codes de fonctions de la fonction SOUS.TOTAL de 1 à 11 (MOYENNE, NB, NBVAL, MAX, MIN, PRODUIT, ECARTYPE.STANDARD, ECARTYPE.PEARSON, SOMME, VAR.S, VAR.P.N).
- On a en plus les codes pour MEDIANE (12), MODE.SIMPLE (13), GRANDE.VALEUR (14), PETITE.VALEUR (15), CENTILE.INCLURE (16), QUARTILE.INCLURE (17), CENTILE.EXCLURE(18), QUARTILE.EXCLURE(19).
Utiliser d'autres fonctions
Principe
On va utiliser, là aussi, une fonction SOUS.TOTAL ou AGREGAT mais de façon détournée. On va utiliser le fait qu'elles ignorent les lignes masquées par le filtre pour déterminer si les lignes sont masquées ou non masquées.
Mise en œuvre
On ajoute une colonne avec la formule suivante (en colonne F "Ligne_Aff", dans les exemple suivants).
=SOUS.TOTAL(3; [@Civilite])
ou
=SOUS.TOTAL(3; A3)
Remarques :
- J'ai pris le calcul NBVAL (3) me parait plus logique pour moi mais les autres sont utilisable.
- L'astuce c'est que la fonction porte sur une seule cellule (compatible avec la fonction sélectionnée) sur la même ligne.
- Si la ligne est affichée, la cellule vaut 1 (avec NBVAL) sinon elle vaut 0 (impossible à voir à l'écran).
Exemple 1 : Recherche (RECHERCHEX) en ignorant les lignes filtrés
Je veux trouver la dernière personne du tableau ayant seulement un enfant sur les valeurs renvoyées par un filtre appliqué au tableau.

=RECHERCHEX( 1;
SI(F2:F7=1; E2:E7; "");
B2:B7;
;
0;
-1)
Interprétation de la formule :
- 1 => Valeur cherchée.
- SI(F2:F7=1 ; E2:E7 ; "") : On modifie la plage de recherche en remplaçant le nombre d'enfants des cellules filtrés par une chaîne vide ("").
- SI(F2:F7=1 ; E2:E7 ; "") => "Si la ligne est affichée, on prend le nombre d'enfant sinon on prend une chaîne vide".
- Peut-être simplifié ainsi : SI(F2:F7 ; E2:E7 ; "").
- B2:B7 => Plage des Noms.
- => Pas de message si non trouvé.
- 0 => On cherche une correspondance exacte.
- -1 => On cherche à partir de la fin.
Exemple 2 : Calcul de la moyenne géométrique (MOYENNE.GEOMETRIQUE)
Je veux calculer la Moyenne géométrique du nombre d'enfants sur les valeurs renvoyées par un filtre appliqué au tableau (pourquoi une moyenne géométrique, mystère 🤔).
=MOYENNE.GEOMETRIQUE(SI(F2:F7;E2:E7;""))
Variantes :
=MOYENNE.GEOMETRIQUE(FILTRE(E2:E7;F2:F7))
Solution sans la colonne F (en utilisassent la définition mathématique (voir Moyenne géométrique — Wikipédia) :
=SOUS.TOTAL(6;E2:E7)^(1/SOUS.TOTAL(3;E2:E7))
Remarque :
- Si une valeur est à 0, on ne peut calculer la moyenne géométrique.
Merci pour votre attention bienveillante.