> Tous les forums > Forum Bureautique
 recherche date selon plusieurs critèresSujet résolu
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
manekineko
  Posté le 15/05/2019 @ 15:31 
Aller en bas de la page 
Petite astucienne

Bonjour,
Je pense avoir essayé toutes les solutions possibles et mon problème n'est toujours pas résolu.

Je remercie d'avance les personnes qui voudront bien se pencher sur mon cas.

J'essaye de trouver la formule sur Excel pour obtenir la date d'entrée la plus ancienne (colonne Q) et la date de sortie la plus récente (à partir de 2018, mais qui peut aller au-delà) (colonne T) pour chaque matricule.
La première feuille de calcul est un export de données, je ne souhaite donc aucun traitement ni formule sur cette feuille. Les calculs et recherches se font sur la seconde feuille. Le même matricule peut avoir plusieurs dates, qu'il faut donc aller chercher sur la première feuille excel.

Précision : la date d'entrée la plus ancienne doit être sur 2019 en cas d'entrée en 2019, ou sur les années précédentes si la personne n'est pas entrée en 2019...

Voici le lien pour voir l'exemple du fichier : https://www.cjoint.com/c/IEpnzzUXNCO

Merci pour votre aide.

[Configuration automatique à compléter]
Windows 10
Chrome 74.0.3729.157



Modifié par manekineko le 16/05/2019 23:27
Publicité
Debrief
 Posté le 17/05/2019 à 11:01 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Bonjour,

Le problème pour cette formule c'est qu'elle doit faire une recherche pour extraire une matrice (lignes correspondantes au matricule) puis rechercher in MIN et un MAX dans cette matrice avec en plus une condition sur l'année).

Personnellement je ne connais pas de formule de recherche rendant une matrice.

Je peux t'écrire un Macro qui fait ça si tu veux. Soit une fonction personnalisée appelée dans la cellule, soit un une fonction qui ne s'exécuterait que sur déclenchement explicite (bouton,...)

Cordialement,
D.

E-L
 Posté le 17/05/2019 à 11:12 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

salut,

J'ai regardé un peu…..Il faut garder que le filtre sur le nom et non sur toutes les colonnes et mettre le tri de AàZ pour avoir les noms groupés.

Ajouter deux colonnes supplémentaires pour les dates d'entrée et de sortie. Mettre ces colonnes au format nombre pour pouvoir utiliser les formules Max et Min.

c'est le principe .

manekineko
 Posté le 19/05/2019 à 14:19 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne
Debrief a écrit :

Bonjour,

Le problème pour cette formule c'est qu'elle doit faire une recherche pour extraire une matrice (lignes correspondantes au matricule) puis rechercher in MIN et un MAX dans cette matrice avec en plus une condition sur l'année).

Personnellement je ne connais pas de formule de recherche rendant une matrice.

Je peux t'écrire un Macro qui fait ça si tu veux. Soit une fonction personnalisée appelée dans la cellule, soit un une fonction qui ne s'exécuterait que sur déclenchement explicite (bouton,...)

Cordialement,
D.

Bonjour,

Je vous remercie de votre proposition : je souhaitais éviter les macros pour ne pas alourdir mon fichier, mais s'il n'y a pas d'autre solution, je prends.
J'ai une préférence pour la fonction appelée dans la cellule.

Merci beaucoup !

Debrief
 Posté le 19/05/2019 à 14:37 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Précision : la date d'entrée la plus ancienne doit être sur 2019 en cas d'entrée en 2019, ou sur les années précédentes si la personne n'est pas entrée en 2019...

Peut-on remplacer dans cette phrase "2019" par <nom de la 1ère feuille> pour généraliser ? -> Non, ce sera passé en paramètre de la macro !

Autre question: Marticule de la 1ère feuille est le Numéro de la 2ème feuille ? (je suppose que oui)

Autre question: dans tes formules (ex pour le genre =RECHERCHEV($A4;données2019;7;FAUX)) tu recherches sur le n° de ligne. Tu ne préférerais pas chercher sur le Matricule / Numéro ?

