Récupérer la liste des valeurs présentes dans plusieurs plages de données

[Auzingueur]

Bonjour à tous,

Je viens sur ce forum en espérant trouver une réponse à mon problèmes.

J'ai plusieurs plages de données et j'aimerai lister l'ensemble des chiffres présents dans toutes les plages (j'ai osé parler d'intersection sur un autre forum et je me suis fait remballer, je préfère ne plus m'y risquer).

J'aimerai faire cela sans utiliser les maccros, car les valeurs des plages seront amenées à changer très fréquemment, et je voudrais limiter le temps de calcul (j'ai déjà une maccro assez lourde qui tourne sur le on_Change de la feuille en question sur mon vrai fichier).

De plus, certaines plages n'existent pas tout le temps (comme la plage 4 dans mon fichier joint par exemple). J'aimerai qu'elles ne soient comptabilisées que lorsqu'elles existent.
=> Piste de solution pour ce problème : il est envisageable d'avoir une "plage de secours" (voir fichier joint) qui comportera toutes les valeurs possibles sur la feuille en question. Cela permettrait de remplacer ci-nécessaire la plage manquante par cette plage et de palier au problème.

Pour résumer, dans mon fichier exemple ci-joint, j'aimerai (si possible sans utiliser de maccro) réussir à récupérer dans mes cases rouges les valeurs 1, 5 et 6. Ceci en prennant bien en compte les plages 1, 2, 3 et 4.

ps: Ce n'est pas toujours la plage 4 qui risque d'être manquante, il arrive qu'il n'y en ai aucune manquante, ou il peut manquer n'importe laquelle.

Voilà, j'espère que mon problème est correctement exprimé, et que vous saurez m'aider.

Par avance merci,
Auzingueur


EDIT : En espérant que personne n'est en train de télécharger l'ancienne version de mon fichier, veuillez trouver ci-joint une nouvelle version, avec une formule (colonneH) qui donne le résultat recherché. Reste maintenant à trouver comment prendre en compte le fait que les plages peuvent être nulles.

 

[vgendron]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Bonjour

une piste. en H4
=SI(ET(SI(NB(plage1)<>0;ESTNUM(EQUIV(B4;plage1;0));"vrai");SI(NB(plage2)<>0;ESTNUM(EQUIV(B4;plage2;0));"vrai");SI(NB(plage3)<>0;ESTNUM(EQUIV(B4;plage3;0));"Vrai");SI(NB(plage4)<>0;ESTNUM(EQUIV(B4;plage4;0));"Vrai"));B4;"")

le truc, c'est que ca te donne bien les valeurs attendues. mais pas regroupées. mais c'est déjà un début ;-)

 

[Auzingueur]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Merci pour ta proposition.

Ta solution est sympa dans cet exemple, mais l'ennuie c'est que ce fichier test est très simplifié et dans mon vrai fichier j'aurai des nombres à 6 ou 8 chiffres, et même des chaines de caractère des fois. Du coup je ne pense pas que cela fonctionne dans le cas de mon vrai fichier.

Mais merci et je vais peut-être m'appuyer sur ta proposition pour la suite !

 

[klin89]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Bonjour Auzingueur, vgendron, le forum

Par formules, je ne sais pas faire

Option Explicit

Sub test()
Dim a, e, x, n As Long
    With Sheets("Feuil1").Range("b3").CurrentRegion
        a = .Value
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For Each e In a
                If e <> "" And IsNumeric(e) Then .Item(e) = Empty
            Next
            x = Application.Transpose(.keys)
            n = .Count
        End With
        With .Offset(, .Columns.Count + 2).Resize(n, 1)
            .CurrentRegion.ClearContents
            .Value = x
        End With
    End With
End Sub

klin89

 

[JHA]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Bonjour à tous,

Une solution par un tri en ligne 2 pour remettre les plages au bon endroit.

j'ai mis la manip sur un bouton mais tu peux le faire sans macro.

JHA

 

[Auzingueur]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Bonjour à tous,

Avant tout merci pour vos réponses klin89 et JHA, mais cela ne colle pas exactement avec ce que je veux. Je m'explique :

- klin89 : Tu me récupère ici toutes les valeurs qui apparaissent au moins 1 fois dans une des plages hors je cherche ici à récupérer les valeurs qui apparaissent au moins 1 fois dans toutes les plages. Grosso modo je cherche à récupérer dans cet exemple les valeurs 1, 5 et 6.

- JHA : J'ai du mal a exécuter ta maccro, et notamment je n'ai pas réussi à prendre en compte la plage 4 lorsqu'elle n'est pas vide. Cela vient certainement de moi, mais je n'arrive malheureusement pas à la faire fonctionner comme je le souhaite :/

