Imbriquer la formule Sous-Total dans la formule NB.DIFF

[JLM7856]

Bonjour,
Comment imbriquer la formule Sous-total (filtrage sur une colonne) dans une formule NB.DIFF ?
Dans le fichier ci-joint , après avoir filtré sur la colonne Services, je cherche à obtenir le chiffre 1 en colonne D et 3 en colonne E
Merci

 

[CB60]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Bonsoir
Moi je trouve 4 en colonne E

=SOMMEPROD((SOUS.TOTAL(3;DECALER(D4:D31;LIGNE(D4:D31)-MIN(LIGNE(D4:D31));;1)))*(1/NB.SI(D4:D31;D4:D31)))

adaptation d'une formule d'Alain Vallon

 

[JLM7856]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Bonsoir,
1er comment : votre formule fonctionne avec l'extract du fichier que je vous ai envoyé mais lorsque je veux la copier/coller sur le grand fichier sur 400 lignes cela ne fonctionne plus même après avoir remplacé 31 par 4OO
que dois je adapter ?

2em comment : la formule fonctionne en saisissant dans la formule le nbre exact de lignes concernées. Cela veut dire que si des lignes sont rajoutées, il faut modifier la régle à chaque changement. Exact ??
merci

 

[JCGL]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Bonjour à tous,

Une formule un peu longue mais le SOMMEPROD() requiert des plages identiques :

Ici pour la colonne B, il faut changer la colonne en minuscules pour les autres colonnes

=SOMMEPROD((SOUS.TOTAL(3;DECALER(INDIRECT("b3:b"&NBVAL(B3:B65000)+2);LIGNE(INDIRECT("b3:b"&NBVAL(B3:B65000)+2))-MIN(LIGNE(INDIRECT("b3:b"&NBVAL(B3:B65000)+2)));;1)))*(1/NB.SI(INDIRECT("b3:b"&NBVAL(B3:B65000)+2);INDIRECT("b3:b"&NBVAL(B3:B65000)+2))))

A+ à tous

 

[JLM7856]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Bonjour,
Votre formule n'est pas loin de la vérité. Après l'avoir "Copier/Coller" et adapté à la colonne qui est la F et à la 1ère ligne à compter qui est la 4, il me manque 1 info dans le compteur, cad 112 i/o 113. (la ligne 3 étant la ligne de titres du tableau.
Ne comprenant pas la lecture du contenu de la formule, peut-être ai-je changé qqch par erreur !!!
J'ai testé le rajout de lignes, cela fonctionne, donc il n'y a plus qu'à trouver le pourquoi,
Je vous remercie et compte sur vous car c'est important pour mon analyse professionnelle
Bonne journée

 

[Tibo]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Bonjour,

Peut-être en changeant le B3 en B2 (si tes données commencent en ligne 2)

Je te laisse regarder

@+

 

[JLM7856]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Bonjour,
La ligne 3 = ligne de titre, la ligne 4 = début des data
Si je mets F3 dans toute la formule j'arrive à 114 lignes prises en compte et si je mets F4 dans toute la formule j'arrive à 112. Or le bon résultat est 112.
Donc il y a bien un endroit de la formule où je dois mettre F3 et le reste en F4, mais où ???
merci pour votre aide

 

[BOISGONTIER]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Bonjour,

=SOMME(--(FREQUENCE(SI(SOUS.TOTAL(3;INDIRECT("D"&LIGNE(D4😀1000)));EQUIV(D4😀1000;D4😀1000;0));LIGNE(INDIRECT("1:"&LIGNES(D4😀1000))))>0))
Valider avec maj+ctrl+entrée


=SOMME(--(FREQUENCE(SI(SOUS.TOTAL(3;INDIRECT("E"&LIGNE(E4:E1000)));EQUIV(E4:E1000;E4:E1000;0));LIGNE(INDIRECT("1:"&LIGNES(E4:E1000))))>0))
Valider avec maj+ctrl+entrée



JB

 

[david84]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Bonjour à tous,
une autre possibilité avec Fréquence :

=SOMMEPROD(N(FREQUENCE(((SOUS.TOTAL(3;DECALER(D4;LIGNE(D4:D31)-LIGNE(D4);)))*(EQUIV(D4:D31;D4:D31;0)));LIGNE(D3:D31))>0))

à tirer vers la droite.
Concernant la délimitation du nombre de lignes, le plus simple est peut-être de les nommer, sinon utiliser indirect combinée à NBVAL comme proposé par JC ou combiné à max et ligne.
A+

 

[david84]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Re
autre possibilité afin de ne plus se soucier de ce problème de plage dynamique (cf. fichier) :
- onglet test1 :enregistrer ta plage de données sous forme de tableau (liste sous Xl 2003) et inclure ta formule dans la ligne de sous-totaux (j'ai modifié la formule en conséquence afin qu'elle accepte les lignes vides lors de l'insertion d'une nouvelle ligne dans le tableau (mais je ne sais pas si cette fonctionnalité existe sur 2003 donc à voir)...
- onglet test2 :si tu ne peux pas inclure ta formule dans la ligne des sous-totaux, définir ton tableau sans inclure de ligne de sous-totaux et placer ta formule dans la ligne qui suit.
A+

 

[JLM7856]

Re : Imbriquer la formule Sous-Total dans la formule NB.DIFF

Fin du Marathon...Rappel et synthèse pour ceux qui peuvent avoir ce type de besoin
1) Compter le nbre de cellules dont les identiques ne comptent qu' 1 fois en tenant compte de lignes rajoutées => =NB.DIFF(D4😀257)
2) Même opération que ci-dessus mais en gérant des filtres sur certaines colonnes =>
=SOMME(--(FREQUENCE(SI(SOUS.TOTAL(3;INDIRECT("E"&LIGNE(E4:E999)));EQUIV(E4:E999;E4:E999;0));LIGNE(INDIRECT("1:"&LIGNES(E4:E999))))>0))
Formule matricielle dont à Valider par CTRL+MAJ+ENTREE

Merci à tous pour votre aide