XL 2010 Sommeprod+ Décaler / NB.SI + Décaler

[lola18]

Bonjour,

Malgré une recherche poussée sur le forum....je cale.

Mon problème :

En colonne A une liste de variables, seules 2 sont à prendre en compte dans mon calcul
En colonne B des titres avec des cellules vides, du contenu unique et du contenu en double
En colonne C une formule pour identifier les doublons ou non doublons de ma colonne B

Je cherche à obtenir le nb de doublons qui correspondent à certaines lignes de ma colonne A et inversement en ôtant les cellules vides.

Et j'oubliais si possible en utilisant une formule décaler parce que mon nombre de ligne n'est pas fixe

Mes formules :

=NB.SI(A2:A2053;"text/html; charset=utf-8")+NB.SI(A2:A2053;"application/pdf")+NB.SI(C2:C2053;"doublon")-NB.VIDE(B2:B2053)
==> Faux

=NB.SI(DECALER($A$1;;;1);"text/html; charset=utf-8")+NB.SI(DECALER($A$1;;;1);"application/pdf")+NB.SI(DECALER($A$1;;;1);"doublon")-NB.VIDE(DECALER($B$1;;;1))

==> 0

=SOMMEPROD(N(G2=$C$2:$C$2053))-NB.VIDE(B1:B2053)

==> chiffre ok mais pas de relation avec ma colonne A

Merci par avance de votre aide
En pj mon fichier

 

[Dugenou]

Bonjour,
Dans ta formuel "dynamique" : il est nécessaire d'indiquer la hauteur de ta zone décalée :
la formule =NB.SI(DECALER($A$2;;;NBVAL($A$2:$A$10000));"text/html; charset=utf-8")+NB.SI(DECALER($A$2;;;NBVAL($A$2:$A$10000));"application/pdf")+NB.SI(DECALER($A$2;;;NBVAL($A$2:$A$10000));"doublon")-NB.VIDE(DECALER($B$2;;;NBVAL($A$2:$A$10000))) donne un résultat de 1 201 : est-ce correct ?
Cordialement

 

[Jocelyn]

Bonjour le Forum,
Bonjour lola18,

suivant ma compréhension pour calculer le

Nb Doublons sans ligne vide de la colonne B + uniquement les valeurs de "text/html; charset=utf-8" et "application/pdf" de la colonne A

=SOMMEPROD((G2=$C$2:$C$2053)*((A2:A2053="text/html; charset=utf-8")+(A2:A2053="application/pdf"))*(B2:B2053<>""))

et pour

Nb Sans doublons sans ligne vide de la colonne B + uniquement les valeurs de "text/html; charset=utf-8" et "application/pdf" de la colonne A

SOMMEPROD((G3=$C$2:$C$2053)*((A2:A2053="text/html; charset=utf-8")+(A2:A2053="application/pdf"))*(B2:B2053<>""))

après ce n'est que ce que j'ai compris

Cordialement

Oup's Bonjour Dugenou

 

[Dugenou]

Un autre essai avec une autre façon de déduire les doublons et des zones dynamiques nommées pour simplier la lecture de la formule.
Résultat de 1 243.

Cordialement
Edit : hello Jocelyn. pas certain que la formule doublons d'origine : si nb.si .... > 1 soit convenable car met toutes les valeurs en doublon qu'il y ait 2 ou 8 valeurs identiques : donc ensuite compliqué de les déduire en comptant 1 à chaque fois
j'ai donc préféré afficher des 1 si première apparition du couple content/titre et 0 ensuite.
ensuite y'a plus qu'a faire des somme.si
on pourait simplifier encore en affichant 1 dans cette colonne si pas doublon et si correspond aux critères, y'aura plus qu'à faire le total de la colonne.

 

[lola18]

Bonjour,

Merci pour la correction sur la formule, malheureusement le résultat ne correspond pas à ce que j'obtiens en filtrant sur :

colonne A : text/html; charset=utf-8 - application/pdf
colonne B : Non vide
colonne C : Doublon

=> 54

Mais mon explication de départ n'était pas très claire.

Merci

 

[lola18]

Bonjour le Forum,
Bonjour lola18,

suivant ma compréhension pour calculer le

Nb Doublons sans ligne vide de la colonne B + uniquement les valeurs de "text/html; charset=utf-8" et "application/pdf" de la colonne A

=SOMMEPROD((G2=$C$2:$C$2053)*((A2:A2053="text/html; charset=utf-8")+(A2:A2053="application/pdf"))*(B2:B2053<>""))

et pour

Nb Sans doublons sans ligne vide de la colonne B + uniquement les valeurs de "text/html; charset=utf-8" et "application/pdf" de la colonne A

SOMMEPROD((G3=$C$2:$C$2053)*((A2:A2053="text/html; charset=utf-8")+(A2:A2053="application/pdf"))*(B2:B2053<>""))

après ce n'est que ce que j'ai compris

Cordialement

