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

Dugenou

XLDnaute Barbatruc

• 12 Avril 2018

• #2

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

 

Vote positif 0 Vote négatif

Jocelyn

XLDnaute Barbatruc

• 12 Avril 2018

• #3

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

 

Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc

• 12 Avril 2018

• #4

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: 12 Avril 2018

Vote positif 0 Vote négatif

L

lola18

XLDnaute Junior

• 12 Avril 2018

• #5

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

 

Vote positif 0 Vote négatif

L

lola18

XLDnaute Junior

• 12 Avril 2018

• #6

Jocelyn à dit:

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

Cliquez pour agrandir...

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

 

Vote positif 0 Vote négatif

N

Nairolf

XLDnaute Accro

• 12 Avril 2018

• #7

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.

 

Vote positif 0 Vote négatif

L

lola18

XLDnaute Junior

• 12 Avril 2018

• #8

Merci

@Nairolf
@Dugenou

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

Merci beaucoup

 

Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc

• 12 Avril 2018

• #9

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

 

Vote positif 0 Vote négatif

N

Nairolf

XLDnaute Accro

• 12 Avril 2018

• #10

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"

 

Vote positif 0 Vote négatif

L

lola18

XLDnaute Junior

• 12 Avril 2018

• #11

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

Merci par avance de ton retour

 

Vote positif 0 Vote négatif

N

Nairolf

XLDnaute Accro

• 12 Avril 2018

• #12

@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):

Code:

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

 

Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc

• 12 Avril 2018

• #13

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: 12 Avril 2018

Vote positif 0 Vote négatif

N

Nairolf

XLDnaute Accro

• 12 Avril 2018

• #14

@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")

 

Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc

• 12 Avril 2018

• #15

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

 

Vote positif 0 Vote négatif