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

lola18

XLDnaute Junior
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
 

Pièces jointes

  • Test_doublon.xlsx
    55.6 KB · Affichages: 38

Dugenou

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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

Code:
=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

Code:
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

XLDnaute Barbatruc
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.
 

Pièces jointes

  • lola18.xlsx
    93.5 KB · Affichages: 25
Dernière édition:

lola18

XLDnaute Junior
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

XLDnaute Junior
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

Code:
=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

Code:
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

XLDnaute Accro
Salut tout le monde,

@lola18, essaye avec ces formules:

Avec doublons :
Code:
=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 :
Code:
=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.
 

Dugenou

XLDnaute Barbatruc
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

XLDnaute Accro
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"
 

Nairolf

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

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):
Code:
=SI(NB.SI($B$2:$B$2053;B1844)>1;"Doublon";"Pas doublon")
 

Dugenou

XLDnaute Barbatruc
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
 
Dernière édition:

Nairolf

XLDnaute Accro
@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 :
Code:
=SI(NB.SI.ENS($B$2:$B$2053;B2;$A$2:$A$2053;A2)>1;"Doublon";"Pas doublon")
 

Dugenou

XLDnaute Barbatruc
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
 

Discussions similaires

Réponses
22
Affichages
690
Réponses
12
Affichages
225
Réponses
1
Affichages
219

Statistiques des forums

Discussions
311 725
Messages
2 081 947
Membres
101 849
dernier inscrit
florentMIG