XL 2013 Recherche de données dans un autre tableau

[Thierry PRAT]

Bonjour à tous,
Je cherche à élaborer un fichier pour gérer une petite bibliothèque. Dans le fichier ci-joint, le premier onglet "Prêt", me sert à saisir les sorties et les retour des livres. Dans le deuxième onglet "Fiches Lecteurs", je peux faire apparaitre une fiche pour chaque lecteur, en utilisant la liste déroulante en B2. La fiche de chaque lecteur contient la liste des livres empruntés par ce lecteur (à partir de B6). Mon problème est le suivant : en D6 du deuxième onglet, je voudrais avoir la date de sortie du livre telle que mentionnée dans l'onglet "Prêt", colonne D. J'ai tout essayé, j’obtiens au mieux une date erronée, ou #N/A ou rien ! J'aimerais aussi, si c'est possible, que cette date reste valide même si je modifie le classement dans l'onglet "Prêt" Si vous pouviez me donner un petit coup de pouce, ce serait formidable !
Merci à vous et continuez longtemps, vous êtes trop forts.
Thierry

 

[djidji59430]

Bonjour,

un essai :

 

[chris]

Bonjour à tous

Quelques remarques : tu utilises des tableaux structurés mais n'en tire par partie.

Ils ne sont pas renommés et portent donc les noms non significatifs que leur donne Excel.

Ils comportent des lignes vides ce qui annule 95% de leurs avantages.

Des validations sont faites sur des colonnes entières alors qu'elles se propagent naturellement dans un tableau, de même que les formules et les MFC

Nombre de tes noms définis n'utilisent pas la notation tableau structurés qui les rendraient dynamiques et naturellement évolutifs et quand tu les utilises ce n'est pas toujours optimisé. D'autres font double emploi avec le nom du tableau.
​

Ce type de gestion est typiquement inspirée d'une base de données.
Access serait plus adapté surtout si plusieurs personnes risquent de l'utiliser en même temps.
Concernant Excel :

Je ne connais pas la taille de ta bibliothèque mais, si importante, les formules matricielles vont ramer.

Tu sembles avoir prévu du code qui ne figure par dans ton exemple.

On peut, par filtre avancé, extraire sans problème. Filtre qui peut être automatisé avec un peu de code...

Autre solution, installer l'addon PowerQuery (gratuit sur 2010 et 2013, intégré à Excel 2016) outil adapté à la gestion de données.
A ta disposition pour en reparler.

A noter que des titres de livres identiques peuvent exister avec des auteurs différents de même que les homonymes...
​

 

[Thierry PRAT]

Bonjour à tous,
Pour djidji59430 : merci beaucoup pour la rapidité de ta réponse. Il semble que c'est bien ce que je souhaitais faire. Le seul hic, c'est que le fichier envoyé était un fichier exemple, très proche du fichier réel dans sa structure, et que lorsque j'essaie de copier ta formule dans le vrai fichier, j'ai quelques difficultés. Je teste et je reviens vers toi pour te dire ce qu'il en est. Merci encore.https://www.excel-downloads.com/members/djidji59430.193645/

 

[Thierry PRAT]

Bonjour à tous.
Pour Chris : si tu as ouvert mon fichier, tu as peut-être pensé que j'avais quelques compétence en Excel, il n'en n'est rien ! Obtenir ce petit bout de fichier m'a pris un temps fou : recherche et suivi de tutos au pas à pas... J'ai découvert les "objets" tableau il y a peu de temps et, effectivement, je ne maitrise absolument pas leurs possibilités. En plus, mais ce n'était pas très clair de ma part, le fichier joint, bien que très proche du fichier réel, est un fichier exemple, réduit. Cependant, si tu peux m'expliquer plus en détail les remarques que tu m'a faites, je suis preneur.

 

[chris]

Bonjour

Pour les tableaux quelques explications et règles là Ce lien n'existe plus

Tu y trouveras

• le conseil de nommer le tableau
• le mode spécifique de sélection
• la syntaxe des formules tableau.
  De là découle la façon de nommer les colonnes ou autres sous-ensemble qui bénéficient de l'élasticité du tableau
• l'aspect élasticité :
  • qui ne fonctionne que si la taille du tableau est toujours conforme à son contenu
  • qui favorise la gestion des listes de validation puisque tout ajout ou suppression dans la colonne du tableau source ajuste automatiquement la liste

A cela j'ajouterais que

• il suffit de paramétrer la 1ère ligne sous l'en-tête : formats dont mise en forme conditionnelle, formules, validations ,... pour que toute nouvelle ligne hérite de ces paramètres.
• le tableau est bien adapté à la saisie où la récupération de données liées mais pas trop à l'extraction par formules, extraction dont on ignore a priori le nombre de lignes.

De façon générale Excel est avant tout un tableur, pas un gestionnaire de bases de données.

Cependant le filtre avancé, qui existe depuis très longtemps est la méthode la plus rapide pour extraire des données d'une table.

Le tableur ne sait pas faire de relations entre tables notamment, aspect majeur des bases de données relationnelles, relations nécessaires dans la gestion d'une bibliothèque.