Oup's Bonjour Dugenou

Bonjour Jocelyn,

Merci ça me donne effectivement le bon résultat. Pour intégrer une formule DECALER dans la somme prod je remplace les plages par

DECALER($A$2;;;NBVAL($A$2:$A$10000)) à la place de A2:A2053
DECALER($A$2;;;NBVAL($B$2:$B$10000)) à la place de B2:B2053

comme la correction de dugenou

c'est bien ça

Merci encore

 

[Nairolf]

Salut tout le monde,

@lola18, essaye avec ces formules:

Avec doublons :

=NB.SI.ENS(DECALER($A$2;;;NBVAL(A:A)-1;);"text/html; charset=utf-8";DECALER($C$2;;;NBVAL(A:A)-1;);"doublon";DECALER($B$2;;;NBVAL(A:A)-1;);"><")+NB.SI.ENS(DECALER($A$2;;;NBVAL(A:A)-1;);"application/pdf";DECALER($C$2;;;NBVAL(A:A)-1;);"doublon";DECALER($B$2;;;NBVAL(A:A)-1;);"><")

Sans doublons :

=NB.SI.ENS(DECALER($A$2;;;NBVAL(A:A)-1;);"text/html; charset=utf-8";DECALER($C$2;;;NBVAL(A:A)-1;);"pas doublon";DECALER($B$2;;;NBVAL(A:A)-1;);"><")+NB.SI.ENS(DECALER($A$2;;;NBVAL(A:A)-1;);"application/pdf";DECALER($C$2;;;NBVAL(A:A)-1;);"pas doublon";DECALER($B$2;;;NBVAL(A:A)-1;);"><")

@Dugenou, j'obtiens 1 de moins que toi, et ça colle avec le nombre de valeurs retournées par filtres.

 

[lola18]

Merci

@Nairolf
@Dugenou

ça me donne plusieurs options avec la somme prod de @Jocelyn

Merci beaucoup

 

[Dugenou]

Re

A mon avis la formule doublon/ pas doublons de Lola : =SI(MAX(NB.SI($B$2:$B$2053;$B$2:$B$2053))>1;"Doublon";"Pas doublon") donne doublon dès la première apparition d'un titre affiché plusieur fois : donc il ne sera jamais compté.
Et je ne vois pas comment lola obtient 54 avec un filtre
Cordialement

 

[Nairolf]

J'obtiens la même info que Lola (54 enregistrement(s) trouvé(s) sur 2052) quand je mets les filtres suivants :
- Dans la colonne "Content" : coche uniquement "text/html; charset=utf-8" et "application/pdf"
- Dans la colonne "Title 1" : décoche de "(Vides)"
- Dans la colonne "duplication title" : coche uniquement "Doublon"

 

[lola18]

@Dugenou tu veux dire que ma formule doublon est erronée??

Merci par avance de ton retour

 

[Nairolf]

@Dugenou , il faut que j'apprenne à lire un message en entier et que je réponde aussi en rapport...

C'est que la formule de Lola (que je n'avais pas regardée) est un peu "bizarre", j'aurais fait plus simple (à étirer ensuite vers le bas):

=SI(NB.SI($B$2:$B$2053;B1844)>1;"Doublon";"Pas doublon")

 

[Dugenou]

Lola :
ça dépends de ce que tu veux faire : ta formule affiche doublon sur toutes les valeurs multiples des titres :
1) sans tenir compte de la colonne content : donc si tu as AA1 pour text html et AA1 pour application pdf : il sera compté comme doublon et donc pas compté
2) si par exemple on remplace AA1 par AA2 en ligne 472 ça écrit doublon sur les deux lignes et donc le AA2 ne sera pas compté du tout.

Pour moi quand je veux enlever des doublons j'enlève pas la première apparition d'une valeur : mais en général je compte des personnes : peut-être que dans ton cas il faut enlever completement tout ce qui est en double

Edit : Nairolf : on est bien d'accord au 1) prés

 

[Nairolf]

@Dugenou , je comprends ce que tu veux dire, le cas se présente pour la ligne 2026 qui est un doublon lorsqu'on considère uniquement la colonne B et qui n'en est pas un si on prend en compte en plus la colonne A.

Dans ce cas là il faudrait plutôt faire :

=SI(NB.SI.ENS($B$2:$B$2053;B2;$A$2:$A$2053;A2)>1;"Doublon";"Pas doublon")

 

[Dugenou]

Nairolf
Avec cette formule tu continues d'afficher en doublon la première apparition d'un couple
pour que l'affichage doublon se fasse à partir de la seconde apparition : en ligne 2
=SI(NB.SI.ENS($B$2:$B2;B2;$A$2:$A2;A2)>1;"Doublon";"Pas doublon")
En allongeant la zone de données au fur et à mesure qu'on descend (pas de $ sur le second B2 et le second A2) on compte 1 sur la première apparition et 2, 3 , 4 ensuite

Cordialement