Autre question: toutes les dates de la feuille source sont en format Texte (précédée de '). Veux-tu conserver ce format dans le résultat ou bien avoir un vrai format Date qui permette tri, classement, filtrage etc... ? (je te recommande Date)



Modifié par Debrief le 19/05/2019 15:00
manekineko
 Posté le 19/05/2019 à 15:00 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Je vais tenter de répondre de la manière la plus claire à cette question :

La première feuille ne se nomme pas "2019", elle comporte toutes les données nécessaires pour remplir une feuille de travail qui elle-même alimente des feuilles et des tableaux d'analyses. A chaque nouvelle extraction pour mise à jour, les données extraites sont copiées telles quelles sur cette première feuille. Donc toutes les vieilles données disparaissent et sont remplacées par la nouvelle liste. Voila pourquoi je n'applique aucune formule sur cette feuille. J'ajoute juste la première colonne "N° ligne" qui ne modifie aucune donnée.

La seconde feuille est la fameuse feuille de travail, celle qui traite les données avec des formules et des tris. Pour éviter les incohérences dans les recherches et regroupements, je regroupe les lignes selon le n° insee (car propre à chacun, aucun risque de doublon). Je suis obligée de garder le matricule, mais certains ont un matricule secondaire. J'ai donc une formule pour attribuer un "n° de ligne unique" pour chaque numéro insee malgré la différence de matricule. Peu importe le n° de ligne attribué, il suffit qu'il n'y ait pas de trou.

Ce numéro de ligne est la première colonne de la 3° feuille (celle qui analyse) : du coup, je tire les numéro de 1 à XXX et les données apparaissent sans trou et sans erreur. Ce numéro sert à connaitre le nombre de personnes traitées et c'est le point de départ des recherches sur la feuille de travail. Et surtout, il n'y a pas de doublon, car cette référence regroupe les données.

J'espère avoir réussi à bien expliquer...

Debrief
 Posté le 19/05/2019 à 15:26 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

La première feuille ne se nomme pas "2019"

Ok, mais bon ça n'a pas d'importance. C'est vrai qu'il y a des feuilles masquées avant.

je regroupe les lignes selon le n° insee (car propre à chacun, aucun risque de doublon)

Tu as surement une bonne raison de ne pas avoir utilisé directement le n° INSEE unique et créé un n° de ligne qui correspond au n° INSEE (si j'ai bien compris), et cette raison est d'avoir des n° en séquence (si j'ai bien compris).

Donc, le critère de match commun aux 2 feuilles entre les 2 feuilles (2019 et FTrav 2019) pour identifier 1 personne (pour la recherche des dates d'entrée et de sortie) est donc le le n° de ligne. Tu confirmes ?

manekineko
 Posté le 19/05/2019 à 15:38 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne
Debrief a écrit :

Ok, mais bon ça n'a pas d'importance. C'est vrai qu'il y a des feuilles masquées avant.

Désolée, je viens de comprendre... si c'est pour la formule, je peux nommer cette feuille "2019". Le nom de feuille ne correspond pas car j'ai anonymisé mes données !

je regroupe les lignes selon le n° insee (car propre à chacun, aucun risque de doublon)

Tu as surement une bonne raison de ne pas avoir utilisé directement le n° INSEE unique et créé un n° de ligne qui correspond au n° INSEE (si j'ai bien compris), et cette raison est d'avoir des n° en séquence (si j'ai bien compris).

OUI !!! oui ! oui !

Donc, le critère de match commun aux 2 feuilles entre les 2 feuilles (2019 et FTrav 2019) pour identifier 1 personne (pour la recherche des dates d'entrée et de sortie) est donc le le n° de ligne. Tu confirmes ?

Oui, pour l'instant. Je pourrais éventuellement faire les recherches par rapport au n° insee, ce serait certainement mieux, mais ces numéro de lignes définissent aussi des plages dynamiques. Car à chaque mise à jour, il y a des lignes en plus.

Debrief
 Posté le 19/05/2019 à 15:45 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Et aussi je ne comprends pas bien ton assertion:

Précision : la date d'entrée la plus ancienne doit être sur 2019 en cas d'entrée en 2019, ou sur les années précédentes si la personne n'est pas entrée en 2019...

Est-ce que tu veux dire :

- Entrées 01/05/2017, 05/03/2018 -> on prendra 01/05/2017

- Entrées 01/05/2017, 05/03/2018, 01/02/2019, 01/07/2019 -> on prendra 01/02/2019

?

Publicité
Debrief
 Posté le 19/05/2019 à 15:49 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Je pourrais éventuellement faire les recherches par rapport au n° insee, ce serait certainement mieux

A mon humble avis ce serait effectivement mieux, il y a toujours moyen de s'en sortir pour les plages dynamiques, mais tu as fais tes choix en connaissance de cause.

Autre question: toutes les dates de la feuille source sont en format Texte (précédée de '). Veux-tu conserver ce format dans le résultat ou bien avoir un vrai format Date qui permette tri, classement, filtrage etc... ? (je te recommande Date)

manekineko
 Posté le 19/05/2019 à 16:00 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne
Debrief a écrit :

Et aussi je ne comprends pas bien ton assertion:

Précision : la date d'entrée la plus ancienne doit être sur 2019 en cas d'entrée en 2019, ou sur les années précédentes si la personne n'est pas entrée en 2019...

Est-ce que tu veux dire :

- Entrées 01/05/2017, 05/03/2018 -> on prendra 01/05/2017

- Entrées 01/05/2017, 05/03/2018, 01/02/2019, 01/07/2019 -> on prendra 01/02/2019

?

Effectivement, il y a plusieurs date de d'entrée. Le résultat doit être :

- Entrées 01/05/2017, 05/03/2018 -> on prendra 05/03/2018 ; soit la plus récente qui n'existe pas sur l'année en cours

- Entrées 01/05/2017, 05/03/2018, 01/02/2019, 01/07/2019 -> on prendra 01/02/2019 ; oui la plus ancienne sur l'année en cours

manekineko
 Posté le 19/05/2019 à 16:05 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne
Debrief a écrit :

Je pourrais éventuellement faire les recherches par rapport au n° insee, ce serait certainement mieux

A mon humble avis ce serait effectivement mieux, il y a toujours moyen de s'en sortir pour les plages dynamiques, mais tu as fais tes choix en connaissance de cause.

Autre question: toutes les dates de la feuille source sont en format Texte (précédée de '). Veux-tu conserver ce format dans le résultat ou bien avoir un vrai format Date qui permette tri, classement, filtrage etc... ? (je te recommande Date)

Pour les dates, j'ai tout converti au format date excepté celles de la première feuille. Je ne suis rendue compte que les autres formules ne fonctionnaient pas à cause de ça et j'ai perdu un temps fou pour une bricole... sur mon fichier, il n'y a que le multiplié par 1 qui fonctionne à 100 %.

Je n'ai pas appliqué ce format sur le fichier exemple, mais il est bien appliqué sur le fichier original.

manekineko
 Posté le 19/05/2019 à 16:14 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne
Debrief a écrit :

Autre question: dans tes formules (ex pour le genre =RECHERCHEV($A4;données2019;7;FAUX)) tu recherches sur le n° de ligne. Tu ne préférerais pas chercher sur le Matricule / Numéro ?

Je remarque que mon fichier exemple n'est pas vraiment un bon exemple ! En fait je retravaille un ancien fichier ; j'ai remplacé les recherchev par index/equiv, sauf pour les recherches sur des tableaux fixes du genre :
code 1 = texte blablabla1
code 2 = texte blablabla2
...

manekineko
 Posté le 19/05/2019 à 16:16 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Auriez-vous besoin de la dernière version du fichier original ?

Debrief
 Posté le 19/05/2019 à 18:55 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Je t'envoie ton fichier exemple avec les 2 macros en tant que fonctions personnalisées.
https://cjoint.com/c/IEtqMd87vih

Exemples:

=dateentrée('2019'!A:A;'2019'!Q:Q;$A4;2019)
=datesortie('2019'!A:A;'2019'!T:T;$A4)

Le code est commenté et tu y trouveras les significations des paramètres que tu peux d'ailleurs deviner sans problème.
Il y a un paramètre de plus pour la date d'entrée qui est affectée par l'année ainsi que tu l'as spécifié. L'année n'intervient pas pour la date de sortie.

S'il y a quelque chose à modifier dans le traitement que je n'aurais pas compris, explique-le moi.

Quelques commentaires:

Je ne connais pas toutes les règles de recalcul des fonctions personnalisées.

Ce que je sais c'est que pour que le recalcul ait lieu sur les éléments pour lesquels on souhaite provoquer un recalcul quand ils sont modifiés, il faut les passer en paramètres pour en donner à Excel la visibilité.
C'est donc pour ça, en dehors du fait qu'ils sont nécessaires au traitement, que les paramètres des colonnes des n° de ligne, de dates d'entrée et de dates de sortie sont passés aux fonctions de cette manière.
La modification de valeurs dans ces colonnes
(A, Q, T) de la feuille source provoquera le recalcul des fonctions.

Maintenant, pas mal d'autres modifs dans la feuille de travail où sont appelées les fonctions et aussi dans la feuille source provoquent le recalcul de ces fonctions. Je ne sais absolument pas pourquoi ni dans quelles conditions (qui varient parfois) car ça ne se justifie pas (*) lorsque ces modifications ne sont pas en lien avec les paramètres cités dans les fonctions, en l'occurrence les colonnes A, Q ou T de la feuille source et la cellule référencée ex. $A4 de la feuille de travail.

Tant que le nombre d'appels de fonctions concernés est faible (on parle en milliers ou X10) et que le traitement est léger (ce que j'ai essayé de faire grâce à ton classement croissant des n° de lignes) ça n'a pas d'impact significatif.
La saisie n'est pas retardée par le recalcul.
D'ailleurs, les fonctions Excel natives sont probablement recalculées fréquemment, mais sans doute avec une optimisation de l'opportunité de le faire.

Si tu le souhaites, tu peux voir quand les fonctions sont recalculées en supprimant le commentaire sur le MsgBox d'entrée de la fonction.
Exemple: 'MsgBox "Enter DateEntrée(" & RangeSourceNuméroLigne.Address & ", " & RangeSourceDateEntrée.Address & ", " & NuméroLigne & ", " & Année & ")"
Attention: autant de MsgBox que de fonctions citées ! Donc si tu fais un test en ce sens, ne laisse qu'une seule fonction dans ta feuille de travail.

Edit: j'ai oublié de te dire que les cellules où sont appelées les fonctions personnalisées doivent avoir le format Date car elles renvoient soit une vrai date Excel, soit une chaine vide.

Edit:(*) Si le recalcul non justifié des fonctions personnalisées posait un problème de performance, il faudrait probablement gérer un "flag" sur l'évènement Worksheet_SelectionChange() et/ou sur l'évènement Worksheet_Change() qui conditionnerait la sortie immédiate des fonctions si la sélection et/ou la modification ne concernait pas les données affectant leurs valeurs de retour. Mais ce n'est pas le cas ici. Inutile de compliquer.



Modifié par Debrief le 19/05/2019 19:51
manekineko
 Posté le 19/05/2019 à 21:31 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Merci Debrief, ça fonctionne bien !

Juste une petite précision : dans quelle cellule j'ajoute l'année en cours, en cas de modification d'année ?

Merci encore pour tout !

Debrief
 Posté le 19/05/2019 à 23:25 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

L'année qui est prise en considération pour la différence de traitement dans la date d'entrée est celle que tu passes en dernier argument de la fonction =dateentrée('2019'!A:A ; '2019'!Q:Q ; $A4 ; 2019).

En fait, les fonctions ne prennent rien d'autre que ce qui vient des arguments que tu leurs passes. Il n'y a qu'une seule chose qui soit paramétrée dans les fonctions c'est:
Private Const FeuilleSourceNbLigTitre = 3 'Nombre de lignes de titre de la feuille source des données
Paramètre que tu peux changer si tu viens à modifier l'entête de ta feuille source et y ajoutes ou en supprimes des lignes. Je n'ai pas voulu passer ça en paramètre mais c'est aussi possible ! Sur demande :)

On serait en 2022 et la feuille source s'appellerait "le joli mois de mai", que tu appellerais la fonction avec =dateentrée('le joli mois de mai'!A:A ; 'le joli mois de mai'!Q:Q ; $A4 ; 2022)
Tu peux d'ailleurs faire l'expérience de renommer ta feuille "2019" en "le joli mois de mai" et tu verras que le nom aura été modifié par Excel dans les fonctions de la feuille de travail sans modifier le résultat.

J'ai ajouté 2 commentaires (exemples d'appel) et fait une modif mineure dans le code sans aucun effet (par défaut VBA avait pris la bonne valeur mais là je l'ai explicitée).

Fichier -> https://cjoint.com/c/IEtvmZzlgwh (tu peux exporter le module (clic droit sur le module ModuleDates / Exporter -> ModuleDates.bas) de ce fichier et l'importer dans ton fichier réel après avoir supprimé le précédent ModuleDates).

Cordialement,
D.

Publicité
Debrief
 Posté le 20/05/2019 à 07:21 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Désolé de t'imposer une nouvelle version -> https://cjoint.com/c/IEufoSAEKDA

Mais dans cette dernière mouture j'ai:

  1. Supprimé la constante Private Const FeuilleSourceNbLigTitre = 3 et fait une recherche programmée de la 1ère ligne utile de la feuille source qui contient 1 dans le numéro de ligne.
    Ça fait un élément de moins à gérer et les fonctions sont maintenant 100% autonomes.
    .
  2. Sécurisé le test du numéro de ligne (une histoire VBA quand la valeur de cellule n'est pas numérique). Le cas ne se présentait pas mais c'est par sécurité.

Bonne journée.

P.S. Si un jour tu décidais de te passer du numéro de ligne et utiliser directement le n° INSEE (il y a des formules pour calculer un nombre de valeurs sans doublons dans une colonne) il faudrait un peu adapter les fonctions sans que ça présente de difficulté particulière. La recherche de la 1ère ligne utile pourrait se faire sur la structure / nombre de caractères du n° INSEE et non plus sur la valeur 1.



Modifié par Debrief le 20/05/2019 07:41
manekineko
 Posté le 25/05/2019 à 21:57 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petite astucienne

Merci !

J'étais un peu débordée : je pense me pencher sur le fichier ce weekend.
Je vais commencer par faire fonctionner correctement le fichier avant de tenter de l'améliorer.

C'est très agréable de pouvoir compter sur des personnes expertes !
Mille sincères remerciements !

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Les bons plans du moment PC Astuces

Tous les Bons Plans
19,79 €Prise connectée Wi-Fi TP-Link HS100 à 19,79 €
Valable jusqu'au 01 Mars

Amazon fait une promotion sur la prise intelligente Wi-Fi TP-Link HS100 qui passe à 19,79 € alors qu'on la trouve habituellement autour de 35 €. Cette prise peut être contrôlée à distance en utilisant l'app gratuite KASA sur votre smartphone (iOS ou Android). Vous pouvez créer des planifications horaires pour allumer ou éteindre automatiquement et quand vous le souhaitez, l'appareil qui y est branché. Pour en savoir plus, n'hésitez pas à lire notre dossier pratique Contrôler une prise électrique à distance.


> Voir l'offre
99,99 €Pack HP Imprimante Photo Sprocket 200 blanche + housse + 20 papiers photos à 99,99 €
Valable jusqu'au 26 Février

La Fnac fait une promotion sur le pack HP Imprimante Photo Sprocket 200 accompagnée d'une housse et de 20 papiers photos à 99,99 € alors qu'on le trouve ailleurs à 129 €. L'imprimante Photo Sprocket 200 va vous permettre d'imprimer des photos instantanées de 5 x 7,6 cm (2 x 3") depuis votre smartphone via le bluetooth. 


> Voir l'offre
GratuitJeu PC Assassin's Creed Syndicate gratuit
Valable jusqu'au 27 Février

Epic Game Store offre actuellement le jeu PC Assassin's Creed Syndicate. Incarnez Jacob Frye, un jeune Assassin impétueux et rebelle, et utilisez vos capacités pour aider les laissés-pour-compte dans la marche vers le progrès. Parcourez la ville à l'apogée de la Révolution Industrielle et rencontrez des personnages historiques emblématiques. De Westminster à Whitechapel, croisez Darwin, Dickens, la reine Victoria, et bien d'autres. En tant que dirigeant d'un gang, fortifiez votre repère et ralliez les membres des gangs rivaux à votre cause afin de reprendre la capitale des mains des Templiers.


> Voir l'offre

Sujets relatifs
Aucun sujet pertinent lié trouvé
 > Tous les forums > Forum Bureautique