Données à transposer dans un nouveau tableau

[loulourav]

bonjour à tous,

je cherche les bonnes formules pour automatiser la création d'un tableau avec une forme précise à respecter, avec des données issues d'un autre tableau d'une forme différente.

une image vaut mieux que mes explications ! :


En haut le tableau source, en bas le tableau à construire
cette opération est à réaliser tout les mois, donc cela commence à être fastidieux ...
Il est à noté que des lignes vides présentent dans le fichier source ne doivent pas l'être dans le fichier final (au pire je les supprimerai à la main )
Si possible également de trier par année, puis par mois puis par Code UF

je joins également un xls

merci pour votre aide !

 

[Modeste]

Re : Données à transposer dans un nouveau tableau

Bonsoir loulourav,

Pas facile de comprendre ce que tu veux!? Dans ton exemple,

• Les "CODE_UF1" ne sont pas identiques à ceux de la feuille "source"!? On ne complète que ceux qui sont communs?
• Où trouve-t-on la mention 2012, dans la feuille source?
• Le code 1610 est présent deux fois, dans la feuille source ... koikonfé??

Bref, il faudrait que tu précises une série de choses, faute de quoi, on en serait réduits à "deviner"! Et là, c'est pas gagné!

 

[loulourav]

Re : Données à transposer dans un nouveau tableau

effectivement cela manque de précisions !! désolé

Les "CODE_UF1" ne sont pas identiques à ceux de la feuille "source"!? On ne complète que ceux qui sont communs?
j'ai extrait un exemple qui a 2 années différentes ... c'était plus pour montrer la "forme"
en fait il s'agit de recopier les valeurs (hors vide) de l'onglet source
Où trouve-t-on la mention 2012, dans la feuille source?
effectivement ici il s'agit de 2013, l'année sera systématiquement mentionné de cette façon dans l'onglet source
Le code 1610 est présent deux fois, dans la feuille source ... koikonfé??
le recopier 2 fois

j’espère avoir été clair dans mes explications !

 

[Modeste]

Re : Données à transposer dans un nouveau tableau

Bonjour,

Je dois avouer que j'ai failli ne plus répondre ... si ton message a été consulté une soixantaine de fois, que tu n'obtiens qu'une seule réponse (sous forme d'une série de questions) il me semble que tu devrais faire l'effort de te mettre à la place de ceux qui te lisent et ne connaissent ni ton fichier, ni ton contexte de travail!
Quand je dis: "il faudrait que tu précises une série de choses", je trouve ta réponse désespérément "minimaliste"

Ceci étant posé, un essai (avec le peu que j'ai compris ... et le reste que j'ai extrapolé!)

J'ai supprimé les lignes vides de la feuille source avant toute chose.

Plages nommées:
- "lib" fait référence à =DECALER(source!$C$5;;;NBVAL(source!$C:$C)-1)
- "codes" fait référence à =DECALER(source!$B$5;;;NBVAL(source!$C:$C)-1)

Ne fonctionnera que si pas de cellules vides en B et C de la feuille source!!

Dans la feuille CodesTriés, 2 plages nommées, là aussi:
"CodesTriés" fait référence à =DECALER(CodesTriés!$A$1;;;NB(CodesTriés!$A:$A))
'LibTriés" fait référence à =DECALER(CodesTriés;;1)

Cette feuille a été ajoutée pour éviter d'avoir des formules matricielles partout en feuille source. Ce sont les doublons dans les codes qui posent le plus gros problème! Les seules formules matricielles sont dans la plage "LibTriés"

Le résultat est en feuil3. Je n'ai recopié que sur une trentaine de lignes, juste pour passer au mois suivant. Je te laisse le soin de recopier ... aussi bas que nécessaire!

... À tester en long, en large, en travers, dans l'espace et dans le temps!

 

[loulourav]

Re : Données à transposer dans un nouveau tableau

ok merci pour ta proposition, je vais essayer !

désolé pour les explications je pensais que c'était compréhensible ainsi
Pour ce qui est des doublons dans les codes si cela est vraiment trop compliqué à gérer je peux modifier le code et lui ajouter un caractère type : 1610A
est ce une solution ?

 

[Modeste]

Re : Données à transposer dans un nouveau tableau

Re,