Je suis donc toujours à la recherche d'une solution, je ne pense pas qu'on en soit loin avec le dernier fichier que j'ai posté, mais je bloque sur la prise en compte (ou non) des plages vides...

 

[Auzingueur]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Me revoici, toujours avec mon problème de plages.

J'ai cependant avancé : j'ai décidé de vérifié a l'aide d'une maccro si mes plages existent ou non. En fonction de celles qui existent je modifie ma formule dans une variable, avant de l'écrire dans la case souhaitée.

Cela marche, et la formule est bonne. Cependant, il faut que ma formule soit validée en matriciel (avec les accolades). J'ai trouvé la fonction Range.FormulaArray qui semble fonctionner lorsque je la test avec des formules simples tels qu'un SUM. Je n'arrive cependant pas a la faire fonctionner avec ma variable formule.

Vous trouvez ci-joint le fichier dans son état actuel. J'ai 2 variables (formule et formuleTemp) dans lesquelles on trouve deux fois la même formule, une fois en français et une fois en anglais (pour mes tests).

Si je pouvais avoir un dernier coup de pousse sur ce problème je vous en serai reconnaissant, surtout que je touche au but !!

Par avance merci

 

[Auzingueur]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Pour information : je n'ai toujours pas réussi à régler mon problème, mais j'ai pu avancer : il faut que la formule dans mon FormulaArray soit écrite en anglais et en style L1C1.

Je préfère préciser au cas ou quelqu'un s’arrêterait sur le topic.. Je ne suis vraiment pas loin du but, mais je n'arrive actuellement toujours pas à terminer :/

 

[job75]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Bonjour Auzingueur, vgendron, klin89, JHA,

Il faut que d'une manière ou d'une autre les plages à étudier soient bien repérées.

Avec cette macro toutes les plages colorées sont étudiées :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cible As Range, P As Range, d As Object, d1 As Object, c As Range, a, b, i&, j&
Set cible = [G3] 'à adapter
If Intersect(Target, cible) Is Nothing Then Exit Sub
Cancel = True
Application.ScreenUpdating = False
cible(2).Resize(Rows.Count - cible.Row).ClearContents 'RAZ
Set P = Me.UsedRange
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
'---liste des couleurs---
For Each c In P
  If c.Interior.ColorIndex <> xlNone Then d(c.Interior.Color) = ""
Next
If d.Count = 0 Then Exit Sub
'---analyse de chaque couleur et des valeurs---
a = d.keys: d.RemoveAll
For i = 0 To UBound(a)
  d1.RemoveAll
  For Each c In P
    If c.Interior.Color = a(i) Then _
      If i Then d1(c.Value) = "" Else d(c.Value) = ""
  Next c
  If d.Count = 0 Then Exit Sub
  If i Then
    If d1.Count = 0 Then Exit Sub
    b = d.keys
    For j = 0 To UBound(b)
      If Not d1.exists(b(j)) Then d.Remove b(j)
    Next j
  End If
Next i
If d.Count = 0 Then Exit Sub
'--- restitution---
cible(2).Resize(d.Count) = Application.Transpose(d.keys)
End Sub

Fichier joint.

A+

 

[Auzingueur]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Merci pour ton code, je testerai ca lundi, mais pour te répondre, les plages sont repérées puisqu'elles ont un nom fixe

 

[job75]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Re,

Nommer les plages n'est pas forcément la meilleure méthode puisque vous pouvez les supprimer.

Pour terminer cette macro est plus rapide car elle mémorise les couleurs dans le tableau coul :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cible As Range, P As Range, t, nlig&, ncol%, coul()
Dim d As Object, d1 As Object, i&, j%, x&, a, k&, b
Set cible = [G3] 'à adapter
If Intersect(Target, cible) Is Nothing Then Exit Sub
Cancel = True
Application.ScreenUpdating = False
cible(2).Resize(Rows.Count - cible.Row).ClearContents 'RAZ
Set P = Me.UsedRange
If P.Count = 1 Then If P.Interior.ColorIndex <> xlNone Then cible(2) = P: Exit Sub
t = P 'matrice
nlig = P.Rows.Count: ncol = P.Columns.Count
ReDim coul(1 To nlig, 1 To ncol)
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
'---liste des couleurs et mémorisation---
For i = 1 To nlig
  For j = 1 To ncol
    If P(i, j).Interior.ColorIndex <> xlNone Then
      x = P(i, j).Interior.Color
      d(x) = ""
      coul(i, j) = x
    End If
