XL 2010 Recherche multi-critères dont 2 dates et heures

[solosuccess]

Bonjour,

Je fais appel à vos idées car je suis bloqué en l'état.

J'ai un premier onglet qui comporte une liste de passages de péages :
- colonne A : immatriculation
- colonne F : date de sortie de péage
- colonne G : heure de sortie de péage
Le deuxième comporte les périodes de service de nos conducteurs, également appelées "disques" :
- colonne B : conducteur
- colonne E : immatriculation
- colonne F : date début service
- colonne G : heure début service
- colonne H : date fin service
- colonne I : heure fin service

L'objectif est de faire apparaître, dans le premier onglet en colonne L, le nom du conducteur concerné pour chaque passage de péage.
L'immatriculation est un champ commun aux 2 onglets.
C'est sur les dates et heures que je bloque, d'autant plus qu'un disque peut démarrer la veille du passage de péage ou bien se terminer le lendemain.
La clé pour retrouver l'identité du conducteur réside dans la date et l'heure du passage de péage qui doivent être situés entre les bornes de début et de fin de service.

S'il n'y avait eu qu'un seul disque par conducteur et par camion et par jour, j'aurais réussi grâce à INDEX et EQUIV.
Mais pour notre cas, le véhicule peut être utilisé par plusieurs personnes le même jour.

Je remercie par avance ceux qui pourront aider à résoudre ce problème.
Je joins le fichier en PJ.

Bonne journée et encore merci

 

[Dugenou]

Bonjour,
pour date et heure c'est simple : il suffit de faire la somme. j'ai donc ajouté 2 colonnes dans chaque tableau
Ensuite un sommeprod pour récupérer le N° de ligne et un index pour afficher le chauffeur.
En considérant que le début du chauffeur commence avant l'entrée péage et que la fin du chauffeur se termine après la sortie (pas de changement entre entrée et sortie.
Attention : le sommeprod sur plusieurs milliers de lignes peut générer un long temps de calcul.

Cordialement

 

[solosuccess]

Bonjour,
Je viens de tester les calculs : tout fonctionne à merveille !
Merci beaucoup pour cette réponse rapide et votre aide, qui va aussi me servir pour d'autres recherches.
Bonne continuation et encore un grand merci!
Cordialement

 

[solosuccess]

Bonjour Dugenou,

Pardon de vous déranger avec la suite de mon problème.
J'ai réussi à décliner la formule combinée INDEX et SOMMEPROD pour rattacher les péages aux disques de nos conducteurs.
A présent, je cherche à rattacher les ordres de transport (OT) effectués par le chauffeur aux disques correspondants.
J'ai voulu utiliser la même formule pour afficher dans l'onglet OT les dates et heures de début et de fin de disque.
Le but est similaire à la précédente demande : dans l'onglet OT, nous nous basons sur la date et heure de livraison, l'immatriculation, le nom du conducteur, que nous sommes censés retrouver dans l'onglet DISQUES.
Les dates et heures de chargement dans OT étant parfois erronées, j'ai utilisé uniquement DATE ET HEURE DE LIVRAISON qui doit être compris strictement entre le début et la fin du disque du chauffeur sur l'immat concernée.
Malheureusement, je n'obtiens pas de bons résultats (#VALEUR!).
Pourriez-vous svp jeter un œil au fichier ci-joint et m'aider à obtenir les résultats ?

A la fin, je cherche à "chaîner" PASSAGES (de péages), DISQUES et OT pour être en mesure de :
- dans PASSAGES, identifier la journée de travail concernée issue de DISQUES donc donner les heures, kilomètres, etc... et, en provenance d'OT, donner le numéro de Dossier puis le CA, etc... ;
- dans DISQUES, donner le montant des péages issus de PASSAGES et, en provenance d'OT, donner le numéro de Dossier puis le CA, etc... ;
- dans OT, identifier la journée de travail concernée issue de DISQUES donc donner les heures, kilomètres, etc... et, en provenance de PASSAGES, donner le montant des péages ;

Il semble que tout repose sur les fonctions INDEX-SOMMEPROD mais je n'irai pas bien loin en l'état...

Vous remerciant par avance,
Bonne journée

 

[Dugenou]

Bonjour,
Il y avait un pb dans la plage d'index : il faut sélectionner toute la colonne et cela doit afficher DISQUES[DEBUT DISQUE] et non DISQUES[@[DEBUT DISQUE]] dans ce dernier cas votre zone d'index ne fait qu'une ligne.
Voir pj avec correction ou j'ai pris seulement heure de livraison dans la plage du disque. En ligne 20 on a bien le chauffeur et l'immat mais pas dans la plage du disque (voir colonne de vérification à droite)

Je regarde le reste

 

[Dugenou]

Voir pj où j'ai essayé de faire quelques uns des rapprochements demandé : vérifiez si c'est OK
J'ai l'impression qu'on refait plusieurs fois les rapprochements : il vaudrait mieux ne les faire qu'une fois en construisant une base de données puis analyses à partir de la base. Le risque est que tous ces sommeprod qui croisent toutes les données donnent un fichier très long à calculer quand vous aurez beaucoup de données

Cordialement

 

[solosuccess]

Bonjour,

Tout d'abord, je vous remercie pour votre aide.
J'ai réussi à reproduire vos formules exceptée celle qui remonte le montant des péages sur chaque disque. Pour celle-ci, je n'obtiens que des #VALEUR!.
Pouvez-vous svp m'éclairer?

