Formule Nb si ens ou autre idée ?

Le 16/04/2019 à 10:56

Bonjour

Je cherche à connaître le nombre de vendeurs qui répondent à certains critères sans compter les doublons.

=NB.SI.ENS(DataQ1!$A:$A;2020;DataQ1!$E:$E;"*Ingram*";DataQ1!$I:$I;"CSG";DataQ1!$D;$D;"AUSTRIA") mais sans les doublons comme certains vendeurs on plusieurs ventes donc plusieurs lignes.

J'ai mis un échantillon de mon tableau en pièce jointe.

Je bloque sur la façon d'y arriver.

Merci d'avance pour votre aide.

T

4formule-magique.xlsm (918.01 Ko)
Le 16/04/2019 à 12:00

Bonjour,

La formule vient du site de Boisgontier

https://www.cjoint.com/c/IDqkaBXfc0y

On peut le faire avec un TCD

Crdlmt

Le 16/04/2019 à 12:09

Merci, mais j'aimerais éviter le TCD car j'ai beaucoup de formules à décliner et de combinaisons à faire.

Le 16/04/2019 à 12:11

Tu l'as, la, la formule en F1 !

Crdlmt

Le 16/04/2019 à 12:14

Ah oui pardon je n'avais pas vu.

Merci je regarde ça C'est gentil de votre part.

Le 16/04/2019 à 12:43

Question : Pourquoi le résultat est il 1 alors que je cherche le nombre de vendeurs différents (colonne L étant leurs identifiants uniques) répondant à différents critères ?

Le 16/04/2019 à 12:54

J'ai modifié la formule en F1 mais le résultat est différent du TCD.

https://www.cjoint.com/c/IDqk1Opiuce

Le 16/04/2019 à 15:33

Bonjour,

Je te propose une fonction VBA que tu pourras appeler depuis une cellule quelconque de ta feuille en lui passant les paramètres voulus.

Exemple: NbvendeursUniques(2018,"INGRAM", "CSG","AUSTRIA")

J'ai mentionné "INGRAM" sans les caractères génériques. Toutefois la fonction recherche bien la chaine à l'intérieur de la cellule concernée via la fonction INSTR.

Par ailleurs, certaines lignes ne comportent pas de vendeurs. On trouve un tiret "-" à la place. La fonction considère que "-" est un vendeur et il est comptabilisé. Si tu souhaites l'exclure il faudra modifier le premier bloc IF (If TB(i,1) <>"-" and ....

Nous avons une boucle principale qui alimente un tableau et qui incrémente un compteur (+1). Dans la foulée une boucle interne parcoure les enregistrements du tableau pour vérifier si la ligne correspondant aux paramètres n'existe pas déjà. Si tel est le cas le compteur passe à -1. C'est la méthode que j'ai trouvé pour éviter de compter les doublons.

J'ai effectué un test avec les mêmes données importé dans Microsoft ACCESS. C'est plus facile avec une requête ACCESS.

A priori j'obtiens les mêmes résultats

Je te donne le code SQL si ca t'intéresse:

SELECT Feuil1.[Fiscal Year], Feuil1.[Partner Country], Feuil1.[Partner Name], Feuil1.[CSG/ISG], Feuil1.[(POS) Validated Reseller Affinity ID]
FROM Feuil1
GROUP BY Feuil1.[Fiscal Year], Feuil1.[Partner Country], Feuil1.[Partner Name], Feuil1.[CSG/ISG], Feuil1.[(POS) Validated Reseller Affinity ID]
HAVING (((Feuil1.[Fiscal Year])=2019) AND ((Feuil1.[Partner Country])="AUSTRIA") AND ((Feuil1.[Partner Name]) Like "*ingram*") AND ((Feuil1.[CSG/ISG])="CSG"))
ORDER BY Feuil1.[(POS) Validated Reseller Affinity ID];

Et donc voilà la fonction VBA que tu pourras modifier à loisirs selon tes besoins:

Function NbVendeursUniques(ColA As Variant, ColE As Variant, ColI As Variant, ColD As Variant) As Long
    Dim NbLignes As Long
    Dim Compteur As Long
    Dim I As Long
    Dim J As Long
    NbLignes = ActiveSheet.Cells(64536, 12).End(xlUp).Row
    ReDim TB(2 To NbLignes, 5) As Variant

    Range("L2").Select
    For I = 2 To NbLignes
        TB(I, 1) = Cells(I, 12)
        TB(I, 2) = Cells(I, 1)
        TB(I, 3) = Cells(I, 5)
        TB(I, 4) = Cells(I, 9)
        TB(I, 5) = Cells(I, 4)
        If TB(I, 2) = ColA And InStr(TB(I, 3), ColE) > 0 And TB(I, 4) = ColI And TB(I, 5) = ColD Then
            Compteur = Compteur + 1

            For J = 2 To I - 1
                If TB(I, 1) = TB(J, 1) And TB(I, 2) = TB(J, 2) And TB(I, 3) = TB(J, 3) And _
                    TB(I, 4) = TB(J, 4) And TB(I, 5) = TB(J, 5) Then
                    Compteur = Compteur - 1
                    Exit For
                End If
            Next J
        End If
    Next I

    NbVendeursUniques = Compteur

End Function

Bien cordialement

Le 16/04/2019 à 15:42

Merci, il va me falloir un peu de temps pour décortiquer tout ça. Je vais me plonger dedans.

C'est vrai que j'ai quand même tendance à privilegier les formules aux VBA.

T

GNIN a écrit :

Bonjour,

Je te propose une fonction VBA que tu pourras appeler depuis une cellule quelconque de ta feuille en lui passant les paramètres voulus.

Exemple: NbvendeursUniques(2018,"INGRAM", "CSG","AUSTRIA")

J'ai mentionné "INGRAM" sans les caractères génériques. Toutefois la fonction recherche bien la chaine à l'intérieur de la cellule concernée via la fonction INSTR.

Par ailleurs, certaines lignes ne comportent pas de vendeurs. On trouve un tiret "-" à la place. La fonction considère que "-" est un vendeur et il est comptabilisé. Si tu souhaites l'exclure il faudra modifier le premier bloc IF (If TB(i,1) <>"-" and ....

Nous avons une boucle principale qui alimente un tableau et qui incrémente un compteur (+1). Dans la foulée une boucle interne parcoure les enregistrements du tableau pour vérifier si la ligne correspondant aux paramètres n'existe pas déjà. Si tel est le cas le compteur passe à -1. C'est la méthode que j'ai trouvé pour éviter de compter les doublons.

J'ai effectué un test avec les mêmes données importé dans Microsoft ACCESS. C'est plus facile avec une requête ACCESS.

A priori j'obtiens les mêmes résultats

Je te donne le code SQL si ca t'intéresse:

SELECT Feuil1.[Fiscal Year], Feuil1.[Partner Country], Feuil1.[Partner Name], Feuil1.[CSG/ISG], Feuil1.[(POS) Validated Reseller Affinity ID]
FROM Feuil1
GROUP BY Feuil1.[Fiscal Year], Feuil1.[Partner Country], Feuil1.[Partner Name], Feuil1.[CSG/ISG], Feuil1.[(POS) Validated Reseller Affinity ID]
HAVING (((Feuil1.[Fiscal Year])=2019) AND ((Feuil1.[Partner Country])="AUSTRIA") AND ((Feuil1.[Partner Name]) Like "*ingram*") AND ((Feuil1.[CSG/ISG])="CSG"))
ORDER BY Feuil1.[(POS) Validated Reseller Affinity ID];

Et donc voilà la fonction VBA que tu pourras modifier à loisirs selon tes besoins:

