NB.SI et fitre automatique

Trance4life · 24 Juin 2012

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 · 24 Juin 2012

Re : NB.SI et fitre automatique

Bonjour à tous,

Avec sommeprod

JHA

Trance4life · 24 Juin 2012

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 · 24 Juin 2012

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 · 24 Juin 2012

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 · 25 Juin 2012

Re : NB.SI et fitre automatique

Amilo à dit:
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 · 25 Juin 2012

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 · 25 Juin 2012

Re : NB.SI et fitre automatique

Re,

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

Code:

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

JHA

Trance4life · 25 Juin 2012

Re : NB.SI et fitre automatique

JHA à dit:
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 · 25 Juin 2012

Re : NB.SI et fitre automatique

JHA à dit:
Re,

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

Code:

=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 · 25 Juin 2012

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 · 25 Juin 2012

Re : NB.SI et fitre automatique

JHA à dit:
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.

NB.SI et fitre automatique

Trance4life

XLDnaute Nouveau

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

Trance4life

XLDnaute Nouveau

Trance4life

XLDnaute Nouveau

Amilo

XLDnaute Accro

Trance4life

XLDnaute Nouveau

JHA

XLDnaute Barbatruc

JHA

XLDnaute Barbatruc

Pièces jointes

Trance4life

XLDnaute Nouveau

Trance4life

XLDnaute Nouveau

JHA

XLDnaute Barbatruc

Trance4life

XLDnaute Nouveau

Discussions similaires