Fractionner les textes multilignes d'une plage avec une répétition d'un titre

Petite formule à la demande d'un utilisateur du forum Answers.

La demande

Fractionner le texte multilignes en colonne B en plusieurs cellules tout en répétant le titre correspondant de la colonne A.

La solution proposée

Une seule formule permettant de générer les 2 colonnes et les différentes lignes.

=LET(a;A2:A4;
b;B2:B4;
r;CAR(10);
c;"|";
FRACTIONNER.TEXTE(
    JOINDRE.TEXTE(r;VRAI;a&c&SUBSTITUE(b;r;r&a&c));c;r))

Résultat de la formule

Analyse des contraintes liées aux données et de la stratégie employée

• Découper les valeurs d'une cellule de la colonne B en lignes est facilement faisable avec la fonction FRACTIONNER.TEXTE en se basant sur le retour à la ligne (caractère car(10) => Par exemple pour B2, on obtiendrait une matrice de 3 lignes).
• Mais cette solution n'est pas applicable sur une plage de plusieurs cellules (ici B2:B4). N'ayant pas la "place", la fonction ne renverrait que la 1^ere valeur des 3 matrices résultantes (ici a1, b1, c1) et l'utilisation d'une fonction ASSEMB.V n'y changera rien.
• L'idée est donc de transformer les 3 cellules en 1 seule et unique chaîne (concaténation des valeurs via JOINDRE.TEXTE) !
• La répétition des valeurs de la colonne A pose aussi un problème quand on veut la jumeler au résultat de la fonction FRACTIONNER.TEXTE. Les formules Excel n'aimant pas (pour l'instant) les tableaux (matrices) imbriqués.
• L'idée est là encore d'utiliser une concaténation des valeurs avec l'opérateur & cette fois (étant suffisant) !
• On va obtenir une chaîne unique où :
  • Les changements de colonne seront repérés avec un caractère spécifique ne devant pas être présent dans le texte d'origine. Ici le caractère pipe ( | ) par exemple (@, # ou la tabulation CAR(9) auraient pu convenir).
  • Les changements de ligne seront repérés avec le caractère Retour à la ligne (CAR(10)).

Interprétation de la formule

• On définit des variables avec la fonction LET
  • a : Valeurs de la colonne A.
  • b : Valeurs de la colonne B.
  • r : Séparateur de ligne (row), ici le caractère Retour à la ligne (CAR(10)).
  • c : Séparateur de colonne, ici le caractère pipe ( | ).
• On se libère des 2 colonnes avec a & c & SUBSTITUE( b ; r ; r & a & c ) qui renvoie une matrice de 3 lignes :
  { "xx | a1 ¶ xx | a2 ¶ xx | a3" ; "yy | b1 ¶ yy | b2" ; "zz | c1" }
  le signe ¶ symbolise ici le caractère Retour à la ligne.
  
  
  • Cette portion de formule permet d'ajouter après chaque Retour à la ligne, la valeur de la colonne A et le caractère pipe | . On ajoute aussi cette paire de valeurs en début de chaine pour traiter une exception.
• On se libère des 3 lignes avec JOINDRE.TEXTE et on obtient une chaîne unique :
  "xx | a1 ¶ xx | a2 ¶ xx | a3 ¶ yy | b1 ¶ yy | b2 ¶ zz | c1"
• FRACTIONNER.TEXTE termine le travail en se basant sur nos caractères symbolisant les lignes et les colonnes pour renvoyer la matrice désirée.

Merci pour votre attention bienveillante.