DLTC :: Dégroupage lignes en colonnes

Bonjour,
cet utilitaire fait suite au post :https://excel-downloads.com/threads...de-donnees-en-lignes-via-powerquery.20080390/ où il y a eu différentes contributions de Mromain et de Cousinhub. Merci à eux.
La proposition de Mromain a été retenue et implémentée dans cet outil. La requête a été uniquement modifiée de manière à la rendre paramétrable en fonction du contexte.

L'interface d'accueil se présente comme suit :

Renseigner le fichier .csv à traiter. Ce nom de fichier est passé en paramètre dans la requête d'importation des données sources.
Par défaut l'outil propose des noms d'onglets qui vont accueillir les données sources et les différentes solutions. Ceux-ci sont modifiables. Ils sont directement reportés dans les onglets paramètres de calculs et Params.
Le critère de bloc : sa présence est vitale. C'est lui qui va identifier le nombre de blocs à traiter afin de dégrouper les lignes en colonnes. Un contrôle cohérence et de détection de ce critère est automatiquement effectué. Si le critère n'est pas identifié il ne sera pas possible de démarrer le dégroupage. En effet le Nbr de blocs sera à zéro, ce qui rendront impossibles les traitements (des contrôles sont présents dans le code des macros et formules)
Renseigner le séparateur des étiquettes et valeurs. Par défaut on considère ce format ETIQUETTE;VALEUR. Ce caractère est passé en paramètre dans la requête PowerQuery qui va importer les données sources
Par défaut l'outil considère que le fichier source comporte des titres de colonnes. Ce paramètre est aussi passé à la requête PowerQuery de Mromain afin de tenir compte de la ligne de renommage présente dans la requête. Voir le code.

Paramètres de calculs - Onglet [Paramètres de calculs]

J'invite ici les débutants à consulter les formules qui se trouvent dans cette zone. Figurent des astuces pour récupérer la lettre de la colonne ou calculer la dimension de la plage à traiter à partir de deux paramètres seulement : adresse source des données et le critère de bloc. Ces deux informations permettent de calculer les paramètres ci-dessous.
Je les invite également à "détourer" les différents noms de champs. On y montre l'utilisation de la fonction LAMBDA pour le dégroupage par formules. On y retrouve aussi la puissance de la fonction DECALER pour le plages nommées dynamiques et la fonction INDIRECT qui permet de construire des formules en mode dynamique via le passage de certains de ces paramètres ci-dessous.

Enfin en ce qui concerne le code VBA il reste standard aux utilitaires que j'ai publié. Il présente de bonnes choses pour les internautes du Forum qui souhaiteraient avancer plus loin en VBA. Idem pour PowerQuery, une manière "de mettre le pied à l'étrier".

A propos de la fonction LAMBDA et fonctions avancées

Office 365 - LAMBDA - LET - Avancé

Quelques exemples qui démontrent la puissance des fonctions LAMBDA et LET. Pour une grande majorité de celles présentes dans le fichier Excel joint nous aurions eu recourt à du VBA. LAMBDA accompagnée de la fonction LET nous évite cela. Nous pouvons créer des sous-fonctions qui font office de...

excel-downloads.com

Office 365 - LAMBDA - LET - SCAN - MAP - REDUCE - UNIQUE - TRIERPAR - TRIER - FILTRE - - INDIRECT - avancé

Cette livraison c'est la continuité des premières toujours sur la base du même fichier d'exemples déjà livré les semaines précédentes. Il est composé d'un onglet sommaire qui résume les fonctions développées. Je vous invite cependant à explorer tous les onglets ainsi que tous les champs et...

excel-downloads.com

FONCTIONS AVANCEES - LET - LAMBDA - suite et fin

Une dernière séries de fonction UDF via LAMBDA. Il est démontré ici que les fonctions les plus compliquées peuvent être traduites en "LAMBDA". Au passage un onglet avec les jeux de caractères ANSI/ASCII Subscript et Superscript et quelques astuces pour extraire des nombres, valeurs entre {}[]()...

excel-downloads.com

Traduction de ces paramètres dans un tableau (pour les macros et powerquery) - Onglet [Params]
Extrait - ils sont repris dans un tableau de paramètres pilotés par les Macro via une classe dédiée. Voir un de post à ce propos.

Onglet [Source données] - extrait
Les informations sont sans aucune valeur fonctionnelle

Source

Transformation

Quelque soit le mode de traitement

Voici le résultat

RQ_MROMAIN

PowerQuery:

let

    //=================================================================================================
    // Auteur : Oguruma2D
    // Forum  : https://excel-downloads.com/
    // Objet  : Adaptation du code de mromain (XLD) afin que la requête soit dynamique
    //=================================================================================================

    // ---------------------------------------------------------------------------
    // Paramètres pour gérer la requête en fonction du contexte des données source
    // ---------------------------------------------------------------------------
    TbSource=getParameters("TB_PARAMS", "PARAM_TB_SOURCE"),
    ColonneTitre=getParameters("TB_PARAMS", "PARAM_COLONNE_TITRE"),
    LabelEtiquette=getParameters("TB_PARAMS", "PARAM_TITRE_ETIQUETTE"),
    LabelValeur=getParameters("TB_PARAMS", "PARAM_TITRE_VALEUR"),
    LabelEtiquetteDefault=getParameters("TB_PARAMS", "PARAM_DEFAULT_COLUMN_1"),
    LabelValeurDefault=getParameters("TB_PARAMS", "PARAM_DEFAULT_COLUMN_2"),
    Etiquette=if ColonneTitre = "OUI" then LabelEtiquette else LabelEtiquetteDefault,
    Valeur=if ColonneTitre = "OUI" then LabelValeur else LabelValeurDefault,

    //=================================================================================================
    // Auteur : mromain
    // Forum  : https://excel-downloads.com/
    // Objet  : Reprise du code en l'état avec remplacement par les paramètres en début de requête
    // Params : TbSource, Etiquette, Valeur
    //=================================================================================================
    Source = Excel.CurrentWorkbook(){[Name=TbSource]}[Content],
    RenameColumns = Table.RenameColumns(Source,{{Etiquette, "Name"}, {Valeur, "Value"}}),
    SplitTable = Table.Split(RenameColumns, List.Count(List.Distinct(RenameColumns[Name]))),
    ConvertToRecord = List.Transform(SplitTable, Record.FromTable),
    ToTable = Table.FromRecords(ConvertToRecord)
in
    ToTable

Requête d'importation

PowerQuery:

