Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

[flagnico]

Bonjour,

J'ai un problème de taille dans Excel pour trouver des valeurs et les copier au bon endroit.
Je m'explique.

J'ai un classeur avec deux feuilles.
La première (fichier_complet) est un fichier général avec beaucoup de lignes et beaucoup d'informations.
La seconde (fichier_trié) est un fichier trié qui ne récupère que quelques informations.

La seconde feuille est remplie comme il faut, mais il me manque les numéros de téléphone, les fax et les emails qui se trouvent dans la première feuille. Et je ne sais pas comment faire pour qu'Excel vérifie que la société est la même dans les deux feuilles et fasse un copier coller de la bonne cellule de la première feuille au bon endroit dans la deuxième feuille.

fichier exemple : http://cjoint.com/?jkrFJITNv6

Sur le fichier exemple joint, il faudrait par exemple faire les opérations suivantes pour la "société A" :
- Dans la feuille 'fichier_trié, aller chercher dans la feuille 'fichier_complet la cellule G3 et la copier en H2
- Dans la feuille 'fichier_trié, aller chercher dans la feuille 'fichier_complet la cellule H3 et la copier en I2
- Dans la feuille 'fichier_trié, aller chercher dans la feuille 'fichier_complet la cellule I3 et la copier en J2

Et ainsi de suite pour toutes les sociétés de la feuille fichier_trié

Pensez-vous qu'il existe une solution automatique ou faut-il faire les recherches et les copier-coller à la main ? Il y a environ 700 lignes à compléter.

Un grand merci pour votre aide

 

[mth]

Re : Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

Bonjour,