Un classeur peut difficilement être utilisé par plusieurs utilisateurs simultanément. Si le mode partagé existe, il fonctionne mal, a de multiples restrictions, et n'accepte aucune des nouvelles fonctionnalités comme les tableaux (qui ont déjà plus de 10 ans !).

Dès que l'on est amené à gérer plusieurs tables en relation, le tableur est inadapté.
D'où mon conseil de te tourner vers Acces si tu le peux.
Tu indiques que ton classeur n'est qu'une ébauche. Plus il va s'étoffer plus l'écart entre tableur et gestionnaire de BD va s'accentuer.
Par exmple, si tu envisages des formulaires de saisie, il aisé de les réaliser dans Access alors qu'il faut tout programmer dans Excel...

C'est en raison de cet écart entre Tableur, dédié au calcul, et besoins en gestion de données, que depuis la version 2010, Microsoft a proposé, en add on, PowerQuery qu'il a fini par intégrer à partir de la version 2016.
C'est un outil capable de gérer des tables de données : requêtes et traitements des résultats.
Ce pourquoi je l'ai évoqué pour les extractions mais son rôle est d'exploiter les données, pas construire ni gérer.

 

[Thierry PRAT]

Bonjour à tous.
Pour Chris : merci pour toutes ces infos. J'ai bien conscience que Excel n'est pas vraiment adapté à ce que je cherche à faire et que Acces l'est beaucoup plus. Mais je ne possède pas Acces et pour l'avoir testé il y a de nombreuses années, j'en garde le souvenir d'une "usine à gaz", pas facile à paramétrer. Et puis, surtout, j'aimerai m'améliorer dans Excel. Merci de ton aide.

 

[Thierry PRAT]

Bonjour à tous.
Pour Djidji 59430 : ce que je craignais a eu lieu : alors que le tableau que j'ai fourni en exemple est un enregistrement de mon tableau réel, simplement "allégé" en supprimant des données mais dont la structure est identique, lorsque je transpose ta formule dans mon tableau réel, j’obtiens "#N/A". As-tu une idée sur l'origine du problème. Merci de ton aide.

 

[chris]

Re

...J'ai bien conscience que Excel n'est pas vraiment adapté à ce que je cherche à faire et que Acces l'est beaucoup plus. Mais je ne possède pas Acces et pour l'avoir testé il y a de nombreuses années, j'en garde le souvenir d'une "usine à gaz", pas facile à paramétrer.

Les systèmes de gestion de bases de données et les tableurs sont 2 outils très différents.

Une fois qu'on a compris la logique des SGBD, qui n'est pas si compliquée, on s'aperçoit que, si on a bien réfléchi et structuré sa base, tout roule naturellement et c'est alors la gestion d'une base de données dans un tableur qui semble une usine à gaz.

Formant les utilisateurs tant sur Excel que sur Access, nombre de ceux qui ont au préalable fait ce que tu envisages avant de passer à Access sont souvent sidérés par la facilité avec laquelle on y fait ce qui est compliqué et long à réaliser dans Excel.

C'était donc juste un conseil amical mais je ne fais aucun prosélytisme...

 

[djidji59430]

Bonjour à tous.
j’obtiens "#N/A". As-tu une idée sur l'origine du problème. Merci de ton aide.

Verifie que tes plages ont la,meme longueur

 

[Thierry PRAT]

Re


Les systèmes de gestion de bases de données et les tableurs sont 2 outils très différents.

Une fois qu'on a compris la logique des SGBD, qui n'est pas si compliquée, on s'aperçoit que, si on a bien réfléchi et structuré sa base, tout roule naturellement et c'est alors la gestion d'une base de données dans un tableur qui semble une usine à gaz.

Formant les utilisateurs tant sur Excel que sur Access, nombre de ceux qui ont au préalable fait ce que tu envisages avant de passer à Access sont souvent sidérés par la facilité avec laquelle on y fait ce qui est compliqué et long à réaliser dans Excel.

C'était donc juste un conseil amical mais je ne fais aucun prosélytisme...

Bonjour Chris,
loin de moi l'idée que tu fais du prosélytisme ! J'ai réessayé ACCESS et ce n'est franchement pas un succès. Je vais tenté de m'y remettre à temps perdu. En tout cas, merci pour ton aide.

 

[Thierry PRAT]

Verifie que tes plages ont la,meme longueur

Verifie que tes plages ont la,meme longueur

Bonjour djidji59430,
j'ai mis un peu de temps à comprendre de quoi il s'agissait, mais ça y est, ça fonctionne. J'ai même réussi (!) à transposer la formule dans la colonne "retour" de la fiche "livres" et dans les deux colonnes de la fiche "lecteurs". Il ne reste plus qu'un léger souci pour le cas (rare) où la même personne emprunte deux fois le même livre : j'ai alors l'affichage d'une date aléatoire et identiques dans les deux lignes et cette date se termine par l'année 2137. Vois-tu l'origine du problème et la façon d'y remédier ?
Merci pour ton aide.

 

[djidji59430]

Re
avec les formules c'est impossible de prendre 2 fois le meme livres. Avec les tcd, facile .....
Crdlmt

 

[Thierry PRAT]

Re
avec les formules c'est impossible de prendre 2 fois le meme livres. Avec les tcd, facile .....
Crdlmt

OK, merci. C'était juste par curiosité...