Formule recherchev perfectionnée
Résolu
thibaut_francois
Messages postés
99
Statut
Membre
-
tontong Messages postés 2575 Date d'inscription Statut Membre Dernière intervention -
tontong Messages postés 2575 Date d'inscription Statut Membre Dernière intervention -
Bonjour,
J'ai besoin de votre aide pour élaborer une formule recherchev assez complexe.
Je vous glisse un fichier avec une liste A et une liste B.
https://www.cjoint.com/?0KBprozUUwq
L'idée est de compléter la liste A avec l'élément de colonne H de la liste B. Ceci peut se faire facilement avec une recherchev sur le N° de dossier.
La complexité vient du fait qu'il faut que la Date Exécution et Heure de la liste A soit comprise entre les Date Entrée/Heure Entrée et Date Sortie/Heure Sortie de la liste B.
Pour donner un exemple :
Liste A (ligne 12) - N° Dossier 23931219 le 02/01/2014 à 00:00 doit me rapporter la valeur "3321" de la colonne H de la liste B (ligne 6).
Merci des solutions que vous pourrez m'apporter.
J'ai besoin de votre aide pour élaborer une formule recherchev assez complexe.
Je vous glisse un fichier avec une liste A et une liste B.
https://www.cjoint.com/?0KBprozUUwq
L'idée est de compléter la liste A avec l'élément de colonne H de la liste B. Ceci peut se faire facilement avec une recherchev sur le N° de dossier.
La complexité vient du fait qu'il faut que la Date Exécution et Heure de la liste A soit comprise entre les Date Entrée/Heure Entrée et Date Sortie/Heure Sortie de la liste B.
Pour donner un exemple :
Liste A (ligne 12) - N° Dossier 23931219 le 02/01/2014 à 00:00 doit me rapporter la valeur "3321" de la colonne H de la liste B (ligne 6).
Merci des solutions que vous pourrez m'apporter.
2 réponses
-
Malgré une validation usuelle Sommeprod est une matricielle.
Pour chaque item-condition la formule balaye le champ et renvoie Vrai ou Faux (assimilable à 1 ou 0)
('Liste B'!$A$2:$A$205='Liste A'!C2) renvoie Vrai si le N° 24061065 est trouvé sur la ligne balayée.
('Liste B'!$B$2:$B$205+'Liste B'!$C$2:$C$205)<('Liste A'!A2+'Liste A'!B2) compare date +heure de consigne (B2) à Date + heure de début
('Liste B'!$E$2:$E$205+'Liste B'!$F$2:$F$205)>('Liste A'!A2+'Liste A'!B2) compare date +heure de consigne (B2) à Date + heure de fin
Ligne par ligne la formule effectue le produit des Vrai ou Faux.
Le dernier item ('Liste B'!$H$2:$H$205) définit le champ à " sommer":
La formule additionne les valeurs de ce champ dont les lignes remplissent les 3 conditions.
C'est pourquoi en cas de doublon on trouvera 6642 au lieu de 3321. -
Bonjour,
Un sommeprod permet l'addition jour + heure mais il y a des risques en cas de doublons.
En D2 de Liste A!
=SOMMEPROD(('Liste B'!$A$2:$A$205='Liste A'!C2)*(('Liste B'!$B$2:$B$205+'Liste B'!$C$2:$C$205)<('Liste A'!A2+'Liste A'!B2))*(('Liste B'!$E$2:$E$205+'Liste B'!$F$2:$F$205)>('Liste A'!A2+'Liste A'!B2))*('Liste B'!$H$2:$H$205))
À recopier.
À vérifier avec soin!-
Merci tontong! Une fois de plus, tu résolves mon problème.
J'ai fait une vérif des 20 premiers N° de dossier et c'est bon!
J'ai juste modifié un truc car pour 3 résultats (au global), je n'obtenais pas de résultat :
=SOMMEPROD(('Liste B'!$A$2:$A$205='Liste A'!C2)*(('Liste B'!$B$2:$B$205+'Liste B'!$C$2:$C$205)<=('Liste A'!A2+'Liste A'!B2))*(('Liste B'!$E$2:$E$205+'Liste B'!$F$2:$F$205)>=('Liste A'!A2+'Liste A'!B2))*('Liste B'!$H$2:$H$205))
Peux-tu me mettre l'explication de la formule que je puisse comprendre et la ré-utiliser dans d'autres cas similaires ?
-
