Transposer en colonnes des blocs de données en lignes - via formules

L'idée est la suivante - à partir de ce format


obtenir cette présentation



Plusieurs méthodes :
- par formules
- en vba
- via powerquery

La méthode présentée ici est en formules - une nouvelle fois via la puissance de la fonction DECALER
en résumé :

• définir une feuille paramètres dans laquelle on trouve
  • Le nombre de groupes de données (ou blocs de données)
  • Le nombre d'item ou rubriques par groupe
  • Le nombre de lignes de données
• ces 3 paramètres sont calculés automatiquement - respectivement
  • =NB.SI(COL_RUBRIQUES;"Nom") -> champ : NB_GROUPES
  • =NOMBRE_DE_LIGNES/NB_GROUPES -> champ : NB_RUBRIQUES_GROUPE
  • =NBVAL(DONNEES_BRUTES) -> champ : NOMBRE_DE_LIGNES
• Nous avons besoin de connaître
  • l'adresse de début de groupe -> champ : ADRESSE_RUBRIQUE
  • l'adresse de fin de groupe -> champ : ADRESSE_RUBRIQUE_FIN
  • L'étendue maximum de la colonne rubriques -> champ : MAX_LIGNES
  • ligne et colonne de début où seront déposées les données pour le mode dynamique (onglet Extraction dynamique du fichier joint)
    • LIGNE_DEBUT_DONNEES
    • COLONNE_DEBUT_DONNEES
• COL_RURBIQUES est déterminé dynamiquement via la fonction
  • =INDIRECT(ADRESSE_RUBRIQUE&":"&MAX_LIGNES)
• DONNEES_BRUTES
  • =DECALER('Données brutes'!$A$8;0;0;NBVAL('Données brutes'!$A$8:$A$1008);1)
• INFO_DEPART
  • ='Données brutes'!$B$8

Il sera néanmoins nécessaire d'ajuster cette formule en fonction du contexte pour calculer le nbr de blocs


Description des paramètres

Ensuite pour chaque colonne - méthode statique il s


L'astuce : les blocs de données ou groupes sont multiples de 5 étant donné qu'il y a 5 rubriques par blocs
d'où la démonstration suivante pour les données ligne à ligne par bloc

on a ainsi la séquence de début de lecture de chaque bloc
Le multiple de 5 est déterminé ici :

le positionnement des colonnes est déterminé comme suit afin de ventiler chaque item


Ensuite pour chaque colonne - méthode statique il suffit de positionner respectivement les formules suivantes

1. NOM : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(NOM)))+(COLONNE()-COLONNE(NOM));0;1;1)
2. ADRESSE : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(ADRESSE)))+(COLONNE()-COLONNE(NOM));0;1;1)
3. VILLE : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(VILLE)))+(COLONNE()-COLONNE(NOM));0;1;1)
4. DEPART : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(DEPART)))+(COLONNE()-COLONNE(NOM));0;1;1)
5. CODE_POS : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(CODE_POS)))+(COLONNE()-COLONNE(NOM));0;1;1)

Les champs NOM, ADRESSE, VILLE, DEPART, CODE_POS sont déterminés comme suit

NOM : ='Données brutes'!$E$8
ADRESSE : ='Données brutes'!$F$8
VILLE : ='Données brutes'!$G$8
DEPART : ='Données brutes'!$H$8
CODE_POS : ='Données brutes'!$G$8

puis recopier dans les lignes voulues selon l'étendue les formules 1 à 5

Le mode dynamique diffère du mode statique car on ne s'appuie plus sur des noms de champs mais sur le calcul dynamique de début de ligne et colonne où on va déposer les données via la fonction DECALER comme ceci :
=DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE_DEBUT_DONNEES))+(COLONNE()-COLONNE_DEBUT_DONNEES);0;1;1)
et pour éviter l'affichage des zéros quand aucune donnée n'est trouvée on applique le format personnalisé --> voir mon post sur ce sujet


Pour récupérer automatiquement les titres des rubriques via la magie de TRANSPOSE aidée par INDIRECT de manière à ce que ce soit totalement dynamique : =TRANSPOSE(INDIRECT(ADRESSE_RUBRIQUE & ":" & ADRESSE_RUBRIQUE_FIN))

Cette formule =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE_DEBUT_DONNEES))+(COLONNE()-COLONNE_DEBUT_DONNEES);0;1;1) peut être décomposée pour une meilleure lecture avec la fonction LET et créer une fonction LAMBDA avec les paramètres INFO_DEPART, NB_RUBRIQUES_GROUPE, COLONNE_DEBUT_DONNEES, COLONNE_DEBUT_DONNEES.
INFO_DEPART : c'est le champ pivot pour la lecture des blocs de données. Il est donc important de bien le renseigner au même titre que les paramètres cités ci-dessus.

- le plus simple est de consulter le fichier