Menu

Chercher une valeur répondant à trois critères variables [Résolu/Fermé]

Messages postés
9
Date d'inscription
mardi 28 avril 2015
Statut
Membre
Dernière intervention
30 avril 2015
- - Dernière réponse : Vaucluse
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
- 30 avril 2015 à 17:49
Chers amis bonjour,
C'est ma première participation dans ce forum que je viens de découvrir et qui me plait bien. J'aimerais que vous m'aidiez à résoudre un petit problème qui commence à me désespérer, étant donné que je suis novice en VBA et que mes connaissances en fonction Excel sont loin d'être affutées.

Mon objectif est simple à concevoir :
1/ j'ai deux feuilles Excel 2010 : une base de données (environ 6 000 lignes), avec des informations du type DATE/VEHICULE/HEURE_PASSAGE et une feuille export (environ 400 lignes), avec des informations du type DATE/VEHICULE/HEURE_JOURNEE/TRAJET/COEFFICIENT. Je souhaite alimenter ma base de données à partir d'information de ma feuille export.

2/ dans ma base de données et mon export, les informations de DATE/VEHICULE peuvent être identiques sur plusieurs lignes alors que l'HEURE_PASSAGE et HEURE_JOURNEE/TRAJET/COEFFICIENT varient.

3/ je cherche une formule me permettant d'aller chercher dans ma "feuille export" les informations spécifiques TRAJET et COEFFICIENT, selon des critères déterminés et les introduire dans ma "base de données" aux lignes répondant aux critères.
Mes critères sont :
- si la DATE base de données = DATE export ;
- si le VEHICULE base de données = VEHICULE export ;
- si l'HEURE_PASSAGE < la plus petite des deux HEURE_JOURNEE correspondante à la DATE et au VEHICULE
      • Attention petite subtilité : il existe souvent 2 valeurs HEURE_JOURNEE pour une même DATE et un même VEHICULE. Or si l'HEURE_PASSAGE est < la plus petite des 2 HEURE_JOURNEE alors il faut lui attribuer un TRAJET (trajet A), sinon un autre TRAJET correspond (trajet B).



Pour finir, mes remarques : la fonction Recherchev ne me permet pas d'aller chercher une information répondant à de multiples critères et je n'ai pas de clé unique associant mes critères ... Comme je vous l'ai dit précédemment, pour une même DATE et un même VEHICULE, je peux avoir 2 TRAJETS d'où mon troisième critère.
Les fonctions matricielles, Index ou Equiv ou Bdlire, ne me permettent pas d'introduire toutes les occurrences, elles s'arrêtent aux premiers enregistrements trouvés.

Vous l'aurez compris, sans aide, ni petit coup de pouce, je risque de chercher encore longtemps... C'est pourquoi je m'adresse à vous, amis éclairés et amateurs de challenge pour m'orienter vers une solution...

Si besoin, je vous transmettrai mon fichier.
Afficher la suite 

5 réponses

Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
5788
0
Merci
Bonjour

voila une tartine bien difficile à digérer, et qui ne donne pas de précisions suffisantes sur votre organisation (colonne / ligne) etc
pour faciliter cette digestion, pouvez vous placer un modèle de votre fichier avec quelques explications et exemple de ce que vous chercher sur:
http://www.cjoint.com
et revenir ici coller le lien donné par le site

A vous lire

Errare humanum est, perseverare diabolicum
Fifi_apprend
Messages postés
9
Date d'inscription
mardi 28 avril 2015
Statut
Membre
Dernière intervention
30 avril 2015
-
Bonjour Vaucluse,

Voici le lien via lequel vous trouverez le fichier modèle :
http://cjoint.com/?EDCsk4e3XZI

Précision sur l'organisation :
  • Dans l'onglet "base de donnees" j'ai surligné les données en jaune les données pour lesquelles je souhaiterai automatiser la recherche.
  • Je souhaite appliquer la formule à sur l'ensemble des lignes de mon tableau "base de donnees".


Cela est-il plus clair? Sinon, n'hésitez pas.
Merci d'avance.
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
5788
0
Merci
Pouvez vous préciser quel est l'argument qui permet de choisir l'heure dans la colonne C de Export si la date et le type sont identiques, alors que les heures ne le sont pas?

A vous lire
Fifi_apprend
Messages postés
9
Date d'inscription
mardi 28 avril 2015
Statut
Membre
Dernière intervention
30 avril 2015
-
Encore une précision, j'ai essayé d'organiser les informations de mon onglet Export dans un TCD. J'ai réussi à obtenir le nombre de trajets réalisé par les camions/date (date en ligne, numéro des camions en colonnes et nombre de trajets aux intersections date/camion correspondant). Par contre je n'ai pas réussi faire correspondre le(s) nom(s) du trajet...

J'ai également essayé avec la fonction Excel BDLIRE... Mais elle nécessite des critères définis dans un tableau sur 2 lignes, une pour les champs et la seconde pour les critères correspondants. Or comme je souhaite faire "glisser" ma formule sur toute une colonne, ça ne fonctionne pas. Et je n'ai pas réussi à trouver le moyen de fixer la première ligne de mon tableau de critère...
Vaucluse
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
5788 -
Bon, je crois que j'ai compris, je vous regarde ça dans l'après midi. Ca devrait pouvoir se faire avec un jeu de formule
Donc si je traduis dans mon langage
si l'heure de Base est plus petite que la plus petite des heures de export, on prend la ligne correspondante
si l'heure de Base est supérieure à la plus petite , on prend la ligne de la plus grande heure
Mais pour qu'elle soit le plus simple possible, si vous passer par la, pouvez vous me dire si l'enregistrement dans la feuille export est toujours progressifs, et donc les dates et les heures toujours croissantes?
en supposant que les codes véhicules, eux, ne sont pas classés, bien sur
(... et sachant aussi que pour l'ordre croissant de la date associée à l'heure, le 14/3/2015 à 00:01h et plus grand que le 13/3/2015 à 23:59)
le but de la manoeuvre étant d'obtenir un nombre en concaténant la date et l'heure

à vous lire
Fifi_apprend
Messages postés
9
Date d'inscription
mardi 28 avril 2015
Statut
Membre
Dernière intervention
30 avril 2015
> Vaucluse
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
-
Votre langage est une très bonne traduction.

Et non l'enregistrement de la feuille export n'est pas progressif sur les heures, mais il l'est sur les dates.
Pour plus de précision, les dates sont croissantes. Les codes véhicules sont enregistrés de manière aléatoire : on a un AAA05208, puis un AAA05220, AAA05219, puis AAA05212... Par contre, les heures associées aux véhicules sont, elles, croissantes et se succèdent. Par exemple,

VEHICULE DATE HEURE TRAJET
AAA05208 01/01/2015 18:53 BB130151
AAA05208 01/01/2015 20:54 BB130152
AAA05220 01/01/2015 17:19 BB130011
AAA05220 01/01/2015 19:23 BB130012
AAA05219 01/01/2015 17:13 BB130021
AAA05219 01/01/2015 19:15 BB130022
AAA05212 01/01/2015 17:52 BB130031
AAA05212 01/01/2015 20:14 BB130032

Si je comprends votre concaténation, vous voulez associer un nombre (date+heure) à un véhicule et, donc, à un trajet. De cette manière, nous avons un tableau de clé de répartition avec 3 valeurs distinctes... Mais après je ne vois pas comment vous voulez l'utiliser. Je suis bien curieuse.
Vaucluse
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
5788 -
Dernière question et je m'y met
pour une même date les deux références du même véhicule sont elles toujours à la suite l'une de l'autre dans la feuille Export ?
Fifi_apprend
Messages postés
9
Date d'inscription
mardi 28 avril 2015
Statut
Membre
Dernière intervention
30 avril 2015
-
Après vérification, non car je viens de trouver cet exemple : pour une même date, on a une référence d'un véhicule pour 1 trajet située entre la référence d'un deuxième véhicule qui lui a fait 2 trajets...
Ce qui donne :

VEHICULE DATE HEURE TRAJET
AAA05218 12/03/2015 19:18 BB130091
AAA05217 12/03/2015 21:24 BB130092
AAA05218 12/03/2015 22:05 BB130111
AAA05207 12/03/2015 20:50 BB130142
AAA05209 13/03/2015 19:14 BB130151
AAA05209 13/03/2015 20:58 BB130152