Function NbVendeursUniques(ColA As Variant, ColE As Variant, ColI As Variant, ColD As Variant) As Long
    Dim NbLignes As Long
    Dim Compteur As Long
    Dim I As Long
    Dim J As Long
    NbLignes = ActiveSheet.Cells(64536, 12).End(xlUp).Row
    ReDim TB(2 To NbLignes, 5) As Variant

    Range("L2").Select
    For I = 2 To NbLignes
        TB(I, 1) = Cells(I, 12)
        TB(I, 2) = Cells(I, 1)
        TB(I, 3) = Cells(I, 5)
        TB(I, 4) = Cells(I, 9)
        TB(I, 5) = Cells(I, 4)
        If TB(I, 2) = ColA And InStr(TB(I, 3), ColE) > 0 And TB(I, 4) = ColI And TB(I, 5) = ColD Then
            Compteur = Compteur + 1

            For J = 2 To I - 1
                If TB(I, 1) = TB(J, 1) And TB(I, 2) = TB(J, 2) And TB(I, 3) = TB(J, 3) And _
                    TB(I, 4) = TB(J, 4) And TB(I, 5) = TB(J, 5) Then
                    Compteur = Compteur - 1
                    Exit For
                End If
            Next J
        End If
    Next I

    NbVendeursUniques = Compteur

End Function

Bien cordialement

Le 16/04/2019 à 15:51
Willou a écrit :

Question : Pourquoi le résultat est il 1 alors que je cherche le nombre de vendeurs différents (colonne L étant leurs identifiants uniques) répondant à différents critères ?

Parce que c'est 1 pour tes critères !. Si tu mets d'autres critères, tu peux monter jusqu'a 6. tu peux d’ailleurs vérifier avec le tcd ou filtrer le tableau principal, tu verras bien

C'est quoi, ta formule qui pose problèmes ??

Crdlmt

Le 16/04/2019 à 15:57

J'avais mis celle çi: Mais j'ai comme résultat 0.

=NB(1/FREQUENCE(SI(($A$3:$A$9624=2020)*($I$3:$I$9624="csg")*($D$3:$D$9624="austria");EQUIV($L$3:$L$9624;$L$3:$L$9624;0));LIGNE(INDIRECT("1:"&LIGNES($L$3:$L$9624)))))

DjiDji59430 a écrit :
Willou a écrit :

Question : Pourquoi le résultat est il 1 alors que je cherche le nombre de vendeurs différents (colonne L étant leurs identifiants uniques) répondant à différents critères ?

Parce que c'est 1 pour tes critères !. Si tu mets d'autres critères, tu peux monter jusqu'a 6. tu peux d’ailleurs vérifier avec le tcd ou filtrer le tableau principal, tu verras bien

C'est quoi, ta formule qui pose problèmes ??

Crdlmt

Le 16/04/2019 à 16:15

Le resultat de ta formule est 115 et est confirmé par le TCD

https://www.cjoint.com/c/IDqonsClFQy

C'est une formule matricielle tridactyle, donc a valider par

Maj+Ctrl+Entrée (les 3 doigts en même temps) pour faire apparaitre les accolades et a chaque fois qu'on y touche.

Le 16/04/2019 à 16:31

Merci je ne connaissais pas la manipulation.

Une fois la manipulation des 3 touches je n'ai pas à recommencer chaque fois que je mets à jour la base de données ? si ?

Peut on choisir les colonnes plutôt ? Je ne suis pas sur que ça fonctionne ainsi ?

=NB(1/FREQUENCE(SI(($A:$A=2020)*($I:$I="csg")*($D:$D="austria");EQUIV($L:$L;$L:$L;0));LIGNE(INDIRECT("1:"&LIGNES($L:$L)))))

DjiDji59430 a écrit :

Le resultat de ta formule est 115 et est confirmé par le TCD

https://www.cjoint.com/c/IDqonsClFQy

C'est une formule matricielle tridactyle, donc a valider par

Maj+Ctrl+Entrée (les 3 doigts en même temps) pour faire apparaitre les accolades et a chaque fois qu'on y touche.

Le 16/04/2019 à 18:22

Re

Willou a écrit :

Une fois la manipulation des 3 touches je n'ai pas à recommencer chaque fois que je mets à jour la base de données ? si ?

Non, seulement si tu retouches a ta formule

Willou a écrit :

Peut on choisir les colonnes plutôt ? Je ne suis pas sur que ça fonctionne ainsi ?

C'est une matricielle et ça prend beaucoup de temps Ça depend de ton pc mais ça fonctionne aussi

Cependant, si tu transformes ta plage en tableau structuré, tu n'as plus a t'occuper de la longueur de la plage.