Pour ce qui est des doublons dans les codes si cela est vraiment trop compliqué à gérer je peux modifier le code et lui ajouter un caractère type : 1610A
est ce une solution ?

Ah clairement oui!! (Encore une fois ce genre de précision aurait dû arriver quand j'ai demandé ce qu'on faisait des doublons dans les codes) Le tout est de voir si tu t'y retrouves avec les formules proposées! Si elles ne te posent pas de problèmes, que tu te sens de taille à adapter au fur et à mesure de l'évolution de ton fichier et que les résultats fournis actuellement sont corrects, tu peux laisser les codes en l'état.
Ceci dit, il serait -de très loin- préférable que le code reste numérique (si tu rajoutes un "A" ... ce ne sera plus le cas!)

 

[loulourav]

Re : Données à transposer dans un nouveau tableau

Dans ce cas je vais supprimer les doublons, naïvement je pensais que c'était une sorte de "Transposé" des données, sans que les répétitions ou les caractères n'est d'importance

pour ce qui est d'éventuelle modifications, comment puis je modifier les formules contenues dans "Lib" et dans "codes"
edit : modif de type ajouter des lignes et/ou des colonnes dans le tableau source, ou si je décale le tableau source càd au lieu de démarrer en cellule A4, le tableau commencerai en cellule A2 par exemple

de plus j'ai complété le fichier source en entier, recopié les formules mais j'ai des #N/A et des #REF! sur l'onglet feuille 3
et les libellés ne correspondent pas aux codes
pour les doublons de codes dans l'onglet source le fichier contient les doublons de codes, dans l'onglet source(2) j'ai éliminer les doublons de cette manière :

code 1610 de la ligne 27 : supprimé
code 2040 de la ligne 45,46,47 : supprimé
code 8001 de la ligne 72 : fusionné avec la ligne 67

voici les opérations que j'ai faite à la main, est ce possible d'automatiser ca ? ce qui règle le problème des doublons ... ???

 

[Modeste]

Re : Données à transposer dans un nouveau tableau

je pensais que c'était une sorte de "Transposé" des données, sans que les répétitions ou les caractères n'est d'importance

Ah que nenni non point!
En 2 mots, les formules "classiques", en cas de doublons, ne trouveront que la première occurrence du code 1610 (et donc ignoreront superbement le service "néonatologie réa": dans ta feuille de "répartition" tu aurais 2 fois les chiffres de "néonatologie" et pas ceux de la "néonatologie réa"!) ... Tu me suis?

Maintenant, si tu supprimes les doublons dans les codes, il faut que tu le dises, parce qu'on peut alors certainement simplifier certaines formules (et se passer de la plage "LibTriés" !)

pour ce qui est d'éventuelles modifications, comment puis je modifier les formules contenues dans "Lib" et dans "codes"