Next j, i
If d.Count = 0 Then Exit Sub
'---analyse de chaque couleur et des valeurs---
a = d.keys: d.RemoveAll
For k = 0 To UBound(a)
  d1.RemoveAll
  For i = 1 To nlig
    For j = 1 To ncol
      If coul(i, j) <> "" Then _
        If coul(i, j) = a(k) Then _
          If k Then d1(t(i, j)) = "" Else d(t(i, j)) = ""
  Next j, i
  If d.Count = 0 Then Exit Sub
  If k Then
    If d1.Count = 0 Then Exit Sub
    b = d.keys
    For i = 0 To UBound(b)
      If Not d1.exists(b(i)) Then d.Remove b(i)
    Next i
  End If
Next k
If d.Count = 0 Then Exit Sub
'--- restitution---
cible(2).Resize(d.Count) = Application.Transpose(d.keys)
End Sub

Fichier (2).

Bonne fin de soirée.

 

[job75]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Re,

J'ai recopié la plage B4: D9 vers le bas jusqu'à la ligne 60003.

Durées d'exécution sur Win 10 - Excel 2013 :

- fichier (1) du post #9 => 27 secondes

- fichier (2) du post #11 => 8 secondes.

A+

 

[job75]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Bonjour Auzingueur, le fil,

Si les plages à étudier sont rectangulaires voici une solution très simple et très rapide.

Elle consiste à les sélectionner en faisant une sélection multiple, touche Ctrl enfoncée.

La macro dans le code de la feuille :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Areas.Count = 1 Then Exit Sub
Dim cible As Range, d As Object, d1 As Object, i&, c As Range, a, j&
Set cible = [G4] '1ère cellule des résultats, à adapter
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
cible.Resize(Rows.Count - cible.Row + 1).ClearContents 'RAZ
For i = 1 To Target.Areas.Count
  d1.RemoveAll
  For Each c In Target.Areas(i)
    If i = 1 Then d(c.Value) = "" Else d1(c.Value) = ""
  Next c
  If d.Count = 0 Then Exit Sub
  If i > 1 Then
    If d1.Count = 0 Then Exit Sub
    a = d.keys
    For j = 0 To UBound(a)
      If Not d1.exists(a(j)) Then d.Remove a(j)
    Next j
  End If
Next i
If d.Count = 0 Then Exit Sub
'--- restitution---
With cible.Resize(d.Count)
  .Value = Application.Transpose(d.keys)
  If d.Count > 1 Then .Sort cible, xlAscending, Header:=xlNo 'tri facultatif
End With
End Sub

Pour sélectionner de grandes plages utiliser la commande Atteindre (touche F5).

Fichier (3).

Sur la sélection B4:B60003 + C4:C60003 + D4: D60003 la macro s'exécute en 0,7 seconde.

Edit : pour info, si vous voulez effacer les résultats, touche Ctrl et cliquez sur une cellule vide.

A+

 

[job75]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Bonjour Auzingueur, le fil,

Maintenant si vous voulez utiliser des plages nommées il suffit d'inscrire leur nom tel quel dans la feuille.

Pour leur recherche il n'est pas nécessaire que la casse soit respectée :

Private Sub CommandButton1_Click()
Dim cible As Range, d As Object, d1 As Object, nom As Name, c As Range, n&, a, i&
Set cible = [H4] '1ère cellule des résultats, à adapter
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
cible.Resize(Rows.Count - cible.Row + 1).ClearContents 'RAZ
For Each nom In ThisWorkbook.Names
  If Not Cells.Find(nom.Name, , xlValues, xlWhole) Is Nothing _
    And TypeName(Evaluate(nom.Name)) = "Range" Then
    d1.RemoveAll
    For Each c In Evaluate(nom.Name)
      If n Then d1(c.Value) = "" Else d(c.Value) = ""
    Next c
    If d.Count = 0 Then Exit Sub
    If n Then
      If d1.Count = 0 Then Exit Sub
      a = d.keys
      For i = 0 To UBound(a)
        If Not d1.exists(a(i)) Then d.Remove a(i)
      Next i
    End If
    n = n + 1
  End If
Next nom
If d.Count = 0 Then Exit Sub
'--- restitution---
With cible.Resize(d.Count)
  .Value = Application.Transpose(d.keys)
  If d.Count > 1 Then .Sort cible, xlAscending, Header:=xlNo 'tri facultatif
End With
End Sub

Fichier (4).

A+

 

[Auzingueur]

Re : Récupérer la liste des valeurs présentes dans plusieurs plages de données

Merci à tous pour vos réponses. Avec quelques petites modifications sur la boucle notamment, cela correspond à ma demande.

J'ai cependant un problème avec la coloration des cellules, sachant qu'à terme, la personne utilisant le fichier n'aura pas accès à cette feuille excel.

J'ai donc voulu résoudre ce problème avec la mise en forme conditionnelle, en colorant simplement les cellules contenant des données. Excel ne compte cependant pas la case comme "colorée" lorsqu'il s'agit d'une mise en forme conditionnelle.

J'ai donc toujours ce problème de gérer les plages vides :/