Microsoft 365 Rechercher des blocs de valeurs dans multiples feuilles du même classeur et les empiler

M92_ · 12 Octobre 2022

Bonjour le fil,

J'ai un problème que je n'arrive pas à résoudre. Ci-dessous, mes inputs/ouput :

# Input :
Il s'agit d'un nombre aléatoire de blocs qui sont éparpillés aléatoirement dans plusieurs feuilles.
Le seul point commun entre les différents blocs c'est le format : noms des réf (A, B, ..) et la taille.

# Output (souhaité) :

Je me demande s'il est possible d'effectuer cette transfo en Excel (VBA et/ou PowerQuery).
Si oui, pourriez-vous me donner un coup de main, s'il vous plaît ? En PJ, un exemple de fichier test.

Merci beaucoup,

Cdlt,
M92

vgendron · 12 Octobre 2022

Hello

Juste une piste
pour trouver les blocs: il suffirait de faire une recherche (Find next) sur la Ref A
et à chaque occurence, en déduire le tableau (currentregion), le nommer avec un nom type "Tab_xx"
une fois que tous les tableaux sont connus==> les compiler..
je ne connais que très peu PQ. je ne vais donc pas pouvoir t'aider plus

sylvanu · 12 Octobre 2022

Bonjour M92, Vgendron,
Ne connaissant pas la méthode de Vgendron pour délimiter les zones à analyser, j'ai fait plus "bourrin" avec :

VB:

Sub Worksheet_Activate()
    Dim LigneRefC%, LigneRefD%, DL%, i%, j%, Nmax%
    Nmax = 100  ' Nombre max de lignes et colonnes à analyser pour trouver la dernière
    LigneRefC = 2: LigneRefD = 2
    [A2:B1000].ClearContents: [A1:B1000].Borders.LineStyle = xlNone
    For Each F In Worksheets
        If F.Name <> "Synthèse" Then
            For L = 1 To Nmax    ' Dernière ligne
                DL = Application.Max(DL, Sheets(F.Name).Cells(65000, L).End(xlUp).Row)
            Next L
            For C = 1 To Nmax    ' Dernière colonne
                DC = Application.Max(DC, Sheets(F.Name).Cells(C, Columns.Count).End(xlToLeft).Column)
            Next C
            With Sheets(F.Name)
                T = .Range(.Cells(1, 1), .Cells(DL, DC))
            End With
            For i = 1 To UBound(T)
                For j = 1 To UBound(T, 2)
                    If T(i, j) = "REF C" Then
                        Cells(LigneRefC, "A") = T(i, j + 2)
                        LigneRefC = LigneRefC + 1
                    End If
                    If T(i, j) = "REF D" Then
                        Cells(LigneRefD, "B") = T(i, j + 2)
                        LigneRefD = LigneRefD + 1
                    End If
                Next j
            Next i
        End If
    Next F
    DL = Application.Max(Cells(65000, "A").End(xlUp).Row, Cells(65000, "B").End(xlUp).Row)
    Range("A1:B" & DL).Borders.Weight = xlThin
End Sub

J'ai limité la recherche des lignes et colonnes à 100, sinon modifier Nmax.
La macro est automatique lorsqu'on sélectionne la feuille Synthèse.
Mais par pitié, la prochaine fois, éviter les cellules fusionnées. 😱

vgendron · 12 Octobre 2022

Hello @sylvanu

je pense que ma méthode n'est pas moins bourrin que la tienne 🙂

Le code suivant suppose que les tableaux sont TOUJOURS sous la meme forme
avec REF A en première ligne
que les cellules sont fusionnées sur 2 puis 4 colonnes pour ne former qu'un tableau de 2 colonnes.. (Grrr.. la fusion...)
le code commence par transformer les tableaux en PLAGES Nommées
ensuite, les plages nommées deviennent des tableaux structurés.
pourquoi le passage par plage nommée?? parce que la création d'un tableau structuré ajoute une ligne d'entete.. qu'on la masque ou pas. du coup. les données sont décalées d'une ligne. et donc.. le firstadress.. bah.. il ne sert à rien. et la boucle tourne et tourne et tourne... j'en ai le mal de mer🙂

VB:

Sub GenererTab()
Dim c As Range
Dim firstAddress As String

