créer une liste à partir d'un tableau

[poussiere]

Bonjour,

Soit un tableau avec des en-têtes de lignes et de colonnes, et des valeurs à l'intérieur.
Je voudrais récupérer une liste dont le nombre de lignes est égal au nombre de valeurs du tableau (nb lignes x nb colonnes), et dont les 3 colonnes seraient :
valeur d'une cellule à l'intérieur du tableau, l'en tête de ligne qui lui correspond, l'en tête de colonne qui lui correspond.

Je ne trouve pas de solution simple à cette question, qui est en fait un peu l'inverse du tableau croisé dynamique...

Merci par avance de votre aide.

 

[rem$]

salut
je ne connais pas la solution mais elle m'interesse aussi.....

 

[Jean-Marie]

Bonjour Poussière, Rem$

Poussière, met un exemple pour nous aider à comprendre ton problème.

@+Jean-Marie

 

[poussiere]

Merci de vous pencher sur ma question !
je mets en pièce jointe une illustration de ma question, c'est effectivement plus facile pour comprendre avec un exemple.
Cordialement,
Poussière

 

[Jean-Marie]

Re...

Voilà ton fichier modifié

Cette fonction retourne le prénom
=INDEX($A$3:$A$7;1+ENT((LIGNE()-LIGNE(G$3))/4);1)

1+ENT((LIGNE()-LIGNE(G$3))/4) donne 1;1;1;1;2;2;2;2;3;3;3;3;4;4;4;4

Cette fonction retourne le trimestre
=INDEX($B$2:$E$2;1;MOD(LIGNE()-LIGNE(G$3);4)+1)

MOD(LIGNE()-LIGNE(G$3);4)+1 donne 1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4

Cette fonction retourne la valeur
=INDEX($B$3:$E$7;1+ENT((LIGNE()-LIGNE(G$3))/4);MOD(LIGNE()-LIGNE(G$3);4)+1)

Pour adapter à ton fichier modifie les plages des données, et l'emplacement de ta cellule de départ de ton tableau résultat.

@+Jean-Marie

 

[Monique]

Bonjour,

Une autre proposition.
Le 2è tableau s'adapte au nombre de lignes et de colonnes du tableau d'origine.
(Plus format conditionnel pour comparer plus facilement les 2 tableaux)

 

[Bernard]

Bonsoir Poussière, Rems$, Jean-Marie et Monique

Le même fichier En VBA.

Cordialement

Bernard

 

[poussiere]

Bonjour Jean-Marie, Monique et Bernard,

Merci à tous de votre aide !

Je ne sais pas me servir de vba, qui permet sûrement de faire des fonctions sophistiquées... Il faudra que je m'y mette.
En attendant, la solution de Monique a bien résolu mon problème, car elle s'adapte plus facilement à un tableau de départ de dimension variable que celle de Jean-Marie.

Je ne sais pas si c'est possible d'aller un peu plus loin...?
Dans mon tableau, un bon nombre de cellules sont vides ou à 0. L'idéal serait de ne récupérer dans la liste de destination que les lignes des cellules non vides ou non nulles.

Avez-vous une idée pour cela ? Merci encore !

Poussiere

 

[Monique]

Bonjour,

Ce n'est pas une réussite.
Ce sont les colonnes qui sont prioritaires et guident le reste,
je ne réussis pas à mettre la formule dans l'autre sens,
il ne doit pourtant pas manquer grand-chose.
Sinon, il ne faut pas de colonnes intermédiaires ni de lignes intermédiaires vides.

 

[poussiere]

Bonjour Monique,

Bravo et merci. L'ordre d'arrivée dans la liste m'importe finalement peu, que ce soit par colonne ou par ligne, et je peux préparer le tableau pour qu'il n'y ait ni ligne ni colonne complètement à 0.

