compter nb de valeur sans doublon et en excluant des valeurs

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Y
Yuukia
Jeune membre
Jeune membre
Messages : 18
Inscrit le : 11 avril 2016
Version d'Excel : 2010 EN

Message par Yuukia » 4 octobre 2016, 10:50

Bonjour à tous !

Après moulte recherche je pense que mon problème ne c'est pas déjà posé.
Alors voila je dois compter le nombre de valeur unique d'une liste (jusqu'à la c'est assez basique et je m'en serait sortie comme une grande) mais je dois en plus ne pas compter les valeurs en doublon (la encore j'ai la solution) mais surtout je dois pouvoir exclure toute les cellules qui commencent par "matricule".

Ma difficulté c'est que je ne peux pas lister l'ensemble des cellules a exclure car je travaille sur des bases avec des centaines de ligne...
Actuellement pour compter le nombre de valeur unique j'ai mis cette formule matricielle (bien faire ctrl+maj+entrée) :
=SUM(IF(FREQUENCY(IF(LENB(A2:A10)>0;MATCH(A2:A10;A2:A10;0);"");IF(LENB(A2:A10)>0;MATCH(A2:A10;A2:A10;0);""))>0;1))

Il me manque donc à exclure les cellules commençant par "matricule" sachant qu'il sont complété par des numéros différents...
Je ne sais pas si ça peut aider, mais il sont souvent en plage mais l'intérêt pour moi est de reporter cette formule sur une centaine de fichier différent avec des listes différentes ou tout dois être automatique...

Pour faciliter la compréhension du problème je vous mets mon exemple sur lequel je travaille.

D'avance merci pour tous ceux qui prendront le temps de me répondre et qui auront une solution a me proposer
essai excel.xlsx
(10.14 Kio) Téléchargé 11 fois
f
frangy
Passionné d'Excel
Passionné d'Excel
Messages : 4'337
Appréciation reçue : 1
Inscrit le : 19 novembre 2012
Version d'Excel : 2007 FR

Message par frangy » 4 octobre 2016, 11:16

Bonjour,

Tu peux utiliser un filtre avancé, l'intérêt dans ton cas étant que ce type de filtre permet d'avoir une liste sans doublon.
Il suffit ensuite de compter les cellules restantes avec la fonction SOUS.TOTAL.

A+
Yuukia.xls
(231.5 Kio) Téléchargé 9 fois
Y
Yuukia
Jeune membre
Jeune membre
Messages : 18
Inscrit le : 11 avril 2016
Version d'Excel : 2010 EN

Message par Yuukia » 4 octobre 2016, 11:42

bonjour,

je te remercie pour ta solution qui marche bien je le reconnais mais l'inconvénient est pour moi que cela oblige à une manipulation sur les 100 fichiers différents...
Mon objectif est d'avoir la formule qui marche et que je n'ai qu'a changer les bases sans manipulation supplémentaire pour afficher le chiffre
f
frangy
Passionné d'Excel
Passionné d'Excel
Messages : 4'337
Appréciation reçue : 1
Inscrit le : 19 novembre 2012
Version d'Excel : 2007 FR

Message par frangy » 4 octobre 2016, 12:58

Une solution avec une fonction personnalisée.

A+
Yuukia-1.xls
(43 Kio) Téléchargé 43 fois
Y
Yuukia
Jeune membre
Jeune membre
Messages : 18
Inscrit le : 11 avril 2016
Version d'Excel : 2010 EN

Message par Yuukia » 4 octobre 2016, 13:26

waw c'est génial !!!!
mais comment as-tu fait ? car je dois mettre cette formule sur mon fichier et je ne sais pas si un basique copier coller va marcher :p

mais vraiment merci parce que vu comme ça, ça me semble simple ;)
f
frangy
Passionné d'Excel
Passionné d'Excel
Messages : 4'337
Appréciation reçue : 1
Inscrit le : 19 novembre 2012
Version d'Excel : 2007 FR

Message par frangy » 4 octobre 2016, 16:10

Comme je te l'ai indiqué précédemment, il s'agit d'une fonction personnalisée, c'est à dire une fonction créée avec VBA.

Pour visualiser le code,
alt + F11 pour ouvrir l'éditeur,
ctrl + R pour ouvrir l’explorateur de projets.
La fonction se trouve sous Modules/ Module1.
Function Nb_Valeurs(Plage As Range) As Long
Dim Dico
Dim Cel As Range
Dim n As Long
    Set Dico = CreateObject("Scripting.Dictionary")
    For Each Cel In Plage
        If Not Dico.Exists(Cel.Value) And Cel.Value <> "" And Not (Cel.Value Like "*Matricule*") Then
            Dico.Add Cel.Value, Cel.Value
            n = n + 1
        End If
    Next Cel
    Nb_Valeurs = n
End Function
L'objet "dictionnaire" utilisé par la fonction permet d'obtenir une liste sans doublon.
Les éléments du dictionnaire étant uniques, on peut incrémenter un compteur chaque fois qu'un élément est ajouté et obtenir ainsi le nombre d'éléments uniques. Pour éviter de compter les cellules vides et celles qui contiennent "Matricule", il suffit d'ajouter un critère d'exclusion.

Pour utiliser cette fonction avec plusieurs classeurs, il faut créer un complément afin qu'elle soit disponible dès l'ouverture de l'application.

A+
Avatar du membre
DjiDji59430
Membre impliqué
Membre impliqué
Messages : 2'165
Appréciations reçues : 78
Inscrit le : 18 avril 2015
Version d'Excel : 2019 FR

Message par DjiDji59430 » 4 octobre 2016, 16:34

Bonjour,

en formule (matricielle)

=SUM(IF(FREQUENCY(IF(LENB(A2:A10)>0;MATCH(A2:A10;A2:A10;0);"");IF(LENB(A2:A10)>0;MATCH(A2:A10;A2:A10;0);""))>0;1)) -SOMME((GAUCHE(A2:A13;3)<>"mat")*1)
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message