EXCEL - éclater une lignes / cellules sur plusieurs lignes

qtn.leclerc

XLDnaute Nouveau
Bonjour,

Désolé pour ce nom qui est pas forcément très compréhensible mais voici ma problématique, je désirerai éclater une cellule / lignes contenant des mots ou expressions plus ou moins longs séparés par des virgules en plusieurs lignes et également réussir à annoter un numéro d'ID (recette) et une référence (source) de ligne sur chacune de ces lignes nouvellement crées.

Exemple en B2 : Tomate, Cerise, Poivron Vert, Courgette, Haricot Rouge
et en A2 : Recette 1 (ID) et C2 : Marché, Grande Surface (Source)

Je souhaiterai avoir sur une autre feuille et dans l'idéal en ne recourant qu'à des formules :
A2 : Recette 1 / B2 : Tomate / C2 : Marché
A3 : Recette 1 / B3 : Cerise / C3 : Marché
A4 : Recette 1 / B4 : Poivron Vert / C4 : Marché
A5 : Recette 1 / C5 : Courgette / C5 : Marché
A6 : Recette 1 / C6 : Haricot Rouge / C6 : Marché
A7 : Recette 1 / B7 : Tomate / C7 : Grande Surface
A8 : Recette 1 / B8 : Cerise / C8 : Grande Surface
A9 : Recette 1 / B9 : Poivron Vert / C9 : Grande Surface
A10 : Recette 1 / B10 : Courgette / C10 : Grande Surface
A11 : Recette 1 / B11 : Haricot Rouge / C11 : Grande Surface
...

Vous trouverez ci-après un fichier résumant ma demande qui je le pense serait plus explicite.

Merci par avance pour votre aide,
 

Pièces jointes

  • Tableau_donnees_eclatees.xlsx
    16.2 KB · Affichages: 90

CISCO

XLDnaute Barbatruc
Bonjour

Une possibilité en pièce jointe.

J'essayerai de simplifier tout cela un peu plus tard !

Une couleur --> un type de formule.

Il ni y a que des formules matricielles, donc à valider avec Ctrl+maj+entrer, sauf dans E2.

@ plus.

P.s : Pour avoir le tableau sur une autre feuille, il suffit de faire un copier-couper-coller.
 

Pièces jointes

  • Tableau_donnees_eclatees.xlsx
    23.9 KB · Affichages: 37

CISCO

XLDnaute Barbatruc
Bonjour

Idem, avec des noms.

La PlageA est une plage dynamique, donc dont la hauteur s'adapte au nombre de données dans la colonne A. Les autres noms sont définis, directement ou indirectement, par rapport à cette PlageA.

@ plus

P.S : Tout fonctionne en comptant le nombre de virgules dans les colonnes B et C. S'il en manque une, ou s'il y en a une en trop, le résultat obtenu ne sera pas correct.
 

Pièces jointes

  • Tableau_donnees_eclateesbis.xlsx
    21 KB · Affichages: 35

qtn.leclerc

XLDnaute Nouveau
Bonjour

Idem, avec des noms.

La PlageA est une plage dynamique, donc dont la hauteur s'adapte au nombre de données dans la colonne A. Les autres noms sont définis, directement ou indirectement, par rapport à cette PlageA.

@ plus

P.S : Tout fonctionne en comptant le nombre de virgules dans les colonnes B et C. S'il en manque une, ou s'il y en a une en trop, le résultat obtenu ne sera pas correct.

Bonjour Cisco,
Je viens de prendre connaissance de ton fichier et c'est pas mal du tout est beaucoup plus simple.
Il y a un point que j'ai réussi à voir par moi même, cela concerne la rédaction du contenu de la cellule B (ingrédients), faisant appel à une fonction concatener (mais comment gérer les trous dans la liste qui posent un problème lors de la ré-écriture).

Par contre, j'aurai également deux petites questions, est-ce que on pourrait imaginer que le tableau s'agrandissent tout seul sans être obligé d'étirer les cellules (dans l'idéal sans recourir au macro).

D'autre part, est-ce que l'on pourrait si je rajoute une autre colonne (D) par exemple avec la commande faite ou non saisir automatiquement dans la colonne (E - F - G - H) la data / heure au moment ou cela a été fait ?

J'essai de trouver une solution de mon côté également mais je sèche un peu ...
Merci par avance pour ton aide,

Bonne réception,

P.S Ci-joint le fichier à nouveau :)
 

Pièces jointes

  • Tableau_donnees_eclateesbis.xlsx
    22.9 KB · Affichages: 23