=NB(1/FREQUENCE(SI((Data[Fiscal Year]=2020)*(Data[CSG/ISG]="csg")*(Data[Partner Country]="austria");EQUIV(Data[(POS) Validated Reseller Affinity ID];Data[(POS) Validated Reseller Affinity ID];0));LIGNE(INDIRECT("1:"&LIGNES(Data[(POS) Validated Reseller Affinity ID]))))) fonctionne quelle que soit la longueur de la plage de ton tableau que tu as renommé Data

Crdlmt

Le 23/04/2019 à 14:25

Malheureusement quand j'utilise cette formule sur ma base de données qui fait 400 000 lignes (et qui va continuer de grossir jusqu'à la fin du trimestre) le résultat reste à 0 malgré le controle Maj entrée. RIen ne se passe bien que les accolades apparaissent dans la formule.

Je sais que ça prend du temps mais quand même. 2h après cela reste à 0. Je ne sais pas pourquoi.

=NB(1/FREQUENCE(SI((DataQ1!$A1:$A800000=2020)*(DataQ1!$I1:$I800000="csg")*(DataQ1!$D1:$D800000="austria")*(DataQ1!$E1:$E800000="*Ingram*");EQUIV(DataQ1!$L1:$L800000;DataQ1!$L1:$L800000;0));LIGNE(INDIRECT("1:"&LIGNES(DataQ1!$L1:$L800000)))))

Le 23/04/2019 à 16:41

Re

(DataQ1!$E1:$E800000="*Ingram*")

Ça, ça ne fonctionne pas

tu dois faire((DataQ1!$E1:$E800000="Ingram micro at")+(DataQ1!$E1:$E800000="Ingram micro belgium"))

=NB(1/FREQUENCE(SI((DataQ1!$A1:$A800000=2020)*(DataQ1!$I1:$I800000="csg")*(DataQ1!$D1:$D800000="austria")*((DataQ1!$E1:$E800000="Ingram micro at")+(DataQ1!$E1:$E800000="Ingram micro belgium"));EQUIV(DataQ1!$L1:$L800000;DataQ1!$L1:$L800000;0));LIGNE(INDIRECT("1:"&LIGNES(DataQ1!$L1:$L800000)))))

Crdlmt

Le 24/04/2019 à 09:40

D'accord bon à savoir qu'il faut marquer les noms en entiers et pas simplement utiliser un asterisque comme dans une formule classique. Dans mon cas le résultat est le même mon ordi s'excite mais cela reste 0.

J'ai juste besoin de 2020, CSG, Austria et Ingram Micro At comme critères.

=NB(1/FREQUENCE(SI((DataQ1!$A1:$A800000=2020)*(DataQ1!$I1:$I800000="csg")*(DataQ1!$D1:$D800000="austria")*((DataQ1!$E1:$E800000="Ingram micro at"));EQUIV(DataQ1!$L1:$L800000;DataQ1!$L1:$L800000;0));LIGNE(INDIRECT("1:"&LIGNES(DataQ1!$L1:$L800000)))))

DjiDji59430 a écrit :

Re

(DataQ1!$E1:$E800000="*Ingram*")

Ça, ça ne fonctionne pas

tu dois faire((DataQ1!$E1:$E800000="Ingram micro at")+(DataQ1!$E1:$E800000="Ingram micro belgium"))

=NB(1/FREQUENCE(SI((DataQ1!$A1:$A800000=2020)*(DataQ1!$I1:$I800000="csg")*(DataQ1!$D1:$D800000="austria")*((DataQ1!$E1:$E800000="Ingram micro at")+(DataQ1!$E1:$E800000="Ingram micro belgium"));EQUIV(DataQ1!$L1:$L800000;DataQ1!$L1:$L800000;0));LIGNE(INDIRECT("1:"&LIGNES(DataQ1!$L1:$L800000)))))

Crdlmt

Le 24/04/2019 à 12:55

re

Mon pc beugue au dessus de 300000 lignes. Il te faudrait du VBA, mais je ne sais pas faire !

Repose ta question en disant dans le titre que tu veux du vba et tu expliques que tu veux appliquer une matricielle a 800000 lignes et que ton pc beugue.

Crdlmt

Rechercher des sujets similaires à "formule ens idee"