Discussion :

[BENNASR]

Bonjour à toutes et à tous
Bonne fête pour celles et ceux qui font la fête...que du bonheur et joie pour cette fin d'année 2020
A toutes et tous je vous souhaite bonne Année 2021 pleine de santé et du succès
Pour une base de donnée je veux supprimer les articles qui se répètent 50 fois
pour ça j'ai fais un dictionnaire pour récupérer le nombre des lignes par article
puis un deuxième dictionnaire lié au premier dictionnaire pour les article égal à 50
A ce stade tout fonctionne d'ailleurs quand je met un message pour récupérer le nombre des articles du deuxième dictionnaire ça donne le bon résultat
Le problème commence quand je filtre ma BD avec ce deuxième dico...Excel ne répond plus et l'application se ferme ...
y a t'il des remarques ou des erreurs que j'ai commis ?? merci de m'éclairer sur le sujet
NB : Obliger de passer par VBA et non pas power Query

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub FiltreSelonListe()
  Set f1 = Sheets("Base de données")
  Set d = CreateObject("scripting.dictionary")
  d.CompareMode = vbTextCompare
   TblBD = f1.Range("A2:D" & f1.Range("A" & Rows.Count).End(xlUp).Row)
  For i = 1 To UBound(TblBD)
    clé = TblBD(i, 1)
    d(clé) = d(clé) + 1
  Next i
  Set d1 = CreateObject("scripting.dictionary")
   d1.CompareMode = vbTextCompare
