NB.SI et fitre automatique

[Trance4life]

Bonjour, voici mon fichier Excel ci-joint.

J'utilise un NB.SI voir en R5 par exemple en haut de mon calendrier pour calculer le nombre de lettres C, N et P dans chaque colonne.

Mais mon souci est que quand j'utilise le filtre automatique, ça me calcule tout au lieu de prendre en compte que les lignes filtrées.

J'ai cherché un peu sur le net mais je n'ai pas compris les explications.

Merci de votre aide.

 

[JHA]

Re : NB.SI et fitre automatique

Bonjour à tous,

Avec sommeprod

JHA

 

[Trance4life]

Re : NB.SI et fitre automatique

Merci beaucoup !

Mais vous pouvez m'expliquer la formule SVP ?

=SOMMEPROD((AI$8:AI$207="A")*SOUS.TOTAL(3;DECALER(AI$7;LIGNE($1:$200)))

Surtout le "SOUS.TOTAL(3;DECALER(AI$7;LIGNE($1:$200))" ...

Cordialement.

 

[Trance4life]

Re : NB.SI et fitre automatique

DECALER( AI$7;LIGNE($1:$200)))

Pourquoi on va de la ligne 1 à 200 ? J'ai pas trop compris ce truc.

 

[Amilo]

Re : NB.SI et fitre automatique

Bonsoir Trance4life,

Sauf erreur de ma part, cette fonction Decaler signifie que la zone des données commence à la cellule AI$7 ET pour une hauteur de 200 lignes avec la fonction ligne()....

Cordialement

 

[Trance4life]

Re : NB.SI et fitre automatique

Bonsoir Trance4life,

Sauf erreur de ma part, cette fonction Decaler signifie que la zone des données commence à la cellule AI$7 ET pour une hauteur de 200 lignes avec la fonction ligne()....

Cordialement

Ce qui est bizarre c'est quand j'essaie de modifier le $200 ça marche plus et ça me met #N/A

 

[JHA]

Re : NB.SI et fitre automatique

Bonjour à tous,

Le problème avec les fonctions de sommation, c'est quelles prennent la plage entière. En utilisant la fonction DECALER, pour la plage de la fonction SOUS.TOTAL, on va décomposer la plage ligne par ligne.
Obligation :
- Q$7 correspond à la cellule titre d'entête du tableau.
- LIGNE($1:$200) doit commencer à la ligne 1 de la feuille et comporter autant de lignes que la plage

AI$8:AI$207

JHA​

 

[JHA]

Re : NB.SI et fitre automatique

Re,

On peut en nommant une plage, simplifier la formule exemple en "Q5"

=SOMMEPROD((DECALER(mission;;5+COLONNES($A:A))="C")*SOUS.TOTAL(3;DECALER(Q$7;LIGNE(mission)-7;)))

JHA

 

[Trance4life]

Re : NB.SI et fitre automatique

Bonjour à tous,

Le problème avec les fonctions de sommation, c'est quelles prennent la plage entière. En utilisant la fonction DECALER, pour la plage de la fonction SOUS.TOTAL, on va décomposer la plage ligne par ligne.
Obligation :
- Q$7 correspond à la cellule titre d'entête du tableau.
- LIGNE($1:$200) doit commencer à la ligne 1 de la feuille et comporter autant de lignes que la plage

AI$8:AI$207

JHA​

Ah d'accord je vois

 

[Trance4life]

Re : NB.SI et fitre automatique

Re,

On peut en nommant une plage, simplifier la formule exemple en "Q5"

=SOMMEPROD((DECALER(mission;;5+COLONNES($A:A))="C")*SOUS.TOTAL(3;DECALER(Q$7;LIGNE(mission)-7;)))

JHA

Euh là je suis un peu perdu.
mission ça correspond à quoi?
$A:A ?
Le -7 ?

Merci d'avance.

 

[JHA]

Re : NB.SI et fitre automatique

Re,

Mission correspond à une plage nommée voir dans gestionnaire des noms

=DECALER('Plan de charge IDF 2012'!$K$7;;;NBVAL('Plan de charge IDF 2012'!$K:$K))

Colonnes($A:A) te renvoie 1 quand tu tires la formules sur la droite tu auras automatiquement colonnes($A:B) qui te donne 2 etc..
la plage mission débute en $K$7 le "-7" derrière ligne(mission) te donne la ligne 1 au lieu de 7

JHA

 

[Trance4life]

Re : NB.SI et fitre automatique

Re,

Mission correspond à une plage nommée voir dans gestionnaire des noms

Colonnes($A:A) te renvoie 1 quand tu tires la formules sur la droite tu auras automatiquement colonnes($A:B) qui te donne 2 etc..
la plage mission débute en $K$7 le "-7" derrière ligne(mission) te donne la ligne 1 au lieu de 7

JHA

Merci mais je préfère la première formule

=SOMMEPROD((AI$8:AI$207="A")*SOUS.TOTAL(3;DECALER( AI$7;LIGNE($1:$200)))

La deuxième est bien plus compliquée je trouve.