let

    //=================================================================================================
    // Auteur : Oguruma2D
    // Forum  : https://excel-downloads.com/
    // Objet  : Ceci fait suite au post à propos du dégroupage de lignes en colonnes
    //=================================================================================================

    //-------------------------------------------------------------------------------------------------
    // On récupère les paramètres du fichier source à importr
    // puis si ce fichier source comporte des titres de colonnes
    //-------------------------------------------------------------------------------------------------
    FichierSource=getParameters("TB_PARAMS", "PARAM_FICHIER_SOURCE"),
    ColonneTitre=getParameters("TB_PARAMS", "PARAM_COLONNE_TITRE"),
    Sep=getParameters("TB_PARAMS", "PARAM_SEPARATEUR"),

    Source = Csv.Document(File.Contents(FichierSource),[Delimiter=Sep, Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    Promote = if ColonneTitre ="OUI" then Table.PromoteHeaders(Source, [PromoteAllScalars=true]) else Source
in
    Promote

Requête pour le passage de paramètres à PowerQuery : getParameter

PowerQuery:

(pTable as text, pName as text) =>
 let
        Source = Excel.CurrentWorkbook(){[Name=pTable]}[Content],
        RowsParams = Table.SelectRows(Source, each ([PARAMETRE] = pName)),
        value = RowsParams{0}[VALEUR]
in
        value

Ici le post concernant le passage de paramètres à une requête PowerQuery

Excel :: PowerQuery :: Passage de paramètres

Ce post fait suite au dépôt hier concernant la fonction RechercheX en langage .M Ici, c'est une mise en oeuvre avancée permettant de gérer et de passer des paramètres à PowerQuery plus d'autres méthodes pour y parvenir. Enfin quelques petites astuces via ces fonctionnalités pour récupérer des...

excel-downloads.com

A propos du mode dynamique
Comme expliqué dans le poste initial il est possible de faire ce dégroupage par formule. Ceci a été traduit dans une macro ayant pour but de poser les formules de dégroupage puis de les transformer en valeurs afin de ne pas encombrer la feuille de calculs surtout en cas de forte volumétrie.

Extrait du code

Code:

    '--------------------------------------
    ' Formule pour les titres des colonnes
    '--------------------------------------
    Range(sADRESSE_COLONNE_TITRE_DYNAMIC).Activate
    Range(sADRESSE_COLONNE_TITRE_DYNAMIC).Formula2R1C1 = _
        "=" & sFORMULE_TITRE_COLONNE
 
    '----------------------------------------------------------------------------------
    ' Formule pour le dégroupage - contraint de la mettre en dur car erreur de compile
    ' en mode variable comme les titres des colonnes
    '-----------------------------------------------------------------------------------
    Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE).Activate
    Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE).Formula2R1C1 = _
        "=myDEGROUPE(NBR_ETIQUETTES_PAR_GROUPE,NUM_LIGNE_CIBLE_DONNEES,NUM_COLONNE_CIBLE_ETIQUETTE,ADRESSE_DEBUT_VALEUR_DYNAMIQUE)"

    '-----------------------------------------
    ' Recopie de la formule sur la 1ère ligne
    '-----------------------------------------
    Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE).Copy
    Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE & ":" & sADRESSE_DEGROUPAGE_FIN_COLONNE).PasteSpecial _
        Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    '--------------------------------------------
    ' Recopie des formules sur toute la plage
    '--------------------------------------------
    Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE & ":" & sADRESSE_DEGROUPAGE_FIN_COLONNE).Copy
    Range(Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE & ":" & sADRESSE_DEGROUPAGE_FIN_COLONNE), sADRESSE_DEGROUPAGE_FIN_LIGNE).PasteSpecial _
        Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
 
    '---------------------------
    ' Activation des calculs
    '---------------------------
    Calculate

    '--------------------------------------
    ' Transforme les formules en valeurs
    '--------------------------------------
    Range(Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE & ":" & sADRESSE_DEGROUPAGE_FIN_COLONNE), sADRESSE_DEGROUPAGE_FIN_LIGNE).Copy
    Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE).PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

A propos du mode Macro
Deux versions :
1. Exploration et traitement des données directement sur les feuilles source et cible
2. Exploration et traitement des données via des tableaux en mémoire. Les données sources sont montées et le résultat de la ventilation est aussi déposé dans un tableau en mémoire. A l'issue le tableau en mémoire résultant est déposé sur la feuille

Extrait du code version 1

VB:

    '------------------------------------------------------------
    ' Parsing des données dans l'onglet source ligne/colonne
    '------------------------------------------------------------
    For lngIndexRow = LngIndexStartRow To lngMaxLoopRow
        ' On détermine les limites du parsing en colonne
        lngMaxLoopCol = LngIndexStartColumn + iNbItem - 1
        ' dispatching en colonne
        For lngIndexColumn = LngIndexStartColumn To lngMaxLoopCol
            ' on se déplace dans le tableau
            lngParse = lngParse + 1
            ' On va se décaler d'une cellule pour récupérer la valeur de l'item et affectation
            wkMacro.Cells(lngIndexRow, lngIndexColumn).Value = DataSource.Cells(lngParse, 1).Offset(0, 1)
        Next lngIndexColumn
        ' et traitement du bloc suivant
    Next lngIndexRow

