Transposer des données avec PowerQuery... ou VBA ?
Bonjour ! Après quelques recherches, je n’ai pas trouvé de solution
A partir d’une colonne contenant des pays qui peuvent apparaître à plusieurs reprises (que l’on peut indexer par un numéro d’ordre si ça peut aider).
Dans l’exemple en bleu, j’ai simplifié à une seule colonne mais il existe plusieurs colonnes non utilisées dans le tableau initial.
J’aimerais construire avec PowerQuery un autre tableau qui reprend en ligne de titre la liste des pays classés par ordre alphabétique, puis en dessous de chaque pays les numéros d’ordre ou numéro de ligne du tableau (ou numéro de ligne de la feuille -1) lorsque le pays apparaît dans la liste principale.
Pas sûr que ce soit réalisable avec PowerQuery car le fait de supprimer les cellules vides, à l’intérieur de chaque colonne n’est pas tout à fait dans l’esprit « ligne complète » d’une base de données ; peut-être qu’il serait préférable de faire du VBA ?! Ou une formule matricielle ?! Un peu perdu sur ce coup là…
Merci à celles et ceux qui pourront me guider un peu ! Ci-joint le fichier...
Bonjour à tous !
Et....
Une autre approche Power Query ?
Ouaoouuuuhhh ! Vous êtes au top !
Je ne m'attendais pas à une réponse aussi rapide. Je vais plancher là-dessus, ce sera l'occasion pour moi d'en apprendre un peu sur PowerQuery grâce à ce cas pratique. En plus, le fait d'avoir deux méthodes différentes va m'obliger à les tester, ce sera un bon apprentissage ! Merci encore.
Bonsoir à tous,
Du VBA ...
Option Explicit
Sub test()
Dim a, b, i As Long, ii As Long, t As Long, w, SL As Object
Set SL = CreateObject("System.Collections.SortedList")
a = Sheets("Liste").Cells(1).CurrentRegion.Value
For i = 2 To UBound(a, 1)
If Not SL.Contains(a(i, 1)) Then
ReDim w(1 To 2)
w(1) = a(i, 1)
Else
w = SL(a(i, 1))
ReDim Preserve w(1 To UBound(w) + 1)
End If
t = IIf(t < UBound(w), UBound(w), t)
w(UBound(w)) = i - 1
SL(a(i, 1)) = w
Next
ReDim b(1 To SL.Count, 1 To t)
For i = 0 To SL.Count - 1
For ii = 1 To UBound(SL.GetByIndex(i))
b(i + 1, ii) = SL.GetByIndex(i)(ii)
Next
Next
If Not Evaluate("isref('Resultat'!a1)") Then Sheets.Add(, Sheets(Sheets.Count)).Name = "Resultat"
With Sheets("Resultat").Cells(1)
.CurrentRegion.Clear
.Resize(UBound(b, 2), UBound(b, 1)).Value = Application.Transpose(b)
With .Cells(1).CurrentRegion
.Rows(1).Interior.ColorIndex = 43
.Borders.LineStyle = 2
.Borders.Weight = 2
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End With
End Subklin89
Bonjour et bienvenue CyberDid
Via VBA. Dans la feuille "Liste", cliquer sur le bouton en orange. Dans le classeur, le code est commenté. Le tableau structuré résultat sur la feuille "Objectif" s'appelle "tsPaysIdx".
Le code dans Module1 :
Sub Transposer()
Dim t, tIdx, r, i&, max&, col&, deb, tmp
deb = Timer: Application.ScreenUpdating = False
t = Sheets("Liste").[a1].ListObject.ListColumns(1).DataBodyRange
With Sheets("Objectif")
.[a1].CurrentRegion.Clear
If Not .[a1].ListObject Is Nothing Then .[a1].ListObject.Delete
.[a1].Resize(UBound(t)) = t
.[a1].Resize(UBound(t)).Sort .[a1], 1, Header:=xlNo, MatchCase:=False
.[a1].Resize(UBound(t)).RemoveDuplicates Columns:=1, Header:=xlNo
tIdx = .[a1].CurrentRegion
If Not IsArray(tIdx) Then tmp = tIdx: ReDim tIdx(1 To 1, 1 To 1): tIdx(1, 1) = tmp
ReDim res(1 To UBound(t) + 1, 1 To UBound(tIdx))
For i = 1 To UBound(tIdx): res(1, i) = 1: Next
For i = 1 To UBound(t)
col = Application.Match(t(i, 1), tIdx, 0)
res(1, col) = res(1, col) + 1
If res(1, col) > max Then max = res(1, col)
res(res(1, col), col) = i
Next i
For i = 1 To UBound(tIdx): res(1, i) = tIdx(i, 1): Next
.[a1].CurrentRegion.Clear
.[a1].Resize(max, UBound(res, 2)) = res
.ListObjects.Add(xlSrcRange, .[a1].CurrentRegion, , xlYes).Name = "tsPaysIdx"
.[a1].ListObject.TableStyle = "TableStyleLight10"
.Select
End With
MsgBox "Traitement terminé en " & Format((Timer - deb), "0.000\ sec.")
End SubRe,
Pour le fun...
Avec deux formules uniquement pour Excel 365.
Dans la cellule E1, la formule : =TRANSPOSE(TRIER(UNIQUE(Tableau1[Pays])))
Dans la cellule E2 (à copier vers la droite) , la formule :
=LET(P;Tableau1[Pays];S;SEQUENCE(NBVAL(Tableau1[Pays]);1;1;1);FILTRE(S;P=E1))
Bonjour à tous !
Je vais rejoindre notre célèbre horticulteur !
Une proposition M365 (Insider ?), type formule unique et dynamique, qui retourne l'entièreté du tableau :
=LET(
p;Tableau1[Pays];
s;SEQUENCE(NBVAL(p));
t;TRANSPOSE(PIVOTER.PAR(;p;s;TABLEAU.EN.TEXTE;;;;0));
SIERREUR(ASSEMB.V(TRANSPOSE(TRIER(UNIQUE(p)));TRANSPOSE(EXCLURE(REDUCE("";PRENDRE(t;;-1);LAMBDA(a;c;ASSEMB.V(a;FRACTIONNER.TEXTE(c;"; "))));1))*1);"")
)Bonjour JFL
Ta formule M365 : c'est en fait à quoi j'aurais voulu aboutir. J'apprécie
Merci pour tout ! Donc 2 procédures Power Query, 2 macros complètes et 2 formules novatrices. J'en demandais pas tant !
J'avoue que les macros nécessiteront un certain temps pour se les approprier et certaines formules sont pour moi nouvelles (LET, LAMBDA) mais je crois les avoir vu l'autre jour dans les bibliothèques de formules en me disant justement "il faudrait peut-être que je m'y mette"...
Pour l'instant, j'ai mis en application l'approche Power Query de JFL et ça fonctionne parfaitement ! Je testerai l'autre approche dans la semaine...
Je vous souhaite à toutes et tous une belle semaine !