Compter les lignes visibles – Excel

Dans ce tutoriel vous allez découvrir comment compter les lignes visibles dans Microsoft Excel.
 
 

 

Formule générique
=SOMMEPROD((range=critères)*(SOUS.TOTAL(103,DECALER(range,lignes,0,1))))

 

Explication

Pour compter les lignes visibles uniquement avec des critères, vous pouvez utiliser une formule plutôt complexe basée sur SOMMEPROD, SOUS.TOTAL et DECALER. Dans l’exemple ci-dessus, la formule dans la cellule active est la suivante:

=SOMMEPROD((C6:C11=C15)*(SOUS.TOTAL(103;DECALER(C6;LIGNE(C6:C11)-MIN(LIGNE(C6:C11));0))))

La fonction SOUS.TOTAL peut facilement générer des sommes et des comptes pour les lignes cachées et non cachées. Pourtant, il ne peut pas gérer des critères tels que NB.SI ou SOMME.SI. Une solution consiste à utiliser SOMMEPROD pour appliquer à la fois la fonction SOUS.TOTAL et les critères. Les détails de cette approche sont décrits ci-dessous.
 
 

Comment fonctionne cette formule

À la base, cette formule fonctionne en configurant deux tableaux dans SOMMEPROD. Le premier tableau applique des critères et le second gère la visibilité:

=SOMMEPROD(critères * visibilité)

Le critère est appliqué avec une partie de la formule:

C6:C11=C15

Ce qui génère un tableau comme celui-ci:

{FALSE;TRUE;FALSE;TRUE}

Où TRUE signifie « répond aux critères ». Notez que comme nous utilisons la multiplication (*) sur ce tableau, les valeurs TRUE et FALSE seront automatiquement converties en 1 et en 0 par l’opération mathématique. Nous arrivons donc à:

{0;1;0;1}

Le filtre de visibilité est appliqué à l’aide de la fonction SOUS.TOTAL.

La fonction SOUS.TOTAL est capable d’exclure des lignes cachées lors de l’exécution de calculs. Nous pouvons donc l’utiliser dans ce cas pour générer un « filtre » afin d’exclure des lignes cachées à l’intérieur de la fonction SOMMEPROD. Le problème est que la fonction SOUS.TOTAL renvoie un nombre unique, alors que nous avons besoin d’un tableau de résultats pour pouvoir l’utiliser avec succès dans SOMMEPROD. L’astuce consiste à utiliser DECALER pour fournir à la fonction SOUS.TOTAL une référence par ligne, de sorte que la fonction DECALER renvoie un résultat par ligne.

Bien sûr, cela nécessite une autre astuce, qui consiste à donner à la fonction DECALER un tableau contenant un nombre par ligne, en commençant par zéro. Nous faisons cela avec une expression construite sur la fonction LIGNE:

=LIGNE(C6:C11)-MIN(LIGNE(C6:C11))

Qui générera un tableau comme celui-ci:

{0;1;2;3}

Et enfin, nous avons:

=SOMMEPROD({0,1,0,1}*{1;0;1;1})

Ce qui retourne 1.
 
 
QCM Excel

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *