XL 2016 recherche dans tableau

[JJCODO]

Plus qu'un long discourt, je vous joins des tableaux : je souhaite passer de l'un à l'autre avec des formules et non en VBA.
Pouvez vous m'aider
Merci d'avance

 

[Hasco]

Bonjour et bienvenue sur le forum,

Lorsque vous rentrez chez quelqu'un que vous ne connaissez pas, vous ne lui dites pas bonjour ?

Si le problème n'est pas détaillé dans le message, beaucoup, comme moi, passent leur chemin.

https://www.excel-downloads.com/help/charte/

Cordialement

 

[JJCODO]

Bonjour,
Je souhaite modifier l'organisation des données d'un tableau.
C-à-d, récupérer le titres de colonne en fonction des données de certaines cellules et les rapatrier dans des cellules adjacentes. Je vous joins un fichier présentant le tableau d'origine et le tableau final que je souhaite obtenir tout ceci par des formules (sans Vba)
Merci d'avance de votre aide
Cordialement

 

[Hasco]

Bonsoir,

Vous avez excel 2016 et votre format de fichier est toujours .xls (format dépassé depuis 2007) aussi pour pouvoir utiliser les fonctions sierreur et agregat apparues depuis, le fichier est au format .xlsx

=SIERREUR(INDEX($C$3:$H$3;AGREGAT(15;6;COLONNE($C6:$H6)-2/($C6:$H6="X");COLONNE(C3)));SI(NB.SI($B14:E14;$H6)=0;$H6;""))

Cordialement

 

[JJCODO]

Bonsoir,

Vous avez excel 2016 et votre format de fichier est toujours .xls (format dépassé depuis 2007) aussi pour pouvoir utiliser les fonctions sierreur et agregat apparues depuis, le fichier est au format .xlsx



Cordialement

Merci pour cette rapide réponse.
En effet le fichier est xls. Je crée un outil de recherche pour une association qui le souhaite sous excel et dont certain membre sont encore en excel 97 2003 ! Je ne pense pas qu'AGREGAT fonctionne.
Si vous avez une autre solution, je suis preneur bien sur
Merci d'avance
Cordialement

 

[Hasco]

bonjour,

Voici, voici en formule matricielle à valider par CTRL+MAJ+ENTREE après avoir sélectionné C12:H12 (C12 étant la cellule active) :

=SI(ESTERREUR(SUBSTITUE(INDEX($C$3:$H$3;PETITE.VALEUR(SI($C4:$H4<>"";{1.2.3.4.5.6});{1.2.3.4.5.6}));"AUTRE";$H4));"";SUBSTITUE(INDEX($C$3:$H$3;PETITE.VALEUR(SI($C4:$H4<>"";{1.2.3.4.5.6});{1.2.3.4.5.6}));"AUTRE";$H4))

Par contre les cellule du tableau d'origine seront prises en compte si elles contiennent autre chose que "X" c'est à dire une valeur différente de "".

Cordialement

 

[JJCODO]

Bonjour Barbatruc,
Merci beaucoup de "coup de main".
Je vais pouvoir finaliser mon fichier mais avant tout il faut que je comprenne ta formule.

Bien cordialement

 

[Hasco]

Re,

Explication pour ligne 4

La formule cherche dans l'entête (ligne 3) le nom des langues pour lesquelles en ligne 4 il y a un X pour les ramener à la suite et dans l'ordre de leur de position {1.2.3.4.5.6}

Pour récupérer la langue qui pourrait être en H4 il faut substituer en ligne 3 le mot "AUTRE" par le nom de la langue en H4. (SUBSTITUE(C3:H3;$H3;$H4))

Petite.Valeur se charge de ramener les numéros d'ordre, dans la suite {1.2.3.4.5.6} pour lesquels il existe quelque chose en C4:H4 soit le tableau suivant :

