XL 2016 recherche dans tableau

JJCODO · 17 Mars 2021

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 · 20 Mars 2021

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

Hasco · 17 Mars 2021

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 · 17 Mars 2021

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 · 17 Mars 2021

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 · 17 Mars 2021

Roblochon à dit:
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 · 18 Mars 2021

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 · 18 Mars 2021

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 · 18 Mars 2021

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 · 18 Mars 2021

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 · 19 Mars 2021

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 · 19 Mars 2021

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 · 19 Mars 2021

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 · 20 Mars 2021

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 · 20 Mars 2021

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

XL 2016 recherche dans tableau

JJCODO

XLDnaute Nouveau

Pièces jointes

Hasco

Hasco

XLDnaute Barbatruc

JJCODO

XLDnaute Nouveau

Pièces jointes

Hasco

XLDnaute Barbatruc

Pièces jointes

JJCODO

XLDnaute Nouveau

Hasco

XLDnaute Barbatruc

Pièces jointes

JJCODO

XLDnaute Nouveau

Hasco

XLDnaute Barbatruc

JJCODO

XLDnaute Nouveau

JJCODO

XLDnaute Nouveau

Hasco

XLDnaute Barbatruc

JJCODO

XLDnaute Nouveau

Hasco

XLDnaute Barbatruc

Pièces jointes

JJCODO

XLDnaute Nouveau

Discussions similaires