Discussion :

[bartimeus35]

Bonjour,
J'ai un feuille excel qui contient plein de ligne du genre :


Je voudrais pouvoir avoir pour chaque ligne N lignes :Jalon/Date,

Est il possible de faire cela sans avoir recours à une macro VBA?
D'avance merci,
@+
François

[78chris]

Bonjour

Avec 2007 (16 ans c'est beaucoup surtout pour une version mal finie) le choix des solutions est très limité. VBA ou formules

Tu as plusieurs lignes ? Le nombre de colonnes est le même d'une ligne à l'autre ?

[bartimeus35]

oui j'ai vraiment bcp de lignes .
Les lignes sont toutes sur le même format, je vais donc faire du code VBA , a moins d'un miracle
@+

[Pierre Dumas]

Bonsoir

[...] a moins d'un miracle
@+

Vous passez votre fichier à quelqu'un qui a une version un peu plus récente que la votre et cela se fait en moins d'une minute (même sur des centaines de milliers de lignes). Si cela doit être récurrent, la mise à jour ne prendra que quelques secondes.

Vous pouvez aussi acheter une version récente d'Office pour vraiment pas cher (tout en étant légal) et ainsi être autonome.

En espérant que cela aide

Bonne fin d'après-midi.

Pierre Dumas

[bartimeus35]

Salut,
C'est pas donné les versions d'office. Office 365 c'est 100€/ an.
Tu as une autre idée pour avoir une version plus récente moins cher que 100€/an
Et avec une version plus récente. Quelle est la méthode?
Merci.
A+
Francis

[78chris]

Bonjour

Tu peux regarder ce fil https://www.developpez.net/forums/d2...-ebay-rakuten/

On trouve aussi des licence365 : achetées en nombre et revendues à l'unité...

En attendant

[bartimeus35]

Salut.
Ce n'est pas super clair si c'est légal ou non ces histoires de licences achetés en gros et revendelues à l'unité. Je vais creuser....
Et sinon comment on résoud mon problème avec un version plus récente?
Merci
A+

[Pierre Dumas]

Bonsoir

On trouve des versions d'Office 2019 ou 2021 à moins d'un euro. On n'a ainsi pas besoin d'un compte O365 puisqu'il s'agit de versions installées.

78chris, que je salue, a proposé une solution tout en formule qui doit fonctionner même sur une version 2007.

Sinon, avec PowerQuery, on prépare des requêtes qui ont le même nombre de colonnes (ici 2), puis on les ajoute bout à bout. Et c'est fini.

En espérant que cela aide.

Bonne soirée

Pierre Dumas

[78chris]

RE

Par requête une seule suffit (tableau source nommé Donnees):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
let
    Source = Excel.CurrentWorkbook(){[Name="Donnees"]}[Content],
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(Source, {}, "Attribut", "Valeur"),
    PStep = Table.SplitColumn(#"Tableau croisé dynamique des colonnes supprimé", "Attribut", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Info"}),
    #"Index ajouté" = Table.AddIndexColumn(PStep, "Index", 0, 1, Int64.Type),
    #"Lignes groupées" = Table.Group(#"Index ajouté", {"Info"}, {{"Tabl", each _, type table [Info=nullable text, Valeur=any]}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes groupées", "Personnalisé", each Table.AddIndexColumn([Tabl],"Idx",1)),
    #"Personnalisé développé" = Table.ExpandTableColumn(#"Personnalisée ajoutée", "Personnalisé", {"Valeur", "Index", "Idx"}, {"Valeur", "Index", "Idx"}),
    #"Lignes triées" = Table.Sort(#"Personnalisé développé",{{"Index", Order.Ascending}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes triées",{"Tabl", "Index"}),
    #"Colonne dynamique" = Table.Pivot(#"Colonnes supprimées", List.Distinct(#"Colonnes supprimées"[Info]), "Info", "Valeur"),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne dynamique",{{"Date", type date}}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Type modifié",{"Idx"})
in
    #"Colonnes supprimées1"

Le vente à l'unité est légale en Europe a priori

On dispose d'une licence liée à la licence globale mais on dispose d'un mot de passe perso pour l'installation.
Si la licence globale n'est pas conservée, on perd son accès mais à quelques euros ce n'est pas grave...
J'ai vu des licences durer plus de 2 ans, d'autres quelques mois...

Sinon 2021 n'est pas lié à une autre licence...

[Pierre Dumas]

La proposition de 78Chris a éveillée ma curiosité
Merci à toi

Toujours sur le même jeu de départ, voici une autre solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
let
    Source = Excel.CurrentWorkbook(){[Name="Donnees"]}[Content],
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(Source, {}, "Attribut", "Valeur"),
    #"Diviser la colonne selon les transitions de caractères" = Table.SplitColumn(#"Tableau croisé dynamique des colonnes supprimé", "Attribut", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Info"}),
    #"Index ajouté" = Table.AddIndexColumn(#"Diviser la colonne selon les transitions de caractères", "Index", 0, 1, Int64.Type),
    #"Colonne divisée par entier" = Table.TransformColumns(#"Index ajouté", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Colonne dynamique" = Table.Pivot(#"Colonne divisée par entier", List.Distinct(#"Colonne divisée par entier"[Info]), "Info", "Valeur"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonne dynamique",{"Index"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Jalon", type text}, {"Date", type date}})
in
    #"Type modifié"

Je ne connaissais pas la technique qui permet de fractionner les colonnes en ne gardant que la première en une seule étape

Pour avoir un index allant de 2 en 2, j'ai fait un index classique, puis une division par entier de 2.

En espérant que cela aide

Bonne journée

Pierre Dumas

[Raccourcix]

Bonjour
personnellement, si j'avais encore Excel 2007, que mon besoin était ponctuel je partirai sur la solution avec INDEX, ENT, MOD proposée par Chris en #6,


sinon, Pierre, Chris par Power Query on peut concaténer toutes les colonnes paires/impaires les unes sous les autres

"Données" étant la table de base,
List.Alternate avec les paramètres 1, 1, 1 pour les paires 2, 4, 6, 8
List.Alternate avec les paramètres 1, 1 pour les impaires 1, 3, 5, 7

Colonnes_paires

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
List.Combine(
    List.Transform(
        List.Alternate(Table.ColumnNames( Données),1,1,1),
        each Table.Column(Données,_)
        )
    )

Ensuite, il suffit de regrouper les paires et les impaires dans une seul table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Table.FromColumns({Colonnes_impaires,Colonnes_paires},{"Jalon", "Date"})

Pas d'idée sur la performance avec beaucoup de lignes.

Stéphane

[Tête de chat]

Bonjour

a priori la fonction décaler existe en 2007, on est sauvés



donc par formules on s'en sort :

Renormalisation.xlsx

Il suffit de tirer la formuler vers le bas suffisamment , jusqu'à ce que ctrl soit égal à cible.

[Raccourcix]

Bonjour

désolé, mais pour moi DECALER n'apporte rien de mieux que le INDEX de 78Chris au #6. c'est une fonction qu'il vaut mieux éviter lorsqu'on le peut (car volatile et donc qui se recalcule constamment)

Toujours par Power Query, la demande peut se traiter de manière très synthétique avec List.TransforMany

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
#table(
    {"Jalon","Date"},
    List.TransformMany(
        Table.ToRows(Données), 
        each {0..Table.ColumnCount(Données)/2-1},
        (x,y)=>{x{2*y},x{2*y+1}}
        )
    )

Table.ToRows va convertir chaque ligne en liste
{0..Table.ColumnCount(Données)/2-1} va créé une liste {0, 1, 2, 3, ... jusque la moitié des colonnes initiales}
(x,y)=>{x{2*y},x{2*y+1}} va crée une liste avec la {colonne1,colonne2} puis {colonne3,colonne4}... le tout pour chaque ligne
- x étant la liste des éléments de chaque ligne,
- y valant successivement 0, 1, 2, 3. 2*y vaut 0, 2, 4, 6 et 2*y+1 vaut 1, 3, 5, 7...

#table({"Jalon","Date"}, ... va créé une table avec les 2 colonnes "Jalon" et "Date" en titre et les valeurs de chaque ligne découpée 2 colonnes par 2 colonnes

Je n'ai pas testé non plus suivant la volumétrie. mais ça faisait longtemps que je voulais tester cette fonction complexe sur de tel exemple

Stéphane

[Pierre Dumas]

Bonjour de nouveau

[...]
sinon, Pierre, Chris par Power Query on peut concaténer toutes les colonnes paires/impaires les unes sous les autres
[...]

Je me doutais bien que ma solution ferait sortir du bois Raccourcix et qu'il nous proposerait une solution plus efficace.

Bravo à toi

Belle journée à chacun

Pierre

[78chris]

Bonjour à tous

Je me doutais bien que ma solution ferait sortir du bois Raccourcix et qu'il nous proposerait une solution plus efficace.

J'avais failli mettre solution simple en attendant la solution experte de Raccourcix...

[Raccourcix]

Merci pour vos retours.
Le dimanche mon PC est au repos !

Le code est plus concis mais pas certain que ce soit plus efficace. il faudrait tester avec plusieurs milliers de lignes

Ensuite c'est clairement plus complexe à comprendre/mettre en œuvre pour la très grande majorité des utilisateurs, donc un intérêt pédagogique plus limité.
De mon point de vue il vaut souvent mieux une solution techniquement moins aboutie mais plus aisée à reproduire/adapter au cas de chacun.

Stéphane

[Pierre Dumas]

Bonsoir

Merci pour vos retours.
Le dimanche mon PC est au repos !

Cela n'a pas toujours été le cas
Je me rappelle une solution que tu avais trouvé en une seule formule pour quelqu'un qui cherchait une solution pour vérifier des codes et avec une clé de vérification

Le code est plus concis mais pas certain que ce soit plus efficace. il faudrait tester avec plusieurs milliers de lignes

Ensuite c'est clairement plus complexe à comprendre/mettre en œuvre pour la très grande majorité des utilisateurs, donc un intérêt pédagogique plus limité.
De mon point de vue il vaut souvent mieux une solution techniquement moins aboutie mais plus aisée à reproduire/adapter au cas de chacun.

Stéphane

Entièrement d'accord avec toi sur ta dernière remarque. Ce n'est pas forcément la même solution pour tout le monde.
Il faut aussi penser à la maintenance de l'outil.

Bonne soirée

Pierre Dumas

[bartimeus35]

Bonsoir,
J'ai réussi avec vos solutions mais on vient de me proposer d'utiliser office 365.
Avec cette versions comment peut on faire cela plus simplement ?
Merci,

[Pierre Dumas]

Bonsoir

Voilà une bonne nouvelle.

Le code que j'ai donné dans le post 10 est à copier-coller dans l'éditeur avancé de Power Query. C'est un compromis entre le nombre d'étapes peu élevé et la complexité (pas trop élevée).

En espérant que cela aide

Bonne soirée

Pierre Dumas

[Raccourcix]

Bonjour
la discussion est résolue mais il me semble intéressant de vous faire une synthèse de mes tests ce matin.

Soit un tableau de la forme demandée avec 20 colonnes (10 jalons et 10 dates) et 10 000 lignes

Voici le temps nécessaire pour obtenir les 100 000 lignes de jalons et dates

- Proposition de 78chris en #9 : entre 22 et 26 secondes
- Proposition de Pierre en #10 : entre 15 et 20 secondes
- Ma proposition en #11 : entre 6 et 9 secondes (mais les données sont présentées colonnes par colonnes et non lignes à lignes)
- Celle en #13 : entre 1,5 et 2 secondes

Je vous redonne ce code très performant,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
#table(
    type table [Jalon = text, Date = date],
    List.TransformMany(
        Table.ToRows(Données), 
        each {0..Table.ColumnCount(Données)/2-1},
        (x,y)=>{x{2*y},x{2*y+1}}
        )
    )

Table.ToRows va convertir chaque ligne en liste
{0..Table.ColumnCount(Données)/2-1} va créé une liste {0, 1, 2, 3, ... jusque la moitié des colonnes initiales}
(x,y)=>{x{2*y},x{2*y+1}} va crée une liste avec la {Jalon1,Date1} puis {Jalon2,Date2}... le tout pour chaque ligne
- x étant la liste des éléments de chaque ligne,
- y valant successivement 0, 1, 2, 3. 2*y vaut 0, 2, 4, 6 et 2*y+1 vaut 1, 3, 5, 7... {x{0},x{1}} donne {Jalon1,Date1}

#table(type table [Jalon = text, Date = date], ...va créer une table avec les 2 colonnes typées "Jalon" en texte et "Date" en date et les valeurs de chaque ligne découpée 2 colonnes par 2 colonnes

Stéphane