For Each c In d
If d.Item(c) = 50 Then d1(c) = d1(c)
Next c
' ICI LE PROBLEME
 f1.Range("A1:D" & f1.Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=d1.keys, Operator:=xlFilterValues
End Sub

[Pierre Fauconnier]

Salut.

Perso, je n'utiliserais qu'un dictionnaire, mais peut-être n'ai-je pas bien compris ton propos.

Voici une macro utilisant un dico pour supprimer les valeurs qui se trouvent dans un tableau en nombre supérieur à la valeur de la limite fixée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub test()
  Dim Dico As Object
  Dim r As Range
  Dim i As Long
  Dim Limit As Long
 
  Limit = 5
  Set Dico = CreateObject("Scripting.dictionary")
  Dico.CompareMode = TextCompare
  For Each r In Range("Tableau1[Article]")
    If Not Dico.Exists(r.Value) Then
      Dico.Add r.Value, 1
    Else
      Dico.Item(r.Value) = Dico.Item(r.Value) + 1
    End If
  Next
 
  For i = Range("tableau1").Rows.Count To 1 Step -1
    If Dico.Item(Range("tableau1[Article]")(i).Value) >= Limit Then Range("tableau1").ListObject.ListRows(i).Delete
  Next
End Sub

[BENNASR]

Bonjour Pièrre et merci pour la réponse
il s'agit d'une grande base de données ou les articles (les lignes) se répètent plusieurs fois,
le but est de supprimer les articles qui se répètent exactement 20 fois
pour ça j'ai pensé à un dictionnaire pour compter les lignes par numéro d'article
un deuxième dico lié au premier pour récupérer que les article qui se répètent exactement 20 fois
puis j'ai pensé à passer par un filtre et non pas un boucle pour gagner en temps d'exécution
comment remplacer cette partie du code par un filtre selon les articles du dictionnaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
For i = Range("tableau1").Rows.Count To 1 Step -1
    If Dico.Item(Range("tableau1[Article]")(i).Value) >= Limit Then Range("tableau1").ListObject.ListRows(i).Delete
  Next

[Pierre Fauconnier]

Si tu changes le >= par = et que tu initialises la variable Limit à 20, ça devrait être bon.

Ma macro charge un dico en parcourant séquentiellement la colonne du tableau structuré, puis boucle de bas en haut sur le tableau et regarde dans le dico si on a atteint la limite pour cette valeur. Si oui, on supprime la ligne du tableau.

Teste peut-être sur un petit tableau avec une petite limite

[Philippe Tulliez]

Bonjour,
Une autre solution serait d'ajouter une colonne au tableau, même avec VBA, en utilisant la fonction NB.SI > 50, supprimer toutes les lignes dont la formule renvoitVRAI et puis supprimer la colonne éphémère

[Pierre Fauconnier]

Salut Philippe

[...]

J'y avais pensé, mais il faut faire un collage spécial valeurs sinon les résultats du NB.SI vont être modifiés à chaque suppression.

Perso, je n'aime pas trop les dicos donc je serais a priori parti sur un tableau temporaire sans doublons avec la formule NB.SI.ENS figée puis suppression des lignes qui répondent à la condition...

Il y a souvent plusieurs manières de pratiquer, mais je suppose que si BENNASR souhaite utiliser un dico, c'est qu'il ne veut pas utiliser des colonnes ou des tableaux temporaires.

Après cela, mis à part pour un exercice, je ne vois pas trop pourquoi on se refuserait d'utiliser Excel (Quand vous programmez en VBA pour Excel, pensez Excel avant de penser VBA... )

[Philippe Tulliez]

Bonjour Pierre,
Je programme toujours également en utilisant toutes les fonctionnalités natives d'excel que je traduis en VBA et pour ce cas précis, j'aurais privilégié l'usage d'une colonne avec formule booléenne et pour le copier/collage spécial-Valeur, j'utilise toujours ObjetRange.Value = ObjetRange.Value

[BENNASR]

Après cela, mis à part pour un exercice, je ne vois pas trop pourquoi on se refuserait d'utiliser Excel (Quand vous programmez en VBA pour Excel, pensez Excel avant de penser VBA... )

t'as totalement raison... je chercher à comprendre le fonctionnement des dictionnaires
merci Philippe Tulliez
merci Pierre Fauconnier
et déja j'ai inspirer mon dico de la proposition de boisgontierjacques " filtrer à l'inverse d'une liste"

[Pierre Fauconnier]

[...]je chercher à comprendre le fonctionnement des dictionnaires[...]

Un dictionnaire, c'est un tableau de paires clé/Valeur indexé sur les clés, qui sont uniques. Comme tu l'as vu, les clés peuvent être déterminées avec ou sans différenciation de casse (majuscule/minuscule). En programmation pure, c'est intéressant pour:

• créer des listes de clés uniques car le dico rejette l'ajout d'une clé existante. On peut (on doit) tester l'existence d'une clé avant de l'ajouter;
• rechercher plusieurs clés dans le tableau créé car la recherche étant indexée, elle est extrêmement rapide (une lecture de plus max par doublement des lignes => 1000 lignes, 10 lectures max, 1 million de lignes, 20 lectures max).

En utilisation de VBA pour Excel, j'ai des doutes sur la pertinence de l'utilisation de cet outil, car Excel permet de créer rapidement des tableaux temporaires qui font le même boulot (la fonction EQUIV permet une recherche très rapide sur des colonnes triées sur base du même principe, avec toutefois un doublement du nombre de lectures pour retrouver une clé exacte) et qui permette à l'excellien non programmeur de réaliser un travail identique. Le seul "avantage" que je pourrais y voir, c'est que Keys et Items renvoient toujours un tableau, même lorsqu'il n'y a qu'une seule valeur dans le dico, alors que Range.Value renvoie une valeur si Range est une cellule unique et un tableau de valeurs si Range est multi-cellules. C'est facilement contournable par fonction perso mai j'aurais apprécié que les concepteurs d'Excel nous permettent de ne pas se tracasser de cela.

En conclusion, c'est un outil intéressant pour le programmeur qui ne s'intéresse pas à Excel, mais pas du tout indispensable en VBA, pour Excel en tout cas.

Il faut aussi noter qu'il n'est pas nativement possible d'itérer sur le contenu d'un dico. On ne peut donc pas boucler sur un dico. Par contre, un dico expose deux tableaux (indice de base 0 quelle que soit l'option Option Compare du module), Keys et Items, sur lesquels on peut itérer.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub Test()
  Dim Dico As Scripting.Dictionary
  Dim i As Long
 
  Set Dico = New Dictionary
  Dico.Add "1", "test"
  Dico.Add "2", "Deuxième test"
  Dico.Add "3", "troisième test"
 
  For i = 0 To Dico.Count - 1
    Debug.Print Dico.Keys(i), Dico.Items(i)
  Next i
End Sub

Dans les faits, le dico est une surcouche de l'array VBA qui contient deux tableaux à deux colonnes: un avec les paires clé/valeur, et un, inaccessible à l'utilisateur du dico, avec les clés ordonnées et leur position. C'est le classement croissant des clés qui permet la recherche très rapide d'une clé dans un dico très fourni. Voici un schéma très imagé de comment fonctionne un dico. A chaque ajout de clé, le tableau d'index (à droite) est mis à jour par l'insertion de la clé au bon endroit.







Lorsque je parle de valeurs dans l'association clé/valeur, c'est au sens large puisque tu peux pousser dans un dico tout ce que tu veux: des valeurs "simples telles que du texte ou des valeurs numériques, mais également des objets (feuilles de calcul, classeurs, objets issus de classes perso, autres dicos, ...). On peut même pousser des objets au niveau des clés, mais dans 99.99999% des cas, les clés sont alphanumériques ou temporelles (dates, par exemple).


NB: J'ai déjà lu sur des forums qu'un dico était trié, mais il n'en est rien. Il est indexé pour accélérer la recherche, mais il ne possède pas d'outils de tri.