Formule Nb si ens ou autre idée ?
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
Bonjour,
La formule vient du site de Boisgontier
https://www.cjoint.com/c/IDqkaBXfc0y
On peut le faire avec un TCD
Crdlmt
Merci, mais j'aimerais éviter le TCD car j'ai beaucoup de formules à décliner et de combinaisons à faire.
Tu l'as, la, la formule en F1 !
Crdlmt
Ah oui pardon je n'avais pas vu.
Merci je regarde ça
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 ?
J'ai modifié la formule en F1 mais le résultat est différent du TCD.
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
Merci, il va me falloir un peu de temps pour décortiquer tout ça.
C'est vrai que j'ai quand même tendance à privilegier les formules aux VBA.
T
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
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
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)))))
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 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.
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)))))
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.
Re
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
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
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)))))
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
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)))))
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
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