Un essai en PJ en utilisant la fonction DECALER() (à condition que les noms soient uniques car ils servent d'identifiant, est-ce le cas?)

Bonne soirée,

mth

 

[flagnico]

Re : Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

Bonjour,

Merci pour la réponse.
Oui, les noms de société sont uniques et ils servent d'identifiant.
En revanche, le vrai fichier est un peu plus complexe (plus de colonnes) que le fichier exemple.
Comme cela a l'air de bien marcher, pourriez-vous expliquer la construction et le fonctionnement de la formule pour que je puisse l'adapter au bon fichier ?

Merci bcp.

 

[mth]

Re : Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

Bonjour flagnico,

Voici quelques commentaires sur la formule utilisée:

=DECALER([COLOR=teal][B]fichier_complet!$A$1[/B][/COLOR];[B][COLOR=blue]EQUIV(Fichier_trié!$A2;fichier_complet!$A$1:$A$10;0)-1;[/COLOR][COLOR=darkslateblue]6[/COLOR][/B];;)

Syntaxe de la fonction DECALER():
DECALER( Réf; Nombre de lignes; Nombre de colonnes; Hauteur; Largeur )

Réf: Cellule de départ à partir de laquelle le décalage doit être opéré.
Ici, nous partons de la cellule $A$1 de l'onglet "Fichier_complet"

Nombre de lignes: Nombre de lignes vers le haut (chiffre négatif) ou vers le bas (chiffre positif) dont la cellule supérieure gauche de la référence renvoyée doit être décalée.
Ici, le décalage est calculé par la fonction EQUIV():

EQUIV[COLOR=black](Fichier_trié!$A2;fichier_complet!$A$1:$A$10;0)-1[/COLOR]

Syntaxe de la fonction EQUIV():
EQUIV(valeur_cherchée, matrice_recherche, [type])

La fonction EQUIV recherche un élément spécifique dans une plage de cellules, puis renvoie la position relative de l’élément dans la plage.
Extrait de l'aide Excel:
valeur_cherchée: Obligatoire. Valeur dont vous voulez l’équivalent dans l’argument matrice_recherche. Par exemple, lorsque vous cherchez le numéro de téléphone d’une personne dans un annuaire, vous utilisez le nom de la personne comme valeur de recherche alors que la valeur que vous voulez obtenir est son numéro de téléphone.
L’argument valeur_cherchée peut être une valeur (nombre, texte ou valeur logique) ou une référence de cellule à un nombre, à du texte ou à une valeur logique.

Ici, on cherche l'équivalent du nom de l'entreprise, situé en colonne A de l'onglet "Fichier_trié": Fichier_trié!$A2

matrice_recherche: Obligatoire. Plage de cellules dans laquelle s’effectue la recherche.
Ici, la plage de recherche est la plage $A$1:$A$10 de l'onglet "Fichier_complet"

type: Facultatif. Nombre -1, 0 ou 1. L’argument type indique comment Excel compare l’argument valeur_cherchée aux valeurs de l’argument matrice_recherche. Valeur par défaut de cet argument : 1.
Ici, nous cherchons la valeur exacte, donc l'argument est 0

Afin de tenir compte de la première ligne de titre, on retire 1 au nombre renvoyé par EQUIV()
Par exemple, pour la première ligne, EQUIV(Fichier_trié!$A2;fichier_complet!$A$1:$A$10;0) renvoie 3, effectivement "Entreprise A" se trouve en ligne 3 de l'onglet "Fichier_complet", et on retire 1 pour ne pas compter la ligne d'en-tête.
​

revenons à la fonction DECALER() ...
Nombre de colonnes: Idem que pour les lignes.
Ici, la colonne "Téléphone standard" est en colonne G, c'est donc la 6ème colonne à droite après notre colonne de départ en $A$2

Hauteur: Indique la hauteur de la plage, en nombre de lignes (elle doit être impérativement supérieure à 0).
Ici, nous n'avons besoin que d'une ligne, la hauteur est donc 1, pris par défaut: au lieu de laisser vide entre les deux points virgule j'aurai pu mettre 1 (ce qui rendrait la formule plus lisible)

Largeur: Indique la largeur de la plage, en nombre de colonnes (la largeur doit également être supérieure à 0).
De même, nous travaillons sur un seule colonne, la Largeur sera donc égale à 1 (que j'aurais pu écrire)

Au global, par exemple pour la ligne 2 de l'onglet "Fichier_trié",

=DECALER(fichier_complet!$A$1;EQUIV(Fichier_trié!$A2;fichier_complet!$A$1:$A$10;0)-1;6;;)

revient à écrire

=DECALER(fichier_complet!$A$1;2;6;1;1)

soit on part de la cellule $A$1, on descend de deux lignes, on se décale vers la droite de 6 colonnes, on prend en compte 1 ligne et 1 colonne, et on trouve notre numéro de téléphone

Voilà, j'espère que ces commentaires pourront t'aider, bon courage pour ton travail et @ +

mth.

 

[Calvus]

Re : Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

Bonjour,

Je vais bosser un peu à la place de Mth, que je salue bien bas.

Comme cela a l'air de bien marcher, pourriez-vous expliquer la construction et le fonctionnement de la formule pour que je puisse l'adapter au bon fichier ?

Aide excel, fonction Decaler()

DECALER(réf;lignes;colonnes;hauteur;largeur)

réf est la référence par rapport à laquelle le décalage doit être opéré. L'argument réf doit être une référence à une cellule ou à une plage de cellules adjacentes ; sinon, la fonction DECALER renvoie la valeur d'erreur #VALEUR!.

lignes est le nombre de lignes vers le haut ou vers le bas dont la cellule supérieure gauche de la référence renvoyée doit être décalée. Si l'argument lignes est égal à 5, la cellule supérieure gauche de la référence est décalée de cinq lignes en dessous de la référence. L'argument lignes peut être positif (c'est-à-dire en dessous de la référence de départ) ou négatif (c'est-à-dire au-dessus de la référence de départ).

colonnes est le nombre de colonnes vers la droite ou vers la gauche dont la cellule supérieure gauche de la référence renvoyée doit être décalée. Si l'argument colonnes est égal à 5, la cellule supérieure gauche de la référence est décalée de cinq colonnes vers la droite par rapport à la référence. L'argument colonnes peut être positif (c'est-à-dire à droite de la référence de départ) ou négatif (c'est-à-dire à gauche de la référence de départ).

hauteur est la hauteur, exprimée en nombre de lignes que la référence renvoyée doit avoir. L'argument hauteur doit être un nombre positif.

largeur est la largeur, exprimée en nombre de colonnes que la référence renvoyée doit avoir. L'argument largeur doit être un nombre positif.




Le but : Décaler des cellules par rapport à une cellule de référence.
Ce qui nous intéresse ici, c'est de trouver des coordonnées par rapport à un argument qui est en colonne A (Société).

On pourrait donc dire Decaler(par rapport à A1; x nombre de lignes; x nombre de colonnes)

A1 est fixe
La fonction Equiv nous donne le numéro de ligne
La foction colonne nous donne le numéro de colonne


Voir le fichier joint pour explications détaillées pas à pas.
J'ai reproduit le tableau de la feuille 2 en feuille Fichier complet pour plus de lisibilité.

J'ai également adapté la formule de Mth, à qui je profite de demander, concernant la fonction Equiv, pourquoi elle a commencé sa matrice en A1 et ajouté ensuite "-1". Pourquoi ne pas commencer directement en A2 comme je l'ai fait. Y a t-il une raison particulière ?

J'ai également remplacé le "6" par la fonction Colonne()+5 pour que l'incrémentation puisse se faire automatiquement vers la droite.

Bonne journée.

EDIT: BLING BLING Bonjour Mth, je ne voulais pas empiéter mais apporter un coup de main, et peut être finalement que 2 explications valent mieux qu'une..
A bientôt

 

[flagnico]

Re : Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

Vraiment merci à tous,

Vous m'avez épargné des heures de travail rébarbatif (je m'apprêtais à faire des centaines de recherches, de copiers et de collers !).

Les fichiers joints et les explications m'ont permis de dupliquer cette formule sur le vrai fichier. Ca marche tout seul !

Je vais maintenant pouvoir passer au niveau 2 ! Même principe, mais on ajoute des contacts aux sociétés et on cherche à transférer les numéros de téléphone directs. Je n'ai plus une seule cellule de référence (la société), mais une plage de 1 ligne x 3 colonnes (société, nom, prénom). On fait donc une recherche avec un identifiant plus complexe, et quand celui-ci est trouvé (société, nom, prénom), on pourra faire le décalage des numéros de téléphone d'une feuille à l'autre.

Je vais essayer de faire cela tout seul, mais ce sera lundi maintenant, car les fichiers sont au bureau !

Encore merci pour vos précieux conseils.

 

[mth]

Re : Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

Sourire... bonjour Calvus

Beau travail d'équipe là et en plus on a ton fichier en prime, rudement bien !!

J'ai également adapté la formule de Mth, à qui je profite de demander, concernant la fonction Equiv, pourquoi elle a commencé sa matrice en A1 et ajouté ensuite "-1". Pourquoi ne pas commencer directement en A2 comme je l'ai fait. Y a t-il une raison particulière ?

Ben ... non, pas de raison particulière , les deux sont bien.

Très bon après midi à toi (arf, une fois n'est pas coutume, il fait aussi beau à Paris que chez toi )
et à bientôt flagnico

m

 

[Calvus]

Re : Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

Sourire... bonjour Calvus

Beau travail d'équipe là et en plus on a ton fichier en prime, rudement bien !!

Je te remercie.

(arf, une fois n'est pas coutume, il fait aussi beau à Paris que chez toi )

Ha ha ! c'est vrai que ce n'est pas coutume. Mais tu es la bienvenue si tu souhaites profiter de la mer et du soleil.
Bonne après midi à toi aussi.

 

[kifa]

Re : Recherche de valeurs dans deux feuilles différentes et copie au bon endroit

Salut au forum,

Je traînais dans ce site lorsque je suis tombé sur ces claires explications de l'indispensable fonction "décaler". J'ai beaucoup appris. Merci à mth, à Calvus ... et aux autres contributeurs de ce forum qui est une véritable caverne d'Ali Baba.