Transformation de données d'arbre à tableau complet Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 8'685
Appréciations reçues : 292
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 26 janvier 2016, 01:07

Bon, ben le mien encore en retard ;-)
Ca va sans dire mais c'est mieux en le disant : la plage des cat doit être entourée de cellules vides.
Finalement ça l'a simplifié ce nombre de colonnes variables.
eric
Transformation.xlsm
(23.47 Kio) Téléchargé 17 fois
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'203
Appréciations reçues : 445
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 26 janvier 2016, 01:39

Re,

J'ai en effet l'habitude de zapper les commentaires ! Si l'on veut expliquer, ça prend souvent plus de temps que l'écriture du code, et les commentaires repères, sauf exceptions ne me suffisent jamais pour m'y retrouver rapidement dans un code que j'ai produit auparavant, donc j'en mets rarement pour moi.
Pour les variables, plus le nom est court, moins cela prend de temps à écrire... Je n'utilise donc pas la "méthode pro" de préfixage des noms pour reconnaître d'emblée le type de variable, etc. Je compense par des habitudes de nommage qui me permettent de m'y retrouver sans recherche particulière.

Pour déroger, j'espère que les commentaires suivants te seront utiles pour suivre le cheminement de la macro...
Si des commandes que tu penses ne pas interpréter exactement, n'hésite pas à demander...
Sub Transformation()
    'variables: n et col = dimensionnement lignes et colonnes utilisées (integer)
    ' i et k = var. compteurs (integer) ou à utilisation temporaire
    ' h variable (variant) destinée à recueillir numéros lignes à supprimer
    Dim n%, i%, k%, col%, h
    With ActiveSheet
    'Dimensionnement lignes et colonnes
    ' on utilise la fonction Excel NBVAL pour savoir si la col. suivante contient des valeur
    ' si oui, on incrémente col et on recherche dernière ligne utilisée dans la colonne
    ' on l'affecte à n si elle supérieure à la même valeur pour les col? précédentes
    ' si non, le dimensionnement est terminé, on sort de la boucle Do... Loop
        Do
            If Application.CountA(.Columns(col + 1)) > 0 Then
                col = col + 1
                k = .Cells(.Rows.Count, col).End(xlUp).Row
                If k > n Then n = k
            Else
                Exit Do
            End If
        Loop
        ' Si le tableau était vide on interrompt [NB: col aurait également la valeur 0]
        If n = 0 Then Exit Sub
    'Ajout des valeurs manquantes
        'Dans chaque colonne, de l'avant-dernière à la première (boucle)
        For k = col - 1 To 1 Step -1
            ' on parcourt toutes les lignes (boucle imbriquée)
            For i = 1 To n
                'Si la cellule est vide
                If .Cells(i, k) = "" Then
                    'alors que celle de la colonne suivante ne l'est pas
                    If .Cells(i, k + 1) <> "" Then
                        'on lui affecte la valeur de la ligne précédente dans la même colonne
                        .Cells(i, k) = .Cells(i - 1, k)
                        'Si dans cette ligne précédente la cellule de la colonne suivante était vide
                        ' on enregistre son numéro de ligne pour suppression ultérieure
                        ' en le concaténant dans h, suivi d'un séparateur
                        If .Cells(i - 1, k + 1) = "" Then h = h & i - 1 & ";"
                    End If
                End If
            Next i
        Next k
    'Traitement des lignes à supprimer
        'On transforme la suite des numéros de lignes à supprimer (dans h) en tableau
        ' la chaîne enregistrée dans h contenant un séparateur final, le tableau contiendra
        ' un élément final de plus (vide) que le nombre de lignes à supprimer
        h = Split(h, ";")
        'On trie ce tableau pour mettre les numéros de lignes dans l'ordre croissant
        ' (le tableau étant un tableau de chaînes, la comparaison se fait sur les valeurs
        ' converties en nombres), l'élément supplémentaire du tableau sert aux substitutions
        For i = LBound(h) To UBound(h) - 2
            For k = i + 1 To UBound(h) - 1
                If CInt(h(k)) < CInt(h(i)) Then
                    h(UBound(h)) = h(k)
                    h(k) = h(i)
                    h(i) = h(UBound(h))
                End If
            Next k
        Next i
        'On parcourt le tableau trié de l'avant-dernier élément (le dernier n'étant pas un
        ' numéro de ligne à supprimer) au premier, en supprimant les lignes correspondantes
        For i = UBound(h) - 1 To LBound(h) Step -1
            .Rows(h(i)).Delete
        Next i
    End With
End Sub
Cordialement.
U
UnProblemeAResoudre
Jeune membre
Jeune membre
Messages : 19
Inscrit le : 25 janvier 2016
Version d'Excel : 2010

Message par UnProblemeAResoudre » 26 janvier 2016, 07:30

Bonjour MFerrand,

Merci BEAUCOUP c'est très bien commenté!

Je m'étais mis des debugs prints pour le comprendre et je viens de voir ton message.

En language naturel l'algo est de la sorte:

On commence par l'avant derniere colonne jusqu'à la première
Pour chaque ligne,
Si la case est vide et que celle d'a coté ne l'est pas,
Je remplis la case précédente
Si dans la cellule précédente de la colonne suivante la case était vide je stocke le numero de la ligne.

Je trie mon tableau et vire les ligne stockées.

Très malin!

Je me galérais en essayant de partir de la première colonne et en disant:
Tant que je ne suis pas arrivé à la dernière ligne, si la cellule d'en bas et vide et celle d'en bas à gauche je ne l'ai pas je construir cellule & "," & cellule en bas à droite, et en faisant des goto avec des select cases.. vraiment sâle.

Je l'ai adapté à mon traitement qui ne commence pas en ligne 1 ni col 1 et dont je ne peux pas toucher la structure de la feuille en copiant les données de départ dans un onglet tampon et en balayant depuis le tampon avant de virer l'onglet après avoir remonté en tableaux.

Dans une démarche d'amélioration je vais essayer de le faire directement depuis des tableaux!

@Eric, maintenant le code marche parfaitement, il est très optimisé et très imprésionnant, je vais le passer au crible pour le comprendre! Merci mille fois!

Merci infiniment.
E
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 8'685
Appréciations reçues : 292
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 26 janvier 2016, 12:04

Bonjour,

un peu le principe que tu as décris.
Sur chaque ligne, par colonne, je sauvegarde les cat et ss-cat en cours. Puis je regarde en-dessous et en-dessous à gauche. Si une est remplie c'est qu'il y une nouvelle ligne à créer.
eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message