Recherche formule

Bonjour,

Je sollicite votre aide pour m'aider à trouver une formule.

Dans un fichier Excel, j'ai deux tableaux sur deux feuilles différentes. Dans le premier j'ai plusieurs colonnes (N° Dossier, Nom, Prénom, Nom + Prénom [valeur résultant d'une formule CONCATENER],...) et dans le second Nom + Prénom (tapé manuellement) et année de décès. Le premier tableau est trié sur le numéro de dossier, le second par défaut sur l'année de décès.

Je voudrais ajouter dans le premier tableau une colonne "année de décès" : je voudrais à partir de la colonne "Nom + Prénom" que la formule me trouve l'équivalence dans la colonne "Nom + Prénom" du deuxième tableau et me renvoie comme valeur l'année du décès si la formule est vraie, rien si la personne est toujours vivante.

La contrainte : mes deux tableaux ne sont pas triés par ordre alphabétique, et le premier tableau contient plus d'identités que le second.

J'ai testé la fonction "SI" qui me semblait la fonction la plus simple et la plus appropriée, mais je n'y arrive pas. J'ai fouillé un peu partout sur les forums Internet mais je ne trouve pas la solution.

Je vous remercie d'avance pour votre aide.

Salut et bienvenue sur le Forum,

Dans le fichier ci-joint, j'ai placé une formule que devrait répondre à ton attente. Comme je travaille sur un PC allemand, je ne peux pas inscrire cette formule sur ce fil et suis obligé de compter sur Excel afin de la traduire

Pour une prochaine fois, fournis-nous un bout de fichier, c'est quand même souvent plus facile

Cordialement.

Merci beaucoup Yvouille, la formule fonctionne parfaitement !

J'ai deux autres questions :

- Si je souhaite ajouter dans mes 2 tableaux le champ "Date de naissance" pour plus de fiabilité (cf. fichier joint), il faudra que je rajoute dans la formule le numéro de la cellule comme suit : SI(ESTERREUR(RECHERCHEV(D2:E2;Tabelle2!A:C;2;FAUX));"";SI(RECHERCHEV(D2:E2;Tabelle2!A:C;2;FAUX)=0;"";RECHERCHEV(D2:E2;Tabelle2!A:C;2;FAUX)))

C'est bien ça ?

- Pour mon test, j'ai fais un copier-coller du tableau n°2 à partir d'un tableau existant dans un autre fichier. Mais comme cet autre fichier est en constante évolution (ajout de nouvelles lignes liées à l'enregistrement de nouveaux décès), y a-t-il un moyen que la formule recherche directement sur ce fichier source sans passer par un tableau tiers (le tableau n°2) ? J'ai matérialisé ça par le fichier DamienP en pièce jointe.

En tout cas 1000 mercis pour ces premiers éléments de réponse.

Re,

Dans ton fichier Damien, que veux-tu réaliser ? Ces dates te servent à contrôler qu’en plus des noms et prénoms, la date de naissance correspond également sur les deux feuilles (et donc qu’il s’agit bien de la même personne) ou désires-tu reporter la date de naissance d’une feuille sur l’autre ? Dans ce dernier cas, également de la feuille 2 à la feuille 1 ???

Si je n’ai rien compris, place ta formule dans le fichier à l’endroit que tu souhaites - même si elle ne fonctionne pas - et je comprendrais peut-être mieux ce que tu désires.

Par la suite, lorsque ça fonctionnera comme tu le désires par rapport à deux feuilles, on pourra voir comment transformer cela afin de réaliser la même chose d'un fichier à un autre. Mais comme de telles liaisons apportent souvent des soucis complémentaires, réfléchis encore bien si tu ne pourrais pas tout simplement travailler qu'avec un seul fichier.

A te relire.

Tout à fait, si je souhaite ajouter les dates de naissances, c'est pour avoir une protection complémentaire contre le risque d'homonymie (les DN étant renseignées dans les deux tableaux).

J'ai tenté de bidouiller la formule (cf. pièce jointe), mais ça ne fonctionne pas.

L'intérêt pour moi d'utiliser un second fichier me permettrait d'éviter une double manipulation, celle d'entrer les futures données dans deux fichiers différents (car actuellement l'un me fait office de base de données de personnes décédées [tableau en constante évolution], l'autre de base de données pour accéder aux dossiers papiers de personnes en vie [tableau qui n'évoluera plus]). Le but premier de la formule étant de détecter sur les 30000 lignes que compte mon tableau le nombre de personnes décédées sur les 7000 identités dont j'ai eu confirmation de leur décès.

Est-ce assez clair ?

L'autre intérêt serait d'alléger le poids de mon fichier en enlevant un tableau afin d'éviter les lenteurs lors du recalcul de la formule ou l'utilisation de filtres. Après, si c'est trop complique, une bonne huile de coude pour tapoter les nouvelles données manuellement et le problème est réglé !

Par rapport à ma première question, je pense à une chose : en utilisant la formule CONCATENER pour inclure dans la même cellule les champs "Nom + Prénom" et "Date de naissance", à priori je n'aurais pas besoin de modifier la formule que tu m'as donné ce matin. J'essaierai ça.

J’ai également continué à chercher une solution à ton problème et je t’en fais quand même part.

Tout d’abord j’ai travaillé cette fois sur les deux fichiers ci-joints. Si jamais tu as des problèmes avec les références qui changent lorsque tu utilises ces fichiers depuis Internet, tu peux d’abord les enregistrés dans un seul et même dossier chez toi puis remplacer les formules des cellules F2, G2 et H2 par celle fournies aux lignes 11 à 13.

L’idée de mes différentes possibilités est que tu peux soit travailler avec les colonnes F et G qui te donnent des informations complémentaires, soit ne travailler qu’avec la colonne H si cette information condensée te suffit.

Tu as compris que dans tes essais avortés, le problème vient que tu ne peux pas effectuer une RECHERCHEV sur la base d’un groupe de cellule, du genre A1:B1.

Avec ton idée de concaténer le groupe nom+prénom avec la date de naissance, ça rejoint un peu l’idée de ma colonne H.

Cordialement.

J'ai testé les nouvelles formules, c'est nickel elles fonctionnent parfaitement !

Maintenant, il va falloir que je trouve une parade pour que le fichier n°2 s'ouvre plus rapidemment, car même si les formules sont enregistrées dans l'autre fichier, il me faut désormais environ 5 minutes pour l'ouvrir (Recalcul), alors que je l'utilise et l'alimente régulièrement.

En tout cas merci beaucoup, j'aurais été incapable de trouver cette formule. C'est du bon boulot.

Salut,

Damien P. a écrit :

Maintenant, il va falloir que je trouve une parade pour que le fichier n°2 s'ouvre plus rapidemment

Cordialement.

On peut toujours tester !

Il y a quand même une chose que je ne comprends pas. Pourquoi dis-tu que le Fichier 2 fait 5 minutes pour réactualiser les calculs alors que les formules sont dans le Fichier 1 ? Il me semble qu'il y a quelque chose d'illogique

As-tu les Fichier 1 ouvert au moment où tu ouvres le Fichier 2 ?

A te relire.

EDIT DE 19:10 :

Yvouille a écrit :

L’idée de mes différentes possibilités est que tu peux soit travailler avec les colonnes F et G qui te donnent des informations complémentaires, soit ne travailler qu’avec la colonne H si cette information condensée te suffit.

En fait, laquelle de ces deux solutions te convient le mieux ???

Bonjour Yvouille,

Désolé de répondre un peu tardivement, je viens de passer plusieurs jours à refaire le tableau 2 après avoir découvert qu'il était truffé de doublons en tout genres...

Je ne sais pas non plus pourquoi le tableau mettait autant de temps à s'ouvrir. Ce temps de réaction m'agaçait tellement que j'ai fini par rappatrier les 2 tableaux dans un seul et même fichier (retour à la case départ donc). J'en ai profité pour délester ce fichier des formules non essentielles (j'avais rajouté une autre page avec des statistiques calculées selon des formules NB.SI NB.VIDE et SOMME).

En refaisant la table n°2, j'ai eu l'idée de modifier la requête que tu m'avais trouvée en combinant uniquement le NOM (au lieu de NOM + Prénom) avec la date de naissance, celà me semblait une meilleure solution pour éviter le risque d'homonymie. Malheureusement, la requête s'arrête dès la première ligne où elle trouve le nom recherché, recoupe avec la date de naissance associée et me renvoit le résultat, résultat parfois erroné si une autre personne porte le même nom alors que la date de naissance diffère. Elle ne poursuit pas la recherche sur l'ensemble de la table n°2.

J'ai matérialisé ce problème en pièce jointe, le cas qui pose problème est "Nom 3". Est-ce qu'il est possible de forcer la requête à étudier l'ensemble d'un tableau avant d'envoyer à la table n°1 son résultat ? Si tu as la réponse à cette question, je suis tout ouï !

Et pour répondre à ta question, et comme tu pourras le constater dans le fichier joint, j'ai une préférence pour travailler avec la formule condensée (affichage de l'année de DC si DCD, vide si en vie ou "DN différentes" si les dates de naissances ne sont pas concordantes).

Encore une fois merci de t'intéresser à mon cas et à bientôt je l'espère.

Damien

Salut,

Damien P. a écrit :

..... Ce temps de réaction m'agaçait tellement que j'ai fini par rappatrier les 2 tableaux dans un seul et même fichier

Maintenant je n'ai aucune envie de chercher une solution pour ton fichier "à nouveau réuni" si par la suite tu vas dire que ce serait mieux de travailler avec deux fichiers séparés. Alors, si tu es toujours intéressé par mon aide, dans quelle direction définitive on se dirige ??

Cordialement.

Bonjour,

Si j'ai décidé de revenir à un système de deux tableaux dans un même fichier et de valider ce système, c'est parce que je me suis rendu compte qu'il était essentiel de simplifier au maximum la tâche pour une éventuelle alimentation par un utilisateur tiers. D'autre part, je me suis aperçu que j'avais initialement trop compliqué les choses et alourdi inutilement le fichier qui contient déjà bon nombre de lignes.

Désolé pour tous ces tâtonnements mais il faut dire que tout ceci est nouveau pour moi. Jusqu'à présent, j'utilisais Excel de façon plutôt sommaire, et là il fallait passer au stade supérieur pour cette tâche. Et si je me suis inscrit sur ce site, c'est parce que je ne trouvais pas de sujets succeptibles de répondre à mes attentes.

Les bases de mon projet sont désormais fixées (l'architecture correspond au fichier envoyé dans mon message précédent), je ne modifierai plus la trame. Libre à vous de vous pencher sur mon cas, sachez tout de même que je vous suis déjà très reconnaissant d'avoir répondu à mes premières interrogations, ça m'a permis de mettre sur pied une première solution que je n'aurais pu réaliser seul.

Cordialement,

Damien

Salut,

Damien P. a écrit :

En refaisant la table n°2, j'ai eu l'idée de modifier la requête que tu m'avais trouvée en combinant uniquement le NOM (au lieu de NOM + Prénom) avec la date de naissance, celà me semblait une meilleure solution pour éviter le risque d'homonymie.

Je ne vois pas très bien comment tu peux imaginer que la recherche soit plus précise avec le nom + la date de naissance plutôt qu’avec le nom + le prénom + la date de naissance !!??

Et je ne comprends pas non plus pourquoi tu veux effectuer cette recherche « au rabais » alors que tu as tous les éléments afin d’effectuer une recherche plus complète.

Il me semble assez normal qu’une formule recherchant « Nom 3 » s’arrête lorsqu’elle a trouvé « Nom 3 ».

Cordialement.

Yvouille a écrit :

Je ne vois pas très bien comment tu peux imaginer que la recherche soit plus précise avec le nom + la date de naissance plutôt qu’avec le nom + le prénom + la date de naissance !!??

En nettoyant la seconde table des doublons en tout genres, j'ai découvert un grand nombre d'erreurs sur la saisie des prénoms, notamment les prénoms composés ou bien ceux d'origines étrangères qui ont été comme par magie francisés (ex : Henriette dans la table n°1, qui devient Enriquetta dans la table n°2). Il faut bien avoir conscience que les deux tableaux proviennent de deux sources distinctes et je me dois bon gré mal gré faire au mieux avec sans altérer l'intégrité des données. Les champs NOM et Date de Naissance étant plus fiables, il y a plus de chances à ce que la formule me renvoie des résultats.

Yvouille a écrit :

Il me semble assez normal qu’une formule recherchant « Nom 3 » s’arrête lorsqu’elle a trouvé « Nom 3 ».

C'est justement la question que je me pose : une fois qu'elle s'arrête elle qu'elle a trouvé "Nom 3", mais que la date de naissance ne correspond pas, y a-t-il un moyen de relancer automatiquement la recherche qui s'arrêterait sur un autre "Nom 3" avec analyse de la date de naissance associée ? Et si vraiment rien ne concorde à la fin de la requête dans le tableau n°2, faire afficher le terme "DN Différentes".

Après c'est promis je n'ai plus d'autres questions.

Bonne soirée.

Salut,

Maintenant que tu donnes un peu plus de précisions sur ton fichier, on peut avancer Tu n’avais par exemple jamais dit que tu avais des prénoms fantaisistes.

Je te propose alors le fichier ci-joint qui contient une macro que tu peux déclencher en appuyant sur le bouton « Report » et qui effectue ceci :

A) Elle cherche tout d’abord une correspondance « Nom + Date de naissance » entre les deux tableaux et si elle trouve quelque chose, elle indique dans tous les cas la ligne du tableau 2 dans laquelle elle a trouvé la correspondance (en colonne G). Si en plus il y a une date de décès dans le tableau 2, elle est reportée sur le tableau 1 en colonne F et enfin, si le prénom trouvé dans le tableau 2 n’est pas identique à celui du tableau 1, il est indiqué dans la colonne H.

Ainsi, Papaux Madeleine est bien trouvée dans le tableau 2 à la ligne 3, mais elle n’a pas de date de décès et son prénom est correct ; Durant Jules est trouvé à la ligne 4 et à une date de décès ; Jaquemettaz Marc n’est pas du tout trouvé dans le tableau 2, ses colonnes F à H restent vides et Durant Henriette est trouvée dans le tableau 2 à la linge 8 mais son prénom est différent sur les deux tableaux ; le prénom trouvé dans le tableau 2 est donc indiqué en colonne H.

B) Ensuite cette macro cherche encore si des personnes avaient une correspondance « Nom + Prénom » exacte mais un date de naissance différente, auquel cas les informations trouvées seraient reportées dans les colonnes F et G, alors que la colonne H indiquerait la date de naissance différente trouvée. C’est par exemple le cas de Favre Julie.

Est-ce que ceci permet d’aller de l’avant ? Et dis-toi bien que tant que j’ai l’impression qu’on avance, ça ne me dérange pas du tout de t’aider.

A te relire.

Edit de mardi matin à 7 heures en Suisse :

Comme tu indiques dans l’un de tes messages que tu as presque 30'000 lignes, je t’informe que ma macro est limitée à des feuilles de 32768 lignes. Si vraiment l’un ou l’autre de tes tableaux dépasse ce nombre je devrais corriger mon code.

Les recherches effectuées se font sur des correspondances exactes. Si tu as par exemple des espaces inutiles dans tes cellules, ça peut fausser ces recherches. Est-ce qu’un « nettoyage » des espaces inutiles te paraîtrait une bonne chose ? Si oui sais-tu le faire ? Si non il serait peut-être bien que tu me fournisses quand même ton fichier réel. Si ça t’intéresse, écris-moi un message privé dans lequel tu inscriras ton adresse électronique privée et je prendrais contact avec toi de cette manière. De plus, éventuellement qu’en voyant ton fichier réel, j’aurais d’autres idées à te proposer.

Il se peut qu’avec un très grand nombre de lignes, ma macro rame passablement (jusqu’à plusieurs minutes ??). Comme je ne connais pas ta manière de travailler, j’ai créé ma macro de manière à ce qu’elle recommence tout le travail à chaque fois que tu la déclenches. Mais s’il s’agit de ne contrôler que de nouvelles entrées à chaque fois, on pourrait par exemple passer par-dessus le travail déjà fait au lieu de le refaire à chaque fois. A toi de me dire. D’autres gains de temps sont également envisageables si nécessaire.

Les informations reportées et les textes indiqués dans les colonnes F à H ne sont que des propositions. Si tu désires modifier une chose ou l’autre, tout est normalement possible.

Bonjour,

Je viens d'ouvrir le fichier ci-joint, le rendu est vraiment intéressant, mais alors autant pour les formules je sais me débrouiller, autant je suis un novice pour la création de macros.

Pour répondre à tes questions, je me suis déjà chargé du nettoyage des espaces inutiles dans les 2 tableaux. J'ai également supprimé tous les caractères accentués (é devient e, ö en o, j'ai remplacé les apostrophes par des espaces...) pour répondre aux critères internes actuels de saisie.

Pour information, je te donne une amorce de renseignements sur la trame exacte de mes deux tableaux dans le fichier ci-joint, ainsi que le nombre de lignes utilisées, et si ces tableaux sont susceptibles d'être complétés ou pas. Le cas échéant, je t'enverrais les données associés par mail, mais le fichier est assez volumineux (12 Mo actuellement avec la formule CONCATENER et celle que tu m'as déjà trouvé).

Il me semble intéressant que la macro se déclenche ensuite à chaque nouvelles lignes renseignées dans le tableau n°2 (Base DC), qu'elle passe au dessus du travail déjà fait comme tu l'évoques.

En tout cas merci pour toutes ces solutions et le temps passé sur ce sujet.

Cordialement,

Damien

Re,

Au sujet de ton fichier tellement lourd à cause, peut-être, des formules CONCATENER : Tu pourrais supprimer ces formules et remplacer leurs résultats par des valeurs. Peut-être que ça allégerait le tout ?? Sais-tu comment réaliser cette opération en une seule mutation ?

Sinon, le dernier fichier que tu me fournis n’est pas très utilisable car tu m’y donnes bien des informations intéressantes, mais tu y as enlevé les renseignements essentiels comme par exemple à quelle ligne exactement est placé le premier nom, quelles formules sont déjà en place et ne peuvent pas être remplacées par des macros, quelles colonnes sont encore vides, etc.

Je vois donc deux manières de continuer ce travail, si tu es toujours intéressé :

A) Soit tu me fournis un autre fichier exemple avec sur chaque tableau une dizaine de (faux-)noms représentatifs de toutes les lignes qui suivent (ensuite, qu’il y ait 10 ou 30'000 lignes, ce n’est pas trop important). Mais dans ces quelques lignes, tu ne laisses en place que les informations qui ne doivent pas être trouvées par ma macro. Ensuite, lorsque le fichier sera revu et corrigé jusqu’à te donner entière satisfaction, tu y enlèveras les données exemples et tu les remplaceras par tes données réelles.

B) Soit tu me fournis ton fichier réel et complet en privé et j’y place les codes nécessaires (et éventuellement j’y remplace, lorsque c’est possible, les formules par des valeurs) avant de te le rendre.

A te relire.

Damien P. a écrit :

Je me suis amusé à regarder l'incidence des formules dans le poids du fichier :

- les 2 tableaux seuls sans utilisation d'une quelconque formule : environ 4 Mo

- avec la formule CONCATENER : 1 Mo supplémentaire

- avec la formule que tu m'as trouvé pour la recherche sur le nom + Prénom et DDN : encore 7 Mo de plus.

La formule CONCATENER pourrait à terme être supprimée par l'emploi des macros car actuellement cette formule me sert uniquement pour le fonctionnement de celle que tu m'as donnée la semaine dernière.

Et je ne sais pas du tout comment on fait pour supprimer les formules et les remplacer par des valeurs. Si on procède ainsi, le calcul dans le tableau n°1 risque de ne plus se faire automatiquement si j'entre de nouvelles données dans le tableau n°2 ?

Sinon je suis toujours intéressé s'il y a moyen d'optimiser se sacré fichier. N'étant pas disponible jusqu'à vendredi, je tâcherai d'élaborer ce week-end un mini fichier représentatif basé sur le modèle que tu m'as fourni hier soir avec des vivants, des décédés, des homonymes, des identités doublonnées, des prénoms écrit différemment, des dates de naissances approximatives... Bref, avec pleins de subtilités qui font le charme de ce tableau.

Je te tiens au courant de l'avancée de mon travail.

Bonne soirée !

Merci de rester sur le Forum tant qu’il n’y a pas de raison de passer en privé.

Pour remplacer des formules par des valeurs, tu sélectionnes la plage désirée (dans l’exemple ci-joint, la plage C1:C7), tu la copies normalement, puis tu cliques droit sur cette plage et tu choisis « Collage spécial ». Là tu cliques « Valeurs » et tu confirmes. N’oublie pas de presser encore la touche « Esc » tout à la fin. Toutes les formules de la plage sont alors remplacées en une seule fois (même avec 29'000 cellules). Extrêmement pratique dans bien des cas.

Damien P. a écrit :

La formule CONCATENER pourrait à terme être supprimée par l'emploi des macros car actuellement cette formule me sert uniquement pour le fonctionnement de celle que tu m'as donnée la semaine dernière.

Pour le fichier que tu veux préparer, ne te casse quand même pas trop la tête. Si les exemples que tu veux me montrer correspondent à ceux que je connais déjà, ne te fatigue pas. Maintenant, j’ai surtout besoin de la configuration exacte de ton fichier.

A te relire.