J'ai trouvé aussi pour une journée, un véhicule qui a fait 3 trajets... Ce cas de figure ne s'est produit que pour 1 journée et 1 véhicule. C'est donc très ponctuel. Je pourrais réfléchir à "encadrer" les heures de la Base de donnée par les heures Export.
Si vous avez encore des questions, n'hésitez pas. Encore merci!
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
5788
0
Merci
Voila une première option, avec une feuille "calc" qui assure la sélection des données, (mais avec deux lignes seulement par type et date dans la feuille export):
le principe:
on forme une valeur numérique en concaténant le code type, la date et l'heure, autant pour la feuille export (colonne A de calc) que pour la feuille base de données (colonne B de calc)
(le code type en tête de valeur évite les chevauchement de valeur dans les résultats)
en fonction de la concaténation, les colonnes C et D de calc ressorte les mini maxi de chaque ensemble
les formules de base de données vont, selon le cas chercher la ligne qui correspond:
au mini si l'horaire est inférieur ou égal au mini de export
au maxi sinon.
Si jamais il y a plus de trois lignes même type et même date dans export, le système renverra (je crois) toujours les données par rapport au min/maxi

A noter toutefois, important:

la feuille calc utilise des matricielles assez pénibles et qui ralentissent le fichier>. Il est actuellement monté sur 400 lignes

Reste donc à valider:
que tous les cas de figures soient bien pris en compte
que le fichier reste utilisable raisonnablement au delà d'un certain nombre de lignes.
(Dans tous les cas, il faut un peu de patience pour monter ou tirer les formules, mais ça n'augure pas des temps de réponse ensuite)

si toutefois le système s'avérait trop lourd il faudra passer par VBA et là, je vous confie aux mains des experts de très haut niveau qui interviennent sur ce forum, mais dont je ne fais pas partie)

http://cjoint.com/?EDDp6o3Pzch

à votre écoute
Crdlmnt
Fifi_apprend
Messages postés
9
Date d'inscription
mardi 28 avril 2015
Statut
Membre
Dernière intervention
30 avril 2015
-
Merci pour l'aide. Ca semble fonctionner sur le fichier modèle.
Avantde l'appliquer à mon fichier, je souhaite le comprendre. Et en le regardant, j'ai de nombreuses questions :

1/ la fonction droite, je connais. Mais je ne l'ai jamais appliquée de cette manière. A quoi cela sert-il? Le "&" permet de concaténer? Mais le *1...??
Pourquoi n'utilisez-vous pas la formule CONCATENER?

2/ A quoi servent les accolades devant la fonction? Ce n'est pas la première fois que je lis cela.

3/ Je ne saisis pas ce que font vos formules MIN et MAX... C'est vraiment très élaboré pour moi, et pour bien comprendre, je pense avoir besoin des étapes intermédiaires...
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
5788
0
Merci
Alors quelques explications, mais par le net, ça ne va pas être facile
1/ la fonction droite, je connais. Mais je ne l'ai jamais appliquée de cette manière. A quoi cela sert-il? Le "&" permet de concaténer? Mais le *1...??
Pourquoi n'utilisez-vous pas la formule CONCATENER?

CONCATENER(A1;A2;A3) ou A1&A2&A3 reviennent au même, c'est uniquement une question de goût
DROITE(Cell;4) sert à extraire les 4 derniers chiffres du code TYPE. En les plaçant en tête de la concaténation, on crée une "fourchette" de valeur qui permet d'identifier les lignes en fonction du type sans risque de con fusion avec d'autres valeurs.
pourquoi le*1
une concaténation créé dans Excel une valeur texte qui n'est pas utilisable comme valeur numérique. Donc inutilisable pour les mini maxi.
Le *1 permet de retrouver une valeur numérique

2/ A quoi servent les accolades devant la fonction? Ce n'est pas la première fois que je lis cela.
Les accolades ne sont pas entrées au clavier dans l'écriture de la formule. Elles découlent du type d'entrée d'une formule dite matricielle, qui s'effectue avec la touche enter en maintenant les touches ctrl et shift enfoncées . Les accolades se placent automatiquement.

3/ Je ne saisis pas ce que font vos formules MIN et MAX... C'est vraiment très élaboré pour moi, et pour bien comprendre, je pense avoir besoin des étapes intermédiaires...
Les formules MIN et MAX justement sont matricielles. Elles traitent ligne par les lignes les champs spécifiés, de façon à trouver le maxi et le mini de chaque assemblage type et date seulement
elle vont donc chercher dans la matrice, la valeur concaténée sur toutes les lignes et ressortir la valeur mini et maxi de la colonne A:A
Le code cherché étant composé uniquement, là, du type et de la date, elle s'adresse uniquement à la valeur entière de A via le code=ENT(A2)
__sachant que les décimales du code en A, de par les fonctions horaires d'Excel (dont l'unité de temps est le jour) sont forcément les décimales (pour Excel, 24h =1)