Ensuite, je comprends vos réserves sur ces différents croisements de données.
Simplement, je vous donne quelques précisions sur le but final recherché : la finalité est de construire un compte de résultat analytique par jour et donc connaître la rentabilité de chaque opération.
Il faudrait que nous puissions ventiler sur chaque OT ("Dossier") les heures et kilomètres présents dans DISQUES, ainsi que tous les PEAGES.
Il y a plusieurs écueils à cette démarche :
- les OT sont parfois planifiés à des horaires qui ne correspondent pas à la réalité (exemple : chargement prévisionnel à 23h alors que le disque commence le lendemain à 00h05) ;
- il y a parfois plusieurs OT pour un seul disque ;
etc...
La table qui regrouperait le tout serait celle des OT : elle afficherait les heures et kms, péages affectés.
Il resterait évidemment les disques non affectés et donc les OT sans ventilations : pour ceux-là, j'imaginais de modifier à la main les dates et heures de chargement dans les OT s'il s'agit de planifications erronées. Pour les disques et les péages qui sont réellement sans OT (cela peut arriver quand les conducteurs à des travaux annexes), j'imaginais de créer une ventilation séparée.

Merci encore pour votre aide précieuse.
Bonne journée

 

[Dugenou]

Bonjour,
Difficile de répondre sur votre premier point sans votre fichier avec votre formule.
dans mon fichier (le second V2) :
=SOMMEPROD((Passages[IMMAT]=[@IMMAT])*(Passages[CONDUCTEUR]=[@[NOM PRENOM]])*(Passages[entree]>=[@[DEBUT DISQUE]])*(Passages[sortie]<=[@[FIN DISQUE]])*Passages[PEAGE HT])
C'est un sommeprod ((condition1)*(condition2)*.(..)..*colonne des péages) : on obtient la somme de tous les péages qui correspondent aux conditions (plus d'index cette fois ci et risque de compter deux fois le même péage s'il les conditions sont remplies sur 2 lignes).
Le #valeur peut s'expliquer si les valeurs de la colonne péage ne sont pas des chiffres : caractère utilisé pour la virgule ?? il faut vérifier que les valeurs de cette colonne sont alignées à droite (chiffres) et non à gauche (texte) quand vous enlevez le format de la cellule.
Voir aussi chaque condition une par une si elle renvoie bien un résultat différent de 0 ; essayer =sommeprod((condition1)*1) : le résultat doit être différent de 0, puis ajoutez les conditions une par une pour voir laquelle cloche.

Cordialement

 

[solosuccess]

Bonsoir,
Je viens de tester : lorsque je fais SOMMEPROD avec la seule immat, cela fonctionne.
C'est lorsque j'ajoute le conducteur que cela ne va plus.
Pourtant, je n'ai rien changé.
Auriez-vous une idée?
Merci d'avance.
Cordialement

 

[Dugenou]

Bonjour,
Si vous n'avez pas de résultat avec=SOMMEPROD((Passages[IMMAT]=[@IMMAT])*(Passages[CONDUCTEUR]=[@[NOM PRENOM]])*1) c'est que vos noms prénoms ne sont pas écrits de la même façon. S'il s'agit d'un export : vérifiez qu'il n'y a pas d'espace après : cela arrive parfois.
Sinon joingnez quelques lignes de votre vrai fichier en masquant les données sensibles.
Cordialement

 

[solosuccess]

Bonjour,
Je vous joins un fichier pour la seule immat BR394WJ.
Vous constaterez les "REF" dans l'onglet "PASSAGES" et les "VALEUR" dans l'onglet "DISQUES".
Je vous remercie par avance.
Cordialement

 

[Dugenou]

vous avez deux disques qui se superposent le 13 entre 2h21 et 2h53 : donc on a deux réponses : les N° de lignes s'additionnent et renvoient une valeur hors zone d'index. Mais quelque chose ne colle pas : vous avez un disque pour un chauffeur qui se termine à 3h59 et l'autre qui commence à 2h06 : il me paraît pas possible d'avoir 2 disques pour 2 chauffeurs pour la même immat entre 2h06 et 3h59

 

[solosuccess]

Bonjour,
j'ai corrigé à la main les disques qui se "chevauchent".
Cela peut arriver notamment lorsqu'il y a double équipage mais cela doit rester rare.
Je commence à comprendre le fonctionnement de ces formules.
Merci encore.
Cordialement

 

[solosuccess]

Bonjour,
j'ai commencé par faire le ménage dans les données avant d'aller plus loin.
A présent, je cherche à ramener dans les péages le nom du client présent dans les OT.
J'ai essayé une formule index + equiv mais elle ne fonctionne pas : INDEX(OT[CLIENT];EQUIV([OT];$R:$R;0))
La colonne R représente les numéros d'OT que nous avons ramenés dans l'onglet PASSAGES.
Auriez-vous svp une solution?
Merci d'avance de votre aide.
Cordialement

 

[Dugenou]

Bonjour,
Le Equiv va renvoyer le N° de l'item dans la colonne R : votre zone d'index doit commencer à la ligne 1 ou bien il faut enlever 1 à votre equiv (si le tableau OT[client] commence à la ligne 2 (sans en tête)
D'autre part la syntaxe equiv([OT] ne semble pas correcte : peut être equiv([@[OT]]....
Joignez un fichier allégé (quelques lignes sans nom de client réel) pour que je puisse regarder en détail.
Cordialement
PS quand vous créez une formule : cliquez sur la cellule ou la colonne du tableau, Excel utilisera directement la bonne syntaxe.