CISCO

XLDnaute Barbatruc
Bonjour

Pour supprimer les espaces liés à la présence d'une virgule suivie d'un espace, puis d'une autre virgule dans la colonne B, il faut, après avoir sélectionné une cellule de la ligne 2, remplacer la définition de CelluleB dans le gestionnaire de noms par
Code:
=SUBSTITUE(SUPPRESPACE(INDEX(PlageB;EQUIV(Data!$J1;PlageA;0)));", ,";",")
et de CelluleBbis par
Code:
=SUBSTITUE(SUPPRESPACE(INDEX(PlageB;EQUIV(Data!$J2;PlageA;0)));", ,";",")

Si tu risques d'avoir certaines fois deux virgules l'une après l'autre, sans espace entre elles, il faudra transformer ces dernières propositions avec un SUBSTITUE(formule ci-dessus;",,";",").

Si tu as le même problème sur la colonne C, il faut modifier en conséquence CelluleC et CelluleCbis.

Pour ce qui est de remplir les colonnes E, F, G et H, c'est plus compliqué. J'y réfléchi, mais je ne vois pas comment faire avec des formules pour que les dates obtenues n'évoluent pas dans le temps.

@ plus
 

CISCO

XLDnaute Barbatruc
Bonjour

Bonjour Cisco,
...
Par contre, j'aurai également deux petites questions, est-ce que on pourrait imaginer que le tableau s'agrandissent tout seul sans être obligé d'étirer les cellules (dans l'idéal sans recourir au macro).

....:)

Et pour cela, là aussi, je ne vois pas trop comment faire... Tu peux toujours tirer les formules, après avoir sélectionné les cellules de la dernière ligne contenant des formules, très, très bas...


@ plus
 

klin89

XLDnaute Impliqué
Bonsoir qtn.leclerc, CISCO, le forum :)

Pourquoi pas une macro :(
VB:
Option Explicit
Sub test()
Dim a, b(), x, y, i As Long, ii As Byte, iii As Byte, n As Long
    a = Sheets("Data").Range("a1").CurrentRegion.Value
    'attention a la 1ere dimension
    ReDim b(1 To UBound(a, 1) * 100, 1 To UBound(a, 2))
    n = 1
    b(n, 1) = a(1, 1): b(n, 2) = a(1, 2): b(n, 3) = a(1, 3)
    For i = 2 To UBound(a, 1)
        y = Split(a(i, 2), ",")
        x = Split(a(i, 3), ",")
        For ii = 0 To UBound(x)
            For iii = 0 To UBound(y)
                n = n + 1
                b(n, 1) = a(i, 1)
                b(n, 3) = x(ii)
                b(n, 2) = y(iii)
            Next
        Next
    Next
    With Sheets.Add().Cells(1).Resize(n, 3)
        .Value = b
        .Columns.AutoFit
    End With
End Sub
klin89
 

qtn.leclerc

XLDnaute Nouveau
Bonjour

Pour supprimer les espaces liés à la présence d'une virgule suivie d'un espace, puis d'une autre virgule dans la colonne B, il faut, après avoir sélectionné une cellule de la ligne 2, remplacer la définition de CelluleB dans le gestionnaire de noms par
Code:
=SUBSTITUE(SUPPRESPACE(INDEX(PlageB;EQUIV(Data!$J1;PlageA;0)));", ,";",")
et de CelluleBbis par
Code:
=SUBSTITUE(SUPPRESPACE(INDEX(PlageB;EQUIV(Data!$J2;PlageA;0)));", ,";",")

Si tu risques d'avoir certaines fois deux virgules l'une après l'autre, sans espace entre elles, il faudra transformer ces dernières propositions avec un SUBSTITUE(formule ci-dessus;",,";",").

Si tu as le même problème sur la colonne C, il faut modifier en conséquence CelluleC et CelluleCbis.

Pour ce qui est de remplir les colonnes E, F, G et H, c'est plus compliqué. J'y réfléchi, mais je ne vois pas comment faire avec des formules pour que les dates obtenues n'évoluent pas dans le temps.

@ plus

Bonjour @CISCO,

J'essayé en modifiant actuellement les données dans le gestionnaire de noms car en effet le cas peut se produire pour chacune des colonnes mais malheureusement cela vide entièrement ton tableau ...

Plus je touche plus les informations s'effacent car en effet cela peut arriver qu'il n'y ait parfait pas d'ingrédient une fois voir parfois deux fois etc. également.

Pour ce qui est du blocage des cellules, je cherche mais pour le moment rien de concret de mon côté et toi as-tu réussi à trouver quelque chose qui pourrait convenir ? Le principal problème étant de réussir à figer la cellule pour qu'elle arrête de changer.

Merci pour ton aide,
 

qtn.leclerc

XLDnaute Nouveau
Bonsoir qtn.leclerc, CISCO, le forum :)

