Discussion :

[facteur]

Bonjour a tous,

J'ai un fichier transport dont la colonne B contient des valeurs qui peuvent etre soit du texte ou des chiffres (nat; morin;ldlc;1;26;38....) et dont la colonne H contient a la fois des heures et la valeur "nc" .
je souhaiterais creer dans la cellule B4 la formule sous.total qui me donnerait le nombre de ligne de la valeur choisie, au prealable par le filtre, et qui serait aussi different de la valeur "nc" contenu dans la colonne H.
Exemple:
Si, dans la colonne B, je choisie, par le biais du filtre, la valeur " nat";
Alors, dans la cellule B4 s'affiche le total de ligne contenant le nom "nat" et different de "nc".
Et meme chose pour les autres valeurs telles que : 1;26;38...

Je maitrise la formule sous.toal mais uniquement pour des calules de chiffres.
cette formule de sous.toal sera ensuite integrer dans ma macro afin de la completer un peu plus...

PS: je vous joins le fichier que j'ai simplifier en masquant les colonnes inutiles pour ce probleme.

[Gorfael]

Salut facteur et le forum

cette formule de sous.total sera ensuite integrée dans ma macro afin de la completer un peu plus....

Un peu compliqué comme problème. Tu peu créer une colonne supplémentaire qui ne donne que les valeurs de H dont B correspond au critère (ex pour la ligne 6) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(H6="nc";"";(B6="nat")*H6)

(on peut remplacer le critère par la référence à une cellule) et on fait le sous-total de cette colonne.
Mais si tu comptes utiliser une macro, pourquoi ne pas s'en servir (si ligne visible et cellule B contient "nat" alors comptabiliser) ?
A+

[facteur]

Salut Gorfael,

Merci pour ton intervention.
Si en utilisant une macro, le probleme devient moins complique ,alors j'accepte la proposition...
Mais que veux tu dire lorsque tu ecris :"si ligne visible et cellule B contient "nat" alors comptabiliser".
Pourrais tu ecrire cela en language VBA.

Salut facteur et le forumUn peu compliqué comme problème. Tu peu créer une colonne supplémentaire qui ne donne que les valeurs de H dont B correspond au critère (ex pour la ligne 6) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(H6="nc";"";(B6="nat")*H6)

(on peut remplacer le critère par la référence à une cellule) et on fait le sous-total de cette colonne.
Mais si tu comptes utiliser une macro, pourquoi ne pas s'en servir (si ligne visible et cellule B contient "nat" alors comptabiliser) ?
A+

[ouisansdoute]

Bonjour,

Je réagis peut-être un peu tard mais la fonction sous.total peut avoir différents chiffres comme premier argument.
par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOUS.TOTAL(3;J6:J171)

Donne le nombre de valeurs, sans qu'il soit besoin de rajouter une colonne.
autres possibilités :
1 MOYENNE
2 NB
3 NBVAL
4 MAX
5 MIN
6 PRODUIT
7 ECARTYPE
8 ECARTYPEP
9 SOMME
10 VAR
11 VAR.P

sur la colonne H, il faut également positionner le filtre personnalisé à "Est différent de" nc

[facteur]

Bonjour ouisansdoute

Effectivement, la formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOUS.TOTAL(3;J6:J171)

fonctionne a merveille.
Merci.
Si je devais compliquer l'affaire en voulant que ce sous total exclu les lignes qui contiennent la valeur "nc" contenues dans la colonne H.
Est ce realisable ?

Je te joins le fichier ou j'ai masque les colonnes surpflues pour cette excercice.

Bonjour,

Je réagis peut-être un peu tard mais la fonction sous.total peut avoir différents chiffres comme premier argument.
par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOUS.TOTAL(3;J6:J171)

Donne le nombre de valeurs, sans qu'il soit besoin de rajouter une colonne.
autres possibilités :
1 MOYENNE
2 NB
3 NBVAL
4 MAX
5 MIN
6 PRODUIT
7 ECARTYPE
8 ECARTYPEP
9 SOMME
10 VAR
11 VAR.P

[ouisansdoute]

Boànjour,

Tu as des filtres sur B, G et H. En H, tu mets le filtre personnalisé et tu choisis de ne pas afficher les valeurs nc comme je l'indiquais dans mon précédent post.

sur la colonne H, il faut également positionner le filtre personnalisé à "Est différent de" nc

Si cela ne convient pas, reviens vers nous.

[facteur]

Rebonjour ouisansdoute

Ce que je souhaite c'est que dans cette formule sous.total , le critere :different de "nc" soit integre.
En resume, si, dans sa colonne B, je clique sur le critere "nat', j'ai le nombre de ligne egal a "nat" et different de "nc".
Tu vas me dire que mettre un filtre personnalise dans la colonne H prend quelque seconde. Mais, si je peux m'epargner cette tache, se serait sympas.
Mais encore une fois, cette formule sous.total avec l'argument 3 est type top...

Boànjour,

Tu as des filtres sur B, G et H. En H, tu mets le filtre personnalisé et tu choisis de ne pas afficher les valeurs nc comme je l'indiquais dans mon précédent post.

Si cela ne convient pas, reviens vers nous.

[ouisansdoute]

Pour combiner les deux critères, je pense que la formule SOMMEPROD est la plus adaptée.
Ainsi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((B6:B200="nat")*(H6:H200<>"nc"))

donne le nombre de lignes répondant aux deux critères sans avoir besoin des filtres automatiques.
Tu peux isoler les critères recherchés dans des cellules avec listes déroulantes. Par exemple en B3 et H3
du coup, ta formule peut être adaptée de la façon suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((B6:B200=B3)*(H6:H200<>H3))

en changeant la valeur recherchée en B3 et en H3, le calcul se met à jour tout de suite.

[facteur]

salut ouisansdoute

Je connaissais cette formule "somme prod"...
Je vais faire comme tu l'a dis ci dessous : je vais mettre le filtre personnalise sur <>"nc" ..C'est plus simple...
Merci encore...

Pour combiner les deux critères, je pense que la formule SOMMEPROD est la plus adaptée.
Ainsi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((B6:B200="nat")*(H6:H200<>"nc"))

donne le nombre de lignes répondant aux deux critères sans avoir besoin des filtres automatiques.
Tu peux isoler les critères recherchés dans des cellules avec listes déroulantes. Par exemple en B3 et H3
du coup, ta formule peut être adaptée de la façon suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((B6:B200=B3)*(H6:H200<>H3))

en changeant la valeur recherchée en B3 et en H3, le calcul se met à jour tout de suite.