Nouvelle fonction excel "occurence" pour détecter les doublons quand on ne peut pas trier la feuille

Soyez le premier à donner votre avis sur cette source.

Vue 6 356 fois - Téléchargée 530 fois

Description

Pour répondre à la demande du membre SNOFNIE
qui ne peut ni trier sa feuille ni en supprimer les doublons avec les methodes classiques d'excel.

Cette fonction detecte les doublons sans trier la feuille
Pour le premiere occurence d'un identifiant la fonction retourne 1
Pour toutes les autres occurences la fonction retourne "n"

Source / Exemple :


Public Function Occurence(oCell As Range, oRange As Range)
  
   ' ----------------------------------------------------------------------------
   ' Nouvelle fonction Excel par BILLOT Michel 20120530
   ' Détermine s'il s'agit de la premiere occurence de la cellule oCell sur la plage oRange
   ' Pour identifier les doublons sans avoir à trier la feuille
   ' ----------------------------------------------------------------------------
   ' Retourne
   '    "#" si la cellule cherchee ne fait pas partie de la plage ou si plage au lieu de celleule cherchee
   '    ""  si la valeur cherchee est vide
   '     1  s'il s'agit de la premiere occurence de la cellule sur la plage
   '    "n" pour toutes les autres occurences de la valeur
   
   ' Exemple d'utilisation dans une formule Excel
   '      =Occurence(D222,D$2:D$857)
   '
   ' Exemple d'utilisation pour générer la formule dans la cellule F19
   '       Range("F19").Formula = "=occurence(D19,D$2:D$857)"
   '
   ' Exemple d'utilisation de la fonction directement en VBA
   '    Select Case Occurence(Range("D19"), Range("D$2:D$857"))
   '       Case Is = 1
   '       Case Else
   '    End Select
   
   Dim wCell As Range
   Dim Found%, Ctr%
   
   If oCell.Cells.Count = 1 Then
      For Each wCell In oRange
         If Not IsEmpty(wCell.Value) Then
            If wCell.Value = oCell.Value Then
               ' Cellule contenant la valeur demandée
               Ctr% = Ctr% + 1
            End If
         End If
         If wCell.Address = oCell.Address Then
            ' Cellule recherche trouvée dans la plage
            Found% = True
            Exit For
         End If
      Next wCell
   End If
   
   If Found% Then
      Select Case Ctr%
         Case Is = 0:    Occurence = ""     ' Cellule vide recherchée
         Case Is = 1:    Occurence = 1      ' 1 ere occurence
         Case Else:      Occurence = "n"    ' n eme occurence
      End Select
   Else
      ' Erreur d'utilisation
      ' La cellule recherchee n'est pas sur la plage indiquée
      ' ou bien c'est une plage qui est cherchee au lieu d'une cellule
      Occurence = "#"
   End If
         
End Function

Codes Sources

A voir également

Ajouter un commentaire

Commentaires

Bonjour,

la macro est un bel exemple didactique mets c'est encore plus facile d'utiliser les fonctions d'XL et surtout plus rapide si on a des millions de cellules.

Donc si on utilise dans la case d8 la fonction =COUNTIF($F$10:$K$18;C8)
on aura dans la case d8 le nombre d'occurences du contenu de la case c8 dans le range absolu f10 k18

Plus drôle si on veut avoir un message si on dépassse 1
on change la formule =IF(COUNTIF($F$10:$K$18;C8)<=1;COUNTIF($F$10:$K$18;C8);" n occurrences ")

mais si on veut être plus performant
on garde la formule =COUNTIF($F$10:$K$18;C8)
et on applique le format suivant [Green][<2]# ##0;"zéro";[Blue]"n occurences";"Zorro est arrivé"
j'ai mis des couleurs, c'est plus joli ;)

pour cela aller dans format cells et puis custom (sorry je n'utilise que la version US)
ici perf maximale car pas de double calcul possible et pas de if, seulement à l'affichage

Sinon, dans la macro pour un peu de perf, juste après la ligne 34 rajouter if Ctr% >1 then exit for

A+

Mon principe: vive les formules
mets >>> mais
quelle horreur
BILLOTmi
Messages postés
13
Date d'inscription
jeudi 27 novembre 2008
Statut
Membre
Dernière intervention
25 octobre 2018
-
Bonjour,
Je crois que SquirelXL n'a pas bien compris l'énoncé du problème.

Il ne s'agit pas de compter le nombre d'occurrences d'une valeur
(ce que fait la fonction CountIF ou NB.SI en français)
mais de déterminer si dans une liste de valeurs une cellule est la premiere ou la n ème.
Le but étant d'identifier l'original et les doublons sans trier la feuille (condition imposée par le membre utilisateur SNOFNIE).

Avec la solution CountIF on n' identifierait que les valeurs uniques et les valeurs multiples
alors que l'on veut identifier la première occurrence d'une valeur avec 1 et ses doubles avec n

Quand à l'optimisation proposée elle génèrerait un retour "#" erreur à tort.
Si on veut vraiment optimiser il faut faire
' Cellule contenant la valeur demandée
Ctr% = Ctr% + 1
If Ctr% > 1 Then
Occurence = "n"
Exit Function
End If
Mais alors on ne vérifie plus si la cellule recherchée est bien à l'intérieur de la plage.

Donc je ne changerais rien à la solution proposée au probleme de SNOFNIE.
Michel BILLOT

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.