Excel 2010 : Supprimer les doublons

Pour en finir avec la question des doublons dans les listes de données, je souhaitais simplement rappeler qu'un code VBA ou une macro commande ne vous sera probablement pas utile si vous êtes utilisateurs des versions Excel 2010 ou 2013. Effectivement dans ces versions,  des outils sont intégrés pour la suppression des doublons et devraient largement couvrir la plupart de vos besoins dans ce domaine.

Cette vidéo vous explique la démarche à suivre à travers deux méthodes très simples. Elle vous permettra de plus de découvrir la notion de tableau mise en œuvre par Microsoft pour faciliter le travaux les plus courants sur vos listes de données.




Excel 2010 : Supprimer les doublons dans une... par O_Picot_chez_AV


Bonne consultation...

Partager |

Le quartet d'Anscombe

Démarrons aujourd'hui une nouvelle série d'articles sur la construction des graphiques dans Excel. La problématique ne sera pas la réalisation technique de ces graphiques (les manipulations nécessaires étant en général d'une extrême simplicité) mais le choix du bon type de représentation en fonction des données à analyser.
Dans ce premier article nous allons étudier le quartet d'Anscombe, il s'agit d'une suite de 4 séries statistique dont les moyennes arithmétiques simples et les variances sont rigoureusement identiques mais dont les tracés sont totalement inégales. Certainement, il s'agit d'un cas fortuit très particulier, mais il met parfaitement en lumière l'importance de l'expression graphique dans l'analyse des données chiffrées.

Étape 1 : Commençons  par la réalisation du tableau chiffrée, après la saisie il suffira de calculer la moyenne =MOYENNE(B5:B15) et la variance =VAR.P.N(B5:B15) et de les recopier vers la droite.

Étape 2 : Traçons maintenant les 4 graphiques (X,Y) à l'aide du type nuage de points, correspondant aux quatre séries de données.

Étape 3 : Nous pouvons maintenant vérifier d'autres propriétés statistiques et constater à nouveau des résultats identiques pour les quatre séries.
En premier lieu vérifions la corrélation des plages X et Y à l'aide des coefficients de corrélation r={COEFFICIENT.CORRELATION(C5:C15;B5:B15)} ou de détermination R²={COEFFICIENT.DETERMINATION(C5:C15;B5:B15)}.

Ensuite calculons les paramètres a et b de l'équation y = ax + b à l'aide de la fonction ={DROITEREG(C5:C15;B5:B15)}, le résultat est immanquablement :

y = 1/2x + 3

Ne reste plus alors que l'ajout à l'aide du menu contextuel de la droite de régression linéaire sur le graphique et la vérification par la méthode graphique d'excel du R² et de l'équation de la droite. Si vous ne maitrisez pas cette partie, reportez vous à mon article du 1^er Mars 2009 sur la tendance d'une série de valeur.


Etape 4 : Essayons maintenant de calculer le r, non pas pour les 11 valeurs de la  série 3, mais uniquement avec 10 valeurs en excluant la valeur aberrante, vous constaterez alors que le coefficient passe de 0.82 à 1. (r = 1 ou r = -1 indiquant une corrélation parfaite)

Conclusion :  Éclairage sur l’intérêt des représentations graphiques et mise en évidence de l'influence des données aberrantes, voici l'apport du quartet d'Anscombe.

Merci de votre attention,

Partager |

VBA : Supprimer les doublons

Nous voici donc sur le second article concernant le probléme des doublons dans les bases de données. La procédure suivante va nettoyer la base de tous les doublons qu'elle contient, ici nous considérons que les clés en surnombre sont placées dans  la première colonne.
Par sécurité il n'y a pas d'action dans la base de départ, le contenu de la base est stockée dans un tableau a(), puis après traitement, la nouvelle base contenue dans un tableau c() sera récupérée sur une autre feuille de calcul.
Nous utilisons bien sur l'objet Dictionary pour réaliser ce traitement, vous pouvez consulter l'article précédent si vous ne maîtrisez pas complétement cet objet.


Sub Doublons_BDD()

Dim a(), c()
Dim mondico As Object
Dim ligne, i, k As Long

  Application.ScreenUpdating = False

'va suspendre l'affichage à l'écran des travaux de la macro
'ce qui accélére son exécution le nombre de lignes dans la base pouvant être élevé

  Set mondico = CreateObject("Scripting.Dictionary")
 
  '********************************************************************
  'attention je suis sur la feuille ou se trouve ma base
  'sinon je declare un autre dico puis le dico est stockée dans le tableau
  'dim dico2 as object
  'Set dico2 = Sheets("BD")
  'a = dico2.Range("A1").CurrentRegion.Value
  '***************************************************************
 
  a = Range("A1").CurrentRegion.Value
  'La base est stockée dans le tableau a()

  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
  ligne = 1
 
  For i = 1 To UBound(a)
    If Not mondico.Exists(a(i, 1)) Then
      mondico.Add a(i, 1), 1
      For k = 1 To UBound(a, 2): c(ligne, k) = a(i, k): Next k
      ligne = ligne + 1
    End If
  Next

  Sheets("BD-2").[A1].Resize(mondico.Count, UBound(a, 2)) = c
'il faut recopier le tableau c() sur la feuille de calcul "BD-2"
End Sub

Bonne lecture et bon courage pour vos adaptations...

Partager |

VBA : Repérer les doublons

Nous allons consacrés deux articles à la problématique des doublons à l'intérieur
des bases de données. Les doublons pouvant se définir comme deux enregistrement rigoureusement identiques dans une base, les valeurs de tous les champs de l'enregistrement sont communs, ou alors seulement les données d'une colonne  (généralement la colonne qui doit servir de clé unique) sont communes (Numéro de client, E-mail...). Enfin par doublons il faut entendre que la valeur n'est pas forcément doublée, elle peut être triplée etc...

En général nous souhaitons pouvoir repérer les informations présentes en surnombre, cela fera l'objet de cet article. La suppression définitive des enregistrements redondants sera l'objet de l'article suivant.
Il existe plusieurs méthodes et techniques pour intervenir sur cette problématique, j'ai notament montré dans une vidéo du 12/04/2012 comment travailler grâce à la fonction =nb.si() et la commande Validation des données de l'onglet Données.
 En terme de programmation le bon réflexe est de penser Collection et Dictionary. Le premier objet étant réputé très lent en terme de résultat, nous allons ici présenter l'objet Dictionary.
 Cet objet associe des valeurs (nommées Items) à des clés (Keys). Il s'agit en fait d'un tableau à une dimension dont on accède aux éléments par une clé et non par un indice.

Soit un Dictionnaire MonDico :

Keys	Items
bleu	2
rouge	6
vert	9
blanc	4
noir	7

 
Les métodhes et propriétés suivantes peuvent être associés au Dictionnaire :

Add clé,élément	Ajoute une clé et la valeur associée
Exists(clé)	Teste l'existence d'une clé
Tbl=Items	Donne dans un tableau les éléments
Tbl=Keys	Donne dans un tableau les clés
Remove (clé)	Suprime la clé
Removeall	Supprime tous les éléments
Count	Donne le nombre d'éléments
Item(clé) =valeur	Modifie la valeur de la clé
Item(clé)	Donne la valeur associée à la clé
CompareMode=vbTextCompare	Ignore la casse

 
Attention ! Vous ne pourrez pas utiliser l'objet Dictionary si vous n'avez pas chargé la bibliothéque "Scripting". Cocher la ligne "MicroSoft Scripting Runtime" dans la commande Outils / références... de votre éditeur VBA.

La procédure ci dessous va vous permettre de vous familiariser avec les manipulations de base en matière de dictionnaire. Pour ceux qui veulent étudier cette question de manière plus approfondie, lisez l'excellent article de Jacques Boisgontier.

Option Explicit
Option Base 1
Sub ListeDictionnaire()

Dim mondico As Object, cle
Set mondico = CreateObject("Scripting.Dictionary")
'déclaration classique de l'objet dictionnaire notez toutefois le ,cle
'qui servira de variable (type range) pour la colonne clé
Dim TabC()
'tableau non typé et non dimensionné pour recevoir les clés du dictionnaire
Dim TabI()
'tableau non typé et non dimensionné pour recevoir les items du dictionnaire

'*** Les trois méthodes permettant l'association des items aux clés ***
  mondico.item("bleu") = 2
  mondico.item("rouge") = 6
  mondico("vert") = 9
  If Not mondico.Exists("violet") Then mondico.Add "blanc", 4
  mondico.item("noir") = 7
 
 '*** vérifions l'item pour une clé ***
  cle = "noir"
  MsgBox cle & " : " & mondico.item(cle), vbOKOnly + vbInformation
 
  '*** vérifions tous les items de  toutes les clés ***
  For Each cle In mondico.Keys
     MsgBox cle & " : " & mondico.item(cle), _
     vbOKOnly + vbInformation, vbOKOnly + vbInformation, _
     " Clés + Item : "
  Next cle
 
  '*** pour passer des éléments clés et items dans des tableaux ***
  TabC = mondico.Keys
  TabI = mondico.Items
  MsgBox TabC(3) & " : " & TabI(3), _
  vbOKOnly + vbInformation, vbOKOnly + vbInformation, _
  " Clés + Item : "
   
 '*** faire des Statistiques sur les items ***
MsgBox "Total : " & Application.Sum(mondico.Items) _
 & " - Moyenne : " & Application.Average(mondico.Items), _
 vbOKOnly + vbInformation, "Statistiques : "
End Sub

Venons en maintenant au probléme de repérage des doublons, analysons ensemble la procédure suivante qui va repérer par un code couleur touts les doublons de ma base sur la colonne que j'ai choisi comme étant la clé de mon dictionnaire.

Sub Doublons_repere()

Dim mondico As Object, cle
Set mondico = CreateObject("Scripting.Dictionary")
 
Columns("A:A").Select
Selection.Interior.ColorIndex = xlNone
'nous travaillons donc sur la colonne A
'pour chaque clé de A2 jusqu'à la fin de la colonne
  For Each cle In Range("a2", Range("a2").End(xlDown))
  'on va affecter à chaque clé du dictionnaire un item = à 1
     mondico.item(cle.Value) = mondico.item(cle.Value) + 1
     'si la clé est présente 2 fois ou plus on aura un item > à 1
  Next cle
 
  For Each cle In Range("a2", Range("a2").End(xlDown))
   'reste alors le coloriage du fond des cellules pour les item
      'présents 2 fois ou plus
    If mondico.item(cle.Value) > 1 Then cle.Interior.ColorIndex = 6
  Next cle
End Sub

Bonne lecture et bon courage pour vos adaptations...

 

Partager |

VBA : La fonction OnTime

Comment déclencher l’exécution d’une macro commande ou d’une procédure VBA en fonction du temps, c’est-à-dire comment créer un minuteur pouvant déclencher l’exécution d’une action à une date précise ou après un intervalle de temps déterminé. Nous allons étudier ici la méthode OnTime de l’objet application qui permet d’arrivée à ce résultat. Cette méthode es décrite à l’aide de 4 paramètres que nous allons décrire ici. La syntaxe en est :

Application.OnTime EarliestTime, Procedure, [LatestTime], [Schedule]

 

EarliestTime

 

(Argument obligatoire) est la valeur temps qui indique le moment de démarrage d’une procédure. Cette programmation horaire peut s’écrire et se concevoir e deux manières :

 

Lancer une procédure à une heure précise : 

 

Dans ce premier exemple (Sub attendre_exemple1) l’exécution de la macro aura lieu à 10 h et 53’. Nous utilisons la fonction TimeValue( ) qui va retourner une variable de type date contenant l’heure. Ensuite la méthode OnTime exécutera la procédure affiche_1 (noter que le nom de la procédure est utilisé sous la forme d’une chaîne de texte). La boite de dialogue affiche alors l’heure courante.

Public heure As Date ‘ou Variant
Sub attendre_exemple1() ‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = TimeValue("10:53:00")
Application.OnTime heure, "affiche_1", , True
End Sub
Sub affiche_1()
MsgBox "ll est : " & heure, vbOKOnly + vbInformation, "Horloge : "
End Sub

 

Lancer une procédure après un délai imposé :

 

Dans ce deuxième exemple il faudra attendre 10 secondes pour voir la macro s’exécuter. Nous utilisons la fonction Now qui va retourner une variable de type date contenant l’heure et la date système de l’ordinateur à laquelle nous allons ajouter le délai. OnTime exécutera la procédure affiche_2. La boite de dialogue affiche ici la date système complète.

Public heure As Date
Sub attendre_exemple_2()‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = Now + TimeValue("00:00:10")
Application.OnTime heure, "affiche_2", , True
End Sub
Sub affiche_2()
MsgBox heure, vbOKOnly + vbInformation, "Horloge : "
End Sub

 

Procedure

 

(Argument obligatoire) est la valeur de type chaîne de texte qui contient le nom de la procédure à exécuter.

LatestTime


(Argument facultatif) est la valeur temps qui indique le délai maximal d’attente d’Excel en cas d’indisponibilité de ce dernier (exécution d’une autre procédure en cours). Si le logiciel n'est pas disponible au bout de ce délai, la procédure ne s'exécutera pas. Si ce paramètre est omis, le logiciel peut attendre indéfiniment avant l’exécution de OnTime, il semble donc que cette seconde option soit recommandable.
Si vous devez indiquer une valeur LatestTime, vous pouvez la calculer à partir de EarliestTime.

LatestTime = EarliestTime + 10 'pour attendre 10 secondes la disponibilité d’Excel.

Schedule

 

 (Argument facultatif) est la valeur de type booléen qui indique si la procédure doit être exécutée ou non. La valeur par défaut est True. Le problème est que pour stopper une procédure OnTime il faut renvoyer à nouveau cette dernière en paramétrant la valeur de Schedule à False. Cette opération générant une erreur il faudra utiliser le processus habituel en matière de gestion d’erreur « On Error Resume Next » qui détournera tous messages d’erreurs liés aux instructions suivantes.

Dans ce troisième et dernier exemple, nous allons afficher à quatre reprises (pendant une minute) l’heure courante dans la cellule A1 (mise en format hh:mm:ss) avec un intervalle de 15 secondes entre chaque nouvel affichage, puis nous interromprons la procédure en passant l’argument Schedule à False, c’est seulement de cette manière que l’arrêt de la méthode OnTime s’effectue convenablement.

Public heure As Date
Public compteur As Byte
Sub attendre_exemple_3()()‘la macro est ici accrochée à un bouton de commande de la feuille de calcul
heure = Now + TimeValue("00:00:15")
Application.OnTime heure, "affiche_3", , True
End Sub
Sub affiche_3()
Dim EcrireH As String
Range("a1").ClearContents
EcrireH = heure
Range("a1").Value = EcrireH
compteur = compteur + 1
If compteur = 4 Then
MsgBox "TERMINE", vbOKOnly + vbInformation, "Horloge : "
Range("a1").ClearContents
compteur = 0
On Error Resume Next
Application.OnTime heure, "affiche_3", , False
Else
attendre_exemple_3
End If
End Sub

Bon courage pour vos tests et vos adaptations...

Partager |