Recherche formule Excel Résolu/Fermé

Question

Bonjour, 



Configuration: Windows / Edge 98.0.1108.55



J'ai besoin d'aide pour une formule.
Le premier onglet se nomme TABLES sur lequel les données progresses constamment. La première colonne contient des noms d'employés. 
Les 52 colonnes suivantes contiennent un nombre d'heures travaillées par semaine pour lesquels ces données sont entrer manuellement.

Les autres onglets (selon le nombre de noms des employés a la colonne A du premier onglet), seront nommé selon le nom de chaque employé.

Donc ma demande est : a la colonne C de chaque onglet, d'afficher la valeur  correspondante au nom de l'employé pour chaque employé pour le nombre d'heures par semaine.

Je mets le fichier pour bien voir ma demande. 

https://www.cjoint.com/c/LBuqTGA4YAY



Merci

Mike-31 · Answer

Bonsoir,

En C3 de chaque onglet colle cette formule que tu incrémenteras vers le bas

=INDEX(TABLES!$A$3:$P$50;EQUIV(STXT(CELLULE("nomfichier");TROUVE("]";CELLULE("nomfichier"))+1;20);TABLES!$A$3:$A$50;0);EQUIV((TEXTE(A3+6;"j mmmm")&" "&B$2)*1;TABLES!$A$3:$BB$3;0)) 
 

Tu peux écourter la formule en nommant tes plages de l'onglet TABLES, exemple la tableau A3:BB50 nommée Tablo, la plage des noms A3:A50 nommée Nom et la plage des dates A3:BB3 nommée Date ce qui te permettra de modifier dans le gestionnaire des noms tes plages et de ce fait toutes tes formules s'actualiseront

=INDEX(Tablo;EQUIV(STXT(CELLULE("nomfichier");TROUVE("]";CELLULE("nomfichier"))+1;20);Nom;0);EQUIV((TEXTE(A3+6;"j mmmm")&" "&B$2)*1;Date;0))

DjiDji59430 · Answer

Bonjour à tous,

https://www.cjoint.com/c/LBusAEKQFaY
avec transpose()

Crdlmt

Vaucluse · Answer

Bonsoir
votre fichier en retour, avec quelques explications en feuille TABLES
revenez si soucis 
 https://www.cjoint.com/c/LBusAjFtfYc
crdlmnt

Mike-31 · Answer

Re,

Non la formule donne la bonne réponse, sur le premier onglet tu as la formule normale et sur les autres onglets la formule avec les plages nommées

https://www.cjoint.com/c/LBusFMpVOrF

Mike-31 · Answer

Re,

prends ce fichier, je crois que je ne t'ai pas retourné le bon
https://www.cjoint.com/c/LBusSMYlbgF

Mike-31 · Answer

Re,

quelle formule as tu retenue, celle avec les plages nommées ou avec formule classique !

es ce que dans les autres onglets la formule fonctionne !

si tu as recopié la formule classique as tu respecté le symbole DOLLAR $

si ta matrice est exemple A3;R20 il est impératif que la liste des noms soit du même nombre de ligne A3:A30 comme pour le nombre de colonne, si ta matrice va jusqu'à R il est impératif que la liste des dates soit A3:R3

faisdlair · Answer

Bonjour Mike,

est-ce que je dois mettre cette formule a la cellule C3 de chaque onglet ?

Mike-31 · Answer

Re,

Dans ton premier onglet tu n'as pas modifié la première formule comme je t'ai expliqué sur le post 11

alors pour éviter les interférences efface la formule En C3 et colle celle ci dessous

=INDEX(TABLES!$A$3:$BB$50;EQUIV(STXT(CELLULE("nomfichier");TROUVE("]";CELLULE("nomfichier"))+1;20);TABLES!$A$3:$A$50;0);EQUIV((TEXTE(A3+6;"j mmmm")&" "&B$2)*1;TABLES!$A$3:$BB$3;0))

et idem dans chaque onglet efface C3 et colle la même formule

https://www.cjoint.com/c/LButTtU6JnF

Mike-31 · Answer

Re,

ça venait de l'identification du nom de l'onglet essye avec cette formule dans chaque cellule C3

=INDEX(TABLES!$A$3:$BB$50;EQUIV(STXT(CELLULE("nomfichier";A1);TROUVE("]";CELLULE("nomfichier";A1))+1;20);TABLES!$A$3:$A$50;0);EQUIV((TEXTE(A3+6;"j mmmm")&" "&B$2)*1;TABLES!$A$3:$BB$3;0))

Mike-31 · Answer

Re,

Oui c'est tout a fait ça, si tu as assez de 30 caractères il suffit de remplacer le 20 par 30 ou plus

=INDEX(TABLES!$A$3:$BB$50;EQUIV(STXT(CELLULE("nomfichier";A1);TROUVE("]";CELLULE("nomfichier";A1))+1;30);TABLES!$A$3:$A$50;0);EQUIV((TEXTE(A3+6;"j mmmm")&" "&B$2)*1;TABLES!$A$3:$BB$3;0))

Par contre après il faut que le nom soit exactement orthographié dans l'onglet TABLES, si tu as une erreur c'est qu'il y une erreur à ce niveau, souvent un espace qui nous échappe, le mieux est de faire un COPIER/COLLER du nom de l'onglet dans la colonne NOMS ou inversement

faisdlair · Answer

Bonjour Mike,

J'espère que tu vas bien, est-ce que vous pourriez m'aider encore une fois pour le fichier que vous m'aviez adapté en février 2022. J'ai un petit soucis avec celui-ci.

Merci et bonne journée

Mike-31 · Answer

Bonjour,

Explique déjà ce que tu veux faire ou modifier sur ce fichier,

Mike-31 · Answer

Re,

Je me suis permis de supprimer ton post contenant des fichiers hautement confidentiels.

Regarde en haut à droite de la discussion tu verras ton pseudo avec un petit rond rouge, clic dessus et Boite de réception je t'ai laissé un message à ce sujet.

Cordialement

Mike-31 · Answer

Re,

je me souviens de ton fichier, déjà le bouton IMPORT lié au code Sub QC_MR() ne veut rien dire, il prépare une copie sur une plage "A8:BC35" alors que ton tableau s'arrête à la colonne V, par contre il y a une montagne de ligne, puis il désactive le mode COPY pour recommencer, colle plusieurs fois les données pour finalement filtrer les données que sur une partie de la matrice.

que veux tu faire exactement avec ce bouton IMPORT, si je comprends ta demande, certainement copier toute la plage pour prendre en compte le rajout d'un nom en fin de tableau.

pour la deuxième question, certainement que oui, il suffit avec le bouton IMPORT des fichiers 2023 de coller les données mais çà a voir après avoir corrigé cette macro Sub QC_MR()

Mike-31 · Answer

Re,

Seul les fichiers et données confidentielles ne doivent transiter en clair, mais les discussions doivent rester ouvertes.

pour revenir à ton problème et avant de partir tête baissée, ton problème ne concerne que le fichier C2022

sous réserve d'avoir bien compris, tu veux ajouter un onglet ou supprimé et donc sur l'onglet TABLES tu veux que la liste des noms colonne A s'actualise et les données se filtrent en ordre croissant, c'est bien cela

je ne me souviens pas, les données de chaque agent en fin de chaque de semaine comment sont t'elles transposées sur l'onglet TABLES manuellement !

exemple pour la première ligne BENOIT il y a deux lignes, es ce nécessaire !

semaine du 3 décembre sur la 1ére ligne 3,00 et sur la 2éme 48,00 explique ce fonctionnement

faisdlair · Answer

Oui mon problème n'est que sur le fichier C2022. Les 2 autres sont correct.

Oui ajouter ou supprimer un onglet qui correspondent aux noms des employés. Oui je veux que la liste a la colonne A s'actualise et soit en ordre alphabétique aussi.

Oui les données sur chaque employé dans leur onglet correspondant sont entrées manuellement (Colonne D sur chaque onglet des employés)

Oui il y a des noms qui apparaissent sur 2 lignes car ces employés ont des données correspondantes sur les 2 autres classeurs. De combiner ces 2 lignes en une seule a l'onglet TABLES serait-il possible

Merci,

Mike-31 · Answer

Re,

En attendant ton retour, j'ai regardé tes formules et ce que je te proposais ne peut fonctionner et c'est normal d'avoir de #N/A ou des cellules vides parce qu'on est en circulaire, un peu comme le serpent qui se mord la queue

dans les colonne C de chaque agent tu as cette formule

=SI(NB.SI(Noms;$B$1)=1;INDEX(Tablo;EQUIV($B$1;Noms;0);EQUIV((TEXTE(A3+6;"j mmmm")&" "&B$2)*1;Dates;0));INDEX(Tablo;EQUIV($B$1;Noms;0);EQUIV((TEXTE(A3+6;"j mmmm")&" "&B$2)*1;Dates;0))+INDEX(Tablo;EQUIV($B$1;Noms;0)+1;EQUIV((TEXTE(A3+6;"j mmmm")&" "&B$2)*1;Dates;0)))

qui fait référence au tableau onglet TABLES parce qu'apparemment les heures travaillées de chaque agent colonne C proviennent du tableau TABLES.

il faut donc revoir tout le code VBA pour simplement afficher dans TABLES que les onglets visibles

enfin c'est ce que je comprends

faisdlair · Answer

Bonjour Mike,

Je comprends mieux avec ton explication maintenant, alors le VBA doit être revu aussi.

Oui ce que tu comprends est bon aussi

Merci,

Mike-31 · Answer

Re,

teste ce fichier voir ce qui est possible de faire, on verra plus tard pour l'adapter à ta sauce.

Si tu clic sur le bouton Masquer/Afficher

une boite de dialogue te propose de masquer ou afficher l'onglet et la ligne dans l'onglet TABLES

dans le cas ou tu rajouterais un onglet, une boite de dialogue te proposera de créer automatiquement la ligne dans l'onglet TABLES

dans le cas on un onglet est masqué et que tu crées une ligne au nom de cet onglet, il y a déséquilibre vu que, l'onglet est masquée et la ligne visible, il suffit de faire deux cycles Masquer/Afficher pour qu'ils se mettent en harmonie

le bouton filtre les données classe en ordre croissant le tableau

https://www.cjoint.com/c/MAhqviEWRGF

faisdlair · Answer

Bonjour Mike,

Je viens de tester et ca marche bien, j'ai crée un nouvel onglet et je l'ai nommé ALAIN. Ensuite avec le bouton MASQUE...., ca m'a permit d'ajouter une ligne. Et le bouton filtre a mis en ordre alphabétique.

Ce que je comprends, c'est que tu repars de zéro. Je te laisse continuer

Merci et bon Samedi

Mike-31 · Answer

Re,

en plus on peut ajouter un bouton pour filtrer également les onglets sauf dans l'exemple les deux premiers ce qui mettra en ordre croissant la liste de validation

https://www.cjoint.com/c/MAhsHeOcNRF

faisdlair · Answer

Je viens de tester aussi et c'est efficace.

Merci,

Mike-31 · Answer

Re,

pour continuer, lorsque tu clic sur le bouton IMPORT... in ouvre le fichier concerné et sélectionne une plage A8:BC35 qui correspond à une plage en plein tableau que veux tu copier de ce classeur, de quel onglet et ou !

et Classeur C2022 a quoi te sert l'onglet 00

faisdlair · Answer

Bonjour Mike,

La plage A8 a BC35 doit venir de l'onglet TEST des classeurs SUIVI CHANTIERS.

L'onglet 00 me sert seulement pour faire un suivi pour ceux qui n'ont pas d'heures accumulées, alors ne touche pas a celui-ci svp

Merci,

Mike-31 · Answer

Re,

J'ai du mal à comprendre ta logique, donc le bouton IMPORT doit activer le classeur SUIVI CHANTIER/l'onglet TEST, sélectionner la plage A8:BC35 COPIER

et la macro doit le copier ou !

et tu as TEST et TEST2 qui alourdi ton fichier a quoi servent ces deux onglets

parce que ton code ne veut rien dire et ne me donne aucune explication

faisdlair · Answer

Bonsoir Mike,

Les données  de A8 a BC 35 doivent être transférées a l'onglet TABLES vers les colonnes C a BB

TEST 2 n'est que pour Montréal et ne sera plus utilisé pour 2023. Donc seulement les valeurs des onglets TEST des 2 fichiers SUIVI doivent être ramenés au classeur C2022 a l'onglet TABLES.

