Extraire à partir d'une valeur unique

[brutor2]

Bonjour à tous,

Je dois monter un classeur qui permettra d'importer sur 5 feuilles différentes des valeurs concernant des personnes répertoriés par 2 critères !
un numéro de 1 à 200
un nom

Donc mon problème c'est que cette importation de valeurs n'a pas de logique dans la chronologie des numéros.

En effet, dans le premier tableau, on peut trouver des numéros de 1 à 10, mais aussi de 20 à 25, ...
Dans mon deuxième tableau, on pourra trouver 11 et 12 et 30 à 36
Dans mon troisième tableau 160 à 200.
Etc...
Or ce chiffre est unique.

Je voulais donc reporter l'ensemble de ces données dans un dernier tableau récapitulatif numéroter de 1 à 200 suivi du nom.

Pour obtenir toutes les informations lier au numéro dans un tableau, j'ai trouver la formule suivante :
=INDEX(Personnes.xls!Nom;EQUIV(1;Personnes.xls!Numero;0))

ou Nom est le nom de la colonne contenant les noms et Numero la colonne contenant le numéro de la personne et 1 la valeur du numéro.

Cela marche bien si mes numéros sont définis à l'avance dans une des feuilles.

Mais si mes numéros se retrouvent dans n'importe quelle feuille, je ne sais pas comment développer cette formule.

En fait dans mon tableau récapitulatif, je souhaiterai adapter ma formule pour retrouver le numero 1 d'une des 5 feuilles et le nom qui va avec et ainsi de suite jusqu'à 200.

J'espère avoir été assez clair et surtout que vous pourrez m'aider.

Merci à tous.

Brutor2

 

[jeanpierre]

Re : Extraire à partir d'une valeur unique

Bonjour brutor2, Monique l'excellente, Tibo pas moins non plus,

brutor, ton fichier est toujours aussi lourd.

J'ai tenté hier de supprimer les lignes dans les feuilles 001Ax, mais je me suis fait jeter d'excel, donc, je ne recommence pas.

Il te faudrait, dans ce fichier et pour chapque feuille, selectionner la première ligne vide et la touche Ctrl enfoncée, descendre jusqu'à la ligne 65536, clic droit et simplement supprimer. Tu vas gagner en poids et si ce n'est pas suffisant, fais pareil avec les colonnes...

Je n'ai pas encore regardé la formule qui me semble bonne en lecture.

Bon dimanche.

Jean-Pierre

 

[Monique]

Re : Extraire à partir d'une valeur unique

Bonjour,

En colonne A de la feuille des présents, ce sont des numéros qui sont obtenus par formule
C'est vrai que ces n° existaient déjà dans ton fichier mais tous, absents ou présents
Maintenant, il y a le n° des présents et c'est tout, les n° absents ont un 0
C'est le même résultat que ce que tu as fait en colonne E
Tu peux supprimer carrément la colonne A et la formule de la feuille des n° libres fonctionne en se basant sur la colonne E
Mais avec une autre formule
En E3 : =SI(B3="";0;LIGNES(E$3:E3))

"Peut-on adapter tous ce que tu as fait en prenant en référence le fait que la cellule nom est vide en col B"
Je ne sais pas. Tout ce que je sais, c'est que j'ai essayé, sans succès
J'aurais bien aimé faire le contraire de ce que tu dis : extraire les noms à partir des n° > à 0 de la colonne A
mais Equiv(), Decaler() et les autres ne veulent pas fonctionner en 3D

La formule de la colonne A va chercher tous les n° (présents ou libres) dans les 3 feuilles
Si elle trouve le n° (pour A9 par exemple, c'est le n° 7)
elle cherche le nombre LIGNE(A$3:A9) par cette partie :
=SOMMEPROD(NB.SI(INDIRECT(NomOnglets&"!B4:B200");TEXTE(LIGNES(A$3:A9);"0")))
Cette partie de la formule donne 1 si le n° existe (en espérant qu'il n'y ait pas de doublons)
sinon, elle renvoie 0
Le 1 ou le 0, multiplié par LIGNE(A$3:A9) donne 7 ou 0

Ce sont ces 0 que va chercher la formule de la feuille des N° libres
=PETITE.VALEUR(SI(NB.SI(NoTous;LIGNE(INDIRECT(B$3&":"&B$4)))=0;LIGNE(INDIRECT(B$3&":"&B$4)));LIGNES(B$5:B7))
LIGNE(INDIRECT(B$3&":"&B$4)) représente une matrice de x n° dont les bornes sont en B3 et B4
LIGNE(INDIRECT(31&":"&36)) renvoie les nombres de 31 à 36 en simulant une plage comme ça ={31;32;33;34;35;36}

PETITE.VALEUR(SI(NB.SI(NoTous;{31;32;33;34;35;36})=0;{31;32;33;34;35;36});1)
renvoie le plus petit de ces nombres de 31 à 36 n'existant pas dans la plage "NoTous"
PETITE.VALEUR(SI(NB.SI(NoTous;{31;32;33;34;35;36})=0;{31;32;33;34;35;36});2)
renvoie le suivant non présent dans la plage "NoTous"
PETITE.VALEUR(SI(NB.SI(Plage;Critère);1 puis 2 puis 3, etc)

S'il y a le nom des onglets dans une liste nommée, c'est parce que la formule de la colonne A en a besoin
NB.SI(INDIRECT(NomOnglets&"!B4:B200"))

Des formules en 3D ici :
Ce lien n'existe plus

 

[Monique]

Re : Extraire à partir d'une valeur unique

Re,

Je reviens sur ce que j'ai dit
Ne pas supprimer la colonne A, la formule de la colonne B en a besoin...
Sinon, on peut extraire les n° absents en se servant de la plage "NomTous"

 

[Monique]

Re : Extraire à partir d'une valeur unique

Re,

Les numéros absents de l'onglet "N° Présents"

Au choix :
Selon les 0 des colonnes A ou E (A dans le fichier joint)
Selon le contenu de la colonne B

 

[brutor2]

Re : Extraire à partir d'une valeur unique

Bonsoir Monique, bonsoir Jean-Pierre, Bonsoir Tibo

Jean-Pierre Je vais essayer ta méthode pour alléger mon fichier... Merci

Monique, je te remercie pour ces explications très détaillées.
Je vais étudier cela de près...

Merci à tous les 3 je vais me coucher moins idiot ce soir...

Brutor