Numtab = 1
For Each ws In ActiveWorkbook.Sheets
With ws
    
    With .Cells
        Set c = .Find("REF A", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                Set NewTab = c.CurrentRegion
    
                ActiveWorkbook.Names.Add Name:="Tab_" & Numtab, RefersTo:=NewTab
                Numtab = Numtab + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End With
Next ws

For i = 1 To Numtab - 1
    Application.Goto Reference:="Tab_" & i
    Set NewTab = Range("Tab_" & i)
    ActiveSheet.ListObjects.Add(xlSrcRange, NewTab, , xlNo).Name = "Tab_" & i
    ActiveSheet.ListObjects("Tab_" & i).TableStyle = "TableStyleMedium2"
    ActiveSheet.ListObjects("Tab_" & i).ShowHeaders = False
Next i
End Sub

sylvanu · 12 Octobre 2022

vgendron à dit:
je pense que ma méthode n'est pas moins bourrin que la tienne

C'est bien ce que j'ai dit ! 🙂

sylvanu à dit:
j'ai fait plus "bourrin"

😂😂😂

vgendron · 12 Octobre 2022

non non !! PAS moins bourrin. donc.. aussi bourrin que toi..🙂🙂
bref.. on bourrine tous les deux 😂😂
on va voir que dit @M92_ Le Temps (bourrin) nous dira .. Tadaaaaa !! 🥁🥁

M92_ · 12 Octobre 2022

@sylvanu, @vgendron,

Honnêtement, je ne sais comment vous remercier.
Vous m'avez fait gagné des heures de prod/travail manuel.

Juste une petite question: Admettons que REF C et REF D ne sont pas voisines, par exemple :

Est-ce que vos codes prendraient en considération cette configuration et seraient capables de gérer ce scénario ?

vgendron · 12 Octobre 2022

essaie..tu verras

vgendron · 12 Octobre 2022

autre contrainte avec mon code.. il faut que tous les tableaux soient séparés d'au moins une ligne et une colonne..==> pas de voisinage proche avec un autre tableau
en cause: c.currentregion==> le currentregion est un équivalent d'un Ctrl+A sur une cellule d'une zone de données

sylvanu · 12 Octobre 2022

Une version où on peut modifier ce qu'on recherche en Synthèse A1 et B1. La mise à jour est automatique.

M92_ · 12 Octobre 2022

vgendron à dit:
autre contrainte avec mon code.. il faut que tous les tableaux soient séparés d'au moins une ligne et une colonne..==> pas de voisinage proche avec un autre tableau
en cause: c.currentregion==> le currentregion est un équivalent d'un Ctrl+A sur une cellule d'une zone de données

Le fichier que je reçois par le client respecte la contrainte que tu décris. Les blocs sont séparés horizontalement par deux colonnes et verticalement par au moins quatre lignes.

M92_ · 12 Octobre 2022

sylvanu à dit:
Une version où on peut modifier ce qu'on recherche en Synthèse A1 et B1. La mise à jour est automatique.

Là c'est vraiment du Excel avancé. Alors, j'ai tenté de jouer avec l'emplacement des références dans chaque bloc, non seulement la mise à jour était instantanée mais les valeurs récupérées sont correctes aussi.

Merci beaucoup @sylvanu.

sylvanu · 12 Octobre 2022

M92_ à dit:
mais les valeurs récupérées sont correctes aussi.

Evidemment. J'avais testé. 😂
D'ailleurs on pourrait mettre plus de références en recherche, c'est guère plus complexe.

M92_ · 12 Octobre 2022

sylvanu à dit:
Evidemment. J'avais testé. 😂
D'ailleurs on pourrait mettre plus de références en recherche, c'est guère plus complexe.

Désolé 😂 j'étais pris par l'émotion. J'arrivai pas à croire que enfin j'aurai plus à faire de la mano (sachant qu'on parle parfois d'une centaines de blocs).

vgendron · 12 Octobre 2022

Juste parce que je suis content d'avoir fait un power query, je poste
1) bouton pour lancer la génération des tableaux structurés
2) clic droit sur le tableau de la feuille "Résultat" / actualiser

Effectuez une recherche sur Excel Downloads...

Microsoft 365 Rechercher des blocs de valeurs dans multiples feuilles du même classeur et les empiler

M92_

XLDnaute Junior

Pièces jointes

vgendron

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Pièces jointes

vgendron

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

M92_

XLDnaute Junior

vgendron

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Pièces jointes

M92_

XLDnaute Junior

M92_

XLDnaute Junior

sylvanu

XLDnaute Barbatruc

M92_

XLDnaute Junior

vgendron

XLDnaute Barbatruc

Pièces jointes

Nous accordons de l'importance à votre vie privée