en plus clair peut être:
la formule analyse toutes les valeurs entières de A qui correspondent à la condition selon valeur obtenue par
DROITE('base de données'!$A2;4)&'base de données'!$B2)*1
et va ressortir le MINI de A ou le MAXI selon la formule.

ensuite, en feuille base de données, la formule décide dans le code EQUIV si la recherche doit s'effectuer avec le min ou avec le maxi selon la position de la valeur complète par rapport au mini
(si plus petite ou égal au mini, s'effectue avec le mini, sinon avec la maxi)
ce système permet au code EQUIV de fonctionner avec la recherche d'une valeur exacte existant en colonne A de calc. Ce qui permet de se passer du classement des dates et horaire par ordre croissant.

Wouououffff. je vous souhaite une bonne lecture, et précise que je ne fournis pas l'aspirine.
A défaut, je peux compléter si vous avez d'autres questions.
bon courage

crdlmnt
Fifi_apprend
Messages postés
9
Date d'inscription
mardi 28 avril 2015
Statut
Membre
Dernière intervention
30 avril 2015
-
J'ai compris. Et maintenant je mets en application.

Dans la formule du MIN, est-ce important de faire appel à une colonne dans la même feuille de calcul? ou le même fichier que celui dans lequel on écrit notre formule.

Je m'explique, j'ai commencé par faire appel à la colonne correspondante "VEHICULE+DATE+HEURE" dans mon fichier Export (car bien sûre, ce n'était des onglets que dans mon modèle, mais des fichiers séparés dans la réalité) j'obtiens un #VALEUR!...
=MIN(SI(O56=ENT('[2015-04-29_v16h45_data-export_V2.xlsx]T2 NomFichierExport'!$J:$J);'[2015-04-29_v16h45_data-export_V2.xlsx]T2 NomFichierExport'!$J:$J;1))

Si je fais un test en prenant 1 cellule de mon fichier Export (J23 par exemple), j'obtiens un résultat qui semble convenir
=MIN(SI(O56=ENT('[2015-04-29_v16h45_data-export_V2.xlsx]T2 NomFichierExport'!$J23);'[2015-04-29_v16h45_data-export_V2.xlsx]T2 NomFichierExport'!$J23;1))

Et si j'importe mes données de "VEHICULE+DATE+HEURE" de mon fichier Export dans ma feuille de calcul, en prenant la colonne, j'obtiens un résultat qui semble convenir
=MIN(SI(O56=ENT(P:P);P:P;1))

Connaissez-vous une explication qui justifie les résultats ? Ai-je oublié un point essentiel?

Encore merci.
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
5788
0
Merci
Ca n'a aucune importance, faites seulement attention à bien caler la correspondance des lignes (départ sur ligne 2 pour tous les tableaux.
Je préfère pour ma part utiliser la feuille Cal pur ne pas perturber les tableaux que vous manipulez pour entrer les données, mais c'est vous qui voyez.
J'aurais du mal à vous dire si vos formules conviennent ou pas, compte tenu de vos données
Tout ce que je peux dire, c'est :
que MIN ou MAX ne peuvent se référer qu'à des valeurs numériques, ce qui imposait dans le mien, le DROITE(Cell;4) que je ne vois pas dans vos formules.
que je ne vois pas non plus dans vos formules sauf dans la dernière, de limites de champ où chercher MIN car il n'y a qu'une cellule spécifiée.

Mais pour le reste, va falloir que vous compariez toute seule avec vos résultats, (moi je suis trop loin des infos,) si ça correspond bien à ce que vous attendez.

crdlmnt

Fifi_apprend
Messages postés
9
Date d'inscription
mardi 28 avril 2015
Statut
Membre
Dernière intervention
30 avril 2015
-
Bonjour Vaucluse,

J'ai travaillé sur vos formules. Ca correspond bien à ce que j'attends.

Je vous remercie d'une part pour votre disponibilité et réactivité... Et d'autre part pour votre patience à m'avoir expliquer en détail le fonctionnement des formules que vous aviez utilisées. Grace à votre travail, j'ai avancé d'un pas dans mon étude, et maintenant je maitrise beaucoup mieux ces quelques formules.

Encore merci de ce coup de pouce bénévole et ce temps passé !
A une prochaine fois.
Vaucluse
Messages postés
23953
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
17 mai 2019
5788 -
Pas de quoi fifi,
bonne route et à une prochaine fois si besoin
bonne route
je passe le sujet en résolu
crdlmnt