Transpose avec des liens

[julien]

bonjour a tous,

premier post sur ce forum, mais déjà bcp de lecture par le passé ) merci à tous.

En fait j'aimerais faire un transpose (j'ai la version US d'excel) tout en gardant les liens. ce qui de manière automatique est malheureusement impossible.

ce que je cherche à faire se rapproche d'une formule matricielle sauf que...

bref je vous laisse découvrir mon fichier excell d'exemple. l'objectif étant d'aprés de faire une pivot table puis d'utiliser des getdatapivottable.. enfin ca c'est une autre histoire ;o)

merci a tous.

julien

 

[Monique]

Bonjour,

Si tu voulais du matriciel, tu es servi !
Il y a quelques explications en bas de feuille.
Je ne suis pas sûre à 100% pour le report des noms, tu feras des essais.

 

[julien]

Je te remercie beaucoup je vais tester cela de suite.

 

[julien]

zut alors je crois que je me suis mal exprimé :/ vous trouverez toutes les infos dans le fichier numéro 2.

mais en gardant ta solution en premièr feuillet.

merci de m'aider

 

[Monique]

Bonjour,

On obtient le nom des matières par formule, cette fois-ci.
(4, ce n'est pas assez si tous les élèves ont la même matière)
Je ne réussis pas à me passer de la colonne des noms
(à peu près la même formule qu'en feuille 1, une condition en plus)
Ce sont encore des formules matricielles.

 

[julien]

monique you are a supastar !!

many thx.


julien

 

[julien]

en fait dans ta première réponse je ne comprenais pas dans la formule pour trouver les chiffres:

la présence du -9.

je ne saisis pas bien;


MERCI

 

[julien]

je suis vraiment désolé de vous embeter mais j'ai un soucis.

en effet, votre formule ne tient compte pas compte des 0,5 ou 0,75.

admettons que germaine à O,5 en note.

elle disparait automatiquement du tableau comment remédier à cela?

merci.


au fait pour le 9 j'ai compris ;o)

 

[Monique]

Re,

Chez moi ça marche.
J'ai mis des 0, des 0,10 et même des notes négatives, elles apparaissent toutes dans le tableau (feuille 1) et les noms aussi (feuille 2)

 

[julien]

tout d'abord bravo pour le boulot c'est impressionnant.

ensuite je ne comprends pas car pour la première colonne (celle des matières) ca va.

par contre aprés cela se gate. j'ai "value" de marquer. et malheureusement je ne sais pas d'ou ca vient. (j'ia pourtant bien validé avec ctrl shift enter)
Par logique dans la troisième case c'est la même chose.

QQ éléments qui vous éclaireront peut être:

mon tableau (réel) est un peu plus complexe puisque j'ai des colonnes intercalaires (par exemple entre la colonne des pays et celui des noms)
cela se fait sur des fichiers séparés
ce sont des % issus de liste déroulante lié à d'autres fichiers extérieurs.

je suis étonné. je ne comprends pas j'ai vérifié plusieurs fois mes formules.

voir ci dessous: (1, 2 et 3 colonnes respectifs) pour 2 ligne:

=IF(COUNTA(A$4:A4)>COUNT('Data EPG'!O2:AK1231),"",OFFSET('Data EPG'!$O$1,,SUMPRODUCT(1/COUNTIF(A$4:A4,A$4:A4))-1+IF(COUNTIF(A$4:A4,A4)>=COUNT(INDIRECT(A4)),1,0)))

=IF(A4="","",IF(A3<>A4,INDEX('Data EPG'!C2:C1231,MIN(IF(ISNUMBER(INDIRECT(A4)),ROW(INDIRECT(A4))))-ROW('Data EPG'!O1:AJ1)),INDEX('Data EPG'!C2:C1231,MIN(IF(ISNUMBER(INDIRECT(A4)),IF(MATCH('Data EPG'!C2:C1231,'Data EPG'!C2:C1231,0)>MATCH(B3,'Data EPG'!C2:C1231,0),ROW(INDIRECT(A4)))))-ROW('Data EPG'!O1:AJ1))))

=IF(ISNA(MATCH(C$3&$B4,'Data EPG'!A2:A1231&'Data EPG'!C2:C1231,0)),"",$B$4)

encore merci

 

[Monique]

Re,

Je n'ai pas la version anglaise et j'ai du mal à vérifier.
Pour utiliser la fonction Indirect(), tu as nommé des plages.
Est-ce qu'elles font bien toutes la même longueur, et est-ce qu'elles font la même longueur que A2:A1231 et C2:C1231 ,

Pour la 1ère formule
=IF(COUNTA(A$4:A4)>= ( >= au lieu de > ), il ne faut pas attendre que le nombre soit dépassé pour changer de matière
Le tableau : mettre des $ (ou mieux : lui donner un nom)
=IF(COUNTA(A$4:A4)>=COUNT('Data EPG'!$O$2:$AK$1231)
Le tout :
=IF(COUNTA(A$4:A4)>=COUNT('Data EPG'!$O$2:$AK$1231),"",OFFSET('Data EPG'!$O$1,,SUMPRODUCT(1/COUNTIF(A$4:A4,A$4:A4))-1+IF(COUNTIF(A$4:A4,A4)>=COUNT(INDIRECT(A4)),1,0)))

La 2è formule
Les $ manquent
Et la ligne O1:AJ1, il manque 1 colonne, ça doit être O1:AK1
Pour cette colonne, la 1ère cellule à gauche doit être située dans la même colonne que la 1ère note.
(mais c'est pour la feuille 1 que c'est important)
=IF(A4="","",IF(A3<>A4,INDEX('Data EPG'!C$2:C$1231,MIN(IF(ISNUMBER(INDIRECT(A4)),ROW(INDIRECT(A4))))-ROW('Data EPG'!O$1:AJ$1)),INDEX('Data EPG'!C$2:C$1231,MIN(IF(ISNUMBER(INDIRECT(A4)),IF(MATCH('Data EPG'!C$2:C$1231,'Data EPG'!C$2:C$1231,0)>MATCH(B3,'Data EPG'!C$2:C$1231,0),ROW(INDIRECT(A4)))))-ROW('Data EPG'!$O$1:$AJ$1))))

La 3è formule
La 1ère cellule, C$3, doit correspondre à A1:A1231
La 2è, $B4, doit correspondre à C2:C1231
Il faut que les 2 arguments aient le même ordre.
Pour B4, tu tapes $B4 pour copier sur tout le tableau
Pour les colonnes A2:A1231 et C2:C1231, tu mets $A$2:$A$1231 et $C$2:$C$1231
ça donnerait :
=IF(ISNA(MATCH(C$3&$B4,'Data EPG'!$A$2:$A$1231&'Data EPG'!$C$2:$C$1231,0)),"",$B4)

Tu devrais donner un nom à toutes ces colonnes et tableau et ligne A2:A1231 et C2:C1231 et O2:AK1231 et O1:AJ1
En plus, c'est plus facile à déchiffrer.
Lire 3 ou 4 lettres à la place de ça : 'Data EPG'!$A$2:$A$1231

 

[julien]

avant tout merci !!

je suis néanmoins totalement découragé. j'ai pourtant suivi vos conseils (j'ai nommé les lignes et les colonnes) mais je n'y arrive pas:

pour la première c'est la ligne 2 de données)

=IF(COUNTA(A$4:A4)>=COUNT(NOTE),"",OFFSET('Data EPG'!$O$1,,SUMPRODUCT(1/COUNTIF(A$4:A4,A$4:A4))-1+IF(COUNTIF(A$4:A4,A4)>=COUNT(INDIRECT(A4)),1,0)))


(pour les deux suivantes, ce sont la ligne 1 de données)

=IF(A4="","",IF(A3<>A4,INDEX(NOM,MIN(IF(ISNUMBER(INDIRECT(A4)),ROW(INDIRECT(A4))))-ROW(PARTNER)),INDEX(NOM,MIN(IF(ISNUMBER(INDIRECT(A4)),IF(MATCH(NOM,NOM,0)>MATCH(B3,NOM,0),ROW(INDIRECT(A4)))))-ROW(PARTNER))))

=IF(ISNA(MATCH(C$3&$B5,PAYS&NOM,0)),"",$B5)

je ne comprends pas....

si vous pouviez m'aider, je n'ai toujours que value value value value...

merci encore.

 

[Monique]

Re,

C'est pile pareil.
Reste un détail qui n'en est pas un.
La fonction Indirect() demande une orthographe identique entre le nom de la plage et le nom tapé dans la cellule
Il suffit d'un accent, d'un espace et ça ne fonctionne pas. (géo et geo, ça ne marche plus)

En dehors du problème des plages nommées, en vérifiant, il y a un espace après "math " et après "geo "
En feuille 2, si on ajoute un espace après le nom d'un pays, le prénom disparaît (mais il n'y a pas de signal d'erreur)
En feuille 1, si on enlève l'espace au titre de la colonne, on a #N/A
Si on l'enlève aussi aux titres des lignes, ça refonctionne.

 

[julien]

Merci opur vos conseils.

cependant j'ai le regret (enfin surtout pour moi) que je n'y arrive pas... le pire c'est que je ne vois pas d'ou peut provenir la faute... décidément.

je vous donne toutes les infos:

mes données:
NOM ='Data EPG'!$C$2:$C$1231
NOTE ='Data EPG'!$O$2:$AJ$1231
PARTNER ='Data EPG'!$O$1:$AJ$1
PAYS ='Data EPG'!$A$2:$A$1231

mes formules:

=IF(COUNTA(A$4:A4)>=COUNT(NOTE),"",OFFSET('Data EPG'!$O$1,,SUMPRODUCT(1/COUNTIF(A$4:A4,A$4:A4))-1+IF(COUNTIF(A$4:A4,A4)>=COUNT(INDIRECT(A4)),1,0)))

=IF(A4="","",IF(A3<>A4,INDEX(NOM,MIN(IF(ISNUMBER(INDIRECT(A4)),ROW(INDIRECT(A4))))-ROW(PARTNER)),INDEX(NOM,MIN(IF(ISNUMBER(INDIRECT(A4)),IF(MATCH(NOM,NOM,0)>MATCH(B3,NOM,0),ROW(INDIRECT(A4)))))-ROW(PARTNER))))

=IF(ISNA(MATCH(C$3&$B4,PAYS&NOM,0)),"",$B4)

sachant que pour les deux premières formules, je valide en faisant control, shift, enter.

je n'y comprends plus rien...

merci.

 

[Monique]

Bonjour,

Je n'y comprends rien non plus.
Est-ce que, quand tu valides par ctrl, maj et entrée, tu as bien 2 accolades qui entourent la formule ?
Dans la barre de formule, on voit : {=taformule(A1:A10)}
Je pense à ça, parce que, de temps en temps, Excel refuse de mettre les accolades quand je valide une matricielle.
Seule solution trouvée : fermer excel et ouvrir à nouveau

Autre chose : si tu as tant de lignes dans ton fichier, le temps de recalcul va être très très très long.
Il faudra cocher l'option "Calcul sur ordre".
De $C$2 à $C$1231, c'est un peu beaucoup pour ce genre de formules.

Autre idée : pourquoi ne pas refaire ton fichier à partir de celui qui fonctionne ? (le fichier du forum)