XL 2016 VBA Transposer avec conditions

arake · 26 Mai 2021

Bonjour à tous,

Pour commencer, un grand merci pour votre aide car je débute à peine dans le vba

Alors voilà, j'ai un extract de questionnaire que je cherche à structurer différemment pour en faire une base de données "clean".
Pour chaque ID (de 1 à 9), je voudrais que les groupes de colonnes encadrées en gras (dans l'onglet "ref") soient transposés en lignes, si ces colonnes sont renseignées.

Exemple:
"ID 4" a acheté la marque de bière NUMBER ONE en 3 formats différents, donc 3 groupes de colonnes : G à L , M à R, S à X .
Comment faire pour que ces 3 groupes soient présentés sur 3 lignes différentes plutôt que sur 1 seule ligne ?
Dans l'onglet "transposer", j'ai fait la manip manuellement pour vous donner un exemple.

Encore un grand merci.

arake · 5 Juin 2021

Merci beaucoup pour votre réponse Roblochon! C'est super.

J'avais posé la question à Chris car il avait mentionné dans sa réponse qu'il arrivait au même résultat que vous mais en bien plus d'étapes.
Étant donné que je ne comprends pas comment sont conçues les fonctions, je voulais lui demander si dans son modèle on pouvait voir toutes les étapes appliquées sans utilisation de fonction. Ainsi, vu mon très faible niveau de connaissances en PQ, je peux voir et analyser étape par étape le cheminement jusqu'au résultat.

J'ai bien entendu essayé de "décortiquer" votre fonction fnLigne, et si j'ai bien compris le principe, elle consiste en une succession de requêtes qui évite les répétitions d'étapes.
Je comprends que la 1ère ligne de formule sert à ajouter une colonne nommée "data". Et ensuite je suis perdu...

En tout cas, votre modèle est remarquable, et je vous en remercie!

Hasco · 5 Juin 2021

Re,

Dans la requête tableau 1 à l'étape 'Lignes groupées' les lignes sont regroupées par 'ID' donnant des sous-tables (ex pour ID = 2) :

Ces tables doivent à leur tour être groupées sur Type ligne, et chaque groupe résultant indexé afin que l'opération de pivotage final sur 'Type ligne' sache quoi faire des lignes trouvées.

// "LaTable" est le nom du paramètre de type table a passer à la fonction
(LaTable as table)=>
let
// Grouper sur la colonne "Type ligne" chaque group contiendra une colonne nommée "données" qui contiendra une table avec en colonnes 'ID', 'Valeur' et 'Type ligne'
Source = Table.Group(LaTable, {"Type ligne"}, {{"données", each _, type table [ID=nullable number, Valeur=text, Type ligne=text]}}),

// Ajouter dans la table une colonne d'index, la nommer datas (ce pourrait être tout autre nom)
#"Personnalisée ajoutée" = Table.AddColumn(Source, "datas", each Table.AddIndexColumn([données],"Index",1,1)),

// Supprimer la colonne Données, devenue inutile
#"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée",{"données"}),

// développer les colonnes de 'datas' qui nous interressent
#"datas développé" = Table.ExpandTableColumn(#"Colonnes supprimées", "datas", {"ID", "Valeur", "Index"}, {"ID", "Valeur", "Index"}),

// Pivoter la colonne 'Type ligne'
#"Colonne dynamique" = Table.Pivot(#"datas développé", List.Distinct(#"datas développé"[#"Type ligne"]), "Type ligne", "Valeur"),

// Supprimer la colonne d'index qui ne sert plus
#"Colonnes supprimées1" = Table.RemoveColumns(#"Colonne dynamique",{"Index"})
in
#"Colonnes supprimées1"

Cordialement

arake · 7 Juin 2021

Bien reçu Roblochon, merci!

Je suis encore en train d'étudier votre fonction, et en effet c'est un gain de temps considérable!

Dans les tests que j'ai fait, j'ai ajouté un ID avec plusieurs combinaisons de CATÉGORIE et MARQUES, et pour l'ID 10 j'ai remarqué que certaines catégories ne correspondent pas aux marques.
Ex : pour l'ID 10 (surligné en orange dans le xls, onglet "Résultats"), il y a une marque de bière alors que c'est la catégorie "Colas".

Je ne trouve pas dans les requêtes où se situe ce décalage.
Est-ce que vous pouvez m'aider svp?
Merci encore

Hasco · 7 Juin 2021

Bonjour,

J'essaierai de voir ça dans la journée, même si mon compte est connecté à xld pratiquement toute la journée, je ne suis pas forcément devant l'ordinateur.

Cordialement

Hasco · 7 Juin 2021

Re,

Je viens de regarder rapidement, mais dans votre exemple concernant l'id 10, mais en F11 de la source (feuil 'ref') il y a bien 'BIERE' pour la marque 'MANTA CITRON' .

Si ce n'est pas le résultat souhaité, essayez d'être très précis dans votre demande.

Cordialement

arake · 7 Juin 2021

Roblochon à dit:
Re,

Je viens de regarder rapidement, mais dans votre exemple concernant l'id 10, mais en F11 de la source (feuil 'ref') il y a bien 'BIERE' pour la marque 'MANTA CITRON' .

Si ce n'est pas le résultat souhaité, essayez d'être très précis dans votre demande.

Cordialement

Oui Roblochon, cette correspondance CATEGORIE / MARQUE est correcte. Et ce que vous avez réussi à faire est exactement ce que je cherchais à faire.

Ce sont les autres catégories de l'ID 10 qui ne correspondent pas. Dans l'onglet "Résultats", pour l'ID10, c'est à partir de la ligne 24 à 26 (surlignées en orange) que les catégories ne correspondent pas aux marques

En B24, la catégorie devrait être "BIERE" (pour la NUMBER ONE ZEST) et non "COLAS".
Idem pour les 2 lignes suivantes (en B25 et B26), la catégorie devrait être "COLAS" et non "THES GLACES".

On dirait qu'il y a un décalage d'une ligne.
Je ne comprends pas par contre pourquoi tous les autres ID sont corrects.

Bien cordialement

chris · 7 Juin 2021

Bonjour à tous

J'ai du ajouter une indexation et modifier en conséquence la fonction pour traiter ces cas

arake · 7 Juin 2021

chris à dit:
Bonjour à tous

J'ai du ajouter une indexation et modifier en conséquence la fonction pour traiter ces cas

Roblochon, Chris,
Un grand merci!

Hasco · 8 Juin 2021

Bonjour @arake, @chris , le fil,

J'arrive après la bataille, mais voici une autre version légèrement différente.

Cordialement

XL 2016 VBA Transposer avec conditions

arake

XLDnaute Nouveau

Pièces jointes

arake

XLDnaute Nouveau

Hasco

XLDnaute Barbatruc

arake

XLDnaute Nouveau

Pièces jointes

Hasco

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

arake

XLDnaute Nouveau

chris

XLDnaute Barbatruc

Pièces jointes

arake

XLDnaute Nouveau

Hasco

XLDnaute Barbatruc

Pièces jointes

Discussions similaires