Merci,

Martin,

Mike-31 · Answer

Re,

je comprends mieux pourquoi des agents pouvait être en doublon sur C2022

Donc pour être clair, tu clic sur le bouton IMPORT le code va sur le fichier suivi chantier_1 copie la plage A8:BC35 sur le fichier C2022 onglet TABLES, puis active le classeur suivi chantier_2 copie la plage A8:BC35 et la colle dans C2022 à la suite

et pour éviter les doublons il faut additionner les valeurs d'un même agent et supprimer les lignes en doublon

c'est bien ça pour n'en conserver qu'une !

faisdlair · Answer

Bonjour Mike,

Ton explication est juste et tu as bien compris ma demande.

Oui de fusionner les doublons et additionner les valeurs après l'importation, rendra plus simple les formules a l'onglet RECAP a la colonne A

Merci,

Mike-31 · Answer

Re,

Fusionner les doublons n'est pas un problème, le problème réside du fait que les données à récupérer dans les deux fichiers Chantier sont enregistré dimanche, lundi, parfois le mardi quand apparemment le lundi est férié, et ces données doivent être transcrite le samedi fin de semaine.

la question que je me pose est, les sommes exemple enregistré le Lundi 07 mars 2022 sur le fichier C2022 on les enregistre 05 mars ou le 12 mars

de toute façon avant de les copier sur le fichier de réception C2022 il faut déjà les transcrire en tableau.

Cette semaine ça va être chaud mais je regarderai dès que possible

faisdlair · Answer

Bonsoir Mike,

Pour regrouper les données des noms en double, sur la même ligne, tu mentionnes que c'est dimanche, lundi et mardi.

Ces jour affichés sont issue d'une formule qui indique le premier jour travaillé de cette semaine. Regarde la cellule C2.il est indiqué que chaque date a la ligne 3 correspond a la date finissant de cette semaine. Donc le 7 mars serait a la colonne L, soit la semaine finissant le 12 mars.

Je te souhaite une bonne semaine, qui semble assez occupée.

Merci encore,

Martin,

Mike-31 · Answer

Re,

pour répondre à ton questionnement :

je viens de tester et j'aime ca.  Pourquoi a chaque importation, ne pas supprimer a la place, les données a l'onglet TABLES du classeur C2022.

Les données importées sur l'onglet TABLES sont automatiquement supprimées après l'importation et additionnées aux données existantes.

le risque de problème peut venir du fait que si tu conserves les données déjà importées des onglets TEST des classeurs chantier ... si tu actives le bouton IMPORTER QC & MR & Cumul les données vont à chaque fois être additionnées sur l'onglet TABLES.

maintenant c'est toi qui voit,

faisdlair · Answer

Bonsoir Mike,

Les données qui sont importées de l'onglet TEST s'additionneront toujours a ceux actuelles a l'onglet TABLES, c'est ce que j'en comprends. Donc il faut que les nouvelles données écrasent les anciennes, ou bien les effacer avant d'importer.

Est-ce possible

Merci et bonne nuit

Martin.

Mike-31 · Answer

Re,

Donc en résumé sur l'onglet TABLES il faut effacer les données existantes, puis importer les données des classeurs CHANTIER qui s'additionnent.

Mais si tu suis cette logique tu vas créer des doublons avec les lignes masquées ou il faut supprimer toutes les lignes y compris les masquées avant d'importer les données CHANTIER !

faisdlair · Answer

Bonjour Mike,

Oui il faut effacer toutes les lignes y compris celles qui sont masquées. Et importer a nouveau pour ne pas avoir de données en double.

Merci,

Mike-31 · Answer

Re,

en ce qui concerne les nombres avec deux décimales, il te suffit de déprotéger l'onglet TABLES et sélectionner la plage C5:BB100 puis clic droit sur la sélection/Format de cellule/Nombre/Nombre et choisir le format souhaité.

Pour les formules de l'onglet RECAP, en A5 réécrire la formule =TABLES!A5

en B5 =SIERREUR(INDIRECT("'" & SUBSTITUE($A5;"_";" ") & "'!G55");"")

sélectionner A5 et B5 et incrémenter vers le bas exemple jusqU40 LA LIGNE 100