C'est un peu trop fort pour moi, et j'ai peiné toute la soirée pour comprendre la formule matricielle, et l'adapter à mon cas. Je ne désespère pas...
mais quand même, je suis obligé de vous resolliciter.
Après avoir renoncé temporairement à appliquer dans mon propre tableau, je tente de vérifier que je suis capable de le faire à petit échelon dans le tableau que vous m'avez envoyé. Je m'aperçois que je ne comprends pas le fonctionnement et l'utilité de la formule matricielle... et que je ne sais même pas recopier !
Je la valide avec ctrl shift enter, mais le résultat n'est pas correct. Je remarque que je ne peux pas modifier une cellule isolée de ma matrice, alors que c'est possible dans celle que vous m'avez envoyée.
J'ai regardé en vain dans l'aide excel.

J'espère que vous pourrez encore m'aider.

Poussière

 

[poussiere]

Bonjour Monique,

le matin les idées sont plus claires...
dans ma matrice, j'avais copié la même formule sur toutes les lignes, c'est pourquoi cela ne marchait pas.
J'ai donc supprimé la matrice, collé la formule vers le bas, puis transformé chaque cellule en matrice en faisant modifier puis ctrl shift enter.
ça marche !
Mais cette façon d'entrer la matrice est franchement laborieuse. Peux-tu me dire comment entrer en matrice directement toute la plage de formules ?

Et en un mot, m'éclairer : que change au calcul le fait d'indiquer que c'est une formule matricielle ? Est-ce que c'est une question d'itération ?

Merci encore.

Poussière

PS
puisqu'il faut supprimer dans la base de données avant traitement les lignes et les colonnes qui ne contiennent que des valeurs nulles, peut-on créer une macro pour cela ? (C'est aussi une question qui me vient souvent quand je travaille sur des listes)

 

[Jean-Marie]

Bonjour Poussière

Je ne vais pas t'expliquer le fonctionnement des formules, je vais tant remettre une couche.

Pour ne pas avoir "les colonnes qui sont prioritaires", je suis passé par la fonction FREQUENCE qui transforme un tableau à 2 dimensions à un tableau à 1 dimension.

Pour diminuer les longueurs des formules, j'ai déclaré 4 noms de matrice.

Les longueurs des matrices, vont d'adapter au longueurs des plages Horiz, Vertic, Donnée.

Poussière il est préférable d'adapter ce fichier en déplaçant la liste en résultat, puis de coller les valeurs et pour finir de modifier les trois plages. Ce qui évitera de faire des erreurs dans la copie des formules des matrices. Un déplacer de feuille, n'est pas à faire, le lien des formules avec le fichier d'origine sera conservé.

@+Jean-Marie

 

[Monique]

Bonjour,

La formule nommée EqU1 ne faisait pas référence à la bonne cellule, c'est tout.
Tel que tu as disposé ton tableau, Eq fonctionnait bien.
Eq fait référence à la cellule situé sur la même ligne, 2 colonnes à droite.
Equ fait référence à la cellule situé sur la même ligne, 2 colonnes à gauche.

Pour copier-coller une formule matricielle, tu peux copier une première déjà validée et la coller directement, sans avoir besoin de valider chacune.
Sinon, pour t'expliquer ce genre de formule, j'aurais du mal.
C'est fait par petits bouts, par étapes, une formule courte au départ, puis un rajout, puis un autre,
on la met ensuite au conditionnel, on enlève les parenthèses inutiles, les 0 inutiles (dans Decaler() par exemple)
(j'ai souvent pensé que je devrais garder les étapes, mais...)

Pour donner un nom à une formule, tu peux regarder ici, le message de 13:49
<http://www.excel-downloads.com/html/French/forum/messages/1_108660_108660.htm>

 

[poussiere]

Bonsoir,
grand merci à Jean-Marie et à Monique, pour votre aide précieuse, vos explications, vos propositions de solutions, votre temps et votre gentillesse.
J'ai retenu et adapté à mon cas l'idée de Jean-Marie, qui m'est apparue plus simple à comprendre pour moi et qui n'impose pas l'absence de colonnes sans valeurs dans la base. Cela marche parfaitement bien !
Vos suggestions m'ont permis de découvrir des fonctions dans excel, que je ne connaissais pas.
Poussiere