{2.4.6.#NOMBRE!.#NOMBRE!.#NOMBRE!}

Si nous faisions =INDEX({2.4.6.#NOMBRE!.#NOMBRE!.#NOMBRE!};{1.2.3.4.5.6})
les erreurs seraient remontées. C'est pourquoi les versions excel antérieur à 2007 obligent à doubler l'écriture de la fonction pour la tester =Si(EstErreur(Fonction);"";Fonction)

J'espère que ces indications vous orienteront dans votre compréhension.

Une version un peu plus longue mais qui correspond plus directement aux explications:

SI(ESTERREUR(INDEX(SUBSTITUE($C$3:$H$3;$H3;$H4);PETITE.VALEUR(SI(SUBSTITUE($C4:$H4;$H4;"X")="X";{1.2.3.4.5.6});{1.2.3.4.5.6})));"";INDEX(SUBSTITUE($C$3:$H$3;$H3;$H4);PETITE.VALEUR(SI(SUBSTITUE($C4:$H4;$H4;"X")="X";{1.2.3.4.5.6});{1.2.3.4.5.6})))

cordialement

 

[JJCODO]

Hello,

J'ai appliqué la formule dans mon appli sans réflechir et cela fonctionne bien : IMPEC
Maintenant j'ai commencé à disséquer la formule, mais tes dernières explications sont plus que bien venues
Mais je m'entête car je veux bien tout comprendre (les retraités ont bien du temps !!!).
Merci encore à toi pour ton aide et au plaisir
Cordialement

 

[JJCODO]

Bonjour,
Je viens de terminer mon application avec vos conseils et cela fonctionne très bien.

Dans ce bout de tableau transmis, dans les formules, la matrice utilisée s'écrit {1\2\3\4\5\6}.
En réalité, j'ai 30 colonnes, donc j'ai continué l'énumération jusqu'à 30 et pas de pb.
Je risque de devoir étendre le nombre de colonne.
Peut on alors écrire la matrice sous une autre forme tel que A1: AD1 pour en faciliter l'écriture ?
Merci d'avance de votre réponse
Cordialement,

 

[Hasco]

Re,

Hélas, Non surtout avec un format .xls qui n'autorise qu'un nombre limité d'imbrication (j'ai testé).
Parcontre en mettant 1 à N au dessus de votre tableau: 1 à 6 dans les cellules $C2:$H2 de votre tableau d'origine. Vous pouvez faire :

=SI(ESTERREUR(PETITE.VALEUR(SI(SUBSTITUE(SUBSTITUE($C$4:$H$4;$C$4:$H$4;$C$3:$H$3);"AUTRE";$H4)<>"";$C2:$H2);$C2:$H2));"";INDEX(SUBSTITUE(SUBSTITUE($C$4:$H$4;$C$4:$H$4;$C$3:$H$3);"AUTRE";$H4);PETITE.VALEUR(SI(SUBSTITUE(SUBSTITUE($C$4:$H$4;$C$4:$H$4;$C$3:$H$3);"AUTRE";$H4)<>"";$C2:$H2);$C2:$H2)))

La formule a un peu changé par rapport à la dernière; celle-ci ne substitue pas les X mais en fin de compte elle est plus longue 😱
Cordialement

 

[JJCODO]

Re
OK comme je l'ai dis la précédente solution étendue fonctionne, mais la curiosité étant un vilain défaut alors je vais mettre en place cette nouvelle version histoire de me faire qqs noeuds aux méninges 😳🙄😵
Merci encore,
CDLT

 

[Hasco]

Re,

Que la lumière soit ! et la lumière fût :

=SI(ESTERREUR(PETITE.VALEUR(SI($C4:$H4<>"";COLONNE($C4:$H4)-2);COLONNE($C4:$H4)-2));"";INDEX(SUBSTITUE(SUBSTITUE($C4:$H4;$C4:$H4;$C$3:$H$3);"AUTRE";$H4);PETITE.VALEUR(SI($C4:$H4<>"";COLONNE($C4:$H4)-2);COLONNE($C4:$H4)-2)))

Sans avoir à mettre la suite de nombres en ligne 2 !
Le test d'erreur étant plus court, la formule est plus courte.

Cordialement

 

[JJCODO]

Re
je n'ai plus qu'à bosser ! Merci encore
CDLT