veiller que chaque onglet est reprotégé et enregistrer le fichier pour conserver les modifications

faisdlair · Answer

Bonsoir Mike,

Ok merci je fais les changements.

Merci et a bientôt

Mike-31 · Answer

Re,

Tu rencontreras également un problème avec tes plages nommées  NOMS et TABLO qui raccourcissent à chaque suppression des lignes en doublon ce qui est normal vu que tu supprimes des lignes à l'intérieur de ces plages ce qui génèrera les messages d'erreur  #N/A  sur tes formules

alors soit tu revois régulièrement ces deux plages ou on inclus dans la macro l'actualisation de ces plages nommées.

faisdlair · Answer

Bonjour Mike,

Je viens de voir dans le gestionnaire de noms, que les plages (tablo et noms) n'incluaient pas les bonnes pages. J'ai ajusté et ca fonctionne bien pour l'onglet RECAP.

Le plus pratique serait d'inclure ces 2 pages dans la macro

Merci,

Mike-31 · Answer

Re,

L'onglet RECAP fonctionne parfaitement, en A5 tu as cette formule

=TABLES!A5

en C5

=SIERREUR(INDIRECT("'" & SUBSTITUE($A5;"_";" ") & "'!G55");"")

ces deux formules incrémentées  au cas ou il y aurait une erreur, ensuite l'activation du bouton BANQUE permet de récupérer les données et pour ma part je ne vois pas d'erreur

faisdlair · Answer

Bonjour Mike,

Désolé pour ma réponse tardive. Je n'avais pas essayé le bouton banque, alors tout fonctionne bien maintenant.

Je te remercie infiniment

Mike-31 · Answer

Re,

Je crois que nous arrivons à la fin de la discussion que tu as relancé, le code VBA qui recrée les formules permet en outre refaire les formules dans le cas d'erreur mais surtout reconsidérer et actualiser les plages de travail qui sont régulièrement diminuées par la suppression des lignes en doublon sur l'onglet TABLES et donc génère des erreurs sur l'onglet RECAP.

Pour éviter ces erreurs, deux solutions ou on reconsidère les formules manuellement ou automatiquement par VBA solution que j'ai privilégier ou à la place de supprimer les lignes en doublon on efface le contenu et on se retrouve avec des lignes vides un peu partout comme un gruyère.

A la prochaine

faisdlair · Answer

Bonjour Mike,

Merci pour tout, j'apprécie vraiment

faisdlair · Answer

Bonjour Mike,

J'utilise ton fichier maintenant et c'est super, mais il y a une fonction qui ne fonctionne pas bien, alors peut tu me revenir en privé que je te retourne le classeur avec les infos nécessaires

Merci

Mike-31 · Answer

Salut,

Envoi toujours, si je peux, mais cette semaine ça va être plus que chaud

Mike-31 · Answer

Re,

J'ai jeté un oeil sur ton fichier,

l'adaptation du code à tes fichiers qui change de nom ne pose aucun problème en mettant en début du Module VBA trois constantes

Public Const Pilote As Variant = "C2023.xlsm" 
Public Const Chantier_MR As Variant = "SUIVI CHANTIERS MR 2023.xlsx"
Public Const Chantier_QC As Variant = "SUIVI CHANTIERS QC 2023.xlsx"

par contre tu as reconsidéré tous tes onglets agents ce qui pose problème, la cellule G55 est devenue J2

en plus tu as empilé les années et donc pour 2023 la cellule est J56 avec cette formule

=SIERREUR(RECHERCHE(9^9;G3:G217);"")

comme d'ailleurs J111 pour 2024 etc ...

cette formule rapatrie la dernière valeur de la colonne G donc  J2, J56, J111 etc ... affiche la même et dernière valeur de la colonne G, alors qu'en J2 la recherche devrait s'arrêter à la cellule G54, pour la cellule J56 la recherche devrait s'arrêter à G109 etc ...et pour adapter le code VBA c'est le même problème

en plus tu surcharges ton fichier, pourquoi ne fait tu pas un fichier agent par an

s ta colonne A tu as tous

faisdlair · Answer

Bonsoir Mike,

Merci pour le retour, je comprends tes explications, je regarde ca de plus près

Merci,

Recherche formule Excel

46 réponses

Discussions similaires