XL 2016 recherche dans tableau

  • Initiateur de la discussion Initiateur de la discussion JJCODO
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

JJCODO

XLDnaute Nouveau
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
 

Pièces jointes

Solution
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
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
 

Pièces jointes

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
 

Pièces jointes

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
 
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
 

Pièces jointes

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
 
Dernière édition:
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
 
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,
 
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
 
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
 
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
 

Pièces jointes

Dernière édition:
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Réponses
4
Affichages
470
Réponses
1
Affichages
199
Réponses
1
Affichages
355
Réponses
8
Affichages
376
Réponses
22
Affichages
777
Réponses
3
Affichages
534
Retour