Bonjour Petitpeton, Marc et tout le forum
J'avais noté cette doc à un moment ou j'avais un Pbl de doublons.
peut-être sera-t-elle utile. (elle n'est pas de moi, Laurent Longre peut-être)
Les Doublons sous Excel 97
A plusieurs reprises vous m'avez posé la question des valeurs ou désignations en doubles dans un fichier Excel. Cette page présente différentes méthodes (formules, macro...) pour détecter et éliminer des données ou enregistrements dupliqués dans des plage de cellules.
Quatre méthodes :
1. Contrôle de doublons à la saisie
2. Elimination de doublons par formules
3. Elimination de doublons par filtre élaboré
4. Elimination de doublons par macro
1. Contrôle de doublons à la saisie (simple et efficace à la saisie)
Pour éviter la présence de doublons dans une plage en avertissant en cours de saisie que la valeur a déjà été entrée, on peut exploiter l'outil de validation des données.
Par exemple, pour avertir par un message d'alerte lorsque l'on saisit une valeur déjà existante dans la plage A2:A300 :
- Sélectionner la plage A2:A300
- Dans le menu 'Données', activer la commande 'Validation'
- Dans la boîte de dialogue suivante, onglet 'Options' :
. Sélectionner 'Personnalisé' dans la liste déroulante 'Autoriser'
. Dans la zone de texte 'Formule', saisir la formule suivante=NB.SI(A$2:A$300;A2)=1
- Dans l'onglet 'Alerte d'erreur', indiquer le titre et le message de la boîte de dialogue avertissant l'utilisateur que la valeur saisie a déjà été entrée dans la plage.
2. Elimination de doublons par formules (simple pour corriger)
Ces formules permettent de reproduire dans une autre plage les valeurs déjà saisies dans une plage-colonne en éliminant tous les doublons. La plage source doit être de taille relativement limitée (quelques centaines de cellules) si l'on veut éviter que le temps de calcul nécessité soit trop important.
Exemple : reproduire dans la plage B2:B19 les valeurs déjà saisies dans la plage A1:A19 en éliminant les doublons.
- Taper la formule ="" dans la cellule B20, et faire un copier-coller sur cette cellule par valeur uniquement. - En B2, entrer la formule =A2
- En B3, saisir la formule suivante (saisie matricielle par la combinaison de touches Ctrl-Maj-Entrée) :
=SI(B2="";"";INDEX(A3:A$20;MIN(SI(NB.SI(B$2:B2;A3:A$19);LIGNE(A$20)-LIGNE()+1;LIGNE(A3:A$19)-LIGNE()+1))))
- Recopier cette formule vers le bas jusqu'à la cellule B19
Pour fonctionner correctement, cette formule nécessite que la plage source (ici A2:A19) ne comporte aucune cellule vide.
La fonction matricielle VALEURS.UNIQUES de la macro Morefun.xll permet également d'obtenir le contenu d'une plage ou matrice d'une seule colonne en éliminant les valeurs
dupliquées. Cette fonction reposant sur un tri par QuickSort, elle peut-être appliquée à des plages de plusieurs milliers de cellules sans ralentir de façon notable le recalcul de la feuille.
3. Elimination de doublons par filtre élaboré (simple pour corriger mais peut-être destructif)
Le filtre élaboré d'Excel dispose d'une option "extraction sans doublon" qui permet d'éliminer très efficacement les valeurs dupliquées d'une plage.
Exemple : extraction sans doublons de la plage A1:C100 (A1:C1 contenant les noms des champs) :
- Activer la commande Filtre -> Filtre élaboré du menu 'Données'
- Dans la boîte de dialogue "Filtre élaboré", indiquer les valeurs suivantes :
. Action : Copier vers un autre emplacement
. Plages : $A$1:$C$300
. Destination : toute cellule située à un emplacement libre de taille assez grande dans la feuille
. Activer l'option "Extraction sans doublon"
Le tableau créé après validation de la boîte de dialogue contient une copie de la plage d'origine A1:C300 sans enregistrements (lignes) dupliqués.
Pour remplacer la plage d'origine, il est nécessaire d'en effacer le contenu et de faire un couper-coller de la plage contenant les données filtrées vers l'emplacement de la plage source.
Si l'on veut appliquer un filtre non destructif (masquant simplement les enregistrements excédentaires), il faut garder l'option par défaut "Filtrer la liste sur place" au lieu de "Copier vers un autre emplacement" dans la boîte de dialogue du filtre élaboré.
4. Elimination de doublons par macro (marrant pour celui qui veut programmer un peu)
La macro suivante permet de supprimer les enregistrements dupliqués dans une plage, avec différentes options concernant le traitement des lignes "libérées" par l'élimination des doublons.
- La fonction SupprDoublons(Plage, Modif) supprime les enregistrements (lignes) dupliqués dans la plage donnée en argument. La première ligne de cette plage doit contenir les intitulés des champs. Cette fonction renvoie 0 en cas de succès, et un code d'erreur si l'opération a échoué.
L'argument 'Modif' indique l'action à effectuer sur les lignes excédentaires libérées par le filtrage :
. 0 (par défaut) = effacement sans suppression
. 1 = suppression des lignes à l'interieur de la plage
. 2 = suppression des lignes entières
- Les procédures Unique_Efface, Unique_SupprPartielle et Unique_SupprEntière éliminent les enregistrements dupliqués dans la plage actuellement sélectionnée en transmettant à la fonction de suppression (Function Unique) respectivement les trois paramètres précédents.
'_____________________________________________________________
Function SupprDoublons(Plage As Range, Optional Modif As Integer) As Long
Dim Temp As Range
Dim MiseAJourEcran As Boolean, Recalcul As Boolean
On Error GoTo Fin
' Sauvegarde des paramètres actuels d'affichage et de recalcul
If Application.ScreenUpdating Then
MiseAJourEcran = True
Application.ScreenUpdating = False
End If
If Application.Calculation = xlCalculationAutomatic Then
Recalcul = True
Application.Calculation = xlCalculationManual
End If
' Détermination de la plage d'extraction temporaire (Temp),
' exécution du filtre et déplacement vers la plage d'origine
With Plage
Set Temp = .Worksheet.Cells(.SpecialCells(xlCellTypeLastCell). _
Row + 1, 1).Resize(.Rows.Count, .Columns.Count)
.AdvancedFilter xlFilterCopy, CopyToRange:=Temp, Unique:=True
End With
Temp.Cut Plage ' Traitement des lignes libérées en fonction de 'Modif'
If Modif And WorksheetFunction.CountBlank(Temp) > 0 Then
With Range(Temp.End(xlDown)(2), Temp(Temp.Count))
If Modif = 1 Then .Delete xlShiftUp Else .EntireRow.Delete
End With
End If
' Réinitialisation du UsedRange
Temp.Parent.UsedRange
Fin:
SupprDoublons = Err
If MiseAJourEcran Then Application.ScreenUpdating = True
If Recalcul Then Application.Calculation = xlCalculationAutomatic
End Function
'___________________________________________________________
Sub Unique_Efface()
SupprDoublons Selection
End Sub
Sub Unique_SupprPartielle()
SupprDoublons Selection, 1
End Sub
Sub Unique2_SupprEntière()
SupprDoublons Selection, 2
End Sub
'______________________________________________________________
La fonction 'SupprDoublons' repose sur l'utilisation d'un filtre élaboré avec extraction sans doublon. Une plage temporaire (Temp) située en-dessous de la dernière cellule utilisée dans la
feuille de calcul reçoit les données construites par le filtre, et son contenu est recopié dans un deuxième temps à l'emplacement de la plage d'origine.
