EXCEL - éclater une lignes / cellules sur plusieurs lignes

Q

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
Trier par date Trier par votes
CISCO

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
Vote positif 0 Vote négatif
CISCO

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
Vote positif 0 Vote négatif
Q

qtn.leclerc

XLDnaute Nouveau
CISCO à dit:
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.
Cliquez pour agrandir...

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
Vote positif 0 Vote négatif
CISCO

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
 
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Bonjour

qtn.leclerc à dit:
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).

....:)
Cliquez pour agrandir...

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
 
Vote positif 0 Vote négatif
klin89

klin89

XLDnaute Accro
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
 
Vote positif 0 Vote négatif
Q

qtn.leclerc

XLDnaute Nouveau
CISCO à dit:
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
Cliquez pour agrandir...

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,
 
Vote positif 0 Vote négatif
Q

qtn.leclerc

XLDnaute Nouveau
klin89 à dit:
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
Cliquez pour agrandir...

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
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Bonjour

qtn.leclerc à dit:
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,
Cliquez pour agrandir...

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:
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Bonjour

qtn.leclerc à dit:
@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 ?
Cliquez pour agrandir...

"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
 
Vote positif 0 Vote négatif

Membres actuellement en ligne

2 membres et 60 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
314 719
Messages
2 112 183
Membres
111 456
dernier inscrit
Bologne5