Extrait du code version 2

Code:

'-------------------------------
    ' Init du parsing des données
    '------------------------------
    lngParse = 0
    lngMaxLoopRow = (LngIndexStartRow + (DataSource.Rows.Count / iNbItem))
 
    '-------------------------------------------------------------------------
    '               Préparation du travail en mémoire
    '-------------------------------------------------------------------------
    ' Initialisation des tableaux de récupération des étiquettes et valeurs
    ' Ici via cette méthode le degroupage est effectué dans un tableau
    ' A l'issue ce tableau sera déposé sur la cellule destination
    '-------------------------------------------------------------------------
 
    '-------------------------------------------------------------------------
    ' Nbre de lignes max à traiter
    '-------------------------------------------------------------------------
    lngEndRow = wkSource.Range(sADRESSE_DEBUT_ETIQUETTE).End(xlDown).Row
 
    '---------------------------------------------------------------------------
    ' On initialise le tableau qui va recevoir les données en mémoire
    '---------------------------------------------------------------------------
    ReDim vData(1 To lngMaxLoopRow - 1, 1 To CInt(sNBR_ETIQUETTES_PAR_GROUPE))
 
    '-----------------------------------------------------------------------------
    ' Ce tableau contient les valeurs à traiter
    '-----------------------------------------------------------------------------
    vValues = wkSource.Range(sADRESSE_DEBUT_ETIQUETTE).Resize(lngEndRow, 2).Value
 
    '----------------------------------------------------------------------------
    ' Parsing du tableau qui contient les étiquettes à dégrouper
    '----------------------------------------------------------------------------
    For lngIndexRow = LngIndexStartRow To lngMaxLoopRow - 1
        ' On détermine les limites du parsing
        lngMaxLoopCol = LngIndexStartColumn + iNbItem - 1
        ' dispatching en colonne
        For lngIndexColumn = LngIndexStartColumn To lngMaxLoopCol
            ' on se déplace dans le tableau
            lngParse = lngParse + 1
            '-----------------------------------------------------------------------------------
            ' On va se décaler d'une cellule pour récupérer la valeur de l'item et affectation
            ' wkMacro.Cells(lngIndexRow, lngIndexColumn).Value = DataSource.Cells(lngParse, 1).Offset(0, 1)
            ' on balaye les données en mémoire via les tableaux initialisés au préalable
            '-----------------------------------------------------------------------------------
            vData(lngIndexRow - 1, lngIndexColumn) = vValues(lngParse, 2)
        Next lngIndexColumn
        ' et traitement du bloc suivant
    Next lngIndexRow
 
    '------------------------------------------------------------------------------
    ' On dépose le tableau résultat à la cellule cible de l'onglet Macro
    ' On taille la plage d'arrivée en fonction de la taille du tableau de données
    '------------------------------------------------------------------------------
    Set rOutputData = wkMacro.Range(sADRESSE_DEGROUPAGE_DEBUT_COLONNE).Resize(UBound(vData, 1), UBound(vData, 2))
 
    '----------------------
    ' dépot sur la feuille
    '----------------------
    rOutputData = vData

Auteur: Oguruma

Version: 1.0

Effectuez une recherche sur Excel Downloads...

DLTC :: Dégroupage lignes en colonnes 1.0

Office 365 - LAMBDA - LET - Avancé

Office 365 - LAMBDA - LET - SCAN - MAP - REDUCE - UNIQUE - TRIERPAR - TRIER - FILTRE - - INDIRECT - avancé

FONCTIONS AVANCEES - LET - LAMBDA - suite et fin

Excel :: PowerQuery :: Passage de paramètres

Plus de ressources de oguruma

Partager cette ressource