oguruma
XLDnaute Impliqué
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é :
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
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
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
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
- NOM : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(NOM)))+(COLONNE()-COLONNE(NOM));0;1;1)
- ADRESSE : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(ADRESSE)))+(COLONNE()-COLONNE(NOM));0;1;1)
- VILLE : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(VILLE)))+(COLONNE()-COLONNE(NOM));0;1;1)
- DEPART : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(DEPART)))+(COLONNE()-COLONNE(NOM));0;1;1)
- CODE_POS : =DECALER(INFO_DEPART;(NB_RUBRIQUES_GROUPE*(LIGNE()-LIGNE(CODE_POS)))+(COLONNE()-COLONNE(NOM));0;1;1)
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é
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
Dernière édition:
