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

oguruma

XLDnaute Occasionnel
L'idée est la suivante - à partir de ce format
1703068626994.png


obtenir cette présentation

1703068652480.png


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
1703075432257.png


Description des paramètres
1703073714983.png

Ensuite pour chaque colonne - méthode statique il s
1703072918690.png


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
1703073067840.png

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


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


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
1703069841828.png

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é
1703073299273.png
--> voir mon post sur ce sujet
1703073772359.png


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
 

Pièces jointes

  • Transpose_Blocs_Lignes_Colonnes.xlsm
    50.1 KB · Affichages: 4
Dernière édition:

Statistiques des forums

Discussions
313 313
Messages
2 097 050
Membres
106 820
dernier inscrit
Kalouppppp