Tu veux parler de la formule utilisée pour nommer les 2 plages? Si oui, il faudrait que tu précises quelles modifs tu ferais?? (note: s'il n'y a plus de doublons dans les codes, on n'a même plus besoin de la plage "lib", non plus!?)

Le mieux serait peut-être que tu déposes une version actualisée de la feuille "source" (et rien qu'elle!), avec les codes "dédoublonnés" et une explication -illustrée si possible- des modifs que tu veux faire.

Et, maintenant que tu commences à comprendre l'importance que ça peut avoir, essaye d'être le plus complet, le plus précis, le plus circonstancié, etc... D'accord, ça prend plus de temps, mais ça évite (une partie) des soucis

 

[loulourav]

Re : Données à transposer dans un nouveau tableau

j'ai édité mon message avec je pense les réponses à tes questions

merci en tout cas de ta patience et du temps passé à mon problème

 

[Modeste]

Re : Données à transposer dans un nouveau tableau

j'ai édité mon message avec je pense les réponses à tes questions

Pas certain que ça facilite les choses pour le lecteur!? ... d'autant que le nouveau fichier était là aussi (et pas vu tout de suite!)

Le lecteur attentif notera qu'une des premières choses que j'avais signalées était que j'avais supprimé les lignes vides de la feuille source. J'avais mentionné aussi que pour les deux plages nommées, ça ne fonctionnerait qu'à condition qu'il n'y ait pas de cellules vides en colonnes B et C.
Si ces vides sont aussi "incontournables" qu'ils semblent l'être on change la manière de nommer les plages (voir dans l'onglet Formules > Gestionnaire de noms)

Je te propose de vérifier très attentivement les résultats dans cette nouvelle version. En fin de compte, je n'ai conservé que les plages nommées "codes" et CodesTriés" (mais n'utilise que la première dans les formules)
Les formules sont revues elles aussi (simplifiées + correction de l'un ou l'autre message d'erreur évitable)

J'ai noté (avec satisfaction) que tu avais vu les "messages" en feuille CodesTriés, en cas de "dépassement des prévisions"

Comme précédemment, je n'ai recopié les formules que jusqu'à hauteur du changement de mois (pour alléger le fichier). Pour la même raison, j'ai supprimé les feuilles inutiles.

Une fois que tu auras vérifié, je répondrai à tes questions sur la formule pour la plage nommée "codes" (formule qui est différente de la précédente, donc!)

 

[loulourav]

Re : Données à transposer dans un nouveau tableau

je viens de vérifier les valeurs et ca fonctionne parfaitement !!
merci beaucoup pour ce super boulot !

par contre, si possible je veux bien un peu d'explication sur les formules afin que je comprenne comment elles sont construite et surtout si je dois un jour ou l'autre les modifier que je sache comment faire

Plusieurs formules contiennent:
=SI(LIGNES($1:1)<=NBVAL(codes)
j'ai du mal a saisir le rapport entre la ligne 1 et la formule NbVal des codes du tableau source ...

ok merci pour le gestionnaire de noms des formules, c'est qqch que je n'utilisais pas et qui est bien pratique finalement !

merci beaucoup en tout cas !

 

[Modeste]

Re : Données à transposer dans un nouveau tableau

je viens de vérifier les valeurs et ca fonctionne parfaitement !!

Allez, tant mieux!

=SI(LIGNES($1:1)<=NBVAL(codes)

Cette partie, au début de la formule permet en feuille "CodesTriés" de n'afficher un code que sur les 55 premières lignes, =NBVAL(codes) donnant 55, dans ton dernier exemple, mais 26 dans le précédent. Au-delà, la fonction PETITE.VALEUR donnerait un message d'erreur (#NOMBRE!) puisqu'on lui demanderait d'extraire la 56e valeur (qui n'existe pas, dans le cas présent).
Dans ce genre de situation, copie-colle des "morceaux de formule" dans des cellules vides, pour voir ce qu'elles donnent individuellement: ici, en feuille "CodesTriés", écris, en E1: =LIGNES($1:1). En F1, =NBVAL(codes) et en G1 =PETITE.VALEUR(codes;LIGNES($1:1)).
Recopie les trois formules jusqu'en ligne 60.

Comme ton fichier est au format '.xls', je n'ai pas osé utiliser une fonction Excel 2007 (et au-delà), qui aurait été plus courte et plus "intuitivement compréhensible".

Dans la feuil3, en colonne A, le même principe est utilisé pour n'afficher '2013' que sur une hauteur de 55 (codes) * 12 (mois) ... et plus rien au-delà, même si la formule est recopiée plus bas!

... C'est plus clair? N'hésite pas si ce n'est pas le cas!

 

[loulourav]

Re : Données à transposer dans un nouveau tableau

oui c'est un peu plus clair : merci !
je vais essayer de manipuler tout ca pour mieux les appréhender
par contre effectivement le fichier est en .xls, mais je travaille principalement sous 2007 voir 2010
donc si formule plus compréhensible il ya, je suis preneur !!
néanmoins le résultat actuel est très satisfaisant !
merci beaucoup

 

[Modeste]

Re : Données à transposer dans un nouveau tableau

Bonsoir,

Teste, en B1 de la feuille "CodesTriés":

=SIERREUR(PETITE.VALEUR(codes;LIGNES(Feuil3!$1:1));"")

... Le résultat devrait être identique à celui de la colonne A, MAIS impossible d'utiliser ce fichier sur un PC équipé d'Excel 2003 (ou antérieur).

Donc, réfléchis bien et, si tu as le moindre doute quant à l'environnement, garde la formule précédente (parce que travailler "principalement" avec 2007 ou 2010, c'est une chose, mais "exclusivement", c'en est une autre!) ... sur ces pensées profondes, je vous souhaite la bonne nuit