Pourquoi pas une macro :(
VB:
Option Explicit
Sub test()
Dim a, b(), x, y, i As Long, ii As Byte, iii As Byte, n As Long
    a = Sheets("Data").Range("a1").CurrentRegion.Value
    'attention a la 1ere dimension
    ReDim b(1 To UBound(a, 1) * 100, 1 To UBound(a, 2))
    n = 1
    b(n, 1) = a(1, 1): b(n, 2) = a(1, 2): b(n, 3) = a(1, 3)
    For i = 2 To UBound(a, 1)
        y = Split(a(i, 2), ",")
        x = Split(a(i, 3), ",")
        For ii = 0 To UBound(x)
            For iii = 0 To UBound(y)
                n = n + 1
                b(n, 1) = a(i, 1)
                b(n, 3) = x(ii)
                b(n, 2) = y(iii)
            Next
        Next
    Next
    With Sheets.Add().Cells(1).Resize(n, 3)
        .Value = b
        .Columns.AutoFit
    End With
End Sub
klin89

Bonjour @klin89 ,

MErci pour ton aide mais de mon côté, je n'arrive pas à la faire fonctionner :/
Si as tout hasard tu avais également une idée par rapport à mon précédent message également ce serait top :

"cf message :

Bonjour Cisco,
Je viens de prendre connaissance de ton fichier et c'est pas mal du tout est beaucoup plus simple.
Il y a un point que j'ai réussi à voir par moi même, cela concerne la rédaction du contenu de la cellule B (ingrédients), faisant appel à une fonction concatener (mais comment gérer les trous dans la liste qui posent un problème lors de la ré-écriture).

Par contre, j'aurai également deux petites questions, est-ce que on pourrait imaginer que le tableau s'agrandissent tout seul sans être obligé d'étirer les cellules (dans l'idéal sans recourir au macro).

D'autre part, est-ce que l'on pourrait si je rajoute une autre colonne (D) par exemple avec la commande faite ou non saisir automatiquement dans la colonne (E - F - G - H) la data / heure au moment ou cela a été fait ?

J'essai de trouver une solution de mon côté également mais je sèche un peu ...
Merci par avance pour ton aide,

Bonne réception,

P.S Ci-joint le fichier à nouveau :)"

Mille merci pour ton aide :)

Quentin
 

Pièces jointes

  • Tableau_donnees_eclateesbis.xlsx
    22.5 KB · Affichages: 29

CISCO

XLDnaute Barbatruc
Bonjour

Bonjour @CISCO,

J'essayé en modifiant actuellement les données dans le gestionnaire de noms car en effet le cas peut se produire pour chacune des colonnes mais malheureusement cela vide entièrement ton tableau ...
...

Merci pour ton aide,

Dans ma dernière proposition, post #5, il y a un espace entre les deux virgules, c'est ", ," et pas ",,". Ton dernier problème venait peut être de là.

Dans la pièce jointe, j'ai réutilisé le même type de formule :
SUPPRESPACE supprime les espaces en double, donc ici transforme les , , (avec plusieurs espaces entre les deux virgules) en , , avec un seul espace entre les deux virgules.
SUBSTITUE(........;", , ,";",") remplace les , , , (avec un espace entre chaque virgule) par une seule virgule.
SUBSTITUE(........;", ,";",") remplace les , , (avec un espace entre les deux virgules) par une seule virgule.
SUBSTITUE(........;",,";",") remplace les virgules en double par une seule virgule.

@ plus
 

Pièces jointes

  • Tableau_donnees_eclateesquatre.xlsx
    23.2 KB · Affichages: 36
Dernière édition:

CISCO

XLDnaute Barbatruc
Bonjour

@CISCO , Oui en effet on pourrait tirer les formules jusqu'en bas, très très bon mais cela fait pas forcément très propre, non ?

"Pas très propre"... Pourquoi ? J'ai mis des SIERREUR(......;"") ou des SI(...="";"";....) pour ne rien avoir dans les lignes en trop. Il faut juste tirer les formules assez bas pour afficher le nombre correct de lignes. Au besoin, on doit pouvoir mettre un test affichant si tout est affiché.

@ plus
 
Haut Bas